phpExcel类库实现

电玩女神 2022-02-03 05:11 143阅读 0赞

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;

PHPexcel 在我的博客也有下载