使用PHP生成Excel和PDF文件

摘要：使用 Laravel 作为PHP框架，结合 phpspreadsheet 生成 Excel，结合 dompdf 生成 PDF 。

1. 扩展安装：

• composer安装

     composer require "phpoffice/phpspreadsheet" [php5]
     composer require "dompdf/dompdf" [php5]
  

2. 创建控制器生成文件：

• 生成 Excel

  	namespace App\Http\Controllers;
  	use Illuminate\Http\Request;
  	
  	// 引用
  	use PhpOffice\PhpSpreadsheet\Spreadsheet;
  	use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
  	use PhpOffice\PhpSpreadsheet\IOFactory;
  	use PhpOffice\PhpSpreadsheet\Worksheet\Drawing; // 用以插入图片
  	...
  	
  	// 定义样式
  	$alignCenter = [ // 水平居中
  	    'alignment' => [
  	        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
  	    ],
  	];
  	$alignLeft = [ // 水平居左
  	    'alignment' => [
  	        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
  	    ],
  	];		
  	$verticalCenter = [ // 垂直居中
  	    'alignment' => [
  	        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
  	    ],
  	];
  	$blackBorder = [ // 黑边框 border
  	    'borders' => [
  	        'outline' => [
  	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
  	            'color' => ['argb' => '00000000'],
  	        ],
  	    ],
  	];
  	$blackBorderTop = [ // 黑边框 border-top
  	    'borders' => [
  	        'top' => [
  	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
  	            'color' => ['argb' => '00000000'],
  	        ],
  	    ],
  	];
  	$blackBorderBottom = [ // 黑边框 border-bottom
  	    'borders' => [
  	        'bottom' => [
  	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
  	            'color' => ['argb' => '00000000'],
  	        ],
  	    ],
  	];
  	$blackBorderLeft = [ // 黑边框 border-left
  	    'borders' => [
  	        'left' => [
  	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
  	            'color' => ['argb' => '00000000'],
  	        ],
  	    ],
  	];
  	$blackBorderRight = [ // 黑边框 border-right
  	    'borders' => [
  	        'right' => [
  	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
  	            'color' => ['argb' => '00000000'],
  	        ],
  	    ],
  	];
  	$blackBorderLeftRight = [ // 黑边框 border-left & border-right
  	    'borders' => [
  	        'left' => [
  	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
  	            'color' => ['argb' => '00000000'],
  	        ],
  	        'right' => [
  	            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
  	            'color' => ['argb' => '00000000'],
  	        ],
  	    ],
  	];
  	
  	// 插入图片
  	$spreadsheet = new Spreadsheet();
  	$sheet = $spreadsheet->getActiveSheet();		
  	$drawing = new Drawing();
  	$drawing->setName('Logo');
  	$drawing->setDescription('Logo');
  	$drawing->setPath(public_path().'/images/LogoBig.png');
  	$drawing->setCoordinates('A1'); // 图片插入位置
  	$drawing->setWidth(268); // 设置图片宽度，高度会自动按比例缩放
  	$drawing->setWorksheet($sheet);
  		// $drawing->setOffsetX(268);
  		// $drawing->setRotation(50);
  		// $drawing->getShadow()->setVisible(true);
  		// $drawing->getShadow()->setDirection(45);
  	
  	// 主要设置单元格格式
  	$sheet->getStyle('A1:G500')->applyFromArray($alignCenter); // 设置单元格水平居中
  	$sheet->getStyle('A1:G500')->applyFromArray($verticalCenter); // 设置单元格垂直居中
  	for ($i=1; $i <= 500; $i++) {
  		$sheet->getRowDimension($i)->setRowHeight(20); // 设置行高
  	}
  	$sheet->getColumnDimension('A')->setWidth(5); // 设置列宽
  	$sheet->getStyle('A'.$line)->getAlignment()->setWrapText(true); // 设置自动换行
  	$sheet->getStyle('A'.$line)->applyFromArray($blackBorder); // 设置单元格边框		
  	$sheet->getStyle('E'.$line)->applyFromArray($alignLeft); // 设置单元格水平居左
  
  	$sheet->getStyle('A1:G1')->getFont()->setBold(true)->setName('Arial')->setSize(22); // 设置字体
  	$sheet->mergeCells('A1:G2'); // 合并单元格
  	
  	$sheet->setCellValue('A1', 'PROFORMA INVOICE'); // 设置单元格的值
      $sheet->getCell('A'.$line)->setValueExplicit('2500000000', \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC); // 设置单元格的值，并设置为数字 
      $sheet->getCell('A'.$line)->setValueExplicit('2500000000', \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING); // 设置单元格的值，并设置为字符串 
      
      // 完整代码 --- Begin ---
      $spreadsheet = new Spreadsheet();
      $sheet = $spreadsheet->getActiveSheet();
      $sheet->setTitle('COMMERCIAL INVOICE');
      
      $line = 1;
      $sheet->setCellValue('A'.$line, 'COMMERCIAL INVOICE');
      $sheet->getRowDimension($line)->setRowHeight(84);
      $sheet->mergeCells('A'.$line.':G'.$line);
      $sheet->getStyle('A'.$line.':G'.$line)->getFont()->setBold(true)->setName('Arial')->setSize(22);
      $line++;        
  	
      $sheet->setCellValue('A'.$line, 'Company Name');
      $sheet->mergeCells('A'.$line.':G'.$line);
      $line++;
      
      $sheet->setCellValue('A'.$line, 'Address : Address');
      $sheet->mergeCells('A'.$line.':G'.$line);
      $line++;
  
      $sheet->setCellValue('A'.$line, 'Tel : +86          Email : email@qq.com          Web : www.xxx.com');
      $sheet->mergeCells('A'.$line.':G'.$line);
      $line++;
  
      $sheet->mergeCells('A'.$line.':G'.$line);
      $line++;
  
  	$file_name = 'filename.xlsx';
  	
  	// // 方式一 保存到本地
  	// $writer = new Xlsx($spreadsheet);
  	// $file_name = public_path().'/files/'.$file_name;
  	// $writer->save($file_name);
  	
  	// 方式二 浏览器直接下载
  	header('Content-Type: application/vnd.ms-excel');
      header('Content-Disposition: attachment;filename='.$file_name);
      header('Cache-Control: max-age=0');
      $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
      $writer->save('php://output');
  
  	// --- End---
  

