读取Excel
require_once "../vendor/autoload.php"; use \PhpOffice\PhpSpreadsheet\IOFactory; use \PhpOffice\PhpSpreadsheet\Cell\Coordinate; $spreadsheet = IOFactory::load("./file.xlsx"); // 载入Excel表格 # 这里和上面代码的效果都一样,好像就是只读区别吧 不是特别清楚,官网上也给出了很多读写的写法,应该用会少消耗资源 # 官网地址 https://phpspreadsheet.readthedocs.io/en/develop/topics/reading-and-writing-to-file/ //$reader = IOFactory::createReader('Xlsx'); //$reader->setReadDataOnly(TRUE); //$spreadsheet = $reader->load('./file.xlsx'); //载入excel表格 $worksheet = $spreadsheet->getActiveSheet(); $highestRow = $worksheet->getHighestRow(); // 总行数 $highestColumn = $worksheet->getHighestColumn(); // 总列数 # 把列的索引字母转为数字 从1开始 这里返回的是最大列的索引 # 我尝试了下不用这块代码用以前直接循环字母的方式,拿不到数据 # 测试了下超过26个字母也是没有问题的 $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn); //var_dump($highestColumnIndex); $data = []; for ($row = 2; $row <= $highestRow; ++$row) { // 从第二行开始 $row_data = []; for ($column = 1; $column <= $highestColumnIndex; $column++) { $row_data[] = $worksheet->getCellByColumnAndRow($column, $row)->getValue(); } $data[] = $row_data; } var_export($data);导出Excel
require '../vendor/autoload.php'; use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Spreadsheet; $data = array(0 => array(0 => 1, 1 => 'alex1', 2 => 1,), 1 => array(0 => 2, 1 => 'alex2', 2 => 2,), 2 => array(0 => 3, 1 => 'alex3', 2 => 1,), 3 => array(0 => 4, 1 => 'alex4', 2 => 2,), 4 => array(0 => 5, 1 => 'alex5', 2 => 1,), 5 => array(0 => 6, 1 => 'alex6', 2 => 2,)); $title = ['id', 'name', 'sex']; $spreadsheet = new Spreadsheet(); $worksheet = $spreadsheet->getActiveSheet(); //设置工作表标题名称 $worksheet->setTitle('测试Excel'); //表头 //设置单元格内容 foreach ($title as $key => $value) { $worksheet->setCellValueByColumnAndRow($key + 1, 1, $value); } $row = 2; //第二行开始 foreach ($data as $item) { $column = 1; foreach ($item as $value) { $worksheet->setCellValueByColumnAndRow($column, $row, $value); $column++; } $row++; } # 保存为xlsx $filename = '测试Excel.xlsx'; $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); $writer->save($filename); # 浏览器下载 //header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); //header('Content-Disposition: attachment;filename="'.$filename.'"'); //header('Cache-Control: max-age=0'); //$writer->save('php://output'); # 保存为xls $filename = '测试Excel.xls'; $writer = IOFactory::createWriter($spreadsheet, 'xls'); $writer->save($filename); # 浏览器下载 //header('Content-Type: application/vnd.ms-excel'); //header('Content-Disposition: attachment;filename="'.$filename.'"'); //header('Cache-Control: max-age=0'); //$writer->save('php://output');代码地址 https://gitee.com/JFliuxian/PhpDemo/tree/master/Spreadsheet
练习代码 有问题的请留言指出来,感谢
