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');