public function generarExcel($condiciones) { set_time_limit(0); ini_set('memory_limit', '-1'); $AgenciaCargaDAO = new AgenciaCargaDAO(); $AgenciaCargaDAO->setEntityManager($this->getEntityManager()); //----------------Se configura las Etiquetas de Seleccion----------------- $texto_criterio_busqueda = ''; $texto_estado = 'TODOS'; $texto_sincronizado = 'TODOS'; if (!empty($condiciones['criterio_busqueda'])) { $texto_criterio_busqueda = $condiciones['criterio_busqueda']; } //end if switch ($condiciones['estado']) { case 'A': $texto_estado = 'ACTIVO'; break; case 'I': $texto_estado = 'INACTIVO'; break; } //end switch switch ($condiciones['sincronizado']) { case 'SINCRONIZADO': $texto_sincronizado = 'SINCRONIZADO'; break; case 'PENDIENTE': $texto_sincronizado = 'PENDIENTE'; break; } //end switch //----------------Se inicia la configuracion del PHPExcel----------------- $PHPExcelApp = new PHPExcelApp(); $objPHPExcel = new \PHPExcel(); // Set document properties $PHPExcelApp->setUserName(''); $PHPExcelApp->setMetaDataDocument($objPHPExcel); $objPHPExcel->setActiveSheetIndex(0); //Configura el tamaño del Papel $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(\PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(\PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); //Se establece la escala de la pagina $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1); $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0); //Se establece los margenes de la pagina $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.1); $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.1); $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.1); $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.1); //------------------------------Registra la cabecera-------------------------------- $row = 1; $col_ini = $PHPExcelApp->getNameFromNumber(0); $col_fin = $PHPExcelApp->getNameFromNumber(5); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, "Agencia Carga"); $objPHPExcel->getActiveSheet()->mergeCells($col_ini . $row . ':' . $col_fin . $row); $objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA)); $objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //------------------------------Registra criterios linea 1-------------------------- $row = 2; $col_ini = $PHPExcelApp->getNameFromNumber(0); $col_fin = $PHPExcelApp->getNameFromNumber(5); $objRichText = new \PHPExcel_RichText(); $objRichText->createText(''); $objInventario = $objRichText->createTextRun(' Criterio: '); $objInventario->getFont()->setBold(true); $objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN)); $objRichText->createText($texto_criterio_busqueda); $objInventario = $objRichText->createTextRun(' Estado: '); $objInventario->getFont()->setBold(true); $objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN)); $objRichText->createText($texto_estado); $objInventario = $objRichText->createTextRun(' Sincronizado: '); $objInventario->getFont()->setBold(true); $objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN)); $objRichText->createText($texto_sincronizado); $objPHPExcel->getActiveSheet()->getCell($col_ini . $row)->setValue($objRichText); $objPHPExcel->getActiveSheet()->mergeCells($col_ini . $row . ':' . $col_fin . $row); //------------------------------ Registro de Fecha de Generacion -------------------------------- $row = 3; $col_ini = $PHPExcelApp->getNameFromNumber(0); $col_fin = $PHPExcelApp->getNameFromNumber(5); //$etiqueta = ""; $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, "Generado: " . \Application\Classes\Fecha::getFechaHoraActualServidor()); $objPHPExcel->getActiveSheet()->mergeCells($col_ini . $row . ':' . $col_fin . $row); $objPHPExcel->getActiveSheet()->getStyle($col_ini . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA)); $objPHPExcel->getActiveSheet()->getStyle($col_ini . $row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); //---------------------------IMPRIME TITULO DE COLUMNA----------------------------- $row = $row + 1; $row_detalle_ini = $row; $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, "Nro"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, "Id"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, "Agencia"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, "Direccion"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, "Telefono"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, "Estado"); //----------------------AUTO DIMENSIONAR CELDAS DE ACUERDO AL CONTENIDO--------------- $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(0)->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(1)->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(2)->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(3)->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(4)->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(5)->setAutoSize(true); $objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA)); $objPHPExcel_getActiveSheet = $objPHPExcel->getActiveSheet(); //----------------------CONSULTA LOS REGISTROS A EXPORTAR--------------- $result = $this->listado($condiciones); $cont_linea = 0; foreach ($result as $reg) { $reg['nombre'] = trim($reg['nombre']); $reg['direccion'] = trim($reg['direccion']); $reg['telefono'] = trim($reg['telefono']); $cont_linea++; $row = $row + 1; $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $cont_linea); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, $reg['id']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, $reg['nombre']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, $reg['direccion']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, $reg['telefono']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, $reg['estado']); } // end foreach //Margenes $col_ini = $PHPExcelApp->getNameFromNumber(0); $col_fin = $PHPExcelApp->getNameFromNumber(5); $objPHPExcel->getActiveSheet()->getStyle($col_ini . $row_detalle_ini . ":" . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_BORDE_TODO)); // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle('Listado Agencias'); $PHPExcelApp->save($objPHPExcel, $PHPExcelApp::FORMAT_EXCEL_2007, "ListadoAgencias.xlsx"); }
public function testDataTypeForRichTextObject() { $objRichText = new PHPExcel_RichText(); $objRichText->createText('Hello World'); $expectedResult = PHPExcel_Cell_DataType::TYPE_INLINE; $result = call_user_func(array('PHPExcel_Cell_DefaultValueBinder', 'dataTypeForValue'), $objRichText); $this->assertEquals($expectedResult, $result); }
/** * 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); }
$objCommentRichText->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getComment('E12')->getText()->createTextRun("\r\n"); $objPHPExcel->getActiveSheet()->getComment('E12')->getText()->createTextRun('Total amount of VAT on the current invoice.'); $objPHPExcel->getActiveSheet()->getComment('E13')->setAuthor('PHPExcel'); $objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E13')->getText()->createTextRun('PHPExcel:'); $objCommentRichText->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getComment('E13')->getText()->createTextRun("\r\n"); $objPHPExcel->getActiveSheet()->getComment('E13')->getText()->createTextRun('Total amount on the current invoice, including VAT.'); $objPHPExcel->getActiveSheet()->getComment('E13')->setWidth('100pt'); $objPHPExcel->getActiveSheet()->getComment('E13')->setHeight('100pt'); $objPHPExcel->getActiveSheet()->getComment('E13')->setMarginLeft('150pt'); $objPHPExcel->getActiveSheet()->getComment('E13')->getFillColor()->setRGB('EEEEEE'); // Add rich-text string echo date('H:i:s'), " Add rich-text string", EOL; $objRichText = new PHPExcel_RichText(); $objRichText->createText('This invoice is '); $objPayable = $objRichText->createTextRun('payable within thirty days after the end of the month'); $objPayable->getFont()->setBold(true); $objPayable->getFont()->setItalic(true); $objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN)); $objRichText->createText(', unless specified otherwise on the invoice.'); $objPHPExcel->getActiveSheet()->getCell('A18')->setValue($objRichText); // Merge cells echo date('H:i:s'), " Merge cells", EOL; $objPHPExcel->getActiveSheet()->mergeCells('A18:E22'); $objPHPExcel->getActiveSheet()->mergeCells('A28:B28'); // Just to test... $objPHPExcel->getActiveSheet()->unmergeCells('A28:B28'); // Just to test... // Protect cells echo date('H:i:s'), " Protect cells", EOL;
/** * Read LABELSST record * This record represents a cell that contains a string. It * replaces the LABEL record and RSTRING record used in * BIFF2-BIFF5. * * -- "OpenOffice.org's Documentation of the Microsoft * Excel File Format" */ private function _readLabelSst() { $length = $this->_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; // offset: 0; size: 2; index to row $row = $this->_GetInt2d($recordData, 0); // offset: 2; size: 2; index to column $column = $this->_GetInt2d($recordData, 2); $columnString = PHPExcel_Cell::stringFromColumnIndex($column); // Read cell? if (!is_null($this->getReadFilter()) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle())) { // offset: 4; size: 2; index to XF record $xfIndex = $this->_GetInt2d($recordData, 4); // offset: 6; size: 4; index to SST record $index = $this->_GetInt4d($recordData, 6); // add cell if (($fmtRuns = $this->_sst[$index]['fmtRuns']) && !$this->_readDataOnly) { // then we should treat as rich text $richText = new PHPExcel_RichText($this->_phpSheet->getCell($columnString . ($row + 1))); $charPos = 0; for ($i = 0; $i <= count($this->_sst[$index]['fmtRuns']); ++$i) { if (isset($fmtRuns[$i])) { $text = mb_substr($this->_sst[$index]['value'], $charPos, $fmtRuns[$i]['charPos'] - $charPos, 'UTF-8'); $charPos = $fmtRuns[$i]['charPos']; } else { $text = mb_substr($this->_sst[$index]['value'], $charPos, mb_strlen($this->_sst[$index]['value']), 'UTF-8'); } if (mb_strlen($text) > 0) { if ($i == 0) { // first text run, no style $richText->createText($text); } else { $textRun = $richText->createTextRun($text); if (isset($fmtRuns[$i - 1])) { if ($fmtRuns[$i - 1]['fontIndex'] < 4) { $fontIndex = $fmtRuns[$i - 1]['fontIndex']; } else { // this has to do with that index 4 is omitted in all BIFF versions for some strange reason // check the OpenOffice documentation of the FONT record $fontIndex = $fmtRuns[$i - 1]['fontIndex'] - 1; } $textRun->setFont(clone $this->_objFonts[$fontIndex]); } } } } } else { $this->_phpSheet->setCellValueExplicit($columnString . ($row + 1), $this->_sst[$index]['value'], PHPExcel_Cell_DataType::TYPE_STRING); } // add style information if (!$this->_readDataOnly) { $this->_phpSheet->getCell($columnString . ($row + 1))->setXfIndex($this->_mapCellXfIndex[$xfIndex]); } } }
private function _parseRichText($is = '') { $value = new PHPExcel_RichText(); $value->createText(self::_convertStringEncoding($is, $this->_charSet)); return $value; }
require $dir."/db.php";//引入mysql操作类文件 require $dir."/PHPExcel/PHPExcel.php";//引入PHPExcel $objPHPExcel=new PHPExcel();//实例化PHPExcel类, 等同于在桌面上新建一个excel $objSheet=$objPHPExcel->getActiveSheet();//获得当前活动sheet /**插入图片代码开始**/ $objDrawing=new PHPExcel_WorkSheet_Drawing();//获得一个图片的操作对象 $objDrawing->setPath($dir."/image/imooc.jpg");//加载图片路径 $objDrawing->setCoordinates("F6");//设置图片插入位置的左上角坐标 $objDrawing->setWidth(500);//设置插入图片的大小 //$objDrawing->setHeight(100); //$objDrawing->setOffsetX(20)->setOffsetY(40);//设定单元格内偏移量 $objDrawing->setWorkSheet($objSheet);//将图片插入到sheet /**代码结束**/ /**添加丰富的文字块**/ $objRichText=new PHPExcel_RichText();//获得一个文字块操作对象 $objRichText->createText("慕课网(imooc)");//添加普通的文字 不能操作样式 $objStyleFont=$objRichText->createTextRun("是国内最大的IT技能免费培训平台");//生成可以添加样式的文字块 $objStyleFont->getFont()->setSize(16)->setBold(True)->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_GREEN));//加一些样式 $objRichText->createText(",课程丰富多样"); $objSheet->getCell("F4")->setValue($objRichText);//将文字块插入sheet里 /**代码结束**/ /**添加批注代码开始**/ $objSheet->mergeCells("F4:N4");//合并单元格 $objSheet->getComment("F4")->getText()->createTextRun("Van:\r\n慕课网\n\n时尚时尚最时尚");//添加批注 /**代码结束**/ /**超链接代码开始**/ $objSheet->setCellValue("I3","慕课网");//添加文字 $objSheet->getStyle("I3")->getFont()->setSize(16)->setBold(true)->setUnderline(true)->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_BLUE));//添加样式 $objSheet->getCell("I3")->getHyperlink()->setUrl("http://www.imooc.com");//给文字加上链接地址 /**代码结束**/
$activeSheet->getStyle('C2:G2')->applyFromArray($canhgiua)->applyFromArray($indam); $a_date = $val[0]->namlamthem . "-" . $key . "-01"; $tu = date("01/m/Y", strtotime($a_date)); $den = date("t/m/Y", strtotime($a_date)); $objRichText = new PHPExcel_RichText(); $objRichText->createText('Tháng ' . $key . ': '); $objBold = $objRichText->createTextRun($tu . ' - ' . $den); $objBold->getFont()->setItalic(true)->setName('Times New Roman')->setSize(13); $activeSheet->setCellValue('B5', $objRichText); unset($objBold); $activeSheet->setCellValue('B4', 'GIẤY BÁO LÀM THÊM GIỜ'); $activeSheet->mergeCells('B4:F4')->mergeCells('B5:F5'); $activeSheet->getStyle('B4:F4')->applyFromArray($canhgiua)->applyFromArray($indam); $activeSheet->getStyle('B5:F5')->applyFromArray($canhgiua); $objRichText = new PHPExcel_RichText(); $objRichText->createText('Họ và tên: '); $objBold = $objRichText->createTextRun($val[0]->tennhanvien); $objBold->getFont()->setBold(true)->setSize(13); $activeSheet->setCellValue('A7', $objRichText); unset($objBold); $activeSheet->setCellValue('A8', "Bộ phận công tác: Nhân viên phòng Phòng Phát triển phần mềm"); $activeSheet->setCellValue('A10', 'Ngày, tháng')->setCellValue('B10', "Những công việc đã làm")->setCellValue('C10', "Những công việc đã làm")->setCellValue('C11', 'Từ giờ')->setCellValue('D11', 'Đến giờ')->setCellValue('E11', 'Tổng giờ')->setCellValue('F11', 'Đơn giá')->setCellValue('G11', 'Thành tiền'); $activeSheet->mergeCells('A10:A11')->mergeCells('B10:B11')->mergeCells('C10:G10'); $activeSheet->getStyle('A10:A11')->applyFromArray($canhgiua)->applyFromArray($indam); $activeSheet->getStyle('B10:B11')->applyFromArray($canhgiua)->applyFromArray($indam); $activeSheet->getStyle('C10:G10')->applyFromArray($canhgiua)->applyFromArray($indam); $activeSheet->getStyle('C11:G11')->applyFromArray($canhgiua); $tongthoigian = 0; for ($i = 0; $i < count($val); $i++) { $stt = 12 + $i; $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);
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) { $objText = $value->createTextRun(PHPExcel_Shared_String::ControlCharacterOOXML2PHP((string) $run->t)); if (isset($run->rPr->rFont["val"])) { $objText->getFont()->setName($run->rPr->rFont["val"]); } if (isset($run->rPr->sz["val"])) { $objText->getFont()->setSize($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"]) && ($run->rPr->b["val"] == 'true' || $run->rPr->b["val"] == '1')) { $objText->getFont()->setBold(true); } if (isset($run->rPr->i["val"]) && ($run->rPr->i["val"] == 'true' || $run->rPr->i["val"] == '1')) { $objText->getFont()->setItalic(true); } if (isset($run->rPr->u["val"]) && ($run->rPr->u["val"] == 'true' || $run->rPr->u["val"] == '1')) { $objText->getFont()->setUnderline(true); } if (isset($run->rPr->strike["val"]) && ($run->rPr->strike["val"] == 'true' || $run->rPr->strike["val"] == '1')) { $objText->getFont()->setStriketrough(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; }
$objPHPExcel->getActiveSheet()->getStyle('C9')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2); $objPHPExcel->getActiveSheet()->setCellValue('A10', 'Date/Time')->setCellValue('B10', 'Time')->setCellValue('C10', PHPExcel_Shared_Date::PHPToExcel($dateTimeNow)); $objPHPExcel->getActiveSheet()->getStyle('C10')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4); $objPHPExcel->getActiveSheet()->setCellValue('A11', 'Date/Time')->setCellValue('B11', 'Date and Time')->setCellValue('C11', PHPExcel_Shared_Date::PHPToExcel($dateTimeNow)); $objPHPExcel->getActiveSheet()->getStyle('C11')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME); $objPHPExcel->getActiveSheet()->setCellValue('A12', 'NULL')->setCellValue('C12', NULL); $objRichText = new PHPExcel_RichText(); $objRichText->createText('你好 '); $objPayable = $objRichText->createTextRun('你 好 吗?'); $objPayable->getFont()->setBold(true); $objPayable->getFont()->setItalic(true); $objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN)); $objRichText->createText(', unless specified otherwise on the invoice.'); $objPHPExcel->getActiveSheet()->setCellValue('A13', 'Rich Text')->setCellValue('C13', $objRichText); $objRichText2 = new PHPExcel_RichText(); $objRichText2->createText("black text\n"); $objRed = $objRichText2->createTextRun("red text"); $objRed->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_RED)); $objPHPExcel->getActiveSheet()->getCell("C14")->setValue($objRichText2); $objPHPExcel->getActiveSheet()->getStyle("C14")->getAlignment()->setWrapText(true); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true); // Rename worksheet echo date('H:i:s'), " Rename worksheet", EOL; $objPHPExcel->getActiveSheet()->setTitle('Datatypes'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Save Excel 95 file echo date('H:i:s'), " Write to Excel5 format", EOL; $callStartTime = microtime(true); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
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"); }
/** * 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); }
$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'); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="' . $excelFileName . '.xlsx"');
protected function parseRichText($is = '') { $value = new PHPExcel_RichText(); $value->createText(self::convertStringEncoding($is, $this->charSet)); return $value; }
/** * * @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"); }
if (!empty($_REQUEST['pageNow'])) { $fenyePage->pageNow = $_REQUEST['pageNow']; } //调用getFenyePage,该方法可以把fenyePage完成 $nationService->getFenyePage($fenyePage); include '../Nation/nationFrontQuery.php'; } else { if ($action == "OutToExcel") { require_once './PHPExcel/PHPExcel.php'; require_once './PHPExcel/phpExcel/IOFactory.php'; require_once '../service/NationService.class.php'; $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); $objPHPExcel->setActiveSheetIndex(0); $objRichText = new PHPExcel_RichText(); $objRichText->createText(''); $objPayable = $objRichText->createTextRun('民族信息信息记录'); $objPayable->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_RED)); $objPayable->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('A1:B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A1:B1')->getFill()->getStartColor()->setARGB('00FFFF00'); // 底纹 $objPHPExcel->getActiveSheet()->mergeCells('A1:B1'); $objPHPExcel->getActiveSheet()->getCell('A1')->setValue($objRichText); $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true); // 加粗 $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14); // 字体大小 $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED); // 文本颜色
$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); $objRichText3 = new PHPExcel_RichText(); $objRichText3->createText("Hello "); $objUnderlined = $objRichText3->createTextRun("underlined"); $objUnderlined->getFont()->setUnderline(true); $objRichText3->createText(' World.'); $objPHPExcel->getActiveSheet()->getCell("C15")->setValue($objRichText3); $objPHPExcel->getActiveSheet()->setCellValue('A17', 'Hyperlink'); $objPHPExcel->getActiveSheet()->setCellValue('C17', 'www.phpexcel.net'); $objPHPExcel->getActiveSheet()->getCell('C17')->getHyperlink()->setUrl('http://www.phpexcel.net'); $objPHPExcel->getActiveSheet()->getCell('C17')->getHyperlink()->setTooltip('Navigate to website'); $objPHPExcel->getActiveSheet()->setCellValue('C18', '=HYPERLINK("mailto:abc@def.com","*****@*****.**")'); // 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
/** * * @param array $condiciones (inventario_id, proveedor_id, clasifica, color_ventas_id, calidad_variedad_id) */ public function generarExcel($condiciones) { set_time_limit(0); ini_set('memory_limit', '-1'); $InventarioDAO = new InventarioDAO(); $ProveedorDAO = new ProveedorDAO(); $CalidadDAO = new CalidadDAO(); $ColorVentasDAO = new ColorVentasDAO(); $CalidadVariedadDAO = new CalidadVariedadDAO(); $DispoDAO = new DispoDAO(); $InventarioDAO->setEntityManager($this->getEntityManager()); $ProveedorDAO->setEntityManager($this->getEntityManager()); $CalidadDAO->setEntityManager($this->getEntityManager()); $ColorVentasDAO->setEntityManager($this->getEntityManager()); $CalidadVariedadDAO->setEntityManager($this->getEntityManager()); $DispoDAO->setEntityManager($this->getEntityManager()); //----------------Se configura las Etiquetas de Seleccion----------------- $texto_inventario = 'TODOS'; $texto_proveedor = 'TODOS'; $texto_calidad = 'TODAS'; $texto_color_ventas = 'TODOS'; $texto_calidad_variedad = 'TODAS'; if (!empty($condiciones['inventario_id'])) { $InventarioData = $InventarioDAO->consultar($condiciones['inventario_id']); $texto_inventario = $InventarioData->getNombre(); } //end if if (!empty($condiciones['proveedor_id'])) { $ProveedorData = $ProveedorDAO->consultar($condiciones['proveedor_id']); $texto_proveedor = $ProveedorData->getNombre(); } //end if if (!empty($condiciones['clasifica'])) { $CalidadData = $CalidadDAO->consultarPorClasificaFox($condiciones['clasifica']); $texto_calidad = $CalidadData->getNombre(); } //end if if (!empty($condiciones['color_ventas_id'])) { $ColorVentasData = $ColorVentasDAO->consultar($condiciones['color_ventas_id']); $texto_color_ventas = $ColorVentasData->getNombre(); } //end if if (!empty($condiciones['calidad_variedad_id'])) { $CalidadVariedadData = $CalidadVariedadDAO->consultar($condiciones['calidad_variedad_id']); $texto_calidad_variedad = $CalidadVariedadData->getNombre(); } //end if //----------------Se inicia la configuracion del PHPExcel----------------- $PHPExcelApp = new PHPExcelApp(); $objPHPExcel = new \PHPExcel(); // Set document properties $PHPExcelApp->setUserName(''); $PHPExcelApp->setMetaDataDocument($objPHPExcel); //Se consultan los bunchs por fechas $result_fechas = $DispoDAO->listadoAgrupadoPorFechaBunch($condiciones); /* * Se añade el primer elemento en nulo en las fechas para indicar que * se obtendra toda la dispo de bunches en la primera pestaña del excel */ array_unshift($result_fechas, null); $indice_hoja = -1; foreach ($result_fechas as $reg_fecha) { if (empty($reg_fecha)) { $ind_fecha_dispo_final = count($result_fechas) - 1; $print_fecha_dispo = $result_fechas[1]['fecha_bunch'] . ' al ' . $result_fechas[$ind_fecha_dispo_final]['fecha_bunch']; } else { $print_fecha_dispo = $reg_fecha['fecha_bunch']; } //end if $indice_hoja++; switch ($indice_hoja) { case 0: //El primer elemento debe ser NULL de reg_fecha $condiciones_sin_fecha = $condiciones; $result = $this->listadoSinVacios($condiciones_sin_fecha); $titulo_hoja = 'Dispo General'; break; default: $condiciones_con_fecha = $condiciones; $condiciones_con_fecha['fecha_bunch'] = $reg_fecha['fecha_bunch']; $result = $this->listadoSinVacios($condiciones_con_fecha); $objPHPExcel->createSheet($indice_hoja); $titulo_hoja = $reg_fecha['fecha_bunch']; break; } //end switch $objPHPExcel->setActiveSheetIndex($indice_hoja); //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(13); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, "DISPONIBILIDAD GENERAL"); $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)); //------------------------------Registra criterios linea 1-------------------------- $row = 2; $col_ini = $PHPExcelApp->getNameFromNumber(0); $col_fin = $PHPExcelApp->getNameFromNumber(13); $objRichText = new \PHPExcel_RichText(); $objRichText->createText(''); $objInventario = $objRichText->createTextRun('Inventario: '); $objInventario->getFont()->setBold(true); $objInventario->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN)); $objRichText->createText($texto_inventario); $objCalidad = $objRichText->createTextRun(' Calidad: '); $objCalidad->getFont()->setBold(true); $objCalidad->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN)); $objRichText->createText($texto_calidad); $objProveedor = $objRichText->createTextRun(' Proveedor: '); $objProveedor->getFont()->setBold(true); $objProveedor->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN)); $objRichText->createText($texto_proveedor); $objColor = $objRichText->createTextRun(' Color: '); $objColor->getFont()->setBold(true); $objColor->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN)); $objRichText->createText($texto_color_ventas); $objCategoria = $objRichText->createTextRun(' Categoria: '); $objCategoria->getFont()->setBold(true); $objCategoria->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN)); $objRichText->createText($texto_calidad_variedad); $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(13); //$etiqueta = ""; $texto_fechas = "Fecha Flor: " . $print_fecha_dispo . " Generado: " . \Application\Classes\Fecha::getFechaHoraActualServidor(); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $texto_fechas); $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); //--------------------------------------------------------------------------------- $row = $row + 1; $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, " "); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, "Color"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, "40"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $row, "50"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $row, "60"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $row, "70"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $row, "80"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $row, "90"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $row, "100"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(12, $row, "110"); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(13, $row, "Total"); $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)->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()->getColumnDimensionByColumn(12)->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(13)->setWidth(6); $objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA)); $objPHPExcel_getActiveSheet = $objPHPExcel->getActiveSheet(); //$result = $this->listadoSinVacios($condiciones); //MORONITOR $totales['40'] = 0; $totales['50'] = 0; $totales['60'] = 0; $totales['70'] = 0; $totales['80'] = 0; $totales['90'] = 0; $totales['100'] = 0; $totales['110'] = 0; $totales['total'] = 0; $cont_linea = 0; foreach ($result as $reg) { $reg['variedad'] = trim($reg['variedad']); $reg['total'] = $reg['40'] + $reg['50'] + $reg['60'] + $reg['70'] + $reg['80'] + $reg['90'] + $reg['100'] + $reg['110']; //Array de Totales $totales['40'] = $totales['40'] + $reg['40']; $totales['50'] = $totales['50'] + $reg['50']; $totales['60'] = $totales['60'] + $reg['60']; $totales['70'] = $totales['70'] + $reg['70']; $totales['80'] = $totales['80'] + $reg['80']; $totales['90'] = $totales['90'] + $reg['90']; $totales['100'] = $totales['100'] + $reg['100']; $totales['110'] = $totales['110'] + $reg['110']; $totales['total'] = $totales['total'] + $reg['total']; $cont_linea++; $row = $row + 1; $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $cont_linea); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, $reg['variedad_id']); 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 if (!empty($reg['url_ficha'])) { $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, 'FOTO'); $objPHPExcel->getActiveSheet()->getCell('D' . $row)->getHyperlink()->setUrl($reg['url_ficha']); } else { $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, ''); } //end if $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, $reg['color_ventas_nombre']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, $reg['40']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $row, $reg['50']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $row, $reg['60']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $row, $reg['70']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $row, $reg['80']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $row, $reg['90']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $row, $reg['100']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(12, $row, $reg['110']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(13, $row, $reg['total']); //$objPHPExcel->getActiveSheet()->setCellValue('N'.$row,$reg['total'] ); } //end foreach //Totales $row = $row + 1; $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, ''); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, ''); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, 'TOTALES'); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, ''); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, ''); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, $totales['40']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $row, $totales['50']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $row, $totales['60']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $row, $totales['70']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $row, $totales['80']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $row, $totales['90']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $row, $totales['100']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(12, $row, $totales['110']); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(13, $row, $totales['total']); $objPHPExcel->getActiveSheet()->getStyle($col_ini . $row . ':' . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_NEGRILLA)); //Formato de Numeros $col_ini = $PHPExcelApp->getNameFromNumber(5); $col_fin = $PHPExcelApp->getNameFromNumber(13); $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(13); $objPHPExcel->getActiveSheet()->getStyle($col_ini . $row_detalle_ini . ":" . $col_fin . $row)->applyFromArray($PHPExcelApp->getStyleArray($PHPExcelApp::STYLE_ARRAY_BORDE_TODO)); // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle($titulo_hoja); } //end foreach $objPHPExcel->setActiveSheetIndex(0); $PHPExcelApp->save($objPHPExcel, $PHPExcelApp::FORMAT_EXCEL_2007, "Dispo General.xlsx"); }
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); }
$objPHPExcel->getActiveSheet()->mergeCells("C{$cell_row}:E{$cell_row}"); $VendorPerson = stripslashes(utf8_encode($VendorPerson)); $objPHPExcel->getActiveSheet()->setCellValue("C{$cell_row}", "{$VendorPerson}"); //Contact Value Formatting $objPHPExcel->getActiveSheet()->getStyle("A{$cell_row}")->getFont()->setName('Calibri'); $objPHPExcel->getActiveSheet()->getStyle("A{$cell_row}")->getFont()->setSize(11); $objPHPExcel->getActiveSheet()->getStyle("A{$cell_row}:E{$cell_row}")->applyFromArray($bordersAllsides); //Terms Header $cell_row += 1; $objPHPExcel->getActiveSheet()->mergeCells("C{$cell_row}:E{$cell_row}"); //Bold Terms Header $objRichText = new PHPExcel_RichText(); $objBold = $objRichText->createTextRun("TERMS: "); $objBold->getFont()->setBold(true); $VendorTerms = utf8_encode($VendorTerms); $objRichText->createText("{$VendorTerms}"); $objPHPExcel->getActiveSheet()->getCell("C{$cell_row}")->setValue($objRichText); //Terms Header Formatting $objPHPExcel->getActiveSheet()->getStyle("C{$cell_row}")->getFont()->setName('Calibri'); $objPHPExcel->getActiveSheet()->getStyle("C{$cell_row}")->getFont()->setSize(11); $objPHPExcel->getActiveSheet()->getStyle("C{$cell_row}")->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle("C{$cell_row}:E{$cell_row}")->applyFromArray($bordersAllsides); //Include PO Details Header $cell_row += 1; $objPHPExcel->setActiveSheetIndex(0)->setCellValue("A{$cell_row}", "ITEM")->setCellValue("B{$cell_row}", "DESCRIPTION")->setCellValue("C{$cell_row}", "QTY")->setCellValue("D{$cell_row}", "PRICE")->setCellValue("E{$cell_row}", "AMOUNT"); //Header Formatting $objPHPExcel->getActiveSheet()->getStyle("A{$cell_row}:E{$cell_row}")->getFont()->setName('Calibri'); $objPHPExcel->getActiveSheet()->getStyle("A{$cell_row}:E{$cell_row}")->getFont()->setSize(11); $objPHPExcel->getActiveSheet()->getStyle("A{$cell_row}:E{$cell_row}")->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle("A{$cell_row}:E{$cell_row}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle("A{$cell_row}:E{$cell_row}")->applyFromArray($bordersAllsides);
// offset: 3; size: var; list of ($nc + 1) * ($nr + 1) constant values $matrixChunks = array(); for ($r = 1; $r <= $nr + 1; ++$r) { $items = array(); for ($c = 1; $c <= $nc + 1; ++$c) { $constant = self::_readBIFF8Constant($arrayData);
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; }
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; }
$objPHPExcel->getActiveSheet()->getStyle('B8:C8')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('B8:C8')->getFill()->getStartColor()->setARGB('FF808080'); //-----------------------------------设置边框------------------------/ $styleThinBlackBorderOutline = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000')))); $objPHPExcel->getActiveSheet()->getStyle('C9:D10')->applyFromArray($styleThinBlackBorderOutline); //----------------设置单元格宽度--------------------------------------/ //自动 $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); //指定宽度 $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(35); //---------------合并表格(基本上已经满足所有)--------------------------/ $objPHPExcel->getActiveSheet()->mergeCells('F2:J2'); //------------------------表格文字样式相关-----------------------------/ $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)); $objPHPExcel->getActiveSheet()->setCellValue('B2', $objRichText); //------------------------设置头样式,特别适合表头------------------------/ $objPHPExcel->getActiveSheet()->getStyle('A13:E13')->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')))); //---------------------------------------------------------------------/ //--------------------------表名称转换---------------------------------/ $fileName = iconv("gbk", "utf-8", $fileName); //---------------------------工作表区域重命名表---------------------------------/