<?php
|
|
namespace app\common\toole;
|
|
/**
|
* 导出Excel封装
|
* author 锦鲤来了(1197185312@qq.com)
|
*/
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
|
use PhpOffice\PhpSpreadsheet\Style\Alignment;
|
use PhpOffice\PhpSpreadsheet\Style\Border;
|
use PhpOffice\PhpSpreadsheet\Style\Fill;
|
|
class ExportExcel
|
{
|
private $seller_id;
|
private $type;
|
private $form_page;
|
public function __construct($config = [])
|
{
|
$this->seller_id = $config['seller_id'] ?? 0;;
|
$this->type = $config['type'] ?? '';
|
$this->form_page = $config['form_page'] ?? '';
|
}
|
public function getExportUrl($orders, $headers = [])
|
{
|
$exportDir = 'static/export/';
|
if (!file_exists($exportDir)) {
|
mkdir($exportDir, 0755, true);
|
}
|
$spreadsheet = new Spreadsheet();
|
$sheet = $spreadsheet->getActiveSheet();
|
|
// // 设置文档属性
|
// $spreadsheet->getProperties()
|
// ->setCreator("Export System")
|
// ->setTitle("Orders Export")
|
// ->setSubject("Orders Data");
|
$sheet->fromArray($headers, null, 'A1');
|
$sheet = $spreadsheet->getActiveSheet();
|
//$sheet->setTitle('发货订单信息');
|
if (!$headers) {
|
$headers = $orders[0];
|
}
|
$field_count = count($headers);
|
//从headers中获取字段名
|
$fields = array_keys($headers);
|
$row = 1;
|
// 将查询结果写入电子表格
|
foreach ($orders as $item) {
|
$rowNumber = $sheet->getHighestDataRow() + 1; // 获取当前行号并加1以开始新行。
|
$sheet->fromArray($item, NULL, 'A' . $rowNumber); // 从数组写入行数据，'A' . $rowNumber指定起始单元格位置。
|
$row++;
|
}
|
// 将查询结果写入电子表格
|
$row = 2; // 从第二行开始写入数据
|
foreach ($orders as $item) {
|
for ($i = 0; $i < $field_count; $i++) {
|
//$cell = $sheet->getCellByColumnAndRow($i + 1, $row);
|
//修复升级到2.0以上报错
|
$columnLetter = chr(65 + $i);
|
$cell = $sheet->getCell($columnLetter . $row);
|
$field = $fields[$i];
|
$value = isset($item[$field]) ? $item[$field] : '';
|
// 检查值是否为数字，并显式设置数据类型
|
if (is_numeric($value)) {
|
$cell->setValueExplicit($value, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC);
|
} else {
|
$cell->setValue($value);
|
}
|
}
|
$row++;
|
}
|
|
// 设置样式
|
$this->applySheetStyle($sheet, $field_count, $row - 1);
|
|
// 保存文件
|
$writer = new Xlsx($spreadsheet);
|
$filename = $exportDir . '/orders_' . Date('md') . time() . '.xlsx';
|
$writer->save($filename);
|
$excel_url = '/' . $filename;
|
$res = ['code' => 1, 'msg' => '导出成功', 'url' => $excel_url];
|
return $res;
|
}
|
// 应用表格样式
|
protected function applySheetStyle($sheet, $colCount, $rowCount)
|
{
|
// 设置表头样式
|
$headerStyle = [
|
'font' => [
|
'bold' => true,
|
'color' => ['rgb' => 'FFFFFF']
|
],
|
'fill' => [
|
'fillType' => Fill::FILL_SOLID,
|
'color' => ['rgb' => '4472C4']
|
],
|
'alignment' => [
|
'horizontal' => Alignment::HORIZONTAL_CENTER,
|
'vertical' => Alignment::VERTICAL_CENTER
|
],
|
'borders' => [
|
'allBorders' => [
|
'borderStyle' => Border::BORDER_THIN
|
]
|
]
|
];
|
|
$sheet->getStyle('A1:' . chr(64 + $colCount) . '1')
|
->applyFromArray($headerStyle);
|
|
// 设置数据区域样式
|
$dataStyle = [
|
'alignment' => [
|
'vertical' => Alignment::VERTICAL_CENTER
|
],
|
'borders' => [
|
'allBorders' => [
|
'borderStyle' => Border::BORDER_THIN
|
]
|
]
|
];
|
|
$sheet->getStyle('A2:' . chr(64 + $colCount) . ($rowCount + 1))
|
->applyFromArray($dataStyle);
|
|
// 设置自动列宽
|
for ($i = 0; $i < $colCount; $i++) {
|
$sheet->getColumnDimension(chr(65 + $i))->setAutoSize(true);
|
}
|
|
// 设置行高
|
$sheet->getDefaultRowDimension()->setRowHeight(20);
|
}
|
}
|
