/** * @return \PHPExcel */ protected function mockWorkbook() { $workbook = new \PHPExcel(); $workbook->disconnectWorksheets(); $workbook->getProperties()->setTitle('mocked'); $sheet = new \PHPExcel_Worksheet($workbook); $sheet->fromArray([['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test']]); $workbook->addSheet($sheet); $sheet = new \PHPExcel_Worksheet($workbook); $sheet->fromArray([['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test']]); $workbook->addSheet($sheet); $workbook->setActiveSheetIndex(0); return $workbook; }
function catalog_to_xlsx() { $objPHPExcel = new PHPExcel(); // Set document properties $objPHPExcel->getProperties()->setCreator("Boy Gruv")->setLastModifiedBy("Boy Gruv")->setTitle("PHPExcel Document")->setSubject("PHPExcel Document")->setDescription("Document for PHPExcel, generated using PHP classes.")->setKeywords("office PHPExcel php")->setCategory("Result file"); // Заголовок страницы Categories $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'category_id')->setCellValue('B1', 'parent_id')->setCellValue('C1', 'name(en)')->setCellValue('D1', 'name(ru)')->setCellValue('E1', 'top')->setCellValue('F1', 'columns')->setCellValue('G1', 'sort_order')->setCellValue('H1', 'image_name')->setCellValue('I1', 'date_added')->setCellValue('J1', 'date_modified')->setCellValue('K1', 'seo_keyword')->setCellValue('L1', 'description(en)')->setCellValue('M1', 'description(ru)')->setCellValue('N1', 'meta_title(en)')->setCellValue('O1', 'meta_title(ru)')->setCellValue('P1', 'meta_description(en)')->setCellValue('Q1', 'meta_description(ru)')->setCellValue('R1', 'meta_keywords(en)')->setCellValue('S1', 'meta_keywords(ru)')->setCellValue('T1', 'store_ids')->setCellValue('U1', 'layout')->setCellValue('V1', 'status'); // Заполняем данные $i = 2; $arr = SQL_get_catalog(); for ($j = 0; $j < count($arr); $j++) { $i = $j + 2; $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $i, $arr[$j]["id_cat"] + 100)->setCellValue('B' . $i, $arr[$j]["parent_id"] + 100)->setCellValue('D' . $i, $arr[$j]["name"])->setCellValue('E' . $i, 'true')->setCellValue('F' . $i, '1')->setCellValue('G' . $i, '0')->setCellValue('I' . $i, date('Y-m-d H:i:s'))->setCellValue('J' . $i, date('Y-m-d H:i:s'))->setCellValue('K' . $i, '')->setCellValue('M' . $i, '')->setCellValue('T' . $i, '0')->setCellValue('V' . $i, 'true'); } // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle('Categories'); //Добавляем новую страницу $MyWorkSheet = new PHPExcel_Worksheet($objPHPExcel, 'CategoryFilters'); $objPHPExcel->addSheet($MyWorkSheet, 1); // Заголовок страницы CategoryFilters $objPHPExcel->setActiveSheetIndex(1)->setCellValue('A1', 'category_id')->setCellValue('B1', 'filter_group')->setCellValue('C1', 'filter'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Save Excel 2007 file $callStartTime = microtime(true); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save(str_replace('.php', '.xlsx', __FILE__)); $callEndTime = microtime(true); $callTime = $callEndTime - $callStartTime; //echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL; $ret_str = date('H:i:s') . " File written to " . str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)); return $ret_str; }
protected function _write(array $data){ //Umwandeln in numerischen Array foreach($data AS $k => $dataEntry){ if( $this->_charset != 'UTF-8' ){ //Umwandeln des Zeichensatzes foreach($dataEntry AS $dataEntryK => $dataEntryV){ $dataEntry[$dataEntryK] = iconv('UTF-8', $this->_charset.'//TRANSLIT', $dataEntryV); } } $data[$k] = array_values($dataEntry); } if( $this->_useFirstRowAsTitles() ){ $data = array_merge(array($this->_titles), $data); } $this->_phpExcel = new PHPExcel(); $this->_phpExcel->addSheet(); $this->_phpExcel->getActiveSheet()->fromArray($data); $this->_getWriter()->save($this->_file); }
/** * @param string $text * * @return Cell */ protected function mockCell($text = 'text', $settings = false) { $workbook = new \PHPExcel(); $workbook->disconnectWorksheets(); $worksheet = new \PHPExcel_Worksheet($workbook); $workbook->addSheet($worksheet); $cell = $worksheet->setCellValue('A1', $pValue = $text, $returnCell = true); $settings = $settings ?: new ParserSettings(); $cell = new Cell($cell, 1, $settings); return $cell; }
/** * (non-PHPdoc) * @see \scipper\Datatransfer\TransferService::generateEmptyDocument() */ public function generateDocument(Map $map) { if (!class_exists("PHPExcel")) { throw new GenerationException("dependency 'PHPExcel' not found"); } $excel = new \PHPExcel(); $excel->removeSheetByIndex(0); $excel->getProperties()->setCreator($map->getCreator()); $excel->getProperties()->setTitle($map->getTitle()); $protectedStyle = new \PHPExcel_Style(); $protectedStyle->applyFromArray(array("fill" => array("type" => \PHPExcel_Style_Fill::FILL_SOLID, "color" => array("argb" => "55CCCCCC")), "borders" => array("bottom" => array("style" => \PHPExcel_Style_Border::BORDER_THIN), "right" => array("style" => \PHPExcel_Style_Border::BORDER_MEDIUM)))); $i = 0; foreach ($map->getSheets() as $sheet) { $active = $excel->addSheet(new \PHPExcel_Worksheet(NULL, $sheet->getTitle()), $i); $active->getProtection()->setSheet(true); $active->getStyle("A1:Z30")->getProtection()->setLocked(\PHPExcel_Style_Protection::PROTECTION_UNPROTECTED); foreach ($sheet->getCells() as $cell) { //Convert content to list format ist necessary if ($cell->getType() == "select") { $dataValidation = $active->getCell($cell->getCoord())->getDataValidation(); $dataValidation->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST); $dataValidation->setAllowBlank(false); $dataValidation->setShowInputMessage(true); $dataValidation->setShowDropDown(true); $dataValidation->setFormula1($cell->getContent()); } else { $active->setCellValue($cell->getCoord(), $cell->getValue()); } //Add protection is necessary if ($cell->isProtected()) { $active->protectCells($cell->getCoord(), "123"); $active->setSharedStyle($protectedStyle, $cell->getCoord()); // } elseif(!$cell->isProtected() && $active->getProtection()->isProtectionEnabled()) { // $active->unprotectCells($cell->getCoord()); } $active->getColumnDimension($cell->getX())->setAutoSize(true); if (!$cell->isVisible()) { $active->getColumnDimension($cell->getX())->setVisible(false); } } $i++; } $excel->setActiveSheetIndex(0); $writer = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007'); $filename = $this->documentRoot . $excel->getProperties()->getTitle() . ".xlsx"; $writer->save($filename); return $filename; }
/** * * @param PHPExcel_Worksheet $hoja * @param int $pk representa el índice de la columna en el archivo de excel que está asociada con la llave primaria de la tabla * @return string rerpesenta el nombre la ruta del archivo creado. Si no se pudo crear el archivo se regresa otra cosa :p */ function prepararArchivo($hoja, $pk = false, $incluirPrimeraFila = false) { $objetoExcel = new PHPExcel(); $hojaInsertar = $objetoExcel->getSheet(0); $hojaInsertar->setTitle('Insertar'); if ($objetoExcel->getSheetCount() > 1) { $hojaActualizar = $objetoExcel->getSheet(1); $hojaActualizar->setTitle('Actualizar'); } else { $hojaActualizar = new PHPExcel_Worksheet(); $hojaActualizar->setTitle('Actualizar'); $objetoExcel->addSheet($hojaActualizar); } $rango = $hoja->calculateWorksheetDataDimension(); if (!$incluirPrimeraFila) { $rango[1] = '2'; } $contenidoExcel = $hoja->rangeToArray($rango); $datos = array(); $datos['insertar'] = array(); $datos['actualizar'] = array(); if ($pk) { $db = new DbConnection(); $db->abrirConexion(); $llavePrimaria = $_SESSION['pk']; foreach ($contenidoExcel as $fila) { $existe = $db->existeRegistro($_SESSION['tabla'], $llavePrimaria, $fila[$pk]); if ($existe) { $datos['actualizar'][] = $fila; } else { $datos['insertar'][] = $fila; } } $db->cerrarConexion(); } else { foreach ($contenidoExcel as $fila) { $datos['insertar'][] = $fila; } } $hojaInsertar->fromArray($datos['insertar'], null, 'A1', true); $hojaActualizar->fromArray($datos['actualizar'], null, 'A1', true); $escritorExcel = PHPExcel_IOFactory::createWriter($objetoExcel, 'Excel2007'); $escritorExcel->save('excelTmp/tmp_import_upload.xlsx'); return 'excelTmp/tmp_import_upload.xlsx'; }
private function addAnalysis(\PHPExcel $ea) { $ews2 = new \PHPExcel_Worksheet($ea, 'Summary'); $ea->addSheet($ews2, 0); $ews2->setTitle('Summary'); $ews2->setCellValue('a1', 'Lakers 2013-2014 Season'); $style = array('font' => array('bold' => true, 'size' => 20), 'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT)); $ews2->mergeCells('a1:b1'); $ews2->getStyle('a1')->applyFromArray($style); $ews2->getColumnDimension('a')->setAutoSize(true); $ews2->setCellValue('a2', 'Win:'); $ews2->setCellValue('a3', 'Loss:'); $ews2->setCellValue('a4', '%:'); $ews2->setCellValue('b2', '=COUNTIF(Data!G2:G91, "W")-COUNTIF(Data!G2:G9, "W")'); $ews2->setCellValue('b3', '=COUNTIF(Data!G2:G91, "L")-COUNTIF(Data!G2:G9, "L")'); $ews2->setCellValue('b4', '=b2/(b2+b3)'); //$ews2->getStyle('b4')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00); return $ews2; }
function excel_writer($row_data_excel) { $objPHPEXCEL = new PHPExcel(); $row_len = count($row_data_excel); $row_count = 0; $myWorkSheet = new PHPExcel_WorkSheet($objPHPEXCEL, "班級"); $objPHPEXCEL->addSheet($myWorkSheet, 0); $objPHPEXCEL->getSheet(0)->setCellValueByColumnAndRow(0, 1, "班級"); $objPHPEXCEL->getSheet(0)->getColumnDimension('A')->setWidth(12); $column = 2; while ($row_count < $row_len) { $objPHPEXCEL->getSheet(0)->setCellValueByColumnAndRow(0, $column, $row_data_excel[$row_count]["class"]); $row_count++; $column++; } $objWriter = PHPExcel_IOFactory::createWriter($objPHPEXCEL, 'Excel5'); $objWriter->save("/var/www/sports/67/admin/excel/class.xls"); $objPHPEXCEL->disconnectWorksheets(); unset($objPHPEXCEL); header("Location: http://dpo.nttu.edu.tw/sports/67/admin/excel/class.xls"); }
/** * 生成多个sheet的excel */ public function exportSheetExcel($listArr1 , $listArr2 , $headArr1 , $headArr2){ //if(!$listArr || !$headArr) return false; set_time_limit(0); // 关闭YII的自动加载功能,改用手动加载,否则会出错,PHPExcel有自己的自动加载功能 Yii::$enableIncludePath=false; /*引入PHPExcel.php文件*/ Yii::import('application.extensions.PHPExcel.PHPExcel', 1); $obj_phpexcel = new PHPExcel(); $obj_phpexcel->setActiveSheetIndex(0); //设置第一个工作表为活动工作表 $obj_phpexcel->getActiveSheet()->setTitle('orderhead'); //设置工作表名称 $objActSheet = $obj_phpexcel->getActiveSheet(); //设置字体 $objActSheet->getDefaultStyle()->getFont()->setName('微软雅黑'); //设置字体大小 $objActSheet->getDefaultStyle()->getFont()->setSize(9); //设置自动行高 $objActSheet->getDefaultRowDimension()->setRowHeight(15); $key = 0; $keyArr = array(); foreach($headArr1 as $v){ $index = PHPExcel_Cell::stringFromColumnIndex($key); $keyArr[] = $index; //设置页头填充色 $objStyle = $objActSheet ->getStyle($index.'1'); $objFill = $objStyle->getFill(); $objFill->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objFill->getStartColor()->setARGB('#FFFF00'); //设置页头边框色 $objBorder = $objStyle->getBorders(); $objBorder->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objBorder->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objBorder->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objBorder->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); //设置自动列宽 $objActSheet->getColumnDimension($index)->setAutoSize(true); //设置对其方式 $objAlign = $objStyle->getAlignment(); $objAlign->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objAlign->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $obj_phpexcel->setActiveSheetIndex(0)->setCellValue($index.'1' , $v); $key++; } $keyWordArr = CDict::$title_key_word; $keyWordArr2 = CDict::$title_hscode_word; $key2 = 2; foreach($listArr1 as $l_key =>$info){ //行写入 $key3 = 0; foreach($info as $c_key=>$value){// 列写入 $span = $keyArr[$key3]; // $objStyle = $objActSheet ->getStyle($span.$key2); // $objStyle->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER); //设置单元格避免大数字科学计数显示 // if(in_array($c_key, array('ORDERCOMMITTIME','PAY_TIME','USER_ID','IDENTIFY_CODE'))){ // $objStyle = $objActSheet ->getStyle($span.$key2); // $objStyle->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER); // } //$objActSheet->setCellValue($span.$key2, $value); if(is_numeric($value) && $c_key != 'TRX_SERIAL_NO' && $c_key!='IDENTIFY_CODE' && $c_key!='USER_ID' && $c_key != 'GOODNO' && $c_key != 'HSCODE' && $c_key != 'RECEIVEZIPCODE' && $c_key != 'RECZIP' && $c_key != 'MERCHANTORDERID'){ $objStyle = $objActSheet ->getStyle($span.$key2); if(strpos($value,'.')){ $objStyle->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00); }else{ $objStyle->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER); } $objActSheet->setCellValue($span.$key2,$value); }else{ $objActSheet->setCellValueExplicit($span.$key2,$value,PHPExcel_Cell_DataType::TYPE_STRING); } //$objActSheet->setCellValueExplicit($span.$key2,$value,PHPExcel_Cell_DataType::TYPE_STRING); $key3++; } $key2++; } //创建第二个工作表 $newWorkSheet = new PHPExcel_Worksheet($obj_phpexcel, 'orderlist'); //创建一个工作表 $obj_phpexcel->addSheet($newWorkSheet); //插入工作表 $obj_phpexcel->setActiveSheetIndex(1); //切换到新创建的工作表 $objActSheet1 = $obj_phpexcel->getActiveSheet(); //设置字体 $objActSheet1->getDefaultStyle()->getFont()->setName('微软雅黑'); //设置字体大小 $objActSheet1->getDefaultStyle()->getFont()->setSize(9); //设置自动行高 $objActSheet1->getDefaultRowDimension()->setRowHeight(15); $key = 0; $keyArr = array(); foreach($headArr2 as $v){ $index = PHPExcel_Cell::stringFromColumnIndex($key); $keyArr[] = $index; //设置页头填充色 $objStyle = $objActSheet1 ->getStyle($index.'1'); $objFill = $objStyle->getFill(); $objFill->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objFill->getStartColor()->setARGB('#FFFF00'); //设置页头边框色 $objBorder = $objStyle->getBorders(); $objBorder->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objBorder->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objBorder->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objBorder->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); //设置自动列宽 $objActSheet1->getColumnDimension($index)->setAutoSize(true); //设置对其方式 $objAlign = $objStyle->getAlignment(); $objAlign->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objAlign->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $obj_phpexcel->setActiveSheetIndex(1)->setCellValue($index.'1' , $v); $key++; } $key2 = 2; foreach($listArr2 as $l_key =>$info){ //行写入 $key3 = 0; foreach($info as $c_key=>$value){// 列写入 $span = $keyArr[$key3]; //设置关键字高亮 if($c_key == 'NAMEZH'){ foreach ($keyWordArr as $word){ if(strpos($value, $word)!==false){ $objStyle = $objActSheet1 ->getStyle($span.$key2); $objStyle->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED); $objStyle->getFont()->setSize(10); } } } if($c_key == 'HSCODE'){ //设置关键字高亮 foreach ($keyWordArr2 as $word2){ if(strpos($value, $word2)!==false){ $objStyle = $objActSheet1 ->getStyle($span.$key2); $objStyle->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED); $objStyle->getFont()->setSize(10); } } } // $objStyle = $objActSheet ->getStyle($span.$key2); // $objStyle->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER); //设置单元格避免大数字科学计数显示 // $objStyle = $objActSheet1 ->getStyle($span.$key2); // $objStyle->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER); //$objActSheet1->setCellValue($span.$key2, $value); if(is_numeric($value) && $c_key != 'GOODNO' && $c_key != 'HSCODE' && $c_key != 'RECEIVEZIPCODE' && $c_key != 'RECZIP'){ $objStyle = $objActSheet1 ->getStyle($span.$key2); if(strpos($value,'.')){ $objStyle->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00); }else{ $objStyle->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER); } $objActSheet1->setCellValue($span.$key2,$value); }else{ $objActSheet1->setCellValueExplicit($span.$key2,$value,PHPExcel_Cell_DataType::TYPE_STRING); } //$objActSheet1->setCellValueExplicit($span.$key2,$value,PHPExcel_Cell_DataType::TYPE_STRING); $key3++; } $key2++; } $obj_phpexcel->setActiveSheetIndex(0); //设置第一个工作表为活动工作表 $obj_Writer=new PHPExcel_Writer_Excel5($obj_phpexcel);//用于其他版本格式 $date = date("YmdHis"); $filename = "export_".$date.".xls"; ob_end_clean();//清除缓冲区,避免乱码 header("Content-Type: application/force-download"); header("Content-Type: application/octet-stream"); header("Content-Type: application/download"); header('Content-Disposition:inline;filename="'.$filename.'"'); header("Content-Transfer-Encoding: binary"); header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Pragma: no-cache"); $obj_Writer->save('php://output'); }
/** * Class constructor * @param PHPExcel_Worksheet $worksheet */ public function __construct(PHPExcel $spreadsheet, PHPExcel_Worksheet $worksheet = NULL) { if ($worksheet === NULL) { $this->_worksheet = new PHPExcel_Worksheet($spreadsheet); } else { $this->_worksheet = $worksheet; } // Add worksheet to a spreadsheet $spreadsheet->addSheet($this->_worksheet); // Set worksheet title if ($this->title !== NULL) { $this->title($this->title); } }
/** * Constructs one Moodle Worksheet. * * @param string $name The name of the file * @param PHPExcel $workbook The internal Workbook object we are creating. */ public function __construct($name, PHPExcel $workbook) { // Replace any characters in the name that Excel cannot cope with. $name = strtr($name, '[]*/\\?:', ' '); // Shorten the title if necessary. $name = core_text::substr($name, 0, 31); if ($name === '') { // Name is required! $name = 'Sheet' . ($workbook->getSheetCount() + 1); } $this->worksheet = new PHPExcel_Worksheet($workbook, $name); $this->worksheet->setPrintGridlines(false); $workbook->addSheet($this->worksheet); }
$pr = $modelo->campania_001_Vodafon_one($in); if (is_array($pr['datos'])) { $ou = $modelo->campania_001_Vodafon_one_process($pr); } else { $ou = null; } } // -------------------------------------------------------- TEST // Utilidades::printr($in); // Utilidades::printr($ou); // -------------------------------------------------------- OUT $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator("Claudio Rodriguez Ore"); $objPHPExcel->getActiveSheet()->setTitle('Total'); $myWorkSheet = new PHPExcel_Worksheet($objPHPExcel, 'Supervisores'); $objPHPExcel->addSheet($myWorkSheet, 1); $myWorkSheet = new PHPExcel_Worksheet($objPHPExcel, 'Asesores'); $objPHPExcel->addSheet($myWorkSheet, 2); $border_style = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_HAIR, 'color' => array('argb' => '000000')))); $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->getColumnDimension(c0(3))->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension(c0(5))->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension(c0(8))->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension(c0(10))->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension(c0(12))->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension(c0(15))->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension(c0(17))->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension(c0(20))->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension(c0(22))->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension(c0(24))->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension(c0(26))->setWidth(6);
function downloadPHPExcelFile() { $sheet = new PHPExcel(); $sheet->getProperties()->setCreator('business.usa.gov')->setLastModifiedBy('business.usa.gov')->setTitle('Expired Event Summary')->setKeywords('events'); $sheet->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP); $sheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); $sheet->getDefaultStyle()->getFont()->setName('Arial'); $sheet->getDefaultStyle()->getFont()->setSize(12); $sheet->setActiveSheetIndex(0); $activeSheet = $sheet->getActiveSheet(); $activeSheet->setTitle('Events Summary'); $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'); /** * Print cell value 'Expired Event Count' */ $activeSheet->setCellValue('A1', 'Events Expired'); foreach ($results = get_expired_event_count_mysql() as $result) { $activeSheet->setCellValue('A2', $result->count); } /** * Current event count page */ $activeSheet->setCellValue('B1', 'Events Current'); foreach ($results = get_current_event_count_mysql() as $result) { $activeSheet->setCellValue('B2', $result->count); } // Format cells A & B $styleArray = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('argb' => 'EBEBEBEB'))); $allBorders = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN))); $activeSheet->getStyle('A1:B1')->applyFromArray($styleArray); $activeSheet->getStyle('A4:B4')->applyFromArray($styleArray); $activeSheet->getStyle('D4:E4')->applyFromArray($styleArray); $activeSheet->getStyle('G4:H4')->applyFromArray($styleArray); $activeSheet->getStyle('J4:K4')->applyFromArray($styleArray); // Loop to autoSize culumns width for ($col = 'A'; $col <= 'K'; $col++) { $activeSheet->getColumnDimension($col)->setAutoSize(true); } //Border formating $activeSheet->getStyle('A1:B2')->applyFromArray($allBorders); /** * Count by event type */ // Print column table labels $header = array('Events Expired', 'Event Type'); foreach ($header as $key => $value) { $activeSheet->setCellValueByColumnAndRow($key, 4, $value); } $rowNum = 5; $colNum = 0; foreach ($results = get_expired_event_count_by_type_mysql() as $result) { $activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->node_count); $activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->event_type != '' ? $result->event_type : 'None'); $rowNum++; $colNum = 0; } $resultsCount = 4 + count($results); $activeSheet->getStyle('A4:B' . $resultsCount)->applyFromArray($allBorders); $header = array('Events Current', 'Event Type'); $rowNum = $resultsCount + 2; $initialRowNum = $rowNum; $colNum = 0; foreach ($header as $value) { $activeSheet->setCellValueByColumnAndRow($colNum, $rowNum, $value); $colNum++; } $rowNum = $initialRowNum + 1; $colNum = 0; foreach ($results = get_current_event_count_by_type_mysql() as $result) { $activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->node_count); $activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->event_type != '' ? $result->event_type : 'None'); $rowNum++; $colNum = 0; } $resultsCount = $initialRowNum + count($results); $activeSheet->getStyle('A' . $initialRowNum . ':B' . $resultsCount)->applyFromArray($allBorders); $activeSheet->getStyle('A' . $initialRowNum . ':B' . $initialRowNum)->applyFromArray($styleArray); /** * event count by agency */ $header = array('Event Expired Count by', 'Agency'); $colNum = 3; foreach ($header as $value) { $activeSheet->setCellValueByColumnAndRow($colNum, 4, $value); $colNum++; } $rowNum = 5; $colNum = 3; $final_list = GetEventsByAgencies(0); /** * Loop through the new array after organization mapping and print the output on the excel sheet. */ foreach ($final_list as $key => $value) { $activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $value); $activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $key); $rowNum++; $colNum = 3; } $resultsCount = 4 + count($final_list); $activeSheet->getStyle('D4:E' . $resultsCount)->applyFromArray($allBorders); $header = array('Event Current Count by', 'Agency'); $rowNum = $resultsCount + 2; $initialRowNum = $rowNum; $colNum = 3; foreach ($header as $value) { $activeSheet->setCellValueByColumnAndRow($colNum, $rowNum, $value); $colNum++; } $rowNum = $initialRowNum + 1; $colNum = 3; $final_list = GetEventsByAgencies(1); foreach ($final_list as $key => $value) { $activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $value); $activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $key); $rowNum++; $colNum = 3; } $resultsCount = $initialRowNum + count($final_list); $activeSheet->getStyle('D' . $initialRowNum . ':E' . $resultsCount)->applyFromArray($allBorders); $activeSheet->getStyle('D' . $initialRowNum . ':E' . $initialRowNum)->applyFromArray($styleArray); /** * print value for events entered manually */ $header = array('Events Expired', 'User Name'); $colNum = 6; foreach ($header as $value) { $activeSheet->setCellValueByColumnAndRow($colNum, 4, $value); $colNum++; } $rowNum = 5; $colNum = 6; foreach ($results = get_expired_event_manual_entry_users_mysql() as $result) { $activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->node_count); $activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->user_name != null ? $result->user_name : 'Anonymous'); $rowNum++; $colNum = 6; } $resultsCount = 4 + count($results); $activeSheet->getStyle('G4:H' . $resultsCount)->applyFromArray($allBorders); $header = array('Events Current', 'User Name'); $rowNum = $resultsCount + 2; $initialRowNum = $rowNum; $colNum = 6; foreach ($header as $value) { $activeSheet->setCellValueByColumnAndRow($colNum, $rowNum, $value); $colNum++; } $rowNum = $initialRowNum + 1; $colNum = 6; foreach ($results = get_current_event_manual_entry_users_mysql() as $result) { $activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->node_count); $activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->user_name); $rowNum++; $colNum = 6; } $resultsCount = $initialRowNum + count($results); $activeSheet->getStyle('G' . $initialRowNum . ':H' . $resultsCount)->applyFromArray($allBorders); $activeSheet->getStyle('G' . $initialRowNum . ':H' . $initialRowNum)->applyFromArray($styleArray); /** * Count and names of Feed Importers */ $header = array('Events Expired', 'Feed Importer'); $colNum = 9; foreach ($header as $value) { $activeSheet->setCellValueByColumnAndRow($colNum, 4, $value); $colNum++; } $rowNum = 5; $colNum = 9; foreach ($results = get_expired_event_feed_importers_name_mysql() as $result) { $activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->node_count); $activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->user_name); $rowNum++; $colNum = 9; } $resultsCount = 4 + count($results); $activeSheet->getStyle('J4:K' . $resultsCount)->applyFromArray($allBorders); $header = array('Events Current', 'Feed Importer'); $rowNum = $resultsCount + 2; $initialRowNum = $rowNum; $colNum = 9; foreach ($header as $value) { $activeSheet->setCellValueByColumnAndRow($colNum, $rowNum, $value); $colNum++; } $rowNum = $initialRowNum + 1; $colNum = 9; foreach ($results = get_current_event_feed_importers_name_mysql() as $result) { $activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->node_count); $activeSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->user_name != '' ? $result->user_name : 'Anonymous'); $rowNum++; $colNum = 9; } $resultsCount = $initialRowNum + count($results); $activeSheet->getStyle('J' . $initialRowNum . ':K' . $resultsCount)->applyFromArray($allBorders); $activeSheet->getStyle('J' . $initialRowNum . ':K' . $initialRowNum)->applyFromArray($styleArray); /** * Create a new worksheet called "Event Detail" $secondSheet */ $secondSheet = new PHPExcel_Worksheet($sheet, 'Event Detail'); $sheet->addSheet($secondSheet, 1); $secondSheet = $sheet->getSheet(1); $secondSheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $secondSheet->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); $secondSheet->getStyle('A1:N1')->applyFromArray($styleArray); // Loop to autoSize culumns width for ($col = 'A'; $col <= 'M'; $col++) { $secondSheet->getColumnDimension($col)->setAutoSize(true); } $secondSheet->getColumnDimension('N')->setWidth(60); // Print column table labels $header = array('Event Title', 'Detail Description', 'Event Start Date', 'Event End Date', 'Start Time', 'End Time', 'Address 1', 'Address 2', 'City', 'State', 'Zip Code', 'Country', 'Organization', 'Learn More URL'); foreach ($header as $key => $value) { $secondSheet->setCellValueByColumnAndRow($key, 1, $value); } // Print cell values $rowNum = 2; $colNum = 0; foreach ($results = current_events_detail_list_mysql() as $result) { $secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->title); $secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, strip_tags($result->field_event_detail_desc_value)); $secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, date('M-d-Y', strtotime($result->field_event_date_value))); $secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->field_event_date_value2 != null ? date('M-d-Y', strtotime($result->field_event_date_value2)) : $result->field_event_date_value2); $secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, date('H:i:s', strtotime($result->field_event_date_value))); $secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, date('H:i:s', strtotime($result->field_event_date_value2))); $secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->field_event_address_1_value); $secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->field_event_address_2_value); $secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->field_event_city_value); $secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->field_event_state_value); $secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->field_event_zip_value); $secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->field_event_country_value); $secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->field_program_org_tht_owns_prog_value); $secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $result->field_event_url_url); //$secondSheet->setCellValueByColumnAndRow($colNum++, $rowNum, $GLOBALS['base_url'] . '/node/' . $result->nid); $rowNum++; $colNum = 0; } $resultsCount = 1 + count($results); $secondSheet->getStyle('A1:N' . $resultsCount)->applyFromArray($allBorders); /** * redirect output to client browser */ header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="event-summary.xls"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($sheet, 'Excel5'); $objWriter->save('php://output'); return; }
// Miscellaneous glyphs, UTF-8 $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A4', 'Miscellaneous glyphs')->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç'); $objPHPExcel->getActiveSheet()->setCellValue('A8', "Hello\nWorld"); $objPHPExcel->getActiveSheet()->getRowDimension(8)->setRowHeight(-1); $objPHPExcel->getActiveSheet()->getStyle('A8')->getAlignment()->setWrapText(true); // Rename worksheet echo date('H:i:s'), " Rename worksheet", EOL; $objPHPExcel->getActiveSheet()->setTitle('Simple'); // Clone worksheet echo date('H:i:s'), " Clone worksheet", EOL; $clonedSheet = clone $objPHPExcel->getActiveSheet(); $clonedSheet->setCellValue('A1', 'Goodbye')->setCellValue('A2', 'cruel')->setCellValue('C1', 'Goodbye')->setCellValue('C2', 'cruel'); // Rename cloned worksheet echo date('H:i:s'), " Rename cloned worksheet", EOL; $clonedSheet->setTitle('Simple Clone'); $objPHPExcel->addSheet($clonedSheet); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Save Excel 2007 file echo date('H:i:s'), " Write to Excel2007 format", EOL; $callStartTime = microtime(true); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save(str_replace('.php', '.xlsx', __FILE__)); $callEndTime = microtime(true); $callTime = $callEndTime - $callStartTime; echo date('H:i:s'), " File written to ", str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)), EOL; echo 'Call time to write Workbook was ', sprintf('%.4f', $callTime), " seconds", EOL; // Echo memory usage echo date('H:i:s'), ' Current memory usage: ', memory_get_usage(true) / 1024 / 1024, " MB", EOL; // Echo memory peak usage echo date('H:i:s'), " Peak memory usage: ", memory_get_peak_usage(true) / 1024 / 1024, " MB", EOL;
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; }
} $start_date = $start_date_day . "." . $start_date_month . "." . $start_date['year']; $end_date = getdate($end); $end_date_day = $end_date["mday"]; if ($end_date_day < 10) { $end_date_day = "0" . $end_date_day; } $end_date_month = $end_date["mon"]; if ($end_date_month < 10) { $end_date_month = "0" . $end_date_month; } $end_date = $end_date_day . "." . $end_date_month . "." . $end_date['year']; $objPHPExcel = new PHPExcel(); $objPHPExcel->removeSheetByIndex(0); $worksheet = new PHPExcel_Worksheet($objPHPExcel, 'График поездок'); $objPHPExcel->addSheet($worksheet, 1); $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); $con = oci_connect('purchases', 'PURCHASES', '192.168.50.52/ORCLWORK', 'AL32UTF8'); if (!$con) { oci_close($con); die('Ошибка подключения к базе'); } date_default_timezone_set("Etc/GMT-4"); $query = "SELECT *\n FROM V_AUTO_REQUESTS\n WHERE V_AUTO_REQUESTS.START_TIME >= '{$start}' AND\n V_AUTO_REQUESTS.START_TIME <= '{$end}' AND\n V_AUTO_REQUESTS.REQUEST_DEPARTMENT = {$department} AND\n V_AUTO_REQUESTS.REQUEST_REJECT_RECIEVED = 0\n ORDER BY V_AUTO_REQUESTS.REQUEST_ID ASC "; $statement = oci_parse($con, $query); if (oci_execute($statement, OCI_DEFAULT)) { while ($res = oci_fetch_array($statement)) { $requests[] = $res; }
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; }
/** * Get a more complete report- a data dump for analysis * @global type $CFG * @param type $formID * @return PHPExcel an Excel spreadsheet containing a detailed report of the results of an assessment session */ public function getFullReportAsExcel($session_ID) { $overviewXML = simplexml_load_string($this->getSummaryReportForExamInstance($session_ID)); // print("form ID is: $session_ID <br/>"); global $CFG; // questions labels. // alphabet array $alphabetArr = array(); foreach (range('A', 'Z') as $i) { $alphabetArr[] = $i; } // this extends the possible spreadsheet cells a bit. foreach (range('A', 'Z') as $i) { $alphabetArr[] = "A" . $i; } foreach (range('A', 'Z') as $i) { $alphabetArr[] = "B" . $i; } foreach (range('A', 'Z') as $i) { $alphabetArr[] = "C" . $i; } // There shouldn't be more than, say, 70 $j = 0; // get student sessions $sessionsXMLStr = "<data>"; try { $conn = new PDO("mysql:host={$CFG->db};dbname={$CFG->schema}", $CFG->dbuser, $CFG->dbuserpass); } catch (PDOException $e) { die('<data><error>failed connecting to database</error><detail>' . $e->getMessage() . '</detail></data>'); } $query = "SELECT a.ID, a.student_id, a.overall_rating, a.additional_rating, a.site_ID,\n (SELECT fname FROM students WHERE students.ID = a.student_id) as fname, \n (SELECT lname FROM students WHERE students.ID = a.student_id) as lname, \n (SELECT studentnum FROM students WHERE students.ID = a.student_id) as studentnum, a.form_id, a.start_timestamp\n from student_exam_sessions a inner join exam_instances b on a.form_ID = b.ID WHERE a.form_ID = :session_ID"; $stmt = $conn->prepare($query); $stmt->bindValue(':session_ID', $session_ID, PDO::PARAM_INT); $stmt->execute() or die('<data><error>getSummaryReportForExamInstance query failed</error><detail>' . $stmt->errorCode() . '</detail></data>'); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $sessionsXMLStr .= "<session><id>{$row['ID']}</id><student_id>{$row['student_id']}</student_id><fname>{$row['fname']}</fname><lname>{$row['lname']}</lname><studentnum>{$row['studentnum']}</studentnum><siteid>{$row['site_ID']}</siteid><created_timedate>{$row['start_timestamp']}</created_timedate></session>"; } $sessionsXMLStr .= "</data>"; // print($query); $sessionsXML = simplexml_load_string($sessionsXMLStr); // get question definitions for this session $enumlib = new EnumLib(); $questionsXML = simplexml_load_string($enumlib->getQuestionsForSession($session_ID)); // create excel sheet $phpexcelObj = new PHPExcel(); // make a new Excel sheet $quantitativeWorksheet = new PHPExcel_Worksheet($phpexcelObj, 'Quantitative Cohort Outcomes'); $phpexcelObj->addSheet($quantitativeWorksheet, 0); // set labels $quantitativeWorksheet->getColumnDimension("A")->setWidth(200); $quantitativeWorksheet->setCellValue('A1', "Quantitative Cohort Outcomes for: {$overviewXML->summary->description} {$overviewXML->summary->examdate}, n=" . count($sessionsXML->session)); $quantitativeWorksheet->getStyle('A1')->getFont()->setSize(16); $quantitativeWorksheet->setCellValue('A2', "Item"); $quantitativeWorksheet->getStyle("A2")->getFont()->setBold(true); // $quantitativeWorksheet->setCellValue('C2', "Percentage"); // $quantitativeWorksheet->getStyle("C2")->getFont()->setBold(true); // $quantitativeWorksheet->getColumnDimension("C")->setAutoSize(true); $questionscount = 0; $questionsArr = array(); $k = 3; // foreach ($questionsXML->question as $question) { //set all criteria labels $quantitativeWorksheet->setCellValue("A{$k}", $question->text); // $quantitativeWorksheet->getStyle("A$k")->getFont()->setBold(true); $quantitativeWorksheet->getColumnDimension("A")->setWidth(150); // get the criteria $currentColumn = 1; $criteriaDef = simplexml_load_string($enumlib->getCriteriaScaleItems($overviewXML->summary->scale_id)); foreach ($criteriaDef->item as $item) { $quantitativeWorksheet->setCellValue("{$alphabetArr[$currentColumn]}2", "n marked {$item->short_description}"); $quantitativeWorksheet->getStyle("{$alphabetArr[$currentColumn]}2")->getFont()->setBold(true); $quantitativeWorksheet->getColumnDimension("{$alphabetArr[$currentColumn]}")->setAutoSize(true); $quantitativeWorksheet->setCellValue("{$alphabetArr[$currentColumn + 1]}2", "% marked {$item->short_description}"); $quantitativeWorksheet->getStyle("{$alphabetArr[$currentColumn + 1]}2")->getFont()->setBold(true); $quantitativeWorksheet->getColumnDimension("{$alphabetArr[$currentColumn + 1]}")->setAutoSize(true); $query = "SELECT COUNT(answer) as total FROM student_exam_sessions_responses WHERE question_ID = :id AND answer = :answer AND student_exam_sessions_responses.student_exam_session_ID IN(SELECT ID FROM student_exam_sessions WHERE student_exam_sessions.form_ID = :session_ID)"; $stmt = $conn->prepare($query); $stmt->bindValue(':id', $question->id, PDO::PARAM_INT); $stmt->bindValue(':answer', $item->value, PDO::PARAM_STR); $stmt->bindValue(':session_ID', $session_ID, PDO::PARAM_INT); $stmt->execute() or die('<data><error>select query failed query failed</error><detail>' . $stmt->errorCode() . '</detail></data>'); $resultArr = array(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $quantitativeWorksheet->setCellValue("{$alphabetArr[$currentColumn]}{$k}", $row['total']); $quantitativeWorksheet->setCellValue("{$alphabetArr[$currentColumn + 1]}{$k}", round($row['total'] / count($sessionsXML->session), 2) * 100 . "%"); } $currentColumn += 2; $stmt->closeCursor(); } $k++; } // qualitative data $criteriaWorksheet = new PHPExcel_Worksheet($phpexcelObj, 'Criteria Comments'); $phpexcelObj->addSheet($criteriaWorksheet, 1); // set labels $criteriaWorksheet->getColumnDimension("A")->setWidth(150); $criteriaWorksheet->setCellValue('A1', "Qualitative Comments on Criteria Requiring Comments: {$overviewXML->summary->description} {$overviewXML->summary->examdate}, n=" . count($sessionsXML->session)); $criteriaWorksheet->getStyle('A1')->getFont()->setSize(16); $criteriaWorksheet->setCellValue('A2', "Item"); $criteriaWorksheet->getStyle("A2")->getFont()->setBold(true); $criteriaWorksheet->setCellValue('B2', "Comments"); $criteriaWorksheet->getStyle("B2")->getFont()->setBold(true); $criteriaWorksheet->getColumnDimension("B")->setAutoSize(true); $k = 3; // foreach ($questionsXML->question as $question) { //set all criteria labels // add up questions here //$currentrow = 4; //print('num'.$studentexamsession->studentnum); $sql = "SELECT COUNT(*) FROM student_exam_sessions_responses WHERE question_ID = :id AND answer = 0 AND student_exam_sessions_responses.student_exam_session_ID IN(SELECT ID FROM student_exam_sessions WHERE student_exam_sessions.form_ID = :session_ID)"; $stmt = $conn->prepare($sql); $stmt->bindValue(':id', $question->id, PDO::PARAM_INT); $stmt->bindValue(':session_ID', $session_ID, PDO::PARAM_INT); if ($stmt->execute()) { if ($stmt->fetchColumn() > 0) { $stmt->closeCursor(); $criteriaWorksheet->setCellValue("A{$k}", html_entity_decode($question->text, ENT_QUOTES, 'UTF-8')); // $quantitativeWorksheet->getStyle("A$k")->getFont()->setBold(true); $criteriaWorksheet->getColumnDimension("A")->setAutoSize(true); $query = "SELECT comments FROM student_exam_sessions_responses WHERE question_ID = :id AND answer IN (SELECT value FROM assessment_criteria_scales_items WHERE assessment_criteria_scale_typeID = :criteriaid AND needs_comment ='true')"; $stmt = $conn->prepare($query); $stmt->bindValue(':id', $question->id, PDO::PARAM_INT); $stmt->bindValue(':criteriaid', $overviewXML->summary->scale_id, PDO::PARAM_STR); $stmt->execute() or die('<data><error>select query failed query failed</error><detail>' . $stmt->errorCode() . '</detail></data>'); $resultArr = array(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $criteriaWorksheet->setCellValue("B{$k}", $row['comments']); $k++; } $stmt->closeCursor(); } } } // qualitative data $overallCommentsWorksheet = new PHPExcel_Worksheet($phpexcelObj, 'Overall Comments'); $phpexcelObj->addSheet($overallCommentsWorksheet, 2); // set labels $overallCommentsWorksheet->getColumnDimension("A")->setWidth(200); $overallCommentsWorksheet->setCellValue('A1', "Qualitative Overall Comments: {$overviewXML->summary->description} {$overviewXML->summary->examdate}, n=" . count($sessionsXML->session)); $overallCommentsWorksheet->getStyle('A1')->getFont()->setSize(16); $overallCommentsWorksheet->setCellValue('A2', "Comments"); $overallCommentsWorksheet->getStyle("A2")->getFont()->setBold(true); $k = 3; // foreach ($overviewXML->session as $session) { if (strlen($session->comments) > 0) { $overallCommentsWorksheet->setCellValue("A{$k}", html_entity_decode($session->comments, ENT_QUOTES, 'UTF-8')); // $quantitativeWorksheet->getStyle("A$k")->getFont()->setBold(true); $k++; } } $rawWorksheet = new PHPExcel_Worksheet($phpexcelObj, 'Raw Scores Data Dump'); $phpexcelObj->addSheet($rawWorksheet, 3); // assessment raw data $rawWorksheet->setCellValue('A1', "Assessment Raw Data: {$overviewXML->summary->description} {$overviewXML->summary->examdate}"); $rawWorksheet->getStyle('A1')->getFont()->setSize(16); $rawWorksheet->setCellValue('A2', "Student Number"); $rawWorksheet->getStyle('A2')->getFont()->setBold(true); $rawWorksheet->setCellValue('B2', "Student Name"); $rawWorksheet->getStyle('B2')->getFont()->setBold(true); $rawWorksheet->setCellValue('C2', "Site ID"); $rawWorksheet->getStyle('C2')->getFont()->setBold(true); $rawWorksheet->setCellValue('D2', "Assessment Timestamp"); $rawWorksheet->getStyle('D2')->getFont()->setBold(true); // $rawWorksheet->getColumnDimension('B')->setAutoSize(true); // $rawWorksheet->getColumnDimension('C')->setAutoSize(true); $questionscount = 0; $questionsArr = array(); $k = 0; // get all criteria labels foreach ($questionsXML->question as $question) { // $criterias = simplexml_load_string($enumlib->getCriteriaForQuestion($question->id, false)); // foreach ($criterias as $criteria) { // print($criteria->text . '<br/>'); if ($question->type != 'label') { $rawWorksheet->setCellValue($alphabetArr[$k] . '2', $question->text); $rawWorksheet->getStyle($alphabetArr[$k] . '2')->getFont()->setBold(true); $rawWorksheet->getColumnDimension($alphabetArr[$k])->setAutoSize(true); $questionsArr[] = array("id" => (string) $question->id, "column" => $alphabetArr[$k]); // get student results, loop to populate all answers to this criteria // can probably do this better... $questionscount++; $k++; } // } } // print('questionsArr:<br/>'); // print_r($questionsArr); // populate student details $currentrow = 4; foreach ($sessionsXML->session as $studentexamsession) { //print('num'.$studentexamsession->studentnum); $rawWorksheet->setCellValue('A' . $currentrow, $studentexamsession->studentnum); $rawWorksheet->setCellValue('B' . $currentrow, $studentexamsession->fname . ' ' . $studentexamsession->lname); $rawWorksheet->setCellValue('C' . $currentrow, $studentexamsession->siteid); $rawWorksheet->setCellValue('D' . $currentrow, $studentexamsession->created_timedate); $rawWorksheet->setCellValue('E' . $currentrow, $studentexamsession->created_timedate); $query = "SELECT * FROM student_exam_sessions_responses WHERE student_exam_session_ID = :sessionid"; $stmt = $conn->prepare($query); $stmt->bindValue(':sessionid', $studentexamsession->id, PDO::PARAM_INT); $stmt->execute() or die('<data><error>>select query failed query failed</error><detail>' . $stmt->errorCode() . '</detail></data>'); $resultArr = array(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $resultArr[] = array("id" => $row['question_ID'], "answer" => $row['answer']); } for ($i = 0; $i < count($questionsArr); $i++) { $rawWorksheet->setCellValue($questionsArr[$i]['column'] . $currentrow, $this->findAnswer($questionsArr[$i]['id'], $resultArr)); } $currentrow++; $stmt->closeCursor(); } $phpexcelObj->setActiveSheetIndex(0); return $phpexcelObj; }
function reporte($buque, $viaje, $codPuerto) { global $linkSrv, $linkDB, $linkUsr, $linkPass, $connI; // ------------------------------------------------------------------------------- // 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 //$connI = odbc_connect($dsn, $linkUsr, $linkPass); //$connI2 = 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('Importacion'); $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial'); $objPHPExcel->getDefaultStyle()->getFont()->setSize(8); // HOJA 2 $workSheet2 = new PHPExcel_Worksheet($objPHPExcel, 'Transbordos'); $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('no', 'origen', 'regimen', 'destino', 'bl', 'contenedor', 'tipo', 'medida', 'iso', 'producto', 'paquetes', 'embalaje', 'peso', 'sello', 'transporte', 'un', 'imo'); // 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:Q7'); // 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); // Titulo $objPHPExcel->getActiveSheet()->setCellValue('A1', 'MEDITERRANEAN SHIPPING COMPANY MEXICO, S.A. DE C.V.')->getStyle('A1')->applyFromArray($styleTitulo); // SUBTitulo $objPHPExcel->getActiveSheet()->setCellValue('A2', 'CONSTITUCION No.7 PLANTA BAJA'); $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo); $objPHPExcel->getActiveSheet()->setCellValue('A3', 'COL.BUROCRATA, MANZANILLO'); $objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo); $objPHPExcel->getActiveSheet()->setCellValue('A4', "{$buque} {$viaje}"); $objPHPExcel->getActiveSheet()->getStyle('A4')->applyFromArray($styleSubtitulo); $objPHPExcel->getActiveSheet()->setCellValue('A5', 'Importacion / Transbordo'); $objPHPExcel->getActiveSheet()->getStyle('A5')->applyFromArray($styleSubtitulo); } // ----------------------------------- // HOJA 1 // ----------------------------------- $objPHPExcel->setActiveSheetIndex(0); // Simple query $sql = "\n SELECT\n -- EC.EventType,\n\tEC.POL as POL,\n\tBL.FDest as FDest,\n\tBL.Bill_Of_Lading_Number as Bl1,\n\tEC.EC_BIll_Of_Lading_Number as Bl2,\n\tEC.Equipment_Number as Conte,\n\tEC.EqType as TipoConte,\n\tEC.ET_Full_or_Empty as ISO,\n\tC.Ex_Short_Description as Producto,\n\tC.BL_Cargo_Packages as Paquetes,\n\tC.Ex_Package_Type_Code as Embalaje,\n\t-- C.Packages,\n\tC.TotCntr_Cargo_Weight_Kgs as Peso,\n\tC.Seal_No as Sello,\n\tdbo.FN_MX_ConcatenaBOLREP_Transport_Mode (C.BC_BOLREP_id, DEFAULT) AS TransMode,\n\t-- DTX_MOF_Name,\n\tR.IMO_Code as ImoCode,\n\tR.IMO_Class_Number as ImoCodeClass,\n\tR.UN_Number as UNNumber\n\t-- TS1.*\n FROM Interlink_DFeed90. dfeed.FNTB_Equipment_Cycle (0,null) EC\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_Booking_BL_CSC (0,null) BL on BL.EQ_Cycle_id=EC.Eq_Cycle_id\n LEFT JOIN Interlink_Main90.dbo.Cargo_Shipment S (nolock) on S.Cargo_Shipment_id=BL.Cargo_Shipment_id\n left join Interlink_DFeed90. dfeed.FNTB_BOLREP_Container () C on EC.Equipment_Number=C.Container_Number and C.BC_BOLREP_id in (select max(BC_BOLREP_id) from Interlink_DFeed90.dfeed.FNTB_BOLREP_Container () where Container_Number=EC.Equipment_Number )\n LEFT JOIN Interlink_DFeed90. dfeed.FNTB_BOLREP_Cargo () R on R.BOLREP_Container_id=C.BOLREP_Container_id\n LEFT JOIN Interlink_DFeed90. dfeed.FNTB_AssocDocCompany( 'SHIPPER') ADC on EC.bill_of_lading_id=ADC.Bill_Of_Lading_id\n LEFT JOIN (\n SELECT TS.cargo_shipment_container_id , P1. EDI_Code TS1, P2.EDI_Code TS2, P3.EDI_Code TS3 , P4. EDI_Code TS4, P5 .EDI_Code TS5,\n CASE WHEN 'MX' IN (LEFT(P1. EDI_Code,2 ),LEFT(P2. EDI_Code,2 ),LEFT(P3. EDI_Code,2 ),LEFT(P4. EDI_Code,2 ),LEFT(P5. EDI_Code,2 )) THEN 1 ELSE 0 END Transshipment\n FROM Interlink_Main90. dbo.FNTB_TSPorts_CSC () TS\n LEFT JOIN Interlink_DFeed90.dfeed .FNTB_Port_EDI( 1,'UN CODE' ) P1 ON P1.port_id = TS.TS1\n LEFT JOIN Interlink_DFeed90.dfeed .FNTB_Port_EDI( 1,'UN CODE' ) P2 ON P2.port_id = TS.TS2\n LEFT JOIN Interlink_DFeed90.dfeed .FNTB_Port_EDI( 1,'UN CODE' ) P3 ON P3.port_id = TS.TS3\n LEFT JOIN Interlink_DFeed90.dfeed .FNTB_Port_EDI( 1,'UN CODE' ) P4 ON P4.port_id = TS.TS4\n LEFT JOIN Interlink_DFeed90.dfeed .FNTB_Port_EDI( 1,'UN CODE' ) P5 ON P5.port_id = TS.TS5\n ) TS1 ON TS1.cargo_shipment_container_id=C.Cargo_Shipment_Container_idx\n where EC.Vessel_Name='{$buque}' AND EC.Voyage='{$viaje}' and EC.PODUN='{$codPuerto}'\n AND EC.EventType in ('IDV','MDV','ILV')\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)) { $bl1 = odbc_result($rs, "Bl1"); $bl2 = odbc_result($rs, "Bl2"); $pol = odbc_result($rs, "POL"); $dest = odbc_result($rs, "FDest"); $conte = odbc_result($rs, "Conte"); $tipoConte = odbc_result($rs, "TipoConte"); $iso = odbc_result($rs, "ISO"); $producto = odbc_result($rs, "Producto"); $paquetes = odbc_result($rs, "Paquetes"); $embalaje = odbc_result($rs, "Embalaje"); $peso = odbc_result($rs, "Peso"); $sello = odbc_result($rs, "Sello"); $transMode = odbc_result($rs, "TransMode"); $UNNumber = odbc_result($rs, "UNNumber"); $imoCode = odbc_result($rs, "ImoCode"); $imoCodeClass = odbc_result($rs, "ImoCodeClass"); if (!empty($bl1)) { $bl = $bl1; } else { $bl = $bl2; } if (preg_match("/(\\d{2,2})(.+)/i", $tipoConte, $parts)) { $tipo = $parts[1]; $medida = $parts[2]; } unset($parts); // Regimen if (preg_match("/MAZATLAN|GUAYMAS/i", $dest)) { $regimen = "T-S"; } else { $regimen = "IMP"; } // Por manejo de memoria se deben de meter los datos con las menos operaciones posibles. $objPHPExcel->getActiveSheet()->setCellValue('A' . $rowNumber, $lin)->setCellValue('B' . $rowNumber, $pol)->setCellValue('C' . $rowNumber, $regimen)->setCellValue('D' . $rowNumber, $dest)->setCellValue('E' . $rowNumber, $bl)->setCellValue('F' . $rowNumber, $conte)->setCellValue('G' . $rowNumber, $tipo)->setCellValue('H' . $rowNumber, $medida)->setCellValue('I' . $rowNumber, $iso)->setCellValue('J' . $rowNumber, $producto)->setCellValue('K' . $rowNumber, $paquetes)->setCellValue('L' . $rowNumber, $embalaje)->setCellValue('M' . $rowNumber, $peso)->setCellValue('N' . $rowNumber, $sello)->setCellValue('O' . $rowNumber, $transMode)->setCellValue('P' . $rowNumber, $UNNumber)->setCellValue('Q' . $rowNumber, $imoCodeClass)->setCellValue('R' . $rowNumber, $imoCode); $rowNumber++; $lin++; } // ------------------------------ // Hoja 2 - TRANSBORDOS // ------------------------------ $objPHPExcel->setActiveSheetIndex(1); // Simple query $sql = "\n SELECT\n\tE.Orig_POL as POL,\n\tE.POD as FDest,\n\tE.Bill_of_Lading_Number as Bl1,\n\tE.Equipment_Number as Conte,\n\tE.EQ_Type as TipoConte,\n\tE.IsEmpty as IsEmpty,\n\tE.Ex_Cargo_Short_Description as Producto,\n\tE.Cargo_Packages as Paquetes,\n\tE.Ex_Cargo_Package_Type as Embalaje,\n\tE.Cargo_Weight_Kgs as Peso,\n\tdbo.FN_MX_ConcatenaBOLREP_Transport_Mode (C.BC_BOLREP_id, DEFAULT) AS TransMode,\n\tR.IMO_Code as ImoCode,\n\tR.IMO_Class_Number as ImoCodeClass,\n\tR.UN_Number as UNNumber,\n\tC.Seal_No as Sello\n FROM\n Interlink_DFeed90.dfeed.TSHIP_FNTB_Equipment (NULL, NULL) E\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_BOLREP_Container () C ON E.Equipment_Number = C.Container_Number\n AND C.BC_BOLREP_id IN (\n SELECT\n MAX (BC_BOLREP_id)\n FROM\n Interlink_DFeed90.dfeed.FNTB_BOLREP_Container ()\n WHERE\n Container_Number = E.Equipment_Number\n )\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_BOLREP_Cargo () R ON C.BOLREP_Container_id = R.BOLREP_Container_id\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_Booking_BL (NULL, NULL) Bl ON E.Bill_Of_Lading_Number = Bl.Bill_Of_Lading_Number\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_AssocDocCompany ('SHIPPER') ADS ON Bl.Bill_Of_Lading_id = ADS.Bill_Of_Lading_id\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_AssocDocCompany ('CONSIGN') ADC ON Bl.Bill_Of_Lading_id = ADC.Bill_Of_Lading_id\n WHERE\n E.VPCDisch_Vessel_Name = '{$buque}'\n AND E.VPCDisch_Voyage = '{$viaje}'\n AND E.VPCDisch_UNCode = '{$codPuerto}'\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)) { $bl1 = odbc_result($rs, "Bl1"); //$bl2 = odbc_result($rs, "Bl2"); $pol = odbc_result($rs, "POL"); $dest = odbc_result($rs, "FDest"); $conte = odbc_result($rs, "Conte"); $tipoConte = odbc_result($rs, "TipoConte"); $isEmpty = odbc_result($rs, "IsEmpty"); if ($isEmpty == 1) { $iso = "E"; } else { $iso = "F"; } $producto = odbc_result($rs, "Producto"); $paquetes = odbc_result($rs, "Paquetes"); $embalaje = odbc_result($rs, "Embalaje"); $peso = odbc_result($rs, "Peso"); $sello = odbc_result($rs, "Sello"); $transMode = odbc_result($rs, "TransMode"); $UNNumber = odbc_result($rs, "UNNumber"); $imoCode = odbc_result($rs, "ImoCode"); $imoCodeClass = odbc_result($rs, "ImoCodeClass"); if (!empty($bl1)) { $bl = $bl1; } else { $bl = $bl2; } if (preg_match("/(\\d{2,2})(.+)/i", $tipoConte, $parts)) { $tipo = $parts[1]; $medida = $parts[2]; } unset($parts); // Regimen if (preg_match("/MAZATLAN|GUAYMAS/i", $dest)) { $regimen = "T-S"; } else { $regimen = "IMP"; } // Por manejo de memoria se deben de meter los datos con las menos operaciones posibles. $objPHPExcel->getActiveSheet()->setCellValue('A' . $rowNumber, $lin)->setCellValue('B' . $rowNumber, $pol)->setCellValue('C' . $rowNumber, $regimen)->setCellValue('D' . $rowNumber, $dest)->setCellValue('E' . $rowNumber, $bl)->setCellValue('F' . $rowNumber, $conte)->setCellValue('G' . $rowNumber, $tipo)->setCellValue('H' . $rowNumber, $medida)->setCellValue('I' . $rowNumber, $iso)->setCellValue('J' . $rowNumber, $producto)->setCellValue('K' . $rowNumber, $paquetes)->setCellValue('L' . $rowNumber, $embalaje)->setCellValue('M' . $rowNumber, $peso)->setCellValue('N' . $rowNumber, $sello)->setCellValue('O' . $rowNumber, $transMode)->setCellValue('P' . $rowNumber, $UNNumber)->setCellValue('Q' . $rowNumber, $imoCodeClass)->setCellValue('R' . $rowNumber, $imoCode); $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="repForCapitania.xlsx"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); exit; }
function reporte($fec1 = "", $fec2 = "", $IE, $oficina = "", $usrCap = "", $dtxFlg = "") { global $connI, $searchConte, $dbS, $dbS2; $reportName = "Costos por contenedor"; // 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:AK7')->applyFromArray($styleEnc); // Encabezados $headings = array('No', 'Contenedeor', 'Tipo', 'BL', 'Buque', 'Viaje', 'ETA', 'POL', 'POD', 'IE', 'Terr_PP', 'Terr_CC', 'Terr_EE', '20s', '40s', 'SH/CNG', 'BKP', 'I-ONC', 'CUR', 'CH.Applic', 'Payment_Type', 'E-PRE', 'CUR', 'CH.Applic', 'Payment_Type', 'THC', 'CUR', 'CH.Applic', 'Payment_Type', 'Coor.Logistica', 'CUR', 'CH.Applic', 'Payment_Type', 'Pago de Maniobras', 'CUR', 'CH.Applic', 'Payment_Type'); // 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:AK7'); // 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(45); $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(45); $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); $objPHPExcel->getActiveSheet()->getColumnDimension('AA')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('AB')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('AC')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('AD')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('AE')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('AF')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('AG')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('AH')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('AI')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('AJ')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('AK')->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); unset($arrBls); if (!empty($searchConte)) { $arrBls = explode("\r\n", $searchConte); foreach ($arrBls as $cte) { $cte = trim($cte); $in .= "'{$cte}',"; } $in = substr($in, 0, -1); } //echo $in; $sql = "\n select c.numero as contenedor,q.equipo,\n b.Nombre as buque,b.Viaje,b.Eta1 as ETA,b.Impo_expo as IE,\n e.Bl,Shipper,C20,C40,Loading as POL,Discharge as POD,Precarr_pp,Precarr_cc,Precarr_else \n from EXPO e\n INNER JOIN BARCO b ON e.Id_barco = b.Id_barco\n inner join BL_CONTE bc ON e.Id_expo = bc.id_bl\n inner JOIN CONTENEDOR c ON bc.id_contenedor = c.id_contenedor\n INNER JOIN EQUIPO q ON c.id_equipo=q.id_equipo\n where b.Eta1 >= '2015-01-01' and c.numero in ({$in})\n "; $dbS->query($sql); $lin = 1; $rowNumber = 8; // donde inicia los datos while ($dbS->next_record()) { $user = $dbS->f('Nombre'); $bl = $dbS->f('Bl'); $conte = $dbS->f('contenedor'); $sql2 = "select bkg_party from DOC_CTRL_EXPO where bl='{$bl}'"; $dbS2->query($sql2); while ($dbS2->next_record()) { $bkgParty = $dbS2->f('bkg_party'); } // -------------------- // Consultas a MSCLINK // Bl-Contenedor forzoso // -------------------- // $sqlL = " // SELECT // tt.Remarks as Remarks, // t.Bill_Of_Lading_Number as BL, // t.Booking_Number as BKG, // t.Container_Number as CONTE, // t.I_E as IE, // t.Charge as Charge, // t.Result_Std as Result_Std, // t.Payment_Type as Payment_Type, // t.Currency as Currency, // t.Charge_Applicable_At, // t.Charge_DTX_Code as dtxCode // FROM Interlink_Dfeed90. dfeed.FNTB_Charge(0,null) t // inner JOIN Interlink_Main90.dbo.Charge tt ON t.Charge_id = tt.Charge_id // where // t.Container_Number='$conte' // and // t.Bill_Of_Lading_Number like '%$bl'; // "; $sqlL = "\n SELECT \n t.Bill_Of_Lading_Number as BL,\n t.Booking_Number as BKG,\n t.Container_Number as CONTE,\n t.I_E as IE,\n t.Charge as Charge,\n t.Result_Std as Result_Std,\n t.Payment_Type as Payment_Type,\n t.Currency as Currency,\n t.Charge_Applicable_At,\n t.Charge_DTX_Code as dtxCode\n FROM Interlink_Dfeed90. dfeed.FNTB_Charge(0,null) t \n where \n t.Container_Number='{$conte}'\n and \n t.Bill_Of_Lading_Number in ('MSCU{$bl}','MEDU{$bl}'); \n "; $rs = odbc_exec($connI, $sqlL); unset($cl); unset($clCUR); unset($clAP); unset($clPT); unset($mani); unset($maniCUR); unset($maniAP); unset($maniPT); while (odbc_fetch_row($rs)) { $dtxCode = odbc_result($rs, 'dtxCode'); // $Remarks = odbc_result( $rs ,'Remarks'); $Charge = odbc_result($rs, 'Charge'); $Result_Std = odbc_result($rs, 'Result_Std'); $Payment_Type = odbc_result($rs, 'Payment_Type'); $Currency = odbc_result($rs, 'Currency'); $Charge_Applicable_At = odbc_result($rs, 'Charge_Applicable_At'); // $ = odbc_result($rs,''); if ($Charge == 'COORDINACION LOGISTICA') { $cl = $Result_Std; $clCUR = $Currency; $clAP = $Charge_Applicable_At; $clPT = $Payment_Type; } elseif ($Charge == 'PAGO MANIOBRAS') { $mani = $Result_Std; $maniCUR = $Currency; $maniAP = $Charge_Applicable_At; $maniPT = $Payment_Type; } } // -------------------------------------------------------------------------------------------------------------- // OncCarriage // Nota: Se tuvo que realizar otro query por que no se especifica contenedor cuando los cargos son manifestados. // Att. nestor. // -------------------------------------------------------------------------------------------------------------- // $sqlL=" // select // t.Result_Std as Result_Std, // t.Payment_Type as Payment_Type, // t.Currency as Currency, // t.Charge_Applicable_At, // t.Charge_DTX_Code // FROM Interlink_Dfeed90. dfeed.FNTB_Charge(0,null) t // inner JOIN Interlink_Main90.dbo.Charge tt ON t.Charge_id = tt.Charge_id // where // t.Bill_Of_Lading_Number like '%$bl' // "; $sqlL = "\n select\n t.Result_Std as Result_Std,\n t.Payment_Type as Payment_Type,\n t.Currency as Currency,\n t.Charge_Applicable_At,\n t.Charge_DTX_Code\n FROM Interlink_Dfeed90. dfeed.FNTB_Charge(0,null) t \n where \n t.Bill_Of_Lading_Number in ('MSCU{$bl}','MEDU{$bl}')\n "; $rs = odbc_exec($connI, $sqlL); unset($onc); unset($oncCUR); unset($oncAP); unset($oncPT); unset($pre); unset($preCUR); unset($preAP); unset($prePT); while (odbc_fetch_row($rs)) { $dtxCode = odbc_result($rs, 'Charge_DTX_Code'); switch ($dtxCode) { case 'ONC': $onc = odbc_result($rs, 'Result_Std'); $oncCUR = odbc_result($rs, 'Currency'); $oncAP = odbc_result($rs, 'Charge_Applicable_At'); $oncPT = odbc_result($rs, 'Payment_Type'); break; case 'PRE': $pre = odbc_result($rs, 'Result_Std'); $preCUR = odbc_result($rs, 'Currency'); $preAP = odbc_result($rs, 'Charge_Applicable_At'); $prePT = odbc_result($rs, 'Payment_Type'); break; case 'THC': $thc = odbc_result($rs, 'Result_Std'); $thcCUR = odbc_result($rs, 'Currency'); $thcAP = odbc_result($rs, 'Charge_Applicable_At'); $thcPT = odbc_result($rs, 'Payment_Type'); break; default: # code... break; } } // Por manejo de memoria se deben de meter los datos con las menos operaciones posibles. $objPHPExcel->getActiveSheet()->setCellValue('A' . $rowNumber, $lin)->setCellValue('B' . $rowNumber, $dbS->f('contenedor'))->setCellValue('C' . $rowNumber, $dbS->f('equipo'))->setCellValue('D' . $rowNumber, $bl)->setCellValue('E' . $rowNumber, $dbS->f('buque'))->setCellValue('F' . $rowNumber, $dbS->f('Viaje'))->setCellValue('G' . $rowNumber, $dbS->f('ETA'))->setCellValue('H' . $rowNumber, $dbS->f('POL'))->setCellValue('I' . $rowNumber, $dbS->f('POD'))->setCellValue('J' . $rowNumber, $dbS->f('IE'))->setCellValue('K' . $rowNumber, $dbS->f('Precarr_pp'))->setCellValue('L' . $rowNumber, $dbS->f('Precarr_cc'))->setCellValue('M' . $rowNumber, $dbS->f('Precarr_else'))->setCellValue('N' . $rowNumber, $dbS->f('C20'))->setCellValue('O' . $rowNumber, $dbS->f('C40'))->setCellValue('P' . $rowNumber, $dbS->f('Shipper'))->setCellValue('Q' . $rowNumber, $bkgParty)->setCellValue('R' . $rowNumber, $onc)->setCellValue('S' . $rowNumber, $oncCUR)->setCellValue('T' . $rowNumber, $oncAP)->setCellValue('U' . $rowNumber, $oncPT)->setCellValue('V' . $rowNumber, $pre)->setCellValue('W' . $rowNumber, $preCUR)->setCellValue('X' . $rowNumber, $preAP)->setCellValue('Y' . $rowNumber, $prePT)->setCellValue('Z' . $rowNumber, $thc)->setCellValue('AA' . $rowNumber, $thcCUR)->setCellValue('AB' . $rowNumber, $thcAP)->setCellValue('AC' . $rowNumber, $thcPT)->setCellValue('AD' . $rowNumber, $cl)->setCellValue('AE' . $rowNumber, $clCUR)->setCellValue('AF' . $rowNumber, $clAP)->setCellValue('AG' . $rowNumber, $clPT)->setCellValue('AH' . $rowNumber, $mani)->setCellValue('AI' . $rowNumber, $maniCUR)->setCellValue('AJ' . $rowNumber, $maniAP)->setCellValue('AK' . $rowNumber, $maniPT); $rowNumber++; $lin++; } // 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; }
$value = $current_sheet->getCell($adr)->getValue(); if ($c_i == 'B' && empty($value)) { break; } if (is_object($value)) { $value = $value->__toString(); } $c_arr[$c_i] = $value; } $c_arr && ($all_arr[] = $c_arr); } //读取完毕============================================= //准备写入 $msgWorkSheet = new PHPExcel_Worksheet($excel_obj, strval($i)); //创建一个工作表 $excel_obj->addSheet($msgWorkSheet); //插入工作表 $excel_obj->setActiveSheetIndex($i); $act_sheet_obj = $excel_obj->getActiveSheet(); //内容处理,并写入 foreach ($all_arr as $k => $v) { if ($k != 0) { $act_sheet_obj->setCellValue('A' . ($k + 1), $v['A']); $act_sheet_obj->setCellValue('B' . ($k + 1), base_decode($v['B'])); $act_sheet_obj->setCellValue('C' . ($k + 1), $v['C']); $act_sheet_obj->setCellValue('D' . ($k + 1), date('Y-m-d', $v['D'])); $act_sheet_obj->setCellValue('E' . ($k + 1), date('Y-m-d H:i:s', $v['E'])); $act_sheet_obj->setCellValue('F' . ($k + 1), $reg_host[$v['F']]); $act_sheet_obj->setCellValue('G' . ($k + 1), $v['G']); $act_sheet_obj->setCellValue('H' . ($k + 1), $v['H']); $act_sheet_obj->setCellValue('I' . ($k + 1), date('Y-m-d H:i:s', $v['I']));
function reporte($vessel, $voyage, $pol = '', $pod = '') { 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('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. $arrHojas[] = 0; //$arrHojas[]=1; foreach ($arrHojas as $hoja) { $objPHPExcel->setActiveSheetIndex($hoja); // Se aplica estilo al encabezado $objPHPExcel->getActiveSheet()->getStyle('A7:M7')->applyFromArray($styleEnc); // Encabezados $headings = array('BL', 'Vessel', 'Voyage', 'Date', 'Amount', 'CheckNumber', 'PayerName', 'Tracking_Number', 'Received_Via', 'Remarks', 'POL', 'POD', 'Comments'); // 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:M7'); // Auto Ajuste de Ancho en Columna $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30); $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(30); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(30); $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(30); $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(30); $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(100); // Titulo $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Mediterranean Shipping Company Mexico, S.A. de C.V.')->getStyle('A1')->applyFromArray($styleTitulo); // SUBTitulo $objPHPExcel->getActiveSheet()->setCellValue('A2', 'X-Tractor Version 1.0.'); $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleSubtitulo); $objPHPExcel->getActiveSheet()->setCellValue('A3', 'Reporte de cobranza por Barco'); $objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleSubtitulo); $objPHPExcel->getActiveSheet()->setCellValue('A4', ""); $objPHPExcel->getActiveSheet()->getStyle('A4')->applyFromArray($styleSubtitulo); $objPHPExcel->getActiveSheet()->setCellValue('A5', ''); $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); // ---------------------------------------------------------------------------- */ // ----------------------------------- // HOJA 1 // ----------------------------------- $vessel = trim($vessel); $voyage = trim($voyage); $objPHPExcel->setActiveSheetIndex(0); // Simple query $sql = "\n SELECT\n\tBL.Bill_Of_Lading_Number as bl,\n\tBL2.Vessel_Name as vessel,\n\tBL2.Voyage as voyage,\n\tM.DATE as fecha,\n\tM.Amount as amount,\n\tM.Check_Number as checkNum,\n\tM.Payer_Name as payer,\n\tM.Tracking_Number as tnumber,\n\tM.Received_Via as received_via,\n\tM.Remarks as remarks,\n\tBL2.POLUN as polun,\n\tBL2.PODUN as podun\n\n FROM\n Interlink_DFeed90.dfeed.FNTB_Bill_Of_Lading_to_Booking () BL\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_Bill_Of_Lading (NULL, NULL) BL2 ON BL.Bill_Of_Lading_id = BL2.Bill_Of_Lading_id\n LEFT JOIN Interlink_Main90.dbo.BL_Money_Received M (NOLOCK) ON BL.Bill_Of_Lading_id = M.Bill_Of_Lading_id\n -- LEFT JOIN Interlink_Main90.dbo.BL_Comment C (NOLOCK) ON BL.Bill_Of_Lading_id = C.Bill_Of_Lading_id\n WHERE\n BL2.Vessel_Name='{$vessel}'\n AND BL2.Voyage='{$voyage}' "; if (!empty($pod)) { $sql .= "AND BL2.PODUN='{$pod}' "; } if (!empty($pol)) { $sql .= "AND BL2.POLUN='{$pol}' "; } $rs = odbc_exec($conn, $sql); $rowNumber = 8; // donde inicia los datos while (odbc_fetch_row($rs)) { $c1 = odbc_result($rs, "bl"); $c2 = odbc_result($rs, "vessel"); $c3 = odbc_result($rs, "voyage"); $c4 = odbc_result($rs, "fecha"); $c5 = odbc_result($rs, "amount"); $c6 = odbc_result($rs, "checkNum"); $c7 = odbc_result($rs, "payer"); $c8 = odbc_result($rs, "tnumber"); $c9 = odbc_result($rs, "received_via"); $c10 = odbc_result($rs, "remarks"); $pol = odbc_result($rs, "polun"); $pod = odbc_result($rs, "podun"); // Buscar y concatenar los comentarios del BL. $nota = ""; $sql2 = "\n SELECT\n C.Comment as nota\n --,C.CreatedTime\n FROM\n Interlink_DFeed90.dfeed.FNTB_Bill_Of_Lading_to_Booking () BL\n LEFT JOIN Interlink_DFeed90.dfeed.FNTB_Bill_Of_Lading (NULL, NULL) BL2 ON BL.Bill_Of_Lading_id = BL2.Bill_Of_Lading_id\n -- LEFT JOIN Interlink_Main90.dbo.BL_Money_Received M (NOLOCK) ON BL.Bill_Of_Lading_id = M.Bill_Of_Lading_id\n LEFT JOIN Interlink_Main90.dbo.BL_Comment C (NOLOCK) ON BL.Bill_Of_Lading_id = C.Bill_Of_Lading_id\n WHERE\n BL.Bill_Of_Lading_Number = '{$c1}';\n "; $rs2 = odbc_exec($conn2, $sql2); while (odbc_fetch_row($rs2)) { $nota .= odbc_result($rs2, "nota"); } $nota = addslashes($nota); $nota = normaliza($nota); $c7 = normaliza($c7); // Quitar acentos // 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, $pol)->setCellValue('L' . $rowNumber, $pod)->setCellValue('M' . $rowNumber, $nota); $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 = " SELECT CH.Show_as AS CONCEPTO, CH.Start_Event AS EVENTO, CH.start_event_datetime AS EVENTOTIEMPO, CH.End_Event AS ENVENTOFIN, CH.end_event_datetime AS EVENTOFINTIEMPO, CH.POD_name AS PODNAME, CH.Bill_Of_Lading_Number AS BL, CH.Equipment_Number AS CONTE, ADCSH.MSCCode AS TMSCODESHI, ADCCN.Company AS SHIPPER, ADCIC.Company AS INVCOMPANY, CH.Charge_Level_0_Days AS DIASLIBRES, CH.invoice_number AS INVOICE, I.Doc_Date AS DOCDATE, CH.Charge_Amount AS AMOUNTX, (select I.Invoice_Number from Interlink_Accounting90. Accounting .InvoiceState I (NOLOCK ) left JOIN Interlink_Accounting90 .dbo . ACC_Invoice AI ( NOLOCK) ON AI.ACC_Invoice_id = I.ACC_Invoice_id where i.InvoiceState_id=(select MAX(InvoiceState_id) from Interlink_Accounting90.Accounting.InvoiceState (NOLOCK) where Invoice_Number=CH.invoice_number)) as InvNum, (select (AI . Inv_Total_Inv_Currency + AI. Inv_Total_VAT_Inv_Currency) Total from Interlink_Accounting90. Accounting .InvoiceState I (NOLOCK ) left JOIN Interlink_Accounting90 .dbo . ACC_Invoice AI ( NOLOCK) ON AI.ACC_Invoice_id = I.ACC_Invoice_id where i.InvoiceState_id=(select MAX(InvoiceState_id) from Interlink_Accounting90.Accounting.InvoiceState (NOLOCK) where Invoice_Number=CH.invoice_number)) as TotInv, (select I.Paid from Interlink_Accounting90. Accounting .InvoiceState I (NOLOCK ) left JOIN Interlink_Accounting90 .dbo . ACC_Invoice AI ( NOLOCK) ON AI.ACC_Invoice_id = I.ACC_Invoice_id where i.InvoiceState_id=(select MAX(InvoiceState_id) from Interlink_Accounting90.Accounting.InvoiceState (NOLOCK) where Invoice_Number=CH.invoice_number)) as PaidInv, (select ( AI .Inv_Total_Inv_Currency + AI. Inv_Total_VAT_Inv_Currency - I .Paid ) from Interlink_Accounting90. Accounting .InvoiceState I (NOLOCK ) left JOIN Interlink_Accounting90 .dbo . ACC_Invoice AI ( NOLOCK) ON AI.ACC_Invoice_id = I.ACC_Invoice_id where i.InvoiceState_id=(select MAX(InvoiceState_id) from Interlink_Accounting90.Accounting.InvoiceState (NOLOCK) where Invoice_Number=CH.invoice_number )) as OustInv FROM Interlink_DFeed90.dfeed.ILIM_FNTB_Charges () CH INNER JOIN Interlink_Main90.dbo.Invoice I (NOLOCK) ON I.Invoice_id = CH.Invoice_id LEFT JOIN Interlink_Dfeed90.dfeed.FNTB_AssocDocCompany ('CONSIGN') ADCCN ON ADCCN.Bill_Of_Lading_id = CH.bill_of_lading_id LEFT JOIN Interlink_Dfeed90.dfeed.FNTB_AssocDocCompany ('SHIPPER') ADCSH ON ADCSH.Bill_Of_Lading_id = CH.bill_of_lading_id LEFT JOIN Interlink_Dfeed90.dfeed.FNTB_AssocDocCompany ('INVCMPBL') ADCIC ON ADCIC.Bill_Of_Lading_id = CH.bill_of_lading_id WHERE Show_as = 'DEMURRAGE' AND CH.Bill_Of_Lading_Number in ($blsInTxt) AND Charge_Status IN ('INVOICED') "; $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="x-tractor.xlsx"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); exit; }
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>"; }
$objPHPExcel->getProperties()->setTitle("Raumbelegungsplan")->setSubject("Ferienschule")->setDescription("Belegungsplan für die einzelnen Räume"); $objPHPExcel = new PHPExcel(); //$objPHPExcel->setActiveSheetIndex(0); $days = array("Mo", "Di", "Mi", "Do", "Fr"); $slots = array(1, 2, 3); $times = array("9:00-\n10:30", "10:45-\n12:15", "12:30-\n14:00"); $cells = array('A', 'B', 'C', 'D', 'C', 'D', 'E', 'F', 'G'); $sql_rooms = "SELECT * from rooms"; //$rooms = mysqli_query($conn, $sql_rooms) or trigger_error("Query Failed! SQL: $sql - Error: ".mysqli_error('ferienschule'), E_USER_ERROR); $rooms = get_sql_result($sql_rooms); $sheet = 1; while ($room = mysqli_fetch_assoc($rooms)) { //create new worksheet for every room and make it active $objWorksheet = new PHPExcel_Worksheet($objPHPExcel); $objWorksheet->setTitle('' . $room["room_name"]); $objPHPExcel->addSheet($objWorksheet); $objPHPExcel->setActiveSheetIndex($sheet); $sheet++; $rowHeight = 130; $columnWidth = 60; $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20); $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight($rowHeight); $objPHPExcel->getActiveSheet()->getRowDimension('4')->setRowHeight($rowHeight); $objPHPExcel->getActiveSheet()->getRowDimension('5')->setRowHeight($rowHeight); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth($columnWidth); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth($columnWidth); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth($columnWidth); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth($columnWidth); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth($columnWidth); $rowCount = 1; $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $room["room_name"]);
/** * @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); }
$ews->setCellValue('A1', 'RUT'); $ews->setCellValue('B1', 'ROL'); $ews->setCellValue('C1', 'CARATULADO'); $ews->setCellValue('D1', 'FECHA INGRESO'); $ews->setCellValue('E1', 'ESTADO ADMINISTRATIVO'); $ews->setCellValue('F1', 'PROCEDIMIENTO'); $ews->setCellValue('G1', 'UBICACION'); $ews->setCellValue('H1', 'ETAPA'); $ews->setCellValue('I1', 'ESTADO_PROCEDIMIENTO'); $ews->setCellValue('J1', 'TRIBUNAL'); for ($col = ord('a'); $col <= ord('j'); $col++) { $ews->getColumnDimension(chr($col))->setAutoSize(true); } $ews->fromArray($causas, ' ', 'A2'); $ews2 = new \PHPExcel_Worksheet($ea, 'Causa Historia'); $ea->addSheet($ews2, 1); $ews2->setTitle('Causa Historia'); //echo count($causa_hisotoria); $ews2->setCellValue('A1', 'RUT'); $ews2->setCellValue('B1', 'ROL'); $ews2->setCellValue('C1', 'FOLIO'); $ews2->setCellValue('D1', 'ETAPA'); $ews2->setCellValue('E1', 'TRAMITE'); $ews2->setCellValue('F1', 'DESC_TRAMITE'); $ews2->setCellValue('G1', 'FECHA_TRAMITE'); $ews2->setCellValue('H1', 'FOJA'); $ews2->setCellValue('I1', 'DOCUMENTO_LINK'); $ews2->fromArray($causa_hisotoria, ' ', 'A2'); for ($col = ord('a'); $col <= ord('i'); $col++) { $ews2->getColumnDimension(chr($col))->setAutoSize(true); }
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; }
function reporte($fec1, $fec2) { global $connI; $reportName = ""; // 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', 'POL', 'POD'); // 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 = ""; $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)) { $pol = odbc_result($rs, "POLUN"); $pod = odbc_result($rs, "PODUN"); // Por manejo de memoria se deben de meter los datos con las menos operaciones posibles. $objPHPExcel->getActiveSheet()->setCellValue('A' . $rowNumber, $lin)->setCellValue('B' . $rowNumber, $pol)->setCellValue('C' . $rowNumber, $pod)->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); $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="repForCapitania.xlsx"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); exit; }
$recap[$elm->id_produit][3] += $elm->quantite_commande; } } $sheet = new PHPExcel_Worksheet($xls,"commande ".$commande->id_commande); $sheet->fromArray( $grid2, // The data to set NULL, // Array values with this value will not be set 'A1' // Top left coordinate of the worksheet range where we want to set these values (default is A1) ); $sheet->fromArray( $grid, // The data to set NULL, // Array values with this value will not be set 'A4' // Top left coordinate of the worksheet range where we want to set these values (default is A1) ); $xls->addSheet($sheet); $nbre_commandes = $nbre_commandes+1; } $recapsheet->SetCellValue('F1', $nbre_commandes); $recapsheet->fromArray( $recap, // The data to set NULL, // Array values with this value will not be set 'A1' // Top left coordinate of the worksheet range where we want to set these values (default is A1) ); $xlsWriter = new PHPExcel_Writer_Excel2007($xls); $file = tempnam(sys_get_temp_dir(), 'Tux'); $xlsWriter->save($file); header('Content-Description: File Transfer'); header('Content-Type: application/octet-stream'); header('Content-Disposition: attachment; filename=Preparations.xlsx');
/** * @author caochunhui@dachuwang.com * @description 用数组和地址直接生成excel文件 * 每一个数组占一个sheet */ private function _convert_array_to_excel($arr = array(), $sheet_titles = array(), $out_name = '') { //下面的代码是抄的。 //set cache $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp; PHPExcel_Settings::setCacheStorageMethod($cacheMethod); //open excel file $write_objPHPExcel = new PHPExcel(); //下面要循环了 $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); } } $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); }