PHP 导入和导出 Excel表格

admin管理员组

文章数量:1609902

一、序言

 以前的phpoffice/phpexcel  已经不在更新 而且在  新版的liunx服务器可能会出现兼容性问题

二、 使用新版导出类  phpoffice/phpspreadsheet

composer require phpoffice/phpspreadsheet

示例：使用Thinkphp框架测试

<?php
//引入导出类

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\facade\Db;

class PhpExcel {
 
     public function phpExcelList()
    {
        //查询数据库（可以加where条件筛选一下）
        $list = Db::name('member')->order('id desc')->select();
        $arr = array();
        foreach ($list as $index => $item){
            $arr[$index]['team_name'] = $item['team_name'];
            $arr[$index]['member_name'] = $item['member_name'];
            $arr[$index]['type'] = $item['type_name'];
            $arr[$index]['state'] = $item['state_name'];
            $arr[$index]['start_time'] = $item['s_time'];
            $arr[$index]['end_time'] = $item['n_time'];
            $arr[$index]['content'] = $item['content'];
            $arr[$index]['pin_time'] = $item['p_time'];

        }


        $title = '请假_' . date('Y-m-d');
        ##对应单元格数据匹配字段
        $field = array(
            'A' => array('team_name', '所属班级',1,20),
            'B' => array('member_name', '请假人',1,25),
            'C' => array('type', '请假类型',1,15),
            'D' => array('state', '状态',1,15),
            'E' => array('start_time', '开始时间',1,20),
            'F' => array('end_time', '结束时间',1,20),
            'G' => array('content', '请假原因',1,20),
        );

        $spreadsheet = new Spreadsheet();

        $sheet = $spreadsheet->getActiveSheet();
        foreach ($field as $k => $v){
            $sheet->setCellValue($k.$v[2],  $v[1]);
        }

       //单元格长度
        foreach ($field as $k => $v){
            $spreadsheet->getActiveSheet()->getColumnDimension($k)->setWidth($v[3]);
        }

        $sheet->fromArray(
            $arr,##需要放的数据
           count($arr),##总数
            'A2'##起始位置
        );
       ##导出一 直接生成xlsx文件
        $writer = new Xlsx($spreadsheet);
        $root = $_SERVER['DOCUMENT_ROOT'];
        $filePath = $root.'/excel/';
        if(!is_dir($filePath)){
            mkdir($filePath,0777,true);
        }
        ##文件地址
        $fileName = $filePath.time().'.xlsx';
        $writer->save($fileName);
       ##导出二 直接返回二进制数据
        header('Content-Type:application/vnd.ms-excel');
        header('Content-Disposition:attachment;filename='.time().'.xls');
        header('Cache-Control:max-age=0');
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
        $writer->save('php://output');

    }
}

三、导入

       //获取上次的文件
       $upload_file = $_FILES['file']['tmp_name'];

        //自动获取文件类型 (xlsx,xls,csv)
        $spreadsheet = IOFactory::load($upload_file);
        $worksheet = $spreadsheet->getActiveSheet();
        // 总行数
        $highestRow = $worksheet->getHighestRow();
        // 总列数
        $highestColumn = $worksheet->getHighestColumn();

        $lines = $highestRow - 1;
        if ($lines <= 0) {
            return ['code' => 0, 'msg' => '暂无数据', 'data' => []];
        }
        $arr = [];
        //第二行开始
        for ($row = 2; $row <= $highestRow; ++$row) {
           //根据自身逻辑获取需求的数据进行解析
            $week_time = $worksheet->getCellByColumnAndRow(1, $row)->getValue(); //时间
            $heat = $worksheet->getCellByColumnAndRow(2, $row)->getValue(); //热量
            $grass_txt = $worksheet->getCellByColumnAndRow(3, $row)->getValue(); //早餐
            $noon_txt = $worksheet->getCellByColumnAndRow(4, $row)->getValue(); //午餐
            if($week_time){
                $arr[] = [
                    'week_time'=>$week_time,
                    'heat'=>$heat,
                    'grass'=>$grass_txt,
                    'noon'=>$noon_txt,
                    'create_time'=>date('Y-m-d H:i:s',time()),
                    'update_time'=>date('Y-m-d H:i:s',time()),
                    'update_user'=>request()->getUserID(),
                    'is_del'=>0,

                ];
            }
        }

四、总结

   更加简洁的导出数据 以上代码亲测有效，希望可以帮到大家

本文标签： 表格PHPExcel