如何在不使用任何 excel 库 PHP 的情况下创建 xlsx 文件

How to create xlsx file without using any excel library PHP

提问by Daric

This is right now I am using.

这是我现在正在使用的。

$mimeType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
header('Content-Description: File Transfer');
header('Content-Type: ' . $mimeType);
header('Content-Disposition: attachment; filename='.basename($type.'.xlsx'));
header('Content-Transfer-Encoding: binary');
header('Expires: 0');   
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
print "$header\n$data";
exit;

$headervariable contains the header row of excel to be generated and looks like this

$header变量包含要生成的 excel 的标题行，如下所示

$header= "Business_Name\tBusiness_Type\tType";

$header= "Business_Name\tBusiness_Type\tType";

separated by \t

隔开 \t

and $datacontains rows to be generated under header columns. They are also separated by \tand a row is terminated by \n.

并$data包含要在标题列下生成的行。它们也由 分隔，\t一行以 结束\n。

With the current setup file is downloaded but it is not opening with ms excel and showing this message.

下载了当前的安装文件，但它没有用 ms excel 打开并显示此消息。

Excel cannot open the file "file name" because the file format or file extension is not valid. Verify that the file format has not been corrupted and that the file extension matches the format of the file.

Excel 无法打开文件“文件名”，因为文件格式或文件扩展名无效。验证文件格式未损坏并且文件扩展名与文件格式匹配。

What header should be sent to server? or how do I generate that file?

应该向服务器发送什么标头？或者我如何生成该文件？

采纳答案by Skrol29

What you have is actually a CSV file. Depending on your OS, your browser and your Excel version, then the browser will differently let you or not let your open the extensions CSV, XLS XLSX with the Excel software.

您拥有的实际上是一个 CSV 文件。根据您的操作系统、浏览器和 Excel 版本，浏览器将允许或不允许您使用 Excel 软件打开扩展 CSV、XLS XLSX。

If you do want to have your data opened with Excel, then you can merge the data with an Excel template using OpenTBS. Use version 1.6.0 (or greater) which is currently in Release Candidate because it brings major facilities for Excel files.

如果您确实希望使用 Excel 打开数据，则可以使用OpenTBS将数据与 Excel 模板合并。使用当前处于 Release Candidate 中的 1.6.0（或更高版本）版本，因为它为 Excel 文件提供了主要功能。

In your title there is "no excel library PHP". I don't know why you have this specification but OpenTBS is not exactly an Excel library. It's a PHP tool for merging OpenOffice and Ms Office documents using templates.

在您的标题中有“没有 excel 库 PHP”。我不知道你为什么有这个规范，但 OpenTBS 不完全是一个 Excel 库。它是一个 PHP 工具，用于使用模板合并 OpenOffice 和 Ms Office 文档。

回答by Supernovah

I achieve this in a fast, sort of cheapskate way - because it's long and winded I'll just explain it in concept rather than code.

我以一种快速、小气的方式实现了这一点——因为它冗长而啰嗦，我将只用概念而不是代码来解释它。

XLSX adheres to ISO 29500 which is publicly available if you want to manipulate a document thoroughly in php. Otherwise, realise that xlsx files are zipped archives of a bunch of xml files.

XLSX 遵守 ISO 29500，如果您想在 php 中彻底操作文档，该标准是公开可用的。否则，请意识到 xlsx 文件是一堆 xml 文件的压缩档案。

Make a template that you want, say it has alternating rows with styles of different types, making that in excel or an open xml editor of some description. Make sure you put some data in there, and make sure some fields are equal (just for learning purposes).

制作一个你想要的模板，假设它有不同类型样式的交替行，在 excel 或一些描述的打开的 xml 编辑器中制作。确保将一些数据放入其中，并确保某些字段相等（仅用于学习目的）。

Then save your file as xlsx, rename it .zip, or open it in an archive extractor and observe the contents.

然后将您的文件另存为 xlsx，将其重命名为 .zip，或在存档提取器中打开它并观察其内容。

Firstly, note the [Content_Types].xml file, this describes the location of the major files in the archive and the standards to which it itself adheres and the content types of those files.

首先，请注意 [Content_Types].xml 文件，它描述了存档中主要文件的位置以及它本身遵循的标准以及这些文件的内容类型。

Everything outside the xl/folder is just meta data really. But observe docProps/core.xmlcontains author, modification and timestamp information - which you can replace in php when you recreate this file. Also everything that is pointed to say, docProps/core.xmlcan be renamed to your tastes, [Content_Types].xmlcan't.

xl/文件夹外的所有内容都只是元数据。但是observedocProps/core.xml包含作者、修改和时间戳信息——当你重新创建这个文件时，你可以在php 中替换这些信息。此外，所指的一切都docProps/core.xml可以根据您的口味重命名，[Content_Types].xml不能。

Okay so now you understand this, you'll begin observing ids thrown around the place. They love to use this in the file format, everything refers to everything else by its index in a particular xml property list or similar. They also usually describe the quantity of items in such lists.

好的，现在你明白了这一点，你将开始观察周围抛出的 id。他们喜欢在文件格式中使用它，所有内容都通过特定 xml 属性列表或类似内容中的索引来引用其他所有内容。它们通常还描述此类列表中的项目数量。

