网站信息,内蒙古建设工程造价信息网官方网站,网站设计与网页制作正文,企查查在线查询法人一、说明
1.需要安装Spreadsheet#xff0c;如未安装自行composer安装即可
2.定义导出表格的表头#xff08;及键值#xff09;
3.数据内容需要与定义的表头一致
二、核心代码
try {// 获取表格数据$list (new Activity())-select()-toArray();if (!empty($l…一、说明
1.需要安装Spreadsheet如未安装自行composer安装即可
2.定义导出表格的表头及键值
3.数据内容需要与定义的表头一致
二、核心代码
try {// 获取表格数据$list (new Activity())-select()-toArray();if (!empty($list)) {// 设置key$key [[key index, title 序号],[key activity_name, title 名称],[key student_name, title 学生姓名],];$sheet new Spreadsheet();$activeSheet new ExcelLogic($sheet);// 计算总列数$allColumn count($key);// 计算总行数$allRow count($list);// 重制表格数据与键值对应$newArr [];foreach ($list as $k $v) {$newArr[$k][index] $k 1;$newArr[$k][activity_name] $v[activity][title];$newArr[$k][student_name] $v[student][name];}// 添加表内容for ($i 0; $i $allColumn; $i) {// 设置标题$activeSheet-setCellsValue(1, $i 1, $key[$i][title])-setAlign(1, $i 1)-setrgBgColor(1, $i 1, 1, 7f89e6)-setFont(1, $i 1, 1, ffffff)-setBorder(1, $i 1)-setRowAndCol(1, $i 1, 24, strlen($key[$i][title]));// 设置内容for ($j 0; $j $allRow; $j) {$rowData $newArr[$j][$key[$i][key]];$activeSheet-setCellsValue($j 2, $i 1, strval($rowData))-setAlign($j 2, $i 1)-setBorder($j 2, $i 1);}}// 导出表格$writer IOFactory::createWriter($sheet, Xlsx);//创建excel文件$exportCache new ExportCache();$src $exportCache-getSrc();if (!file_exists($src)) {mkdir($src, 0775, true);}$filename 报名列表数据导出_ . date(YmdH) . .xlsx;$writer-save($src . $filename);// 返回下载地址}return $this-success(, [url ], 2);} catch (\Exception $e) {return $this-fail($e-getMessage());}
三、ExcelLogic类
?phpnamespace app\common\logic;use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;/*** note Excel处理*/
class ExcelLogic
{// 定义sheetprivate object $sheet;// 定义当前操作工作表private object $currentSheet;/*** note 初始化* param object $sheet* param int $sheetIndex*/public function __construct(object $sheet, int $sheetIndex 0){$this-sheet $sheet;
// $this-sheet new Spreadsheet();$this-currentSheet $this-sheet-getActiveSheet($sheetIndex);}/*** notes 设置工作表名* param string $title 表名* return ExcelLogic*/public function setTitle(string $title 模板): ExcelLogic{$this-currentSheet-setTitle($title);return $this;}/*** notes 设置字体样式* param int $row 行* param int $col 列* param string $fontName 字体名称* param string $fontSize 字体大小* param bool $isBold 是否加粗* param string $fontColor 字体颜色* param bool $isWrap 是否自动换行* param int $colSpan 列跨度* return ExcelLogic*/public function setFont(int $row 1,int $col 1,int $colSpan 1,string $fontColor 000000,bool $isBold false,string $fontName 宋体,string $fontSize 14,bool $isWrap true,): ExcelLogic{$this-currentSheet-getStyleByColumnAndRow($col, $row, $col $colSpan - 1, $row)-getFont()-setName($fontName)-setSize($fontSize)-setBold($isBold)-setColor(new Color($fontColor));// 自动换行$this-currentSheet-getStyleByColumnAndRow($col, $row, $col $colSpan - 1, $row)-getAlignment()-setWrapText($isWrap);return $this;}/*** notes 设置文字对齐方式* param int $row 行* param int $col 列* param string $alignHorizontal 水平对齐方式* param string $alignVertical 垂直对齐方式* return ExcelLogic*/public function setAlign(int $row 1, int $col 1, string $alignHorizontal center, string $alignVertical center): ExcelLogic{// 水平居中$this-currentSheet-getStyleByColumnAndRow($col, $row)-getAlignment()-setHorizontal($alignHorizontal);// 垂直居中$this-currentSheet-getStyleByColumnAndRow($col, $row)-getAlignment()-setVertical($alignVertical);return $this;}/*** notes 行高及列宽* param int $row 行* param int $col 列* param int $rowHeight 行高* param int $colWidth 列宽* return ExcelLogic*/public function setRowAndCol(int $row 1, int $col 1, int $rowHeight 24, int $colWidth 20): ExcelLogic{// 设置行高$this-currentSheet-getRowDimension($row)-setRowHeight($rowHeight);$this-currentSheet-getColumnDimensionByColumn($col)-setWidth($colWidth);return $this;}/*** notes 设置边框* param int $row 开始行* param int $col 开始列* param int $colSpan 列合并数* param string $borderStyle 边框样式* param string $borderColor 边框颜色* return ExcelLogic*/public function setBorder(int $row 1,int $col 1,int $colSpan 1,string $borderStyle thin,string $borderColor 000000): ExcelLogic{// 设置边框样式$this-currentSheet-getStyleByColumnAndRow($col, $row, $col $colSpan - 1, $row)-getBorders()-getallBorders()-setBorderStyle($borderStyle)-setColor(new Color($borderColor));return $this;}/*** notes 设置背景色* param int $row 开始行* param int $col 开始列* param int $colSpan 列合并数* param string $bgColor 背景色* return ExcelLogic*/public function setrgBgColor(int $row 1, int $col 1, int $colSpan 1, string $bgColor ffffff): ExcelLogic{// 设置背景色$this-currentSheet-getStyleByColumnAndRow($col, $row, $col $colSpan - 1, $row)-getFill()-setFillType(Fill::FILL_SOLID)-getStartColor()-setARGB($bgColor);return $this;}/*** notes 设置单元格内容* param int $row 行* param int $col 列* param string $title 表头名称* param int $dataRow 内容* param string|int $data 内容* return ExcelLogic*/public function setCellValue(int $row 1,int $col 1,string $title 序号,string|int $data ,int $dataRow 0,): ExcelLogic{// 设置表头$this-currentSheet-setCellValueByColumnAndRow($col, $row, $title);// 设置内容if ($dataRow 0) $dataRow $row 1;$this-currentSheet-setCellValueByColumnAndRow($col, $dataRow, $data);return $this;}/*** notes 设置单元格内容* param int $row 行* param int $col 列* param string $data 内容* return ExcelLogic*/public function setCellsValue(int $row 1,int $col 1,string $data ,): ExcelLogic{// 设置内容$this-currentSheet-setCellValueByColumnAndRow($col, $row, $data);return $this;}/*** notes 合并列并设置内容* param string $val 单元格内容* param int $col 开始列* param int $colSpan 列合并数* param int $row 合并行* param string $bgColor 背景色* param string $align 文字对齐* param string $fontColor 字体颜色* param string $fontSize 字体大小* param bool $isBold 字体粗细* param string $fontName 字体名称* param bool $isWrap 是否自动换行* param float $rowHeight 行高* return ExcelLogic*/publicfunction mergeCell(string $val,int $col 1,int $colSpan 1,int $row 1,string $bgColor FFFFFF,string $align left,string $fontColor ff4040,string $fontSize 14,bool $isBold true,string $fontName 宋体,bool $isWrap true,float $rowHeight 60): ExcelLogic{$this-currentSheet-mergeCellsByColumnAndRow($col, $row, $col $colSpan - 1, $row);$this-currentSheet-setCellValueByColumnAndRow($col, $row, $val);$this-setFont($row, $col, $colSpan, $fontColor, $isBold, $fontName, $fontSize, $isWrap);$this-setAlign($row, $col, $align);$this-setRowAndCol($row, $col, $rowHeight);$this-setBorder($row, $col, $colSpan);$this-setrgBgColor($row, $col, $colSpan, $bgColor);return $this;}/*** notes 新建工作表* param string $title 工作表名称* param int $sheetIndex 工作表序号* param array $tableArr 表头数组* param array $keys 键值数组* param array $dataArr 数据数组* return ExcelLogic*/public function createSheet(string $title sheet1, int $sheetIndex 1, array $tableArr [], array $keys [], array $dataArr []): ExcelLogic{// 新建工作表$newSheet new Worksheet($this-sheet, $title);$this-sheet-addSheet($newSheet, $sheetIndex);// 计算总列数$totalCol count($tableArr);$colArr $this-getColumn($totalCol);// 添加数据内容for ($i 0; $i $totalCol; $i) {// 设置表头$newSheet-setCellValue($colArr[$i] . 1, $tableArr[$i]);for ($j 0; $j count($dataArr); $j) {// 设置内容$newSheet-setCellValue($colArr[$i] . $j 2, $dataArr[$j][$keys[$i]]);}}return $this;}/*** notes 自动计算列数* param string $col*/publicfunction getColumn(int $colNumber 1){// 生成A-Z的数组$arr range(A, Z);// 计算循环次数$no ceil($colNumber / count($arr));// 定义数组$data [];if ($no 1) {for ($i 0; $i $colNumber; $i) {$data[] $arr[$i];}} else {for ($i 0; $i count($arr); $i) {$data[] $arr[$i];}for ($i 0; $i $colNumber - count($arr); $i) {$list (($i count($arr)) % count($arr));$data[] $arr[ceil(($i 1) / count($arr)) - 1] . $arr[$list];}}return $data;}
}
