PHP 生成 .xlsx
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37958282/
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
PHP generate .xlsx
提问by angel enanod
I am generating Excel file with extension of .xlsx
我正在生成扩展名为 .xlsx 的 Excel 文件
Here is my simple code:
这是我的简单代码：
$file = "test.xlsx";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename='.$file);
$content = "Col1\tCol2\tCol3\t\n";
$content .= "test1\ttest1\ttest3\t\n";
$content .= "testtest1\ttesttest2\ttesttest3\t\n";
echo $content;
But I get this error when I open the generated file:
但是当我打开生成的文件时出现此错误：
Excel cannot open the file 'test.xlsx' because the file format or file extension is not valid.
Excel 无法打开文件“test.xlsx”，因为文件格式或文件扩展名无效。
Been searching for the right code for 1 hour, but I didn't find the solution.
一直在寻找正确的代码 1 小时，但我没有找到解决方案。
回答by Drew
You won't be able to just create one without a library. Have a read through this PHPExcel Githubwhich should point you in the right direction.
如果没有库，您将无法创建一个。通读这个PHPExcel Github，它应该为您指明正确的方向。
EDIT:PHPExcel is DEPRECATED, you may use its successor PHPSpreadsheet, also on GitHub. PhpSpreadsheet
编辑：PHPExcel 已弃用，您也可以在 GitHub 上使用其后继 PHPSpreadsheet。Php电子表格
回答by Trudeau Fernandes
The content is not a valid xlsx content. Try sending your content as a comma separated value content and use xls to open that
该内容不是有效的 xlsx 内容。尝试将您的内容作为逗号分隔值内容发送并使用 xls 打开它
As a suggestion, If u can change your content, May be you could try this?
作为建议，如果您可以更改您的内容，您可以试试这个吗？
$file = "test.csv";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename='.$file);
$content = "Col1,Col2,Col3\n";
$content .= "test1,test1,test3\n";
$content .= "testtest,ttesttest2,testtest3\n";
echo $content;
回答by HHG
May be the file is not compatible with the version of Excel that is used. Try with change your file extension 'xlsx' to 'xls and check it's working or not. if it's working then this file extension(.xlsx) is not compatible with the version of Excel that, you used.
可能是该文件与所使用的 Excel 版本不兼容。尝试将您的文件扩展名“xlsx”更改为“xls”并检查它是否正常工作。如果它正常工作，则此文件扩展名 (.xlsx) 与您使用的 Excel 版本不兼容。
回答by billynoah
As others have mentioned, PhpSpreadsheetprovides a nice library for this. Assuming you have it installed via composerand the vendor/autoload.phphas been included in your project, you can use the function below to generate an xlsxfrom an array of arrays. I've included extensive comments here to help teach beginners about how PhpSpreadsheet works and what the code is doing:
正如其他人提到的，PhpSpreadsheet 为此提供了一个不错的库。假设你已经通过 composer 安装了它并且vendor/autoload.php已经包含在你的项目中，你可以使用下面的函数从数组的数组中生成一个xlsx。我在此处包含了大量注释，以帮助向初学者介绍 PhpSpreadsheet 的工作原理以及代码的作用：
function writeXLSX($filename, $rows, $keys = [], $formats = []) {
// instantiate the class
$doc = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $doc->getActiveSheet();
// $keys are for the header row. If they are supplied we start writing at row 2
if ($keys) {
$offset = 2;
} else {
$offset = 1;
}
// write the rows
$i = 0;
foreach($rows as $row) {
$doc->getActiveSheet()->fromArray($row, null, 'A' . ($i++ + $offset));
}
// write the header row from the $keys
if ($keys) {
$doc->setActiveSheetIndex(0);
$doc->getActiveSheet()->fromArray($keys, null, 'A1');
}
// get last row and column for formatting
$last_column = $doc->getActiveSheet()->getHighestColumn();
$last_row = $doc->getActiveSheet()->getHighestRow();
// autosize all columns to content width
for ($i = 'A'; $i <= $last_column; $i++) {
$doc->getActiveSheet()->getColumnDimension($i)->setAutoSize(TRUE);
}
// if $keys, freeze the header row and make it bold
if ($keys) {
$doc->getActiveSheet()->freezePane('A2');
$doc->getActiveSheet()->getStyle('A1:' . $last_column . '1')->getFont()->setBold(true);
}
// format all columns as text
$doc->getActiveSheet()->getStyle('A2:' . $last_column . $last_row)->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);
if ($formats) {
// if there are user supplied formats, set each column format accordingly
// $formats should be an array with column letter as key and one of the PhpOffice constants as value
// https://phpoffice.github.io/PhpSpreadsheet/1.2.1/PhpOffice/PhpSpreadsheet/Style/NumberFormat.html
// EXAMPLE:
// ['C' => \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_00, 'D' => \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_00]
foreach ($formats as $col => $format) {
$doc->getActiveSheet()->getStyle($col . $offset . ':' . $col . $last_row)->getNumberFormat()->setFormatCode($format);
}
}
// write and save the file
$writer = new PhpOffice\PhpSpreadsheet\Writer\Xlsx($doc);
$writer->save($filename);
}
EXAMPLE USAGE:
用法示例：
$rows = [
['sku' => 'A123', 'price' => '99'],
['sku' => 'B456', 'price' => '5.35'],
['sku' => 'C789', 'price' => '17.7']
];
$keys = array_keys(current($rows));
$formats = ['B' => \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_00];
writeXLSX('pricelist.xlsx', $rows, $keys, $format);
回答by Sergey Shuchkin
$books = [
['ISBN', 'title', 'author', 'publisher', 'ctry' ],
[618260307, 'The Hobbit', 'J. R. R. Tolkien', 'Houghton Mifflin', 'USA'],
[908606664, 'Slinky Malinki', 'Lynley Dodd', 'Mallinson Rendel', 'NZ']
];
$xlsx = SimpleXLSXGen::fromArray( $books );
$xlsx->saveAs('books.xlsx');
回答by HHG
See my code, here I have made simple xlsx file
查看我的代码，这里我制作了简单的 xlsx 文件
<?php
include 'PHPExcel/PHPExcel.php';
include 'PHPExcel/PHPExcel/Writer/Excel2007.php';
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Hello');
$objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Trudeau');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Fernandes');
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
echo " Click here for gerate xlsx file <a href='test.xlsx'>clicking me</a>";
?>
and for me, it's working fine.
对我来说，它运行良好。