In xl/you'll see themes.xml, styles.xml, workbook.xml, sharedStrings.xml, _rels/, worksheets/.

在xl/你会看到themes.xml, styles.xml, workbook.xml, sharedStrings.xml, _rels/, worksheets/。

Styles is going to be inflated with a whole lot of unnecessary styles that excel builds by default if you used it. But you should be able to see how these styles work such that you can customise your own.

如果您使用它，样式将被大量不必要的样式膨胀，这些样式默认情况下会很好地构建。但是您应该能够看到这些样式是如何工作的，以便您可以自定义自己的样式。

Themes to me is rather pointless so I delete it and its referenced ids throughout.

主题对我来说毫无意义，所以我删除了它及其引用的 ID。

Next up you'll see workbook, that's the file containing information regarding the sheets which are inside of the spreadsheet document since you can have more than 1 obviously. It also contains some sheet metadata such as its size etc.

接下来您将看到工作簿，这是包含有关电子表格文档中工作表信息的文件，因为您显然可以拥有 1 个以上的工作表。它还包含一些工作表元数据，例如其大小等。

Now comes the first big hua you'll encounter. sharedStrings.xmlis a weird file which stores all the information that will be inserted into cells in a static spreadsheet. They are indexed, but the engine reading the document figures out what their indexes are. Anything which repeats can be referred back to its old index in the sheet itself (inside worksheets folder) as to save on file size in large documents with repeated values. Not the attributes countand uniquecountin the sstelement and what they obviously mean.

现在来你会遇到的第一个大花。sharedStrings.xml是一个奇怪的文件，它存储将插入到静态电子表格单元格中的所有信息。它们被编入索引，但读取文档的引擎会计算出它们的索引是什么。任何重复的内容都可以返回到工作表本身（在工作表文件夹内）中的旧索引，以节省具有重复值的大型文档中的文件大小。没有属性count，并uniquecount在sst元素和他们明显的意思。

This is the stage in php where you populate an array of data containing what you want in your sheet, and dump it into an xml formatted list such as this file appears. Also note these files don't need to be jammed up without newlines or linefeed characters as with or without is still valid xml and they will work in readers regardless.

这是 php 中的阶段，您在其中填充包含您想要的工作表内容的数据数组，并将其转储到 xml 格式的列表中，例如出现此文件。还要注意，这些文件不需要在没有换行符或换行符的情况下卡住，因为有或没有仍然是有效的 xml，无论如何它们都可以在阅读器中工作。

Check out the _rels folder, it's fairly obvious again.

查看 _rels 文件夹，它再次变得相当明显。

Lastly is the sheet itself. The numbers in fields here refer to the indexed locations of strings in sharedStrings.xml. The attribute s is the style, t is the type of data in the field. R is the cell location though why it needs that is beyond me when it could really be figured out rather easily.

最后是表本身。此处字段中的数字指的是 中字符串的索引位置sharedStrings.xml。属性 s 是样式，t 是字段中的数据类型。R 是单元格位置，尽管当它真的可以很容易地弄清楚时，它为什么需要它超出了我的范围。

Producing this file in php shouldn't be too difficult either. Just use your indexes from your data array you used to make your sharedStrings.xmlfile.

在 php 中生成这个文件也不应该太困难。只需使用用于制作sharedStrings.xml文件的数据数组中的索引即可。

Oh also sheet has column width information in it which you can figure out based on the font you used and automatically size them in php too if need be.

哦，工作表中还有列宽信息，您可以根据使用的字体确定这些信息，并在需要时在 php 中自动调整它们的大小。

Lastly is the packaging of it all in php.

最后是将其全部打包在 php 中。

My code is in a class which receives data and specific saved files I created with excel to keep it simple.

我的代码在一个类中，它接收数据和我用 excel 创建的特定保存文件，以保持简单。

$this->folder_structure_simple = Array(
    "_rels/.rels" => "_rels__rels",
    "docProps/app.xml" => "docProps_app_xml",
    "docProps/core.xml" => "docProps_core_xml",
    "xl/_rels/workbook.xml.rels",
        "xl/theme/theme1.xml",
        "xl/worksheets/sheet1.xml",
        "xl/sharedStrings.xml",
        "xl/styles.xml",
        "xl/workbook.xml",
    "[Content_Types].xml" => "Content_Types_xml"
);

$zip = new ZipArchive;
$res = $zip->open($this->file_name, ZipArchive::CREATE);
if($res === TRUE){
    foreach($this->folder_structure_simple as $file => $function){
            $zip->addFromString($file, $this->$funtion);
    }
    $zip->close();
    echo 'ok';
}else{
    return FALSE;
}

And functions produce the required data. Very fast, not very flexible.

函数产生所需的数据。非常快，不是很灵活。

回答by John Green

What you have a CSV, not an XLSX file. XLSX is a ZIP-wrapped blob of XML. Change your MIME type to text/csv.

你有一个 CSV，而不是一个 XLSX 文件。XLSX 是一个 ZIP 包装的 XML blob。将您的 MIME 类型更改为 text/csv。