PHPEXCEL导出复杂模板和填充的使用

我们最近需要对系统加一个报表导出的功能,可以通过POI直接导出,导出后的excel文件需要支持在office里面修改数据后图表也会自动变换。 在phpexcel中我们可以使用模板进行填充,不用对复杂模板进行样式设置,先看看模板吧:

这个一个电商销售SKU的模板,当然了,模板还有表头,表尾,这个只是一部分,我们只需要填充数据,最终生成excel文件即可。

直接上代码吧,表头,表尾略掉,这部分的代码如下:

      //合并边框样式
        $styleArray = array(
            'borders' => array(
                    'bottom'     => array(
                       'style' => \PHPExcel_Style_Border::BORDER_NONE,
                    ),
                    'top'     => array(
                         'style' => \PHPExcel_Style_Border::BORDER_NONE,

                ),
        ));
        $styleArray2 = array(
            'borders' => array(
                'top'     => array(
                    'style' => \PHPExcel_Style_Border::BORDER_NONE,

                ),
            ));
        $styleArray3 = array(
            'borders' => array(
                'bottom'     => array(
                    'style' => \PHPExcel_Style_Border::BORDER_NONE,
                ),
            ));
        $styleArray4 = array(
            'borders' => array(
                'top'     => array(
                    'style' => \PHPExcel_Style_Border::BORDER_NONE,

                ),
                'bottom'     => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,
                ),
            ));

        $styleArray5 = array(
            'borders' => array(
                'top'     => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,

                ),
                'bottom'     => array(
                    'style' => \PHPExcel_Style_Border::BORDER_NONE,
                ),
            ));

        $baseRow = 21; //第21行开始插入
        $line = 0;
        foreach($body as $index=>$dataRow){
            if($index == 0) {
                $line = $index;
                $row= $baseRow +  $index + 1;   //$row是循环操作行的行号
            } else {
                $row= $baseRow + $line  + 1;    //$row是循环操作行的行号
            }
            $objPHPExcel->getActiveSheet()->insertNewRowBefore($row, 3);

            //对应的列都附上数据和编号
            $objPHPExcel->getActiveSheet()->getStyle( 'A'.(22+$line))->applyFromArray($styleArray5);
            $objPHPExcel->getActiveSheet()->getStyle( 'A'.(23+$line))->applyFromArray($styleArray);
            $objPHPExcel->getActiveSheet()->getStyle( 'A'.(24+$line))->applyFromArray($styleArray4);

            $objPHPExcel->getActiveSheet()->setCellValue( 'A'.(22+$line), $dataRow['sort']."\t");
            $objPHPExcel->getActiveSheet()->setCellValue( 'A'.(23+$line), $dataRow['sort']."\t");
            $objPHPExcel->getActiveSheet()->setCellValue( 'A'.(24+$line), "\t"."0");

            $objPHPExcel->getActiveSheet()->mergeCells( 'B'.(22+$line).':'.'H'.(22+$line));
            //行设置样式-商品编码
            $objPHPExcel->getActiveSheet()->getStyle( 'B'.(22+$line).':'.'H'.(22+$line))->applyFromArray($styleArray);
            $objPHPExcel->getActiveSheet()->getStyle( 'B'.(23+$line).':'.'H'.(23+$line))->applyFromArray($styleArray);
            $objPHPExcel->getActiveSheet()->getStyle( 'B'.(24+$line).':'.'H'.(24+$line))->applyFromArray($styleArray2);


            $objPHPExcel->getActiveSheet()->setCellValue( 'B'.(22+$line), "\t".$dataRow['hs_code']);
            $objPHPExcel->getActiveSheet()->mergeCells( 'B'.(23+$line).':'.'H'.(23+$line));
            $objPHPExcel->getActiveSheet()->setCellValue( 'B'.(23+$line), "\t".$dataRow['hs_ciq_code']);

            $objPHPExcel->getActiveSheet()->mergeCells( 'B'.(24+$line).':'.'H'.(24+$line));

            $objPHPExcel->getActiveSheet()->mergeCells( 'I'.(22+$line).':'.'O'.(22+$line));
            $objPHPExcel->getActiveSheet()->setCellValue( 'I'.(22+$line), "\t".$dataRow['goods_name']);

            //合并行列
            $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(8,(23+$line),14,(24+$line));
            $objPHPExcel->getActiveSheet()->getStyle('I'.(23+$line))->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('I'.(23+$line))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //行设置样式-商品名称及规格型号
            $objPHPExcel->getActiveSheet()->getStyle( 'I'.(22+$line).':'.'O'.(22+$line))->applyFromArray($styleArray5);
            $objPHPExcel->getActiveSheet()->getStyle( 'I'.(23+$line).':'.'O'.(23+$line))->applyFromArray($styleArray);
            $objPHPExcel->getActiveSheet()->getStyle( 'I'.(24+$line).':'.'O'.(24+$line))->applyFromArray($styleArray4);

            $objPHPExcel->getActiveSheet()->setCellValue( 'I'.(23+$line), $dataRow['goodBrand']);
            $objPHPExcel->getActiveSheet()->getRowDimension((23+$line))->setRowHeight(40);

            $objPHPExcel->getActiveSheet()->mergeCells( 'P'.(22+$line).':'.'R'.(22+$line));
            //行设置样式-数量及单位
            $objPHPExcel->getActiveSheet()->getStyle( 'P'.(22+$line).':'.'S'.(22+$line))->applyFromArray($styleArray5);
            $objPHPExcel->getActiveSheet()->getStyle( 'P'.(23+$line).':'.'S'.(23+$line))->applyFromArray($styleArray);
            $objPHPExcel->getActiveSheet()->getStyle( 'P'.(24+$line).':'.'S'.(24+$line))->applyFromArray($styleArray4);

            $objPHPExcel->getActiveSheet()->setCellValue( 'P'.(22+$line), $dataRow['num1Total']."\t");

            $objPHPExcel->getActiveSheet()->mergeCells( 'P'.(23+$line).':'.'R'.(23+$line));
            $objPHPExcel->getActiveSheet()->setCellValue( 'P'.(23+$line), "\t".$dataRow['num2Total']);

            $objPHPExcel->getActiveSheet()->mergeCells( 'P'.(24+$line).':'.'R'.(24+$line));
            $objPHPExcel->getActiveSheet()->setCellValue( 'P'.(24+$line), "\t".$dataRow['num']);

            $objPHPExcel->getActiveSheet()->setCellValue( 'S'.(22+$line), "\t".$dataRow['unit1']);
            $objPHPExcel->getActiveSheet()->setCellValue( 'S'.(23+$line), $dataRow['unit2']);
            $objPHPExcel->getActiveSheet()->setCellValue( 'S'.(24+$line), $dataRow['unit']);

            //行设置样式-空行
            $objPHPExcel->getActiveSheet()->getStyle( 'T'.(22+$line).':'.'Z'.(22+$line))->applyFromArray($styleArray5);
            $objPHPExcel->getActiveSheet()->getStyle( 'T'.(23+$line).':'.'Z'.(23+$line))->applyFromArray($styleArray);
            $objPHPExcel->getActiveSheet()->getStyle( 'T'.(24+$line).':'.'Z'.(24+$line))->applyFromArray($styleArray4);

            $objPHPExcel->getActiveSheet()->getStyle('AA'.(22+$line).':'.'AS'.(22+$line))->applyFromArray($styleArray5);
            $objPHPExcel->getActiveSheet()->getStyle('AA'.(23+$line).':'.'AS'.(23+$line))->applyFromArray($styleArray);
            $objPHPExcel->getActiveSheet()->getStyle('AA'.(24+$line).':'.'AS'.(24+$line))->applyFromArray($styleArray4);


            $objPHPExcel->getActiveSheet()->setCellValue( 'AA'.(22+$line), $dataRow['price']."\t");
            $objPHPExcel->getActiveSheet()->setCellValue( 'AA'.(23+$line), "\t".$dataRow['totalPrice']);
            $objPHPExcel->getActiveSheet()->setCellValue( 'AA'.(24+$line), $head['curruncy']."\t");

            //合并行列
            $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(28,(22+$line),32,(24+$line));
            $objPHPExcel->getActiveSheet()->getStyle('AC'.(22+$line))->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('AC'.(22+$line))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //行设置样式-原产国(地区)
            $objPHPExcel->getActiveSheet()->getStyle( 'AC'.(22+$line).':'.'AG'.(22+$line))->applyFromArray($styleArray5);
            $objPHPExcel->getActiveSheet()->getStyle( 'AC'.(23+$line).':'.'AG'.(23+$line))->applyFromArray($styleArray);
            $objPHPExcel->getActiveSheet()->getStyle( 'AC'.(24+$line).':'.'AG'.(24+$line))->applyFromArray($styleArray4);

            $objPHPExcel->getActiveSheet()->setCellValue( 'AC'.(22+$line), "\t".$dataRow['country_origin']);

            //合并行列
            $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(33,(22+$line),38,(24+$line));
            $objPHPExcel->getActiveSheet()->getStyle('AH'.(22+$line))->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('AH'.(22+$line))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //行设置样式-最终目的国(地区)
            $objPHPExcel->getActiveSheet()->getStyle( 'AH'.(22+$line).':'.'AM'.(22+$line))->applyFromArray($styleArray3);
            $objPHPExcel->getActiveSheet()->getStyle( 'AH'.(23+$line).':'.'AM'.(23+$line))->applyFromArray($styleArray);
            $objPHPExcel->getActiveSheet()->getStyle( 'AH'.(24+$line).':'.'AM'.(24+$line))->applyFromArray($styleArray4);

            $objPHPExcel->getActiveSheet()->setCellValue( 'AH'.(22+$line), $dataRow['country_destination']);

            //合并行列
            $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(40,(22+$line),42,(24+$line));

            //行设置样式-境内货源地
            $objPHPExcel->getActiveSheet()->getStyle( 'AO'.(22+$line).':'.'AQ'.(22+$line))->applyFromArray($styleArray3);
            $objPHPExcel->getActiveSheet()->getStyle( 'AO'.(23+$line).':'.'AQ'.(23+$line))->applyFromArray($styleArray);
            $objPHPExcel->getActiveSheet()->getStyle( 'AO'.(24+$line).':'.'AQ'.(24+$line))->applyFromArray($styleArray4);


            $objPHPExcel->getActiveSheet()->getStyle('AO'.(22+$line))->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('AO'.(22+$line))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValue( 'AO'.(22+$line), $dataRow['district_code']);

            //行设置样式-征免
            //合并行列
            $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(44,(22+$line),44,(24+$line));
            $objPHPExcel->getActiveSheet()->getStyle('AS'.(22+$line))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValue( 'AS'.(22+$line), $dataRow['tax_method']);
            $line = $line+3;
        }

