PHPxlswriter支持无限表头层级Excel导出
本章介绍基于PHP扩展xlswriter的Vtiful\Kernel\Excel类可以支持无限层级的复杂表头导出!
废了九牛二虎之力,终于把这个功能类写完了…后续会持续更新优化
准备xlswriter扩展
windows系统:
到PECL网站下载符合自己本地PHP环境的ddl文件下载地址,并复制到PHP的扩展目录ext文件夹下,修改php.ini文件,加上这行
extension=xlswriter
打开phpinfo()验证扩展是否安装成功
Linux系统:
pecl install xlswriter
php配置文件添加
extension = xlswriter.so
重启php nginx
composer下载phpoffice/phpexcel
因为有用到单元格相关函数,所以需要执行下列命令
composer require phpoffice/phpexcel 1.8
封装导出类文件(重点来了)
- 支持多层表头
- 支持多Sheet
- 支持过滤选项
- 支持单元格格式
- 支持单元格公式
- 支持表头加粗
- 支持表头斜体
- 支持冻结表头
- 支持插入图片
- 支持表头居中
- 支持锁定保护
- 支持数据合并
- 支持数据背景颜色
-
-
use PHPExcel_Cell;
-
-
class MultiFloorXlsWriterService
-
{
-
// 默认宽度
-
private $defaultWidth = 16;
-
// 默认高度
-
private $defaultHeight = 15;
-
// 默认导出格式
-
private $exportType = '.xlsx';
-
// 表头最大层级
-
private $maxHeight = 1;
-
// 文件名
-
private $fileName = null;
-
// 默认公式行距离数据间隔
-
private $defaultFormulaTop = 2;
-
// 数据占用截至行
-
private $maxDataLine = 2;
-
// 默认的单元格格式,常规
-
private $defaultCellFormat = 'general';
-
// 支持的单元格格式,可扩充
-
private $allowCellFormat = [
-
'general' => \PHPExcel_Style_NumberFormat::FORMAT_GENERAL,
-
'text' => \PHPExcel_Style_NumberFormat::FORMAT_TEXT,
-
];
-
// 支持的单元列操作-数据合并
-
const CELL_ACT_MERGE = 'merge';
-
// 支持的单元列操作-背景颜色
-
const CELL_ACT_BACKGROUND = 'background';
-
// 数据合并开始标识
-
const ACT_MERGE_START = 'start';
-
// 数据合并结束标识
-
const ACT_MERGE_END = 'end';
-
private $allowCellActs = [
-
self::CELL_ACT_MERGE,
-
self::CELL_ACT_BACKGROUND,
-
];
-
// 单元格操作集合
-
private $cellActs = [];
-
-
private $xlsObj;
-
private $fileObject;
-
private $format;
-
private $boldIStyle;
-
private $colManage;
-
private $lastColumnCode;
-
-
public function __construct()
-
{
-
// 文件默认输出地址
-
$path = base_path().'/storage/logs';
-
$config = [
-
'path' => $path
-
];
-
-
$this->xlsObj = (new \Vtiful\Kernel\Excel($config));
-
}
-
-
/**
-
* 设置文件名
-
* @param string $fileName 文件名
-
* @param string $sheetName 第一个sheet名
-
*/
-
public function setFileName(string $fileName = '', string $sheetName = 'Sheet1')
-
{
-
$fileName = empty($fileName) ? (string)time() : $fileName;
-
$fileName .= $this->exportType;
-
-
$this->fileName = $fileName;
-
-
$this->fileObject = $this->xlsObj->fileName($fileName, $sheetName);
-
$this->format = (new \Vtiful\Kernel\Format($this->fileObject->getHandle()));
-
}
-
-
/**
-
* 设置表头
-
* @param array $header
-
* @throws \Exception
-
*/
-
public function setHeader(array $header)
-
{
-
if (empty($header)) {
-
throw new \Exception('表头数据不能为空');
-
}
-
-
if (is_null($this->fileName)) {
-
self::setFileName(time());
-
}
-
-
// 获取单元格合并需要的信息
-
$colManage = self::setHeaderNeedManage($header);
-
-
// 完善单元格合并信息
-
$this->colManage = self::completeColMerge($colManage);
-
-
// 设置最后单元格标识
-
$this->lastColumnCode = self::getColumn(end($this->colManage)['cursorEnd']) . $this->maxHeight;
-
-
// 合并单元格
-
self::queryMergeColumn();
-
}
-
-
/**
-
* 填充文件数据
-
* @param array $data
-
* @throws \Exception
-
*/
-
public function setData(array $data)
-
{
-
// 起始行
-
$indexRow = $this->maxHeight 1;
-
// 起始列
-
$indexCol = 0;
-
foreach ($data as $row => $datum) {
-
foreach ($datum as $column => $value) {
-
// 列值为数组,说明有额外操作
-
if (is_array($value)) {
-
$val = $value[0];
-
$act = $value[1];
-
$pos = self::getColumn($indexCol) . $indexRow;
-
// 有效行为
-
$availableActs = array_intersect($this->allowCellActs, array_keys($act));
-
foreach ($availableActs as $availableAct) {
-
switch ($availableAct) {
-
case self::CELL_ACT_MERGE:
-
// 数据合并
-
$this->cellActs[$indexCol][self::CELL_ACT_MERGE][$act[$availableAct]] = $pos;
-
$this->cellActs[$indexCol][self::CELL_ACT_MERGE]['val'] = $val;
-
break;
-
case self::CELL_ACT_BACKGROUND:
-
// 背景颜色
-
$this->cellActs[$indexCol][self::CELL_ACT_BACKGROUND][] = [
-
'row' => $row,
-
'column' => $column,
-
'color' => $act[$availableAct],
-
'val' => $val
-
];
-
break;
-
default:
-
throw new \Exception('不支持的单元格操作['. $availableAct .']');
-
}
-
}
-
} else {
-
$this->fileObject->insertText($row $this->maxHeight, $column, $value);
-
}
-
$indexCol ;
-
}
-
$indexRow ;
-
$indexCol = 0;
-
}
-
// 执行单元格操作
-
self::queryCellActs();
-
$this->maxDataLine = $this->maxHeight count($data);
-
}
-
-
/**
-
* 添加Sheet
-
* @param string $sheetName
-
*/
-
public function addSheet(string $sheetName)
-
{
-
$this->fileObject->addSheet($sheetName);
-
}
-
-
/**
-
* 设置公式
-
* {start}:数据开始行 {end}:数据结束行
-
* col_title:公式标题所在列标识,从0开始
-
* title:公式标题
-
* col_formula:公式结果所在列标识
-
* formula:公式内容
-
* @param array $formulas
-
* @throws \Exception
-
*/
-
public function setFormula(array $formulas)
-
{
-
if (empty($formulas)) {
-
throw new \Exception('公式格式错误');
-
}
-
-
$line = $this->maxDataLine $this->defaultFormulaTop;
-
-
foreach ($formulas as $formula) {
-
if (isset($formula['col_title']) && isset($formula['title'])) {
-
$this->fileObject->insertText($line, $formula['col_title'], $formula['title']);
-
}
-
-
if (!isset($formula['col_formula']) || !isset($formula['formula']) || empty($formula['formula'])) {
-
throw new \Exception('公式格式错误');
-
}
-
-
$formula['formula'] = str_ireplace('{start}', $this->maxHeight 1, $formula['formula']);
-
$formula['formula'] = str_ireplace('{end}', $this->maxDataLine, $formula['formula']);
-
-
$this->fileObject->insertFormula($line, $formula['col_formula'], $formula['formula']);
-
}
-
}
-
-
/**
-
* 设置公式行距离数据间隔
-
* @param $top
-
*/
-
public function reBuildFormulaTop(int $top)
-
{
-
$this->defaultFormulaTop = $top;
-
}
-
-
/**
-
* 插入本地图片
-
* @param int $row
-
* @param int $column
-
* @param string $localImagePath
-
* @param float|int $widthScale
-
* @param float|int $heightScale
-
* @throws \Exception
-
*/
-
public function setImage(int $row, int $column, string $localImagePath, float $widthScale = 1, float $heightScale = 1)
-
{
-
if (!file_exists($localImagePath)) {
-
throw new \Exception("未检测到图片{$localImagePath}");
-
}
-
$this->fileObject->insertImage($row, $column, $localImagePath, $widthScale, $heightScale);
-
}
-
-
/**
-
* 冻结表头(需放到setHeader后调用)
-
*/
-
public function setFreezeHeader()
-
{
-
$this->fileObject->freezePanes($this->maxHeight, 0);
-
}
-
-
/**
-
* 开启过滤选项(需放到setHeader后调用)
-
*/
-
public function setFilter()
-
{
-
$this->fileObject->autoFilter("A1:{$this->lastColumnCode}");
-
}
-
-
/**
-
* 设置表头加粗(需放到setHeader后调用)
-
*/
-
public function setBoldHeader()
-
{
-
$this->boldIStyle = $this->format->bold()->toResource();
-
$this->fileObject->setRow("A1:{$this->lastColumnCode}", $this->defaultHeight, $this->boldIStyle);
-
}
-
-
/**
-
* 设置表头斜体(需放到setHeader后调用)
-
*/
-
public function setItalicHeader()
-
{
-
$this->boldIStyle = $this->format->italic()->toResource();
-
$this->fileObject->setRow("A1:{$this->lastColumnCode}", $this->defaultHeight, $this->boldIStyle);
-
}
-
-
/**
-
* 设置表头水平居中对齐(需放到setHeader后调用)
-
*/
-
public function setAlignCenterHeader()
-
{
-
$this->boldIStyle = $this->format->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_CENTER, \Vtiful\Kernel\Format::FORMAT_ALIGN_VERTICAL_CENTER)->toResource();
-
}
-
-
/**
-
* 文件密码保护
-
* @param $password
-
*/
-
public function setFileProtection($password = null)
-
{
-
$this->fileObject->protection($password);
-
}
-
-
/**
-
* 保存文件至服务器
-
*/
-
public function output()
-
{
-
return $this->fileObject->output();
-
}
-
-
/**
-
* 输出到浏览器
-
* @param $filePath
-
* @throws \Exception
-
*/
-
public function excelDownload($filePath)
-
{
-
$fileName = $this->fileName;
-
$userBrowser = $_SERVER['HTTP_USER_AGENT'];
-
if( preg_match('/MSIE/i', $userBrowser)) {
-
$fileName = urlencode($fileName);
-
} else {
-
$fileName = iconv('UTF-8', 'GBK//IGNORE', $fileName);
-
}
-
-
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
-
header('Content-Disposition: attachment;filename="' . $fileName . '"');
-
header('Content-Length: ' . filesize($filePath));
-
header('Content-Transfer-Encoding: binary');
-
header('Cache-Control: must-revalidate');
-
header('Cache-Control: max-age=0');
-
header('Pragma: public');
-
-
if (ob_get_contents()) {
-
ob_clean();
-
}
-
-
flush();
-
-
if (copy($filePath, 'php://output') === false) {
-
throw new \Exception($filePath. '地址出问题了');
-
}
-
-
// 删除本地文件
-
@unlink($filePath);
-
-
exit();
-
}
-
-
/**
-
* 组装单元格合并需要的信息
-
* @param $header
-
* @param int $cursor
-
* @param int $col
-
* @param array $colManage
-
* @param $parentList
-
* @param $parent
-
* @throws \Exception
-
* @return array
-
*/
-
private function setHeaderNeedManage($header, $col = 1, &$cursor = 0, &$colManage = [], $parent = null, $parentList = [])
-
{
-
foreach ($header as $head) {
-
if (empty($head['title'])) {
-
throw new \Exception('表头数据格式有误');
-
}
-
-
if (is_null($parent)) {
-
// 循环初始化
-
$parentList = [];
-
$col = 1;
-
} else {
-
// 递归进入,高度和父级集合通过相同父级条件从已有数组中获取,避免递归增加与实际数据不符
-
foreach ($colManage as $value) {
-
if ($value['parent'] == $parent) {
-
$parentList = $value['parentList'];
-
$col = $value['height'];
-
break;
-
}
-
}
-
}
-
-
// 单元格标识
-
$column = $this->getColumn($cursor) . $col;
-
-
// 单元格格式
-
$format = $this->allowCellFormat[$this->defaultCellFormat];
-
if (!empty($head['format'])) {
-
if (!isset($this->allowCellFormat[$head['format']])) {
-
throw new \Exception("不支持的单元格格式{$head['format']}");
-
}
-
$format = $this->allowCellFormat[$head['format']];
-
}
-
-
// 组装单元格需要的各种信息
-
$colManage[$column] = [
-
'title' => $head['title'], // 标题
-
'cursor' => $cursor, // 游标
-
'cursorEnd' => $cursor, // 结束游标
-
'height' => $col, // 高度
-
'width' => $this->defaultWidth, // 宽度
-
'format' => $format, // 单元格格式
-
'mergeStart' => $column, // 合并开始标识
-
'hMergeEnd' => $column, // 横向合并结束标识
-
'zMergeEnd' => $column, // 纵向合并结束标识
-
'parent' => $parent, // 父级标识
-
'parentList' => $parentList, // 父级集合
-
];
-
-
if (!empty($head['children']) && is_array($head['children'])) {
-
// 有下级,高度加一
-
$col = 1;
-
// 当前标识加入父级集合
-
$parentList[] = $column;
-
-
$this->setHeaderNeedManage($head['children'], $col, $cursor,$colManage, $column, $parentList);
-
} else {
-
// 没有下级,游标加一
-
$cursor = 1;
-
}
-
}
-
-
return $colManage;
-
}
-
-
/**
-
* 完善单元格合并信息
-
* @param $colManage
-
* @return array
-
*/
-
private function completeColMerge($colManage)
-
{
-
$this->maxHeight = max(array_column($colManage, 'height'));
-
$parentManage = array_column($colManage, 'parent');
-
-
foreach ($colManage as $index => $value) {
-
// 设置横向合并结束范围:存在父级集合,把所有父级的横向合并结束范围设置为当前单元格
-
if (!is_null($value['parent']) && !empty($value['parentList'])) {
-
foreach ($value['parentList'] as $parent) {
-
$colManage[$parent]['hMergeEnd'] = self::getColumn($value['cursor']) . $colManage[$parent]['height'];
-
$colManage[$parent]['cursorEnd'] = $value['cursor'];
-
}
-
}
-
-
// 设置纵向合并结束范围:当前高度小于最大高度 且 不存在以当前单元格标识作为父级的项
-
$checkChildren = array_search($index, $parentManage);
-
if ($value['height'] < $this->maxHeight && !$checkChildren) {
-
$colManage[$index]['zMergeEnd'] = self::getColumn($value['cursor']) . $this->maxHeight;
-
}
-
}
-
-
return $colManage;
-
}
-
-
/**
-
* 合并单元格
-
*/
-
private function queryMergeColumn()
-
{
-
foreach ($this->colManage as $value) {
-
$this->fileObject->mergeCells("{$value['mergeStart']}:{$value['zMergeEnd']}", $value['title']);
-
$this->fileObject->mergeCells("{$value['mergeStart']}:{$value['hMergeEnd']}", $value['title']);
-
-
// 设置单元格需要的宽度
-
if ($value['cursor'] != $value['cursorEnd']) {
-
$value['width'] = ($value['cursorEnd'] - $value['cursor'] 1) * $this->defaultWidth;
-
}
-
-
// 设置单元格格式
-
$formatCell = (new \Vtiful\Kernel\Format($this->fileObject->getHandle()));
-
$boldStyle = $formatCell->number($value['format'])->toResource();
-
-
// 设置列单元格样式
-
$toColumnStart = self::getColumn($value['cursor']);
-
$toColumnEnd = self::getColumn($value['cursorEnd']);
-
$this->fileObject->setColumn("{$toColumnStart}:{$toColumnEnd}", $value['width'], $boldStyle);
-
}
-
}
-
-
/**
-
* 执行单元格操作
-
*/
-
private function queryCellActs()
-
{
-
if (!empty($this->cellActs)) {
-
foreach ($this->cellActs as $actNote) {
-
$tmpActStyle = (new \Vtiful\Kernel\Format($this->fileObject->getHandle()));
-
// 背景颜色
-
if (isset($actNote[self::CELL_ACT_BACKGROUND])) {
-
foreach ($actNote[self::CELL_ACT_BACKGROUND] as $item) {
-
// 支持颜色常量
-
$tmpActStyle->background($this->backgroundConst($item['color']));
-
$this->fileObject->insertText($item['row'] $this->maxHeight, $item['column'], $item['val'], '', $tmpActStyle->toResource());
-
}
-
}
-
// 数据合并
-
if (isset($actNote[self::CELL_ACT_MERGE])) {
-
if (!empty($actNote[self::CELL_ACT_MERGE][self::ACT_MERGE_START]) && !empty($actNote[self::CELL_ACT_MERGE][self::ACT_MERGE_END])) {
-
// 合并样式:水平左对齐,垂直居中对齐
-
$tmpActStyle->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_LEFT, \Vtiful\Kernel\Format::FORMAT_ALIGN_VERTICAL_CENTER);
-
$this->fileObject->mergeCells(
-
"{$actNote[self::CELL_ACT_MERGE][self::ACT_MERGE_START]}:{$actNote[self::CELL_ACT_MERGE][self::ACT_MERGE_END]}",
-
$actNote[self::CELL_ACT_MERGE]['val'],
-
$tmpActStyle->toResource()
-
);
-
}
-
}
-
}
-
$this->cellActs = [];
-
}
-
}
-
-
/**
-
* 颜色常量转换
-
* @param $color
-
* @return mixed
-
*/
-
private function backgroundConst($color)
-
{
-
$const = [
-
'black' => \Vtiful\Kernel\Format::COLOR_BLACK, // 黑色
-
'blue' => \Vtiful\Kernel\Format::COLOR_BLUE, // 蓝色
-
'brown' => \Vtiful\Kernel\Format::COLOR_BROWN, // 棕色
-
'cyan' => \Vtiful\Kernel\Format::COLOR_CYAN, // 青色
-
'gray' => \Vtiful\Kernel\Format::COLOR_GRAY, // 灰色
-
'green' => \Vtiful\Kernel\Format::COLOR_GREEN, // 绿色
-
'lime' => \Vtiful\Kernel\Format::COLOR_LIME, // 石灰
-
'magenta' => \Vtiful\Kernel\Format::COLOR_MAGENTA, // 洋红
-
'navy' => \Vtiful\Kernel\Format::COLOR_NAVY, // 深蓝
-
'orange' => \Vtiful\Kernel\Format::COLOR_ORANGE, // 橙色
-
'pink' => \Vtiful\Kernel\Format::COLOR_PINK, // 粉红
-
'purple' => \Vtiful\Kernel\Format::COLOR_PURPLE, // 紫色
-
'red' => \Vtiful\Kernel\Format::COLOR_RED, // 红色
-
'silver' => \Vtiful\Kernel\Format::COLOR_SILVER, // 银色
-
'white' => \Vtiful\Kernel\Format::COLOR_WHITE, // 白色
-
'yellow' => \Vtiful\Kernel\Format::COLOR_YELLOW, // 黄色
-
];
-
-
return $const[$color] ?? $color;
-
}
-
-
/**
-
* 获取单元格列标识
-
* @param $num
-
* @return string
-
*/
-
private function getColumn($num)
-
{
-
return PHPExcel_Cell::stringFromColumnIndex($num);
-
}
-
-
}
-
使用示例
header头规则 title表示列标题,children表示子列,没有子列children可不写或为空
-
$header = [
-
[
-
'title' => '一级表头1',
-
'children' => [
-
[
-
'title' => '二级表头1',
-
],
-
[
-
'title' => '二级表头2',
-
],
-
[
-
'title' => '二级表头3',
-
],
-
]
-
],
-
[
-
'title' => '公式测试',
-
],
-
[
-
'title' => '一级表头3',
-
'children' => [
-
[
-
'title' => '二级表头1',
-
'children' => [
-
[
-
'title' => '三级表头1',
-
],
-
[
-
'title' => '三级表头2',
-
],
-
]
-
],
-
[
-
'title' => '公式测试',
-
],
-
[
-
'title' => '二级表头3',
-
'children' => [
-
[
-
'title' => '三级表头1',
-
'children' => [
-
[
-
'title' => '四级表头1',
-
'children' => [
-
[
-
'title' => '五级表头1',
-
],
-
[
-
'title' => '五级表头2',
-
]
-
]
-
],
-
[
-
'title' => '四级表头2'
-
]
-
]
-
],
-
[
-
'title' => '三级表头2',
-
],
-
]
-
]
-
]
-
],
-
[
-
'title' => '一级表头4',
-
'format' => 'text',
-
],
-
[
-
'title' => '一级表头5',
-
'format' => 'text',
-
],
-
];
-
-
$data = [];
-
for ($i = 0; $i < 35; $i ) {
-
// 数据合并
-
$merge = '这是第'. $i .'行测试';
-
if ($i == 10) {
-
$merge = [
-
'数据合并测试',
-
['merge' => 'start']
-
];
-
}
-
if ($i == 30) {
-
$merge = [
-
'数据合并测试',
-
['merge' => 'end']
-
];
-
}
-
-
// 数据合并 背景颜色
-
$megBack = '这是第'. $i .'行测试';
-
if ($i == 0) {
-
$megBack = [
-
'数据合并 背景颜色测试',
-
['merge' => 'start']
-
];
-
}
-
if ($i == 20) {
-
$megBack = [
-
'数据合并 背景颜色',
-
['merge' => 'end', 'background' => 'brown']
-
];
-
}
-
-
// 单个单元格背景颜色
-
$testBack = '这是第'. $i .'行测试';
-
if ($i == 10) {
-
$testBack = [
-
'单行单列背景颜色测试',
-
['background' => 'orange']
-
];
-
}
-
-
// RGB十六进制颜色
-
$rgbColor = '这是第'. $i .'行测试';
-
if ($i == 15) {
-
$rgbColor = [
-
'RGB十六进制颜色测试',
-
['background' => 0xFDB8]
-
];
-
}
-
-
$data[] = [
-
$merge,
-
'这是第'. $i .'行测试',
-
$megBack,
-
rand(1, 99),
-
$testBack,
-
$rgbColor,
-
rand(1, 10),
-
'这是第'. $i .'行测试',
-
'这是第'. $i .'行测试',
-
'这是第'. $i .'行测试',
-
'这是第'. $i .'行测试',
-
'这是第'. $i .'行测试',
-
'这是第'. $i .'行测试',
-
];
-
}
-
-
// 公式测试,计算列的总和、平均
-
$formulas_test = [
-
[
-
'col_title' => 2,
-
'title' => '总计',
-
'col_formula' => 3,
-
'formula' => '=SUM(D{start}:D{end})'
-
],
-
[
-
'col_title' => 5,
-
'title' => '平均',
-
'col_formula' => 6,
-
'formula' => '=AVERAGE(G{start}:G{end})'
-
],
-
];
-
// 公式测试,计算列相乘
-
$formulas_test_two = [
-
[
-
'col_title' => 5,
-
'title' => '总计',
-
'col_formula' => 6,
-
'formula' => '=SUM(G{start}:G{end})'
-
]
-
];
-
-
$data_two = [];
-
for ($i = 0; $i < 30; $i ) {
-
// 数据合并
-
$merge = '这是第'. $i .'行测试';
-
if ($i == 5) {
-
$merge = [
-
'数据合并测试',
-
['merge' => 'start']
-
];
-
}
-
if ($i == 10) {
-
$merge = [
-
'数据合并测试',
-
['merge' => 'end']
-
];
-
}
-
-
// 数据合并 背景颜色
-
$megBack = '这是第'. $i .'行测试';
-
if ($i == 0) {
-
$megBack = [
-
'数据合并 背景颜色测试',
-
['merge' => 'start']
-
];
-
}
-
if ($i == 10) {
-
$megBack = [
-
'数据合并 背景颜色',
-
['merge' => 'end', 'background' => 'brown']
-
];
-
}
-
-
// RGB十六进制颜色
-
$rgbColor = '这是第'. $i .'行测试';
-
if ($i == 15) {
-
$rgbColor = [
-
'RGB十六进制颜色测试2',
-
['background' => 0xFDB8]
-
];
-
}
-
-
$data_two[] = [
-
$merge,
-
'这是第'. $i .'行测试',
-
$megBack,
-
'test',
-
'test2',
-
$rgbColor,
-
'test',
-
'这是第'. $i .'行测试',
-
'这是第'. $i .'行测试',
-
$merge,
-
'这是第'. $i .'行测试',
-
'这是第'. $i .'行测试',
-
$megBack,
-
];
-
}
-
-
$data_three = [];
-
for ($i = 0; $i < 100; $i ) {
-
$data_three[] = [
-
'这是第'. $i .'行测试',
-
'这是第'. $i .'行测试',
-
'这是第'. $i .'行测试',
-
'这是第'. $i .'行测试',
-
'这是第'. $i .'行测试',
-
'这是第'. $i .'行测试',
-
'这是第'. $i .'行测试',
-
'这是第'. $i .'行测试',
-
'这是第'. $i .'行测试',
-
'这是第'. $i .'行测试',
-
'这是第'. $i .'行测试',
-
'这是第'. $i .'行测试',
-
'这是第'. $i .'行测试',
-
];
-
}
-
-
try {
-
$fileName = '很厉害的文件导出类';
-
$xlsWriterServer = new MultiFloorXlsWriterService();
-
$xlsWriterServer->setFileName($fileName, '这是Sheet1别名');
-
$xlsWriterServer->setHeader($header);
-
$xlsWriterServer->setBoldHeader(); // 设置表头加粗
-
$xlsWriterServer->setItalicHeader(); // 设置表头斜体
-
$xlsWriterServer->setFilter(); // 表头开启过滤选项
-
$xlsWriterServer->setFreezeHeader(); // 冻结表头
-
$xlsWriterServer->setAlignCenterHeader(); // 设置表头水平居中
-
$xlsWriterServer->setFileProtection('testpwd'); // 设置文件解除锁定保护密码
-
$xlsWriterServer->setData($data);
-
-
$xlsWriterServer->setFormula($formulas_test); // 设置公式
-
$xlsWriterServer->reBuildFormulaTop(3); // 设置公式行距离数据行的间隔(默认2),这里使第二个公式数组在第一个公式下面
-
$xlsWriterServer->setFormula($formulas_test_two);
-
-
$xlsWriterServer->addSheet('这是Sheet2别名');
-
$xlsWriterServer->setHeader($header); //这里可以使用新的header
-
$xlsWriterServer->setFreezeHeader(); // 冻结表头
-
$xlsWriterServer->setData($data_two); // 这里也可以根据新的header定义数据格式
-
-
$xlsWriterServer->addSheet('这是Sheet3别名');
-
$xlsWriterServer->setHeader($header); //这里可以使用新的header
-
$xlsWriterServer->setFreezeHeader(); // 冻结表头
-
$xlsWriterServer->setData($data_three); // 这里也可以根据新的header定义数据格式
-
-
$filePath = $xlsWriterServer->output(); // 保存到服务器
-
$xlsWriterServer->excelDownload($filePath); // 输出到浏览器
-
} catch (\Exception $e) {
-
exit($e->getMessage());
-
}
导出效果图:
这篇好文章是转载于:学新通技术网
- 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
- 本站站名: 学新通技术网
- 本文地址: /boutique/detail/tanhfiaajh
系列文章
更多
同类精品
更多
-
photoshop保存的图片太大微信发不了怎么办
PHP中文网 06-15 -
Android 11 保存文件到外部存储,并分享文件
Luke 10-12 -
word里面弄一个表格后上面的标题会跑到下面怎么办
PHP中文网 06-20 -
《学习通》视频自动暂停处理方法
HelloWorld317 07-05 -
photoshop扩展功能面板显示灰色怎么办
PHP中文网 06-14 -
微信公众号没有声音提示怎么办
PHP中文网 03-31 -
excel下划线不显示怎么办
PHP中文网 06-23 -
excel打印预览压线压字怎么办
PHP中文网 06-22 -
怎样阻止微信小程序自动打开
PHP中文网 06-13 -
TikTok加速器哪个好免费的TK加速器推荐
TK小达人 10-01