PHP使用PhpSpreadsheet操作Excel实例详解(7)

五、工作薄操作

1、xlsx 文件下载
  • IOFactory::createWriter 写入到文件
<?php
	# 载入composer自动加载文件
	require 'vendor/autoload.php';
	# 给类文件的命名空间起个别名
	use PhpOffice\PhpSpreadsheet\Spreadsheet;
	# 实例化 Spreadsheet 对象
	$spreadsheet = new Spreadsheet();
	# 获取活动工作薄
	$sheet = $spreadsheet->getActiveSheet();

	$sheet->setCellValue('A1','ID');
	$sheet->setCellValue('B1','姓名');
	$sheet->setCellValue('C1','年龄');
	$sheet->setCellValue('D1','身高');

	$sheet->setCellValueByColumnAndRow(1, 2, 1);
	$sheet->setCellValueByColumnAndRow(2, 2, '欧阳克');
	$sheet->setCellValueByColumnAndRow(3, 2, '18岁');
	$sheet->setCellValueByColumnAndRow(4, 2, '188cm');

	// MIME 协议,文件的类型,不设置,会默认html
	header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	// MIME 协议的扩展
	header('Content-Disposition:attachment;filename=1.xlsx');
	// 缓存控制
	header('Cache-Control:max-age=0');

	$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
	// php://output 它是一个只写数据流, 允许你以 print 和 echo一样的方式写入到输出缓冲区。 
	$writer->save('php://output');
2、xls 文件下载
<?php
	# 载入composer自动加载文件
	require 'vendor/autoload.php';
	# 给类文件的命名空间起个别名
	use PhpOffice\PhpSpreadsheet\Spreadsheet;
	# 实例化 Spreadsheet 对象
	$spreadsheet = new Spreadsheet();
	# 获取活动工作薄
	$sheet = $spreadsheet->getActiveSheet();

	$sheet->setCellValue('A1','ID');
	$sheet->setCellValue('B1','姓名');
	$sheet->setCellValue('C1','年龄');
	$sheet->setCellValue('D1','身高');

	$sheet->setCellValueByColumnAndRow(1, 2, 1);
	$sheet->setCellValueByColumnAndRow(2, 2, '欧阳克');
	$sheet->setCellValueByColumnAndRow(3, 2, '18岁');
	$sheet->setCellValueByColumnAndRow(4, 2, '188cm');

	$filename = '1.xls';
	header('Content-Type:application/vnd.ms-excel');
	header('Content-Disposition:attachment;filename=1.xls');
	header('Cache-Control:max-age=0');

	$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
	$writer->save('php://output');
3、设置工作簿标题
  • setTitle
<?php
	# 载入composer自动加载文件
	require 'vendor/autoload.php';
	# 给类文件的命名空间起个别名
	use PhpOffice\PhpSpreadsheet\Spreadsheet;
	# 实例化 Spreadsheet 对象
	$spreadsheet = new Spreadsheet();
	# 获取活动工作薄
	$sheet = $spreadsheet->getActiveSheet();

	$sheet->setCellValue('A1','ID');
	$sheet->setCellValue('B1','姓名');
	$sheet->setCellValue('C1','年龄');
	$sheet->setCellValue('D1','身高');

	$sheet->setCellValueByColumnAndRow(1, 2, 1);
	$sheet->setCellValueByColumnAndRow(2, 2, '欧阳克');
	$sheet->setCellValueByColumnAndRow(3, 2, '18岁');
	$sheet->setCellValueByColumnAndRow(4, 2, '188cm');

	$sheet->setTitle('欧阳克');

	// MIME 协议,文件的类型,不设置,会默认html
	header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	// MIME 协议的扩展
	header('Content-Disposition:attachment;filename=1.xlsx');
	// 缓存控制
	header('Cache-Control:max-age=0');

	$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
	// php://output 它是一个只写数据流, 允许你以 print 和 echo一样的方式写入到输出缓冲区。 
	$writer->save('php://output');

      

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:http://www.heiqu.com/4963.html