TP5는 PHPExcel을 참조하여 가져오기 및 내보내기 기능을 구현합니다.-ThinkPHP-php.cn

TP5는 PHPExcel을 참조하여 가져오기 및 내보내기 기능을 구현합니다.

藏色散人

풀어 주다： 2019-12-25 13:44:05

앞으로

3064명이 탐색했습니다.

PHP 중국어 사이트에서 PHPExcel을 다운로드하세요

PHPExcel

다운받은 PHPExcel 폴더와 PHPExcel을 프레임워크의 Vendor 디렉토리에 넣어두시면 기본적으로 디렉토리 구조에 따라 직접 인용하여 사용하실 수 있습니다. code

TP5는 PHPExcel을 참조하여 가져오기 및 내보내기 기능을 구현합니다.

프런트 엔드 코드

<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>

로그인 후 복사

함수 컨트롤러의 코드 내보내기

$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;);

로그인 후 복사

TP5는 PHPExcel을 참조하여 가져오기 및 내보내기 기능을 구현합니다.

함수 컨트롤러의 코드 가져오기

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;
    }

로그인 후 복사

여기서 루프가 가능한 Excel 파일의 데이터 콘텐츠를 가져옵니다. 데이터베이스에

TP5는 PHPExcel을 참조하여 가져오기 및 내보내기 기능을 구현합니다.