网站首页 文章专栏 基于maatwebsite/excel 组件上传和下载excel
解析excel 文件
构造通用解析类:
<?php namespace App\Imports; use Maatwebsite\Excel\Concerns\Importable; use Maatwebsite\Excel\Concerns\ToArray; use Maatwebsite\Excel\Concerns\WithCalculatedFormulas; use Maatwebsite\Excel\Concerns\WithStartRow; use Maatwebsite\Excel\Concerns\WithMultipleSheets; class Import implements ToArray, WithStartRow, WithCalculatedFormulas,WithMultipleSheets { use Importable; /** * @param array $array * @return array */ public function array(array $array) { return $array; } /**从第二行开始读 * @return int */ public function startRow(): int { return 2; } /**第一个sheet的处理方式,一般只解析第一sheet * @return int */ public function sheets(): array { return [ // Select by sheet index 0 => new ZtcEmployeeImport(), ]; } }
调用:
<?php use Maatwebsite\Excel\Excel; use App\Imports\Import; $sheets = $excel->toArray(new Import (), $request->file('file')); $data = $sheets[0];//获取第一个sheet 里的数据,格式为二维枚举数组 //取出的 数据$data为 [ 0 => [ 0 => 'hbh', 1 => '123456', 2 => '18211111111', ], 1 => [ 0 => 'wl', 1 => '654321', 2 => '15211111111', ] ];
上传常遇到的大坑:
excel 日期格式是一个偏移值,比如 ‘2021/01/01’ 解析出来是个 数字 43831,这是因为 它的偏移基数为1900/1/0 0:00:00的。
知道原理就自己转换吧:
$n = intval(($time_str - 25569) * 3600 * 24); $time = gmdate('Y-m-d', $n);
下载
构造通用解析类:
<?php namespace App\Exports; use Illuminate\Support\Str; use Maatwebsite\Excel\Concerns\Exportable; use Maatwebsite\Excel\Concerns\FromArray; use Maatwebsite\Excel\Concerns\RegistersEventListeners; use Maatwebsite\Excel\Concerns\WithColumnFormatting; use Maatwebsite\Excel\Concerns\WithCustomValueBinder; use Maatwebsite\Excel\Concerns\WithEvents; use Maatwebsite\Excel\Concerns\WithHeadings; use Maatwebsite\Excel\Concerns\WithTitle; use PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder; use PhpOffice\PhpSpreadsheet\Style\NumberFormat; use PhpOffice\PhpSpreadsheet\Cell\DataType as ExcelDataType; class ExcelDownloadExport extends DefaultValueBinder implements FromArray, WithEvents, WithHeadings, WithTitle, WithColumnFormatting, WithCustomValueBinder { use Exportable, RegistersEventListeners; protected $payment; protected $headings; protected $columnFormats; public function __construct(array $payment, array $headings, $columnFormats = []) { $this->payment = $payment; $this->headings = $headings; $this->columnFormats = $columnFormats; } public function headings(): array { return $this->headings; } public function array(): array { return $this->payment; } /**sheet的标题 * @return array */ public function title(): string { return date('YmdHis'); } public function bindValue($cell, $value) { //处理超长字符串 if (is_numeric($value) && Str::length($value) >= 12) { $cell->setValueExplicit($value, ExcelDataType::TYPE_STRING); return true; } // else return default behavior return parent::bindValue($cell, $value); } /**设置某列单元格格式 * @return array */ public function columnFormats(): array { if (empty($this->columnFormats)) { return [ 'B' => NumberFormat::FORMAT_TEXT, 'C' => NumberFormat::FORMAT_TEXT, 'D' => NumberFormat::FORMAT_TEXT, 'E' => NumberFormat::FORMAT_TEXT ]; } return $this->columnFormats; }
调用:
<?php //只导出最基础的表格,要整的花里胡哨样式,请参考文档https://docs.laravel-excel.com/3.1/exports/concerns.html header("access-control-expose-headers: Authorization, Content-Disposition");//请求头,根据需要设置 $headings = ['姓名', '手机号', '身份证']; $export = new ExcelDownloadExport($data, $headings);//$data的元素长度与$headings一致 return $export->download('信息列表.xlsx', Excel::XLSX);
上传常遇到的大坑:
像身份证号这种长字符串,excel会变成科学计数存储。
如:411280199001011234 存储成 411280199001011000
解决办法:转成字符串即可
转载请注明出处