TP5引用PHPExcel实现导入导出功能

ThinkPHP  /  管理员 发布于 8年前   264

从下载PHPExcel

PHPExcel

将下载好的PHPExcel文件夹跟PHPExcel放置在框架中的Vendor目录下，注意目录的结构，按照我的代码来基本上直接引用就能使用

35d8ad7c1cf884da7c88e7e20213d95.png

前端代码

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

6649971f9b932a27525511637e92f30.png

导入功能控制器的代码

public function daoru(){        $file = $_FILES['upload']['tmp_name'][0];        $data = $this->import_excel($file);        var_dump($data);    }    private function import_excel($file){        // 判断文件是什么格式        $type = pathinfo($file);        $type = strtolower($type["extension"]);        $type=$type==='csv' ? $type : 'Excel5';        ini_set('max_execution_time', '0');        Vendor('PHPExcel176.PHPExcel');        // 判断使用哪种格式        $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='A';$k<=$highestColumn;$k++){                // 读取单元格                $data[$j][]=$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue();            }        }        return $data;    }

这里获得excel文件的数据内容，可以循环插入数据库中

478f15ff7b35307d31ccb3e5a56bd9a.png

以上就是TP5引用PHPExcel实现导入导出功能的详细内容，更多请关注其它相关文章！


  • 上一条：
    workerman在tp5怎么使用
    下一条：
    thinkphp和laravel性能比较

    • 昵称：

    邮箱：

    0条评论 (评论内容有缓存机制,请悉知!)
    Top

    Copyright·© 2019 侯体宗版权所有· 粤ICP备20027696号 PHP交流群

    侯体宗的博客