PHPExcel data export instance

PHPExcel data export instance

1. getdata. php Namespace Webadmin \ Model; Use ExtendSpace \ Excel; ...... Omitted ...... // Retrieve data $ DataBillArr = $ this-> get_list_bysql ($ SQL ); // Replace 0 and 1 with Yes and No // PHPExcel has a built-in method for processing, but the result is TRUE/FALSE. let's handle it by yourself. $ This-> _ formatZero ($ dataBillArr, array ('taxflag', 'payflag', 'removeflag ')); // Replace the payment status Foreach ($ dataBillArr as $ key => $ value ){ Switch ($ value ['statustype ']) { Case '-1 ': $ DataBillArr [$ key] ['statustype '] = 'canceled '; Break; Case '-2 ': $ DataBillArr [$ key] ['statustype '] = 'canceled refunded '; Break; Case '0 ': $ DataBillArr [$ key] ['statustype '] = 'awaiting payment '; Break; Case '1 ': $ DataBillArr [$ key] ['statustype '] = 'awaiting shipping '; Break; Case '2 ': $ DataBillArr [$ key] ['statustype '] = 'waiting for shipping '; Break; Case '3 ': $ DataBillArr [$ key] ['statustype '] = 'finished '; Break; Case '10 ': $ DataBillArr [$ key] ['statustype '] = 'return finished '; Break; Case '11 ': $ DataBillArr [$ key] ['statustype '] = 'refund completed '; Break; Default: $ DataBillArr [$ key] ['statustype '] = 'none '; Break; } } // Set the field to be exported and the corresponding header name $ Header = array ( Array ('title' => 'platform order no. ', 'field' => 'billcode', 'type' => 'string', 'autosize' => true ), Array ('title' => 'User account', 'field' => 'username', 'type' => 'string', 'autosize' => true ), Array ('title' => 'User nick name', 'field' => 'nickname '), Array ('title' => 'Merchant ', 'field' => 'shopuser', 'autosize' => true ), Array ('title' => 'ease of ERP ticket no. ', 'field' => 'errorn', 'type' => 'string', 'autosize' => true ), Array ('title' => 'payment order no. ', 'field' => 'babaycode', 'type' => 'string', 'autosize' => true ), Array ('title' => 'bonded batch no. ', 'field' => 'bsbatchcode', 'type' => 'string', 'autosize' => true ), Array ('title' => 'cross-border ', 'field' => 'taxflag '), Array ('title' => 'Order status', 'field' => 'statustype '), ...... Omitted ...... ); // Call the interface to generate and export an Excel file $ Filename = 'Order streaming water meter _ '. date ('y, m, d, _ His', time ()); Excel: export ($ dataBillArr, $ header, $ filename ); II. Excel. class. php Namespace ExtendSpace; /** * Class Excel general Excel interface for export and export operations * Instructions: * 1. import * To be continued... * 2. export * Use ExtendSpace \ Excel; *..... * Excel: export ($ dataArr, $ header, $ filename ); * * @ Package ExtendSpace * @ Author xxxxx 2015-08-27 14:07:14 * @ Version */ Class Excel { // Private static $ objPHPExcel = null; /** * Entry File: export Excel * @ Return */ Public static function export ($ data, $ header, $ filename = 'HMS _ excel_export '){ // Introduce the PHPExcel class library Import ('phpexcel. phpexcel ', dirname (_ FILE _).'/','. php'); // This is unique to TP and can be directly used with include or require // Initialization settings $ ObjPHPExcel = new \ PHPExcel (); $ ObjPHPExcel-> getProperties ()-> setCreator ('test')-> setLastModifiedBy ('test'); // Set Chinese garbled characters here, which has not been resolved //-> SetTitle ('This is the title ') //-> SetSubject ('What is this ') //-> SetDescription ('this is description ') //-> SetKeywords ('This is the keyword ') //-> SetCategory ('Is this a directory '); // Var_dump ($ objPHPExcel-> getProperties (); exit; // Obtain the current activity worksheet $ ObjActSheet = $ objPHPExcel-> getActiveSheet (); // Write the header Foreach ($ header as $ k => $ v ){ $ ColIndex = self: _ getHeaderIndex ($ k ); $ ObjPHPExcel-> setActiveSheetIndex (0)-> setCellValue ($ colIndex. '1', $ v ['title']); // Whether the column needs to automatically adapt to the width If (! Empty ($ v ['autosize']) { $ ObjActSheet-> getColumnDimension ($ colIndex)-> setAutoSize (true ); } } // Write data. starting from the second row, the first row is the header. $ RowNum = 2; Foreach ($ data as $ rows) {// traverses data and obtains a row Foreach ($ header as $ kk => $ vv) {// write a cell $ ColIndex = self: _ getHeaderIndex ($ kk ); // Specify the cell data format If (! Empty ($ vv ['type']) {// Yes Switch ($ vv ['type']) { Case 'number ': $ Type = \ PHPExcel_Cell_DataType: TYPE_NUMERIC; // number Break; Case 'boolean ': $ Type = \ PHPExcel_Cell_DataType: TYPE_BOOL; // boolean value, 0-> FALSE; 1-> TRUE Break; Default: $ Type = \ PHPExcel_Cell_DataType: TYPE_STRING; // string Break; } $ ObjActSheet-> setCellValueExplicit ($ colIndex. $ rowNum, $ rows [$ vv ['field'], $ type ); } Else {// No, general by default $ ObjActSheet-> setCellValue ($ colIndex. $ rowNum, $ rows [$ vv ['field']); } } $ RowNum ++; } // Set the row height rownum // $ ObjPHPExcel-> getActiveSheet ()-> getRowDimension ('1')-> setRowHeight (22 ); // Set the font and style $ ObjActSheet-> getDefaultStyle ()-> getFont ()-> setSize (12); // The overall font size. $ ObjActSheet-> getStyle ('A1 :'. self: _ getHeaderIndex (count ($ header )). '1')-> getFont ()-> setBold (true); // bold the column title // Set the worksheet name $ ObjActSheet-> setTitle ('sheet1 '); // Set header parameters // Header ("Pragma: public "); // Header ("Expires: 0 "); // Header ("Cache-Control: must-revalidate, post-check = 0, pre-check = 0 "); // Header ("Content-Type: application/force-download "); // Header ("Content-Type: application/vnd. ms-execl "); // Header ("Content-Type: application/octet-stream "); // Header ("Content-Type: application/download ");; // Header ('content-Disposition: attachment; filename = "'. $ savedFileName .'"'); // Header ("Content-Transfer-Encoding: binary "); // Final output $ SavedFileName = self: _ iconv ($ filename). '.xls '; // export file name + extension Header ('content-Type: application/vnd. ms-excel '); Header ('content-Disposition: attachment; filename = "'. $ savedFileName .'"'); Header ('cache-Control: max-age = 0 '); $ ObjWriter = \ PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel5 '); $ ObjWriter-> save ('php: // output '); // $ ObjWriter-> save ($ savedFileName ); } /** * Entry File: import an Excel file * @ Return */ Public static function import (){ // Introduce the PHPExcel class library // Import ('phpexcel. phpexcel ', dirname (_ FILE _).'/','. php '); } Private static function _ init (){ } /** * Obtain the index value of the table header, that is, A, B, C..., greater * @ Param array $ header: array used to set the header * @ Return string */ Private function _ getHeaderIndex ($ num ){ Return \ PHPExcel_Cell: stringFromColumnIndex ($ num ); } /** * Character conversion to avoid garbled characters * @ Param string $ str characters to be processed * @ Return string */ Private function _ iconv ($ str ){ Return iconv ('utf-8', 'gb2312 ', $ str ); } }

PHPExcel