/** * Create a new PHPExcel_Writer_HTML * * @param PHPExcel $phpExcel PHPExcel object */ public function __construct(PHPExcel $phpExcel) { $this->_phpExcel = $phpExcel; $this->_defaultFontSize = $this->_phpExcel->getDefaultStyle()->getFont()->getSize(); $this->_sheetIndex = 0; $this->_imagesRoot = '.'; }
/** * Set default style - should only be used by PHPExcel_IReader * implementations! * * @deprecated * * @param PHPExcel_Style $value * @throws Exception * @return PHPExcel_Worksheet */ public function setDefaultStyle(PHPExcel_Style $pValue) { $this->_parent->getDefaultStyle ()->applyFromArray ( array ( 'font' => array ( 'name' => $pValue->getFont ()->getName (), 'size' => $pValue->getFont ()->getSize () ) ) ); return $this; }
private function getPHPExcelObject() { $excelWriter = new PHPExcel(); $excelWriter->setActiveSheetIndex(0); /* set HORIZONTAL_CENTER for the whole sheet */ $excelWriter->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); /* set VERTICAL_CENTER for the whole sheet */ $excelWriter->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); return $excelWriter; }
/** * */ public function render() { $excel = new \PHPExcel(); $excel->getDefaultStyle()->getFont()->setName('Arial'); $excel->getDefaultStyle()->getFont()->setSize(10); $this->_sheet = $excel->getActiveSheet(); $this->_sheet->setTitle($this->_tabs_title); $this->_sheet->getTabColor()->setARGB('FFc3e59e'); $this->_sheet->fromArray($this->makeExportData()); //resize $this->excelWidth(); //height header header('Content-Type: application/excel'); header('Content-Disposition: attachment; filename="' . $this->getFile() . '"'); header('Cache-Control: max-age=0'); $writer = new \PHPExcel_Writer_Excel2007($excel); $writer->save('php://output'); }
public function export() { spl_autoload_unregister(array('YiiBase', 'autoload')); $phpExcelPath = Yii::getPathOfAlias('application.vendors'); include $phpExcelPath . DIRECTORY_SEPARATOR . 'PHPExcel.php'; $objPHPExcel = new PHPExcel(); $objPHPExcel->setActiveSheetIndex(0); // Set style for column name $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial'); $objPHPExcel->getDefaultStyle()->getFont()->setSize(10); $rows = 1; $totalColumn = count($this->attribute); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $rows, $this->title)->mergeCellsByColumnAndRow(0, 1, $totalColumn - 1, 1); $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, $rows)->getFont()->setBold(true)->setSize(14); $rows++; $i = 0; foreach ($this->attribute as $key => $value) { $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i, $rows, $value); $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($i, $rows)->getFont()->setBold(true)->setSize(10); $i++; } $rows++; for ($i = 0; $i <= $totalColumn; $i++) { $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($i)->setAutoSize(true); } foreach ($this->dataSoure as $data) { $i = 0; foreach ($this->attribute as $key => $value) { $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i, $rows, $data[$key]); $i++; } $rows++; } $fileName = $this->title . "-" . date('Ymd', time()) . '.xls'; header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="' . $fileName . '"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); Yii::app()->end(); spl_autoload_register(array('YiiBase', 'autoload')); }
/** * Dumps the stats table * @param StatsTable $statsTable * @return string */ public function dump(StatsTable $statsTable) { $excel = new \PHPExcel(); $excel->getDefaultStyle()->applyFromArray($this->getDefaultStyleArray()); $sheet = $excel->getSheet(); $row = 1; $data = $statsTable->getData(); $width = count(reset($data)); // HEADERS // if ($this->enableHeaders) { $headerStyle = new \PHPExcel_Style(); $headerStyle->applyFromArray($this->getHeadersStyleArray()); $col = 0; foreach ($statsTable->getHeaders() as $header) { $sheet->setCellValueByColumnAndRow($col, $row, $header); $col++; } $sheet->duplicateStyle($headerStyle, 'A1:' . \PHPExcel_Cell::stringFromColumnIndex($width - 1) . '1'); $row++; } // DATA // foreach ($statsTable->getData() as $data) { $this->applyValues($sheet, $row, $data, $statsTable->getDataFormats()); $row++; } // AGGREGATIONS // if ($this->enableAggregation) { $this->applyValues($sheet, $row, $statsTable->getAggregations(), $statsTable->getAggregationsFormats(), $this->getAggregationsStyleArray()); } // FINAL FORMATTING // for ($col = 0; $col < $width; $col++) { $sheet->getColumnDimension(\PHPExcel_Cell::stringFromColumnIndex($col))->setAutoSize(true); } $xlsDumper = new \PHPExcel_Writer_Excel2007($excel); $pFilename = @tempnam(\PHPExcel_Shared_File::sys_get_temp_dir(), 'phpxltmp'); $xlsDumper->save($pFilename); $contents = file_get_contents($pFilename); @unlink($pFilename); unset($excel); unset($xlsDumper); return $contents; }
function array_to_spreadsheet($arr) { $CI =& get_instance(); PHPExcel_Shared_File::setUseUploadTempDirectory(true); $objPHPExcel = new PHPExcel(); //Default all cells to text $objPHPExcel->getDefaultStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); for ($k = 0; $k < count($arr); $k++) { for ($j = 0; $j < count($arr[$k]); $j++) { $objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($j, $k + 1, $arr[$k][$j]); } } if ($CI->config->item('spreadsheet_format') == 'XLSX') { $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); } else { $objWriter = new PHPExcel_Writer_CSV($objPHPExcel); } ob_start(); $objWriter->save('php://output'); $excelOutput = ob_get_clean(); return $excelOutput; }
function reporteNewExcel($fec1, $fec2) { global $db; // Incluir la libreria PHPExcel require '../include/PHPExcel/PHPExcel.php'; // Reservar memoria en servidor PHP // Si el archivo final tiene 5Mb, reservar 500Mb // Por cada operación, phpExcel mapea en memoria la imagen del archivo y esto satura la mamoria ini_set("memory_limit", "512M"); // Create a new PHPExcel object $objPHPExcel = new PHPExcel(); $objPHPExcel->getActiveSheet()->setTitle('Hoja 1'); $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial'); $objPHPExcel->getDefaultStyle()->getFont()->setSize(8); // Estilos Arreglo $styleEnc = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), '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'))); $styleSombra = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => '80E9E07A'), 'endcolor' => array('argb' => 'FFFFFFFF'))); $styleTitulo = array('font' => array('bold' => true, 'size' => 14), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)); $styleSubtitulo = array('font' => array('bold' => true, 'size' => 10), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)); // ---------------------------- // Se aplica estilo al encabezado $objPHPExcel->getActiveSheet()->getStyle('A5:E5')->applyFromArray($styleEnc); // Encabezados $headings = array('User Name', 'Login', 'Email'); // Escribe los encabezados $rowNumber = 5; $col = 'A'; foreach ($headings as $heading) { $objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading); $col++; } // AutoFiltro $objPHPExcel->getActiveSheet()->setAutoFilter('A5:C5'); // Auto Ajuste de Ancho en Columna $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true); // Freeze pane so that the heading line won't scroll $objPHPExcel->getActiveSheet()->freezePane('A6'); // Titulo $objPHPExcel->getActiveSheet()->setCellValue('A1', 'MOPSA S.A. DE C.V.')->getStyle('A1')->applyFromArray($styleTitulo); // SUBTitulo $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Reporte de ...'); $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo); // Simple query $sql = "SELECT * from USUARIO"; $db->query($sql); // Escribe los Registros $rowNumber = 6; while ($db->next_record()) { /*// Se arman las columnas con los datos. $rowF[0]= $db->f(Nombre); $rowF[1]= $db->f(Login); $rowF[2]= $db->f(Email); $col = 'A'; foreach($rowF as $cell) { $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell); // Sombra en registro //$objPHPExcel->getActiveSheet()->getStyle($col.$rowNumber)->applyFromArray($styleSombra); $col++; } unset($rowF) */ // Por manejo de memoria se deben de meter los datos con las menos operaciones posibles. $objPHPExcel->getActiveSheet()->setCellValue('A' . $rowNumber, $db->f(usuario))->setCellValue('B' . $rowNumber, $db->f(login))->setCellValue('C' . $rowNumber, $db->f(id_usuario)); $rowNumber++; } // Se modifican los encabezados del HTTP para indicar que se envia un archivo de Excel2007. header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition:attachment;filename="Report.xlsx"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); exit; }
/** * Write Cell Style Xf * * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer * @param PHPExcel_Style $pStyle Style * @param PHPExcel $pPHPExcel Workbook * @throws Exception */ private function _writeCellStyleXf(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Style $pStyle = null, PHPExcel $pPHPExcel = null) { // xf $objWriter->startElement('xf'); $objWriter->writeAttribute('xfId', 0); $objWriter->writeAttribute('fontId', (int) $this->getParentWriter()->getFontHashTable()->getIndexForHashCode($pStyle->getFont()->getHashCode())); if ($pStyle->getNumberFormat()->getBuiltInFormatCode() === false) { $objWriter->writeAttribute('numFmtId', (int) ($this->getParentWriter()->getNumFmtHashTable()->getIndexForHashCode($pStyle->getNumberFormat()->getHashCode()) + 164)); } else { $objWriter->writeAttribute('numFmtId', (int) $pStyle->getNumberFormat()->getBuiltInFormatCode()); } $objWriter->writeAttribute('fillId', (int) $this->getParentWriter()->getFillHashTable()->getIndexForHashCode($pStyle->getFill()->getHashCode())); $objWriter->writeAttribute('borderId', (int) $this->getParentWriter()->getBordersHashTable()->getIndexForHashCode($pStyle->getBorders()->getHashCode())); // Apply styles? $objWriter->writeAttribute('applyFont', $pPHPExcel->getDefaultStyle()->getFont()->getHashCode() != $pStyle->getFont()->getHashCode() ? '1' : '0'); $objWriter->writeAttribute('applyNumberFormat', $pPHPExcel->getDefaultStyle()->getNumberFormat()->getHashCode() != $pStyle->getNumberFormat()->getHashCode() ? '1' : '0'); $objWriter->writeAttribute('applyFill', $pPHPExcel->getDefaultStyle()->getFill()->getHashCode() != $pStyle->getFill()->getHashCode() ? '1' : '0'); $objWriter->writeAttribute('applyBorder', $pPHPExcel->getDefaultStyle()->getBorders()->getHashCode() != $pStyle->getBorders()->getHashCode() ? '1' : '0'); $objWriter->writeAttribute('applyAlignment', $pPHPExcel->getDefaultStyle()->getAlignment()->getHashCode() != $pStyle->getAlignment()->getHashCode() ? '1' : '0'); if ($pStyle->getProtection()->getLocked() != PHPExcel_Style_Protection::PROTECTION_INHERIT || $pStyle->getProtection()->getHidden() != PHPExcel_Style_Protection::PROTECTION_INHERIT) { $objWriter->writeAttribute('applyProtection', 'true'); } // alignment $objWriter->startElement('alignment'); $objWriter->writeAttribute('horizontal', $pStyle->getAlignment()->getHorizontal()); $objWriter->writeAttribute('vertical', $pStyle->getAlignment()->getVertical()); $textRotation = 0; if ($pStyle->getAlignment()->getTextRotation() >= 0) { $textRotation = $pStyle->getAlignment()->getTextRotation(); } else { if ($pStyle->getAlignment()->getTextRotation() < 0) { $textRotation = 90 - $pStyle->getAlignment()->getTextRotation(); } } $objWriter->writeAttribute('textRotation', $textRotation); $objWriter->writeAttribute('wrapText', $pStyle->getAlignment()->getWrapText() ? 'true' : 'false'); $objWriter->writeAttribute('shrinkToFit', $pStyle->getAlignment()->getShrinkToFit() ? 'true' : 'false'); if ($pStyle->getAlignment()->getIndent() > 0) { $objWriter->writeAttribute('indent', $pStyle->getAlignment()->getIndent()); } $objWriter->endElement(); // protection if ($pStyle->getProtection()->getLocked() != PHPExcel_Style_Protection::PROTECTION_INHERIT || $pStyle->getProtection()->getHidden() != PHPExcel_Style_Protection::PROTECTION_INHERIT) { $objWriter->startElement('protection'); if ($pStyle->getProtection()->getLocked() != PHPExcel_Style_Protection::PROTECTION_INHERIT) { $objWriter->writeAttribute('locked', $pStyle->getProtection()->getLocked() == PHPExcel_Style_Protection::PROTECTION_PROTECTED ? 'true' : 'false'); } if ($pStyle->getProtection()->getHidden() != PHPExcel_Style_Protection::PROTECTION_INHERIT) { $objWriter->writeAttribute('hidden', $pStyle->getProtection()->getHidden() == PHPExcel_Style_Protection::PROTECTION_PROTECTED ? 'true' : 'false'); } $objWriter->endElement(); } $objWriter->endElement(); }
$objPHPExcel->getActiveSheet()->SetCellValue('A6', ''); $objPHPExcel->getActiveSheet()->SetCellValue('B6', 'All Categories'); $objPHPExcel->getActiveSheet()->SetCellValue('C6', $root_sales_detail->quantity); $objPHPExcel->getActiveSheet()->SetCellValue('D6', ''); $objPHPExcel->getActiveSheet()->SetCellValueExplicit('E6', price(CURRENCY, $root_sales_detail->subtotal), PHPExcel_Cell_DataType::TYPE_STRING); if ($root_sales_detail->discount != 0) { $objPHPExcel->getActiveSheet()->SetCellValueExplicit('F6', "-" . price(CURRENCY, $root_sales_detail->discount), PHPExcel_Cell_DataType::TYPE_STRING); } $objPHPExcel->getActiveSheet()->SetCellValueExplicit('G6', price(CURRENCY, $root_sales_detail->subtotal - $root_sales_detail->discount), PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->getActiveSheet()->getStyle('A6:G6')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('A6:G6')->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => 'bbbbbb'))); //rows $rowxls = 6; $_get->print_category_xls($iteration, 'top', 0); //format cell $objPHPExcel->getDefaultStyle()->getFont()->setName('Helvetica')->setSize(10); $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(60); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(5); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15); //title $objPHPExcel->getActiveSheet()->getStyle('A1:G3')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('A1:A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //header row $objPHPExcel->getActiveSheet()->getStyle('A5:G5')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE); $objPHPExcel->getActiveSheet()->getStyle('A5:G5')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('A5:G5')->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => '000000'))); $objPHPExcel->getActiveSheet()->getStyle('A5:G5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
function reporte($fec1, $fec2) { global $connI; $reportName = "Intermodal :: Rep. Hojas de Instruccion en MscLink"; // Incluir la libreria PHPExcel require '../include/PHPExcel/PHPExcel.php'; // Reservar memoria en servidor PHP // Si el archivo final tiene 5Mb, reservar 500Mb // Por cada operación, phpExcel mapea en memoria la imagen del archivo y esto satura la mamoria ini_set("memory_limit", "512M"); // Estilos Arreglo $styleEnc = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), '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'))); $styleSombra = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => '80E9E07A'), 'endcolor' => array('argb' => 'FFFFFFFF'))); $styleTitulo = array('font' => array('bold' => true, 'size' => 14), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)); $styleSubtitulo = array('font' => array('bold' => true, 'size' => 10), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)); // ---------------------------- // Hoja 1 // Nota : No usar acentos!!! $objPHPExcel = new PHPExcel(); $objPHPExcel->getActiveSheet()->setTitle('Hoja1'); $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial'); $objPHPExcel->getDefaultStyle()->getFont()->setSize(8); // HOJA 2 $workSheet2 = new PHPExcel_Worksheet($objPHPExcel, 'Hoja2'); $objPHPExcel->addSheet($workSheet2, 1); $workSheet2->getDefaultStyle()->getFont()->setSize(8); $workSheet2->getDefaultStyle()->getFont()->setName('Arial'); // ----------------------------------------------- // ENCABEZADOS // ----------------------------------------------- // Se crea el arreglo de hojas. Esto es como llevan el mismo encabezado en todas las hojas, solo es recorrer el index = hojas. $objPHPExcel->setActiveSheetIndex(0); // Se aplica estilo al encabezado $objPHPExcel->getActiveSheet()->getStyle('A7:R7')->applyFromArray($styleEnc); // Encabezados $headings = array('No', 'Intermodal_Leg_id,', 'Purchase_Order_Number,', 'Global_Name,', 'Transport_Mode,', 'IL_Estimated_Cost,', 'ISC_Customer_Appointment_Date,', 'Booking_Number,', 'Bill_Of_Lading_Number,', 'Vessel_Name,', 'Voyage,', 'POL,', 'POD,', 'Container_Number,', 'Req_Assign_Container,', 'IC_Req_Assign_Container,', 'EQ_Type,', 'Tot_Container_Packages_Weight,', 'LocalLogisticLocation,', 'Cust_Company,', 'DStart_Depot,', 'DEnd_Depot,', 'Booking_Type_Desc,', 'Ex_Short_Description,', 'Shipment_Method,', 'Origin,', 'FDest,', 'I_E,', 'Charge,', 'Result_Std,', 'Seal_No'); // Escribe los encabezados $rowNumber = 7; // Freeze pane so that the heading line won't scroll $objPHPExcel->getActiveSheet()->freezePane('A8'); $col = 'A'; foreach ($headings as $heading) { $objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading); $col++; } // AutoFiltro $objPHPExcel->getActiveSheet()->setAutoFilter('A7:R7'); // Auto Ajuste de Ancho en Columna $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(15); // Titulo $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Mediterranean Shipping Company Mexico, S.A. DE C.V.')->getStyle('A1')->applyFromArray($styleTitulo); // SUBTitulo $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Solo como Agentes / As Agent only'); $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo); $objPHPExcel->getActiveSheet()->setCellValue('A3', '$reportName'); $objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo); $objPHPExcel->getActiveSheet()->setCellValue('A6', "{$buque} {$viaje}"); $objPHPExcel->getActiveSheet()->getStyle('A6')->applyFromArray($styleSubtitulo); // ----------------------------------- // HOJA 1 // ----------------------------------- $objPHPExcel->setActiveSheetIndex(0); // Simple query $sql = "\n SELECT\n PT.Intermodal_Leg_id,\n PT.Purchase_Order_Number,\n GC.Global_Name,\n IC.Transport_Mode,\n IC.IL_Estimated_Cost,\n IC.ISC_Customer_Appointment_Date,\n IC.Booking_Number,\n IC.Bill_Of_Lading_Number,\n IC.Vessel_Name,\n IC.Voyage,\n IC.POL,\n IC.POD,\n IC.Container_Number,\n IC.Req_Assign_Container,\n IC.IC_Req_Assign_Container,\n IC.EQ_Type,\n IC.Tot_Container_Packages_Weight,\n IC.LocalLogisticLocation,\n IC.Cust_Company,\n IC.DStart_Depot,\n IC.DEnd_Depot,\n IC.Booking_Type_Desc,\n IC.Ex_Short_Description,\n IC.Shipment_Method,\n IC.Origin,\n IC.FDest,\n IC.I_E,\n CH.Charge,\n CH.Result_Std,\n s.Seal_No\n FROM\n Interlink_DFeed90.dfeed.FNTB_Intermodal_Customer (NULL, NULL) IC\n LEFT JOIN Interlink_Main90.dbo.Assoc_Doc_Company DC (nolock) ON DC.Intermodal_Leg_id = IC.Intermodal_Leg_id\n LEFT JOIN Interlink_Main90.dbo.GISCompany_Address GA (nolock) ON GA.Company_Address_id = DC.Company_Address_id\n LEFT JOIN Interlink_Main90.dbo.GISCompany GC (nolock) ON GC.company_id = GA.Company_id\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_Intermodal_EDI_Request_Truck (NULL, NULL) PT ON IC.Intermodal_Leg_id = PT.Intermodal_Leg_id\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_Charge (1, NULL) CH ON CH.Cargo_Shipment_Container_id = IC.Cargo_Shipment_Container_id\n AND CH.Charge_DTX_Code = (\n CASE\n WHEN CH.I_E = 'E' THEN\n 'PRE'\n ELSE\n 'ONC'\n END\n )\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_BOLREP_Container () s ON IC.Container_Number = s.Container_Number\n AND s.Cargo_Shipment_Container_idx = IC.Cargo_Shipment_Container_id\n AND s.BC_BOLREP_id = (\n SELECT\n MAX (BC_BOLREP_id)\n FROM\n Interlink_DFeed90.dfeed.FNTB_BOLREP_Container ()\n WHERE\n Container_Number = IC.Container_Number\n AND Cargo_Shipment_Container_idx = IC.Cargo_Shipment_Container_id\n )\n WHERE\n IC.ISC_Customer_Appointment_Date BETWEEN '{$fec1}' AND '{$fec2}'\n\n "; $rs = odbc_exec($connI, $sql); if (!$rs) { exit("Error en la consulta SQL"); } $lin = 1; $rowNumber = 8; // donde inicia los datos while (odbc_fetch_row($rs)) { $Intermodal_Leg_id = odbc_result($rs, 'Intermodal_Leg_id'); $Purchase_Order_Number = odbc_result($rs, 'Purchase_Order_Number'); $Global_Name = odbc_result($rs, 'Global_Name'); $Transport_Mode = odbc_result($rs, 'Transport_Mode'); $IL_Estimated_Cost = odbc_result($rs, 'IL_Estimated_Cost'); $ISC_Customer_Appointment_Date = odbc_result($rs, 'ISC_Customer_Appointment_Date'); $Booking_Number = odbc_result($rs, 'Booking_Number'); $Bill_Of_Lading_Number = odbc_result($rs, 'Bill_Of_Lading_Number'); $Vessel_Name = odbc_result($rs, 'Vessel_Name'); $Voyage = odbc_result($rs, 'Voyage'); $POL = odbc_result($rs, 'POL'); $POD = odbc_result($rs, 'POD'); $Container_Number = odbc_result($rs, 'Container_Number'); $Req_Assign_Container = odbc_result($rs, 'Req_Assign_Container'); $IC_Req_Assign_Container = odbc_result($rs, 'IC_Req_Assign_Container'); $EQ_Type = odbc_result($rs, 'EQ_Type'); $Tot_Container_Packages_Weight = odbc_result($rs, 'Tot_Container_Packages_Weight'); $LocalLogisticLocation = odbc_result($rs, 'LocalLogisticLocation'); $Cust_Company = odbc_result($rs, 'Cust_Company'); $DStart_Depot = odbc_result($rs, 'DStart_Depot'); $DEnd_Depot = odbc_result($rs, 'DEnd_Depot'); $Booking_Type_Desc = odbc_result($rs, 'Booking_Type_Desc'); $Ex_Short_Description = odbc_result($rs, 'Ex_Short_Description'); $Shipment_Method = odbc_result($rs, 'Shipment_Method'); $Origin = odbc_result($rs, 'Origin'); $FDest = odbc_result($rs, 'FDest'); $I_E = odbc_result($rs, 'I_E'); $Charge = odbc_result($rs, 'Charge'); $Result_Std = odbc_result($rs, 'Result_Std'); $Seal_No = odbc_result($rs, 'Seal_No'); $ISC_Customer_Appointment_Date = substr($ISC_Customer_Appointment_Date, 0, -4); // Por manejo de memoria se deben de meter los datos con las menos operaciones posibles. $objPHPExcel->getActiveSheet()->setCellValue('A' . $rowNumber, $lin)->setCellValue('B' . $rowNumber, $Intermodal_Leg_id)->setCellValue('C' . $rowNumber, $Purchase_Order_Number)->setCellValue('D' . $rowNumber, $Global_Name)->setCellValue('E' . $rowNumber, $Transport_Mode)->setCellValue('F' . $rowNumber, $IL_Estimated_Cost)->setCellValue('G' . $rowNumber, $ISC_Customer_Appointment_Date)->setCellValue('H' . $rowNumber, $Booking_Number)->setCellValue('I' . $rowNumber, $Bill_Of_Lading_Number)->setCellValue('J' . $rowNumber, $Vessel_Name)->setCellValue('K' . $rowNumber, $POL)->setCellValue('L' . $rowNumber, $POD)->setCellValue('M' . $rowNumber, $Container_Number)->setCellValue('N' . $rowNumber, $Req_Assign_Container)->setCellValue('O' . $rowNumber, $IC_Req_Assign_Container)->setCellValue('P' . $rowNumber, $EQ_Type)->setCellValue('Q' . $rowNumber, $Tot_Container_Packages_Weight)->setCellValue('R' . $rowNumber, $LocalLogisticLocation)->setCellValue('S' . $rowNumber, $Cust_Company)->setCellValue('T' . $rowNumber, $DStart_Depot)->setCellValue('U' . $rowNumber, $DEnd_Depot)->setCellValue('V' . $rowNumber, $Booking_Type_Desc)->setCellValue('W' . $rowNumber, $Ex_Short_Description)->setCellValue('X' . $rowNumber, $Shipment_Method)->setCellValue('Y' . $rowNumber, $Origin)->setCellValue('Z' . $rowNumber, $FDest)->setCellValue('AA' . $rowNumber, $I_E)->setCellValue('AB' . $rowNumber, $Charge)->setCellValue('AC' . $rowNumber, $Result_Std)->setCellValue('AD' . $rowNumber, $Seal_No); $rowNumber++; $lin++; } /* // ------------------------------ // Hoja 2 - TRANSBORDOS // ------------------------------ $objPHPExcel->setActiveSheetIndex(1); // Se aplica estilo al encabezado $objPHPExcel->getActiveSheet()->getStyle('A7:T7')->applyFromArray($styleEnc); // Encabezados $headings = Array('No', 'Original_pol'); // Escribe los encabezados $rowNumber = 7; // Freeze pane so that the heading line won't scroll $objPHPExcel->getActiveSheet()->freezePane('A8'); $col = 'A'; foreach ($headings as $heading) { $objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading); $col++; } // AutoFiltro $objPHPExcel->getActiveSheet()->setAutoFilter('A7:T7'); // Auto Ajuste de Ancho en Columna $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(15); // Titulo $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Mediterranean Shipping Company Mexico, S.A. DE C.V.') ->getStyle('A1')->applyFromArray($styleTitulo); // SUBTitulo $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Solo como Agentes / As agents Only'); $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo); $objPHPExcel->getActiveSheet()->setCellValue('A3', '$reportName'); $objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo); $objPHPExcel->getActiveSheet()->setCellValue('A6', "$buque $viaje"); $objPHPExcel->getActiveSheet()->getStyle('A6')->applyFromArray($styleSubtitulo); $objPHPExcel->setActiveSheetIndex(1); // Simple query $sql = " SELECT e.Equipment_Number as CONTE, e.Bill_Of_Lading_Number as BL, e.Origin_Booking_Number, e.EQ_Type as EQ_Type, e.Load_Terminal, e.VPCLoad_Vessel_Name, e.VPCLoad_Voyage, e.Origin AS Inland_Origin, e.FDest AS Inland_Final_Dest, e.Orig_POLUN AS Original_POL, e.VPCLoad_UNCode AS Current_POL, e.Prev_POLUN AS Previous_Port, e.Next_PODUN AS Next_POD, e.PODUN AS Final_POD, e.TSPorts AS All_TS_Ports, e.NVOCC_AMS_CERTIFIED, e.High_Priority_Flag, e.Hold_Indicator, d.UN_Number as UNNumber, d.IMO_Class as ImoClass, e.Shipper_Owned, e.Master_Tare_Weight_Kg as TaraKg, e.Cntr_Weight, e.Cargo_Weight_Kgs as CargoKg, e.Master_Payload_Kg, e.Cargo_Volume_CMT, e.Ex_Cargo_Short_Description as Producto, e.Cargo_Packages as Paquetes, e.Ex_Cargo_Package_Type as Embalaje, e.Ex_Harmonized_Code, e.OOG_Height, e.OOG_Length_Back, e.OOG_Length_Front, e.OOG_Width_Left, e.OOG_Width_Right, e.Tank_Residue, e.Max_Reefer_Temp, e.Min_Reefer_Temp, e.Temp_Unit, e.Ventilated_Container_Flag, e.Air_Fresh_Vents_Setting, e.Cold_Treatment, e.Control_Atmosphere, e.Opening_Percentage, e.Bulb_Mode, e.IsEmpty as IsEmpty, e.Fumigation, e.De_Fumigation, e.Fumigation_Residue_IMO_Class, e.Fumigation_Residue_UN_Number, e.Custom_Status_Desc, a.company AS shipper_name, a.city AS shipper_city, a.Country AS shipper_country, a.MSCCode AS shipper_msccode FROM interlink_dfeed90.dfeed.TSHIP_FNTB_Equipment ('2015-05-01', 'A') e LEFT JOIN Interlink_DFeed90.dfeed.TSHIP_FNTB_Dangerous_Cargo () d ON e.Equipment_ID = d.Equipment_ID LEFT JOIN Interlink_DFeed90.dfeed.TSHIP_FNTB_AssocDocCompany ('shipper') a ON e.Bill_Of_Lading_Number = a.Bill_Of_Lading_Number WHERE E.VPCDisch_Vessel_Name = '$buque' AND E.VPCDisch_Voyage = '$viaje' AND E.VPCDisch_UNCode = '$codPuerto' "; $rs = odbc_exec($connI, $sql); if (!$rs) { exit("Error en la consulta SQL"); } $lin=1; $rowNumber = 8; // donde inicia los datos while (odbc_fetch_row($rs)) { $polOri = odbc_result($rs, "Original_POL"); $polCur = odbc_result($rs, "Current_POL"); // Por manejo de memoria se deben de meter los datos con las menos operaciones posibles. $objPHPExcel->getActiveSheet()->setCellValue( 'A'.$rowNumber,$lin) ->setCellValue('B'.$rowNumber,$polOri) ->setCellValue('C'.$rowNumber,$polPrev) ->setCellValue('D'.$rowNumber,$x) ->setCellValue('E'.$rowNumber,$x) ->setCellValue('F'.$rowNumber,$x) ->setCellValue('G'.$rowNumber,$x) ->setCellValue('H'.$rowNumber,$x) ->setCellValue('I'.$rowNumber,$x) ->setCellValue('J'.$rowNumber,$x) ->setCellValue('K'.$rowNumber,$x) ->setCellValue('L'.$rowNumber,$x) ->setCellValue('M'.$rowNumber,$x) ->setCellValue('N'.$rowNumber,$x) ->setCellValue('O'.$rowNumber,$x) ->setCellValue('P'.$rowNumber,$x) ->setCellValue('Q'.$rowNumber,$x) ->setCellValue('R'.$rowNumber,$x) ->setCellValue('S'.$rowNumber,$x) ->setCellValue('T'.$rowNumber,$x) ; $rowNumber++; $lin++; } // Loregreso a la hoja 1. $objPHPExcel->setActiveSheetIndex(0); */ // Se modifican los encabezados del HTTP para indicar que se envia un archivo de Excel2007. header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition:attachment;filename="hojaIns_msclink.xlsx"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); exit; }
<?php error_reporting(E_ERROR); require_once 'includes/cars_pdo.php'; if (isset($_POST['download'])) { require_once 'src/PHPExcel.php'; try { $sheet = new PHPExcel(); // Установить мета свойства $sheet->getProperties()->setCreator('www.example.com')->setLastModifiedBy('www.example.com')->setTitle('Used cars for sale')->setKeywords('cars second-hand used'); // Установить свойства по умолчанию $sheet->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP); $sheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $sheet->getDefaultStyle()->getFont()->setName('Lucida Sans Unicode'); $sheet->getDefaultStyle()->getFont()->setSize(12); // Получить ссылку на активную spreadsheet $sheet->setActiveSheetIndex(0); $activeSheet = $sheet->getActiveSheet(); // Установить опции для печати $activeSheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE)->setFitToWidth(1)->setFitToHeight(0); $activeSheet->getHeaderFooter()->setOddHeader('&C&B&16' . $sheet->getProperties()->getTitle())->setOddFooter('&CPage &P of &N'); // Заполнить данными $row = getRow($result); $colHeaders = array_keys($row); $col = 'A'; $rownum = 1; // Установить заголовок столбца foreach ($colHeaders as $header) { $activeSheet->setCellValue($col . $rownum, $header); $activeSheet->getStyle($col . $rownum)->getFont()->setBold(true); if ($col == 'G') {
public function exportarSifilis() { date_default_timezone_set('America/Lima'); $nivelUsuario = $this->session->userdata("nivel"); switch ($nivelUsuario) { case '8': $where = array("establecimiento" => $nivelUsuario["establecimiento"]); $query = $this->db->select("sifilis.*, sifilis_materna.*, sifilis_congenita.*")->from("sifilis")->where($where)->join('sifilis_materna', 'sifilis.codigo=sifilis_materna.codigo', 'left')->join('sifilis_congenita', 'sifilis.codigo=sifilis_congenita.codigo', 'left')->get(); break; case '7': $where = array("diresa" => $nivelUsuario["diresa"], "red" => $nivelUsuario["red"], "microred" => $nivelUsuario["microred"]); $query = $this->db->select("sifilis.*, sifilis_materna.*, sifilis_congenita.*")->from("sifilis")->where($where)->join('sifilis_materna', 'sifilis.codigo=sifilis_materna.codigo', 'left')->join('sifilis_congenita', 'sifilis.codigo=sifilis_congenita.codigo', 'left')->get(); break; case '6': $where = array("diresa" => $nivelUsuario["diresa"], "red" => $nivelUsuario["red"]); $query = $this->db->select("sifilis.*, sifilis_materna.*, sifilis_congenita.*")->from("sifilis")->where($where)->join('sifilis_materna', 'sifilis.codigo=sifilis_materna.codigo', 'left')->join('sifilis_congenita', 'sifilis.codigo=sifilis_congenita.codigo', 'left')->get(); break; case '5': $where = array("diresa" => $nivelUsuario["diresa"]); $query = $this->db->select("sifilis.*, sifilis_materna.*, sifilis_congenita.*")->from("sifilis")->where($where)->join('sifilis_materna', 'sifilis.codigo=sifilis_materna.codigo', 'left')->join('sifilis_congenita', 'sifilis.codigo=sifilis_congenita.codigo', 'left')->get(); break; default: $query = $this->db->select("sifilis.*, sifilis_materna.*, sifilis_congenita.*")->from("sifilis")->join('sifilis_materna', 'sifilis.codigo=sifilis_materna.codigo', 'left')->join('sifilis_congenita', 'sifilis.codigo=sifilis_congenita.codigo', 'left')->get(); break; } if (!$query) { return false; } $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setTitle("export")->setDescription("none"); $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial'); $objPHPExcel->getDefaultStyle()->getFont()->setSize(12); $fields = $query->list_fields(); $col = 0; foreach ($fields as $field) { $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field); $col++; } $row = 2; foreach ($query->result() as $data) { $col = 0; foreach ($fields as $field) { $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->{$field}); $col++; } $row++; } $objPHPExcel->setActiveSheetIndex(0); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="sifilis_' . date('dMy') . '.xls"'); header('Cache-Control: max-age=0'); $objWriter->save('php://output'); redirect(site_url('sifilis/principal'), 301); }
function export_goods_list() { $cname = $_GET['cname']; $list = $_GET['list']; $w = ''; if (!empty($cname) && $cname != 'all') { //$cname = str_replace(' ','',trim($cname)); $w = " WHERE goods_cate LIKE '%{$cname}%'"; } $sql = "SELECT * FROM `{$this->App->prefix()}goods_cache_list` {$w} ORDER BY goods_cate ASC, goods_id DESC LIMIT {$list}"; $rt = $this->App->find($sql); $iconv = Import::gz_iconv(); require_once SYS_PATH . 'lib/class/PHPExcel.php'; //require_once SYS_PATH.'lib/class/PHPExcel/IOFactory.php'; $objPHPExcel = new PHPExcel(); // 设置基本属性 $objPHPExcel->getProperties()->setCreator("Sun Star Data Center")->setLastModifiedBy("Sun Star Data Center")->setTitle("Microsoft Office Excel Document")->setSubject("Test Data Report -- From Sunstar Data Center")->setDescription("LD Test Data Report, Generate by Sunstar Data Center")->setKeywords("sunstar ld report")->setCategory("Test result file"); //print_r($rt); exit; // 创建多个工作薄 $sheet1 = $objPHPExcel->createSheet(); //$sheet2 = $objPHPExcel->createSheet(); // 设置第一个工作簿为活动工作簿 //$objPHPExcel->setActiveSheetIndex(0); // 设置活动工作簿名称 // 如果是中文一定要使用iconv函数转换编码 $objPHPExcel->getActiveSheet()->setTitle(empty($cname) ? '商品导出' : $cname); // 设置默认字体和大小 $objPHPExcel->getDefaultStyle()->getFont()->setName('宋体'); $objPHPExcel->getDefaultStyle()->getFont()->setSize(10); // 设置一列的宽度 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(45); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(40); $objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(30); // 设置行的高度 // $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(55); // 定义一个样式,加粗,居中 //$styleArray1 = array( 'font' => array( 'bold' => true, 'color'=>array( 'argb' => '00000000', ), ), 'alignment' => array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, ), ); //$styleArray2 = array( 'font' => array( 'color'=>array( 'argb' => '00000000', ), ), 'alignment' => array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, ), ); //居中 $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('G')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('H')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('I')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('J')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('K')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('L')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('M')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('N')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('O')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('P')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('Q')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('R')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('S')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('T')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('U')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('V')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('W')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //垂直居中 $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('G')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('H')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('I')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('J')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('K')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('L')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('M')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('N')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('O')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('P')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('Q')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('R')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('S')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('T')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('U')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('V')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('W')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); // 将样式应用于A1单元格 /* $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray1); $objPHPExcel->getActiveSheet()->getStyle('B1')->applyFromArray($styleArray1); $objPHPExcel->getActiveSheet()->getStyle('C1')->applyFromArray($styleArray1); $objPHPExcel->getActiveSheet()->getStyle('D1')->applyFromArray($styleArray1); $objPHPExcel->getActiveSheet()->getStyle('E1')->applyFromArray($styleArray1); $objPHPExcel->getActiveSheet()->getStyle('F1')->applyFromArray($styleArray1); $objPHPExcel->getActiveSheet()->getStyle('G1')->applyFromArray($styleArray1); */ // 给特定单元格中写入内容 $objPHPExcel->getActiveSheet()->setCellValue('A1', '图片'); $objPHPExcel->getActiveSheet()->setCellValue('B1', '商品编号'); $objPHPExcel->getActiveSheet()->setCellValue('C1', '商品条形码'); $objPHPExcel->getActiveSheet()->setCellValue('D1', '商品名称'); $objPHPExcel->getActiveSheet()->setCellValue('E1', '商品分类'); $objPHPExcel->getActiveSheet()->setCellValue('F1', '商品品牌'); $objPHPExcel->getActiveSheet()->setCellValue('G1', '商品规格'); $objPHPExcel->getActiveSheet()->setCellValue('H1', '商品重量'); $objPHPExcel->getActiveSheet()->setCellValue('I1', '产地'); $objPHPExcel->getActiveSheet()->setCellValue('J1', '生产商'); $objPHPExcel->getActiveSheet()->setCellValue('K1', '保质期'); $objPHPExcel->getActiveSheet()->setCellValue('L1', '商品单位'); $objPHPExcel->getActiveSheet()->setCellValue('M1', '供应价'); $objPHPExcel->getActiveSheet()->setCellValue('N1', '批发价'); $objPHPExcel->getActiveSheet()->setCellValue('O1', '零售价'); $objPHPExcel->getActiveSheet()->setCellValue('P1', '商品库存'); $objPHPExcel->getActiveSheet()->setCellValue('Q1', '库存警告数量'); $objPHPExcel->getActiveSheet()->setCellValue('R1', 'meta关键字'); $objPHPExcel->getActiveSheet()->setCellValue('S1', 'meta描述'); $objPHPExcel->getActiveSheet()->setCellValue('T1', '商品赠送'); $objPHPExcel->getActiveSheet()->setCellValue('U1', '供应商帐号'); $objPHPExcel->getActiveSheet()->setCellValue('V1', '商品图片路径'); $objPHPExcel->getActiveSheet()->setCellValue('W1', '商品相册[多个用|分隔]'); //循环 $k = 1; if (!empty($rt)) { foreach ($rt as $row) { ++$k; //居中 /* $objPHPExcel->getActiveSheet()->getStyle('A'.$k)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('B'.$k)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('C'.$k)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // $objPHPExcel->getActiveSheet()->getStyle('D'.$k)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('E'.$k)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('F'.$k)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('G'.$k)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //垂直居中 $objPHPExcel->getActiveSheet()->getStyle('A'.$k)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('B'.$k)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('C'.$k)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('D'.$k)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('E'.$k)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('F'.$k)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('G'.$k)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);*/ // 设置行的高度 $objPHPExcel->getActiveSheet()->getRowDimension($k)->setRowHeight(50); //赋值 // $objPHPExcel->getActiveSheet()->getStyle('A'.$k)->applyFromArray($styleArray1); //$objPHPExcel->getActiveSheet()->setCellValue('B'.$k, '图片'); // 给单元格中放入图片, 将数据图片放在J1单元格内 $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('Logo'); $objDrawing->setDescription('Logo'); $objDrawing->setPath(!empty($row['goods_thumb']) && file_exists(SYS_PATH . $row['goods_thumb']) ? '../' . $row['goods_thumb'] : './images/no_picture.gif'); // 图片路径,只能是相对路径 //$objDrawing->setWidth(60); // 图片宽度 $objDrawing->setHeight(50); // 图片高度 $objDrawing->setCoordinates('A' . $k); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); $objPHPExcel->getActiveSheet()->setCellValue('B' . $k, $row['goods_bianhao']); $objPHPExcel->getActiveSheet()->setCellValue('C' . $k, $row['goods_sn']); $objPHPExcel->getActiveSheet()->setCellValue('D' . $k, $row['goods_name']); $objPHPExcel->getActiveSheet()->setCellValue('E' . $k, empty($row['goods_cate_sub']) ? $row['goods_cate'] : $row['goods_cate_sub']); $brand_name = !empty($row['brand_name']) ? trim(stripcslashes(strip_tags(nl2br($row['brand_name'])))) : '---'; $objPHPExcel->getActiveSheet()->setCellValue('F' . $k, $brand_name); $objPHPExcel->getActiveSheet()->setCellValue('G' . $k, $row['goods_brief']); $objPHPExcel->getActiveSheet()->setCellValue('H' . $k, $row['goods_weight']); $objPHPExcel->getActiveSheet()->setCellValue('I' . $k, ''); $objPHPExcel->getActiveSheet()->setCellValue('J' . $k, ''); $objPHPExcel->getActiveSheet()->setCellValue('K' . $k, ''); $objPHPExcel->getActiveSheet()->setCellValue('L' . $k, $row['goods_unit']); $objPHPExcel->getActiveSheet()->setCellValue('M' . $k, $row['market_price']); $objPHPExcel->getActiveSheet()->setCellValue('N' . $k, $row['pifa_price']); $objPHPExcel->getActiveSheet()->setCellValue('O' . $k, $row['shop_price']); $objPHPExcel->getActiveSheet()->setCellValue('P' . $k, $row['goods_number']); $objPHPExcel->getActiveSheet()->setCellValue('Q' . $k, $row['warn_number']); $objPHPExcel->getActiveSheet()->setCellValue('R' . $k, $row['meta_keys']); $objPHPExcel->getActiveSheet()->setCellValue('S' . $k, $row['meta_desc']); $objPHPExcel->getActiveSheet()->setCellValue('T' . $k, ''); $objPHPExcel->getActiveSheet()->setCellValue('U' . $k, ''); $objPHPExcel->getActiveSheet()->setCellValue('V' . $k, $row['original_img']); $objPHPExcel->getActiveSheet()->setCellValue('W' . $k, ''); } } $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $m_strOutputExcelFileName = (empty($cname) ? '商品列表' : $cname) . date('Y-m-j_H_i_s') . ".xls"; // 输出EXCEL文件名 header("Pragma: public"); header("Expires: 0"); header("Cache-Control:must-revalidate, post-check=0, pre-check=0"); header("Content-Type:application/force-download"); header("Content-Type: application/vnd.ms-excel;"); header("Content-Type:application/octet-stream"); header("Content-Type:application/download"); header("Content-Disposition:attachment;filename=" . $m_strOutputExcelFileName); header("Content-Transfer-Encoding:binary"); $objWriter->save("php://output"); }
function generate($tbl) { session_start(); $_SESSION['zprofile'] = 'yes'; $_SESSION['zprofile']['username'] = '******'; $_SESSION['zprofile']['useremail'] = '*****@*****.**'; $_SESSION['zprofile']['usercompany'] = 'hehehahhaha'; $_GET['var'] = 'tbl_tes'; $_SESSION['tbl_tes'] = $tbl; /* $_SESSION['tbl_tes'] = ' <table border="1"> <thead> <tr bgcolor="yellow"> <th>no</th> <th>col1</th> <th>col2</th> <th>col3</th> <th>col4</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>B2</td> <td>C2</td> <td rowspan="2">D2</td> <td>E3</td> </tr> <tr> <td>2</td> <td colspan="2">B3</td> <td>C3</td> </tr> <tr> <td>3</td> <td>B4</td> <td colspan="2" rowspan="2">C4</td> <td>D4</td> </tr> <tr> <td>4</td> <td> B5<img src="././assets/img/ecsi.png" name="gambar tes" title="hehehe coba titlenya" width="100" height="100"> <td><b>C5</b></td> </tr> </tbody> </table> '; */ //echo $_SESSION['tbl_tes'];exit(); ini_set("memory_limit", "-1"); ini_set("set_time_limit", "0"); set_time_limit(0); if (isset($_SESSION['zprofile'])) { $username = $_SESSION['zprofile']['username']; // user's name $usermail = $_SESSION['zprofile']['useremail']; // user's emailid $usercompany = $_SESSION['zprofile']['usercompany']; // user's company } else { header('Location: index.php?e=0'); } if (!isset($_GET['var'])) { echo "<br />No Table Variable Present, nothing to Export."; exit; } else { $tablevar = $_GET['var']; } if (!isset($_GET['limit'])) { $limit = 12; } else { $limit = $_GET['limit']; } if (!isset($_GET['debug'])) { $debug = false; } else { $debug = true; $handle = fopen("Auditlog/exportlog.txt", "w"); fwrite($handle, "\nDebugging On..."); } if (!isset($_SESSION[$tablevar]) or $_SESSION[$tablevar] == '') { echo "<br />Empty HTML Table, nothing to Export."; exit; } else { $htmltable = $_SESSION[$tablevar]; } if (strlen($htmltable) == strlen(strip_tags($htmltable))) { echo "<br />Invalid HTML Table after Stripping Tags, nothing to Export."; exit; } $htmltable = strip_tags($htmltable, "<table><tr><th><thead><tbody><tfoot><td><br><br /><b><span><img><img />"); $htmltable = str_replace("<br />", "\n", $htmltable); $htmltable = str_replace("<br/>", "\n", $htmltable); $htmltable = str_replace("<br>", "\n", $htmltable); $htmltable = str_replace(" ", " ", $htmltable); $htmltable = str_replace("\n\n", "\n", $htmltable); // // Extract HTML table contents to array // $dom = new domDocument(); $dom->loadHTML($htmltable); if (!$dom) { echo "<br />Invalid HTML DOM, nothing to Export."; exit; } $dom->preserveWhiteSpace = false; // remove redundant whitespace $tables = $dom->getElementsByTagName('table'); if (!is_object($tables)) { echo "<br />Invalid HTML Table DOM, nothing to Export."; exit; } if ($debug) { fwrite($handle, "\nTable Count: " . $tables->length); } $tbcnt = $tables->length - 1; // count minus 1 for 0 indexed loop over tables if ($tbcnt > $limit) { $tbcnt = $limit; } // // // Create new PHPExcel object with default attributes // require_once 'PHPExcel/PHPExcel.php'; $objPHPExcel = new PHPExcel(); $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial'); $objPHPExcel->getDefaultStyle()->getFont()->setSize(9); $tm = date(YmdHis); $pos = strpos($usermail, "@"); $user = substr($usermail, 0, $pos); $user = str_replace(".", "", $user); $tfn = $user . "_" . $tm . "_" . $tablevar . ".xlsx"; //$fname = "AuditLog/".$tfn; $fname = $tfn; $objPHPExcel->getProperties()->setCreator($username)->setLastModifiedBy($username)->setTitle("Automated Export")->setSubject("Automated Report Generation")->setDescription("Automated report generation.")->setKeywords("Exported File")->setCompany($usercompany)->setCategory("Export"); // // Loop over tables in DOM to create an array, each table becomes a worksheet // for ($z = 0; $z <= $tbcnt; $z++) { $maxcols = 0; $totrows = 0; $headrows = array(); $bodyrows = array(); $r = 0; $h = 0; $rows = $tables->item($z)->getElementsByTagName('tr'); $totrows = $rows->length; if ($debug) { fwrite($handle, "\nTotal Rows: " . $totrows); } foreach ($rows as $row) { $ths = $row->getElementsByTagName('th'); if (is_object($ths)) { if ($ths->length > 0) { $headrows[$h]['colcnt'] = $ths->length; if ($ths->length > $maxcols) { $maxcols = $ths->length; } $nodes = $ths->length - 1; for ($x = 0; $x <= $nodes; $x++) { $thishdg = $ths->item($x)->nodeValue; $headrows[$h]['th'][] = $thishdg; $headrows[$h]['bold'][] = $this->findWrapText('b', $this->innerHTML($ths->item($x))); $headrows[$h]['italic'][] = $this->findWrapText('i', $this->innerHTML($ths->item($x))); $headrows[$h]['underline'][] = $this->findWrapText('u', $this->innerHTML($ths->item($x))); if ($ths->item($x)->hasAttribute('style')) { $style = $ths->item($x)->getAttribute('style'); $stylecolor = $this->findStyleCSS('color', $style); if ($stylecolor == '') { $headrows[$h]['color'][] = $this->findSpanColor($this->innerHTML($ths->item($x))); } else { $headrows[$h]['color'][] = $stylecolor; } $headrows[$h]['font_name'][] = $this->findStyleCSS('font-family', $style); $headrows[$h]['font_size'][] = $this->findStyleCSS('font-size', $style); $headrows[$h]['border_top'][] = $this->findStyleCSS('border-top', $style); $headrows[$h]['border_bottom'][] = $this->findStyleCSS('border-bottom', $style); $headrows[$h]['border_left'][] = $this->findStyleCSS('border-left', $style); $headrows[$h]['border_right'][] = $this->findStyleCSS('border-right', $style); } else { $headrows[$h]['color'][] = $this->findSpanColor($this->innerHTML($ths->item($x))); } if ($ths->item($x)->hasAttribute('colspan')) { $headrows[$h]['colspan'][] = $ths->item($x)->getAttribute('colspan'); } else { $headrows[$h]['colspan'][] = 1; } if ($ths->item($x)->hasAttribute('rowspan')) { $headrows[$h]['rowspan'][] = $ths->item($x)->getAttribute('rowspan'); } else { $headrows[$h]['rowspan'][] = 1; } if ($ths->item($x)->hasAttribute('align')) { $headrows[$h]['align'][] = $ths->item($x)->getAttribute('align'); } else { $headrows[$h]['align'][] = 'left'; } if ($ths->item($x)->hasAttribute('valign')) { $headrows[$h]['valign'][] = $ths->item($x)->getAttribute('valign'); } else { $headrows[$h]['valign'][] = 'top'; } if ($ths->item($x)->hasAttribute('bgcolor')) { $headrows[$h]['bgcolor'][] = str_replace("#", "", $ths->item($x)->getAttribute('bgcolor')); } else { $headrows[$h]['bgcolor'][] = 'FFFFFF'; } } $h++; } } } $iRow = 0; $fillCell = array(); foreach ($rows as $row) { $iRow++; $tds = $row->getElementsByTagName('td'); if (is_object($tds)) { if ($tds->length > 0) { $bodyrows[$r]['colcnt'] = $tds->length; if ($tds->length > $maxcols) { $maxcols = $tds->length; } $nodes = $tds->length - 1; $iCol = 'A'; for ($x = 0; $x <= $nodes; $x++) { $thistxt = $tds->item($x)->nodeValue; $bodyrows[$r]['td'][] = $thistxt; $bodyrows[$r]['img'][] = $this->collecImg($tds->item($x)->getElementsByTagName('img')); $bodyrows[$r]['bold'][] = $this->findWrapText('b', $this->innerHTML($tds->item($x))); $bodyrows[$r]['italic'][] = $this->findWrapText('i', $this->innerHTML($tds->item($x))); $bodyrows[$r]['underline'][] = $this->findWrapText('u', $this->innerHTML($tds->item($x))); if ($tds->item($x)->hasAttribute('style')) { $style = $tds->item($x)->getAttribute('style'); $stylecolor = $this->findStyleCSS('color', $style); if ($stylecolor == '') { $bodyrows[$r]['color'][] = $this->findSpanColor($this->innerHTML($tds->item($x))); } else { $bodyrows[$r]['color'][] = $stylecolor; } $bodyrows[$h]['font_name'][] = $this->findStyleCSS('font-family', $style); $bodyrows[$h]['font_size'][] = $this->findStyleCSS('font-size', $style); $bodyrows[$h]['border_top'][] = $this->findStyleCSS('border-top', $style); $bodyrows[$h]['border_bottom'][] = $this->findStyleCSS('border-bottom', $style); $bodyrows[$h]['border_left'][] = $this->findStyleCSS('border-left', $style); $bodyrows[$h]['border_right'][] = $this->findStyleCSS('border-right', $style); } else { $bodyrows[$r]['color'][] = $this->findSpanColor($this->innerHTML($tds->item($x))); } if ($tds->item($x)->hasAttribute('colspan')) { $icolspan = $tds->item($x)->getAttribute('colspan'); $bodyrows[$r]['colspan'][] = $tds->item($x)->getAttribute('colspan'); } else { $icolspan = 1; $bodyrows[$r]['colspan'][] = 1; } if ($tds->item($x)->hasAttribute('rowspan')) { $irowspan = $tds->item($x)->getAttribute('rowspan'); $bodyrows[$r]['rowspan'][] = $irowspan; } else { $irowspan = 1; $bodyrows[$r]['rowspan'][] = 1; } if ($tds->item($x)->hasAttribute('align')) { $bodyrows[$r]['align'][] = $tds->item($x)->getAttribute('align'); } else { $bodyrows[$r]['align'][] = 'left'; } if ($tds->item($x)->hasAttribute('valign')) { $bodyrows[$r]['valign'][] = $tds->item($x)->getAttribute('valign'); } else { $bodyrows[$r]['valign'][] = 'top'; } if ($tds->item($x)->hasAttribute('bgcolor')) { $bodyrows[$r]['bgcolor'][] = str_replace("#", "", $tds->item($x)->getAttribute('bgcolor')); } else { $bodyrows[$r]['bgcolor'][] = 'FFFFFF'; } $lastIcol = $iCol; $lastIrow = $iRow; for ($ic = 1; $ic < $icolspan; $ic++) { $lastIcol++; $fillCell[$lastIcol . ':' . $lastIrow] = true; } $lastIcol = $iCol; $lastIrow = $iRow; for ($ir = 1; $ir < $irowspan; $ir++) { $lastIrow++; $fillCell[$lastIcol . ':' . $lastIrow] = true; } $lastIcol = $iCol; $lastIrow = $iRow; for ($ic = 1; $ic < $icolspan; $ic++) { for ($ir = 1; $ir < $irowspan; $ir++) { $lastIrow++; $fillCell[$lastIcol . ':' . $lastIrow] = true; } $lastIcol++; $fillCell[$lastIcol . ':' . $lastIrow] = true; } $iCol++; } $r++; } } } //echo '<pre>';print_r($fillCell); //exit(); if ($z > 0) { $objPHPExcel->createSheet($z); } $suf = $z + 1; $tableid = $tablevar . $suf; $wksheetname = ucfirst($tableid); $objPHPExcel->setActiveSheetIndex($z); // each sheet corresponds to a table in html $objPHPExcel->getActiveSheet()->setTitle($wksheetname); // tab name $worksheet = $objPHPExcel->getActiveSheet(); // set worksheet we're working on $style_overlay = array('font' => array('color' => array('rgb' => '000000'), 'bold' => false), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'CCCCFF')), 'alignment' => array('wrap' => true, 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN))); $xcol = ''; $xrow = 1; $usedhdrows = 0; $heightvars = array(1 => '42', 2 => '42', 3 => '48', 4 => '52', 5 => '58', 6 => '64', 7 => '68', 8 => '76', 9 => '82'); for ($h = 0; $h < count($headrows); $h++) { $th = $headrows[$h]['th']; $colspans = $headrows[$h]['colspan']; $rowspans = $headrows[$h]['rowspan']; $aligns = $headrows[$h]['align']; $valigns = $headrows[$h]['valign']; $bgcolors = $headrows[$h]['bgcolor']; $colcnt = $headrows[$h]['colcnt']; $colors = $headrows[$h]['color']; $bolds = $headrows[$h]['bold']; $italics = $headrows[$h]['italic']; $underlines = $headrows[$h]['underline']; $font_sizes = $headrows[$h]['font_size']; $font_names = $headrows[$h]['font_name']; $border_tops = $headrows[$h]['border_top']; $border_bottoms = $headrows[$h]['border_bottom']; $border_lefts = $headrows[$h]['border_left']; $border_rights = $headrows[$h]['border_right']; $usedhdrows++; $mergedcells = false; for ($t = 0; $t < count($th); $t++) { if ($xcol == '') { $xcol = 'A'; } else { $xcol++; } $thishdg = $th[$t]; $thisalign = $aligns[$t]; $thisvalign = $valigns[$t]; $thiscolspan = (int) $colspans[$t]; $thisrowspan = (int) $rowspans[$t]; $thiscolor = $colors[$t]; $thisbg = $bgcolors[$t]; $thisbold = $bolds[$t]; $thisitalic = $italics[$t]; $thisunderline = $underlines[$t]; $thissize = (double) str_replace(array('pt', 'PT', 'px', 'PX'), '', $font_sizes[$t]); $thissize = $thissize > 0 ? $thissize : 9; $thisname = $font_names[$t]; $thisname = $thisname ? $thisname : 'Arial'; $thisbordertop = str_replace(array('px', 'PX'), '', $border_tops) > 0 && !empty($border_tops) ? PHPExcel_Style_Border::BORDER_THIN : PHPExcel_Style_Border::BORDER_NONE; $thisborderbottom = str_replace(array('px', 'PX'), '', $border_bottoms) > 0 && !empty($border_bottoms) ? PHPExcel_Style_Border::BORDER_THIN : PHPExcel_Style_Border::BORDER_NONE; $thisborderleft = str_replace(array('px', 'PX'), '', $border_lefts) > 0 && !empty($border_lefts) ? PHPExcel_Style_Border::BORDER_THIN : PHPExcel_Style_Border::BORDER_NONE; $thisborderright = str_replace(array('px', 'PX'), '', $border_rights) > 0 && !empty($border_rights) ? PHPExcel_Style_Border::BORDER_THIN : PHPExcel_Style_Border::BORDER_NONE; $strbordertop = str_replace(array('px', 'PX'), '', $border_tops) > 0 ? 'true' : 'false'; $strborderbottom = str_replace(array('px', 'PX'), '', $border_bottoms) > 0 ? 'true' : 'false'; $strborderleft = str_replace(array('px', 'PX'), '', $border_lefts) > 0 ? 'true' : 'false'; $strborderright = str_replace(array('px', 'PX'), '', $border_rights) > 0 ? 'true' : 'false'; $strbold = $thisbold == true ? 'true' : 'false'; $stritalic = $thisitalic == true ? 'true' : 'false'; $strunderline = $thisunderline == true ? 'true' : 'false'; if ($thisbg == 'FFFFFF') { $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_NONE; } else { $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_SOLID; } $style_overlay['alignment']['vertical'] = $thisvalign; // set styles for cell $style_overlay['alignment']['horizontal'] = $thisalign; $style_overlay['font']['color']['rgb'] = $thiscolor; $style_overlay['font']['bold'] = $thisbold; $style_overlay['font']['italic'] = $thisitalic; $style_overlay['font']['underline'] = $thisunderline == true ? PHPExcel_Style_Font::UNDERLINE_SINGLE : PHPExcel_Style_Font::UNDERLINE_NONE; $style_overlay['font']['size'] = $thissize; $style_overlay['font']['name'] = $thisname; $style_overlay['borders']['top']['style'] = $thisbordertop; $style_overlay['borders']['bottom']['style'] = $thisborderbottom; $style_overlay['borders']['left']['style'] = $thisborderleft; $style_overlay['borders']['right']['style'] = $thisborderright; $style_overlay['fill']['color']['rgb'] = $thisbg; $worksheet->setCellValue($xcol . $xrow, $thishdg); $worksheet->getStyle($xcol . $xrow)->applyFromArray($style_overlay); if ($debug) { fwrite($handle, "\n" . $xcol . ":" . $xrow . " Rowspan:" . $thisrowspan . " ColSpan:" . $thiscolspan . " Color:" . $thiscolor . " Align:" . $thisalign . " VAlign:" . $thisvalign . " BGColor:" . $thisbg . " Bold:" . $strbold . " Italic:" . $stritalic . " Underline:" . $strunderline . " Font-name:" . $thisname . " Font-size:" . $thissize . " Border-top: " . $strbordertop . " Border-bottom" . $strborderbottom . " Border-left:" . $strborderleft . " Border-right:" . $strborderright . " cellValue: " . $thishdg); } if ($thiscolspan > 1 && $thisrowspan < 1) { // spans more than 1 column $mergedcells = true; $lastxcol = $xcol; for ($j = 1; $j < $thiscolspan; $j++) { $lastxcol++; $worksheet->setCellValue($lastxcol . $xrow, ''); $worksheet->getStyle($lastxcol . $xrow)->applyFromArray($style_overlay); } $cellRange = $xcol . $xrow . ':' . $lastxcol . $xrow; if ($debug) { fwrite($handle, "\nmergeCells: " . $xcol . ":" . $xrow . " " . $lastxcol . ":" . $xrow); } $worksheet->mergeCells($cellRange); $worksheet->getStyle($cellRange)->applyFromArray($style_overlay); $num_newlines = substr_count($thishdg, "\n"); // count number of newline chars if ($num_newlines > 1) { $rowheight = $heightvars[1]; // default to 35 if (array_key_exists($num_newlines, $heightvars)) { $rowheight = $heightvars[$num_newlines]; } else { $rowheight = 75; } $worksheet->getRowDimension($xrow)->setRowHeight($rowheight); // adjust heading row height } $xcol = $lastxcol; } } $xrow++; $xcol = ''; } //Put an auto filter on last row of heading only if last row was not merged if (!$mergedcells) { $worksheet->setAutoFilter("A{$usedhdrows}:" . $worksheet->getHighestColumn() . $worksheet->getHighestRow()); } if ($debug) { fwrite($handle, "\nautoFilter: A" . $usedhdrows . ":" . $worksheet->getHighestColumn() . $worksheet->getHighestRow()); } // Freeze heading lines starting after heading lines $usedhdrows++; $worksheet->freezePane("A{$usedhdrows}"); if ($debug) { fwrite($handle, "\nfreezePane: A" . $usedhdrows); } // // Loop thru data rows and write them out // $xcol = ''; $xrow = $usedhdrows; for ($b = 0; $b < count($bodyrows); $b++) { $td = $bodyrows[$b]['td']; $img = $bodyrows[$b]['img']; $colcnt = $bodyrows[$b]['colcnt']; $colspans = $bodyrows[$b]['colspan']; $rowspans = $bodyrows[$b]['rowspan']; $aligns = $bodyrows[$b]['align']; $valigns = $bodyrows[$b]['valign']; $bgcolors = $bodyrows[$b]['bgcolor']; $colors = $bodyrows[$b]['color']; $bolds = $bodyrows[$b]['bold']; $italics = $bodyrows[$h]['italic']; $underlines = $bodyrows[$h]['underline']; $font_sizes = $bodyrows[$h]['font_size']; $font_names = $bodyrows[$h]['font_name']; $border_tops = $bodyrows[$h]['border_top']; $border_bottoms = $bodyrows[$h]['border_bottom']; $border_lefts = $bodyrows[$h]['border_left']; $border_rights = $bodyrows[$h]['border_right']; for ($t = 0; $t < count($td); $t++) { if ($xcol == '') { $xcol = 'A'; } else { $xcol++; } if (isset($fillCell[$xcol . ':' . $xrow])) { $xcol = $this->nextCol($xcol, $xrow); } $thistext = $td[$t]; $thisimg = $img[$t]; $thisalign = $aligns[$t]; $thisvalign = $valigns[$t]; $thiscolspan = (int) $colspans[$t]; $thisrowspan = (int) $rowspans[$t]; $thiscolor = $colors[$t]; $thisbg = $bgcolors[$t]; $thisbold = $bolds[$t]; $strbold = $thisbold == true ? 'true' : 'false'; $thisbold = $bolds[$t]; $thisitalic = $italics[$t]; $thisunderline = $underlines[$t]; $thissize = (double) str_replace(array('pt', 'PT', 'px', 'PX'), '', $font_sizes[$t]); $thissize = $thissize > 0 ? $thissize : 9; $thisname = $font_names[$t]; $thisname = $thisname ? $thisname : 'Arial'; $thisbordertop = str_replace(array('px', 'PX'), '', $border_tops) > 0 && !empty($border_tops) ? PHPExcel_Style_Border::BORDER_THIN : PHPExcel_Style_Border::BORDER_NONE; $thisborderbottom = str_replace(array('px', 'PX'), '', $border_bottoms) > 0 && !empty($border_bottoms) ? PHPExcel_Style_Border::BORDER_THIN : PHPExcel_Style_Border::BORDER_NONE; $thisborderleft = str_replace(array('px', 'PX'), '', $border_lefts) > 0 && !empty($border_lefts) ? PHPExcel_Style_Border::BORDER_THIN : PHPExcel_Style_Border::BORDER_NONE; $thisborderright = str_replace(array('px', 'PX'), '', $border_rights) > 0 && !empty($border_rights) ? PHPExcel_Style_Border::BORDER_THIN : PHPExcel_Style_Border::BORDER_NONE; $strbold = $thisbold == true ? 'true' : 'false'; $stritalic = $thisitalic == true ? 'true' : 'false'; $strunderline = $thisunderline == true ? 'true' : 'false'; $strbordertop = str_replace(array('px', 'PX'), '', $border_tops) > 0 ? 'true' : 'false'; $strborderbottom = str_replace(array('px', 'PX'), '', $border_bottoms) > 0 ? 'true' : 'false'; $strborderleft = str_replace(array('px', 'PX'), '', $border_lefts) > 0 ? 'true' : 'false'; $strborderright = str_replace(array('px', 'PX'), '', $border_rights) > 0 ? 'true' : 'false'; if ($thisbg == 'FFFFFF') { $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_NONE; } else { $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_SOLID; } $style_overlay['alignment']['vertical'] = $thisvalign; // set styles for cell $style_overlay['alignment']['horizontal'] = $thisalign; $style_overlay['font']['color']['rgb'] = $thiscolor; $style_overlay['font']['bold'] = $thisbold; $style_overlay['font']['italic'] = $thisitalic; $style_overlay['font']['underline'] = $thisunderline == true ? PHPExcel_Style_Font::UNDERLINE_SINGLE : PHPExcel_Style_Font::UNDERLINE_NONE; $style_overlay['font']['size'] = $thissize; $style_overlay['font']['name'] = $thisname; $style_overlay['borders']['top']['style'] = $thisbordertop; $style_overlay['borders']['bottom']['style'] = $thisborderbottom; $style_overlay['borders']['left']['style'] = $thisborderleft; $style_overlay['borders']['right']['style'] = $thisborderright; $style_overlay['fill']['color']['rgb'] = $thisbg; if ($thiscolspan == 1) { $worksheet->getColumnDimension($xcol)->setWidth(25); } $worksheet->setCellValue($xcol . $xrow, $thistext); if (is_array($thisimg) && count($thisimg) > 0) { $thisCellWidth = $worksheet->getColumnDimension($xcol)->getWidth(); $thisCellHeight = 0; $offsetY = 5; foreach ($thisimg as $Vimg) { $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setWorksheet($worksheet); $objDrawing->setName($Vimg['name']); $objDrawing->setDescription($Vimg['title']); $objDrawing->setPath($Vimg['src']); $objDrawing->setCoordinates($xcol . $xrow); $objDrawing->setOffsetX(1); $objDrawing->setOffsetY($offsetY); $objDrawing->setWidth($Vimg['width']); $objDrawing->setHeight($Vimg['height']); $thisCellHeight += $Vimg['height']; if ($Vimg['width'] > $thisCellWidth) { $worksheet->getColumnDimension($xcol)->setWidth($Vimg['width'] / 5); } if ($Vimg['height'] > 0) { $worksheet->getRowDimension($xrow)->setRowHeight($thisCellHeight); } if ($debug) { fwrite($handle, "\n Insert Image on " . $xcol . ":" . $xrow . ' src:' . $Vimg['src'] . ' Width:' . $Vimg['width'] . ' Height:' . $Vimg['height'] . ' Offset:' . $offsetY); } $offsetY += $Vimg['height'] + 10; } } if ($debug) { fwrite($handle, "\n" . $xcol . ":" . $xrow . " Rowspan:" . $thisrowspan . " ColSpan:" . $thiscolspan . " Color:" . $thiscolor . " Align:" . $thisalign . " VAlign:" . $thisvalign . " BGColor:" . $thisbg . " Bold:" . $strbold . " Italic:" . $stritalic . " Underline:" . $strunderline . " Font-name:" . $thisname . " Font-size:" . $thissize . " Border-top: " . $strbordertop . " Border-bottom" . $strborderbottom . " Border-left:" . $strborderleft . " Border-right:" . $strborderright . " cellValue: " . $thistext); } $worksheet->getStyle($xcol . $xrow)->applyFromArray($style_overlay); if ($thiscolspan > 1 && $thisrowspan == 1) { // spans more than 1 column $lastxcol = $xcol; for ($j = 1; $j < $thiscolspan; $j++) { $lastxcol++; } $cellRange = $xcol . $xrow . ':' . $lastxcol . $xrow; if ($debug) { fwrite($handle, "\nmergeCells: " . $xcol . ":" . $xrow . " " . $lastxcol . ":" . $xrow); } $worksheet->mergeCells($cellRange); $worksheet->getStyle($cellRange)->applyFromArray($style_overlay); $xcol = $lastxcol; } elseif ($thiscolspan == 1 && $thisrowspan > 1) { // spans more than 1 column $lastxrow = $xrow; for ($j = 1; $j < $thisrowspan; $j++) { $lastxrow++; //$fillCell[$xcol.':'.$lastxrow] = true; } $cellRange = $xcol . $xrow . ':' . $xcol . $lastxrow; if ($debug) { fwrite($handle, "\nmergeCells: " . $xcol . ":" . $xrow . " " . $xcol . ":" . $lastxrow); } $worksheet->mergeCells($cellRange); $worksheet->getStyle($cellRange)->applyFromArray($style_overlay); //$xrow = $lastxrow; } elseif ($thiscolspan > 1 && $thisrowspan > 1) { // spans more than 1 column $lastxcol = $xcol; $lastxrow = $xrow; for ($j = 1; $j < $thiscolspan; $j++) { $lastxcol++; for ($k = 1; $k < $thisrowspan; $k++) { $lastxrow++; //$fillCell[$lastxcol.':'.$lastxrow] = true; } } $cellRange = $xcol . $xrow . ':' . $lastxcol . $lastxrow; if ($debug) { fwrite($handle, "\nmergeCells: " . $xcol . ":" . $xrow . " " . $lastxcol . ":" . $lastxrow); } $worksheet->mergeCells($cellRange); $worksheet->getStyle($cellRange)->applyFromArray($style_overlay); $xcol = $lastxcol; //$xrow = $lastxrow; } } $xrow++; $xcol = ''; } // autosize columns to fit data $azcol = 'A'; for ($x = 1; $x == $maxcols; $x++) { $worksheet->getColumnDimension($azcol)->setAutoSize(true); $azcol++; } if ($debug) { fwrite($handle, "\nHEADROWS: " . print_r($headrows, true)); fwrite($handle, "\nBODYROWS: " . print_r($bodyrows, true)); fwrite($handle, "\nFILLCELL: " . print_r($fillCell, true)); } } // end for over tables $objPHPExcel->setActiveSheetIndex(0); // set to first worksheet before close // // Write to Browser // if ($debug) { fclose($handle); } header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header("Content-Disposition: attachment;filename={$fname}"); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); //$objWriter->save($fname); $objWriter->save('php://output'); exit; }
/** * Get default style of workbork. * * @deprecated * @return PHPExcel_Style * @throws Exception */ public function getDefaultStyle() { return $this->_parent->getDefaultStyle(); }
public function support_ocsc_excel_export() { $m = Input::get('m7'); $y = Input::get('y7'); $objPHPExcel = new PHPExcel(); $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial'); $objPHPExcel->getActiveSheet()->setTitle('Sheet1'); $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', 'ชื่อ-นามสกุล'); $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(50); $objPHPExcel->getActiveSheet()->setCellValue('B1', 'ตำแหน่ง'); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50); $objPHPExcel->getActiveSheet()->setCellValue('C1', 'เงินเดือน'); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20); $objPHPExcel->getActiveSheet()->getStyle('C')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1); $objPHPExcel->getActiveSheet()->setCellValue('D1', 'เงินประจำตำแหน่ง'); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20); $objPHPExcel->getActiveSheet()->getStyle('D')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1); $objPHPExcel->getActiveSheet()->setCellValue('E1', 'เงิน พตส.'); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20); $objPHPExcel->getActiveSheet()->getStyle('E')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1); $objPHPExcel->getActiveSheet()->setCellValue('F1', 'OT'); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20); $objPHPExcel->getActiveSheet()->getStyle('F')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1); $objPHPExcel->getActiveSheet()->setCellValue('G1', 'ฉ8'); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20); $objPHPExcel->getActiveSheet()->getStyle('G')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1); $objPHPExcel->getActiveSheet()->setCellValue('H1', 'ไม่ทำเวช'); $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20); $objPHPExcel->getActiveSheet()->getStyle('H')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1); $objPHPExcel->getActiveSheet()->setCellValue('I1', 'ออกหน่วย'); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20); $objPHPExcel->getActiveSheet()->getStyle('I')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1); $objPHPExcel->getActiveSheet()->setCellValue('J1', 'เงินค่าตอบแทนรายเดือน'); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20); $objPHPExcel->getActiveSheet()->getStyle('J')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1); $objPHPExcel->getActiveSheet()->setCellValue('K1', 'เงินค่าตอบแทนเต็มขั้น'); $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20); $objPHPExcel->getActiveSheet()->getStyle('K')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1); $objPHPExcel->getActiveSheet()->setCellValue('L1', 'ค่าเดินทาง'); $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20); $objPHPExcel->getActiveSheet()->getStyle('L')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1); $objPHPExcel->getActiveSheet()->setCellValue('M1', 'เงินพิเศษ'); $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20); $objPHPExcel->getActiveSheet()->getStyle('M')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1); $objPHPExcel->getActiveSheet()->setCellValue('N1', 'ตกเบิก/ครองชีพ'); $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20); $objPHPExcel->getActiveSheet()->getStyle('N')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1); $sql0 = ' select cid, concat(pname,"",fname," ",lname) as name from n_datageneral '; $sql0 .= ' where cid in ( select cid from s_salary_ocsc_detail group by cid )'; $sql0 .= ' order by datainfoID'; $result0 = DB::select($sql0); $row = 0; foreach ($result0 as $key0) { $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row + 2, $key0->name); $sql = ' select s.salary, s.r_c, s.r_other, s.r_pt, s.pts2, s.ot, s.ch8, s.no_v, s.outpcu, s.special_m, s.u_travel, s.game_sp'; $sql .= ' , (select positionName from n_position where position_id=(select ps.position_id from n_position_salary ps where ps.cid=n.cid order by ps.salaryID desc limit 1)) as position'; $sql .= ' from s_salary_ocsc_detail s'; $sql .= ' left join n_datageneral n on n.cid=s.cid'; $sql .= ' where year(s.order_date)=' . $y . ' and month(s.order_date)=' . $m . ' and n.cid=' . $key0->cid . ' '; $sql .= ' order by n.datainfoID'; $result = DB::select($sql); foreach ($result as $key) { $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row + 2, $key->position); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row + 2, $key->salary); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row + 2, $key->r_c); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row + 2, $key->pts2); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row + 2, $key->ot); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $row + 2, $key->ch8); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $row + 2, $key->no_v); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $row + 2, $key->outpcu); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $row + 2, $key->special_m); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $row + 2, $key->r_pt); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $row + 2, $key->u_travel); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(12, $row + 2, $key->r_other); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(13, $row + 2, $key->game_sp); } // end foreach result $row++; } //end foreach result0 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); // Set excel version 2007 $objWriter->save(storage_path() . "/excel/reportSupportData_ocsc.xls"); return Response::download(storage_path() . "/excel/reportSupportData_ocsc.xls", "reportSupportData_ocsc.xls"); }
/** * Set default font * * @param string $name font name * @param int $size font size * @return $this for method chaining */ public function setDefaultFont($name, $size) { $this->_xls->getDefaultStyle()->getFont()->setName($name); $this->_xls->getDefaultStyle()->getFont()->setSize($size); return $this; }
/** * @author caochunhui@dachuwang.com * @description 用数组和地址直接生成excel文件 * 每一个数组占一个sheet */ private function _convert_array_to_excel($arr = array(), $sheet_titles = array(), $out_name = '', $barcode_arr = array()) { //下面的代码是抄的。 //set cache $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp; PHPExcel_Settings::setCacheStorageMethod($cacheMethod); //open excel file $write_objPHPExcel = new PHPExcel(); $write_objPHPExcel->getDefaultStyle()->getFont()->setName('simsun')->setSize(10); //下面要循环了 $sheet_cnt = 0; foreach ($arr as $item) { //用订单id.csv来命名每一个sheet $out_sheet = new PHPExcel_Worksheet($write_objPHPExcel, $sheet_titles[$sheet_cnt]); //$out_sheet->setTitle($item); //row index start from 1 $row_index = 0; foreach ($item as $row) { $row_index++; //$cellIterator = $row->getCellIterator(); //$cellIterator->setIterateOnlyExistingCells(false); //column index start from 0 $column_index = -1; foreach ($row as $cell) { $column_index++; //var_dump($cell); $out_sheet->setCellValueByColumnAndRow($column_index, $row_index, $cell, PHPExcel_Cell_DataType::TYPE_STRING); } } //如果条码数组不为空,那么说明需要在sheet里插入条码 if (!empty($barcode_arr) && isset($barcode_arr[$sheet_cnt])) { $barcode_download_res = $this->_download_barcode($barcode_arr[$sheet_cnt]); if ($barcode_download_res['code'] == 200) { //no pic you say a jb $pic_path = $barcode_download_res['file']; $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('barcode'); $objDrawing->setDescription(''); $objDrawing->setPath($pic_path); $objDrawing->setHeight(50); $objDrawing->setCoordinates('D26'); //$objDrawing->setOffsetX(10); //$objDrawing->getShadow()->setVisible(true); //$objDrawing->getShadow()->setDirection(36); $objDrawing->setWorksheet($out_sheet); //no pic you say a jb } } $write_objPHPExcel->addSheet($out_sheet); $sheet_cnt++; } $write_objPHPExcel->removeSheetByIndex(0); //删除第一个空sheet //上面要循环了 //上面的代码是抄的 //write excel file $objWriter = new PHPExcel_Writer_Excel2007($write_objPHPExcel); $dir_name = dirname($out_name); if (!is_dir($dir_name)) { $res = mkdir($dir_name, 0777, TRUE); } $objWriter->save($out_name); }
function almacenarCSV() { global $connI; if (is_uploaded_file($_FILES['userfile']['tmp_name'])) { copy($_FILES['userfile']['tmp_name'], "../files/blConteIMO{$mscIdUsuario}.csv"); } else { $msg = "<h1><font color=\"red\">Error en el envio del archivo, vuela a intentarlo! " . $_FILES['userfile']['name']; $msg .= "</font></h1>"; echo $msg; } $fp = fopen("../files/blConteIMO{$mscIdUsuario}.csv", "r"); $validFile = "True"; $l = 1; while ($data = fgetcsv($fp, 1000, ";")) { // La linea 4 es donde esta el encabezado if ($l == 1) { foreach ($data as $campo) { $campo = addslashes($campo); //echo $campo."<br>"; $campo = ereg_replace("\n", "", $campo); $campo = ereg_replace("\r", "", $campo); $data[] = $campo; } $data = $data; // Encabezado del archivo de Excel. if ($data[0] != "BL") { $validFile = "False"; break; } if ($data[1] != "CONTENEDOR") { $validFile = "False"; break; } $data = ""; // rompe para que ya no siga con las demas lineas, solo queriamos la linea 3. // y con esto aguilizamos el proceso. break; } // fin del if $l++; // incrementa la linea. } // fin del while fclose($fp); // Verificar si el encabezado del archivo es correcto. if ($validFile == "False") { $msg = "Archivo corrupto, o los encabezados han cambiado, favor de verificar."; $error[archivo] = $msg; echo "<font color=\"red\">{$msg}<p></p></font>"; return $error; } else { $reportName = "IMO / Sobredimensionado en MscLink"; // Incluir la libreria PHPExcel require '../include/PHPExcel/PHPExcel.php'; // Reservar memoria en servidor PHP // Si el archivo final tiene 5Mb, reservar 500Mb // Por cada operación, phpExcel mapea en memoria la imagen del archivo y esto satura la mamoria ini_set("memory_limit", "512M"); // Estilos Arreglo $styleEnc = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), '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'))); $styleSombra = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => '80E9E07A'), 'endcolor' => array('argb' => 'FFFFFFFF'))); $styleTitulo = array('font' => array('bold' => true, 'size' => 14), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)); $styleSubtitulo = array('font' => array('bold' => true, 'size' => 10), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)); // ---------------------------- // Hoja 1 // Nota : No usar acentos!!! $objPHPExcel = new PHPExcel(); $objPHPExcel->getActiveSheet()->setTitle('Hoja1'); $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial'); $objPHPExcel->getDefaultStyle()->getFont()->setSize(8); // HOJA 2 $workSheet2 = new PHPExcel_Worksheet($objPHPExcel, 'Hoja2'); $objPHPExcel->addSheet($workSheet2, 1); $workSheet2->getDefaultStyle()->getFont()->setSize(8); $workSheet2->getDefaultStyle()->getFont()->setName('Arial'); // ----------------------------------------------- // ENCABEZADOS // ----------------------------------------------- // Se crea el arreglo de hojas. Esto es como llevan el mismo encabezado en todas las hojas, solo es recorrer el index = hojas. $objPHPExcel->setActiveSheetIndex(0); // Se aplica estilo al encabezado $objPHPExcel->getActiveSheet()->getStyle('A7:K7')->applyFromArray($styleEnc); // Encabezados $headings = array('BL', 'Contenedor', 'IMO_Number', 'Fumigation_Residue_IMO_Class', 'OD_Height', 'OD_Length_Front', 'OD_Length_Back', 'OD_Width_Right', 'OD_Width_Left', 'Dimension_Unit', 'Is_OverDimension'); // Escribe los encabezados $rowNumber = 7; // Freeze pane so that the heading line won't scroll $objPHPExcel->getActiveSheet()->freezePane('A8'); $col = 'A'; foreach ($headings as $heading) { $objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading); $col++; } // AutoFiltro $objPHPExcel->getActiveSheet()->setAutoFilter('A7:K7'); // Auto Ajuste de Ancho en Columna $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(10); // Titulo $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Mediterranean Shipping Company Mexico, S.A. DE C.V.')->getStyle('A1')->applyFromArray($styleTitulo); // SUBTitulo $objPHPExcel->getActiveSheet()->setCellValue('A2', "Solo como Agentes / As Agent only"); $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo); $objPHPExcel->getActiveSheet()->setCellValue('A3', "{$reportName}"); $objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo); /*$objPHPExcel->getActiveSheet()->setCellValue('A6', "$buque $viaje"); $objPHPExcel->getActiveSheet()->getStyle('A6')->applyFromArray($styleSubtitulo);*/ $rowNumber = 8; // donde inicia los datos //echo "<b>Ok.......</b><br>"; // por default flgRec es igual a 1. // Asume que estan bien los datos para proceder a grabar. // Hasta que en la validacion se cambie a 0, entonces ya no graba, (ver mas abajo). $flgRec = 1; $fp = fopen("../files/blConteIMO{$mscIdUsuario}.csv", "r"); $linea = 1; $e = 0; // elemento del arreglo. while ($dataY = fgetcsv($fp, 1000, ";")) { // Se inicializan las vars como vacias para evitar errores. $flgRec = ""; //echo $dataY[0]."<br>"; // La linea 1 es donde esta el encabezado if ($linea > 1) { for ($i = 0; $i < count($dataY); $i++) { $campo = ereg_replace("\n", "", $dataY[$i]); $campo = ereg_replace("\r", "", $dataY[$i]); $data[$i] = $campo; } $data = $data; $datoX[contenedor] = trim(strtoupper($data[1])); $bl = trim(strtoupper($data[0])); $datoX[bl] = getValidBl($bl); // --- INICIA PROCESO DE VALIDACION --- // VALIDAR CONTENEDOR $conte = $datoX[contenedor]; // --- VALIDAR BL --- $bl = $datoX[bl]; //echo "$bl / $conte <br>"; // ----------------------------------- // HOJA 1 // ----------------------------------- $objPHPExcel->setActiveSheetIndex(0); $sql = "SELECT BL.Bill_Of_Lading_Number, BL.Assigned_Container_Number, BL.IMO_Number, BL.Fumigation_Residue_IMO_Class, BL. OD_Height, BL.OD_Length_Front, BL.OD_Length_Back, BL. OD_Width_Right, \n BL. OD_Width_Left, BL.OD_Unit as Dimension_Unit, BL.Is_OverDimension\n FROM Interlink_DFeed90.dfeed .FNTB_Booking_BL_CSC (0, NULL) BL\n WHERE BL.Bill_Of_Lading_Number='{$bl}' and bl.Assigned_Container_Number='{$conte}' \n "; $rs = odbc_exec($connI, $sql); if (!$rs) { exit("Error en la consulta SQL"); } $lin = 1; //$rowNumber = 8; // donde inicia los datos while (odbc_fetch_row($rs)) { $Bill_Of_Lading_Number = odbc_result($rs, "Bill_Of_Lading_Number"); $Assigned_Container_Number = odbc_result($rs, "Assigned_Container_Number"); $IMO_Number = odbc_result($rs, "IMO_Number"); $Fumigation_Residue_IMO_Class = odbc_result($rs, "Fumigation_Residue_IMO_Class"); $OD_Height = odbc_result($rs, "OD_Height"); $OD_Length_Front = odbc_result($rs, "OD_Length_Front"); $OD_Length_Back = odbc_result($rs, "OD_Length_Back"); $OD_Width_Right = odbc_result($rs, "OD_Width_Right"); $OD_Width_Left = odbc_result($rs, "OD_Width_Left"); $Dimension_Unit = odbc_result($rs, "Dimension_Unit"); $Is_OverDimension = odbc_result($rs, "Is_OverDimension"); //echo "$Bill_Of_Lading_Number - $Assigned_Container_Number | $IMO_Number | $Fumigation_Residue_IMO_Class | $Dimension_Unit | $Is_OverDimension <br>"; // Por manejo de memoria se deben de meter los datos con las menos operaciones posibles. $objPHPExcel->getActiveSheet()->setCellValue('A' . $rowNumber, $Bill_Of_Lading_Number)->setCellValue('B' . $rowNumber, $Assigned_Container_Number)->setCellValue('C' . $rowNumber, $IMO_Number)->setCellValue('D' . $rowNumber, $Fumigation_Residue_IMO_Class)->setCellValue('E' . $rowNumber, $OD_Height)->setCellValue('F' . $rowNumber, $OD_Length_Front)->setCellValue('G' . $rowNumber, $OD_Length_Back)->setCellValue('H' . $rowNumber, $OD_Width_Right)->setCellValue('I' . $rowNumber, $OD_Width_Left)->setCellValue('J' . $rowNumber, $Dimension_Unit); $rowNumber++; //$lin++; } } $linea++; } // fin del while // Loregreso a la hoja 1. $objPHPExcel->setActiveSheetIndex(0); // Se modifican los encabezados del HTTP para indicar que se envia un archivo de Excel2007. header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition:attachment;filename="repFactuMLink.xlsx"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); exit; fclose($fp); } // fin else // --------------- Reporte de errores --------------------------- echo "<br>"; if (!empty($error[contenedor])) { foreach ($error[contenedor] as $err) { echo $err . "<br>"; } } echo "<br>"; if (!empty($error[equipo])) { foreach ($error[equipo] as $err) { echo $err . "<br>"; } } echo "<br>"; if (!empty($error[bl])) { foreach ($error[bl] as $err) { echo $err . "<br>"; } } echo "<br>"; }
<?php $nama_bulan = array("", "Januari", "Februari", "Maret", "April", "Mei", "Juni", "Juli", "Agustus", "September", "Oktober", "November", "Desember"); $tahun = date("Y"); $bulan = $nama_bulan[date("n")]; // PHPExcel object $xls = new PHPExcel(); // Set properties $xls->getProperties()->setCreator("SIM Puskesmas Bogor Tengah")->setLastModifiedBy("SIM Puskesmas Bogor Tengah")->setTitle("Biodata Fungsional")->setSubject("Biodata Fungsional Pegawai Puskesmas Bogor Tengah"); $xls->getDefaultStyle()->getFont()->setName('Tahoma')->setSize(10); $styleThinBlackBorderOutline = array('borders' => array('allborders' => array('style' => Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000')))); // satu pegawai satu sheet $sheet = 0; foreach ($daftar_pegawai as $pegawai) { $xls->createSheet(); $xls->setActiveSheetIndex($sheet); $xls->getActiveSheet()->setTitle($pegawai['nama']); $xls->getActiveSheet()->getColumnDimension('A')->setWidth(4.14); $xls->getActiveSheet()->getColumnDimension('B')->setWidth(22.86); $xls->getActiveSheet()->getColumnDimension('C')->setWidth(17); $xls->getActiveSheet()->getColumnDimension('D')->setWidth(14.14); $xls->getActiveSheet()->getColumnDimension('E')->setWidth(13.14); $xls->getActiveSheet()->getColumnDimension('F')->setWidth(10.29); $xls->getActiveSheet()->getColumnDimension('G')->setWidth(6.43); // header $xls->getActiveSheet()->mergeCells("A1:G1")->setCellValue('A1', "SURAT KETERANGAN"); $xls->getActiveSheet()->getStyle('A1')->getFont()->setSize(16); $xls->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal('center'); $xls->getActiveSheet()->getRowDimension('1')->setRowHeight(19.5); $xls->getActiveSheet()->mergeCells("A2:G2")->setCellValue('A2', "UNTUK MENDAPATKAN PEMBAYARAN TUNJANGAN KELUARGA"); $xls->getActiveSheet()->getStyle('A2')->getFont()->setSize(16);
private function exportExcel($headers, $data) { $objPHPExcel = new \PHPExcel(); $objPHPExcel->getProperties()->setCreator("Bunny's Kinder")->setTitle('Listado de alumnos y padres')->setSubject('Listado de alumnos y padres'); $styleArray = array('borders' => array('allborders' => array('style' => \PHPExcel_Style_Border::BORDER_THIN))); $objPHPExcel->getDefaultStyle()->applyFromArray($styleArray); $objPHPExcel->setActiveSheetIndex(0); $index = 1; $counter = 0; foreach ($headers as $header) { $letter = \PHPExcel_Cell::stringFromColumnIndex($counter); $objPHPExcel->getActiveSheet()->setCellValue($letter . $index, $header); $objPHPExcel->getActiveSheet()->getColumnDimension($letter)->setAutoSize(true); ++$counter; } ++$index; foreach ($data as $row) { $counter = 0; foreach ($row as $field) { $letter = \PHPExcel_Cell::stringFromColumnIndex($counter); $objPHPExcel->getActiveSheet()->setCellValue($letter . $index, $field); $objPHPExcel->getActiveSheet()->getColumnDimension($letter)->setAutoSize(true); ++$counter; } ++$index; } $fileName = 'alumnos-' . date('d-m-Y') . '.xls'; $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $response = new \Symfony\Component\HttpFoundation\StreamedResponse(function () use($objWriter) { $objWriter->save('php://output'); }); $dispositionHeader = $response->headers->makeDisposition('attachment', $fileName); $response->headers->set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8'); $response->headers->set('Pragma', 'public'); $response->headers->set('Cache-Control', 'maxage=1'); $response->headers->set('Content-Disposition', $dispositionHeader); return $response; }
function reporte($bls) { global $linkSrv, $linkDB, $linkUsr, $linkPass; // ------------------------------------------------------------------------------- // ODBC // El sistema se trata de conectar mediante ODBC nativo a la base de datos MscLink // ------------------------------------------------------------------------------- $dsn = "Driver={SQL Server};Server={$linkSrv};Database={$linkDB};Integrated Security=SSPI;Persist Security Info=False;"; // Se realiza la conexón con los datos especificados anteriormente $conn = odbc_connect($dsn, $linkUsr, $linkPass); $conn2 = odbc_connect($dsn, $linkUsr, $linkPass); // Incluir la libreria PHPExcel require '../include/PHPExcel/PHPExcel.php'; // Reservar memoria en servidor PHP // Si el archivo final tiene 5Mb, reservar 500Mb // Por cada operación, phpExcel mapea en memoria la imagen del archivo y esto satura la mamoria ini_set("memory_limit", "512M"); // Estilos Arreglo $styleEnc = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), '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'))); $styleSombra = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => '80E9E07A'), 'endcolor' => array('argb' => 'FFFFFFFF'))); $styleTitulo = array('font' => array('bold' => true, 'size' => 14), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)); $styleSubtitulo = array('font' => array('bold' => true, 'size' => 10), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)); // ---------------------------- // Hoja 1 // Nota : No usar acentos!!! $objPHPExcel = new PHPExcel(); $objPHPExcel->getActiveSheet()->setTitle('Dep.Garantia'); $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial'); $objPHPExcel->getDefaultStyle()->getFont()->setSize(8); // HOJA 2 $workSheet2 = new PHPExcel_Worksheet($objPHPExcel, 'Demoras'); $objPHPExcel->addSheet($workSheet2, 1); $workSheet2->getDefaultStyle()->getFont()->setSize(8); $workSheet2->getDefaultStyle()->getFont()->setName('Arial'); // ----------------------------------------------- // ENCABEZADOS // ----------------------------------------------- // Se crea el arreglo de hojas. Esto es como llevan el mismo encabezado en todas las hojas, solo es recorrer el index = hojas. $arrHojas[] = 0; //$arrHojas[]=1; foreach ($arrHojas as $hoja) { $objPHPExcel->setActiveSheetIndex($hoja); // Se aplica estilo al encabezado $objPHPExcel->getActiveSheet()->getStyle('A7:Q7')->applyFromArray($styleEnc); // Encabezados $headings = array('Concepto', 'Referencia', 'Nota', 'Nota1', 'Nota2', 'Nota3', 'Monto'); // Escribe los encabezados $rowNumber = 7; // Freeze pane so that the heading line won't scroll $objPHPExcel->getActiveSheet()->freezePane('A8'); $col = 'A'; foreach ($headings as $heading) { $objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading); $col++; } // AutoFiltro $objPHPExcel->getActiveSheet()->setAutoFilter('A7:G7'); // Auto Ajuste de Ancho en Columna $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(25); // Titulo $objPHPExcel->getActiveSheet()->setCellValue('A1', 'MEDITERRANEAN SHIPPING COMPANY MEXICO, S.A. DE C.V.')->getStyle('A1')->applyFromArray($styleTitulo); // SUBTitulo $objPHPExcel->getActiveSheet()->setCellValue('A2', '.'); $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo); $objPHPExcel->getActiveSheet()->setCellValue('A3', '.'); $objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo); $objPHPExcel->getActiveSheet()->setCellValue('A4', "."); $objPHPExcel->getActiveSheet()->getStyle('A4')->applyFromArray($styleSubtitulo); $objPHPExcel->getActiveSheet()->setCellValue('A5', 'Depositos en Garantia MscLink'); $objPHPExcel->getActiveSheet()->getStyle('A5')->applyFromArray($styleSubtitulo); } // ------------------------- // Encabezado de la hoja 2 // ------------------------- $objPHPExcel->setActiveSheetIndex(1); // Se aplica estilo al encabezado $objPHPExcel->getActiveSheet()->getStyle('A7:S7')->applyFromArray($styleEnc); // Encabezados $headings = array('Bl', 'Contenedor', 'Concepto', 'Evento', 'Ev.Tiempo', 'EventoFin', 'EventoFin.Tiempo', 'POD', 'TMSCodeShp', 'Shipper', 'Inv.Company', 'DiasLibres', 'Invoice', 'Doc.Date', 'Amount', 'InvNum', 'TotInv', 'PaidInv', 'OustInv'); // Escribe los encabezados $rowNumber = 7; // Freeze pane so that the heading line won't scroll $objPHPExcel->getActiveSheet()->freezePane('A8'); $col = 'A'; foreach ($headings as $heading) { $objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading); $col++; } // AutoFiltro $objPHPExcel->getActiveSheet()->setAutoFilter('A7:S7'); // Auto Ajuste de Ancho en Columna $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(25); // Titulo $objPHPExcel->getActiveSheet()->setCellValue('A1', 'MEDITERRANEAN SHIPPING COMPANY MEXICO, S.A. DE C.V.')->getStyle('A1')->applyFromArray($styleTitulo); // SUBTitulo $objPHPExcel->getActiveSheet()->setCellValue('A2', '.'); $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo); $objPHPExcel->getActiveSheet()->setCellValue('A3', '.'); $objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo); $objPHPExcel->getActiveSheet()->setCellValue('A4', "."); $objPHPExcel->getActiveSheet()->getStyle('A4')->applyFromArray($styleSubtitulo); $objPHPExcel->getActiveSheet()->setCellValue('A5', 'Depositos en Garantia MscLink'); $objPHPExcel->getActiveSheet()->getStyle('A5')->applyFromArray($styleSubtitulo); // ---------------------------------------------------------------------------- // BL DE PRUEBA // MSCUXL570178 // ----------------------------------- // HOJA 1 // ----------------------------------- $objPHPExcel->setActiveSheetIndex(0); // Simple query $sql = "\n SELECT\n A.Name AS CONCEPTO,\n T.ReferenceNumber AS REFERENCIA,\n T2.Note AS NOTA,\n T2.Reference1 AS NOTA1,\n T2.Reference2 AS NOTA2,\n T2.Reference3 AS NOTA3,\n T.Available_Amount AS MONTO\n FROM (\n SELECT T.ReferenceNumber, SUM(T.Currency_Amount) Available_Amount\n FROM Interlink_Accounting90.Accounting.Accounts A (NOLOCK)\n INNER JOIN Interlink_Accounting90.Accounting.Transactions T (NOLOCK) ON T.Account_id = A.Account_id\n WHERE Name = 'Depositos en Garantia MXN'\n GROUP BY T.ReferenceNumber, T.Note, T.Reference1, T.Reference2, T.Reference3\n HAVING SUM(T.Currency_Amount) < 0\n ) T\n INNER JOIN Interlink_Accounting90.Accounting.Transactions T2 (NOLOCK) ON T.ReferenceNumber = T2.ReferenceNumber\n INNER JOIN Interlink_Accounting90.Accounting.Accounts A (NOLOCK) ON A.Account_id = T2.Account_id AND Name = 'Depositos en Garantia MXN'\n WHERE T.Available_Amount < 0\n AND T2.Amount < 0\n "; $sql .= "AND ("; unset($arrBls); $arrBls = explode("\n", $bls); $r = 1; foreach ($arrBls as $bl) { if (!empty($bl)) { $bl = str_replace("\n", "", $bl); $bl = str_replace("\r", "", $bl); $bl = trim($bl); if ($r == 1) { $sql .= "T.ReferenceNumber like '%{$bl}%' "; } elseif ($r > 1) { $sql .= "OR T.ReferenceNumber like '%{$bl}%' "; } $r++; } } $sql .= ")"; $rs = odbc_exec($conn, $sql); if (!$rs) { exit("Error en la consulta SQL"); } $lin = 1; $rowNumber = 8; // donde inicia los datos while (odbc_fetch_row($rs)) { $concepto = odbc_result($rs, "CONCEPTO"); $refer = odbc_result($rs, "REFERENCIA"); $nota = odbc_result($rs, "NOTA"); $nota1 = odbc_result($rs, "NOTA1"); $nota2 = odbc_result($rs, "NOTA2"); $nota3 = odbc_result($rs, "NOTA3"); $monto = odbc_result($rs, "MONTO"); // Por manejo de memoria se deben de meter los datos con las menos operaciones posibles. $objPHPExcel->getActiveSheet()->setCellValue('A' . $rowNumber, $concepto)->setCellValue('B' . $rowNumber, $refer)->setCellValue('C' . $rowNumber, $nota)->setCellValue('D' . $rowNumber, $nota1)->setCellValue('E' . $rowNumber, $nota2)->setCellValue('F' . $rowNumber, $nota3)->setCellValue('G' . $rowNumber, $monto); $rowNumber++; } // ------------------------------ // Hoja 2 - DEMORAS // ------------------------------ $sqlBL = ""; foreach ($arrBls as $bl) { if (!empty($bl)) { $bl = str_replace("\n", "", $bl); $bl = str_replace("\r", "", $bl); $bl = trim($bl); $sqlBL .= "'{$bl}',"; } } $blsInTxt = substr($sqlBL, 0, -1); $objPHPExcel->setActiveSheetIndex(1); // Simple query $sql = "\n SELECT\n CH.Show_as AS CONCEPTO,\n CH.Start_Event AS EVENTO,\n CH.start_event_datetime AS EVENTOTIEMPO,\n CH.End_Event AS ENVENTOFIN,\n CH.end_event_datetime AS EVENTOFINTIEMPO,\n CH.POD_name AS PODNAME,\n CH.Bill_Of_Lading_Number AS BL,\n CH.Equipment_Number AS CONTE,\n ADCSH.MSCCode AS TMSCODESHI,\n ADCCN.Company AS SHIPPER,\n ADCIC.Company AS INVCOMPANY,\n CH.Charge_Level_0_Days AS DIASLIBRES,\n CH.invoice_number AS INVOICE,\n I.Doc_Date AS DOCDATE,\n CH.Charge_Amount AS AMOUNTX,\n\n (select I.Invoice_Number\n from Interlink_Accounting90. Accounting .InvoiceState I (NOLOCK )\n left JOIN Interlink_Accounting90 .dbo . ACC_Invoice AI ( NOLOCK) ON AI.ACC_Invoice_id = I.ACC_Invoice_id\n where i.InvoiceState_id=(select MAX(InvoiceState_id) from Interlink_Accounting90.Accounting.InvoiceState (NOLOCK)\n where Invoice_Number=CH.invoice_number)) as InvNum,\n (select (AI . Inv_Total_Inv_Currency + AI. Inv_Total_VAT_Inv_Currency) Total\n from Interlink_Accounting90. Accounting .InvoiceState I (NOLOCK )\n left JOIN Interlink_Accounting90 .dbo . ACC_Invoice AI ( NOLOCK) ON AI.ACC_Invoice_id = I.ACC_Invoice_id\n where i.InvoiceState_id=(select MAX(InvoiceState_id) from Interlink_Accounting90.Accounting.InvoiceState (NOLOCK)\n where Invoice_Number=CH.invoice_number)) as TotInv,\n (select I.Paid\n from Interlink_Accounting90. Accounting .InvoiceState I (NOLOCK )\n left JOIN Interlink_Accounting90 .dbo . ACC_Invoice AI ( NOLOCK) ON AI.ACC_Invoice_id = I.ACC_Invoice_id\n where i.InvoiceState_id=(select MAX(InvoiceState_id) from Interlink_Accounting90.Accounting.InvoiceState (NOLOCK)\n where Invoice_Number=CH.invoice_number)) as PaidInv,\n (select ( AI .Inv_Total_Inv_Currency + AI. Inv_Total_VAT_Inv_Currency - I .Paid )\n from Interlink_Accounting90. Accounting .InvoiceState I (NOLOCK )\n left JOIN Interlink_Accounting90 .dbo . ACC_Invoice AI ( NOLOCK) ON AI.ACC_Invoice_id = I.ACC_Invoice_id\n where i.InvoiceState_id=(select MAX(InvoiceState_id) from Interlink_Accounting90.Accounting.InvoiceState (NOLOCK)\n where Invoice_Number=CH.invoice_number )) as OustInv\n\n FROM\n Interlink_DFeed90.dfeed.ILIM_FNTB_Charges () CH\n INNER JOIN Interlink_Main90.dbo.Invoice I (NOLOCK) ON I.Invoice_id = CH.Invoice_id\n LEFT JOIN Interlink_Dfeed90.dfeed.FNTB_AssocDocCompany ('CONSIGN') ADCCN ON ADCCN.Bill_Of_Lading_id = CH.bill_of_lading_id\n LEFT JOIN Interlink_Dfeed90.dfeed.FNTB_AssocDocCompany ('SHIPPER') ADCSH ON ADCSH.Bill_Of_Lading_id = CH.bill_of_lading_id\n LEFT JOIN Interlink_Dfeed90.dfeed.FNTB_AssocDocCompany ('INVCMPBL') ADCIC ON ADCIC.Bill_Of_Lading_id = CH.bill_of_lading_id\n WHERE\n Show_as = 'DEMURRAGE'\n AND CH.Bill_Of_Lading_Number in ({$blsInTxt})\n AND Charge_Status IN ('INVOICED')\n "; $rs = odbc_exec($conn, $sql); if (!$rs) { exit("Error en la consulta SQL"); } $rowNumber = 8; // donde inicia los datos while (odbc_fetch_row($rs)) { $c1 = odbc_result($rs, "BL"); $c2 = odbc_result($rs, "CONTE"); $c3 = odbc_result($rs, "CONCEPTO"); $c4 = odbc_result($rs, "EVENTO"); $c5 = odbc_result($rs, "EVENTOTIEMPO"); $c6 = odbc_result($rs, "ENVENTOFIN"); $c7 = odbc_result($rs, "EVENTOFINTIEMPO"); $c8 = odbc_result($rs, "PODNAME"); $c9 = odbc_result($rs, "TMSCODESHI"); $c10 = odbc_result($rs, "SHIPPER"); $c11 = odbc_result($rs, "INVCOMPANY"); $c12 = odbc_result($rs, "DIASLIBRES"); $c13 = odbc_result($rs, "INVOICE"); $c14 = odbc_result($rs, "DOCDATE"); $c15 = odbc_result($rs, "AMOUNTX"); $c16 = odbc_result($rs, "InvNum"); $c17 = odbc_result($rs, "TotInv"); $c18 = odbc_result($rs, "PaidInv"); $c19 = odbc_result($rs, "OustInv"); // Por manejo de memoria se deben de meter los datos con las menos operaciones posibles. $objPHPExcel->getActiveSheet()->setCellValue('A' . $rowNumber, $c1)->setCellValue('B' . $rowNumber, $c2)->setCellValue('C' . $rowNumber, $c3)->setCellValue('D' . $rowNumber, $c4)->setCellValue('E' . $rowNumber, $c5)->setCellValue('F' . $rowNumber, $c6)->setCellValue('G' . $rowNumber, $c7)->setCellValue('H' . $rowNumber, $c8)->setCellValue('I' . $rowNumber, $c9)->setCellValue('J' . $rowNumber, $c10)->setCellValue('K' . $rowNumber, $c11)->setCellValue('L' . $rowNumber, $c12)->setCellValue('M' . $rowNumber, $c13)->setCellValue('N' . $rowNumber, $c14)->setCellValue('O' . $rowNumber, $c15)->setCellValue('P' . $rowNumber, $c16)->setCellValue('Q' . $rowNumber, $c17)->setCellValue('R' . $rowNumber, $c18)->setCellValue('S' . $rowNumber, $c19); $rowNumber++; } // Loregreso a la hoja 1. $objPHPExcel->setActiveSheetIndex(0); // Se modifican los encabezados del HTTP para indicar que se envia un archivo de Excel2007. header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition:attachment;filename="repForCapitania.xlsx"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); exit; }
public function download($export_type, $offset = null, $rows = null, $min_id = null, $max_id = null) { // we use our own error handler global $registry; $registry = $this->registry; set_error_handler('error_handler_for_export_import', E_ALL); register_shutdown_function('fatal_error_shutdown_handler_for_export_import'); // Use the PHPExcel package from http://phpexcel.codeplex.com/ $cwd = getcwd(); chdir(DIR_SYSTEM . 'PHPExcel'); require_once 'Classes/PHPExcel.php'; PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_ExportImportValueBinder()); chdir($cwd); // find out whether all data is to be downloaded $all = !isset($offset) && !isset($rows) && !isset($min_id) && !isset($max_id); // Memory Optimization if ($this->config->get('export_import_settings_use_export_cache')) { $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp; $cacheSettings = array('memoryCacheSize' => '16MB'); PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings); } try { // set appropriate timeout limit set_time_limit(1800); $languages = $this->getLanguages(); $default_language_id = $this->getDefaultLanguageId(); // create a new workbook $workbook = new PHPExcel(); // set some default styles $workbook->getDefaultStyle()->getFont()->setName('Arial'); $workbook->getDefaultStyle()->getFont()->setSize(10); //$workbook->getDefaultStyle()->getAlignment()->setIndent(0.5); $workbook->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); $workbook->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $workbook->getDefaultStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL); // pre-define some commonly used styles $box_format = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'F0F0F0'))); $text_format = array('numberformat' => array('code' => PHPExcel_Style_NumberFormat::FORMAT_TEXT)); $price_format = array('numberformat' => array('code' => '######0.00'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)); $weight_format = array('numberformat' => array('code' => '##0.00'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)); // create the worksheets $worksheet_index = 0; switch ($export_type) { case 'c': // creating the Categories worksheet $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('Categories'); $this->populateCategoriesWorksheet($worksheet, $languages, $box_format, $text_format, $offset, $rows, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the CategoryFilters worksheet if ($this->existFilter()) { $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('CategoryFilters'); $this->populateCategoryFiltersWorksheet($worksheet, $languages, $default_language_id, $box_format, $text_format, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); } break; case 'p': // creating the Products worksheet $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('Products'); $this->populateProductsWorksheet($worksheet, $languages, $default_language_id, $price_format, $box_format, $weight_format, $text_format, $offset, $rows, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the AdditionalImages worksheet $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('AdditionalImages'); $this->populateAdditionalImagesWorksheet($worksheet, $box_format, $text_format, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the Specials worksheet $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('Specials'); $this->populateSpecialsWorksheet($worksheet, $default_language_id, $price_format, $box_format, $text_format, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the Discounts worksheet $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('Discounts'); $this->populateDiscountsWorksheet($worksheet, $default_language_id, $price_format, $box_format, $text_format, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the Rewards worksheet $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('Rewards'); $this->populateRewardsWorksheet($worksheet, $default_language_id, $box_format, $text_format, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the ProductOptions worksheet $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('ProductOptions'); $this->populateProductOptionsWorksheet($worksheet, $box_format, $text_format, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the ProductOptionValues worksheet $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('ProductOptionValues'); $this->populateProductOptionValuesWorksheet($worksheet, $price_format, $box_format, $weight_format, $text_format, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the ProductAttributes worksheet $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('ProductAttributes'); $this->populateProductAttributesWorksheet($worksheet, $languages, $default_language_id, $box_format, $text_format, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the ProductFilters worksheet if ($this->existFilter()) { $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('ProductFilters'); $this->populateProductFiltersWorksheet($worksheet, $languages, $default_language_id, $box_format, $text_format, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); } break; case 'o': // creating the Options worksheet $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('Options'); $this->populateOptionsWorksheet($worksheet, $languages, $box_format, $text_format); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the OptionValues worksheet $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('OptionValues'); $this->populateOptionValuesWorksheet($worksheet, $languages, $box_format, $text_format); $worksheet->freezePaneByColumnAndRow(1, 2); break; case 'a': // creating the AttributeGroups worksheet $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('AttributeGroups'); $this->populateAttributeGroupsWorksheet($worksheet, $languages, $box_format, $text_format); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the Attributes worksheet $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('Attributes'); $this->populateAttributesWorksheet($worksheet, $languages, $box_format, $text_format); $worksheet->freezePaneByColumnAndRow(1, 2); break; case 'f': if (!$this->existFilter()) { throw new Exception($this->language->get('error_filter_not_supported')); break; } // creating the FilterGroups worksheet $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('FilterGroups'); $this->populateFilterGroupsWorksheet($worksheet, $languages, $box_format, $text_format); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the Filters worksheet $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('Filters'); $this->populateFiltersWorksheet($worksheet, $languages, $box_format, $text_format); $worksheet->freezePaneByColumnAndRow(1, 2); break; default: break; } $workbook->setActiveSheetIndex(0); // redirect output to client browser $datetime = date('Y-m-d'); switch ($export_type) { case 'c': $filename = 'categories-' . $datetime; if (!$all) { if (isset($offset)) { $filename .= "-offset-{$offset}"; } else { if (isset($min_id)) { $filename .= "-start-{$min_id}"; } } if (isset($rows)) { $filename .= "-rows-{$rows}"; } else { if (isset($max_id)) { $filename .= "-end-{$max_id}"; } } } $filename .= '.xlsx'; break; case 'p': $filename = 'products-' . $datetime; if (!$all) { if (isset($offset)) { $filename .= "-offset-{$offset}"; } else { if (isset($min_id)) { $filename .= "-start-{$min_id}"; } } if (isset($rows)) { $filename .= "-rows-{$rows}"; } else { if (isset($max_id)) { $filename .= "-end-{$max_id}"; } } } $filename .= '.xlsx'; break; case 'o': $filename = 'options-' . $datetime . '.xlsx'; break; case 'a': $filename = 'attributes-' . $datetime . '.xlsx'; break; case 'f': if (!$this->existFilter()) { throw new Exception($this->language->get('error_filter_not_supported')); break; } $filename = 'filters-' . $datetime . '.xlsx'; break; default: $filename = $datetime . '.xlsx'; break; } header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="' . $filename . '"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($workbook, 'Excel2007'); $objWriter->setPreCalculateFormulas(false); $objWriter->save('php://output'); // Clear the spreadsheet caches $this->clearSpreadsheetCache(); exit; } catch (Exception $e) { $errstr = $e->getMessage(); $errline = $e->getLine(); $errfile = $e->getFile(); $errno = $e->getCode(); $this->session->data['export_import_error'] = array('errstr' => $errstr, 'errno' => $errno, 'errfile' => $errfile, 'errline' => $errline); if ($this->config->get('config_error_log')) { $this->log->write('PHP ' . get_class($e) . ': ' . $errstr . ' in ' . $errfile . ' on line ' . $errline); } return; } }
<?php $nama_bulan = array("", "Januari", "Februari", "Maret", "April", "Mei", "Juni", "Juli", "Agustus", "September", "Oktober", "November", "Desember"); $tahun = $laporan[0]['tahun']; $bulan = $nama_bulan[$laporan[0]['bulan']]; // PHPExcel object $xls = new PHPExcel(); // Set properties $xls->getProperties()->setCreator("SIM Puskesmas Bogor Tengah")->setLastModifiedBy("SIM Puskesmas Bogor Tengah")->setTitle("Rekap Kunjungan Pasien ASKES")->setSubject("Rekapitulasi Kunjungan Pasien ASKES"); $xls->setActiveSheetIndex(0); $xls->getDefaultStyle()->getFont()->setName('Arial'); $styleThinBlackBorderOutline = array('borders' => array('allborders' => array('style' => Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000')))); // header $xls->getActiveSheet()->setTitle("R.Askes " . $bulan . ' ' . $tahun); $xls->getActiveSheet()->mergeCells("A2:AD2")->setCellValueByColumnAndRow(0, 2, "REKAPITULASI KUNJUNGAN PASIEN ASKES"); $xls->getActiveSheet()->getStyle('A2')->getFont()->setSize(16)->setBold(true); $xls->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal('center'); $xls->getActiveSheet()->mergeCells("A3:AD3")->setCellValueByColumnAndRow(0, 3, "PUSKESMAS BOGOR TENGAH"); $xls->getActiveSheet()->getStyle('A3')->getFont()->setSize(14); $xls->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal('center'); $xls->getActiveSheet()->mergeCells("A4:AD4")->setCellValueByColumnAndRow(0, 4, "{$bulan} {$tahun}"); $xls->getActiveSheet()->getStyle('A4')->getFont()->setSize(14); $xls->getActiveSheet()->getStyle('A4')->getAlignment()->setHorizontal('center'); $xls->getActiveSheet()->getRowDimension('2')->setRowHeight(18.5); $xls->getActiveSheet()->getRowDimension('3')->setRowHeight(18); $xls->getActiveSheet()->getRowDimension('4')->setRowHeight(18); $xls->getActiveSheet()->getRowDimension('6')->setRowHeight(25.5); $xls->getActiveSheet()->getRowDimension('7')->setRowHeight(25.5); $xls->getActiveSheet()->getRowDimension('8')->setRowHeight(12.75); // table header $xls->getActiveSheet()->mergeCells('A6:A8')->mergeCells('B6:E6')->mergeCells('F6:I6')->mergeCells('J6:M6')->mergeCells('N6:N8')->mergeCells('O6:AD6')->mergeCells('B7:B8')->mergeCells('C7:C8')->mergeCells('D7:D8')->mergeCells('E7:E8')->mergeCells('F7:F8')->mergeCells('G7:G8')->mergeCells('H7:H8')->mergeCells('I7:I8')->mergeCells('J7:J8')->mergeCells('K7:K8')->mergeCells('L7:L8')->mergeCells('M7:M8')->mergeCells('O7:S7')->mergeCells('T7:X7')->mergeCells('Y7:AC7')->mergeCells('K7:K8')->mergeCells('L7:L8')->mergeCells('M7:M8')->mergeCells('AD7:AD8')->getStyle('A6:AD9')->getAlignment()->setHorizontal('center')->setVertical('center');
public function tahunan() { $bulan = $this->session->get('bulan_bulanan'); $this->session->remove('bulan_bulanan'); $tahun = $this->session->get('tahun_bulanan'); $this->session->remove('tahun_bulanan'); $data_tahunan = $this->session->get('data_tahunan'); $namanya = $this->nama_bulan[$bulan]; $nick_tahun = $tahun % 100; if ($bulan > 1) { $bulan2 = $bulan - 1; $tahun2 = $tahun + 1; } else { $bulan2 = 12; $tahun2 = $tahun; } $header_laporan = 'Rekapan Pemakaian Obat 1 Tahun ' . $this->nama_bulan[$bulan] . ' ' . $tahun . ' S/D ' . $this->nama_bulan[$bulan2] . ' ' . $tahun2; if ($data_tahunan) { require_once 'PHPExcel.php'; require_once 'PHPExcel/IOFactory.php'; $objPHPExcel = new PHPExcel(); // border $styleThinBlackBorderOutline = array('borders' => array('outline' => array('style' => PHPEXCEL_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000')))); $styleThinBlackBorderAll = array('borders' => array('allborders' => array('style' => PHPEXCEL_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000')))); // Align $styleAlignHorizontalCenter = array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)); $styleAlignVerticalCenter = array('alignment' => array('vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER)); // Set properties $objPHPExcel->getProperties()->setCreator("Siemas")->setLastModifiedBy("085697977177")->setTitle("Rekap Tahunan Obat")->setSubject("Rekap Tahunan Obat"); $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE)->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4)->setFitToWidth(1)->setFitToHeight(0); $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.75)->setRight(0.4)->setLeft(0.4)->setBottom(0.75); // Title Sheet $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20); $objPHPExcel->getActiveSheet()->getStyle('A2:P2')->getFont()->setSize(14)->setBold(true); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A2:P2')->setCellValueByColumnAndRow(0, 2, $header_laporan)->getStyle('A2:P2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // Set Cell excell $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial'); $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(3.22); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(11); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(10); // header $objPHPExcel->getActiveSheet()->getStyle('A5:AO273')->getFont()->setSize(9); $objPHPExcel->getActiveSheet()->mergeCells('A4:A5')->setCellValueByColumnAndRow(0, 4, "NO")->mergeCells('B4:B5')->setCellValueByColumnAndRow(1, 4, "Nama Obat")->mergeCells('C4:C5')->setCellValueByColumnAndRow(2, 4, "Sat")->mergeCells('D4:D5')->mergeCells('E4:E5')->mergeCells('F4:F5')->mergeCells('G4:G5')->mergeCells('H4:H5')->mergeCells('I4:I5')->mergeCells('J4:J5')->mergeCells('K4:K5')->mergeCells('L4:L5')->mergeCells('M4:M5')->mergeCells('N4:N5')->mergeCells('O4:O5')->mergeCells('P4:P5')->setCellValueByColumnAndRow(15, 4, "JUML")->getStyle('A4:P5')->applyFromArray($styleAlignHorizontalCenter)->applyFromArray($styleAlignVerticalCenter); $x = $bulan; $y = $nick_tahun; for ($n = 1; $n <= 12; $n++) { $bulan_tahun = $this->nick_bulan[$x] . " '" . $y; $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($n + 2, 4, $bulan_tahun); $x++; if ($x > 12) { $x = 1; $y++; } } $objPHPExcel->getActiveSheet()->getStyle('A4:P5')->applyFromArray($styleThinBlackBorderAll); // the real data $i = 6; foreach ($data_tahunan as $data) { $active1 = 'A' . $i . ':' . 'P' . $i; $active2 = 'A' . $i; $active3 = 'D' . $i . ':' . 'P' . $i; $formula1 = '=SUM(' . 'D' . $i . ':O' . $i . ')'; $objPHPExcel->getActiveSheet()->getStyle($active1)->applyFromArray($styleThinBlackBorderAll); $objPHPExcel->getActiveSheet()->getStyle($active2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle($active3)->applyFromArray($styleAlignHorizontalCenter); if (isset($data['id_obat'])) { $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $i, $data['id_obat']); } if (isset($data['nbk_obat'])) { $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $i, $data['nbk_obat']); } if (isset($data['satuan_obat'])) { $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $i, $data['satuan_obat']); } $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(15, $i, $formula1); $x = 2; $str = 1; for ($z = 1; $z <= 12; $z++) { $t = $x + $z; $obatn = 'obat' . $z; if (isset($data[$obatn])) { $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($t, $i, $data[$obatn]); } $str++; } $i++; } // $objPHPExcel->getActiveSheet()->getStyle('A6:F' . ($i-1))->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('A4:P5')->getFill()->setFillType(PHPEXCEL_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFDDDDDD'); // Rename sheet $objPHPExcel->getActiveSheet()->setTitle('Tahunan'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="rekap_resep_tahunan_' . $namanya . '-' . $tahun . '--' . $this->nama_bulan[$bulan2] . '-' . $tahun2 . '.xls"'); $objWriter = PHPEXCEL_IOFactory::createWriter($objPHPExcel, "Excel5"); $objWriter->save("php://output"); } $this->redirect('index.php/laporan/tahunan/'); }
* @version 1.7.6, 2011-02-27 */ /** Error reporting */ error_reporting(E_ALL); date_default_timezone_set('Europe/London'); /** PHPExcel */ require_once '../Classes/PHPExcel.php'; // Create new PHPExcel object echo date('H:i:s') . " Create new PHPExcel object\n"; $objPHPExcel = new PHPExcel(); // Set properties echo date('H:i:s') . " Set properties\n"; $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->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"); // Set default font echo date('H:i:s') . " Set default font\n"; $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial'); $objPHPExcel->getDefaultStyle()->getFont()->setSize(10); // Add some data, resembling some different data types echo date('H:i:s') . " Add some data\n"; $objPHPExcel->getActiveSheet()->setCellValue('A1', 'String'); $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Simple'); $objPHPExcel->getActiveSheet()->setCellValue('C1', 'PHPExcel'); $objPHPExcel->getActiveSheet()->setCellValue('A2', 'String'); $objPHPExcel->getActiveSheet()->setCellValue('B2', 'Symbols'); $objPHPExcel->getActiveSheet()->setCellValue('C2', '!+&=()~§±æþ'); $objPHPExcel->getActiveSheet()->setCellValue('A3', 'String'); $objPHPExcel->getActiveSheet()->setCellValue('B3', 'UTF-8'); $objPHPExcel->getActiveSheet()->setCellValue('C3', 'Создать MS Excel Книги из PHP скриптов'); $objPHPExcel->getActiveSheet()->setCellValue('A4', 'Number'); $objPHPExcel->getActiveSheet()->setCellValue('B4', 'Integer'); $objPHPExcel->getActiveSheet()->setCellValue('C4', 12);
/** * Author: Phucnh * Date created: Jan 23, 2015 * Company: DNICT */ $border_th = array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN))); $font = array('font' => array('bold' => true, 'name' => 'times')); $mauxanh = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '92D050'))); $mauvang = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FFFF00'))); $indam = array('font' => array('bold' => true)); $canhgiua = array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER)); $canhtrai = array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER)); $gachdit = array('font' => array('underline' => PHPExcel_Style_Font::UNDERLINE_SINGLE)); $innghieng = array('font' => array('italic' => true)); $objPHPExcel = new PHPExcel(); $objPHPExcel->getDefaultStyle()->getFont()->setName('Times New Roman')->setSize(13); $row = $this->rows; $sheet = 1; foreach ($row as $key => $val) { $tennv = $val[0]->tennhanvien; $objWorkSheet = $objPHPExcel->createSheet($key); $objPHPExcel->setActiveSheetIndex($sheet); $activeSheet = $objPHPExcel->getActiveSheet(); /* get sheet active */ $activeSheet->getStyle('B1:G50')->getAlignment()->setWrapText(true); $activeSheet->getStyle('A10:G11')->applyFromArray($border_th); $activeSheet->setCellValue('A1', 'TRUNG TÂM CÔNG NGHỆ THÔNG TIN VÀ TRUYỂN THÔNG')->setCellValue('C1', 'CỘNG HÒA XÃ HỘI CHỦ NGHĨA VIỆT NAM')->setCellValue('C2', 'Độc lập - Tự do - Hạnh phúc')->setCellValue('A2', 'PHÒNG PHÁT TRIỂN PHẦN MỀM'); $activeSheet->mergeCells('A1:B1'); $activeSheet->getStyle('A1:B1')->applyFromArray($canhgiua)->applyFromArray($indam); $activeSheet->getStyle('A2:B2')->applyFromArray($gachdit); $activeSheet->getStyle('C2:G2')->applyFromArray($gachdit);
/** * Build CSS styles * * @param boolean $generateSurroundingHTML Generate surrounding HTML style? (html { }) * @return array * @throws PHPExcel_Writer_Exception */ public function buildCSS($generateSurroundingHTML = true) { // PHPExcel object known? if (is_null($this->_phpExcel)) { throw new PHPExcel_Writer_Exception('Internal PHPExcel object not set to an instance of an object.'); } // Cached? if (!is_null($this->_cssStyles)) { return $this->_cssStyles; } // Ensure that spans have been calculated if (!$this->_spansAreCalculated) { $this->_calculateSpans(); } // Construct CSS $css = array(); // Start styles if ($generateSurroundingHTML) { // html { } $css['html']['font-family'] = 'Calibri, Arial, Helvetica, sans-serif'; $css['html']['font-size'] = '11pt'; $css['html']['background-color'] = 'white'; } // table { } $css['table']['border-collapse'] = 'collapse'; if (!$this->_isPdf) { $css['table']['page-break-after'] = 'always'; } // .gridlines td { } $css['.gridlines td']['border'] = '1px dotted black'; // .b {} $css['.b']['text-align'] = 'center'; // BOOL // .e {} $css['.e']['text-align'] = 'center'; // ERROR // .f {} $css['.f']['text-align'] = 'right'; // FORMULA // .inlineStr {} $css['.inlineStr']['text-align'] = 'left'; // INLINE // .n {} $css['.n']['text-align'] = 'right'; // NUMERIC // .s {} $css['.s']['text-align'] = 'left'; // STRING // Calculate cell style hashes foreach ($this->_phpExcel->getCellXfCollection() as $index => $style) { $css['td.style' . $index] = $this->_createCSSStyle($style); } // Fetch sheets $sheets = array(); if (is_null($this->_sheetIndex)) { $sheets = $this->_phpExcel->getAllSheets(); } else { $sheets[] = $this->_phpExcel->getSheet($this->_sheetIndex); } // Build styles per sheet foreach ($sheets as $sheet) { // Calculate hash code $sheetIndex = $sheet->getParent()->getIndex($sheet); // Build styles // Calculate column widths $sheet->calculateColumnWidths(); // col elements, initialize $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn()) - 1; $column = -1; while ($column++ < $highestColumnIndex) { $this->_columnWidths[$sheetIndex][$column] = 42; // approximation $css['table.sheet' . $sheetIndex . ' col.col' . $column]['width'] = '42pt'; } // col elements, loop through columnDimensions and set width foreach ($sheet->getColumnDimensions() as $columnDimension) { if (($width = PHPExcel_Shared_Drawing::cellDimensionToPixels($columnDimension->getWidth(), $this->_defaultFont)) >= 0) { $width = PHPExcel_Shared_Drawing::pixelsToPoints($width); $column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1; $this->_columnWidths[$sheetIndex][$column] = $width; $css['table.sheet' . $sheetIndex . ' col.col' . $column]['width'] = $width . 'pt'; if ($columnDimension->getVisible() === false) { $css['table.sheet' . $sheetIndex . ' col.col' . $column]['visibility'] = 'collapse'; $css['table.sheet' . $sheetIndex . ' col.col' . $column]['*display'] = 'none'; // target IE6+7 } } } // Default row height $rowDimension = $sheet->getDefaultRowDimension(); // table.sheetN tr { } $css['table.sheet' . $sheetIndex . ' tr'] = array(); if ($rowDimension->getRowHeight() == -1) { $pt_height = PHPExcel_Shared_Font::getDefaultRowHeightByFont($this->_phpExcel->getDefaultStyle()->getFont()); } else { $pt_height = $rowDimension->getRowHeight(); } $css['table.sheet' . $sheetIndex . ' tr']['height'] = $pt_height . 'pt'; if ($rowDimension->getVisible() === false) { $css['table.sheet' . $sheetIndex . ' tr']['display'] = 'none'; $css['table.sheet' . $sheetIndex . ' tr']['visibility'] = 'hidden'; } // Calculate row heights foreach ($sheet->getRowDimensions() as $rowDimension) { $row = $rowDimension->getRowIndex() - 1; // table.sheetN tr.rowYYYYYY { } $css['table.sheet' . $sheetIndex . ' tr.row' . $row] = array(); if ($rowDimension->getRowHeight() == -1) { $pt_height = PHPExcel_Shared_Font::getDefaultRowHeightByFont($this->_phpExcel->getDefaultStyle()->getFont()); } else { $pt_height = $rowDimension->getRowHeight(); } $css['table.sheet' . $sheetIndex . ' tr.row' . $row]['height'] = $pt_height . 'pt'; if ($rowDimension->getVisible() === false) { $css['table.sheet' . $sheetIndex . ' tr.row' . $row]['display'] = 'none'; $css['table.sheet' . $sheetIndex . ' tr.row' . $row]['visibility'] = 'hidden'; } } } // Cache if (is_null($this->_cssStyles)) { $this->_cssStyles = $css; } // Return return $css; }
function reporte($fec1, $fec2, $impoExpo) { global $connI; $reportName = "BLS SUMMARY FROM MSCLINK"; // Incluir la libreria PHPExcel require '../include/PHPExcel/PHPExcel.php'; // Reservar memoria en servidor PHP // Si el archivo final tiene 5Mb, reservar 500Mb // Por cada operación, phpExcel mapea en memoria la imagen del archivo y esto satura la mamoria ini_set("memory_limit", "512M"); // Estilos Arreglo $styleEnc = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), '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'))); $styleSombra = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => '80E9E07A'), 'endcolor' => array('argb' => 'FFFFFFFF'))); $styleTitulo = array('font' => array('bold' => true, 'size' => 14), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)); $styleSubtitulo = array('font' => array('bold' => true, 'size' => 10), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)); // ---------------------------- // Hoja 1 // Nota : No usar acentos!!! $objPHPExcel = new PHPExcel(); $objPHPExcel->getActiveSheet()->setTitle('Hoja1'); $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial'); $objPHPExcel->getDefaultStyle()->getFont()->setSize(8); // HOJA 2 $workSheet2 = new PHPExcel_Worksheet($objPHPExcel, 'Hoja2'); $objPHPExcel->addSheet($workSheet2, 1); $workSheet2->getDefaultStyle()->getFont()->setSize(8); $workSheet2->getDefaultStyle()->getFont()->setName('Arial'); // ----------------------------------------------- // ENCABEZADOS // ----------------------------------------------- // Se crea el arreglo de hojas. Esto es como llevan el mismo encabezado en todas las hojas, solo es recorrer el index = hojas. $objPHPExcel->setActiveSheetIndex(0); // Se aplica estilo al encabezado $objPHPExcel->getActiveSheet()->getStyle('A7:Z7')->applyFromArray($styleEnc); // Encabezados $headings = array('Booking_Number', 'Bill_Of_Lading_Number', 'POL', 'POD', 'Service', 'Trade', 'Agent_Location', 'Vessel_Name', 'Voyage', 'Departure_dateTime', 'DTX_File_Name', 'Collection_Office_Code', 'Inttra_Booking', 'Shipper', 'FFW', 'Sales_Exe', 'Doc_Exe', 'SI_User', 'Created_by_User', 'container_count', 'Origin', 'FDest', 'Express_release', 'Telex_Release', 'EDI_Wizard', 'Created By'); // Escribe los encabezados $rowNumber = 7; // Freeze pane so that the heading line won't scroll $objPHPExcel->getActiveSheet()->freezePane('A8'); $col = 'A'; foreach ($headings as $heading) { $objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading); $col++; } // AutoFiltro $objPHPExcel->getActiveSheet()->setAutoFilter('A7:X7'); // Auto Ajuste de Ancho en Columna $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('Y')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('Z')->setWidth(15); // Titulo $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Mediterranean Shipping Company Mexico, S.A. DE C.V.')->getStyle('A1')->applyFromArray($styleTitulo); // SUBTitulo $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Solo como Agentes / As Agent only'); $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo); $objPHPExcel->getActiveSheet()->setCellValue('A3', '$reportName'); $objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo); $objPHPExcel->getActiveSheet()->setCellValue('A6', "{$buque} {$viaje}"); $objPHPExcel->getActiveSheet()->getStyle('A6')->applyFromArray($styleSubtitulo); // ----------------------------------- // HOJA 1 // ----------------------------------- $objPHPExcel->setActiveSheetIndex(0); // Simple query $sql = "\n\tSELECT\n\tb.Booking_Number as Booking_Number,\n\tb.Bill_Of_Lading_Number as Bill_Of_Lading_Number,\n\tb.POL as POL,\n\tb.POD as POD,\n\tb.Service as Service,\n\tB.Trade as Trade,\n\ta.LOC_Full AS Agent_Location,\n\tb.Vessel_Name as Vessel_Name,\n\tb.Voyage as Voyage,\n\tb.Departure_dateTime as Departure_dateTime,\n\tb.DTX_File_Name as DTX_File_Name,\n\tb.Collection_Office_Code as Collection_Office_Code,\n\tc.Inttra_Booking as Inttra_Booking,\n\ta1.Company AS Shipper,\n\ta2.Company AS FFW,\n\tc.Local_STR_1 AS Sales_Exe,\n\tc.Local_STR_2 AS Doc_Exe,\n\tu.User_Name AS SI_User,\n\ts.User_Name AS Created_by_User,\n\tb.[# CSC] AS container_count,\n\tb.Origin as Origin,\n\tb.FDest as FDest,\n\tb.Express_release as Express_release,\n\tB.Telex_Release as Telex_Release,\n\tU.First_Name +' '+U.Last_Name as EDI_Wizard,\n\tu2.First_Name +' '+u2.Last_Name as Created_by\n\tFROM\n\t\tInterlink_DFeed90.dfeed.FNTB_Booking_BL (0, 300) B\n\t\tLEFT JOIN Interlink_DFeed90.dfeed.FNTB_AssocDocCompany ('Booking Agent') a ON a.Cargo_Shipment_id = b.Cargo_Shipment_id\n\t\tLEFT JOIN Interlink_DFeed90.dfeed.FNTB_AssocDocCompany ('Shipper') a1 ON a1.Bill_Of_Lading_id = b.Bill_Of_Lading_id\n\t\tLEFT JOIN Interlink_DFeed90.dfeed.FNTB_AssocDocCompany ('Freight Fwd (BL) Shipper') a2 ON a2.Bill_Of_Lading_id = b.Bill_Of_Lading_id\n\t\tINNER JOIN Interlink_Main90.dbo.Cargo_Shipment (nolock) c ON c.Cargo_Shipment_id = b.Cargo_Shipment_id\n\t\tLEFT JOIN Interlink_Main90.dbo.Cargo_Shipment_Comment o (nolock) ON b.Cargo_Shipment_id = o.Cargo_Shipment_id AND o.Comment LIKE 'bill of lading created from si%'\n\t\tLEFT JOIN interlink_main90.dbo.gissec_user (nolock) U ON o.User_id = U.SecUser_id\n\t\tLEFT JOIN Interlink_Main90.dbo.GISSec_User (nolock) s ON s.SecUser_id = b.Clerk_User_id\n\t\tLEFT JOIN interlink_main90.dbo.bill_of_lading (nolock) BL ON B.Bill_Of_Lading_id = BL.Bill_Of_Lading_Id\n\t\tLEFT JOIN interlink_main90.dbo.gissec_user (nolock) u2 ON BL.Created_By_User_id = u2.SecUser_id\n\tWHERE\n\t\tb.I_E = '{$impoExpo}'\n\tAND b.Departure_dateTime BETWEEN '{$fec1}' AND '{$fec2}'\n\t-- AND b.Bill_Of_Lading_Number in ('MSCUAU931745','MSCUAU925986')\n\tORDER BY\n\tb.Departure_dateTime,\n\tb.Booking_Number\n\n "; $rs = odbc_exec($connI, $sql); if (!$rs) { exit("Error en la consulta SQL"); } $lin = 1; $rowNumber = 8; // donde inicia los datos while (odbc_fetch_row($rs)) { $Booking_Number = odbc_result($rs, 'Booking_Number'); $Bill_Of_Lading_Number = odbc_result($rs, 'Bill_Of_Lading_Number'); $POL = odbc_result($rs, 'POL'); $POD = odbc_result($rs, 'POD'); $Service = odbc_result($rs, 'Service'); $Trade = odbc_result($rs, 'Trade'); $Agent_Location = odbc_result($rs, 'Agent_Location'); $Vessel_Name = odbc_result($rs, 'Vessel_Name'); $Voyage = odbc_result($rs, 'Voyage'); $Departure_dateTime = odbc_result($rs, 'Departure_dateTime'); $DTX_File_Name = odbc_result($rs, 'DTX_File_Name'); $Collection_Office_Code = odbc_result($rs, 'Collection_Office_Code'); $Inttra_Booking = odbc_result($rs, 'Inttra_Booking'); $Shipper = odbc_result($rs, 'Shipper'); $FFW = odbc_result($rs, 'FFW'); $Sales_Exe = odbc_result($rs, 'Sales_Exe'); $Doc_Exe = odbc_result($rs, 'Doc_Exe'); $SI_User = odbc_result($rs, 'SI_User'); $Created_by_User = odbc_result($rs, 'Created_by_User'); $container_count = odbc_result($rs, 'container_count'); $Origin = odbc_result($rs, 'Origin'); $FDest = odbc_result($rs, 'FDest'); $Express_release = odbc_result($rs, 'Express_release'); $Telex_Release = odbc_result($rs, 'Telex_Release'); $EDI_Wizard = odbc_result($rs, 'EDI_Wizard'); $Created_by = odbc_result($rs, 'Created_by'); // Por manejo de memoria se deben de meter los datos con las menos operaciones posibles. $objPHPExcel->getActiveSheet()->setCellValue('A' . $rowNumber, $Booking_Number)->setCellValue('B' . $rowNumber, $Bill_Of_Lading_Number)->setCellValue('C' . $rowNumber, $POL)->setCellValue('D' . $rowNumber, $POD)->setCellValue('E' . $rowNumber, $Service)->setCellValue('F' . $rowNumber, $Trade)->setCellValue('G' . $rowNumber, $Agent_Location)->setCellValue('H' . $rowNumber, $Vessel_Name)->setCellValue('I' . $rowNumber, $Voyage)->setCellValue('J' . $rowNumber, $Departure_dateTime)->setCellValue('K' . $rowNumber, $DTX_File_Name)->setCellValue('L' . $rowNumber, $Collection_Office_Code)->setCellValue('M' . $rowNumber, $Inttra_Booking)->setCellValue('N' . $rowNumber, $Shipper)->setCellValue('O' . $rowNumber, $FFW)->setCellValue('P' . $rowNumber, $Sales_Exe)->setCellValue('Q' . $rowNumber, $Doc_Exe)->setCellValue('R' . $rowNumber, $SI_User)->setCellValue('S' . $rowNumber, $Created_by_User)->setCellValue('T' . $rowNumber, $container_count)->setCellValue('U' . $rowNumber, $Origin)->setCellValue('V' . $rowNumber, $FDest)->setCellValue('W' . $rowNumber, $Express_release)->setCellValue('X' . $rowNumber, $Telex_Release)->setCellValue('Y' . $rowNumber, $EDI_Wizard)->setCellValue('Z' . $rowNumber, $Created_by); $rowNumber++; $lin++; } /* // ------------------------------ // Hoja 2 - TRANSBORDOS // ------------------------------ $objPHPExcel->setActiveSheetIndex(1); // Se aplica estilo al encabezado $objPHPExcel->getActiveSheet()->getStyle('A7:T7')->applyFromArray($styleEnc); // Encabezados $headings = Array('No', 'Original_pol'); // Escribe los encabezados $rowNumber = 7; // Freeze pane so that the heading line won't scroll $objPHPExcel->getActiveSheet()->freezePane('A8'); $col = 'A'; foreach ($headings as $heading) { $objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading); $col++; } // AutoFiltro $objPHPExcel->getActiveSheet()->setAutoFilter('A7:T7'); // Auto Ajuste de Ancho en Columna $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(15); // Titulo $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Mediterranean Shipping Company Mexico, S.A. DE C.V.') ->getStyle('A1')->applyFromArray($styleTitulo); // SUBTitulo $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Solo como Agentes / As agents Only'); $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo); $objPHPExcel->getActiveSheet()->setCellValue('A3', '$reportName'); $objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo); $objPHPExcel->getActiveSheet()->setCellValue('A6', "$buque $viaje"); $objPHPExcel->getActiveSheet()->getStyle('A6')->applyFromArray($styleSubtitulo); $objPHPExcel->setActiveSheetIndex(1); // Simple query $sql = " SELECT e.Equipment_Number as CONTE, e.Bill_Of_Lading_Number as BL, e.Origin_Booking_Number, e.EQ_Type as EQ_Type, e.Load_Terminal, e.VPCLoad_Vessel_Name, e.VPCLoad_Voyage, e.Origin AS Inland_Origin, e.FDest AS Inland_Final_Dest, e.Orig_POLUN AS Original_POL, e.VPCLoad_UNCode AS Current_POL, e.Prev_POLUN AS Previous_Port, e.Next_PODUN AS Next_POD, e.PODUN AS Final_POD, e.TSPorts AS All_TS_Ports, e.NVOCC_AMS_CERTIFIED, e.High_Priority_Flag, e.Hold_Indicator, d.UN_Number as UNNumber, d.IMO_Class as ImoClass, e.Shipper_Owned, e.Master_Tare_Weight_Kg as TaraKg, e.Cntr_Weight, e.Cargo_Weight_Kgs as CargoKg, e.Master_Payload_Kg, e.Cargo_Volume_CMT, e.Ex_Cargo_Short_Description as Producto, e.Cargo_Packages as Paquetes, e.Ex_Cargo_Package_Type as Embalaje, e.Ex_Harmonized_Code, e.OOG_Height, e.OOG_Length_Back, e.OOG_Length_Front, e.OOG_Width_Left, e.OOG_Width_Right, e.Tank_Residue, e.Max_Reefer_Temp, e.Min_Reefer_Temp, e.Temp_Unit, e.Ventilated_Container_Flag, e.Air_Fresh_Vents_Setting, e.Cold_Treatment, e.Control_Atmosphere, e.Opening_Percentage, e.Bulb_Mode, e.IsEmpty as IsEmpty, e.Fumigation, e.De_Fumigation, e.Fumigation_Residue_IMO_Class, e.Fumigation_Residue_UN_Number, e.Custom_Status_Desc, a.company AS shipper_name, a.city AS shipper_city, a.Country AS shipper_country, a.MSCCode AS shipper_msccode FROM interlink_dfeed90.dfeed.TSHIP_FNTB_Equipment ('2015-05-01', 'A') e LEFT JOIN Interlink_DFeed90.dfeed.TSHIP_FNTB_Dangerous_Cargo () d ON e.Equipment_ID = d.Equipment_ID LEFT JOIN Interlink_DFeed90.dfeed.TSHIP_FNTB_AssocDocCompany ('shipper') a ON e.Bill_Of_Lading_Number = a.Bill_Of_Lading_Number WHERE E.VPCDisch_Vessel_Name = '$buque' AND E.VPCDisch_Voyage = '$viaje' AND E.VPCDisch_UNCode = '$codPuerto' "; $rs = odbc_exec($connI, $sql); if (!$rs) { exit("Error en la consulta SQL"); } $lin=1; $rowNumber = 8; // donde inicia los datos while (odbc_fetch_row($rs)) { $polOri = odbc_result($rs, "Original_POL"); $polCur = odbc_result($rs, "Current_POL"); // Por manejo de memoria se deben de meter los datos con las menos operaciones posibles. $objPHPExcel->getActiveSheet()->setCellValue( 'A'.$rowNumber,$lin) ->setCellValue('B'.$rowNumber,$polOri) ->setCellValue('C'.$rowNumber,$polPrev) ->setCellValue('D'.$rowNumber,$x) ->setCellValue('E'.$rowNumber,$x) ->setCellValue('F'.$rowNumber,$x) ->setCellValue('G'.$rowNumber,$x) ->setCellValue('H'.$rowNumber,$x) ->setCellValue('I'.$rowNumber,$x) ->setCellValue('J'.$rowNumber,$x) ->setCellValue('K'.$rowNumber,$x) ->setCellValue('L'.$rowNumber,$x) ->setCellValue('M'.$rowNumber,$x) ->setCellValue('N'.$rowNumber,$x) ->setCellValue('O'.$rowNumber,$x) ->setCellValue('P'.$rowNumber,$x) ->setCellValue('Q'.$rowNumber,$x) ->setCellValue('R'.$rowNumber,$x) ->setCellValue('S'.$rowNumber,$x) ->setCellValue('T'.$rowNumber,$x) ; $rowNumber++; $lin++; } // Loregreso a la hoja 1. $objPHPExcel->setActiveSheetIndex(0); */ // Se modifican los encabezados del HTTP para indicar que se envia un archivo de Excel2007. header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition:attachment;filename="blSummary.xlsx"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); exit; }