最终生成的模板部分如下:

 

  • 2
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
使用PHPExcel导出MySQL数据到Excel,首先需要安装并引入PHPExcel库。 步骤如下: 1. 创建一个新的PHP文件,并包含PHPExcel库的文件: ```php require_once 'PHPExcel/PHPExcel.php'; ``` 2. 连接到MySQL数据库: ```php $servername = "localhost"; $username = "root"; $password = "password"; $dbname = "database"; $conn = new mysqli($servername, $username, $password, $dbname); if ($conn->connect_error) { die("连接失败: " . $conn->connect_error); } ``` 3. 执行查询语句获取数据: ```php $sql = "SELECT * FROM tablename"; $result = $conn->query($sql); ``` 4. 新建一个PHPExcel对象并设置一些属性: ```php $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties() ->setCreator("Your Name") ->setLastModifiedBy("Your Name") ->setTitle("MySQL导出Excel") ->setSubject("MySQL导出Excel") ->setDescription("MySQL数据导出到Excel"); ``` 5. 将数据填充到Excel中: ```php $row = 1; while($row_data = $result->fetch_assoc()) { $col = 0; foreach($row_data as $value) { $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value); $col++; } $row++; } ``` 6. 设置Excel的输出: ```php $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="mysql_export.xls"'); header('Cache-Control: max-age=0'); $objWriter->save('php://output'); ``` 7. 关闭数据库连接: ```php $conn->close(); ``` 以上就是用PHPExcel导出MySQL数据到Excel的简单步骤。根据需要,您可以添加更多的样式和设置来自定义导出的Excel文件。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值