PHPSpreadsheet Excel的导入和导出,读取时文件上传问题
1、使用composer安装
composer require phpoffice/phpspreadsheet
2、写入Excel(导出)
common(应用公共文件,app/common.php),方便调用:
use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Spreadsheet; function exportData($head, $body, $name = '', $version = '2007',$title='Export Data') { try { // 输出 Excel 文件头 if(empty($name)){ $name =date('Y-m-d-H-i-s'); } $spreadsheet = new Spreadsheet(); $sheetPHPExcel = $spreadsheet->setActiveSheetIndex(0); $char_index = range('A', 'Z');//数组,從A-Z //处理超过26列 $a = 'A'; foreach ($char_index as $item){ $char_index[] = $a . $item; } // Excel 表格头 foreach ($head as $key => $val) { $sheetPHPExcel->setCellValue("{$char_index[$key]}1", $val); } $spreadsheet->getActiveSheet()->setTitle($title); // Excel body 部分 foreach ($body as $key => $val) { $row = $key + 2; $col = 0; foreach ($val as $k => $v) { $spreadsheet->getActiveSheet()->setCellValue("{$char_index[$col]}{$row}", $v); $col++; } } // 版本差异信息 $version_opt = [ '2007' => [ 'mime' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'ext' => '.xlsx', 'write_type' => 'Xlsx', ], '2003' => ['mime' => 'application/vnd.ms-excel', 'ext' => '.xls', 'write_type' => 'Xls', ], 'pdf' => ['mime' => 'application/pdf', 'ext' => '.pdf', 'write_type' => 'PDF', ], 'ods' => ['mime' => 'application/vnd.oasis.opendocument.spreadsheet', 'ext' => '.ods', 'write_type' => 'OpenDocument', ], ]; header('Content-Type: ' . $version_opt[$version]['mime']); header('Content-Disposition: attachment;filename="' . $name . $version_opt[$version]['ext'] . '"'); header('Cache-Control: max-age=0'); $objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx'); return $objWriter->save('php://output'); } catch (Exception $e) { return $e->getMessage(); } }
Index控制器:
public function export(Article $model) { $header = ['編號', '标题', '内容']; $body = []; $data = $model::select(); // halt($data); foreach ($data as $item) { $record = []; $record['id'] = $item->id; $record['title'] = $item->title; $record['content'] = $item->content; $body[] = $record; } return exportData($header, $body, 'Article_' . date('YmdHis')); }
3、读取Excel(导入),文件上传
HTML,form里有type='text'和type='file'
<script src="http://localhost/think/public/static/js/jquery-1.11.1.min.js"></script> <form action="" enctype="multipart/form-data" id="document-form" method="post"> <input type="text" name="id" id="id" value="11"/> <input type="file" name="file" id="file"/> <br><br><br> </form> <input type="submit" id="submit" value="上传" />
如果直接使用serialize,是獲取不了type="file"的的數據,只能获取type='text'的数据:
var formData= $("#document-form").serialize();
我们可以使用formData,这样就可以获取type="file"和type='text'的数据
//通过FormData构造函数创建一个空对象 var formData = new FormData(); //可以通过append()方法来追加数据 formData.append("file", $("#file")[0].files[0]); formData.append("id", $("#id").val());
如果form里有很多个type="file"和type='text',一个个添加到formData太麻烦;但可以通过循环全部添加:
//$.fn.serializeFiles()=function(){}的调用把方法扩展到了对象的prototype上,所以实例化一个jQuery对象的时候,它就具有了这些方法。 $.fn.serializeFiles = function() { var form = $(this),formData = new FormData();//通过FormData构造函数创建一个空对象 var formParams = form.serializeArray();//带有name和value的json,比如{name: "username1", value: "a"} //通过append()方法来追加数据,type="file" $.each(form.find('input[type="file"]'), function(i, tag) { $.each($(tag)[0].files, function(i, file) { formData.append(tag.name, file); }); }); //通过append()方法来追加数据,type="text" $.each(formParams, function(i, val) { formData.append(val.name, val.value); }); return formData; }; $("#submit").click(function () { var formData= $("#document-form").serializeFiles(); $.ajax({ url: "{:url('importE')}", data : formData, cache : false, type : 'POST', processData : false, // 使数据不做处理 contentType : false, // 不要设置Content-Type请求头 success: function(res){ //成功之後 } }) })
注:ajax里processData和contentType都设置false,否则报错
Index控制器:
//導入Excel public function importE(){ /*$spreadsheet = new Spreadsheet(); $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file['tmp_name']);//加载excel $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);//读取excel文件内容 halt($sheetData);*/ // 获取活动工作簿 // 创建读操作 $file = $_FILES['file']; $param = $this->request->param(); halt($file); $reader = IOFactory::createReader('Xlsx'); // 打开文件 载入excel表格 $spreadsheet = $reader->load($file['tmp_name']); $sheet = $spreadsheet->getActiveSheet(); // 获取内容的最大列 如:D $highest = $sheet->getHighestColumn(); // 获取内容的最大行 如:4 $row = $sheet->getHighestRow(); $data = [];// 用于存储表格数据 for ($i = 2; $i <= $row; $i++) { // 獲取行數的數據 $rowData = [ "title" => $sheet->getCellByColumnAndRow(2, $i)->getValue(), "content" => $sheet->getCellByColumnAndRow(3, $i)->getValue(), ]; $data[] = $rowData; } //直接添加 Article::insertAll($data); //或者把数组转换成JSON格式輸出 $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true); return json($sheetData); }
我这篇文章只是简单的介绍,如果想看详解请看下面这篇文章,各种属性和方法都有解释: