How to customize Excel's export and merge cells with PHP-PHP Tutorial-php.cn

How to customize Excel's export and merge cells with PHP

零到壹度

Release： 2023-03-21 20:08:01

Original

4722 people have browsed it

This time I will talk to you about how to use PHP to customize Excel's export and merge cells. The following is a practical case, let's take a look.

First of all, customize the export. I use a plug-in for a drop-down multi-select box, which can be found on Baidu. In order to make the style look good. As shown in the figure

value corresponds to the field value found in your database, and text corresponds to your header information. Ok, then I pass these two values to our controller through GET.

Introducing exported classes, I won’t go into details about this.

Then we query the database, process the data into a two-dimensional array, loop through it and output it in the table

My data format is a 1-to-many relationship, one class teacher corresponds to multiple classes , then I want to merge this class teacher in the table. $count is the statistics of the class. When the number of classes corresponding to the class teacher

is >1, it will be merged.

            $str=$_GET[&#39;str&#39;];//勾选
            $str2=$_GET[&#39;str2&#39;];//表头
            $td_field=explode(&#39;,&#39;, $str2);//表头
            $field=explode(&#39;,&#39;, $str);//勾选
            $objPHPExcel=new \PHPExcel();

            $objPHPExcel->getProperties()->setCreator(&#39;http://www.jb51.NET&#39;)
                ->setLastModifiedBy(&#39;http://www.jb51.Net&#39;)
                ->setTitle(&#39;Office 2007 XLSX Document&#39;)
                ->setSubject(&#39;Office 2007 XLSX Document&#39;)
                ->setDescription(&#39;Document for Office 2007 XLSX, generated using PHP classes.&#39;)
                ->setKeywords(&#39;office 2007 openxml php&#39;)
                ->setCategory(&#39;Result file&#39;);
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue(&#39;A1&#39;,$td_field[0])
                ->setCellValue(&#39;B1&#39;,$td_field[1])
                ->setCellValue(&#39;C1&#39;,$td_field[2])
                ->setCellValue(&#39;D1&#39;,$td_field[3])
                ->setCellValue(&#39;E1&#39;,$td_field[4])
                ->setCellValue(&#39;F1&#39;,$td_field[5])
                ->setCellValue(&#39;G1&#39;,$td_field[6])
                ->setCellValue(&#39;H1&#39;,$td_field[7])
                ->setCellValue(&#39;I1&#39;,$td_field[8])
                ->setCellValue(&#39;J1&#39;,$td_field[9])
                ->setCellValue(&#39;K1&#39;,$td_field[10])
                ->setCellValue(&#39;L1&#39;,$td_field[11])
                ->setCellValue(&#39;M1&#39;,$td_field[12]);
            $i=2;
            
            //->mergeCells(&#39;A18:E22&#39;)合并单元格；->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER) 垂直居中
            foreach($new_array as $k=>$v){
                if($v["count"] > 1){

                    $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue(&#39;A&#39;.$i,$v["$field[0]"])
                    ->setCellValue(&#39;B&#39;.$i,$v["$field[1]"])
                    ->setCellValue(&#39;C&#39;.$i,$v["$field[2]"])
                    ->setCellValue(&#39;D&#39;.$i,$v["$field[3]"])
                    ->setCellValue(&#39;E&#39;.$i,$v["$field[4]"])
                    ->setCellValue(&#39;F&#39;.$i,$v["$field[5]"])
                    ->setCellValue(&#39;G&#39;.$i,$v["$field[6]"])
                    ->setCellValue(&#39;H&#39;.$i,$v["$field[7]"])
                    ->setCellValue(&#39;I&#39;.$i,$v["$field[8]"])
                    ->setCellValue(&#39;J&#39;.$i,$v["$field[9]"])
                    ->setCellValue(&#39;K&#39;.$i,$v["$field[10]"])
                    ->setCellValue(&#39;L&#39;.$i,$v["$field[11]"])
                    ->setCellValue(&#39;M&#39;.$i,$v["$field[12]"])
                    ->mergeCells(&#39;A&#39;.$i.&#39;:A&#39;.($i+$v["count"]-1))
                    ->mergeCells(&#39;B&#39;.$i.&#39;:B&#39;.($i+$v["count"]-1))
                    ->mergeCells(&#39;C&#39;.$i.&#39;:C&#39;.($i+$v["count"]-1))
                    ->mergeCells(&#39;D&#39;.$i.&#39;:D&#39;.($i+$v["count"]-1))
                    ->mergeCells(&#39;E&#39;.$i.&#39;:E&#39;.($i+$v["count"]-1))
                    ->mergeCells(&#39;F&#39;.$i.&#39;:F&#39;.($i+$v["count"]-1))
                    ->mergeCells(&#39;G&#39;.$i.&#39;:G&#39;.($i+$v["count"]-1))
                    ->mergeCells(&#39;H&#39;.$i.&#39;:H&#39;.($i+$v["count"]-1))
                    ->mergeCells(&#39;I&#39;.$i.&#39;:I&#39;.($i+$v["count"]-1));
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle(&#39;A&#39;.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle(&#39;B&#39;.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle(&#39;C&#39;.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle(&#39;D&#39;.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle(&#39;E&#39;.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle(&#39;F&#39;.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle(&#39;G&#39;.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle(&#39;H&#39;.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                    $objPHPExcel->setActiveSheetIndex(0)->getStyle(&#39;I&#39;.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                }else{
                    $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue(&#39;A&#39;.$i,$v["$field[0]"])
                    ->setCellValue(&#39;B&#39;.$i,$v["$field[1]"])
                    ->setCellValue(&#39;C&#39;.$i,$v["$field[2]"])
                    ->setCellValue(&#39;D&#39;.$i,$v["$field[3]"])
                    ->setCellValue(&#39;E&#39;.$i,$v["$field[4]"])
                    ->setCellValue(&#39;F&#39;.$i,$v["$field[5]"])
                    ->setCellValue(&#39;G&#39;.$i,$v["$field[6]"])
                    ->setCellValue(&#39;H&#39;.$i,$v["$field[7]"])
                    ->setCellValue(&#39;I&#39;.$i,$v["$field[8]"])
                    ->setCellValue(&#39;J&#39;.$i,$v["$field[9]"])
                    ->setCellValue(&#39;K&#39;.$i,$v["$field[10]"])
                    ->setCellValue(&#39;L&#39;.$i,$v["$field[11]"])
                    ->setCellValue(&#39;M&#39;.$i,$v["$field[12]"]);
                }
                $i++;
            }

            $objPHPExcel->getActiveSheet()->setTitle(&#39;日报&#39;);
            $objPHPExcel->setActiveSheetIndex(0);
            //$filename=urlencode(&#39;数据表&#39;).&#39;_&#39;.date(&#39;Y-m-dHis&#39;);
            $filename=&#39;日报&#39;.&#39;_&#39;.date(&#39;Y-m-dHis&#39;);

       

            //生成xls文件
            header(&#39;Content-Type: application/vnd.ms-excel&#39;);
            header(&#39;Content-Disposition: attachment;filename="&#39;.$filename.&#39;.xls"&#39;);
            header(&#39;Cache-Control: max-age=0&#39;);
            $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, &#39;Excel5&#39;);
            $objWriter->save(&#39;php://output&#39;);
            exit;

Copy after login

The above is the detailed content of How to customize Excel's export and merge cells with PHP. For more information, please follow other related articles on the PHP Chinese website!