【无标题】PhpSpreadsheet的使用方法

1.引入依赖

composer require phpoffice/phpspreadsheet

2.在类中引入实例

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Font;

设置整体文字垂直居中

	$newExcel = new Spreadsheet();
	$newExcel->getDefaultStyle()->getAlignment()->setHorizontal('CENTER');

设置整体文字居中，左对齐

	//设置整体文字居中，左对齐
		$newExcel = new Spreadsheet()
		$objSheet = $newExcel->getActiveSheet();
        $styleArray2 = ['alignment' => ['horizontal' => 'LEFT','vertical'=>'CENTER'],
        ];
        $objSheet->getStyle('A5')->applyFromArray($styleArray2);
        $objSheet->getStyle('A6')->applyFromArray($styleArray2);

设置整体文字居中，右对齐

		$newExcel = new Spreadsheet()
		$objSheet = $newExcel->getActiveSheet();
        $styleArray3 = ['alignment' => ['horizontal' => 'RIGHT','vertical'=>'CENTER'],
        ];
        $objSheet->getStyle('F5')->applyFromArray($styleArray3);
        $objSheet->getStyle('G6')->applyFromArray($styleArray3);

创建文件,存入表格到指定文件

		//创建文件
		$objWriter = IOFactory::createWriter($newExcel, $format);
        $file=ROOT_PATH . 'public' .DS . 'uploads' . DS . date('Ymd'). DS  ;
        if(!file_exists($file)){
            mkdir($file);
        }
        
        //存入表格到指定文件
        $objWriter->save($file.$name . '.' . strtolower($format));

3.以存入送货单为列子，列子如下

function out($user_info,$custom_name,$gongyis,$jiner,$order_id){
		//设置文件大小，避免导出时文件过大，到处失败
        ini_set('memory_limit','200M');
        //引入实列
        $newExcel = new Spreadsheet();
        //获取当前操作sheet的对象
        $objSheet = $newExcel->getActiveSheet();
		//设置当前sheet的标题
        $date = date('Ymd',time());
        $name = '送货单_'.$custom_name['s_num'];
        $objSheet->setTitle($name);
        
        $mobile='电话-'.$user_info['mobile'];
        
        //合并单元格
        $objSheet->mergeCells('A1:H1');
        $objSheet->mergeCells('A2:H2');
        $objSheet->mergeCells('A3:H3');
        $objSheet->mergeCells('A4:H4');
        
        //设置整体文字垂直居中
        $newExcel->getDefaultStyle()->getAlignment()->setHorizontal('CENTER');

		//给单元格设置值
        $objSheet->setCellValue('A1', $user_info['company_name'])
            ->setCellValue('A2', $user_info['s_address'])
            ->setCellValue('A3',$mobile)
            ->setCellValue('A4', '送货单');
		//合并单元格
        $objSheet->mergeCells('A5:B5');
        $objSheet->mergeCells('F5:H5');
		//给单元格设置值
        $objSheet->setCellValue('A5', '客户:'.$custom_name['username'])
            ->setCellValue('F5', '出货单号:'.$custom_name['s_num']);
		//合并单元格
        $objSheet->mergeCells('A6:E6');
        $objSheet->mergeCells('G6:H6');
		//给单元格设置值
        $objSheet->setCellValue('A6', '送货地址:'.$custom_name['s_address'])
            ->setCellValue('G6', '日期:'.$custom_name['s_time']);
        //设置整体文字居中，左对齐
        $styleArray2 = ['alignment' => ['horizontal' => 'LEFT','vertical'=>'CENTER'],
        ];
        $objSheet->getStyle('A5')->applyFromArray($styleArray2);
        $objSheet->getStyle('A6')->applyFromArray($styleArray2);
		//设置整体文字居中，右对齐
        $styleArray3 = ['alignment' => ['horizontal' => 'RIGHT','vertical'=>'CENTER'],
        ];
        $objSheet->getStyle('F5')->applyFromArray($styleArray3);
        $objSheet->getStyle('G6')->applyFromArray($styleArray3);
        
		//设置表格表头
        $objSheet->setCellValue('A7', '订单号')
            ->setCellValue('B7','工艺')
            ->setCellValue('C7','规格')
            ->setCellValue('D7', '单位')
            ->setCellValue('E7', '数量')
            ->setCellValue('F7', '单价')
            ->setCellValue('G7', '金额')
            ->setCellValue('H7', '备注');

        //设置表头样式
        $font = [
            'font' => [
                'bold' => true,
                'size' => 13,
            ],
        ];
        $objSheet->getStyle('A2:M2')->applyFromArray($font);
        
        header("content-type:text/html;charset=utf-8");
        //获取数据条数
        $dataCount = count($gongyis);
        //存入表格数据
        $k = 7;//表格从第七行开始
        if($dataCount == 0){
            exit;
        }else{
            for ($i=0;$i<$dataCount;$i++){
                $k = $k + 1;
                $objSheet->setCellValue('A' . $k, $gongyis[$i]['order_sn'])
                    ->setCellValue('B' . $k, $gongyis[$i]['gongyi'])
                    ->setCellValue('C' . $k, $gongyis[$i]['guige'])
                    ->setCellValue('D' . $k, $gongyis[$i]['danwei'])
                    ->setCellValue('E' . $k,  $gongyis[$i]['num'])
                    ->setCellValue('F' . $k,  $gongyis[$i]['danjia'])
                    ->setCellValue('G' . $k,  $gongyis[$i]['order_money'])
                    ->setCellValue('H' . $k, $gongyis[$i]['remark']);
            }
        }
        //设置订单号如202309045150为邮编格式，避免出现2.02309E+11现象
        $objSheet->getStyle('A8:A13')->getNumberFormat()->setFormatCode('000000');
		//设置表格线框
        $objSheet->getStyle('A7:H13')->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
		//合并单元格
        $objSheet->mergeCells('G14:H14');

        $objSheet->setCellValue('G14', '合计金额:'.$jiner)
        ;
        $objSheet->mergeCells('A15:A16');
        $objSheet->mergeCells('E15:H16');
        $objSheet->setCellValue('A15', '送货经办人:')
            ->setCellValue('E15', '收货人签名盖章:')
        ;
        //默认行高
        $objSheet->getDefaultRowDimension()->setRowHeight(22);

        //设置宽度
        $objSheet->getColumnDimension('A')->setWidth(25);
        ob_end_clean();
        ob_start();
        //设置文件扩展名， $format只能为 Xlsx 或 Xls
        $format='Xlsx';
        if ($format == 'Xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        } elseif ($format == 'Xls') {
            header('Content-Type: application/vnd.ms-excel');
        }

        header("Content-Disposition: attachment;filename="  . $name . '.' . strtolower($format));
        header('Cache-Control: max-age=0');
        $objWriter = IOFactory::createWriter($newExcel, $format);
		//创建文件
        $file=ROOT_PATH . 'public' .DS . 'uploads' . DS . date('Ymd'). DS  ;
        if(!file_exists($file)){
            mkdir($file);
        }
        
        //存入表格到指定文件
        $ret=$objWriter->save($file.$name . '.' . strtolower($format));
        //下载文件
        //$ret=$objWriter->save('php://output');
        
        //文件存入数据库
        Db::table('fg_orders_all')->where('id',$order_id)->update(['files'=>DS . 'uploads' . DS . date('Ymd'). DS.$name . '.' . strtolower($format)]);
        
        return true;

    }

生成的表格如下