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"); }
protected function getBold($value) { $objRichText = new PHPExcel_RichText(); $objPayable = $objRichText->createTextRun($value); $objPayable->getFont()->setBold(true); return $objRichText; }
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}"); }
/** * Добавляет таблицу со списком новостей * * @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()); }
/** * 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}"); }
$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; $activeSheet->setCellValue('A' . $stt, $val[$i]->ngaylamthem)->setCellValue('B' . $stt, $val[$i]->congvieclamthem)->setCellValue('C' . $stt, $val[$i]->timebatdau)->setCellValue('D' . $stt, $val[$i]->timeketthuc)->setCellValue('E' . $stt, $val[$i]->thoigian); $tongthoigian += $val[$i]->thoigian;
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");//给文字加上链接地址 /**代码结束**/
/** * @param $params * @return array */ protected function headers($params) { $col = array(); $wColumn = 'A'; $last = ''; $wRows = $params['skip_line']; $styleHeader = array(); foreach ($params['fields'] as $field) { if (in_array($field, $params['skip_fields']['export'])) { continue; } $col[] = $this->translator->trans('ApplicationSonataClientOperationsBundle.list.' . $params['entity'] . '.' . $field); if ($params['entity'] == 'V05LIC' || $params['entity'] == 'A06AIB') { if (!isset($styleHeader[$field]) && ($field == 'regime' || $field == 'DEB')) { $styleHeader[$field] = array('fill' => array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'b6dde8'))); } } if ($params['entity'] == 'DEBIntro' || $params['entity'] == 'DEBExped') { if (in_array($field, array('n_ligne', 'nomenclature', 'pays_destination', 'regime', 'masse_mette', 'unites_supplementaires', 'nature_transaction', 'conditions_livraison', 'mode_transport', 'departement', 'pays_origine', 'CEE'))) { $styleHeader[$field] = array('fill' => array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => '000000')), 'font' => array('bold' => true, 'color' => array('argb' => 'ffffff'))); } elseif ($field == 'valeur_fiscale' || $field == 'valeur_statistique') { $styleHeader[$field] = array('fill' => array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'ffff00')), 'font' => array('bold' => true, 'color' => array('argb' => 'ff3900'))); } $this->_sheet->getStyle($wColumn . $wRows)->getBorders()->getTop()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_WHITE)); $this->_sheet->getStyle($wColumn . $wRows)->getBorders()->getLeft()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_WHITE)); } $this->_sheet->getStyle($wColumn . $wRows)->applyFromArray($this->getCustomStyleBorders($params, $field, 'header') + (isset($styleHeader[$field]) ? $styleHeader[$field] : array()))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setWrapText(true); $this->_sheet->getStyle($wColumn . $wRows)->getFont()->setBold(true); $this->setWidthSize($wColumn, $field, $params); // $this->_sheet->getColumnDimension($wColumn)->setAutoSize(true); $last = $wColumn; $wColumn++; } if ($params['entity'] == 'DEBIntro' || $params['entity'] == 'DEBExped') { $index = 1; $objRichText = new \PHPExcel_RichText(); $objPayable = $objRichText->createTextRun("DECLARATION D'ECHANGES DE BIENS ENTRE ETATS MEMBRES DE LA C.E.E. (DEB)"); $objPayable->getFont()->setName('Arial')->setBold(true)->setSize(10); $this->_sheet->getCell('A' . $index)->setValue($objRichText); $this->_sheet->mergeCells('A' . $index . ':' . $last . $index); $this->_sheet->getStyle('A' . $index . ':' . $last . $index)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->_sheet->getRowDimension(1)->setRowHeight(25); $this->_sheet->getStyle('A' . $index . ':' . $last . $index)->getFont()->setBold(true); $index = 3; $objRichText = new \PHPExcel_RichText(); $objPayable = $objRichText->createTextRun("FLUX"); $objPayable->getFont()->setName('Arial')->setBold(true)->setSize(10); $this->_sheet->getCell('A' . $index)->setValue($objRichText); $this->_sheet->mergeCells('A' . $index . ':' . $last . $index); $this->_sheet->getStyle('A' . $index . ':' . $last . $index)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->_sheet->getStyle('A' . $index . ':' . $last . $index)->applyFromArray($this->_styleBorders); $this->_sheet->getRowDimension($index)->setRowHeight(25); $this->_sheet->getStyle('A' . $index . ':' . $last . $index)->getFont()->setBold(true); $index = 4; $objRichText = new \PHPExcel_RichText(); $objPayable = $objRichText->createTextRun($params['entity'] == 'DEBExped' ? "EXPEDITION" : 'INTRODUCTION'); $objPayable->getFont()->setName('Arial')->setSize(10); $this->_sheet->getCell('A' . $index)->setValue($objRichText); $this->_sheet->mergeCells('A' . $index . ':' . $last . $index); $this->_sheet->getStyle('A' . $index . ':' . $last . $index)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->_sheet->getStyle('A' . $index . ':' . $last . $index)->applyFromArray($this->_styleBorders); $this->_sheet->getRowDimension($index)->setRowHeight(25); $this->_sheet->getStyle('A' . $index . ':' . $last . $index)->getFont()->setBold(true); $wColumn = 'A'; $index = 6; foreach ($params['fields'] as $key => $field) { $this->_sheet->getCell($wColumn . $index)->setValue($key + 1); $this->_sheet->getStyle($wColumn . $index)->applyFromArray($this->_styleBorders + array('fill' => array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => '000000')), 'font' => array('bold' => true, 'color' => array('argb' => 'ffffff')))); $this->_sheet->getStyle($wColumn . $index)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->_sheet->getStyle($wColumn . $index)->getBorders()->getLeft()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_WHITE)); $wColumn++; } } $this->setRowHeight($wRows, $params); //autofilter $this->_sheet->setAutoFilter('A' . $wRows . ':' . $last . $wRows); return $col; }
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"); }
} /** * Read VERTICALPAGEBREAKS record */ private function _readVerticalPageBreaks() { $length = self::_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; if ($this->_version == self::XLS_BIFF8 && !$this->_readDataOnly) { // offset: 0; size: 2; number of the following column index structures $nm = self::_GetInt2d($recordData, 0); // offset: 2; size: 6 * $nm; list of $nm row index structures for ($i = 0; $i < $nm; ++$i) { $c = self::_GetInt2d($recordData, 2 + 6 * $i); $rf = self::_GetInt2d($recordData, 2 + 6 * $i + 2); $rl = self::_GetInt2d($recordData, 2 + 6 * $i + 4); // not sure why two row indexes are necessary? $this->_phpSheet->setBreakByColumnAndRow($c, $rf, PHPExcel_Worksheet::BREAK_COLUMN); } } } /** * Read HEADER record */ private function _readHeader() { $length = self::_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; if (!$this->_readDataOnly) { // offset: 0; size: var // realized that $recordData can be empty even when record exists if ($recordData) { if ($this->_version == self::XLS_BIFF8) { $string = self::_readUnicodeStringLong($recordData); } else { $string = $this->_readByteStringShort($recordData); } $this->_phpSheet->getHeaderFooter()->setOddHeader($string['value']); $this->_phpSheet->getHeaderFooter()->setEvenHeader($string['value']); } } } /** * Read FOOTER record */ private function _readFooter() { $length = self::_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; if (!$this->_readDataOnly) { // offset: 0; size: var // realized that $recordData can be empty even when record exists if ($recordData) { if ($this->_version == self::XLS_BIFF8) { $string = self::_readUnicodeStringLong($recordData); } else {
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); }
/** * 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(); } }
//тут йде переприсвоєяння значень $_SESSION['firstGroupVidnPosition_'.$i] щоб //відлік починався з індексу 1 // for ($i = 1; $i <= $_SESSION['num_group']; $i++) { $m = $i + 1; if ($m <= $_SESSION['num_group']) { $_SESSION['wr_firstGroupVidnPosition_' . $i] = $_SESSION['wr_firstGroupVidnPosition_' . $m]; $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $_SESSION['wr_firstGroupVidnPosition_' . $i], 'Важливість груп'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $_SESSION['wr_firstGroupVidnPosition_' . $i], $_SESSION['r_vidn_gr_' . $i]); } } $resPosition = $_SESSION['newPosition']; //$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$resPosition, 'Результати'); /////// $objRichText = new PHPExcel_RichText(); $objPayable = $objRichText->createTextRun('Результати'); $objPayable->getFont()->setBold(true); $objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN)); $objPHPExcel->getActiveSheet()->getCell('A' . $resPosition)->setValue($objRichText); //////////// $resPosition += 1; $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $resPosition, 'Стан підприємства'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $resPosition, $_SESSION['stan']); //$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$resPosition, $_SESSION['newPosition']); $resPosition += 2; //$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$resPosition, $resPosition); for ($i = 1; $i <= $_SESSION['num_group']; $i++) { //$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$resPosition, $resPosition); $text = 'Група ' . $_SESSION['name_gr_' . $i] . ' має рівень'; $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $resPosition, $text); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $resPosition, $_SESSION['level_' . $i]);
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; }
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; }
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; }
$objRichText = new PHPExcel_RichText(); $objBold = $objRichText->createTextRun('THỨ TRONG TUẦN:'); $objBold->getFont()->setBold(true); $objRichText->createText(' Đánh dấu X vào ngày làm thực công việc đó'); $activeSheet->setCellValue('D' . ($count + 9 + 11), $objRichText); $activeSheet->mergeCells('D' . ($count + 9 + 11) . ':O' . ($count + 9 + 11)); unset($objBold); $objRichText = new PHPExcel_RichText(); $objBold = $objRichText->createTextRun('Ý KIẾN/ĐỀ XUẤT:'); $objBold->getFont()->setBold(true); $objRichText->createText(' Ghi ý kiến đề xuất giải quyết công việc nếu có'); $activeSheet->setCellValue('D' . ($count + 9 + 12), $objRichText); $activeSheet->mergeCells('D' . ($count + 9 + 12) . ':O' . ($count + 9 + 12)); unset($objBold); $objRichText = new PHPExcel_RichText(); $objBold = $objRichText->createTextRun('TRƯỞNG PHÒNG ĐÁNH GIÁ:'); $objBold->getFont()->setBold(true); $objRichText->createText(' Dành cho Lãnh đạo phòng Ghi nội dung đánh giá công việc'); $activeSheet->setCellValue('D' . ($count + 9 + 13), $objRichText); $activeSheet->mergeCells('D' . ($count + 9 + 13) . ':O' . ($count + 9 + 13)); /** * start export */ ob_end_clean(); $str = $arr[0]->tennhanvien; $arr = explode(' ', $str); $tennv = $arr[count($arr) - 1]; for ($i = 0; $i <= count($arr) - 2; $i++) { $tennv .= $arr[$i][0]; } $excelFileName = 'Báo cáo công việc tuần - ' . $tennv . '_' . date('dmY');
/** * Spreadsheet mit Daten befüllen * * @param $phpExcel PHPExcel-Objekt */ private function xlsData(&$phpExcel) { global $LANG; $sheet = $phpExcel->getActiveSheet(); $sheet->setTitle($LANG->getLL('workbook_title')); $data = $this->getDbData(); $col = 0; $row = 0; // Kopf foreach ($this->export_cols as $spalte => $breite) { $sheet->getCellByColumnAndRow($col, 1)->setValueExplicit($LANG->getLL('col_' . $spalte), PHPExcel_Cell_DataType::TYPE_STRING); $col++; } $sheet->getStyle('A1:' . PHPExcel_Cell::stringFromColumnIndex(count($this->export_cols) - 1) . '1')->applyFromArray($this->format_head); // Daten foreach ($data as $row => $dataRow) { $col = 0; foreach ($this->export_cols as $dbName => $breite) { // je nach Typ als String, Integer oder Formel // wenn der Wert leer ist, dann natürlich gar nix if (isset($dataRow[$dbName])) { // Textauszug hat Sonderbehandlung if ($dbName == 'excerpt') { $arr = $this->getTextExcerpt($dataRow['id']); if (is_array($arr) && count($arr)) { $content = new PHPExcel_RichText(); $content->createText('...' . $arr['before'] . ' '); if (!empty($arr['comment'])) { $selection = $content->createTextRun($arr['comment']); $selection->getFont()->setBold(true); $selection->getFont()->setItalic(true); $selection->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_RED)); } $content->createText(' ' . $arr['after'] . '...'); $sheet->getCellByColumnAndRow($col, $row + 2)->setValue($content); } $col++; continue; } elseif ($dbName == 'timestamp') { $content = date('d.m.Y H:i', $dataRow[$dbName]); } else { $content = $dataRow[$dbName]; } $dataType = is_numeric($content) ? PHPExcel_Cell_DataType::TYPE_NUMERIC : PHPExcel_Cell_DataType::TYPE_STRING; } elseif ($dbName == 'avg') { $content = '=$F' . ($row + 2) . '-$G' . ($row + 2); $dataType = PHPExcel_Cell_DataType::TYPE_FORMULA; } else { $col++; continue; } $sheet->getCellByColumnAndRow($col, $row + 2)->setValueExplicit($content, $dataType); $col++; } } // Spaltenbreiten $col = 0; foreach ($this->export_cols as $spalte => $breite) { if ($breite == 'auto') { $sheet->getColumnDimensionByColumn($col)->setAutoSize(true); } else { $sheet->getColumnDimensionByColumn($col)->setWidth($breite); } $col++; } // Formatierung Datenpart $sheet->getStyle('A2:' . PHPExcel_Cell::stringFromColumnIndex(count($this->export_cols) - 1) . (count($data) + 1))->applyFromArray($this->format_body); $sheet->getStyle('A1:' . PHPExcel_Cell::stringFromColumnIndex(count($this->export_cols) - 1) . (count($data) + 1))->getAlignment()->setWrapText(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 echo date('H:i:s'), " Protect cells", EOL; $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
/** * * @param array $condiciones (criterio_busqueda, busqueda_color , busqueda_estado) */ public function generarExcel($condiciones) { set_time_limit(0); ini_set('memory_limit', '-1'); $ClienteDAO = new ClienteDAO(); $ClienteDAO->setEntityManager($this->getEntityManager()); //----------------Se configura las Etiquetas de Seleccion----------------- $texto_criterio_busqueda = 'TODOS'; $texto_estado = 'TODOS'; if (!empty($condiciones['criterio_busqueda'])) { $texto_criterio_busqueda = $condiciones['criterio_busqueda']; } //end if switch ($condiciones['busqueda_estado']) { case 'A': $texto_estado = 'ACTIVO'; break; case 'I': $texto_estado = 'INACTIVO'; 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(10); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, "LISTADO DE CLIENTES"); $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(11); $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); $objCalidad = $objRichText->createTextRun(' Estado: '); $objCalidad->getFont()->setBold(true); $objCalidad->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN)); $objRichText->createText($texto_estado); $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(10); //$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); //-------------------------ESTABLECE TITULO DE COLUMNAS---------------------------- $row = $row + 1; $row_detalle_ini = $row; $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, "Nro"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, "Id"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, "Nombre"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, "Direccion"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, "Pais"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, "Estado"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $row, "Cod. Postal"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $row, "Telefono"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $row, "Fax"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $row, "Email"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $row, "Estado"); //-------------------------ESTABLECE ANCHO DE COLUMNAS---------------------------- $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()->getColumnDimensionByColumn(6)->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(7)->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(8)->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(9)->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(10)->setAutoSize(true); $objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA)); $objPHPExcel_getActiveSheet = $objPHPExcel->getActiveSheet(); $result = $this->listado($condiciones); $cont_linea = 0; foreach ($result as $reg) { $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['pais_nombre']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, $reg['nombre_estados']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $row, $reg['codigo_postal']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $row, $reg['telefono1']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $row, $reg['fax1']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $row, $reg['email']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $row, $reg['estado']); } //end foreach //Margenes $col_ini = $PHPExcelApp->getNameFromNumber(0); $col_fin = $PHPExcelApp->getNameFromNumber(10); $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 Clientes'); $PHPExcelApp->save($objPHPExcel, $PHPExcelApp::FORMAT_EXCEL_2007, "Listado_Clientes.xlsx"); }
$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 echo date('H:i:s'), " Write to Excel2007 format", EOL; $callStartTime = microtime(true);
/** * 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() { $pos = $this->_pos; $length = $this->_GetInt2d($this->_data, $pos + 2); $recordData = substr($this->_data, $pos + 4, $length); $pos += 4; // offset: 0; size: 2; index to row $row = $this->_GetInt2d($this->_data, $pos); // offset: 2; size: 2; index to column $column = $this->_GetInt2d($this->_data, $pos + 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($this->_data, $pos + 4); // offset: 6; size: 4; index to SST record $index = $this->_GetInt4d($this->_data, $pos + 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->getFont()->applyFromArray($this->_fonts[$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->getStyle($columnString . ($row + 1))->applyFromArray($this->_xf[$xfindex]); } } // move stream pointer to next record $this->_pos += 4 + $length; }
private function generateadvchecklist($advlist) { //Set column dimensions $advlist->getColumnDimension('A')->setWidth(65); for ($i = 'B'; $i < 'Q'; $i++) { $advlist->getColumnDimension("{$i}")->setWidth(6.5); } //Set Styles for cells $advlist->getStyle("A1:P14")->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $advlist->getStyle("A1:P14")->getFont()->setBold(true); $advlist->getStyle("A1:P2")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $advlist->getStyle("A1:P2")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $advlist->getStyle("A1:A14")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); //Set background for header cells $advlist->getStyle("B1:P2")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('EEEEEE'); $advlist->getStyle("A3:A14")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('EEEEEE'); //Set up top row (header) cells $advlist->mergeCells("A1:A2"); for ($y = 0, $fc = 'B', $lc = 'D'; $y < 5; $y++) { $advlist->mergeCells("{$fc}" . '1:' . "{$lc}" . '1'); $advlist->getCell("{$fc}" . '2')->setValue("F"); $fc++; $lc++; $advlist->getCell("{$fc}" . '2')->setValue("W"); $fc++; $lc++; $advlist->getCell("{$fc}" . '2')->setValue("Sp"); $fc++; $lc++; } //Set text for row titles (Should be hard coded) $advlist->getStyle("A1:A14")->getAlignment()->setWrapText(true); $advlist->getCell("A14")->SetValue("The student has been released on BOSS or CICS screen 7R3."); $advlist->getCell("A1")->setValue("Put your initials in the appropriate cell when completed"); $advlist->getCell("A3")->SetValue("The term, year, and grade of courses already taken have been updated on the check sheet."); //Style needed for EVERY (9) following rich text run $fontstyle = new PHPExcel_Style_Font(); $fontstyle->setName("Arial")->setSize(10)->setBold(true); //Cell A4 -- A lot of code to make a single word green $text = new PHPExcel_RichText(); $run1 = $text->createTextRun("The term and year of current (ongoing) courses have been highlighted in "); $run1->setFont($fontstyle); $run2 = $text->createTextRun("green"); $run2->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_GREEN))->setName("Arial")->setSize(10)->setBold(true); $run3 = $text->createTextRun(" on the check sheet."); $run3->setFont($fontstyle); $advlist->getCell("A4")->SetValue($text); //Cell A5 -- A lot of code to make a single word yellow $text = new PHPExcel_RichText(); $run1 = $text->createTextRun("The term and year of courses scheduled for the next term are highlighted in "); $run1->setFont($fontstyle); $run2 = $text->createTextRun("yellow"); $run2->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_YELLOW))->setName('Arial')->setSize(10)->setBold(true); $run3 = $text->createTextRun(" on the check sheet."); $run3->setFont($fontstyle); $advlist->getCell("A5")->SetValue($text); //Cell A6 -- A lot of code to make a single word red $text = new PHPExcel_RichText(); $run1 = $text->createTextRun("Problems have been highlighted in "); $run1->setFont($fontstyle); $run2 = $text->createTextRun("red"); $run2->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_RED))->setName('Arial')->setSize(10)->setBold(true); $run3 = $text->createTextRun(" on the check sheet and have been discussed with the student."); $run3->setFont($fontstyle); $advlist->getCell("A6")->SetValue($text); $advlist->getCell("A7")->SetValue("All course substitutions have been approved either in the system (BOSS/CICS) or by the Program Chair."); $advlist->getCell("A8")->SetValue("The student has taken all COES prerequisites (and earned a 'C' or better) for all COES courses on the advisement sheet."); $advlist->getCell("A9")->SetValue("The student was informed of the requirement for a minimum 2.0 GPA on all CSC courses, including all attempts."); $advlist->getCell("A10")->SetValue("The student was informed of the requirement for a minimum 2.0 GPA on the MATH 240 series, including all attempts."); $advlist->getCell("A11")->SetValue("A sanity check was done to ensure that the student doesn't get into trouble with once-a-year classes and is subsequently unduly delayed."); $advlist->getCell("A12")->SetValue("All deviances have been documented on a completed petition (one copy has been put in the Program Chair's box, and one copy has been sent to the Associate Dean of Undergraduate Studies)."); $advlist->getCell("A13")->SetValue("The check sheet has been uploaded/copied to the cloud space."); $advlist->getCell("A14")->SetValue("The student has been released on BOSS or CICS screen 7R3."); //Set row heights // There's an issue with excel // A row can't have autosized height, so it must be hard coded $advlist->getRowDimension(3)->setRowHeight(14 * 2); $advlist->getRowDimension(4)->setRowHeight(14 * 2); $advlist->getRowDimension(5)->setRowHeight(14 * 2); $advlist->getRowDimension(6)->setRowHeight(14 * 2); $advlist->getRowDimension(7)->setRowHeight(14 * 2); $advlist->getRowDimension(8)->setRowHeight(14 * 2); $advlist->getRowDimension(9)->setRowHeight(14 * 2); $advlist->getRowDimension(10)->setRowHeight(14 * 2); $advlist->getRowDimension(11)->setRowHeight(14 * 2); $advlist->getRowDimension(12)->setRowHeight(14 * 3); $advlist->getRowDimension(13)->setRowHeight(14 * 1); $advlist->getRowDimension(14)->setRowHeight(14 * 1); }
$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); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save(str_replace('.php', '.xls', __FILE__));
/** * 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; }
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; }
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; }
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; }
function Excel_ColocaMeses($objeto, $columna_inicial, $fila) { global $meses; global $columnas_excel; foreach ($meses as $mes) { $columna = array_search($columna_inicial, $columnas_excel); $columna = $columnas_excel[$columna]; $objeto->getColumnDimension($columna)->setWidth(30); $objRichText = new PHPExcel_RichText(); $objPayable = $objRichText->createTextRun(strtoupper($mes)); $objPayable->getFont()->setBold(true); $objeto->setCellValue($columna . $fila, $objRichText); $columna_inicial++; } }