如何从 PHP 生成包含多个工作表的 Excel 文档？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3269345/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to generate an Excel document with multiple worksheets from PHP?
提问by Cristian
I want to generate an MS Excel file from PHP. I know one can do something like this:
我想从 PHP 生成一个 MS Excel 文件。我知道一个人可以做这样的事情：
header ( "Content-type: application/vnd.ms-excel" );
header ( "Content-Disposition: attachment; filename=foo_bar.xls" );
But it will generate a file with just one Sheet. What I want is generating a file with multiple sheets. How can I do that? Maybe there's a third party library, but I haven't found too much.
但它会生成一个只有一张 Sheet 的文件。我想要的是生成一个包含多张纸的文件。我怎样才能做到这一点？也许有第三方库，但我还没有找到太多。
回答by Mark Baker
Try looking at PHPExcel. This is a simple example that creates an Excel file with two sheets:
尝试查看PHPExcel。这是一个简单的示例，它创建一个包含两张工作表的 Excel 文件：
<?php
require_once 'PHPExcel.php';
require_once 'PHPExcel/IOFactory.php';
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Create a first sheet, representing sales data
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Something');
// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Name of Sheet 1');
// Create a new worksheet, after the default sheet
$objPHPExcel->createSheet();
// Add some data to the second sheet, resembling some different data types
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'More data');
// Rename 2nd sheet
$objPHPExcel->getActiveSheet()->setTitle('Second sheet');
// Redirect output to a client's web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="name_of_file.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
回答by bob-the-destroyer
If you mean like have your PHP script create an Excel file, write some stuff to it on any sheet, etc, then offer that up for the client to download, you can just use PHP's built-in COM extension. See: http://us2.php.net/manual/en/class.com.phpfor all sorts of examples. However, you will need Excel (or a clone like OpenOffice) installed on the server. If you don't, perhaps Mark Baker's answer above will work instead without it.
如果你的意思是让你的 PHP 脚本创建一个 Excel 文件，在任何工作表上写一些东西等等，然后提供给客户端下载，你可以只使用 PHP 的内置 COM 扩展。有关各种示例，请参见：http: //us2.php.net/manual/en/class.com.php。但是，您需要在服务器上安装 Excel（或 OpenOffice 之类的克隆）。如果你不这样做，也许没有它，马克贝克上面的答案会起作用。
回答by Gangadhar
<?php
require_once 'PHPExcel.php';
require_once 'PHPExcel/IOFactory.php';
//Update the multiple sheets in PHP excel
$report_file = 'Report_' . date('Y-m-d') . '.xlsx';
$report_file_exists = 0;
//If the file doesnot exist , create new otherwise append the data at last
if (!file_exists($report_file)) {
$objPHPExcel = new PHPExcel();
} else {
$report_file_exists = 1;
$objPHPExcel = PHPExcel_IOFactory::load($report_file);
}
$columns = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
//Sheet details
$sheet_details = array(
//1st sheet details
0 => array('sheet_title' => 'Products',
'sheet_heading' => array('Article_Number','Name'),
'sheet_data' => array('1234','Pen')
),
//2nd Sheet Details
1 => array('sheet_title' => 'Categories',
'sheet_heading' => array('Category Id','Name'),
'sheet_data' => array(123,'Accessories')
)
);
$sheet_count = 0;
$row = 1;
$column = 0;
while ($sheet_count <= count($sheet_details)) {
$objWorkSheet = '';
if ($report_file_exists == 0) {
if ($sheet_count > 0) {
$objWorkSheet = $objPHPExcel->createSheet($sheet_count);
} else {
$objWorkSheet = $objPHPExcel->getActiveSheet();
}
$row = 1;
$column = 0;
foreach ($sheet_details[$sheet_count]['sheet_heading'] as $head) {
$objWorkSheet->setCellValue($columns[$column] . $row, $head);
$column++;
}
} else {
$objPHPExcel->setActiveSheetIndex($sheet_count);
$objWorkSheet = $objPHPExcel->getActiveSheet($sheet_count);
}
$row = $objWorkSheet->getHighestRow() + 1; //row count
foreach ($sheet_details[$sheet_count]['sheet_data'] as $report_details) {
$column = 0;
foreach ($report_details as $data) {
$objWorkSheet->setCellValue($columns[$column] . $row, $data);
$column++;
}
$row++;
}
$objWorkSheet->setTitle($sheet_details[$sheet_count]['sheet_title']);
$sheet_count++;
}
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save($report_file);
?>
回答by ABDUL JAMAL
You can achieve this by THIS IS DONE BY
您可以通过 THIS IS DONE BY 来实现这一点
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("creater");
$objPHPExcel->getProperties()->setLastModifiedBy("Middle field");
$objPHPExcel->getProperties()->setSubject("Subject");
$objWorkSheet = $objPHPExcel->createSheet();
$work_sheet_count=3//number of sheets you want to create
$work_sheet=0;
while($work_sheet<=$work_sheet_count){
if($work_sheet==0){
$objWorkSheet->setTitle("Worksheet$work_sheet");
$objPHPExcel->setActiveSheetIndex($work_sheet)->setCellValue('A1', 'SR No. In sheet 1')->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->setActiveSheetIndex($work_sheet)->setCellValueByColumnAndRow($col++, $row++, $i++);//setting value by column and row indexes if needed
}
if($work_sheet==1){
$objWorkSheet->setTitle("Worksheet$work_sheet");
$objPHPExcel->setActiveSheetIndex($work_sheet)->setCellValue('A1', 'SR No. In sheet 2')->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->setActiveSheetIndex($work_sheet)->setCellValueByColumnAndRow($col++, $row++, $i++);//setting value by column and row indexes if needed
}
if($work_sheet==2){
$objWorkSheet = $objPHPExcel->createSheet($work_sheet_count);
$objWorkSheet->setTitle("Worksheet$work_sheet");
$objPHPExcel->setActiveSheetIndex($work_sheet)->setCellValue('A1', 'SR No. In sheet 3')->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->setActiveSheetIndex($work_sheet)->setCellValueByColumnAndRow($col++, $row++, $i++);//setting value by column and row indexes if needed
}
$work_sheet++;
}
$filename='file-name'.'.xls'; //save our workbook as this file name
header('Content-Type: application/vnd.ms-excel'); //mime type
header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
header('Cache-Control: max-age=0'); //no cache
$filename='file-name'.'.xls'; //save our workbook as this file name
header('Content-Type: application/vnd.ms-excel'); //mime type
header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
header('Cache-Control: max-age=0'); //no cache
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
