导出效果
自定义表格排版和多工作表
代码:
<?php
require 'vendor/autoload.php';
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 xlsxclass {
public function index($sqlData)
{
//$sql=model('goods')->order('number asc')->select();
//$sqlData = collection($sql)->toArray();
$grouped = [];
foreach ($sqlData as $item) {
$grouped[$item['brand']][] = $item;
}
// 设置表头样式
$headerStyle = [
'font' => [
'bold' => true,
'size' => 12,
],
'alignment' => [
'horizontal' => Alignment::HORIZONTAL_CENTER,
'vertical' => Alignment::VERTICAL_CENTER,
],
'fill' => [
'fillType' => Fill::FILL_SOLID,
'startColor' => ['argb' => 'FFC6EFCE'],
],
'borders' => [
'allBorders' => [
'borderStyle' => Border::BORDER_THIN,
'color' => ['argb' => 'FF000000'],
],
],
];
// 设置数据样式
$dataStyle = [
'font' => [
'bold' => true, // 添加这行来设置文字加粗
],
'alignment' => [
'horizontal' => Alignment::HORIZONTAL_JUSTIFY,
'vertical' => Alignment::VERTICAL_CENTER,
],
'borders' => [
'allBorders' => [
'borderStyle' => Border::BORDER_THIN,
'color' => ['argb' => 'FF000000'],
],
],
];
// 创建新的Spreadsheet对象
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$numCount = 0;
foreach ($grouped as $kk=>$vv) {
if($numCount){
$sheet2 =$spreadsheet->createSheet();
}else{
$sheet2 = $sheet;
}
$numCount = $numCount+1;
$sheet2->setTitle($kk);
$arrABC=$this->arrHandle($vv);
foreach ($arrABC as $key1 => $val1) {
foreach ($val1['mergeCells'] as $key2 => $val2) {
$sheet2->mergeCells($val2[0].':'.$val2[1]);
if(!$key2){
$sheet2->getStyle($val2[0].':'.$val2[1])->applyFromArray($headerStyle);
}else{
$sheet2->getStyle($val2[0].':'.$val2[1])->applyFromArray($dataStyle);
}
}
$sheet2->setCellValue($val1['setCellValue'][0], '北京好利来科技有限公司');
$sheet2->setCellValue($val1['setCellValue'][1], '资产名称：'.$val1['data']['brand']);
$sheet2->setCellValue($val1['setCellValue'][2], '资产编号：'.$val1['data']['number']);
$sheet2->setCellValue($val1['setCellValue'][3], '管理日期：'.$val1['data']['collection_time']);
$sheet2->setCellValue($val1['setCellValue'][4], '规格型号：'.$val1['data']['specification']);
$sheet2->getColumnDimension($val1['letter'][0])->setWidth(8);
$sheet2->getColumnDimension($val1['letter'][1])->setWidth(8);
$sheet2->getColumnDimension($val1['letter'][2])->setWidth(8);
$sheet2->getColumnDimension($val1['letter'][3])->setWidth(13);
$sheet2->getColumnDimension($val1['letter'][4])->setWidth(2);
foreach ($val1['number'] as $key3 => $val3) {
$sheet2->getRowDimension($val3)->setRowHeight(23);
}
//加载图片
$isImg = $val1['data']['qrcode'];
$orImg = pathinfo($isImg);
//$isImg = ROOT_PATH.'public'.'/wxcode/wxcode_'.$val1['data']['id'].'.png';
//$orImg = pathinfo($isImg);
if (isset($orImg['extension']) && (($orImg['extension'] == 'jpg') || ($orImg['extension'] == 'png') || ($orImg['extension'] == 'gif'))){
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing->setName( 'Logo');
$drawing->setDescription( 'Logo');
$drawing->setPath($isImg);
$drawing->setWidth(80);
$drawing->setHeight(80);
$drawing->setCoordinates($val1['letter'][3] . $val1['number'][1]);
$drawing->setOffsetX(10);
$drawing->setOffsetY(23);
$drawing->setWorksheet($sheet2);
// $sheet2->getStyle($val1['letter'][3] . $val1['number'][1])->applyFromArray($headerStyle);
}
}
}
// 输出文件
$writer = new Xlsx($spreadsheet);
$filename = '资产信息表.xlsx';
$writer->save($filename);
// 下载文件
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
$writer->save('php://output');
exit;
}
// 类的内容将在这里定义
public function arrHandle($assets){
$alphabet = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
$loopValue = [0, 5, 10];
$loopValueLength = count($loopValue);
$Y = 0;
$X = 4;
$arrABC = [];
$lengthX = 0;
$lengthY = 6;
$unmber = 0;
$letterStatrt = 0;
for ($i = 0; $i <= count($assets)-1; $i++) {
$letter = $loopValue[$i % $loopValueLength];
$arrABC[$i]['head'] = [$alphabet[$letter],$letter+1];
if(!$letter){
$unmber = $unmber+1;
$lengthX= ($lengthX+5);
$lengthY = ($unmber*5)-4;
$letterStatrt = 0;
}
$forStart = 1;
$qrArr = [];
$arrABC[$i]['data'] = $assets[$i];
for ($o = $lengthX-4; $o <= $lengthX; $o++) {
if($forStart==1){
$arrABC[$i]['mergeCells'][] = [$alphabet[$letter].$o,$alphabet[$letter+3].($lengthY)];//头
}else if($forStart==2){
$arrABC[$i]['mergeCells'][] = [$alphabet[$letter].$o,$alphabet[$letter+2].($o)];//头
$qrArr[0] = $alphabet[$letter+3].$o;
}else if($forStart==3){
$arrABC[$i]['mergeCells'][] = [$alphabet[$letter].$o,$alphabet[$letter+2].($o)];//头
}else if($forStart==4){
$arrABC[$i]['mergeCells'][] = [$alphabet[$letter].$o,$alphabet[$letter+2].($o)];//头
}else if($forStart==5){
$arrABC[$i]['mergeCells'][] = [$alphabet[$letter].$o,$alphabet[$letter+2].($o)];//头
$qrArr[1] = $alphabet[$letter+3].$o;
}else if($forStart==6){
$arrABC[$i]['mergeCells'][] = [$alphabet[$letter].$o,$alphabet[$letter+2].($o)];//头
}else{
}
$arrABC[$i]['setCellValue'][] = $alphabet[$letter].$o;
$arrABC[$i]['letter'][] = $alphabet[$letterStatrt];
$arrABC[$i]['number'][] = $o;
$letterStatrt = $letterStatrt+1;
//print_r($qrArr);
if(count($qrArr)==2){
$arrABC[$i]['mergeCells'][] = $qrArr;
}
$forStart++;
}
}
// print_r($arrABC);
return $arrABC;
}
}
$xlsxclass = new xlsxclass();
//生成测试数据
$sqlData = [];
$brand = ['台式机','笔记本','一体机'];
for($i=0;$i<=20;$i++){
$arr = [
"id"=> $i+1,
"number"=> "XY-BJB0".$i,
"name"=> "测试名称".$i,
"good_name"=> "联想",
"section"=> "财务部",
"brand"=> $brand[array_rand($brand)],
"category"=> "联想",
"specification"=> "联想Y700",
"status"=> 1,
"images"=> [
"/uploads\/20230829\/d61391a4c83ced7378aea3c60d52e546.jpg"
],
"remark"=> "",
"situation"=> "",
"qrcode"=> "wxcode_20.png",
"collection_time"=> "",
"createtime"=> 1693298539,
"updatetime"=> 1718940889,
"deletetime"=> null
];
$sqlData[] = $arr;
}
print_r($xlsxclass->index($sqlData));
?>
源代码:下载地址
