使用PHPExcel导出数据

使用PHPExcel导出数据

封装类

class Excel
{
    protected static $columnStr = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ', 'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ', 'CA', 'CB', 'CC', 'CD', 'CE', 'CF', 'CG', 'CH', 'CI', 'CJ', 'CK', 'CL', 'CM', 'CN', 'CO', 'CP', 'CQ', 'CR', 'CS', 'CT', 'CU', 'CV', 'CW', 'CX', 'CY', 'CZ'];

    /**
     * @name column
     * @desciption 获取坐标
     * @param int $key
     * @param int $columnnum
     * @return string
     */
    protected static function column(int $key, int $columnnum = 1):string
    {
        return self::$columnStr[$key] . $columnnum;
    }

    /**
         * @name userExpressExportExec
         * @desciption              执行导出订单信息
         * @param array $list       数据集
         * @param array $column     字段
         * @param string $fileName  文件名
         * @param int $type         类型[2-XLSX, 3-PDF, 4-CSV, 5-HTML]
         * @throws \PHPExcel_Exception
         * @throws \PHPExcel_Reader_Exception
         * @throws \PHPExcel_Writer_Exception
         */
        public status function userExpressExportExec(array $list, array $column, string $fileName,int $type = 2)
        {
            if (PHP_SAPI == 'cli') die('请从Web浏览器运行!');
            if(Arrays::isEmpty($list)) die('暂无数据');
            // 此处路径换成自己的
            require_once Guide::getPathData()['libsPath'].'Plugins/PhpExcel/PHPExcel.php';
            $excel = new \PHPExcel();
            $excel->getProperties()
                ->setCreator($fileName)
                ->setLastModifiedBy($fileName)
                ->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('report file');
            $sheet  = $excel->setActiveSheetIndex(0);
            $rownum = 1;
            $params = ['title' =>'order-' . date('Y-m-d-H-i', time()), 'columns'=> $column];
            foreach ($params['columns'] as $key => $column) {
                $sheet->setCellValue(self::column($key, $rownum), $column['title']);                                        //设置字段名
                if (isset($column['width'])) $sheet->getColumnDimension(self::$columnStr[$key])->setWidth($column['width']); //设置列宽
            }

            ++$rownum;
            foreach ($list as $row) {
                $len = count($params['columns']);
                $i   = 0;
                while ($i < $len) {
                    if (gettype($row[$params['columns'][$i]['field']]) == 'array') {
                        // TODO 多个商品暂定【】连接符
                        $value = '【' . implode('】【', $row[$params['columns'][$i]['field']]) . '】';
                    } else {
                        $value = $row[$params['columns'][$i]['field']] ?? '';
                    }
                    $sheet->setCellValueExplicit(self::column($i, $rownum), $value, \PHPExcel_Cell_DataType::TYPE_STRING);//写入数据
                    ++$i;
                }
                ++$rownum;
            }

            $excel->getActiveSheet()->setTitle($params['title']);
            $fileName = $fileName.date('YmdHis');
            ob_end_clean();  //关闭输出缓存，避免乱码
            switch ($type) { //导出类型[2-XLSX, 3-PDF, 4-CSV, 5-HTML]
                case 2 :{
                    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                    header('Content-Disposition: attachment;filename='.$fileName.'.xlsx');
                    header('Cache-Control: max-age=0');
                    $objWriter = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
                    break;
                }
                case 3 :{
                    $excel->getActiveSheet()->setShowGridLines(false);
                    header('Content-Type: application/pdf');
                    header('Content-Disposition: attachment;filename='.$fileName.'.pdf');
                    header('Cache-Control: max-age=0');
                    $objWriter = \PHPExcel_IOFactory::createWriter($excel, 'PDF');
                    break;
                }
                case 4 :{
                    header('Content-Type: application/csv');
                    header('Content-Disposition: attachment;filename='.$fileName.'.csv');
                    header('Cache-Control: max-age=0');
                    $objWriter = \PHPExcel_IOFactory::createWriter($excel, 'CSV')
                        ->setDelimiter(',' )    //设置分隔符
                        ->setEnclosure('"' )    //设置包围符
                        ->setLineEnding("\r\n" )//设置行分隔符
                        ->setSheetIndex(0);      //设置活动表
                    break;
                }
                case 5 :{
                    header('Content-Type: application/html');
                    header('Content-Disposition: attachment;filename='.$fileName.'.html');
                    header('Cache-Control: max-age=0');
                    $objWriter = \PHPExcel_IOFactory::createWriter($excel, 'HTML');
                    break;
                }
                default :
                    break;
            }
            $objWriter->save('php://output');
            exit;
        }
    }

调用

$field = [
            //定义导出字段[field-对应数据集下标， title-Excel表格标题， width-Excel列宽]
            ['field' => 'id',                   'title' => '商品ID',      'width' => 15],
            ['field' => 'temp_name',            'title' => '商品名称',     'width' => 30],
            ['field' => 'temp_code',            'title' => '商品编码',     'width' => 30],
            ['field' => 'temp_image',           'title' => '商品封面',     'width' => 50],
            ['field' => 'temp_money_real',      'title' => '',   'width' => 15],
            ['field' => 'temp_money',           'title' => '',   'width' => 15],
            ['field' => 'temp_ext_money',       'title' => '',   'width' => 15],
            ['field' => 'temp_weight',          'title' => '',        'width' => 10],
            ['field' => 'temp_isvirtual',       'title' => '', 'width' => 15],
            ['field' => 'temp_desc',            'title' => '',    'width' => 15],
            ['field' => 'temp_first_time',      'title' => '',    'width' => 30],
            ['field' => 'temp_end_time',        'title' => '',    'width' => 30],
        ];
Excel::userExpressExportExec($list, $field, '商品目录', 2);