PhpOffice处理

<?php

namespace App\Http\Controllers\Admin;

use Illuminate\Http\Request;

use DB;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;

class GroupController extends BaseController
{

public function exportNames(Request $request)
{
$orderid = $request->get(orderid,);
if(!$orderid){
redirect()->to(admin/group);
}

$orderInfo = DB::table(si_group_order_plans)->where(order_id,$orderid)->first();

if(!$orderInfo){   # 空数据
redirect()->to(admin/group);
}

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$sheet->setCellValue(A1, 姓名);//新文件需要设置头部
$sheet->setCellValue(B1, 身份证号码);
$sheet->setCellValue(C1, 民族);
$sheet->setCellValue(D1, 参加工作时间);
$sheet->setCellValue(E1, 户口性质);
$sheet->setCellValue(F1, 工资);

$row = 1;//记录行数

$arr = json_decode($orderInfo->insurant_list,true);

foreach ($arr as $key => $value) {// 写入数据
$sheet->setCellValue(A.($row+1), $value[xm]."\t");
$sheet->setCellValue(B.($row+1), $value[sfzhm]."\t");
$sheet->setCellValue(C.($row+1), $value[mz]."\t");
$sheet->setCellValue(D.($row+1), $value[cjgzsj]."\t");
$sheet->setCellValue(E.($row+1), $value[hkxz]."\t");
$sheet->setCellValue(F.($row+1), $value[gz]."\t");
$row++;
}

$filename = time()..xlsx;
header(Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet);
header(Content-Disposition: attachment;filename=".$filename.");
header(Cache-Control: max-age=0);
$writer = IOFactory::createWriter($spreadsheet, Xlsx);
$writer->save(php://output);
return;
}
}

// Set properties
$objPHPExcel->getProperties()
->setCreator("1245049149@qq.com")
->setLastModifiedBy("1245049149@qq.com")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
// 字体和样式
$objPHPExcel->getActiveSheet()
->getDefaultStyle()
->getFont()
->setSize(12); // 字体大小
$objPHPExcel->getActiveSheet()
->getStyle(A2:D2)
->getFont()
->setBold(false); // 第二行是否加粗
$objPHPExcel->getActiveSheet()
->getStyle(A1)
->getFont()
->setBold(true); // 第一行是否加粗
$objPHPExcel->getActiveSheet()
->getStyle(A1)
->getFont()
->setSize(16); // 第一行字体大小
$objPHPExcel->getActiveSheet()
->getStyle(A2)
->getFont()
->setSize(14); // 第二行字体大小
$objPHPExcel->getActiveSheet()
->getStyle(A6)
->getFont()
->setSize(14); // 第六行字体大小
$objPHPExcel->getActiveSheet()
->getStyle(A11)
->getFont()
->setSize(14); // 第十一行字体大小

// 设置垂直居中
$objPHPExcel->getActiveSheet()
->getStyle(A1)
->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()
->getStyle(A2:D2)
->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// 设置水平居中
$objPHPExcel->getActiveSheet()
->getStyle(A1)
->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()
->getStyle(A3:G5)
->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()
->getStyle(A7:G10)
->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()
->getStyle(A12:B15)
->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// 设置左对齐
$objPHPExcel->getActiveSheet()
->getStyle(A2)
->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()
->getStyle(A6)
->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()
->getStyle(A11)
->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
// 设置行高度
$objPHPExcel->getActiveSheet()
->getDefaultRowDimension()
->setRowHeight(20);
// 设置默认行高
$objPHPExcel->getActiveSheet()
->getRowDimension(1)
->setRowHeight(30); // 第一行行高

// 设置单元格宽度
$objPHPExcel->getActiveSheet()
->getColumnDimension(A)
->setWidth(20);
$objPHPExcel->getActiveSheet()
->getColumnDimension(B)
->setWidth(20);
$objPHPExcel->getActiveSheet()
->getColumnDimension(C)
->setWidth(20);
$objPHPExcel->getActiveSheet()
->getColumnDimension(D)
->setWidth(20);
$objPHPExcel->getActiveSheet()
->getColumnDimension(E)
->setWidth(20);
$objPHPExcel->getActiveSheet()
->getColumnDimension(F)
->setWidth(20);
$objPHPExcel->getActiveSheet()
->getColumnDimension(G)
->setWidth(20);
// 合并
$objPHPExcel->getActiveSheet()->mergeCells(A1:G1);
$objPHPExcel->getActiveSheet()->mergeCells(A2:G2);
$objPHPExcel->getActiveSheet()->mergeCells(A6:G6);
$objPHPExcel->getActiveSheet()->mergeCells(A11:B11);