PHP code example of aoding9 / laravel-xlswriter-export
1. Go to this page and download the library: Download aoding9/laravel-xlswriter-export library . Choose the download type require .
2. Extract the ZIP file and open the index.php.
3. Add this code to the index.php.
<?php
require_once('vendor/autoload.php');
/* Start to develop here. Best regards https://php-download.com/ */
aoding9 / laravel-xlswriter-export example snippets
namespace Aoding9\Laravel\Xlswriter\Export\Demo;
use Aoding9\Laravel\Xlswriter\Export\BaseExport;
class UserExport extends BaseExport {
public $header = [
['column' => 'a', 'width' => 8, 'name' => '序号'],
['column' => 'b', 'width' => 8, 'name' => 'id'],
['column' => 'c', 'width' => 10, 'name' => '姓名'],
['column' => 'd', 'width' => 10, 'name' => '性别'],
['column' => 'e', 'width' => 20, 'name' => '注册时间'],
];
public $fileName = '用户导出表'; // 导出的文件名
public $tableTitle = '用户导出表'; // 第一行标题
// 将模型字段与表头关联
public function eachRow($row) {
/** @var \App\Models\User $row */
return [
$this->index,
$row->id,
\Faker\Factory::create('zh_CN')->name,
random_int(0, 1) ? '男' : '女',
$row->created_at->toDateTimeString(),
];
}
}
$data = [
['id' => 1, 'name' => '小白', 'created_at' => now()->toDateString()],
['id' => 2, 'name' => '小红', 'created_at' => now()->toDateString()],
];
// $data = User::get()->toArray();
\Aoding9\Laravel\Xlswriter\Export\Demo\UserExportFromCollection::make($data)->export();
\Aoding9\Laravel\Xlswriter\Export\Demo\AreaExportFromCollection::make(\App\Models\Area::query()->limit(500000)->get())->export();
\Aoding9\Laravel\Xlswriter\Export\Demo\UserExportFromCollection::make()->export();
namespace Aoding9\Laravel\Xlswriter\Export\Demo;
use Aoding9\Laravel\Xlswriter\Export\BaseExport;
class UserExportFromCollection extends BaseExport {
public $header = [
['column' => 'a', 'width' => 8, 'name' => '序号'],
['column' => 'b', 'width' => 8, 'name' => 'id'],
['column' => 'c', 'width' => 10, 'name' => '姓名'],
['column' => 'd', 'width' => 10, 'name' => '性别'],
['column' => 'e', 'width' => 20, 'name' => '注册时间'],
];
public $fileName = '用户导出表'; // 导出的文件名
public $tableTitle = '用户导出表'; // 第一行标题
// 将模型字段与表头关联
public function eachRow($row) {
return [
$this->index,
$row['id'],
$row['name'],
random_int(0, 1) ? '男' : '女',
$row['created_at'],
];
}
// 方法2 可以分块获取数据
public function buildData(?int $page = null, ?int $perPage = null) {
return collect([
['id' => 1, 'name' => '小白', 'created_at' => now()->toDateString()],
['id' => 2, 'name' => '小红', 'created_at' => now()->toDateString()],
]);
}
}
namespace Aoding9\Laravel\Xlswriter\Export\Demo;
use Aoding9\Laravel\Xlswriter\Export\BaseExport;
use Illuminate\Support\Carbon;
use Vtiful\Kernel\Format;
class UserMergeExport extends BaseExport {
public $header = [
['column' => 'a', 'width' => 10, 'name' => '序号'],
['column' => 'b', 'width' => 10, 'name' => 'id'],
['column' => 'c', 'width' => 10, 'name' => '姓名'],
['column' => 'd', 'width' => 10, 'name' => '性别'],
['column' => 'e', 'width' => 20, 'name' => '注册时间'],
];
public function getGender() {
return random_int(0, 1) ? '男' : '女';
}
// 处理每行的模型，使其对应到表头
public function eachRow($row) {
return [
$this->index, // 自增序号，绑定在模型中
$row->id,
\Faker\Factory::create('zh_CN')->name,
$this->getGender(),
$row->created_at,
];
}
public $fileName = '用户导出表'; // 导出的文件名
public $tableTitle = '用户导出表'; // 第一行标题
public $useFreezePanes = false; // 是否冻结表头
public $fontFamily = '宋体';
public $rowHeight = 30; // 行高
public $titleRowHeight = 40; // 首行大标题行高
public $headerRowHeight = 50; // 表头行高
public $useGlobalStyle=false; // 是否用全局默认样式代替列默认样式（为ture时，数据末尾行下方没有边框，但是速度会慢一点点）
/**
* @Desc 在分块数据插入每行后回调（到下一个分块，则上一分块被销毁）
* @param $row
*/
public function afterInsertEachRowInEachChunk($row) {
// 奇数行进行合并，且不合并到有效数据行之外
if ($this->index % 2 === 1 && $this->getCurrentLine() < $this->completed + $this->startDataRow) {
// 定义纵向合并范围，范围形如"B1:B2"
$range1 = "B" . $this->getCurrentLine() . ":B" . ($this->getCurrentLine() + 1);
$nextRow = $this->getRowInChunkByIndex($this->index + 1);
$ids = $row->id . '---' . ($nextRow ? $nextRow->id : null);
// mergeCells（范围, 数据, 样式） ，通过第三个参数可以设置合并单元格的字体颜色等
$this->excel->mergeCells($range1, $ids, $this->getSpecialStyle());
// 横向合并，形如"C3:D3"
$range2 = "C" . $this->getCurrentLine() . ":D" . $this->getCurrentLine();
$nameAndGender = $row->name . "---" . $this->getGender();
$this->excel->mergeCells($range2, $nameAndGender);
}
}
public function setHeaderData() {
parent::setHeaderData();
// 把表头放到第三行，第二行留空用于合并
$this->headerData->put(2, $this->headerData->get(1));
$this->headerData->put(1, []);
return $this;
}
/**
* @Desc 插入数据完成后进行合并
* @return array[]
*/
public function mergeCellsAfterInsertData() {
// range是合并范围，$this->end是末尾的列名字母，formatHandle指定合并单元格的样式
return [
['range' => "A1:{$this->end}1", 'value' => $this->getTableTitle(), 'formatHandle' => $this->titleStyle],
['range' => "A2:A3", 'value' => '序号', 'formatHandle' => $this->getSpecialStyle()],
['range' => "B2:B3", 'value' => 'id', 'formatHandle' => $this->headerStyle],
['range' => "C2:E2", 'value' => '基本资料', 'formatHandle' => $this->getSpecialStyle()],
];
}
public $specialStyle;
/**
* 定义个特别的表格样式
* @return resource
*/
public function getSpecialStyle() {
return $this->specialStyle ?: $this->specialStyle = (new Format($this->fileHandle))
->background(Format::COLOR_YELLOW)
->fontSize(10)
->border(Format::BORDER_THIN)
->italic()
->font('微软雅黑')
->align(Format::FORMAT_ALIGN_CENTER, Format::FORMAT_ALIGN_VERTICAL_CENTER)
->wrap()
->toResource();
}
// public $specialStyle2;
// public function getSpecialStyle2() {}
/**
* @Desc 重写插入单元格数据的处理方法，可单独设置某个单元格的样式
* @param int $currentLine 单元格行数
* @param int $column 单元格列数
* @param mixed $data 插入的数据
* @param string|null $format 数据格式化
* @param resource|null $formatHandle 表格样式
* @return \Vtiful\Kernel\Excel
*/
public function insertCellHandle($currentLine, $column, $data, $format, $formatHandle) {
// if($this->getCellName($currentLine,$column)==='A4'){ ... } // 根据单元格名称判断
// 筛选出E列，且日期秒数为偶数的单元格
if ($this->getColumn($column) === 'E' && $data instanceof Carbon) {
if ($data->second % 2 === 0) {
// 设置为上面定义好的样式（黄色背景，斜体，微软雅黑，水平垂直居中等）
$formatHandle = $this->getSpecialStyle();
}
$data = $data->toDateTimeString();
}
return $this->excel->insertText($currentLine, $column, $data, $format, $formatHandle);
}
}
public function exportModels() {
// 定义查询构造器，设置查询条件，如果有关联关系，使用with预加载以优化查询
$query=\App\Models\User::query();
// 将查询构造器传入构造函数，然后调用export即可触发下载
\Aoding9\Laravel\Xlswriter\Export\Demo\UserExport::make($query)->export();
// 合并单元格的demo
\Aoding9\Laravel\Xlswriter\Export\Demo\UserMergeExport::make($query)->export();
// 用数据集合或数组
// 方式1：如果给构造函数传数组或集合，必须把数据全部传入
$data = [
['id' => 1, 'name' => '小白', 'created_at' => now()->toDateString()],
['id' => 2, 'name' => '小红', 'created_at' => now()->toDateString()],
];
// $data = \App\Models\User::get()->toArray();
\Aoding9\Laravel\Xlswriter\Export\Demo\UserExportFromCollection::make($data)->export();
// 方式2：无需传参给构造函数，但需要重写buildData方法，分块返回数据
\Aoding9\Laravel\Xlswriter\Export\Demo\UserExportByCollection::make()->export();
// 地区导出的demo
// 用于调试模式查看运行耗时，包含数据查询耗费的时间
$time =microtime(true);
// 用查询构造器
$query=\App\Models\Area::where('parent_code',0); // 查父级为0的地区，即查省份
\Aoding9\Laravel\Xlswriter\Export\Demo\AreaExport::make($query,$time)->export();
// 用数组或集合
// 数据量大时占用很高，需要修改内存上限，不推荐
ini_set('memory_limit', '2048M');
set_time_limit(0);
$data =\App\Models\Area::query()->limit(500000)->get();
\Aoding9\Laravel\Xlswriter\Export\Demo\AreaExportFromCollection::make($data,$time)->export();
}
// UserExport
public $useSwoole = true;
// UserController
return UserExport::make()->export();