PHPExcel data export example

1. getdata.php

namespace WebadminModel;

use ExtendSpaceExcel;

 …
 slightly
 …

 // Get data
 $dataBillArr = $this->get_list_bysql($sql);

 // Replace 0 and 1 in the data with yes and no
 // PHPExcel has a built-in method for processing, but the result is TRUE/FALSE, handle it yourself here
 $this->_formatZero($dataBillArr, array(' taxflag', 'payflag', 'removeflag'));

 // Replace payment status
 foreach ($dataBillArr as $key => $value) {
 switch ($value['statustype']) {
 case ' -1':
 $dataBillArr[$key]['statustype'] = 'Cancelled';
 break;
 case '-2':
 $dataBillArr[$key]['statustype'] = 'Cancelled and returned payment';
 break;
 case '0':
 $dataBillArr[$key]['statustype'] = 'Pending payment';
 break;
 case '1':
 $dataBillArr[$key]['statustype' ] = 'To be shipped';
 break;
 case '2':
 $dataBillArr[$key]['statustype'] = 'To be received';
 break;
 case '3':
 $dataBillArr[$ key]['statustype'] = 'Completed';
 break;
 case '10':
 $dataBillArr[$key]['statustype'] = 'Return completed';
 break;
 case '11':
 $dataBillArr[$key]['statustype'] = 'Refund completed';
 break;
 default:
 $dataBillArr[$key]['statustype'] = 'None';
 break;
 }
 }

 //Set the fields to be exported and the corresponding header names
 $header = array(
 array('title'=>'Platform order number', 'field'=>'billcode', 'type'=> 'string', 'autosize'=>true),
 array('title'=>'User account', 'field'=>'username', 'type'=>'string', 'autosize' =>true),
 array('title'=>'User Nickname', 'field'=>'nickname'),
 array('title'=>'Merchant', 'field'=> ;'shopuser', 'autosize'=>true),
 array('title'=>'Guanyi ERP order number', 'field'=>'erpsn', 'type'=>'string' , 'autosize'=>true),
 array('title'=>'Payment number', 'field'=>'bspaycode', 'type'=>'string', 'autosize'=> ;true),
 array('title'=>'Bonded batch number', 'field'=>'bsbatchcode', 'type'=>'string', 'autosize'=>true),
 array('title'=>'Is it cross-border', 'field'=>'taxflag'),
 array('title'=>'Order status', 'field'=>'statustype'),
 ……
 omitted
 ……
 );

 // Call the interface to perform Excel generation and export operations
 $filename = 'Order flow table_' . date('Y year m month d day_His', time( ));
 Excel::export($dataBillArr, $header, $filename);

二、Excel.class.php

namespace ExtendSpace;

/**
 * Class Excel universal Excel interface, handles export and export operations
 * Instructions for use:
 * 1. Import
 * To be continued. . .
 * 2. Export
 * use ExtendSpaceExcel;
 * .....
 * 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 TP-specific and can be used directly include or require

 // Initial settings
 $objPHPExcel = new PHPExcel();
 $objPHPExcel->getProperties()->setCreator('test')->setLastModifiedBy('test'); // Set here Chinese garbled characters, not solved yet
 // ->setTitle('This is the title')
 // ->setSubject('What is this')
 // ->setDescription('This is the description')
 / / ->setKeywords('This is a keyword')
 // ->setCategory('Is this a directory');
 // var_dump($objPHPExcel->getProperties());exit;

 // Get the active worksheet currently being operated on
 $objActSheet = $objPHPExcel->getActiveSheet();

 // Write the table 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 line , the first row is the header
 $rowNum = 2;
 foreach($data as $rows){ // Traverse the data and get a row
 foreach($header as $kk => $vv){ // Cell writing Enter
 $colIndex = self::_getHeaderIndex($kk);

 // Whether to 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, default normal
 $objActSheet->setCellValue($colIndex.$rowNum, $rows[$vv['field']]);
 }
 }
 $rowNum++;
 }

 // Set row height rownum
 // $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(22);

 // Set font And style
 $objActSheet->getDefaultStyle()->getFont()->setSize(12); // Overall font size
 $objActSheet->getStyle('A1:' . self::_getHeaderIndex(count($ header)) . '1')->getFont()->setBold(true); // Make the column header bold

 // Set the worksheet name
 $objActSheet->setTitle('Sheet1');

 // Set header 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 Excel
 * @return
*/
    public static function import() {

        // 引入 PHPExcel 类库
        // import('phpexcel.PHPExcel', dirname(__FILE__) . '/', '.php');

    }

    private static function _init() {

    }

    /**
 * Get the header index value, that is: A, B, C..., greater than
 * @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