phpExcel类库实现

原创

大宝天天用 2022-11-25 12:54:06 博主文章分类：PHP ©著作权

©著作权归作者所有：来自51CTO博客作者大宝天天用的原创作品，请联系作者获取转载授权，否则将追究法律责任

public  function export($fields,$data,$filename,$widths = array()){
        $this->load->library('PHPExcel');
        $this->load->library('PHPExcel/IOFactory');
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()->setTitle("export")->setDescription("none");
        $cellNum = count($fields);
        $dataNum = count($data);
        $cellName = array('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');
        /*$objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', 'fdsf')
        ->setCellValue('B2', 'world!')
        ->setCellValue('C1', 'Hello');*/
        // Field names in the first row
        $letters = array('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');
        foreach($letters as $letter){
            $objPHPExcel->getActiveSheet()->getColumnDimension($letter)->setWidth(isset($widths[$letter])?$widths[$letter]:20);
        }

        for($i=0;$i<$cellNum;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'1', $fields[$i][1]);
        }
        // Miscellaneous glyphs, UTF-8
        for($i=0;$i<$dataNum;$i++){
            for($j=0;$j<$cellNum;$j++){
                $objPHPExcel->getActiveSheet(0)->setCellValueExplicit($cellName[$j].($i+2), $data[$i][$fields[$j][0]],\PHPExcel_Cell_DataType::TYPE_STRING);
            }
        }
        /*$col = 0;
        foreach ($fields as $field)
        {
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field);
            $col++;
        }

        // Fetching the table data
        $row = 2;
        foreach($data as $item)
        {
            $col = 0;
            foreach ($fields as $key=>$field)
            {
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $item[$key]);
                //$objPHPExcel->getActiveSheet(0)->setCellValueExplicit($cellName[$j].($i+2), $expTableData[$i][$expCellName[$j][0]],\PHPExcel_Cell_DataType::TYPE_STRING);
                $col++;
            }
            $row++;
        }*/

        //$objPHPExcel->setActiveSheetIndex(0);

        $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');

        //发送标题强制用户下载文件
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$filename.date('Ymd').'.xls"');
        header('Cache-Control: max-age=0');

        $objWriter->save('php://output');
    }

实现方法:

$this->load->model('exportxls');
        $fields = [
            ['id','订单ID'], //A
            ['obj_id','游记ID'], //B
            ['total_money','付款金额'], //C
            ['from_user_id','付款方ID'], //D
            ['username','付款方'], //Exxxx
            ['pay_type','付款方式'], //F
            ['to_user_id','收款方ID'], //G
            ['to_user','收款方'], //H
            ['pay_time','支付时间'], //I
            ['name','订单描述'], //J
            ['create_time','订单创建时间'], //K
        ];
        $this->exportxls->export(
            $fields,  //字段数组
            $data,  //数据库查询数据结果集(二位数组)
            'xxxx', //文件名称
            array('A'=>10,'B'=>10,'C'=>10,'D'=>10,'E'=>20,'F'=>10,'G'=>10,'H'=>20,'I'=>20,'J'=>30,'K'=>20) //自定义表格宽度
        );exit;