composer下载phpspreadsheet
composer require phpoffice/phpspreadsheet
二次封装
<?phpnamespace app\index\lib;use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\exception\ValidateException;
use think\facade\Filesystem;class Excel
{// excel导入public static function importExcel($filename = ""){$file[] = $filename;try {// 验证文件大小，名称等是否正确validate(['file' => 'filesize:51200|fileExt:xls,xlsx'])->check($file);// 将文件保存到本地$savename = Filesystem::putFile('topic', $file[0]);// 截取后缀$fileExtendName = substr(strrchr($savename, '.'), 1);// 有Xls和Xlsx格式两种if ($fileExtendName == 'xlsx') {$objReader = IOFactory::createReader('Xlsx');} else {$objReader = IOFactory::createReader('Xls');}// 设置文件为只读$objReader->setReadDataOnly(TRUE);// 读取文件，tp6默认上传的文件，在runtime的相应目录下，可根据实际情况自己更改$objPHPExcel = $objReader->load(public_path() . 'storage/' . $savename);//excel中的第一张sheet$sheet = $objPHPExcel->getSheet(0);// 取得总行数$highestRow = $sheet->getHighestRow();// 取得总列数$highestColumn = $sheet->getHighestColumn();Coordinate::columnIndexFromString($highestColumn);$lines = $highestRow - 1;if ($lines <= 0) {echo('数据不能为空！');exit();}// 直接取出excle中的数据$data = $objPHPExcel->getActiveSheet()->toArray();// 删除第一个元素（表头）array_shift($data);// 返回结果return $data;} catch (ValidateException $e) {return $e->getMessage();}}// 导出public static function export($header = [], $type = true, $data = [], $fileName = ""){// 实例化类$preadsheet = new Spreadsheet();// 创建sheet$sheet = $preadsheet->getActiveSheet();// 循环设置表头数据foreach ($header as $k => $v) {$sheet->setCellValue($k, $v);}// 生成数据$sheet->fromArray($data, null, "A2");// 样式设置$sheet->getDefaultColumnDimension()->setWidth(12);// 设置下载与后缀if ($type) {header("Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");$type = "Xlsx";$suffix = "xlsx";} else {header("Content-Type:application/vnd.ms-excel");$type = "Xls";$suffix = "xls";}// 激活浏览器窗口header("Content-Disposition:attachment;filename=$fileName.$suffix");//缓存控制header("Cache-Control:max-age=0");// 调用方法执行下载$writer = IOFactory::createWriter($preadsheet, $type);// 数据流$writer->save("php://output");}
}
控制器层调用
excel的导入
public function index(Request $request)
{// 接收文件上传信息$files = $request->file("myfile");// 调用类库，读取excel中的内容$data = Excel::importExcel($files);dd($data); // 二维数组
}
excel的导出
public function get()
{// 设置表格的表头数据$header = ["A1" => "编号", "B1" => "姓名", "C1" => "年龄"];// 假设下面这个数组从数据库查询出的二维数组$data = [[1,'yyp',18],[2,'yyp',19],[3,'yyp',22],[4,'yyp',19],[5,'yyp',29]];// 保存文件的类型$type= true;// 设置下载文件保存的名称$fileName = '1909A班违纪信息导出'.time();// 调用方法导出excelExcel::export($header,$type,$data,$fileName);
}第二种方法
public function export(){$data = Db::table('user')->field('id,user_name,phonenumber')->select()->toArray();$title = ['ID', '用户名','电话'];// Create new Spreadsheet object$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();$sheet = $spreadsheet->getActiveSheet();// 方法二，使用 setCellValue//表头//设置单元格内容$titCol = 'A';foreach ($title as $key => $value) {// 单元格内容写入$sheet->setCellValue($titCol . '1', $value);$titCol++;}$row = 2; // 从第二行开始foreach ($data as $item) {$dataCol = 'A';foreach ($item as $value) {// 单元格内容写入$sheet->setCellValue($dataCol . $row, $value);$dataCol++;}$row++;}ob_end_clean();// Redirect output to a client’s web browser (Xlsx)header('Content-Type: applicationnd.openxmlformats-officedocument.spreadsheetml.sheet');header('Content-Disposition: attachment;filename="01simple.xlsx"');header('Cache-Control: max-age=0');// If you're serving to IE 9, then the following may be neededheader('Cache-Control: max-age=1');// If you're serving to IE over SSL, then the following may be neededheader('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the pastheader('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modifiedheader('Cache-Control: cache, must-revalidate'); // HTTP/1.1header('Pragma: public'); // HTTP/1.0$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');$writer->save('php://output');exit;}