• 生成 PDF

  	namespace App\Http\Controllers;
  	use Illuminate\Http\Request;
  	
  	// 引用
  	use Dompdf\Dompdf;
  	...
  	
  	// 完整代码 --- Begin ---
      $html = '<!DOCTYPE html>
              <html>
              <head>
                  <title>PDF File</title>
              </head>
              <body style="font-family: Arial;">
                  <div>
                      <div style="text-align: center;">
                          <h1 style="display: block;padding: 10px;font-size: 36px;">PDF File</h1>
                      </div>
                      <div style="text-align: center;">
                          <p style="font-size: 18px;">Company Name</p>
                          <p>Address : Address</p>
                          <p>Tel : +86 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Email : email@qq.com &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Web : www.xxx.com</p>
                      </div>
                      <table style="width: 100%;" cellspacing="0">
                          <tr>
                              <th style="border: 1px solid #313131;text-align: center;">No.</th>
                              <th style="border: 1px solid #313131;text-align: center;">Name</th>
                              <th style="border: 1px solid #313131;text-align: center;">Email</th>
                          </tr>
                          <tr>
                              <td style="border: 1px solid #313131;padding: 0 5px;">1</td>
                              <td style="border: 1px solid #313131;text-align: center;">A</td>
                              <td style="border: 1px solid #313131;text-align: center;">@</td>
                          </tr>
                          <tr>
                              <td style="border: 1px solid #313131;padding: 0 5px;">2</td>
                              <td style="border: 1px solid #313131;text-align: center;">B</td>
                              <td style="border: 1px solid #313131;text-align: center;">@</td>
                          </tr>
                      </table>
                  </div>
              </body>
              </html>';
      $dompdf = new Dompdf();
      $dompdf->loadHtml($html);
  
      // 设置纸张尺寸
      $dompdf->setPaper('A4');
      // $dompdf->setPaper('A4', 'landscape');
  
      // 把网页转换成pdf
      $dompdf->render();
  
  	$file_name = 'filename.pdf';
  
      // 在浏览器输出pdf文件
      $dompdf->stream($file_name);     
         
  	// --- End ---
     
  

---