[PHP知识库]Laravel使用PhpOffice示例

添加一个controller类

testController.php

<?php

namespace App\Http\Controllers\Web;

use Illuminate\Http\Request;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
class TestController
{
    public function test1(){
        $data = [
            ['adata1', 'adata2'],
            ['bdata1', 'bdata2']
        ];

//        $this->export(['tit1','tit2'], $data, 'test', storage_path().'/');//导出
        dd($this->read('c:/Users/liu/Downloads/test.xlsx')->toArray());//导入
        return 'result';
    }

    /**
     * 导出excel表并保存到服务器
     * @param array $title 标题行名称
     * @param array $data 导出数据
     * @param string $file_name 文件名
     * @param string $save_path 保存路径
     * @param int $options 下载或保存
     * @return string   返回文件全路径
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function export($title = array(), $data = array(), $file_name = '', $save_path = './', $options = 1)
    {
        //实例化类
        $spreadsheet = new Spreadsheet();

        //横向单元格标识
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        //设置sheet名称   $spreadsheet->getActiveSheet(0)  --- 获取工作簿0
        $spreadsheet->getActiveSheet(0)->setTitle('sheet1');
        //设置纵向单元格标识
        $_row = 1;
        if ($title) {
            $_cnt = count($title);
            $spreadsheet->getActiveSheet(0)->mergeCells('A' . $_row . ':' . $cellName[$_cnt - 1] . $_row);   //合并单元格
            $spreadsheet->setActiveSheetIndex(0)
                ->setCellValue('A' . $_row, $file_name.".". date('Y-m-d H:i:s'));//设置合并后的单元格内容
            $_row++;
            $i = 0;
            foreach ($title AS $v) {   //设置列标题
                $spreadsheet->setActiveSheetIndex(0)->setCellValue($cellName[$i] . $_row, $v);
                $i++;
            }
            $_row++;
        }
        //填写数据
        if ($data) {
            $i = 0;
            foreach ($data AS $_v) {
                $j = 0;
                foreach ($_v AS $_cell) {
                    $spreadsheet->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + $_row), $_cell);
                    $j++;
                }
                $i++;
            }
        }
        //文件名处理
        if (!$file_name) {
            $file_name = uniqid(time(), TRUE);
        }
        $writer = new Xlsx($spreadsheet);
        if ($options == 1) {   //网页下载
            header('pragma:public');
            header("Content-Disposition:attachment;filename = $file_name.xlsx");
            $writer->save('php://output');
        } else if ($options == 0) {//存储到后台
            $file_name = iconv("utf-8", "gb2312", $file_name);   //转码
            $save_path = $save_path . $file_name . '.xlsx';
            $writer->save($save_path);
            return $file_name . '.xlsx';
        } else if ($options == 2) { //网页下载 + 存储到后台
            header('pragma:public');
            header("Content-Disposition:attachment;filename = $file_name.xlsx");
            $writer->save('php://output');
            $file_name = iconv("utf-8", "gb2312", $file_name);   //转码
            $save_path = $save_path . $file_name . '.xlsx';
            $writer->save($save_path);
        }

        //删除清空：
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }

    /**
     * 读取excel里面的内容保存为数组
     * @param string $file_path
     * @param array $read_column
     * @return array
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public function read($file_path = '/', $read_column = array())
    {
        $reader = IOFactory::createReader('Xlsx');

        $reader->setReadDataOnly(TRUE);

        //载入excel表格
        $spreadsheet = $reader->load($file_path);

        // 读取第一個工作表
        $sheet = $spreadsheet->getSheet(0);

        // 取得总行数
        $highest_row = $sheet->getHighestRow();

        // 取得总列数
        $highest_column = $sheet->getHighestColumn();
//        dd($highest_row, $highest_column, $sheet->toArray());

        return $sheet;
    }
}

excel内容

除C4是手动添加的内容外，其它都是上面程序导出的内容

excel导入输出结果

array:4 [▼
  0 => array:3 [▼
    0 => "test.2021-08-28 11:36:39"
    1 => null
    2 => null
  ]
  1 => array:3 [▼
    0 => "tit1"
    1 => "tit2"
    2 => null
  ]
  2 => array:3 [▼
    0 => "adata1"
    1 => "adata2"
    2 => null
  ]
  3 => array:3 [▼
    0 => "bdata1"
    1 => "bdata2"
    2 => 2555.0
  ]
]