Dcat-admin使用xlswriter扩展实现分块导出Excel数据功能
2023-06-01 00:00:00
dcat
项目小数据少可以使用默认的laravel-excel，所以数据多了就会影响性能了，后续就会优化或者改成xlswriter扩展实现导出表格数据功能等类似的新方案了，请看下面！
xlswriter是一个PHP C扩展，可用于将文本、数字、公式和超链接写入 Excel 2007+ XLSX 文件中的多个工作表。
官方文档：
https://xlswriter-docs.viest.me/
composer安装
composer require viest/php-ext-xlswriter-ide-helper:dev-master
新建导出类：（测试用）
<?php
namespace App\Exports\OutPerson;
use App\Models\OutPerson;
use Carbon\Carbon;
use Dcat\Admin\Grid\Exporter;
use Dcat\Admin\Grid\Exporters\AbstractExporter;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Model;
use Vtiful\Kernel\Excel;
use Vtiful\Kernel\Format;
class OutPersonExport extends AbstractExporter {
//column只是方便查看对应关系，列号是自动计算的，列号范围从a到zz
public $header = [
0 => ['column' => 'a', 'width' => 8, 'name' => '序号'],
1 => ['column' => 'b', 'width' => 10, 'name' => '住址-乡镇街道'],
2 => ['column' => 'c', 'width' => 10, 'name' => '姓名'],
3 => ['column' => 'd', 'width' => 10, 'name' => '职业'],
4 => ['column' => 'e', 'width' => 5, 'name' => '是否在外定居'],
5 => ['column' => 'f', 'width' => 15, 'name' => '备注'],
6 => ['column' => 'g', 'width' => 10, 'name' => '外出时间'],
7 => ['column' => 'h', 'width' => 10, 'name' => '返回时间'],
8 => ['column' => 'i', 'width' => 10, 'name' => '省'],
9 => ['column' => 'j', 'width' => 10, 'name' => '市'],
10 => ['column' => 'k', 'width' => 10, 'name' => '区县'],
11 => ['column' => 'l', 'width' => 10, 'name' => '街道乡镇'],
12 => ['column' => 'm', 'width' => 10, 'name' => '小区楼栋'],
13 => ['column' => 'n', 'width' => 10, 'name' => '住址-社区、村'],
14 => ['column' => 'o', 'width' => 10, 'name' => '住址-小区楼栋'],
15 => ['column' => 'p', 'width' => 25, 'name' => '身份证号码'],
16 => ['column' => 'q', 'width' => 20, 'name' => '联系方式'],
17 => ['column' => 'r', 'width' => 5, 'name' => '性别'],
];
public $fileName = '外出人员导出表';
public $tableTitle = '外出人员导出表';
/**
* @var Collection
*/
public $data;
public function getTmpDir(): string {
$tmp = ini_get('upload_tmp_dir');
if ($tmp !== false && file_exists($tmp)) {
return realpath($tmp);
}
return realpath(sys_get_temp_dir());
}
public function setFilename($filename) {
$this->fileName = $filename . Date('YmdHis') . '.xlsx';
return $this;
}
public function getFilename() {
return $this->fileName;
}
public function getHeader() {
return $this->header;
}
public function getTableTitle() {
return $this->tableTitle;
}
public function getData() {
return $this->data;
}
public $index;
public function setData($data) {
if (!$data instanceof \Illuminate\Support\Collection) {
$data = collect($data);
}
$this->data = $data;
$this->index = 1;
return $this;
}
// 格式化日期的，不需要可以删除
public static function formatDate($date, $format = "m-d") {
if ($date) {
return (new Carbon($date))->format($format);
}
return null;
}
public function map($row) {
if (!$row instanceof Model) {
return $row;
}
// dd($row);
/** @var OutPerson $row */
// 如果有关联关系，需要在grid或者model定义with预加载，不然每一个查一下，严重影响速度
$person = $row->person;
return [
/*'a' =>*/ // 行号主要方便查看对应关系，可以删除
$this->index++, //序号
/*'b' =>*/
$person->street->name ?? null, //所属乡镇街道
/*'c' =>*/
$person->name, //姓名
/*'d' =>*/
$person->job, //职业
/*'e' =>*/
YesOrNo[$row->live_at_out]??null, //是否在外定居
/*'f' =>*/
$row->note, //备注
/*'g' =>*/
static::formatDate($row->leave_at), //外出时间
/*'h' =>*/
static::formatDate($row->will_return_at), //返回时间
/*'i' =>*/
$row->province->name ?? null, //省
/*'j' =>*/
$row->city->name ?? null, //市
/*'k' =>*/
$row->region->name ?? null, //区县
/*'l' =>*/
$row->street->name ?? null, //街道乡镇
/*'m' =>*/
$row->floor, //小区楼栋
/*'n' =>*/
$person->community->name ?? null, //现住地社区、村
/*'o' =>*/
$person->floor, //现住地小区楼栋
/*'p' =>*/
$person->id_card . ' ', //身份证号码
/*'q' =>*/
$person->phone . ($person->phone2 ? "\n" . $person->phone2 : ""), //联系方式+备用号码
/*'r' =>*/
Genders[$person->gender] ?? null, //性别
];
// return $this;
}
public const FontFamily = '微软雅黑';
public const RowHeight = 40; // 行高
public $filePath; // 表格保存的地址
public $excel;
public $headerLen; // 表头长度
public function __construct() {
parent::__construct();
$config = ['path' => $this->getTmpDir() . '/']; // 文件保存的路径
// dd($config);
$this->excel = (new Excel($config))/*->constMemory($fileName,'Sheet1')*/ ->fileName($this->setFilename($this->fileName)->fileName, 'Sheet1');
}
public function store($isAll = false) {
$fileHandle = $this->excel->getHandle();
$format1 = new Format($fileHandle);
$format2 = new Format($fileHandle);
// $format3 = new Format($fileHandle);
/** @var Collection $data */
$data = $this->getData();
$header = $this->getHeader();
$this->headerLen = count($header);
$columnWidths = array_column($header, 'width');
$columnNames = array_column($header, 'name');
// header
$data->prepend($columnNames);
// title
$title = array_fill(1, $this->headerLen - 1, '');
$title[0] = $this->getTableTitle();
$data->prepend($title);
// title style
$titleStyle = $format1->fontSize(16)
->bold()
->font(self::FontFamily)
->align(Format::FORMAT_ALIGN_CENTER, Format::FORMAT_ALIGN_VERTICAL_CENTER)
->wrap()
->toResource();
// $headerStyle = $format3/*->fontSize(10)*/
// // ->font(self::FontFamily)
// // ->align(Format::FORMAT_ALIGN_CENTER, Format::FORMAT_ALIGN_VERTICAL_CENTER)
// // ->border(Format::BORDER_THIN)
// ->bold()
// // ->wrap()
// ->toResource();
// global style
$globalStyle = $format2->fontSize(10)
->font(self::FontFamily)
->align(Format::FORMAT_ALIGN_CENTER, Format::FORMAT_ALIGN_VERTICAL_CENTER)
->border(Format::BORDER_THIN)
->wrap()
->toResource();
// 获取最后一列的列名
$end = $this->getColumn($this->headerLen - 1);
// dd($end);
// 应用样式
$this->excel = $this->excel/*->defaultFormat($globalStyle)*/// 默认样式
->MergeCells("A1:{$end}1", $this->getFilename()) // 合并title单元格
->setRow("A1", 50, $titleStyle) // title样式
->setRow("A2", self::RowHeight) // header样式
->freezePanes(2, 0); // 冻结前两行，列不冻结
// 设置列宽 以及默认样式
foreach ($columnWidths as $k => $columnWidth) {
$column = $this->getColumn($k);
// dd($column);
$this->excel->setColumn($column . ':' . $column, $columnWidth, $globalStyle);
}
// 数据填充，导出
if ($isAll) {
$this->insertData($data);
$this->filePath = $this->chunk(function(int $times, $perPage) {
// dump($this->buildData($times, $perPage));
return $this->buildData($times, $perPage);
})->output();
} else {
$this->filePath = $this->insertData($data)->output();
}
return $this;
// 以下是php原生写法，后面改成了laravel的response写法
// Set Header
// header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
// header('Content-Disposition: attachment;filename="' . $fileName . '"');
// header('Content-Length: ' . filesize($this->filePath));
// header('Content-Transfer-Encoding: binary');
// header('Cache-Control: must-revalidate');
// header('Cache-Control: max-age=0');
// header('Pragma: public');
// ob_clean();
// flush();
// if (copy($this->filePath, 'php://output') === false) {
// throw new \Exception('failed to write output');
// }
// // Delete temporary file
// @unlink($this->filePath);
// exit();
}
public $shouldDelete = false;
public $startDataRow = 2; // 第三行开始数据行(0是第一行）
public $currentLine = 0; // 当前数据插入行
public function insertData($data) {
// dd($this->currentLine, $data);
foreach ($data as $row => $rowData) {
$rowData = $this->map($rowData);
// 对数据行处理
if ($this->currentLine >= $this->startDataRow) {
$this->excel->setRow($this->currentLine + 1, self::RowHeight); // 设置行高，这里的行又是从1开始的，所以+1
}
foreach ($rowData as $column => $columnData) {
$this->excel->insertText($this->currentLine, $column, $columnData);
}
$this->currentLine++;
}
return $this;
}
public function output() {
return $this->excel->output();
}
// 根据列的序号，得到对应字母列号，范围a~zz
public function getColumn(int $columnIndex) {
$columnIndex++;
$first = 64 + (int)($columnIndex / 26);
$second = 64 + $columnIndex % 26; // 26个字母
if($second===64){ // 如果余0，说明是26的倍数，末位是Z，首位暂不进位，27才进位
$first--;
$second='Z';
}else{
$second = chr($second);
}
if ($first > 90/*64 + 26*/) {
throw new \Exception('超出最大列数');
} else if ($first === 64) {
$first = '';
} else {
$first = chr($first);
}
// dd(ord('Z'));
// dd($first, $second);
return $first . $second;
}
public function shouldDelete($v = true) {
$this->shouldDelete = true;
return $this;
}
public function download($filePath = null) {
if ($filePath) {
$this->filePath = $filePath;
}
if ($key = request('key')) {
$this->filePath = base64_decode($key);
}
response()->download($this->filePath)->deleteFileAfterSend($this->shouldDelete)->send();
exit();
}
public function export() {
$isAll = $this->scope === Exporter::SCOPE_ALL;
if ($isAll) {
set_time_limit(0);
$this->setData([])->store(true);
} else {
$this->setData($this->buildData())->store();
}
// 这个是备选方案
// if ($isAll) {
// $filePath = base64_encode($this->filePath);
// dump('导出完成');
// echo "<a href='/admin/out_persons/export/download?key=$filePath'>点击下载</a>";
// exit();
// }
$this->shouldDelete()->download();
}
public function chunk($callback = null) {
$times = 1;
$chunkSize = 5000; // 分块处理 5000查一次 越小内存占用越少
$max = 100000; // 限制最大导出10万数据 太多的话可能会超时 开发环境测试10万30秒左右，生产环境7万40秒，主要是查数据和map处理数据比较耗时，关联地区表和whereHasIn用得比较多
$completed = 0;
$debug = false; // 设为true可以看导出占用和时间情况
$start = microtime(true);
if ($debug) {
// dd($start);
dump('开始：' . memory_get_peak_usage() / 1000 / 1024);
}
do {
/** @var Collection $result */
$result = $callback($times, $chunkSize);
// dd($result->toArray());
$count = count($result);
$completed += $count;
// dd($times,$result,$count);
$this->insertData($result);
unset($result);
if ($debug) {
dump($completed . '：' . (number_format(microtime(true) - $start, 2)) . "-" . memory_get_peak_usage() / 1024000);
} /*else {
dump('已导出：' . $completed . '条，耗时' . (number_format(microtime(true) - $start, 2)) . '秒');
}*/
$times++;
} while ($count === $chunkSize && $completed < $max);
if ($debug) {
dump('数据插入完成，开始导出到文件...');
}
return $this;
}
}
导出效果：
相关文章