PhpSpreadsheet 读,写,模版生产excel文件

关于这个插件各方面的简单使用例子如下链接：

https://blog.csdn.net/appAndWxy/article/details/84889713

再上自己的代码

然后use

use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx;

public function export()
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        //查询数据信息
        $book = model('Book')->select();
        
        $sheet->setCellValue("A1", "书籍名");
        $sheet->setCellValue("B1", "ID号");


        foreach($book as $k => $v){
            $sheet->setCellValue("A".($k+2), $v['book_name']);
            $sheet->setCellValue("B".($k+2), $v['id']);
        }
    
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出07Excel文件
        //header(‘Content-Type:application/vnd.ms-excel‘);//告诉浏览器将要输出Excel03版本文件
        header('Content-Disposition: attachment;filename="01simple.xlsx"');//告诉浏览器输出浏览器名称
        header('Cache-Control: max-age=0');//禁止缓存
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');

        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
    }

这里是直接输出下载的，事后注意销毁内存

其它博客

https://www.cnblogs.com/itbsl/p/10369695.html

插件官方手册，不过是英文版本的，难受

https://phpspreadsheet.readthedocs.io/en/latest/references/function-list-by-category/

更新：

关于读取表格的写法

public function DaoRuData()
    {
        $inputFileName = ROOT_PATH . 'public' . DS . 'static'. DS . 'exe.xlsx';
        /** Load $inputFileName to a Spreadsheet Object  **/
        // $spreadsheet = PhpOfficePhpSpreadsheetIOFactory::load($inputFileName);

        $reader = new PhpOfficePhpSpreadsheetReaderXlsx();
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load($inputFileName);

        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow = $worksheet->getHighestRow(); // 总行数
        $highestColumn = $worksheet->getHighestColumn(); // 总列数
        $highestColumnIndex = PhpOfficePhpSpreadsheetCellCoordinate::columnIndexFromString($highestColumn);
        $lines = $highestRow - 2; 
        if ($lines <= 0) {
            exit('Excel表格中没有数据');
        }
        $arr = [];
        for ($row = 1; $row <= $highestRow; ++$row) {
            $data['id'] = $worksheet->getCellByColumnAndRow(1, $row)->getValue(); //姓名
            $data['name'] = $worksheet->getCellByColumnAndRow(2, $row)->getValue(); //语文
            $data['auth'] = $worksheet->getCellByColumnAndRow(3, $row)->getValue(); //数学
            $data['b'] = $worksheet->getCellByColumnAndRow(4, $row)->getValue(); //外语
            $arr[] = $data;
        }
        echo "<pre>";
        var_dump($arr);
    }

拿来改动自己的文件地址，和注意引入命名空间。VS即可实现demo

附上地址

官方：https://phpspreadsheet.readthedocs.io/en/latest/topics/reading-and-writing-to-file/

详解PhpSpreadsheet设置单元格：https://blog.csdn.net/gc258_2767_qq/article/details/81003656

使用PhpSpreadsheet将Excel导入到MySQL数据库：https://www.helloweba.net/php/562.html