Maatwebsite 3.1 导出Excel 合并单元格等设置
废话不多说，直接看代码
创建一个导出类，里面实现设置需要的一些方法
<?php
namespace App\Exports;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Style\Border;
class InvoiceStatisticsExports implements FromCollection,WithHeadings,WithEvents
{
protected $data;
protected $headings;
protected $columnWidth = [];//设置列宽 key：列 value:宽
protected $rowHeight = []; //设置行高 key：行 value:高
protected $mergeCells = []; //合并单元格 value:A1:K8
protected $font = []; //设置字体 key：A1:K8 value:Arial
protected $fontSize = []; //设置字体大小 key：A1:K8 value:11
protected $bold = []; //设置粗体 key：A1:K8 value:true
protected $background = []; //设置背景颜色 key：A1:K8 value:#F0F0F0F
protected $vertical = []; //设置定位 key：A1:K8 value:center
protected $sheetName; //sheet title
protected $borders = []; //设置边框颜色 key：A1:K8 value:#000000
protected $fontColor = []; // 设置字体颜色
//设置页面属性时如果无效 更改excel格式尝试即可
//构造函数传值
public function __construct($data, $headings,$sheetName)
{
$this->data = $data;
$this->headings = $headings;
$this->sheetName = $sheetName;
$this->createData();
}
public function headings(): array
{
return $this->headings;
}
//数组转集合
public function collection()
{
return new Collection($this->data);
}
//业务代码
public function createData()
{
$this->data = collect($this->data)->toArray();
}
public function registerEvents(): array
{
return [
AfterSheet::class => function(AfterSheet $event) {
//设置区域单元格垂直居中
$event->sheet->getDelegate()->getStyle('A1:Z1265')->getAlignment()->setVertical('center');
//设置区域单元格水平居中
$event->sheet->getDelegate()->getStyle('A1:Z1265')->getAlignment()->setHorizontal('center');
//设置列宽
foreach ($this->columnWidth as $column => $width) {
$event->sheet->getDelegate()
->getColumnDimension($column)
->setWidth($width);
}
//设置行高，$i为数据行数
foreach ($this->rowHeight as $row => $height) {
$event->sheet->getDelegate()
->getRowDimension($row)
->setRowHeight($height);
}
//设置区域单元格垂直居中
foreach ($this->vertical as $region => $position) {
$event->sheet->getDelegate()
->getStyle($region)
->getAlignment()
->setVertical($position);
}
//设置区域单元格字体
foreach ($this->font as $region => $value) {
$event->sheet->getDelegate()
->getStyle($region)
->getFont()->setName($value);
}
// 设置字体颜色
foreach ($this->fontColor as $region => $value) {
$event->sheet->getDelegate()
->getStyle($region)
->getFont()->setColor($value);
}
//设置区域单元格字体大小
foreach ($this->fontSize as $region => $value) {
$event->sheet->getDelegate()
->getStyle($region)
->getFont()
->setSize($value);
}
//设置区域单元格字体粗体
foreach ($this->bold as $region => $bool) {
$event->sheet->getDelegate()
->getStyle($region)
->getFont()
->setBold($bool);
}
//设置区域单元格背景颜色
foreach ($this->background as $region => $item) {
$event->sheet->getDelegate()->getStyle($region)->applyFromArray([
'fill' => [
'fillType' => 'linear', //线性填充，类似渐变
'startColor' => [
'rgb' => $item //初始颜色
],
//结束颜色，如果需要单一背景色，请和初始颜色保持一致
'endColor' => [
'argb' => $item
]
]
]);
}
//设置边框颜色
foreach ($this->borders as $region => $item) {
$event->sheet->getDelegate()->getStyle($region)->applyFromArray([
'borders' => [
'allBorders' => [
'borderStyle' =>Border::BORDER_THIN,
'color' => ['argb' => $item],
],
],
]);
}
//合并单元格
$event->sheet->getDelegate()->setMergeCells($this->mergeCells);
if(!empty($this->sheetName)){
$event->sheet->getDelegate()->setTitle($this->sheetName);
}
}
];
}
/**
* @return array
* [
* 'B' => 40,
* 'C' => 60
* ]
*/
public function setColumnWidth (array $columnwidth)
{
$this->columnWidth = array_change_key_case($columnwidth, CASE_UPPER);
}
/**
* @return array
* [
* 1 => 40,
* 2 => 60
* ]
*/
public function setRowHeight (array $rowHeight)
{
$this->rowHeight = $rowHeight;
}
/**
* @return array
* [
* A1:K7 => '宋体'
* ]
*/
public function setFont (array $font)
{
$this->font = array_change_key_case($font, CASE_UPPER);
}
/**
* @return array
* @2020/3/22 10:33
* [
* A1:K7 => true
* ]
*/
public function setBold (array $bold)
{
$this->bold = array_change_key_case($bold, CASE_UPPER);
}
/**
* @return array
* @2020/3/22 10:33
* [
* A1:K7 => F0FF0F
* ]
*/
public function setBackground (array $background)
{
$this->background = array_change_key_case($background, CASE_UPPER);
}
/**
* 合并单元格
* @return array
* [
* A1:K7
* ]
*/
public function setMergeCells (array $mergeCells)
{
$this->mergeCells = array_change_key_case($mergeCells, CASE_UPPER);
}
/**
* @return array
* [
* A1:K7 => 14
* ]
*/
public function setFontSize (array $fontSize)
{
$this->fontSize = array_change_key_case($fontSize, CASE_UPPER);
}
/**
* 设置边框颜色
* [
* A1:K7 => #000000
* ]
*/
public function setBorders (array $borders)
{
$this->borders = array_change_key_case($borders, CASE_UPPER);
}
/**
* @description 设置字体颜色
* @param array $fontColor ['A1' => new Color('FF0000'), 'B1:D1' => new Color('FF0000')]
* @author: zhangyf
* @create_at: 2024/4/22 16:41
*/
public function setFontColor(array $fontColor)
{
$this->fontColor = array_change_key_case($fontColor, CASE_UPPER);
}
}
数据组装导出，直接看代码
/**
* @description 发票统计数据导出excel
* @param array $condition
* @return \Symfony\Component\HttpFoundation\BinaryFileResponse
* @throws JsonException
* @throws \App\Exceptions\ApiException
* @author: zhangyf
* @create_at: 2024/4/22 14:04
*/
public function exportStatisticsExcel(array $condition)
{
// 获取统计的数据
$data = $this->api->getUsersInvoiceStatistics([
'query' => $condition
]);
$excel_data = Helper::checkApiResponse($data);
//设置表头
$header = [
[
"title" => '发票审核数据明细表',
]
];
//数据 只管data中对应的value就行，渲染在对应的单元格
$elec_s = ($excel_data['general_person_elec'] + $excel_data['general_public_elec']);
$paper_s = ($excel_data['general_person_paper'] + $excel_data['general_public_paper']);
$all_p = ($elec_s + $paper_s);
$all_s = ($excel_data['special_public_elec'] + $excel_data['special_public_paper']);
$p_elec = ($excel_data['general_public_elec'] + $excel_data['special_public_elec']);
$p_paper = ($excel_data['general_public_paper'] + $excel_data['special_public_paper']);
$all = ($all_p + $all_s);
$all_public = $p_elec + $p_paper;
$person_elec_paper = $excel_data['general_person_elec'] + $excel_data['general_person_paper'];
$data = [
0 => [
"title" => '发票申请种类',
"person" => '个人',
"person_c" => '',
"public" => '对公',
"public_c" => '',
"elec_s" => '电子小计',
"paper_s" => '纸质小计',
"all_s" => '合计'
],
1 => [
"title" => '',
"person_elec" => '电子',
"person_paper" => '纸质',
"public_elec" => '电子',
"public_paper" => '纸质',
"title1" => '',
"title2" => '',
"title3" => ''
],
2 => [
"title" => '增值税普通发票',
"general_person_elec" => $excel_data['general_person_elec'],
"general_person_paper" => $excel_data['general_person_paper'],
"general_public_elec" => $excel_data['general_public_elec'],
"general_public_paper" => $excel_data['general_public_paper'],
"elec_s" => $elec_s . "",
"paper_s" => $paper_s . "",
"all_p" => $all_p . ""
],
3 => [
"title" => '增值税专用发票',
"special_person_elec" => '/',
"special_person_paper" => '/',
"special_public_elec" => $excel_data['special_public_elec'],
"special_public_paper" => $excel_data['special_public_paper'],
"elec_s" => $excel_data['special_public_elec'],
"paper_s" => $excel_data['special_public_paper'],
"all_s" => $all_s . ""
],
4 => [
"title" => '小计',
"person_elec" => $excel_data['general_person_elec'],
"person_paper" => $excel_data['general_person_paper'],
"public_elec" => $p_elec . "",
"public_paper" => $p_paper . "",
"elec_s" => $elec_s . "",
"paper_s" => $paper_s . "",
"all_s" => $all ?? "0"
],
5 => [
"title" => '合计',
"person_elec_paper" => $person_elec_paper ."",
"person_elec_paper_c" => "",
"public_elec_paper" => $all_public ."",
"public_elec_paper_c" => ' ',
"elec_and_paper" => ($elec_s+$paper_s),
"elec_and_paper_c" => '',
"all_s" => ''
]
];
$excel = new InvoiceStatisticsExports($data, $header, '导出sheetName');
// 设置列宽
$excel->setColumnWidth(['A' => 20]);
// 设置行高
$excel->setRowHeight([1 => 40]);
// 设置字体格式
$excel->setFont(['A1:Z1265' => '宋体']);
// 设置字体大小
$excel->setFontSize(['A1:H1' => 14, 'A2:H7265' => 10]);
// 设置边框
$excel->setBold(['A1:H7' => true]);
// 合并单元格
$excel->setMergeCells(['A1:H1', 'A2:A3' , 'B2:C2', 'D2:E2', 'F2:F3', 'G2:G3', 'H2:H3', 'B7:C7', 'D7:E7', 'F7:G7', 'H6:H7']);
// 设置边框颜色
$excel->setBorders(['A1:H7' => '#00000']);
// 设置字体颜色
$red = new Color('FF0000');
$excel->setFontColor(['A7:G7265' => $red, 'H2:H7265' => $red]);
return Excel::download($excel, '导出文件-'.date('YmdHis').'.xlsx');
}
实际导出效果
2897
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
