public function generarExcel($condiciones)
{
set_time_limit(0);
ini_set('memory_limit', '-1');
$AgenciaCargaDAO = new AgenciaCargaDAO();
$AgenciaCargaDAO->setEntityManager($this->getEntityManager());
//----------------Se configura las Etiquetas de Seleccion-----------------
$texto_criterio_busqueda = '';
$texto_estado = 'TODOS';
$texto_sincronizado = 'TODOS';
if (!empty($condiciones['criterio_busqueda'])) {
$texto_criterio_busqueda = $condiciones['criterio_busqueda'];
}
//end if
switch ($condiciones['estado']) {
case 'A':
$texto_estado = 'ACTIVO';
break;
case 'I':
$texto_estado = 'INACTIVO';
break;
}
//end switch
switch ($condiciones['sincronizado']) {
case 'SINCRONIZADO':
$texto_sincronizado = 'SINCRONIZADO';
break;
case 'PENDIENTE':
$texto_sincronizado = 'PENDIENTE';
break;
}
//end switch
//----------------Se inicia la configuracion del PHPExcel-----------------
$PHPExcelApp = new PHPExcelApp();
$objPHPExcel = new \PHPExcel();
// Set document properties
$PHPExcelApp->setUserName('');
$PHPExcelApp->setMetaDataDocument($objPHPExcel);
$objPHPExcel->setActiveSheetIndex(0);
//Configura el tamaño del Papel
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(\PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(\PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
//Se establece la escala de la pagina
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);
//Se establece los margenes de la pagina
$objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.1);
$objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.1);
$objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.1);
$objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.1);
//------------------------------Registra la cabecera--------------------------------
$row = 1;
$col_ini = $PHPExcelApp->getNameFromNumber(0);
$col_fin = $PHPExcelApp->getNameFromNumber(5);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, "Agencia Carga");
$objPHPExcel->getActiveSheet()->mergeCells($col_ini . $row . ':' . $col_fin . $row);
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA));
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//------------------------------Registra criterios linea 1--------------------------
$row = 2;
$col_ini = $PHPExcelApp->getNameFromNumber(0);
$col_fin = $PHPExcelApp->getNameFromNumber(5);
$objRichText = new \PHPExcel_RichText();
$objRichText->createText('');
$objInventario = $objRichText->createTextRun(' Criterio: ');
$objInventario->getFont()->setBold(true);
$objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText($texto_criterio_busqueda);
$objInventario = $objRichText->createTextRun(' Estado: ');
$objInventario->getFont()->setBold(true);
$objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText($texto_estado);
$objInventario = $objRichText->createTextRun(' Sincronizado: ');
$objInventario->getFont()->setBold(true);
$objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText($texto_sincronizado);
$objPHPExcel->getActiveSheet()->getCell($col_ini . $row)->setValue($objRichText);
$objPHPExcel->getActiveSheet()->mergeCells($col_ini . $row . ':' . $col_fin . $row);
//------------------------------ Registro de Fecha de Generacion --------------------------------
$row = 3;
$col_ini = $PHPExcelApp->getNameFromNumber(0);
$col_fin = $PHPExcelApp->getNameFromNumber(5);
//$etiqueta = "";
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, "Generado: " . \Application\Classes\Fecha::getFechaHoraActualServidor());
$objPHPExcel->getActiveSheet()->mergeCells($col_ini . $row . ':' . $col_fin . $row);
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA));
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
//---------------------------IMPRIME TITULO DE COLUMNA-----------------------------
$row = $row + 1;
$row_detalle_ini = $row;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, "Nro");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, "Id");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, "Agencia");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, "Direccion");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, "Telefono");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, "Estado");
//----------------------AUTO DIMENSIONAR CELDAS DE ACUERDO AL CONTENIDO---------------
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(0)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(1)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(2)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(3)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(4)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(5)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA));
$objPHPExcel_getActiveSheet = $objPHPExcel->getActiveSheet();
//----------------------CONSULTA LOS REGISTROS A EXPORTAR---------------
$result = $this->listado($condiciones);
$cont_linea = 0;
foreach ($result as $reg) {
$reg['nombre'] = trim($reg['nombre']);
$reg['direccion'] = trim($reg['direccion']);
$reg['telefono'] = trim($reg['telefono']);
$cont_linea++;
$row = $row + 1;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $cont_linea);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, $reg['id']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, $reg['nombre']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, $reg['direccion']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, $reg['telefono']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, $reg['estado']);
}
// end foreach
//Margenes
$col_ini = $PHPExcelApp->getNameFromNumber(0);
$col_fin = $PHPExcelApp->getNameFromNumber(5);
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row_detalle_ini . ":" . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_BORDE_TODO));
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Listado Agencias');
$PHPExcelApp->save($objPHPExcel, $PHPExcelApp::FORMAT_EXCEL_2007, "ListadoAgencias.xlsx");
}
public function stream($filename, $data = null)
{
if ($data != null) {
$col = 'A';
foreach ($data[0] as $key => $val) {
$objRichText = new PHPExcel_RichText();
$objPayable = $objRichText->createTextRun(str_replace("_", " ", $key));
$this->excel->getActiveSheet()->getCell($col . '1')->setValue($objRichText);
$col++;
}
$rowNumber = 2;
foreach ($data as $row) {
$col = 'A';
foreach ($row as $cell) {
$this->excel->getActiveSheet()->setCellValue($col . $rowNumber, $cell);
$col++;
}
$rowNumber++;
}
}
header('Content-type: application/ms-excel');
header("Content-Disposition: attachment; filename=\"" . $filename . "\"");
header("Cache-control: private");
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
$objWriter->save("./assets/{$filename}");
header("location: " . SITE_LINK . "/assets/{$filename}");
unlink(SITE_LINK . "/assets/{$filename}");
}
protected function getBold($value)
{
$objRichText = new PHPExcel_RichText();
$objPayable = $objRichText->createTextRun($value);
$objPayable->getFont()->setBold(true);
return $objRichText;
}
public function testDataTypeForRichTextObject()
{
$objRichText = new PHPExcel_RichText();
$objRichText->createText('Hello World');
$expectedResult = PHPExcel_Cell_DataType::TYPE_INLINE;
$result = call_user_func(array('PHPExcel_Cell_DefaultValueBinder', 'dataTypeForValue'), $objRichText);
$this->assertEquals($expectedResult, $result);
}
/**
* Get hash code
*
* @return string Hash code
*/
public function getHashCode() {
return md5(
$this->_author
. $this->_text->getHashCode()
. __CLASS__
);
}
/**
* Create a XLS template
*
* @param string $group Group Name to include in template
* @param string $issue Issue title to include in template
* @param boolean $to_string return template as string or write to file
*
* @return list($xlsname, $xlsfile) $xlsname: name of xls file for client /
* $xlsfile: filepath to xls file or string containing xls file
*/
function createTemplate($group, $issue, $to_string = false, $format = "Excel5")
{
global $tmpfiles;
// get extension and make sure it's a supported type
switch ($format) {
case 'Excel2007':
$ext = 'xlsx';
break;
case 'Excel5':
default:
$ext = 'xls';
$format = 'Excel5';
break;
}
// load template
$inputFileType = PHPExcel_IOFactory::identify(XLS_TEMPLATE);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load(XLS_TEMPLATE);
$sheet = $objPHPExcel->getSheet(0);
$objRichText = new PHPExcel_RichText();
$objBold = $objRichText->createTextRun(ISSUE_TAG);
$objBold->getFont()->setBold(true);
$objRichText->createText($issue->Title);
$sheet->getCell(ISSUE_CELL)->setValue($objRichText);
$objRichText = new PHPExcel_RichText();
$objBold = $objRichText->createTextRun(GROUP_TAG);
$objBold->getFont()->setBold(true);
$objRichText->createText($group->Name);
$sheet->getCell(GROUP_CELL)->setValue($objRichText);
$objRichText = new PHPExcel_RichText();
$objRichText->createText($issue->Description);
$sheet->setCellValue(DESCRIPTION_CELL, $objRichText);
$sheet->setSelectedCells(DATA_COLUMN_STATEMENT . DATA_ROW_MIN);
$objPHPExcel->getProperties()->setCreator("IC-Discuss");
$xlsname = Utils::sanitizeFilename(APP_TITLE . ' - ' . $group->Name . ' - ' . $issue->Title) . '.' . $ext;
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $format);
if ($to_string) {
ob_start();
$objWriter->save('php://output');
$xlsfile = ob_get_clean();
} else {
$xlsfile = tempnam("tmp", $ext);
$tmpfiles[] = $xlsfile;
$objWriter->save($xlsfile);
}
return array($xlsname, $xlsfile);
}
public function stream($filename, $data = null, $columns)
{
if ($data != null) {
$col = 'A';
foreach ($columns as $key => $val) {
$objRichText = new PHPExcel_RichText();
$objPayable = $objRichText->createTextRun($val);
$this->excel->getActiveSheet()->getCell($col . '1')->setValue($objRichText);
$col++;
}
$rowNumber = 2;
foreach ($data as $row) {
$col = 'A';
foreach ($columns as $key => $val) {
$cell = $row[$key];
if ($key == "clad_condition") {
$cell = $cell == 1 ? "New" : "Used";
} else {
if ($key == "clad_active") {
$cell = $cell == 1 ? "Active" : "In-Active";
} else {
if ($key == "clad_category") {
$catIds = explode("-", $cell);
$catId = $catIds[count($catIds) - 2];
$cell = $_SESSION['categories'][$catId]['cat_name'];
}
}
}
$this->excel->getActiveSheet()->setCellValue($col . $rowNumber, $cell);
$col++;
}
$rowNumber++;
}
}
header('Content-type: application/ms-excel');
header("Content-Disposition: attachment; filename=\"" . $filename . "\"");
header("Cache-control: private");
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
$objWriter->save("export/{$filename}");
header("location: " . base_url() . "export/{$filename}");
unlink(base_url() . "export/{$filename}");
}
/**
* Добавляет таблицу со списком новостей
*
* @param PHPExcel $objPHPExcel
* @param array $newsReportData
*/
protected function addNewsTable(PHPExcel $objPHPExcel, array $newsReportData)
{
$activeSheet = $objPHPExcel->getActiveSheet();
$row = 15;
$linkColor = new PHPExcel_Style_Color('FF538ed5');
foreach ($this->campaign->news as $news) {
$objRichText = new PHPExcel_RichText();
$objRichText->createTextRun($news->name . "\n")->getFont()->setSize(10);
$objRichText->createTextRun($news->url)->getFont()->setColor($linkColor)->setSize(10);
$activeSheet->setCellValue('A' . $row, Yii::app()->dateFormatter->formatDateTime($news->create_date, 'short', null))->setCellValue('B' . $row, $objRichText)->setCellValue('C' . $row, isset($newsReportData[$news->id]) ? $newsReportData[$news->id]['clicks'] + $newsReportData[$news->id]['fake_clicks'] : 0);
// $activeSheet->getCell('B'.$row)->getHyperlink()->setUrl($news->url);
$row++;
}
$activeSheet->setCellValue('A' . $row, 'Итого:')->setCellValue('B' . $row, count($this->campaign->news) . ' новостей')->setCellValue('C' . $row, $this->campaignReportData ? $this->campaignReportData['clicks'] + $this->campaignReportData['fake_clicks'] : 0);
$this->formatTable($activeSheet, 'A', '14', 'C', $row, array('innerRowHeight' => 27.75 * 1.05));
$activeSheet->getStyle('B15:B' . ($row - 1))->applyFromArray(array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT, 'wrap' => true)));
$activeSheet->getStyle('C15:C' . $row)->getNumberFormat()->setFormatCode(self::FORMAT_NUMBER_SEPARATED);
$activeSheet->getColumnDimension('A')->setWidth(11.29 * 1.05);
$activeSheet->getColumnDimension('B')->setWidth(56.14 * 1.05);
$activeSheet->getColumnDimension('C')->setWidth(17.43 * 1.05);
$this->setPageFit($activeSheet, self::FIT_TO_WIDTH);
$this->addLogo($activeSheet);
$this->setHeader($activeSheet, $this->getHeaders());
}
/**
* Get hash code
*
* @return string Hash code
*/
public function getHashCode()
{
return md5($this->_author . $this->_text->getHashCode() . $this->_width . $this->_height . $this->_marginLeft . $this->_marginTop . ($this->_visible ? 1 : 0) . $this->_fillColor->getHashCode() . __CLASS__);
}
/**
* Write Rich Text
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param string|PHPExcel_RichText $pRichText text string or Rich text
* @param string $prefix Optional Namespace prefix
* @throws PHPExcel_Writer_Exception
*/
public function writeRichTextForCharts(PHPExcel_Shared_XMLWriter $objWriter = null, $pRichText = null, $prefix = null)
{
if (!$pRichText instanceof PHPExcel_RichText) {
$textRun = $pRichText;
$pRichText = new PHPExcel_RichText();
$pRichText->createTextRun($textRun);
}
if ($prefix !== null) {
$prefix .= ':';
}
// Loop through rich text elements
$elements = $pRichText->getRichTextElements();
foreach ($elements as $element) {
// r
$objWriter->startElement($prefix . 'r');
// rPr
$objWriter->startElement($prefix . 'rPr');
// Bold
$objWriter->writeAttribute('b', $element->getFont()->getBold() ? 1 : 0);
// Italic
$objWriter->writeAttribute('i', $element->getFont()->getItalic() ? 1 : 0);
// Underline
$underlineType = $element->getFont()->getUnderline();
switch ($underlineType) {
case 'single':
$underlineType = 'sng';
break;
case 'double':
$underlineType = 'dbl';
break;
}
$objWriter->writeAttribute('u', $underlineType);
// Strikethrough
$objWriter->writeAttribute('strike', $element->getFont()->getStrikethrough() ? 'sngStrike' : 'noStrike');
// rFont
$objWriter->startElement($prefix . 'latin');
$objWriter->writeAttribute('typeface', $element->getFont()->getName());
$objWriter->endElement();
// Superscript / subscript
// if ($element->getFont()->getSuperScript() || $element->getFont()->getSubScript()) {
// $objWriter->startElement($prefix.'vertAlign');
// if ($element->getFont()->getSuperScript()) {
// $objWriter->writeAttribute('val', 'superscript');
// } elseif ($element->getFont()->getSubScript()) {
// $objWriter->writeAttribute('val', 'subscript');
// }
// $objWriter->endElement();
// }
//
$objWriter->endElement();
// t
$objWriter->startElement($prefix . 't');
// $objWriter->writeAttribute('xml:space', 'preserve'); // Excel2010 accepts, Excel2007 complains
$objWriter->writeRawData(PHPExcel_Shared_String::ControlCharacterPHP2OOXML($element->getText()));
$objWriter->endElement();
$objWriter->endElement();
}
}
function sales_summary($data)
{
/**
* PHPExcel
*
* Copyright (C) 2006 - 2014 PHPExcel
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
*
* @category PHPExcel
* @package PHPExcel
* @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
* @version 1.8.0, 2014-03-02
*/
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />');
date_default_timezone_set('Europe/London');
/**
* PHPExcel
*
* Copyright (C) 2006 - 2014 PHPExcel
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
*
* @category PHPExcel
* @package PHPExcel
* @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
* @version 1.8.0, 2014-03-02
*/
/** Error reporting */
error_reporting(E_ALL);
/** Include PHPExcel */
require_once dirname(__FILE__) . '../Classes/PHPExcel.php';
// Create new PHPExcel object
//echo date('H:i:s') , " Create new PHPExcel object" , EOL;
$objPHPExcel = new PHPExcel();
// Set document properties
//echo date('H:i:s') , " Set document properties" , EOL;
$objPHPExcel->getProperties()->setCreator("EPSI")->setLastModifiedBy("MAC")->setTitle("Check Disbursement")->setSubject("Report")->setDescription("Deatiled Report");
// ->setKeywords("Employee DTR Summary")
// ->setCategory("Employee DTR Summary");
// Create a first sheet, representing sales data
$sheet = $objPHPExcel->getActiveSheet();
//HEADER
$objRichText = new PHPExcel_RichText();
$objPayable = $objRichText->createTextRun('EXCELLENT PERFORMANCE SERVICES INC.');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setSize(14);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_BLACK));
$sheet->getCell('A1')->setValue($objRichText);
//DESCRIPTION
$objRichText = new PHPExcel_RichText();
$objPayable = $objRichText->createTextRun('CHECK DISBURSEMENT SUMMARY REPORT');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setSize(12);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_BLACK));
$sheet->getCell('A2')->setValue($objRichText);
//COVERED DATE
$objRichText = new PHPExcel_RichText();
$objPayable = $objRichText->createTextRun('MM/DD/YYYY');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setSize(12);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_BLACK));
$sheet->getCell('A3')->setValue($objRichText);
// Put COntent in the cell. Declare first the cell location, then put the content. -mich
$objPHPExcel->setActiveSheetIndex(0);
$newdata = $data->result_array();
$sheet->setCellValue('A5', 'ACCOUNT TITLE');
$sheet->setCellValue('B5', '');
$sheet->setCellValue('C5', 'DEBIT');
$sheet->setCellValue('D5', 'CREDIT');
// Put data (from the database) in the cell
$counts = 6;
foreach ($data->result() as $key) {
$sheet->setCellValue("A" . $counts . "", $key->account_name);
$sheet->setCellValue("C" . $counts . "", $key->trans_dr);
$sheet->setCellValue("D" . $counts . "", $key->trans_cr);
$counts++;
}
// STYLING CELLS -mich
// FORMAT THE NUMBER IN SPECIFIC CELL
$sheet->getStyle('C6:C30')->getNumberFormat()->setFormatCode('#,##0.00');
$sheet->getStyle('D6:D30')->getNumberFormat()->setFormatCode('#,##0.00');
// MERGE CELLS
$sheet->mergeCells('A1:E1');
$sheet->mergeCells('A2:E2');
$sheet->mergeCells('A3:E3');
// SET COLUMN WIDTH
$sheet->getColumnDimension('A')->setWidth(60);
$sheet->getColumnDimension('B')->setWidth(10);
$sheet->getColumnDimension('C')->setWidth(20);
$sheet->getColumnDimension('D')->setWidth(20);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
/** Include PHPExcel_IOFactory */
require_once dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php';
// Save Excel 2007 file
//echo date('H:i:s') , " Write to Excel2007 format" , EOL;
$callStartTime = microtime(true);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('Reports/Sales_Journal/Summary/sales_summary_' . date("Y-m-d") . '.xls');
$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;
//echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
//echo 'Call time to write Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;
// Echo memory usage
//echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;
// Save Excel 95 file
// echo date('H:i:s') , " Write to Excel5 format" , EOL;
// $callStartTime = microtime(true);
// $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
// $objWriter->save(str_replace('.php', '.xls', __FILE__));
// $callEndTime = microtime(true);
// $callTime = $callEndTime - $callStartTime;
// echo date('H:i:s') , " File written to " , str_replace('.php', '.xls', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
// echo 'Call time to write Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;
// // Echo memory usage
// echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;
// // Echo memory peak usage
// echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;
// // Echo done
// echo date('H:i:s') , " Done writing files" , EOL;
echo 'Files have been created in ', getcwd(), EOL;
}
/**
* Convert to string
*
* @return string
*/
public function __toString()
{
return $this->_text->getPlainText();
}
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E12')->getText()->createTextRun('PHPExcel:');
$objCommentRichText->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getComment('E12')->getText()->createTextRun("\r\n");
$objPHPExcel->getActiveSheet()->getComment('E12')->getText()->createTextRun('Total amount of VAT on the current invoice.');
$objPHPExcel->getActiveSheet()->getComment('E13')->setAuthor('PHPExcel');
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E13')->getText()->createTextRun('PHPExcel:');
$objCommentRichText->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getComment('E13')->getText()->createTextRun("\r\n");
$objPHPExcel->getActiveSheet()->getComment('E13')->getText()->createTextRun('Total amount on the current invoice, including VAT.');
$objPHPExcel->getActiveSheet()->getComment('E13')->setWidth('100pt');
$objPHPExcel->getActiveSheet()->getComment('E13')->setHeight('100pt');
$objPHPExcel->getActiveSheet()->getComment('E13')->setMarginLeft('150pt');
$objPHPExcel->getActiveSheet()->getComment('E13')->getFillColor()->setRGB('EEEEEE');
// Add rich-text string
echo date('H:i:s'), " Add rich-text string", EOL;
$objRichText = new PHPExcel_RichText();
$objRichText->createText('This invoice is ');
$objPayable = $objRichText->createTextRun('payable within thirty days after the end of the month');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText(', unless specified otherwise on the invoice.');
$objPHPExcel->getActiveSheet()->getCell('A18')->setValue($objRichText);
// Merge cells
echo date('H:i:s'), " Merge cells", EOL;
$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
$objPHPExcel->getActiveSheet()->mergeCells('A28:B28');
// Just to test...
$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');
// Just to test...
// Protect cells
private function _parseRichText($is = '')
{
$value = new PHPExcel_RichText();
$value->createText(self::_convertStringEncoding($is, $this->_charSet));
return $value;
}
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'String')->setCellValue('B2', 'Symbols')->setCellValue('C2', '!+&=()~§±æþ');
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'String')->setCellValue('B3', 'UTF-8')->setCellValue('C3', 'Создать MS Excel Книги из PHP скриптов');
$objPHPExcel->getActiveSheet()->setCellValue('A4', 'Number')->setCellValue('B4', 'Integer')->setCellValue('C4', 12);
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Number')->setCellValue('B5', 'Float')->setCellValue('C5', 34.56);
$objPHPExcel->getActiveSheet()->setCellValue('A6', 'Number')->setCellValue('B6', 'Negative')->setCellValue('C6', -7.89);
$objPHPExcel->getActiveSheet()->setCellValue('A7', 'Boolean')->setCellValue('B7', 'True')->setCellValue('C7', true);
$objPHPExcel->getActiveSheet()->setCellValue('A8', 'Boolean')->setCellValue('B8', 'False')->setCellValue('C8', false);
$dateTimeNow = time();
$objPHPExcel->getActiveSheet()->setCellValue('A9', 'Date/Time')->setCellValue('B9', 'Date')->setCellValue('C9', PHPExcel_Shared_Date::PHPToExcel($dateTimeNow));
$objPHPExcel->getActiveSheet()->getStyle('C9')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
$objPHPExcel->getActiveSheet()->setCellValue('A10', 'Date/Time')->setCellValue('B10', 'Time')->setCellValue('C10', PHPExcel_Shared_Date::PHPToExcel($dateTimeNow));
$objPHPExcel->getActiveSheet()->getStyle('C10')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);
$objPHPExcel->getActiveSheet()->setCellValue('A11', 'Date/Time')->setCellValue('B11', 'Date and Time')->setCellValue('C11', PHPExcel_Shared_Date::PHPToExcel($dateTimeNow));
$objPHPExcel->getActiveSheet()->getStyle('C11')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME);
$objPHPExcel->getActiveSheet()->setCellValue('A12', 'NULL')->setCellValue('C12', NULL);
$objRichText = new PHPExcel_RichText();
$objRichText->createText('你好 ');
$objPayable = $objRichText->createTextRun('你 好 吗？');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText(', unless specified otherwise on the invoice.');
$objPHPExcel->getActiveSheet()->setCellValue('A13', 'Rich Text')->setCellValue('C13', $objRichText);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
// Rename worksheet
echo date('H:i:s'), " Rename worksheet", EOL;
$objPHPExcel->getActiveSheet()->setTitle('Datatypes');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Save Excel 2007 file
public function generarExcelInternoCajas($condiciones)
{
set_time_limit(0);
ini_set('memory_limit', '-1');
$GrupoDispoCabDAO = new GrupoDispoCabDAO();
$ProveedorDAO = new ProveedorDAO();
$GrupoDispoCabDAO->setEntityManager($this->getEntityManager());
$ProveedorDAO->setEntityManager($this->getEntityManager());
//----------------Se configura las Etiquetas de Seleccion-----------------
$texto_grupo_dispo_cab_id = 'TODOS';
$texto_color_ventas_id = 'TODOS';
$texto_calidad_variedad_id = 'TODOS';
$inventario_id = $condiciones['inventario_id'];
if (!empty($condiciones['grupo_dispo_cab_id'])) {
$texto_grupo_dispo_cab_id = $condiciones['grupo_dispo_cab_id'];
}
//end if
if (!empty($condiciones['color_ventas_id'])) {
$texto_color_ventas_id = $condiciones['color_ventas_id'];
}
//end if
if (!empty($condiciones['calidad_variedad_id'])) {
$texto_calidad_variedad_id = $condiciones['calidad_variedad_id'];
}
//end if
//----------------Se inicia la configuracion del PHPExcel-----------------
$PHPExcelApp = new PHPExcelApp();
$objPHPExcel = new \PHPExcel();
// Set document properties
$PHPExcelApp->setUserName('');
$PHPExcelApp->setMetaDataDocument($objPHPExcel);
$objPHPExcel->setActiveSheetIndex(0);
//Configura el tamaño del Papel
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(\PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(\PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
//Se establece la escala de la pagina
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);
//Se establece los margenes de la pagina
$objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.1);
$objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.1);
$objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.1);
$objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.1);
//Consulta la lista de registros
$condiciones['opcion_dispo'] = 'BUNCH_TODOS';
$result_dispo = $this->listadoDisponibilidadPorProveedor($condiciones, true);
//Convertir Dispo en Cajas
$result_HB = $this->transformarDispoEnCajas($inventario_id, 'HB', $result_dispo);
$result_QB = $this->transformarDispoEnCajas($inventario_id, 'QB', $result_dispo);
//Consulta las fincas
$result_fincas = $ProveedorDAO->consultarTodos();
$estilo_titulo = $PHPExcelApp::STYLE_ARRAY_TITULO01;
$estilo_columna = $PHPExcelApp::STYLE_ARRAY_COLUMNA01;
$indice_hoja = -1;
$arr_tipo_caja[] = array('tipo_caja_id' => 'HB');
$arr_tipo_caja[] = array('tipo_caja_id' => 'QB');
foreach ($result_fincas as $reg_finca) {
foreach ($arr_tipo_caja as $reg_tipo_caja) {
switch ($reg_tipo_caja['tipo_caja_id']) {
case 'HB':
$result_procesar = $result_HB;
break;
case 'QB':
$result_procesar = $result_QB;
break;
}
//end switch
/*----------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------*/
$indice_hoja++;
if ($indice_hoja > 0) {
$objPHPExcel->createSheet($indice_hoja);
$estilo_titulo = $PHPExcelApp::STYLE_ARRAY_TITULO02;
$estilo_columna = $PHPExcelApp::STYLE_ARRAY_COLUMNA02;
}
//end if
$objPHPExcel->setActiveSheetIndex($indice_hoja);
//------------------------------Registra la cabecera--------------------------------
$row = 1;
$col_ini = $PHPExcelApp->getNameFromNumber(0);
$col_fin = $PHPExcelApp->getNameFromNumber(11);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, "Disponibilidad Por Grupo");
$objPHPExcel->getActiveSheet()->mergeCells($col_ini . $row . ':' . $col_fin . $row);
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA));
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//------------------------------Registra criterios linea 1--------------------------
$row++;
$col_ini = $PHPExcelApp->getNameFromNumber(0);
//$col_fin = $PHPExcelApp->getNameFromNumber(11);
$objRichText = new \PHPExcel_RichText();
$objRichText->createText('');
$objInventario = $objRichText->createTextRun(' Grupo: ');
$objInventario->getFont()->setBold(true);
$objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText($texto_grupo_dispo_cab_id);
$objInventario = $objRichText->createTextRun(' Color: ');
$objInventario->getFont()->setBold(true);
$objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText($texto_color_ventas_id);
$objInventario = $objRichText->createTextRun(' Calidad: ');
$objInventario->getFont()->setBold(true);
$objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText($texto_calidad_variedad_id);
$objPHPExcel->getActiveSheet()->getCell($col_ini . $row)->setValue($objRichText);
$objPHPExcel->getActiveSheet()->mergeCells($col_ini . $row . ':' . $col_fin . $row);
//------------------------------ Registro de Fecha de Generacion --------------------------------
$row++;
$col_ini = $PHPExcelApp->getNameFromNumber(0);
//$col_fin = $PHPExcelApp->getNameFromNumber(11);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, "Generado: " . \Application\Classes\Fecha::getFechaHoraActualServidor());
$objPHPExcel->getActiveSheet()->mergeCells($col_ini . $row . ':' . $col_fin . $row);
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA));
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
//---------------------------IMPRIME TITULO DE COLUMNA-----------------------------
$row++;
$row_detalle_ini = $row;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, "Nro");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, "Id");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, "Variedad");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, "Color");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, "40");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, "50");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $row, "60");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $row, "70");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $row, "80");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $row, "90");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $row, "100");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $row, "110");
//----------------------AUTO DIMENSIONAR CELDAS DE ACUERDO AL CONTENIDO---------------
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(0)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(1)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(2)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(3)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(4)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(5)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(6)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(7)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(8)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(9)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(10)->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(11)->setWidth(6);
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($estilo_titulo));
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_WHITE);
// $objPHPExcel->getActiveSheet()->getStyle($col_ini.$row.':'.$col_fin.$row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA));
//----------------------CONSULTA LOS REGISTROS A EXPORTAR---------------
//$result = $this->listado($condiciones);
$cont_linea = 0;
$row_detalle_info_ini = $row + 1;
foreach ($result_procesar as $reg) {
if (!empty($reg['variedad_id'])) {
$reg['variedad_id'] = trim($reg['variedad_id']);
}
//end if
if (!empty($reg['color_ventas_nombre'])) {
$reg['color_ventas_nombre'] = trim($reg['color_ventas_nombre']);
}
//end if
$cont_linea++;
$row = $row + 1;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $cont_linea);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, $reg['variedad_id']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, $reg['variedad']);
if ($reg['tallos_x_bunch'] == 25) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, $reg['variedad']);
} else {
$objRichText = new \PHPExcel_RichText();
$objRichText->createText($reg['variedad']);
$objInventario = $objRichText->createTextRun(' (' . $reg['tallos_x_bunch'] . ')');
$objInventario->getFont()->setBold(true);
$objInventario->getFont()->setItalic(true);
$col_variedad = $PHPExcelApp->getNameFromNumber(2);
$objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\Application\Classes\PHPExcelApp::COLOR_ORANGE));
$objPHPExcel->getActiveSheet()->getCell($col_variedad . $row)->setValue($objRichText);
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, $reg['variedad'] );
}
//end if
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, $reg['color_ventas_nombre']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, $reg['40']['cajas']['fincas'][$reg_finca['id']]['total']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, $reg['50']['cajas']['fincas'][$reg_finca['id']]['total']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $row, $reg['60']['cajas']['fincas'][$reg_finca['id']]['total']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $row, $reg['70']['cajas']['fincas'][$reg_finca['id']]['total']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $row, $reg['80']['cajas']['fincas'][$reg_finca['id']]['total']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $row, $reg['90']['cajas']['fincas'][$reg_finca['id']]['total']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $row, $reg['100']['cajas']['fincas'][$reg_finca['id']]['total']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $row, $reg['100']['cajas']['fincas'][$reg_finca['id']]['total']);
}
// end foreach
//Formato de Numeros
$col_ini = $PHPExcelApp->getNameFromNumber(4);
$col_fin = $PHPExcelApp->getNameFromNumber(11);
$row_detalle_info_ini = $row_detalle_ini + 1;
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row_detalle_info_ini . ':' . $col_fin . $row)->getNumberFormat()->setFormatCode("#,###");
//Margenes
$col_ini = $PHPExcelApp->getNameFromNumber(0);
$col_fin = $PHPExcelApp->getNameFromNumber(11);
$objPHPExcel->getActiveSheet()->getStyle($col_ini . $row_detalle_info_ini . ":" . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_BORDE_TODO));
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle($reg_finca['id'] . ' - ' . $reg_tipo_caja['tipo_caja_id']);
/*----------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------*/
}
//end foreach
}
//end foreach($result_fincas as $reg_finca)
$PHPExcelApp->save($objPHPExcel, $PHPExcelApp::FORMAT_EXCEL_2007, "Dispo Grupo.xlsx");
}
// offset: 3; size: var; list of ($nc + 1) * ($nr + 1) constant values
$matrixChunks = array();
for ($r = 1; $r <= $nr + 1; ++$r) {
$items = array();
for ($c = 1; $c <= $nc + 1; ++$c) {
$constant = self::_readBIFF8Constant($arrayData);
function setForSpec($obj_xl, $cel, $t1, $v1, $t2, $v2, $t3, $v3)
{
$objRichText = new PHPExcel_RichText();
$objBold = $objRichText->createTextRun("{$t1}\n");
$objBold->getFont()->setBold(true);
$objRichText->createText($v1);
$objBold2 = $objRichText->createTextRun("{$t2}\n");
$objBold2->getFont()->setBold(true);
$objRichText->createText($v2);
$objBold3 = $objRichText->createTextRun("{$t3}\n");
$objBold3->getFont()->setBold(true);
// $objRichText->createText($v3);
$objBold3b = $objRichText->createTextRun("{$v3}\n");
if (!$t3) {
$objBold3b->getFont()->setBold(true);
}
$obj_xl->getCell($cel)->setValue($objRichText);
}
/**
* Loads PHPExcel from file
*
* @param string $pFilename
* @throws Exception
*/
public function load($pFilename)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
// Initialisations
$excel = new PHPExcel();
$excel->removeSheetByIndex(0);
// Use ParseXL for the hard work.
$this->_ole = new PHPExcel_Shared_OLERead();
$this->_rowoffset = $this->_coloffset = 0;
$this->_defaultEncoding = 'ISO-8859-1';
$this->_encoderFunction = function_exists('mb_convert_encoding') ? 'mb_convert_encoding' : 'iconv';
// get excel data
$res = $this->_ole->read($pFilename);
// oops, something goes wrong (Darko Miljanovic)
if ($res === false) {
// check error code
if ($this->_ole->error == 1) {
// bad file
throw new Exception('The filename ' . $pFilename . ' is not readable');
} elseif ($this->_ole->error == 2) {
throw new Exception('The filename ' . $pFilename . ' is not recognised as an Excel file');
}
// check other error codes here (eg bad fileformat, etc...)
}
$this->_data = $this->_ole->getWorkBook();
$this->_pos = 0;
/**
* PARSE WORKBOOK
*
**/
$pos = 0;
$code = ord($this->_data[$pos]) | ord($this->_data[$pos + 1]) << 8;
$length = ord($this->_data[$pos + 2]) | ord($this->_data[$pos + 3]) << 8;
$version = ord($this->_data[$pos + 4]) | ord($this->_data[$pos + 5]) << 8;
$substreamType = ord($this->_data[$pos + 6]) | ord($this->_data[$pos + 7]) << 8;
if ($version != self::XLS_BIFF8 && $version != self::XLS_BIFF7) {
return false;
}
if ($substreamType != self::XLS_WorkbookGlobals) {
return false;
}
$pos += $length + 4;
$code = ord($this->_data[$pos]) | ord($this->_data[$pos + 1]) << 8;
$length = ord($this->_data[$pos + 2]) | ord($this->_data[$pos + 3]) << 8;
$recordData = substr($this->_data, $pos + 4, $length);
while ($code != self::XLS_Type_EOF) {
switch ($code) {
case self::XLS_Type_SST:
/**
* SST - Shared String Table
*
* This record contains a list of all strings used anywhere
* in the workbook. Each string occurs only once. The
* workbook uses indexes into the list to reference the
* strings.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
**/
// offset: 0; size: 4; total number of strings
// offset: 4; size: 4; number of unique strings
$spos = $pos + 4;
$limitpos = $spos + $length;
$uniqueStrings = $this->_GetInt4d($this->_data, $spos + 4);
$spos += 8;
// loop through the Unicode strings (16-bit length)
for ($i = 0; $i < $uniqueStrings; $i++) {
if ($spos == $limitpos) {
// then we have reached end of SST record data
$opcode = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$conlength = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
if ($opcode != self::XLS_Type_CONTINUE) {
// broken file, something is wrong
return -1;
}
$spos += 4;
$limitpos = $spos + $conlength;
}
// Read in the number of characters in the Unicode string
$numChars = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$spos += 2;
// option flags
$optionFlags = ord($this->_data[$spos]);
$spos++;
// bit: 0; mask: 0x01; 0 = compressed; 1 = uncompressed
$asciiEncoding = ($optionFlags & 0x1) == 0;
// bit: 2; mask: 0x02; 0 = ordinary; 1 = Asian phonetic
$extendedString = ($optionFlags & 0x4) != 0;
// Asian phonetic
// bit: 3; mask: 0x03; 0 = ordinary; 1 = Rich-Text
$richString = ($optionFlags & 0x8) != 0;
if ($richString) {
// Read in the crun
// number of Rich-Text formatting runs
$formattingRuns = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$spos += 2;
}
if ($extendedString) {
// size of Asian phonetic setting
$extendedRunLength = $this->_GetInt4d($this->_data, $spos);
$spos += 4;
}
// read in the characters
$len = $asciiEncoding ? $numChars : $numChars * 2;
if ($spos + $len < $limitpos) {
$retstr = substr($this->_data, $spos, $len);
$spos += $len;
} else {
// found countinue record
$retstr = substr($this->_data, $spos, $limitpos - $spos);
$bytesRead = $limitpos - $spos;
// remaining characters in Unicode string
$charsLeft = $numChars - ($asciiEncoding ? $bytesRead : $bytesRead / 2);
$spos = $limitpos;
// keep reading the characters
while ($charsLeft > 0) {
// record data
$opcode = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
// length of continue record data
$conlength = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
if ($opcode != self::XLS_Type_CONTINUE) {
// broken file, something is wrong
return -1;
}
$spos += 4;
$limitpos = $spos + $conlength;
// option flags are repeated when Unicode string is split by a continue record
// OpenOffice.org documentation 5.21
$option = ord($this->_data[$spos]);
$spos += 1;
if ($asciiEncoding && $option == 0) {
// 1st fragment compressed
// this fragment compressed
$len = min($charsLeft, $limitpos - $spos);
$retstr .= substr($this->_data, $spos, $len);
$charsLeft -= $len;
$asciiEncoding = true;
} elseif (!$asciiEncoding && $option != 0) {
// 1st fragment uncompressed
// this fragment uncompressed
$len = min($charsLeft * 2, $limitpos - $spos);
$retstr .= substr($this->_data, $spos, $len);
$charsLeft -= $len / 2;
$asciiEncoding = false;
} elseif (!$asciiEncoding && $option == 0) {
// 1st fragment uncompressed
// this fragment compressed
$len = min($charsLeft, $limitpos - $spos);
for ($j = 0; $j < $len; $j++) {
$retstr .= $this->_data[$spos + $j] . chr(0);
}
$charsLeft -= $len;
$asciiEncoding = false;
} else {
// 1st fragment compressed
// this fragment uncompressed
$newstr = '';
for ($j = 0; $j < strlen($retstr); $j++) {
$newstr = $retstr[$j] . chr(0);
}
$retstr = $newstr;
$len = min($charsLeft * 2, $limitpos - $spos);
$retstr .= substr($this->_data, $spos, $len);
$charsLeft -= $len / 2;
$asciiEncoding = false;
}
$spos += $len;
}
}
//$retstr = ($asciiEncoding) ?
// $retstr : $this->_encodeUTF16($retstr);
// convert string according codepage and BIFF version
if ($version == self::XLS_BIFF8) {
$retstr = $this->_encodeUTF16($retstr, $asciiEncoding);
} else {
// SST only occurs in BIFF8, so why this part?
$retstr = $this->_decodeCodepage($retstr);
}
$fmtRuns = array();
if ($richString) {
// list of formatting runs
for ($j = 0; $j < $formattingRuns; $j++) {
// first formatted character; zero-based
$charPos = $this->_getInt2d($this->_data, $spos + $j * 4);
// index to font record
$fontIndex = $this->_getInt2d($this->_data, $spos + 2 + $j * 4);
$fmtRuns[] = array('charPos' => $charPos, 'fontIndex' => $fontIndex);
}
$spos += 4 * $formattingRuns;
}
if ($extendedString) {
// For Asian phonetic settings, we skip the extended string data
$spos += $extendedRunLength;
}
$this->_sst[] = array('value' => $retstr, 'fmtRuns' => $fmtRuns);
}
break;
case self::XLS_Type_FILEPASS:
/**
* SHEETPROTECTION
*
* This record is part of the File Protection Block. It
* contains information about the read/write password of the
* file. All record contents following this record will be
* encrypted.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
return false;
break;
case self::XLS_Type_EXTERNALBOOK:
break;
case self::XLS_Type_EXTSHEET:
// external sheet references provided for named cells
if ($version == self::XLS_BIFF8) {
$xpos = $pos + 4;
$xcnt = ord($this->_data[$xpos]) | ord($this->_data[$xpos + 1]) << 8;
for ($x = 0; $x < $xcnt; $x++) {
$this->_extshref[$x] = ord($this->_data[$xpos + 4 + 6 * $x]) | ord($this->_data[$xpos + 5 + 6 * $x]) << 8;
}
}
// this if statement is going to replace the above one later
if ($version == self::XLS_BIFF8) {
// offset: 0; size: 2; number of following ref structures
$nm = $this->_GetInt2d($recordData, 0);
for ($i = 0; $i < $nm; $i++) {
$this->_ref[] = array('externalBookIndex' => $this->_getInt2d($recordData, 2 + 6 * $i), 'firstSheetIndex' => $this->_getInt2d($recordData, 4 + 6 * $i), 'lastSheetIndex' => $this->_getInt2d($recordData, 6 + 6 * $i));
}
}
break;
case self::XLS_Type_NAME:
/**
* DEFINEDNAME
*
* This record is part of a Link Table. It contains the name
* and the token array of an internal defined name. Token
* arrays of defined names contain tokens with aberrant
* token classes.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
// retrieves named cells
$npos = $pos + 4;
$opts = ord($this->_data[$npos]) | ord($this->_data[$npos + 1]) << 8;
$nlen = ord($this->_data[$npos + 3]);
$flen = ord($this->_data[$npos + 4]) | ord($this->_data[$npos + 5]) << 8;
$fpos = $npos + 14 + 1 + $nlen;
$nstr = substr($this->_data, $npos + 15, $nlen);
$ftoken = ord($this->_data[$fpos]);
if ($ftoken == 58 && $opts == 0 && $flen == 7) {
$xref = ord($this->_data[$fpos + 1]) | ord($this->_data[$fpos + 2]) << 8;
$frow = ord($this->_data[$fpos + 3]) | ord($this->_data[$fpos + 4]) << 8;
$fcol = ord($this->_data[$fpos + 5]);
if (array_key_exists($xref, $this->_extshref)) {
$fsheet = $this->_extshref[$xref];
} else {
$fsheet = '';
}
$this->_namedcells[$nstr] = array('sheet' => $fsheet, 'row' => $frow, 'column' => $fcol);
}
break;
case self::XLS_Type_FORMAT:
/**
* FORMAT
*
* This record contains information about a number format.
* All FORMAT records occur together in a sequential list.
*
* In BIFF2-BIFF4 other records referencing a FORMAT record
* contain a zero-based index into this list. From BIFF5 on
* the FORMAT record contains the index itself that will be
* used by other records.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
//$indexCode = ord($this->_data[$pos + 4]) | ord($this->_data[$pos + 5]) << 8;
$indexCode = $this->_GetInt2d($recordData, 0);
/*
if ($version == self::XLS_BIFF8) {
*/
$formatString = $this->_readUnicodeStringLong(substr($recordData, 2));
/*
} else {
$numchars = ord($this->_data[$pos + 6]);
$formatString = substr($this->_data, $pos + 7, $numchars*2);
}
*/
$this->_formatRecords[$indexCode] = $formatString;
// now also stored in array _format[]
// _formatRecords[] will be removed from code later
$this->_numberFormat[$indexCode] = $formatString;
break;
case self::XLS_Type_FONT:
/**
* FONT
*/
$this->_font[] = $this->_readFont($recordData);
break;
case self::XLS_Type_XF:
/**
* XF - Extended Format
*
* This record contains formatting information for cells,
* rows, columns or styles.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$indexCode = ord($this->_data[$pos + 6]) | ord($this->_data[$pos + 7]) << 8;
if (array_key_exists($indexCode, $this->_dateFormats)) {
$this->_formatRecords['xfrecords'][] = array('type' => 'date', 'format' => $this->_dateFormats[$indexCode], 'code' => $indexCode);
} elseif (array_key_exists($indexCode, $this->_percentFormats)) {
$this->_formatRecords['xfrecords'][] = array('type' => 'percent', 'format' => $this->_percentFormats[$indexCode], 'code' => $indexCode);
} elseif (array_key_exists($indexCode, $this->_numberFormats)) {
$this->_formatRecords['xfrecords'][] = array('type' => 'number', 'format' => $this->_numberFormats[$indexCode], 'code' => $indexCode);
} else {
if ($indexCode > 0 && isset($this->_formatRecords[$indexCode])) {
// custom formats...
$formatstr = $this->_formatRecords[$indexCode];
if ($formatstr) {
// dvc: reg exp changed to custom date/time format chars
if (preg_match("/^[hmsdy]/i", $formatstr)) {
// custom datetime format
// dvc: convert Excel formats to PHP date formats
// first remove escapes related to non-format characters
$formatstr = str_replace('\\', '', $formatstr);
// 4-digit year
$formatstr = str_replace('yyyy', 'Y', $formatstr);
// 2-digit year
$formatstr = str_replace('yy', 'y', $formatstr);
// first letter of month - no php equivalent
$formatstr = str_replace('mmmmm', 'M', $formatstr);
// full month name
$formatstr = str_replace('mmmm', 'F', $formatstr);
// short month name
$formatstr = str_replace('mmm', 'M', $formatstr);
// mm is minutes if time or month w/leading zero
$formatstr = str_replace(':mm', ':i', $formatstr);
// tmp place holder
$formatstr = str_replace('mm', 'x', $formatstr);
// month no leading zero
$formatstr = str_replace('m', 'n', $formatstr);
// month leading zero
$formatstr = str_replace('x', 'm', $formatstr);
// 12-hour suffix
$formatstr = str_replace('AM/PM', 'A', $formatstr);
// tmp place holder
$formatstr = str_replace('dd', 'x', $formatstr);
// days no leading zero
$formatstr = str_replace('d', 'j', $formatstr);
// days leading zero
$formatstr = str_replace('x', 'd', $formatstr);
// seconds
$formatstr = str_replace('ss', 's', $formatstr);
// fractional seconds - no php equivalent
$formatstr = str_replace('.S', '', $formatstr);
if (!strpos($formatstr, 'A')) {
// 24-hour format
$formatstr = str_replace('h', 'H', $formatstr);
}
// user defined flag symbol????
$formatstr = str_replace(';@', '', $formatstr);
$this->_formatRecords['xfrecords'][] = array('type' => 'date', 'format' => $formatstr, 'code' => $indexCode);
} else {
if (preg_match('/%$/', $formatstr)) {
// % number format
if (preg_match('/\\.[#0]+/i', $formatstr, $m)) {
$s = substr($m[0], 0, 1) . (strlen($m[0]) - 1);
$formatstr = str_replace($m[0], $s, $formatstr);
}
if (preg_match('/^[#0]+/', $formatstr, $m)) {
$formatstr = str_replace($m[0], strlen($m[0]), $formatstr);
}
$formatstr = '%' . str_replace('%', "f%%", $formatstr);
$this->_formatRecords['xfrecords'][] = array('type' => 'percent', 'format' => $formatstr, 'code' => $indexCode);
} else {
// dvc: changed to add format to unknown for debug
$this->_formatRecords['xfrecords'][] = array('type' => 'other', 'format' => $this->_defaultFormat, 'code' => $indexCode);
}
}
}
} else {
// dvc: changed to add format to unknown for debug
if (isset($this->_formatRecords[$indexCode])) {
$formatstr = $this->_formatRecords[$indexCode];
$type = 'undefined';
} else {
$formatstr = $this->_defaultFormat;
$type = 'default';
}
$this->_formatRecords['xfrecords'][] = array('type' => $type, 'format' => $formatstr, 'code' => $indexCode);
}
}
// store styles in xf array
$this->_xf[] = $this->_readBIFF8Style($recordData);
break;
case self::XLS_Type_NINETEENFOUR:
/**
* DATEMODE
*
* This record specifies the base date for displaying date
* values. All dates are stored as count of days past this
* base date. In BIFF2-BIFF4 this record is part of the
* Calculation Settings Block. In BIFF5-BIFF8 it is
* stored in the Workbook Globals Substream.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$this->_nineteenFour = ord($this->_data[$pos + 4]) == 1;
/*
if (ord($this->_data[$pos + 4]) == 1) {
PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904);
} else {
PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900);
}
*/
break;
case self::XLS_Type_BOUNDSHEET:
/**
* SHEET
*
* This record is located in the Workbook Globals
* Substream and represents a sheet inside the workbook.
* One SHEET record is written for each sheet. It stores the
* sheet name and a stream offset to the BOF record of the
* respective Sheet Substream within the Workbook Stream.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$rec_offset = $this->_GetInt4d($this->_data, $pos + 4);
$rec_typeFlag = ord($this->_data[$pos + 8]);
$rec_visibilityFlag = ord($this->_data[$pos + 9]);
$rec_length = ord($this->_data[$pos + 10]);
if ($version == self::XLS_BIFF8) {
$compressedUTF16 = (ord($this->_data[$pos + 11]) & 0x1) == 0;
$rec_length = $compressedUTF16 ? $rec_length : $rec_length * 2;
$rec_name = $this->_encodeUTF16(substr($this->_data, $pos + 12, $rec_length), $compressedUTF16);
} elseif ($version == self::XLS_BIFF7) {
$rec_name = substr($this->_data, $pos + 11, $rec_length);
}
$this->_boundsheets[] = array('name' => $rec_name, 'offset' => $rec_offset);
break;
case self::XLS_Type_CODEPAGE:
/**
* CODEPAGE
*
* This record stores the text encoding used to write byte
* strings, stored as MS Windows code page identifier.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$codepage = $this->_GetInt2d($this->_data, $pos + 4);
switch ($codepage) {
case 367:
// ASCII
$this->_codepage = "ASCII";
break;
case 437:
//OEM US
$this->_codepage = "CP437";
break;
case 720:
//OEM Arabic
// currently not supported by libiconv
$this->_codepage = "";
break;
case 737:
//OEM Greek
$this->_codepage = "CP737";
break;
case 775:
//OEM Baltic
$this->_codepage = "CP775";
break;
case 850:
//OEM Latin I
$this->_codepage = "CP850";
break;
case 852:
//OEM Latin II (Central European)
$this->_codepage = "CP852";
break;
case 855:
//OEM Cyrillic
$this->_codepage = "CP855";
break;
case 857:
//OEM Turkish
$this->_codepage = "CP857";
break;
case 858:
//OEM Multilingual Latin I with Euro
$this->_codepage = "CP858";
break;
case 860:
//OEM Portugese
$this->_codepage = "CP860";
break;
case 861:
//OEM Icelandic
$this->_codepage = "CP861";
break;
case 862:
//OEM Hebrew
$this->_codepage = "CP862";
break;
case 863:
//OEM Canadian (French)
$this->_codepage = "CP863";
break;
case 864:
//OEM Arabic
$this->_codepage = "CP864";
break;
case 865:
//OEM Nordic
$this->_codepage = "CP865";
break;
case 866:
//OEM Cyrillic (Russian)
$this->_codepage = "CP866";
break;
case 869:
//OEM Greek (Modern)
$this->_codepage = "CP869";
break;
case 874:
//ANSI Thai
$this->_codepage = "CP874";
break;
case 932:
//ANSI Japanese Shift-JIS
$this->_codepage = "CP932";
break;
case 936:
//ANSI Chinese Simplified GBK
$this->_codepage = "CP936";
break;
case 949:
//ANSI Korean (Wansung)
$this->_codepage = "CP949";
break;
case 950:
//ANSI Chinese Traditional BIG5
$this->_codepage = "CP950";
break;
case 1200:
//UTF-16 (BIFF8)
$this->_codepage = "UTF-16LE";
break;
case 1250:
// ANSI Latin II (Central European)
$this->_codepage = "CP1250";
break;
case 1251:
//ANSI Cyrillic
$this->_codepage = "CP1251";
break;
case 1252:
//ANSI Latin I (BIFF4-BIFF7)
$this->_codepage = "CP1252";
break;
case 1253:
//ANSI Greek
$this->_codepage = "CP1253";
break;
case 1254:
//ANSI Turkish
$this->_codepage = "CP1254";
break;
case 1255:
//ANSI Hebrew
$this->_codepage = "CP1255";
break;
case 1256:
//ANSI Arabic
$this->_codepage = "CP1256";
break;
case 1257:
//ANSI Baltic
$this->_codepage = "CP1257";
break;
case 1258:
//ANSI Vietnamese
$this->_codepage = "CP1258";
break;
case 1361:
//ANSI Korean (Johab)
$this->_codepage = "CP1361";
break;
case 10000:
//Apple Roman
// currently not supported by libiconv
$this->_codepage = "";
break;
case 32768:
//Apple Roman
// currently not supported by libiconv
$this->_codepage = "";
break;
case 32769:
//ANSI Latin I (BIFF2-BIFF3)
// currently not supported by libiconv
$this->_codepage = "";
break;
}
break;
}
$pos += $length + 4;
$code = ord($this->_data[$pos]) | ord($this->_data[$pos + 1]) << 8;
$length = ord($this->_data[$pos + 2]) | ord($this->_data[$pos + 3]) << 8;
$recordData = substr($this->_data, $pos + 4, $length);
}
/**
*
* PARSE THE INDIVIDUAL SHEETS
*
**/
foreach ($this->_boundsheets as $key => $val) {
// add sheet to PHPExcel object
$sheet = $excel->createSheet();
$sheet->setTitle((string) $val['name']);
$this->_sn = $key;
$spos = $val['offset'];
$cont = true;
// read BOF
$code = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$length = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$version = ord($this->_data[$spos + 4]) | ord($this->_data[$spos + 5]) << 8;
$substreamType = ord($this->_data[$spos + 6]) | ord($this->_data[$spos + 7]) << 8;
if ($version != self::XLS_BIFF8 && $version != self::XLS_BIFF7) {
return -1;
}
if ($substreamType != self::XLS_Worksheet) {
return -2;
}
$spos += $length + 4;
while ($cont) {
$lowcode = ord($this->_data[$spos]);
if ($lowcode == self::XLS_Type_EOF) {
break;
}
$code = $lowcode | ord($this->_data[$spos + 1]) << 8;
$length = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$recordData = substr($this->_data, $spos + 4, $length);
$spos += 4;
$this->_sheets[$this->_sn]['maxrow'] = $this->_rowoffset - 1;
$this->_sheets[$this->_sn]['maxcol'] = $this->_coloffset - 1;
unset($this->_rectype);
unset($this->_formula);
unset($this->_formula_result);
$this->_multiplier = 1;
// need for format with %
switch ($code) {
case self::XLS_Type_DIMENSION:
/**
* DIMENSION
*
* This record contains the range address of the used area
* in the current sheet.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
if (!isset($this->_numRows)) {
if ($length == 10 || $version == self::XLS_BIFF7) {
$this->_sheets[$this->_sn]['numRows'] = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$this->_sheets[$this->_sn]['numCols'] = ord($this->_data[$spos + 6]) | ord($this->_data[$spos + 7]) << 8;
} else {
$this->_sheets[$this->_sn]['numRows'] = ord($this->_data[$spos + 4]) | ord($this->_data[$spos + 5]) << 8;
$this->_sheets[$this->_sn]['numCols'] = ord($this->_data[$spos + 10]) | ord($this->_data[$spos + 11]) << 8;
}
}
break;
case self::XLS_Type_MERGEDCELLS:
/**
* MERGEDCELLS
*
* This record contains the addresses of merged cell ranges
* in the current sheet.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$cellRanges = $this->_GetInt2d($this->_data, $spos);
for ($i = 0; $i < $cellRanges; $i++) {
$fr = $this->_GetInt2d($this->_data, $spos + 8 * $i + 2);
// first row
$lr = $this->_GetInt2d($this->_data, $spos + 8 * $i + 4);
// last row
$fc = $this->_GetInt2d($this->_data, $spos + 8 * $i + 6);
// first column
$lc = $this->_GetInt2d($this->_data, $spos + 8 * $i + 8);
// last column
// this part no longer needed, instead apply cell merge on PHPExcel worksheet object
/*
if ($lr - $fr > 0) {
$this->_sheets[$this->_sn]['cellsInfo'][$fr + 1][$fc + 1]['rowspan'] = $lr - $fr + 1;
}
if ($lc - $fc > 0) {
$this->_sheets[$this->_sn]['cellsInfo'][$fr + 1][$fc + 1]['colspan'] = $lc - $fc + 1;
}
*/
$sheet->mergeCellsByColumnAndRow($fc, $fr + 1, $lc, $lr + 1);
}
}
break;
case self::XLS_Type_RK:
case self::XLS_Type_RK2:
/**
* RK
*
* This record represents a cell that contains an RK value
* (encoded integer or floating-point value). If a
* floating-point value cannot be encoded to an RK value,
* a NUMBER record will be written. This record replaces the
* record INTEGER written in BIFF2.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$rknum = $this->_GetInt4d($this->_data, $spos + 6);
$numValue = $this->_GetIEEE754($rknum);
/*
if ($this->_isDate($spos)) {
list($string, $raw) = $this->_createDate($numValue);
} else {
$raw = $numValue;
if (isset($this->_columnsFormat[$column + 1])){
$this->_curformat = $this->_columnsFormat[$column + 1];
}
$string = sprintf($this->_curformat,$numValue*$this->_multiplier);
}
*/
// offset 4; size: 2; index to XF record
$xfindex = $this->_getInt2d($recordData, 4);
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
if (PHPExcel_Shared_Date::isDateTimeFormatCode($this->_xf[$xfindex]['numberformat']['code'])) {
$numValue = (int) PHPExcel_Shared_Date::ExcelToPHP($numValue);
}
}
//$this->_addcell($row, $column, $string, $raw);
//$sheet->setCellValueByColumnAndRow($column, $row + 1, $string);
$sheet->setCellValueByColumnAndRow($column, $row + 1, $numValue);
break;
case self::XLS_Type_LABELSST:
/**
* LABELSST
*
* This record represents a cell that contains a string. It
* replaces the LABEL record and RSTRING record used in
* BIFF2-BIFF5.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$xfindex = ord($this->_data[$spos + 4]) | ord($this->_data[$spos + 5]) << 8;
$index = $this->_GetInt4d($this->_data, $spos + 6);
//$this->_addcell($row, $column, $this->_sst[$index]);
if ($fmtRuns = $this->_sst[$index]['fmtRuns']) {
// then we have rich text
$richText = new PHPExcel_RichText($sheet->getCellByColumnAndRow($column, $row + 1));
$charPos = 0;
for ($i = 0; $i <= count($this->_sst[$index]['fmtRuns']); $i++) {
if (isset($fmtRuns[$i])) {
$text = mb_substr($this->_sst[$index]['value'], $charPos, $fmtRuns[$i]['charPos'] - $charPos, 'UTF-8');
$charPos = $fmtRuns[$i]['charPos'];
} else {
$text = mb_substr($this->_sst[$index]['value'], $charPos, mb_strlen($this->_sst[$index]['value']), 'UTF-8');
}
if (mb_strlen($text) > 0) {
$textRun = $richText->createTextRun($text);
if (isset($fmtRuns[$i - 1])) {
if ($fmtRuns[$i - 1]['fontIndex'] < 4) {
$fontIndex = $fmtRuns[$i - 1]['fontIndex'];
} else {
// this has to do with that index 4 is omitted in all BIFF versions for some strange reason
// check the OpenOffice documentation of the FONT record
$fontIndex = $fmtRuns[$i - 1]['fontIndex'] - 1;
}
$textRun->getFont()->applyFromArray($this->_font[$fontIndex]);
}
}
}
} else {
$sheet->setCellValueByColumnAndRow($column, $row + 1, $this->_sst[$index]['value']);
}
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
}
break;
case self::XLS_Type_MULRK:
/**
* MULRK - Multiple RK
*
* This record represents a cell range containing RK value
* cells. All cells are located in the same row.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$colFirst = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$colLast = ord($this->_data[$spos + $length - 2]) | ord($this->_data[$spos + $length - 1]) << 8;
$columns = $colLast - $colFirst + 1;
$tmppos = $spos + 4;
for ($i = 0; $i < $columns; $i++) {
// offset: 0; size: 2; index to XF record
$xfindex = $this->_getInt2d($recordData, 4 + 6 * $i);
// offset: 2; size: 4; RK value
$numValue = $this->_GetIEEE754($this->_GetInt4d($this->_data, $tmppos + 2));
/*
if ($this->_isDate($tmppos-4)) {
list($string, $raw) = $this->_createDate($numValue);
} else {
$raw = $numValue;
if (isset($this->_columnsFormat[$colFirst + $i + 1])){
$this->_curformat = $this->_columnsFormat[$colFirst+ $i + 1];
}
$string = sprintf($this->_curformat, $numValue *
$this->_multiplier);
}
*/
//$this->_addcell($row, $colFirst + $i, $string, $raw);
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($colFirst + $i, $row + 1)->applyFromArray($this->_xf[$xfindex]);
if (PHPExcel_Shared_Date::isDateTimeFormatCode($this->_xf[$xfindex]['numberformat']['code'])) {
$numValue = (int) PHPExcel_Shared_Date::ExcelToPHP($numValue);
}
}
//$sheet->setCellValueByColumnAndRow($colFirst + $i, $row + 1, $string);
$sheet->setCellValueByColumnAndRow($colFirst + $i, $row + 1, $numValue);
$tmppos += 6;
}
break;
case self::XLS_Type_NUMBER:
/**
* NUMBER
*
* This record represents a cell that contains a
* floating-point value.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
// offset 4; size: 2; index to XF record
$xfindex = $this->_GetInt2d($recordData, 4);
$numValue = $this->_createNumber($spos);
/*
if ($this->_isDate($spos)) {
$numValue = $this->_createNumber($spos);
list($string, $raw) = $this->_createDate($numValue);
} else {
if (isset($this->_columnsFormat[$column + 1])) {
$this->_curformat = $this->_columnsFormat[$column + 1];
}
$raw = $this->_createNumber($spos);
$string = sprintf($this->_curformat, $raw * $this->_multiplier);
}
*/
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
if (PHPExcel_Shared_Date::isDateTimeFormatCode($this->_xf[$xfindex]['numberformat']['code'])) {
$numValue = (int) PHPExcel_Shared_Date::ExcelToPHP($numValue);
}
}
//$this->_addcell($row, $column, $string, $raw);
//$sheet->setCellValueByColumnAndRow($column, $row + 1, $string);
$sheet->setCellValueByColumnAndRow($column, $row + 1, $numValue);
break;
case self::XLS_Type_FORMULA:
case self::XLS_Type_FORMULA2:
/**
* FORMULA
*
* This record contains the token array and the result of a
* formula cell.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
// offset: 0; size: 2; row index
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
// offset: 2; size: 2; col index
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
// offset: 4; size: 2; XF index
$xfindex = ord($this->_data[$spos + 4]) | ord($this->_data[$spos + 5]) << 8;
// offset: 6; size: 8; result of the formula
if (ord($this->_data[$spos + 6]) == 0 && ord($this->_data[$spos + 12]) == 255 && ord($this->_data[$spos + 13]) == 255) {
//String formula. Result follows in appended STRING record
$this->_formula_result = 'string';
$soff = $spos + $length;
$scode = ord($this->_data[$soff]) | ord($this->_data[$soff + 1]) << 8;
$sopt = ord($this->_data[$soff + 6]);
// only reads byte strings...
if ($scode == self::XLS_Type_STRING && $sopt == '0') {
$slen = ord($this->_data[$soff + 4]) | ord($this->_data[$soff + 5]) << 8;
$string = substr($this->_data, $soff + 7, ord($this->_data[$soff + 4]) | ord($this->_data[$soff + 5]) << 8);
} else {
$string = 'NOT FOUND';
}
$raw = $string;
} elseif (ord($this->_data[$spos + 6]) == 1 && ord($this->_data[$spos + 12]) == 255 && ord($this->_data[$spos + 13]) == 255) {
//Boolean formula. Result is in +2; 0=false,1=true
$this->_formula_result = 'boolean';
$raw = ord($this->_data[$spos + 8]);
if ($raw) {
$string = "TRUE";
} else {
$string = "FALSE";
}
} elseif (ord($this->_data[$spos + 6]) == 2 && ord($this->_data[$spos + 12]) == 255 && ord($this->_data[$spos + 13]) == 255) {
//Error formula. Error code is in +2
$this->_formula_result = 'error';
$raw = ord($this->_data[$spos + 8]);
$string = 'ERROR:' . $raw;
} elseif (ord($this->_data[$spos + 6]) == 3 && ord($this->_data[$spos + 12]) == 255 && ord($this->_data[$spos + 13]) == 255) {
//Formula result is a null string
$this->_formula_result = 'null';
$raw = '';
$string = '';
} else {
// forumla result is a number, first 14 bytes like _NUMBER record
$string = $this->_createNumber($spos);
/*
$this->_formula_result = 'number';
if ($this->_isDate($spos)) {
$numValue = $this->_createNumber($spos);
list($string, $raw) = $this->_createDate($numValue);
} else {
if (isset($this->_columnsFormat[$column + 1])){
$this->_curformat = $this->_columnsFormat[$column + 1];
}
$raw = $this->_createNumber($spos);
$string = sprintf($this->_curformat, $raw * $this->_multiplier);
}
*/
}
// save the raw formula tokens for end user interpretation
// Excel stores as a token record
$this->_rectype = 'formula';
// read formula record tokens ...
$tokenlength = ord($this->_data[$spos + 20]) | ord($this->_data[$spos + 21]) << 8;
for ($i = 0; $i < $tokenlength; $i++) {
$this->_formula[$i] = ord($this->_data[$spos + 22 + $i]);
}
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
if (PHPExcel_Shared_Date::isDateTimeFormatCode($this->_xf[$xfindex]['numberformat']['code'])) {
$string = (int) PHPExcel_Shared_Date::ExcelToPHP($string);
}
}
//$this->_addcell($row, $column, $string, $raw);
$sheet->setCellValueByColumnAndRow($column, $row + 1, $string);
// offset: 14: size: 2; option flags, recalculate always, recalculate on open etc.
// offset: 16: size: 4; not used
// offset: 20: size: variable; formula structure
// WORK IN PROGRESS: TRUE FORMULA SUPPORT
// resolve BIFF8 formula tokens into human readable formula
// so it can be added as formula
// $formulaStructure = substr($recordData, 20);
// $formulaString = $this->_getFormulaStringFromStructure($formulaStructure); // get human language
break;
case self::XLS_Type_BOOLERR:
/**
* BOOLERR
*
* This record represents a Boolean value or error value
* cell.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
// offset: 0; size: 2; row index
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
// offset: 2; size: 2; column index
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
// offset: 4; size: 2; index to XF record
$xfindex = $this->_GetInt2d($recordData, 4);
// offset: 6; size: 1; the boolean value or error value
$value = ord($recordData[6]);
// offset: 7; size: 1; 0=boolean; 1=error
$isError = ord($recordData[7]);
if (!$isError) {
$sheet->getCellByColumnAndRow($column, $row + 1)->setValueExplicit((bool) $value, PHPExcel_Cell_DataType::TYPE_BOOL);
}
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
}
break;
case self::XLS_Type_ROW:
/**
* ROW
*
* This record contains the properties of a single row in a
* sheet. Rows and cells in a sheet are divided into blocks
* of 32 rows.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
if (!$this->_readDataOnly) {
// offset: 0; size: 2; index of this row
$r = $this->_GetInt2d($recordData, 0);
// offset: 2; size: 2; index to column of the first cell which is described by a cell record
// offset: 4; size: 2; index to column of the last cell which is described by a cell record, increased by 1
// offset: 6; size: 2;
// bit: 14-0; mask: 0x7FF; height of the row, in twips = 1/20 of a point
$height = (0x7ff & $this->_GetInt2d($recordData, 6)) >> 0;
// bit: 15: mask: 0x8000; 0 = row has custom height; 1= row has default height
$useDefaultHeight = (0x8000 & $this->_GetInt2d($recordData, 6)) >> 15;
if (!$useDefaultHeight) {
$sheet->getRowDimension($r + 1)->setRowHeight($height / 20);
}
// offset: 8; size: 2; not used
// offset: 10; size: 2; not used in BIFF5-BIFF8
// offset: 12; size: 4; option flags and default row formatting
// bit: 2-0: mask: 0x00000007; outline level of the row
$level = (0x7 & $this->_GetInt4d($recordData, 12)) >> 0;
$sheet->getRowDimension($r + 1)->setOutlineLevel($level);
// bit: 4; mask: 0x00000010; 1 = outline group start or ends here... and is collapsed
$isCollapsed = (0x10 & $this->_GetInt4d($recordData, 12)) >> 4;
$sheet->getRowDimension($r + 1)->setCollapsed($isCollapsed);
// bit: 5; mask: 0x00000020; 1 = row is hidden
$isHidden = (0x20 & $this->_GetInt4d($recordData, 12)) >> 5;
$sheet->getRowDimension($r + 1)->setVisible(!$isHidden);
}
break;
case self::XLS_Type_DBCELL:
/**
* DBCELL
*
* This record is written once in a Row Block. It contains
* relative offsets to calculate the stream position of the
* first cell record for each row. The offset list in this
* record contains as many offsets as ROW records are
* present in the Row Block.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
break;
case self::XLS_Type_MULBLANK:
/**
* MULBLANK - Multiple BLANK
*
* This record represents a cell range of empty cells. All
* cells are located in the same row
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
// offset: 0; size: 2; index to row
$row = $this->_GetInt2d($recordData, 0);
// offset: 2; size: 2; index to first column
$fc = $this->_GetInt2d($recordData, 2);
// offset: 4; size: 2 x nc; list of indexes to XF records
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
for ($i = 0; $i < $length / 2 - 4; $i++) {
$xfindex = $this->_GetInt2d($recordData, 4 + 2 * $i);
$sheet->getStyleByColumnAndRow($fc + $i, $row + 1)->applyFromArray($this->_xf[$xfindex]);
}
}
// offset: 6; size 2; index to last column (not needed)
break;
case self::XLS_Type_LABEL:
/**
* LABEL
*
* This record represents a cell that contains a string. In
* BIFF8 it is usually replaced by the LABELSST record.
* Excel still uses this record, if it copies unformatted
* text cells to the clipboard.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
/*
$this->_addcell($row, $column, substr($this->_data, $spos + 8,
ord($this->_data[$spos + 6]) | ord($this->_data[$spos + 7]) << 8));
*/
$sheet->setCellValueByColumnAndRow($column, $row + 1, substr($this->_data, $spos + 8, ord($this->_data[$spos + 6]) | ord($this->_data[$spos + 7]) << 8));
break;
case self::XLS_Type_PROTECT:
/**
* PROTECT - Sheet protection (BIFF2 through BIFF8)
* if this record is omitted, then it also means no sheet protection
*/
if (!$this->_readDataOnly) {
// offset: 0; size: 2;
// bit 0, mask 0x01; sheet protection
$isSheetProtected = (0x1 & $this->_GetInt2d($recordData, 0)) >> 0;
switch ($isSheetProtected) {
case 0:
break;
case 1:
$sheet->getProtection()->setSheet(true);
break;
}
}
break;
case self::XLS_Type_PASSWORD:
/**
* PASSWORD - Sheet protection (hashed) password (BIFF2 through BIFF8)
*/
if (!$this->_readDataOnly) {
// offset: 0; size: 2; 16-bit hash value of password
$password = strtoupper(dechex($this->_GetInt2d($recordData, 0)));
// the hashed password
$sheet->getProtection()->setPassword($password, true);
}
break;
case self::XLS_Type_COLINFO:
/**
* COLINFO - Column information
*/
if (!$this->_readDataOnly) {
// offset: 0; size: 2; index to first column in range
$fc = $this->_GetInt2d($recordData, 0);
// first column index
// offset: 2; size: 2; index to last column in range
$lc = $this->_GetInt2d($recordData, 2);
// first column index
// offset: 4; size: 2; width of the column in 1/256 of the width of the zero character
$width = $this->_GetInt2d($recordData, 4);
// offset: 6; size: 2; index to XF record for default column formatting
// offset: 8; size: 2; option flags
// bit: 0; mask: 0x0001; 1= columns are hidden
$isHidden = (0x1 & $this->_GetInt2d($recordData, 8)) >> 0;
// bit: 10-8; mask: 0x0700; outline level of the columns (0 = no outline)
$level = (0x700 & $this->_GetInt2d($recordData, 8)) >> 8;
// bit: 12; mask: 0x1000; 1 = collapsed
$isCollapsed = (0x1000 & $this->_GetInt2d($recordData, 8)) >> 12;
// offset: 10; size: 2; not used
for ($i = $fc; $i <= $lc; $i++) {
$sheet->getColumnDimensionByColumn($i)->setWidth($width / 256);
$sheet->getColumnDimensionByColumn($i)->setVisible(!$isHidden);
$sheet->getColumnDimensionByColumn($i)->setOutlineLevel($level);
$sheet->getColumnDimensionByColumn($i)->setCollapsed($isCollapsed);
}
}
break;
case self::XLS_Type_DEFCOLWIDTH:
// offset: 0; size: 2; row index
$width = $this->_GetInt2d($recordData, 0);
$sheet->getDefaultColumnDimension()->setWidth($width);
break;
case self::XLS_Type_DEFAULTROWHEIGHT:
// offset: 0; size: 2; option flags
// offset: 2; size: 2; default height for unused rows, (twips 1/20 point)
$height = $this->_GetInt2d($recordData, 2);
$sheet->getDefaultRowDimension()->setRowHeight($height / 20);
break;
case self::XLS_Type_BLANK:
// offset: 0; size: 2; row index
$row = $this->_GetInt2d($recordData, 0);
// offset: 2; size: 2; col index
$col = $this->_GetInt2d($recordData, 2);
// offset: 4; size: 2; XF index
$xfindex = $this->_GetInt2d($recordData, 4);
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($col, $row + 1)->applyFromArray($this->_xf[$xfindex]);
}
break;
case self::XLS_Type_SHEETPR:
// offset: 0; size: 2
// bit: 6; mask: 0x0040; 0 = outline buttons above outline group
$isSummaryBelow = (0x40 & $this->_GetInt2d($recordData, 0)) >> 6;
$sheet->setShowSummaryBelow($isSummaryBelow);
// bit: 7; mask: 0x0080; 0 = outline buttons left of outline group
$isSummaryRight = (0x80 & $this->_GetInt2d($recordData, 0)) >> 7;
$sheet->setShowSummaryRight($isSummaryRight);
break;
case self::XLS_Type_EOF:
$cont = false;
break;
default:
break;
}
$spos += $length;
}
if (!isset($this->_sheets[$this->_sn]['numRows'])) {
$this->_sheets[$this->_sn]['numRows'] = $this->_sheets[$this->_sn]['maxrow'];
}
if (!isset($this->_sheets[$this->_sn]['numCols'])) {
$this->_sheets[$this->_sn]['numCols'] = $this->_sheets[$this->_sn]['maxcol'];
}
}
/*
foreach($this->_boundsheets as $index => $details) {
$sheet = $excel->getSheet($index);
// read all the columns of all the rows !
$numrows = $this->_sheets[$index]['numRows'];
$numcols = $this->_sheets[$index]['numCols'];
for ($row = 0; $row < $numrows; $row++) {
for ($col = 0; $col < $numcols; $col++) {
$cellcontent = $cellinfo = null;
if (isset($this->_sheets[$index]['cells'][$row][$col])===true) {
$cellcontent = $this->_sheets[$index]['cells'][$row][$col];
} else {
continue;
}
if (isset($this->_sheets[$index]['cellsInfo'][$row][$col])===true) {
$cellinfo = $this->_sheets[$index]['cellsInfo'][$row][$col];
}
$sheet->setCellValueByColumnAndRow($col, $row + 1,
$cellcontent);
}
}
};
*/
return $excel;
}
$objCommentRichText->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun("\r\n");
$objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('Total amount on the current invoice, excluding VAT.');
$objPHPExcel->getActiveSheet()->getComment('E12')->setAuthor('PHPExcel');
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E12')->getText()->createTextRun('PHPExcel:');
$objCommentRichText->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getComment('E12')->getText()->createTextRun("\r\n");
$objPHPExcel->getActiveSheet()->getComment('E12')->getText()->createTextRun('Total amount of VAT on the current invoice.');
$objPHPExcel->getActiveSheet()->getComment('E13')->setAuthor('PHPExcel');
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E13')->getText()->createTextRun('PHPExcel:');
$objCommentRichText->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getComment('E13')->getText()->createTextRun("\r\n");
$objPHPExcel->getActiveSheet()->getComment('E13')->getText()->createTextRun('Total amount on the current invoice, including VAT.');
// Add rich-text string
echo date('H:i:s') . " Add rich-text string\n";
$objRichText = new PHPExcel_RichText($objPHPExcel->getActiveSheet()->getCell('A18'));
$objRichText->createText('This invoice is ');
$objPayable = $objRichText->createTextRun('payable within thirty days after the end of the month');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText(', unless specified otherwise on the invoice.');
// Merge cells
echo date('H:i:s') . " Merge cells\n";
$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
$objPHPExcel->getActiveSheet()->mergeCells('A28:B28');
// Just to test...
$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');
// Just to test...
// Protect cells
echo date('H:i:s') . " Protect cells\n";
protected function _parseRichText($is = null)
{
$value = new PHPExcel_RichText();
if (isset($is->t)) {
$value->createText(PHPExcel_Shared_String::ControlCharacterOOXML2PHP((string) $is->t));
} else {
foreach ($is->r as $run) {
if (!isset($run->rPr)) {
$objText = $value->createText(PHPExcel_Shared_String::ControlCharacterOOXML2PHP((string) $run->t));
} else {
$objText = $value->createTextRun(PHPExcel_Shared_String::ControlCharacterOOXML2PHP((string) $run->t));
}
}
}
return $value;
}
/**
* Write Rich Text
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param PHPExcel_RichText $pRichText Rich text
* @throws Exception
*/
public function writeRichText(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_RichText $pRichText = null)
{
// Loop trough rich text elements
$elements = $pRichText->getRichTextElements();
foreach ($elements as $element) {
// r
$objWriter->startElement('r');
// rPr
if ($element instanceof PHPExcel_RichText_Run) {
// rPr
$objWriter->startElement('rPr');
// rFont
$objWriter->startElement('rFont');
$objWriter->writeAttribute('val', $element->getFont()->getName());
$objWriter->endElement();
// Bold
$objWriter->startElement('b');
$objWriter->writeAttribute('val', $element->getFont()->getBold() ? 'true' : 'false');
$objWriter->endElement();
// Italic
$objWriter->startElement('i');
$objWriter->writeAttribute('val', $element->getFont()->getItalic() ? 'true' : 'false');
$objWriter->endElement();
// Striketrough
$objWriter->startElement('strike');
$objWriter->writeAttribute('val', $element->getFont()->getStriketrough() ? 'true' : 'false');
$objWriter->endElement();
// Color
$objWriter->startElement('color');
$objWriter->writeAttribute('rgb', $element->getFont()->getColor()->getARGB());
$objWriter->endElement();
// Size
$objWriter->startElement('sz');
$objWriter->writeAttribute('val', $element->getFont()->getSize());
$objWriter->endElement();
// Underline
$objWriter->startElement('u');
$objWriter->writeAttribute('val', $element->getFont()->getUnderline());
$objWriter->endElement();
$objWriter->endElement();
}
// t
$objWriter->startElement('t');
$objWriter->writeAttribute('xml:space', 'preserve');
$objWriter->writeRaw(PHPExcel_Shared_String::ControlCharacterPHP2OOXML($element->getText()));
$objWriter->endElement();
$objWriter->endElement();
}
}
public function create_excel($cart, $data)
{
/** Error reporting */
error_reporting(E_ALL);
//define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
date_default_timezone_set('Europe/London');
/** Include PHPExcel */
require_once dirname(__FILE__) . '/excel/PHPExcel.php';
// Create new PHPExcel object
//echo date('H:i:s') , " Create new PHPExcel object" , EOL;
$objPHPExcel = new PHPExcel();
// Set document properties
//echo date('H:i:s') , " Set document properties" , EOL;
$objPHPExcel->getProperties()->setCreator("Margina Radu")->setLastModifiedBy("Margina Radu")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file");
// Create a first sheet, representing sales data
//echo date('H:i:s') , " Add some data" , EOL;
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Corden: Invoice');
$objPHPExcel->getActiveSheet()->setCellValue('F1', PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, date('m'), date('d'), date('Y'))));
$objPHPExcel->getActiveSheet()->getStyle('F1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15);
//$objPHPExcel->getActiveSheet()->setCellValue('E1', '#12566');
$objPHPExcel->getActiveSheet()->setCellValue('A12', 'ID');
$objPHPExcel->getActiveSheet()->setCellValue('B12', 'Simbol');
$objPHPExcel->getActiveSheet()->setCellValue('C12', 'Denumire');
$objPHPExcel->getActiveSheet()->setCellValue('D12', 'Cantitatea');
$objPHPExcel->getActiveSheet()->setCellValue('E12', 'Pret');
$objPHPExcel->getActiveSheet()->setCellValue('F12', 'Subtotal');
$index = 13;
$start_index = 13;
foreach ($cart as $item) {
$objPHPExcel->getActiveSheet()->setCellValue('A' . $index, $item['id']);
$objPHPExcel->getActiveSheet()->setCellValue('B' . $index, $item['simbol']);
$objPHPExcel->getActiveSheet()->setCellValue('C' . $index, $item['name']);
$objPHPExcel->getActiveSheet()->setCellValue('D' . $index, $item['qty']);
$objPHPExcel->getActiveSheet()->setCellValue('E' . $index, $item['price']);
$objPHPExcel->getActiveSheet()->setCellValue('F' . $index, $item['subtotal']);
$index++;
}
$end_index = $index - 1;
$total_index = $index + 1;
$objPHPExcel->getActiveSheet()->setCellValue('E' . $total_index, 'Pret Total:');
$objPHPExcel->getActiveSheet()->setCellValue('F' . $total_index, '=SUM(F13:F' . $end_index . ')');
// Add comment
//echo date('H:i:s') , " Add comments" , EOL;
$objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->setAuthor('PHPExcel');
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->getText()->createTextRun('PHPExcel:');
$objCommentRichText->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->getText()->createTextRun("\r\n");
$objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->getText()->createTextRun('Total amount on the current invoice, including VAT.');
$objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->setWidth('100pt');
$objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->setHeight('100pt');
$objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->setMarginLeft('150pt');
$objPHPExcel->getActiveSheet()->getComment('F' . $total_index)->getFillColor()->setRGB('EEEEEE');
// Add rich-text string
//echo date('H:i:s') , " Add rich-text string" , EOL;
$objRichText = new PHPExcel_RichText();
$objRichText->createText("Nume: ");
$objPayable = $objRichText->createTextRun($data['nume'] . " " . $data['prenume']);
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText("\nEmail : ");
$objPayable = $objRichText->createTextRun($data['email']);
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText("\nTelefon : ");
$objPayable = $objRichText->createTextRun($data['telefon']);
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText("\nAdresa : ");
$objPayable = $objRichText->createTextRun($data['adresa']['orasul'] . "," . $data['adresa']['localitatea'] . ", str. " . $data['adresa']['strada'] . " " . $data['adresa']['numarul'] . ", ap. " . $data['adresa']['apartament']);
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText("\nMetoda de plata : ");
$objPayable = $objRichText->createTextRun($data['payment']);
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText("\nSunet de confirmare : ");
$objPayable = $objRichText->createTextRun(isset($data['call_after']) ? $data['call_after'] : 'nu');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
$objPHPExcel->getActiveSheet()->getCell('A3')->setValue($objRichText);
// Merge cells
//echo date('H:i:s') , " Merge cells" , EOL;
$objPHPExcel->getActiveSheet()->mergeCells('A3:F10');
// Just to test...
// Protect cells
//echo date('H:i:s') , " Protect cells" , EOL;
// $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!
//$objPHPExcel->getActiveSheet()->protectCells('A3:F13', 'PHPExcel');
// Set cell number formats
//echo date('H:i:s') , " Set cell number formats" , EOL;
//$objPHPExcel->getActiveSheet()->getStyle('E4:F13')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
// Set column widths
//echo date('H:i:s') , " Set column widths" , EOL;
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
// Set fonts
//echo date('H:i:s') , " Set fonts" , EOL;
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('F1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('E' . $total_index)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('F' . $total_index)->getFont()->setBold(true);
// Set alignments
//echo date('H:i:s') , " Set alignments" , EOL;
$objPHPExcel->getActiveSheet()->getStyle('F' . $total_index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B13')->getAlignment()->setShrinkToFit(true);
// Set thin black border outline around column
//echo date('H:i:s') , " Set thin black border outline around column" , EOL;
$styleThinBlackBorderOutline = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000'))));
$objPHPExcel->getActiveSheet()->getStyle('A13:F' . $end_index)->applyFromArray($styleThinBlackBorderOutline);
// Set thick brown border outline around "Total"
//echo date('H:i:s') , " Set thick brown border outline around Total" , EOL;
$styleThickBrownBorderOutline = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THICK, 'color' => array('argb' => 'FF993300'))));
$objPHPExcel->getActiveSheet()->getStyle('E' . $total_index . ':F' . $total_index . '')->applyFromArray($styleThickBrownBorderOutline);
// Set fills
//echo date('H:i:s') , " Set fills" , EOL;
$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getFill()->getStartColor()->setARGB('FF808080');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('80FFFFFF');
// Set style for header row using alternative method
//echo date('H:i:s') , " Set style for header row using alternative method" , EOL;
$objPHPExcel->getActiveSheet()->getStyle('A12:F12')->applyFromArray(array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => 'FFA0A0A0'), 'endcolor' => array('argb' => 'FFFFFFFF'))));
$objPHPExcel->getActiveSheet()->getStyle('A12')->applyFromArray(array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('left' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
$objPHPExcel->getActiveSheet()->getStyle('B12')->applyFromArray(array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)));
$objPHPExcel->getActiveSheet()->getStyle('F12')->applyFromArray(array('borders' => array('right' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
// Unprotect a cell
//echo date('H:i:s') , " Unprotect a cell" , EOL;
$objPHPExcel->getActiveSheet()->getStyle('B1')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
// Add a drawing to the worksheet
//echo date('H:i:s') , " Add a drawing to the worksheet" , EOL;
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath($_SERVER['DOCUMENT_ROOT'] . '/assets/img/images/logo.jpg');
$objDrawing->setHeight(36);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
// Set header and footer. When no different headers for odd/even are used, odd header is assumed.
//echo date('H:i:s') , " Set header/footer" , EOL;
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&BInvoice&RPrinted on &D');
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');
// Set page orientation and size
//echo date('H:i:s') , " Set page orientation and size" , EOL;
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
// Rename first worksheet
//echo date('H:i:s') , " Rename first worksheet" , EOL;
$objPHPExcel->getActiveSheet()->setTitle('Invoice');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
/** Include PHPExcel_IOFactory */
require_once dirname(__FILE__) . '/excel/PHPExcel/IOFactory.php';
// Save Excel 2007 file
//echo date('H:i:s') , " Write to Excel2007 format" , EOL;
$callStartTime = microtime(true);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', 'invoice.xlsx'));
//echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
//echo 'Call time to write Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;
// Echo memory usage
//echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;
// Echo done
//echo date('H:i:s') , " Done writing files" , EOL;
//echo 'Files have been created in ' , getcwd() , EOL;
}
public function setData($xlsfile, $dataArr = array(), $mode = "auto")
{
if ('auto' == $mode) {
$ext = substr($xlsfile, strrpos($xlsfile, '.'));
$xlstype = '.xls' == $ext ? 'Excel5' : 'Excel2007';
} else {
$xlstype = $mode;
}
$object = $this->LoadObject($xlstype);
if (!$object) {
return false;
}
$header = isset($dataArr['header']) ? $dataArr['header'] : null;
$body = isset($dataArr['body']) ? $dataArr['body'] : null;
if ($body) {
foreach ($body as $sheet => $dataArr) {
isset($index) ? $index++ : ($index = 0);
$object->setActiveSheetIndex($index);
$object->getActiveSheet()->setTitle($sheet);
foreach ($dataArr as $row_sn => $row) {
foreach ($row as $col_sn => $cell) {
$col_letter = $this->excelColumn($col_sn);
//cell是一个数组
$value = $cell['value'];
$value = str_replace("\r", "\n", $value);
$objRichText = new PHPExcel_RichText();
// $objRichText->createText($value);
$objPayable = $objRichText->createTextRun($value);
if (!empty($cell['bold'])) {
$objPayable->getFont()->setBold(true);
}
if (!empty($cell['italic'])) {
$objPayable->getFont()->setItalic(true);
}
if (isset($cell['color']) && 6 == strlen($cell['color'])) {
$objPayable->getFont()->setColor(new PHPExcel_Style_Color('FF' . $cell['color']));
}
if (!empty($cell['autosize'])) {
$object->getActiveSheet()->getColumnDimension($col_letter)->setAutoSize(true);
}
// print_r(get_class_methods($objPayable->getFont()));exit;
// echo "$col_letter($col_sn).$row_sn =======";
// $objActSheet ->getStyle('A1')->getAlignment()->setShrinkToFit(true);//字体变小以适应宽
// $objActSheet ->getStyle('A1')->getAlignment()->setWrapText(true);//自动换行
$object->getActiveSheet()->setCellValue($col_letter . $row_sn, $objRichText);
// $object->getActiveSheet()->getStyle($col_letter.$row_sn)->getAlignment()->setShrinkToFit(true);//字体变小以适应宽
// $object->getActiveSheet()->getStyle($col_letter.$row_sn)->getAlignment()->setWrapText(true);//自动换行
}
// $object->getActiveSheet()->getRowDimension($row_sn)->setRowHeight(50);
// print_r(get_class_methods($object->getActiveSheet()->getRowDimension($row_sn)));exit;
}
// $object->getActiveSheet()->getColumnDimension('T')->setAutoSize(true);
// $object->getActiveSheet()->getColumnDimension('U')->setAutoSize(true);
// $object->getActiveSheet()->getColumnDimension('V')->setAutoSize(true);
}
}
/*
$object->getProperties()->setCreator("cty")
->setLastModifiedBy("Maarten Balliauw")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
$object->getDefaultStyle()->getFont()->setName('Arial')
->setSize(10);
// Add some data, resembling some different data types
$object->getActiveSheet()->setCellValue('A1', 'String')
->setCellValue('B1', 'Simple')
->setCellValue('C1', 'PHPExcel');
$object->getActiveSheet()->setCellValue('A2', 'String')
->setCellValue('B2', 'Symbols')
->setCellValue('C2', '!+&=()~§±æþ');
$object->getActiveSheet()->setCellValue('A3', 'String')
->setCellValue('B3', 'UTF-8')
->setCellValue('C3', 'Создать MS Excel Книги из PHP скриптов');
$object->getActiveSheet()->setCellValue('A4', 'Number')
->setCellValue('B4', 'Integer')
->setCellValue('C4', 12);
$object->getActiveSheet()->setCellValue('A5', 'Number')
->setCellValue('B5', 'Float')
->setCellValue('C5', 34.56);
$object->getActiveSheet()->setCellValue('A6', 'Number')
->setCellValue('B6', 'Negative')
->setCellValue('C6', -7.89);
$object->getActiveSheet()->setCellValue('A7', 'Boolean')
->setCellValue('B7', 'True')
->setCellValue('C7', true);
$object->getActiveSheet()->setCellValue('A8', 'Boolean')
->setCellValue('B8', 'False')
->setCellValue('C8', false);
$dateTimeNow = time();
$object->getActiveSheet()->setCellValue('A9', 'Date/Time')
->setCellValue('B9', 'Date')
->setCellValue('C9', PHPExcel_Shared_Date::PHPToExcel( $dateTimeNow ));
$object->getActiveSheet()->getStyle('C9')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
$object->getActiveSheet()->setCellValue('A10', 'Date/Time')
->setCellValue('B10', 'Time')
->setCellValue('C10', PHPExcel_Shared_Date::PHPToExcel( $dateTimeNow ));
$object->getActiveSheet()->getStyle('C10')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);
$object->getActiveSheet()->setCellValue('A11', 'Date/Time')
->setCellValue('B11', 'Date and Time')
->setCellValue('C11', PHPExcel_Shared_Date::PHPToExcel( $dateTimeNow ));
$object->getActiveSheet()->getStyle('C11')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME);
$object->getActiveSheet()->setCellValue('A12', 'NULL')
->setCellValue('C12', NULL);
$objRichText = new PHPExcel_RichText();
// $objRichText->createText('你好 ');
$objPayable = $objRichText->createTextRun('你 好 吗？');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor( new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
// $objRichText->createText(', unless specified otherwise on the invoice.');
$object->getActiveSheet()->setCellValue('A13', 'Rich Text')
->setCellValue('C13', $objRichText);
$object->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$object->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
// Rename worksheet
$object->getActiveSheet()->setTitle('Datatypes');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$object->setActiveSheetIndex(0);
// Save Excel 2007 file
$callStartTime = microtime(true);
*/
$objWriter = PHPExcel_IOFactory::createWriter($object, 'Excel2007');
$objWriter->save($xlsfile);
return $xlsfile;
}
$objPHPExcel->getActiveSheet()->setCellValue('A9', 'Date/Time')->setCellValue('B9', 'Date')->setCellValue('C9', PHPExcel_Shared_Date::PHPToExcel($dateTimeNow));
$objPHPExcel->getActiveSheet()->getStyle('C9')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
$objPHPExcel->getActiveSheet()->setCellValue('A10', 'Date/Time')->setCellValue('B10', 'Time')->setCellValue('C10', PHPExcel_Shared_Date::PHPToExcel($dateTimeNow));
$objPHPExcel->getActiveSheet()->getStyle('C10')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);
$objPHPExcel->getActiveSheet()->setCellValue('A11', 'Date/Time')->setCellValue('B11', 'Date and Time')->setCellValue('C11', PHPExcel_Shared_Date::PHPToExcel($dateTimeNow));
$objPHPExcel->getActiveSheet()->getStyle('C11')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME);
$objPHPExcel->getActiveSheet()->setCellValue('A12', 'NULL')->setCellValue('C12', NULL);
$objRichText = new PHPExcel_RichText();
$objRichText->createText('你好 ');
$objPayable = $objRichText->createTextRun('你 好 吗？');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN));
$objRichText->createText(', unless specified otherwise on the invoice.');
$objPHPExcel->getActiveSheet()->setCellValue('A13', 'Rich Text')->setCellValue('C13', $objRichText);
$objRichText2 = new PHPExcel_RichText();
$objRichText2->createText("black text\n");
$objRed = $objRichText2->createTextRun("red text");
$objRed->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_RED));
$objPHPExcel->getActiveSheet()->getCell("C14")->setValue($objRichText2);
$objPHPExcel->getActiveSheet()->getStyle("C14")->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
// Rename worksheet
echo date('H:i:s'), " Rename worksheet", EOL;
$objPHPExcel->getActiveSheet()->setTitle('Datatypes');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Save Excel 95 file
echo date('H:i:s'), " Write to Excel5 format", EOL;
$callStartTime = microtime(true);
$dir=dirname(__FILE__);//查找当前脚本所在路径
require $dir."/db.php";//引入mysql操作类文件
require $dir."/PHPExcel/PHPExcel.php";//引入PHPExcel
$objPHPExcel=new PHPExcel();//实例化PHPExcel类， 等同于在桌面上新建一个excel
$objSheet=$objPHPExcel->getActiveSheet();//获得当前活动sheet
/**插入图片代码开始**/
$objDrawing=new PHPExcel_WorkSheet_Drawing();//获得一个图片的操作对象
$objDrawing->setPath($dir."/image/imooc.jpg");//加载图片路径
$objDrawing->setCoordinates("F6");//设置图片插入位置的左上角坐标
$objDrawing->setWidth(500);//设置插入图片的大小
//$objDrawing->setHeight(100);
//$objDrawing->setOffsetX(20)->setOffsetY(40);//设定单元格内偏移量
$objDrawing->setWorkSheet($objSheet);//将图片插入到sheet
/**代码结束**/
/**添加丰富的文字块**/
$objRichText=new PHPExcel_RichText();//获得一个文字块操作对象
$objRichText->createText("慕课网(imooc)");//添加普通的文字 不能操作样式
$objStyleFont=$objRichText->createTextRun("是国内最大的IT技能免费培训平台");//生成可以添加样式的文字块
$objStyleFont->getFont()->setSize(16)->setBold(True)->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_GREEN));//加一些样式
$objRichText->createText(",课程丰富多样");
$objSheet->getCell("F4")->setValue($objRichText);//将文字块插入sheet里
/**代码结束**/
/**添加批注代码开始**/
$objSheet->mergeCells("F4:N4");//合并单元格
$objSheet->getComment("F4")->getText()->createTextRun("Van:\r\n慕课网\n\n时尚时尚最时尚");//添加批注
/**代码结束**/
/**超链接代码开始**/
$objSheet->setCellValue("I3","慕课网");//添加文字
$objSheet->getStyle("I3")->getFont()->setSize(16)->setBold(true)->setUnderline(true)->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_BLUE));//添加样式
$objSheet->getCell("I3")->getHyperlink()->setUrl("http://www.imooc.com");//给文字加上链接地址
private static function _parseRichText($titleDetailPart = null)
{
$value = new PHPExcel_RichText();
foreach ($titleDetailPart as $titleDetailElementKey => $titleDetailElement) {
if (isset($titleDetailElement->t)) {
$objText = $value->createTextRun((string) $titleDetailElement->t);
}
if (isset($titleDetailElement->rPr)) {
if (isset($titleDetailElement->rPr->rFont["val"])) {
$objText->getFont()->setName((string) $titleDetailElement->rPr->rFont["val"]);
}
$fontSize = self::_getAttribute($titleDetailElement->rPr, 'sz', 'integer');
if (!is_null($fontSize)) {
$objText->getFont()->setSize(floor($fontSize / 100));
}
$fontColor = self::_getAttribute($titleDetailElement->rPr, 'color', 'string');
if (!is_null($fontColor)) {
$objText->getFont()->setColor(new PHPExcel_Style_Color(self::_readColor($fontColor)));
}
$bold = self::_getAttribute($titleDetailElement->rPr, 'b', 'boolean');
if (!is_null($bold)) {
$objText->getFont()->setBold($bold);
}
$italic = self::_getAttribute($titleDetailElement->rPr, 'i', 'boolean');
if (!is_null($italic)) {
$objText->getFont()->setItalic($italic);
}
$baseline = self::_getAttribute($titleDetailElement->rPr, 'baseline', 'integer');
if (!is_null($baseline)) {
if ($baseline > 0) {
$objText->getFont()->setSuperScript(true);
} elseif ($baseline < 0) {
$objText->getFont()->setSubScript(true);
}
}
$underscore = self::_getAttribute($titleDetailElement->rPr, 'u', 'string');
if (!is_null($underscore)) {
if ($underscore == 'sng') {
$objText->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
} elseif ($underscore == 'dbl') {
$objText->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_DOUBLE);
} else {
$objText->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_NONE);
}
}
$strikethrough = self::_getAttribute($titleDetailElement->rPr, 's', 'string');
if (!is_null($strikethrough)) {
if ($strikethrough == 'noStrike') {
$objText->getFont()->setStrikethrough(false);
} else {
$objText->getFont()->setStrikethrough(true);
}
}
}
}
return $value;
}
$activeSheet->mergeCells('C2:G2');
$activeSheet->getStyle('C2:G2')->applyFromArray($canhgiua)->applyFromArray($indam);
$a_date = $val[0]->namlamthem . "-" . $key . "-01";
$tu = date("01/m/Y", strtotime($a_date));
$den = date("t/m/Y", strtotime($a_date));
$objRichText = new PHPExcel_RichText();
$objRichText->createText('Tháng ' . $key . ': ');
$objBold = $objRichText->createTextRun($tu . ' - ' . $den);
$objBold->getFont()->setItalic(true)->setName('Times New Roman')->setSize(13);
$activeSheet->setCellValue('B5', $objRichText);
unset($objBold);
$activeSheet->setCellValue('B4', 'GIẤY BÁO LÀM THÊM GIỜ');
$activeSheet->mergeCells('B4:F4')->mergeCells('B5:F5');
$activeSheet->getStyle('B4:F4')->applyFromArray($canhgiua)->applyFromArray($indam);
$activeSheet->getStyle('B5:F5')->applyFromArray($canhgiua);
$objRichText = new PHPExcel_RichText();
$objRichText->createText('Họ và tên: ');
$objBold = $objRichText->createTextRun($val[0]->tennhanvien);
$objBold->getFont()->setBold(true)->setSize(13);
$activeSheet->setCellValue('A7', $objRichText);
unset($objBold);
$activeSheet->setCellValue('A8', "Bộ phận công tác: Nhân viên phòng Phòng Phát triển phần mềm");
$activeSheet->setCellValue('A10', 'Ngày, tháng')->setCellValue('B10', "Những công việc đã làm")->setCellValue('C10', "Những công việc đã làm")->setCellValue('C11', 'Từ giờ')->setCellValue('D11', 'Đến giờ')->setCellValue('E11', 'Tổng giờ')->setCellValue('F11', 'Đơn giá')->setCellValue('G11', 'Thành tiền');
$activeSheet->mergeCells('A10:A11')->mergeCells('B10:B11')->mergeCells('C10:G10');
$activeSheet->getStyle('A10:A11')->applyFromArray($canhgiua)->applyFromArray($indam);
$activeSheet->getStyle('B10:B11')->applyFromArray($canhgiua)->applyFromArray($indam);
$activeSheet->getStyle('C10:G10')->applyFromArray($canhgiua)->applyFromArray($indam);
$activeSheet->getStyle('C11:G11')->applyFromArray($canhgiua);
$tongthoigian = 0;
for ($i = 0; $i < count($val); $i++) {
$stt = 12 + $i;
private function _parseRichText($is = null) {
$value = new PHPExcel_RichText();
if (isset($is->t)) {
$value->createText( PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $is->t ) );
} else {
foreach ($is->r as $run) {
if (!isset($run->rPr)) {
$objText = $value->createText( PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $run->t ) );
} else {
$objText = $value->createTextRun( PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $run->t ) );
if (isset($run->rPr->rFont["val"])) {
$objText->getFont()->setName((string) $run->rPr->rFont["val"]);
}
if (isset($run->rPr->sz["val"])) {
$objText->getFont()->setSize((string) $run->rPr->sz["val"]);
}
if (isset($run->rPr->color)) {
$objText->getFont()->setColor( new PHPExcel_Style_Color( $this->_readColor($run->rPr->color) ) );
}
if ( (isset($run->rPr->b["val"]) && ((string) $run->rPr->b["val"] == 'true' || (string) $run->rPr->b["val"] == '1'))
|| (isset($run->rPr->b) && !isset($run->rPr->b["val"])) ) {
$objText->getFont()->setBold(true);
}
if ( (isset($run->rPr->i["val"]) && ((string) $run->rPr->i["val"] == 'true' || (string) $run->rPr->i["val"] == '1'))
|| (isset($run->rPr->i) && !isset($run->rPr->i["val"])) ) {
$objText->getFont()->setItalic(true);
}
if (isset($run->rPr->vertAlign) && isset($run->rPr->vertAlign["val"])) {
$vertAlign = strtolower((string)$run->rPr->vertAlign["val"]);
if ($vertAlign == 'superscript') {
$objText->getFont()->setSuperScript(true);
}
if ($vertAlign == 'subscript') {
$objText->getFont()->setSubScript(true);
}
}
if (isset($run->rPr->u) && !isset($run->rPr->u["val"])) {
$objText->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
} else if (isset($run->rPr->u) && isset($run->rPr->u["val"])) {
$objText->getFont()->setUnderline((string)$run->rPr->u["val"]);
}
if ( (isset($run->rPr->strike["val"]) && ((string) $run->rPr->strike["val"] == 'true' || (string) $run->rPr->strike["val"] == '1'))
|| (isset($run->rPr->strike) && !isset($run->rPr->strike["val"])) ) {
$objText->getFont()->setStrikethrough(true);
}
}
}
}
return $value;
}
/**
* Read LABELSST record
* This record represents a cell that contains a string. It
* replaces the LABEL record and RSTRING record used in
* BIFF2-BIFF5.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
private function _readLabelSst()
{
$length = $this->_GetInt2d($this->_data, $this->_pos + 2);
$recordData = substr($this->_data, $this->_pos + 4, $length);
// move stream pointer to next record
$this->_pos += 4 + $length;
// offset: 0; size: 2; index to row
$row = $this->_GetInt2d($recordData, 0);
// offset: 2; size: 2; index to column
$column = $this->_GetInt2d($recordData, 2);
$columnString = PHPExcel_Cell::stringFromColumnIndex($column);
// Read cell?
if (!is_null($this->getReadFilter()) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle())) {
// offset: 4; size: 2; index to XF record
$xfIndex = $this->_GetInt2d($recordData, 4);
// offset: 6; size: 4; index to SST record
$index = $this->_GetInt4d($recordData, 6);
// add cell
if (($fmtRuns = $this->_sst[$index]['fmtRuns']) && !$this->_readDataOnly) {
// then we should treat as rich text
$richText = new PHPExcel_RichText($this->_phpSheet->getCell($columnString . ($row + 1)));
$charPos = 0;
for ($i = 0; $i <= count($this->_sst[$index]['fmtRuns']); ++$i) {
if (isset($fmtRuns[$i])) {
$text = mb_substr($this->_sst[$index]['value'], $charPos, $fmtRuns[$i]['charPos'] - $charPos, 'UTF-8');
$charPos = $fmtRuns[$i]['charPos'];
} else {
$text = mb_substr($this->_sst[$index]['value'], $charPos, mb_strlen($this->_sst[$index]['value']), 'UTF-8');
}
if (mb_strlen($text) > 0) {
if ($i == 0) {
// first text run, no style
$richText->createText($text);
} else {
$textRun = $richText->createTextRun($text);
if (isset($fmtRuns[$i - 1])) {
if ($fmtRuns[$i - 1]['fontIndex'] < 4) {
$fontIndex = $fmtRuns[$i - 1]['fontIndex'];
} else {
// this has to do with that index 4 is omitted in all BIFF versions for some strange reason
// check the OpenOffice documentation of the FONT record
$fontIndex = $fmtRuns[$i - 1]['fontIndex'] - 1;
}
$textRun->setFont(clone $this->_objFonts[$fontIndex]);
}
}
}
}
} else {
$this->_phpSheet->setCellValueExplicit($columnString . ($row + 1), $this->_sst[$index]['value'], PHPExcel_Cell_DataType::TYPE_STRING);
}
// add style information
if (!$this->_readDataOnly) {
$this->_phpSheet->getCell($columnString . ($row + 1))->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
}
}
}
