Example #1
1
 /**
  * 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 = '.';
 }
Example #2
1
	/**
	 * 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;
	}
Example #3
0
 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;
 }
Example #4
0
 /**
  *
  */
 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');
 }
Example #5
0
 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'));
 }
Example #6
0
 /**
  * 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;
}
Example #8
0
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;
}
Example #9
0
 /**
  * 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();
 }
Example #10
0
$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);
Example #11
0
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;
}
Example #12
0
<?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') {
Example #13
0
 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");
 }
Example #15
0
 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("&nbsp;", " ", $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;
 }
Example #16
0
 /**
  * Get default style of workbork.
  *
  * @deprecated
  * @return 	PHPExcel_Style
  * @throws 	Exception
  */
 public function getDefaultStyle()
 {
     return $this->_parent->getDefaultStyle();
 }
Example #17
0
 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");
 }
Example #18
0
 /**
  * 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);
 }
Example #20
0
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>";
}
Example #21
0
<?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;
 }
Example #23
0
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;
}
Example #24
0
 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;
     }
 }
Example #25
0
<?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');
Example #26
0
 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/');
 }
Example #27
0
 * @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);
Example #29
0
 /**
  * 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;
 }
Example #30
0
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;
}