Export an excel table using PHPExcel and export an excel file using phpexcel

Export an excel table using PHPExcel and export an excel file using phpexcel

If (! Function_exists ('exportexcel ')){
// Export an excel file
Function exportExcel ($ datas, $ save_method = 'http', $ save_path = ''){
Set_time_limit (0 );
Error_reporting (E_ALL );
Ini_set ('display _ errors ', TRUE );
Ini_set ('display _ startup_errors ', TRUE );
$ Excel_title = 'box details list'. date ('ymdhis ', time ());
$ Code = $ datas ['code'];
$ TotalBox = $ datas ['totalbox'];
$ TotalPackages = $ datas ['totalpackages '];
$ TotalWeight = $ datas ['totalwiight '];
$ Data = $ datas ['data'];
Require_once ROOT_PATH. '/Classes/PHPExcel. php ';
$ ObjPHPExcel = new PHPExcel ();
$ ObjPHPExcel-> getProperties ()
-> SetCreator ("Maarten Balliauw ")
-> SetLastModifiedBy ("Maarten Balliauw ")
-> SetTitle ("Office 2007 XLSX Test Document ")
-> SetSubject ("Office 2007 XLSX Test Document ")
-> SetDescription ("Test document for Office 2007 XLSX, generated using PHP classes .")
-> SetKeywords ("office 2007 openxml php ")
-> SetCategory ("Test result file ");
$ Row = array ('A', 'B', 'C', 'D', 'E', 'E', 'F', 'G', 'H ');
// Set the column width
$ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('A')-> setWidth (12 );
$ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('B')-> setWidth (23 );
$ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('C')-> setWidth (10 );
$ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('D')-> setWidth (18 );
$ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('E')-> setWidth (22 );
$ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('F')-> setWidth (23 );
$ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('G')-> setWidth (12 );
$ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('H')-> setWidth (15 );
$ StyleThinBlackBorderOutline = array (
'Borders' => array (
'Outline' => array (
'Style' => PHPExcel_Style_Border: BORDER_THIN,
'Color' => array ('arg' => 'ff000000 '),),
),
);
For ($ I = 0; $ I <count ($ row); $ I ++ ){
// Set the border
For ($ a = 1; $ a <4; $ a ++ ){
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ($ row [$ I]. $ a)-> applyFromArray ($ styleThinBlackBorderOutline );
}
// Set Center
For ($ j = 1; $ j <500; $ j ++ ){
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ($ row [$ I]. $ j)-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: VERTICAL_CENTER );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ($ row [$ I]. $ j)-> getAlignment ()-> setVertical (PHPExcel_Style_Alignment: VERTICAL_CENTER );
}
// Set the background color
$ ObjPHPExcel-> getActiveSheet (0)-> getStyle ($ row [$ I]. '1')-> getFill ()-> setFillType (\ PHPExcel_Style_Fill: FILL_SOLID );
$ ObjPHPExcel-> getActiveSheet (0)-> getStyle ($ row [$ I]. '1')-> getFill ()-> getStartColor ()-> setARGB ('00ffff00 ');
$ ObjPHPExcel-> getActiveSheet (0)-> getStyle ($ row [$ I]. '2 :'. $ row [$ I]. '3')-> getFill ()-> setFillType (\ PHPExcel_Style_Fill: FILL_SOLID );
$ ObjPHPExcel-> getActiveSheet (0)-> getStyle ($ row [$ I]. '2 :'. $ row [$ I]. '3')-> getFill ()-> getStartColor ()-> setARGB ('00dbe5f1 ');
// Set line feed
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ($ row [$ I]. '2')-> getAlignment ()-> setWrapText (true );
}
// Set the Row Height
$ ObjPHPExcel-> getActiveSheet ()-> getDefaultRowDimension ()-> setRowHeight (28 );
$ ObjPHPExcel-> getActiveSheet ()-> getRowDimension ('2')-> setRowHeight (66 );
// Define the first three rows of the table
$ ObjPHPExcel-> getActiveSheet ()
-> MergeCells ('a1: B1 ')
-> MergeCells ('c1: D1 ')
-> MergeCells ('e1: f1 ')
-> MergeCells ('g1: H1 ')
-> MergeCells ('b2: b3 ')
-> MergeCells ('c2: c3 ')
-> MergeCells ('h2: H3 ')
-> MergeCells ('g2: g3 ');
$ ObjPHPExcel-> setActiveSheetIndex (0)
-> SetCellValue ('a1', 'customer Code :')
-> SetCellValue ('c1 ', $ code)
-> SetCellValue ('e1 ', 'date :')
-> SetCellValue ('g1 ', date ('Y-m-d H: I: s', time ()))
-> SetCellValue ('a2 ', 'Total number of boxes':'. $ totalBox. "\ n". 'total box ')
-> SetCellValue ('a3 ', 'box No ')
-> SetCellValue ('b2', 'transfer Ticket No. '. "\ n". 'tracking number ')
-> SetCellValue ('c2 ', 'order No ')
-> SetCellValue ('d2 ',' Total number of Packages: '. $ totalPackages. "\ n". 'total Packages ')
-> SetCellValue ('d3 ', 'package weight (g )')
-> SetCellValue ('E2', 'total bin Weight (kg): '. $ totalWeight. "\ n". 'total weight ')
-> SetCellValue ('e3 ', 'box weight (kg )')
-> SetCellValue ('F2', 'total box volume weigh '. "\ n".' (length * width * Height/5000 )')
-> SetCellValue ('f3 ', 'box volume (length * width * Height cm )')
-> SetCellValue ('g2 ', 'header'. "\ n". 'shipping method ')
-> SetCellValue ('h2 ', 'tracking No.'. "\ n". 'tracking number ');
// Process the subsequent Logic
// The number of current rows
$ Num = 4;
Foreach ($ data as $ k => $ v ){
// Number of specific orders
$ Len = count ($ v );
$ MergeLen = $ num + $ len-4;
$ ObjPHPExcel-> getActiveSheet ()
-> MergeCells ('E'. $ num. ': e'. $ mergeLen)
-> MergeCells ('F'. $ num. ': f'. $ mergeLen)
-> MergeCells ('G'. $ num. ': G'. $ mergeLen)
-> MergeCells ('H'. $ num. ': H'. $ mergeLen );
$ ObjPHPExcel-> setActiveSheetIndex ()
-> SetCellValue ('A'. $ num, $ v ['boxid'])
-> SetCellValue ('E'. $ num, $ v ['weights'])
-> SetCellValue ('G'. $ num, $ v ['carrier _ company ']);
For ($ I = 0; $ I <$ len-3; $ I ++ ){
$ ObjPHPExcel-> setActiveSheetIndex ()
-> SetCellValue ('A'. $ num, $ v ['boxid'])
-> SetCellValueExplicit ('B'. $ num, $ v [$ I] ['tracknumber'], PHPExcel_Cell_DataType: TYPE_STRING)
-> SetCellValue ('C'. $ num, $ v [$ I] ['ebay _ id'])
-> SetCellValue ('D'. $ num, $ v [$ I] ['weight']);
$ Num ++;
}
}
$ ObjPHPExcel-> getActiveSheet ()-> setTitle ($ excel_title );
$ ObjPHPExcel-> setActiveSheetIndex (0 );
$ Filename = paiexcel_title.'.xls ';
If ($ save_method = 'file '){
// Save to file
$ ObjWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel5 ');
$ Full_path = $ save_path. $ filename;
// Local use, online comments
// $ Full_path = iconv ('utf-8', 'gbk // IGNORE ', $ full_path );
If (is_file ($ full_path) unlink ($ full_path );
$ ObjWriter-> save ($ full_path );
Return $ full_path;
} Else {
Ob_end_clean ();
// Output directly in the browser
Header ('content-Type: application/vnd. ms-excel ');
Header ("Content-Disposition: attachment; filename = {$ filename }");
Header ('cache-Control: max-age = 0 ');
$ ObjWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel5 ');
$ ObjWriter-> save ('php: // output ');
}
}
}