TP5 references PHPExcel to implement import and export functions-ThinkPHP-php.cn

TP5 references PHPExcel to implement import and export functions

藏色散人

Release： 2019-12-25 13:44:05

forward

3000 people have browsed it

Download PHPExcel from php Chinese website

Place the downloaded PHPExcel folder and PHPExcel in the Vendor directory in the framework, pay attention to the structure of the directory , according to my code, you can basically directly quote it using

TP5 references PHPExcel to implement import and export functions

Front-end code

<html>
 <head></head>
 <body>
  <div class="panel-heading">
    Advanced Tables 
   <a href="/daochu" class="btn-succes">导出</a> 
   <form action="/daoru" method="post" enctype="multipart/form-data"> 
    <input name="upload[]" type="file" /> 
    <input type="submit" /> 
   </form> 
  </div>
 </body>
</html>

Copy after login

Export the code of the function controller

$list = Db::table(&#39;product&#39;)->select();
        vendor("PHPExcel176.PHPExcel");
        $objPHPExcel = new \PHPExcel();
        $objPHPExcel->getProperties()->setCreator("ctos")
            ->setLastModifiedBy("ctos")
            ->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("Test result file");
        $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;A&#39;)->setWidth(8);
        $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;B&#39;)->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;C&#39;)->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;D&#39;)->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;E&#39;)->setWidth(50);
        //设置行高度
        $objPHPExcel->getActiveSheet()->getRowDimension(&#39;1&#39;)->setRowHeight(22);
        $objPHPExcel->getActiveSheet()->getRowDimension(&#39;2&#39;)->setRowHeight(20);
        //set font size bold
        $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);
        $objPHPExcel->getActiveSheet()->getStyle(&#39;A2:E2&#39;)->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle(&#39;A2:E2&#39;)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle(&#39;A2:E2&#39;)->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
        //设置水平居中
        $objPHPExcel->getActiveSheet()->getStyle(&#39;A1&#39;)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $objPHPExcel->getActiveSheet()->getStyle(&#39;A&#39;)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle(&#39;B&#39;)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle(&#39;D&#39;)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle(&#39;E&#39;)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //合并cell
        $objPHPExcel->getActiveSheet()->mergeCells(&#39;A1:J1&#39;);
        // set table header content
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue(&#39;A1&#39;, &#39;订单数据汇总  时间:&#39;.date(&#39;Y-m-d H:i:s&#39;))
            ->setCellValue(&#39;A2&#39;, &#39;订单ID&#39;)
            ->setCellValue(&#39;B2&#39;, &#39;商品名称&#39;)
            ->setCellValue(&#39;C2&#39;, &#39;价格&#39;)
            ->setCellValue(&#39;D2&#39;, &#39;库存&#39;)
            ->setCellValue(&#39;E2&#39;, &#39;图片&#39;);
        // Miscellaneous glyphs, UTF-8
        for($i=0;$i<count($list)-1;$i++){
            $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;A&#39;.($i+3), $list[$i][&#39;id&#39;]);
            $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;B&#39;.($i+3), $list[$i][&#39;name&#39;]);
            $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;C&#39;.($i+3), $list[$i][&#39;price&#39;]);
            $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;D&#39;.($i+3), $list[$i][&#39;stock&#39;]);
            $objPHPExcel->getActiveSheet(0)->setCellValue(&#39;E&#39;.($i+3), $list[$i][&#39;main_img_url&#39;]);
            //$objPHPExcel->getActiveSheet()->getStyle(&#39;A&#39;.($i+3).&#39;:J&#39;.($i+3))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            //$objPHPExcel->getActiveSheet()->getStyle(&#39;A&#39;.($i+3).&#39;:J&#39;.($i+3))->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getRowDimension($i+3)->setRowHeight(16);
        }
        //  sheet命名
        $objPHPExcel->getActiveSheet()->setTitle(&#39;订单汇总表&#39;);
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);
        // excel头参数
        header(&#39;Content-Type: application/vnd.ms-excel&#39;);
        header(&#39;Content-Disposition: attachment;filename="商品表(&#39;.date(&#39;Ymd-His&#39;).&#39;).xls"&#39;);  //日期为文件名后缀
        header(&#39;Cache-Control: max-age=0&#39;);
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, &#39;Excel5&#39;);  //excel5为xls格式，excel2007为xlsx格式
        $objWriter->save(&#39;php://output&#39;);

Copy after login

TP5 references PHPExcel to implement import and export functions

Import the code of the function controller

public function daoru(){
        $file = $_FILES[&#39;upload&#39;][&#39;tmp_name&#39;][0];
        $data = $this->import_excel($file);
        var_dump($data);
    }
    private function import_excel($file){
        // 判断文件是什么格式
        $type = pathinfo($file);
        $type = strtolower($type["extension"]);
        $type=$type===&#39;csv&#39; ? $type : &#39;Excel5&#39;;
        ini_set(&#39;max_execution_time&#39;, &#39;0&#39;);
        Vendor(&#39;PHPExcel176.PHPExcel&#39;);
        // 判断使用哪种格式
        $objReader = \PHPExcel_IOFactory::createReader($type);
        $objPHPExcel = $objReader->load($file);
        $sheet = $objPHPExcel->getSheet(0);
        // 取得总行数
        $highestRow = $sheet->getHighestRow();
        // 取得总列数
        $highestColumn = $sheet->getHighestColumn();
        //循环读取excel文件,读取一条,插入一条
        $data=array();
        //从第一行开始读取数据
        for($j=3;$j<=$highestRow;$j++){
            //从A列读取数据
            for($k=&#39;A&#39;;$k<=$highestColumn;$k++){
                // 读取单元格
                $data[$j][]=$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue();
            }
        }
        return $data;
    }

Copy after login

The data content of the excel file is obtained here, which can be inserted into the database in a loop

TP5 references PHPExcel to implement import and export functions

The above is the detailed content of TP5 references PHPExcel to implement import and export functions. For more information, please follow other related articles on the PHP Chinese website!