vba 如何使用 PHP 将数据添加到现有 Excel 文件中？

How to add data to an existing Excel file with PHP?

提问by Nicero

I have a Excel file with forms and controls. As it is not possible to create a Excel file with forms, controls and VBA code from scratch using PHP, I thought I could upload my empty Excel file to the server, update it and then let the user download it.

我有一个包含表单和控件的 Excel 文件。由于无法使用 PHP 从头开始​​创建带有表单、控件和 VBA 代码的 Excel 文件，我想我可以将我的空 Excel 文件上传到服务器，更新它，然后让用户下载它。

I know there are some very good PHP classes as PHPExcel, but I don't know if these classes may append data as said. Does anybody has already done this before?

我知道有一些非常好的 PHP 类，如 PHPExcel，但我不知道这些类是否可以像所说的那样附加数据。有没有人以前做过这个？

回答by Steve B

Think in the other direction!

换个方向想想！

Instead of pushing data to excel from php, add a data connection to excel that will retrieve its data from a php endpoint taht publish xml.

不是将数据从 php 推送到 excel，而是添加到 excel 的数据连接，该连接将从 php 端点 taht 发布 xml 检索其数据。

Don't know if it applies, but it's far more easier to implement.

不知道它是否适用，但它更容易实现。

Moreover, you will have total control on the excel file, keeping all customisations, including VBA.

此外，您将完全控制 Excel 文件，保留所有自定义设置，包括 VBA。

And finally, you will avoid all messy COM object managing if you end up in this direction.

最后，如果您最终朝着这个方向发展，您将避免所有混乱的 COM 对象管理。

[Edit] Steps for this solution :

[编辑] 此解决方案的步骤：

1. Build a php endpoint that publish the XML data (I have actually no php knowledge. Just ensure that calling yourdata.phpwill produce the data in XML).
2. In excel, using a Data connection, insert the content of this XML file: Go to Data TAB (assuming Excel 2010), "From other source", "From Xml data import".
3. let the wizard do the job

1. 构建一个发布 XML 数据的 php 端点（我实际上没有 php 知识。只需确保调用yourdata.php会以 XML 格式生成数据）。
2. 在 excel 中，使用数据连接，插入此 XML 文件的内容：转到数据选项卡（假设为 Excel 2010）、“来自其他源”、“来自 Xml 数据导入”。
3. 让向导完成工作

After that, you will have a list in Excel, than is linked to your xml source. SImply right lick the list / refresh to get the latest data (or configure the data connection to auto refresh at opening).

之后，您将在 Excel 中获得一个列表，然后链接到您的 xml 源。只需右键单击列表/刷新即可获取最新数据（或将数据连接配置为打开时自动刷新）。

回答by ihightower

@SteveB Further to my comment and your response earlier.. pls read below.

@SteveB 关于我之前的评论和您的回复……请阅读以下内容。

@SteveB Looks like an excellent idea.. but why use xml? i try to use xml but i don't know what format to write the xml in text file? can you explain. can we also just use .csv? as it looks easy to write a simple .csv file. How will the whole solution work.. say i am the user.. who clicks and opens a php page.. and where should the excel template be stored.. and how it will pop to user.. what happens when another user connected to same page, etc. I want to keep pivot tables, excel macros and vba, images and charts in the template file. – ihightower

@ihightower: xml is easier to produces than CSV (at least using .Net and C#, which is my primary programming language), but this can works with CSV too I think. Providing the Excel template is easy... simply serve it as a static content, as the excel file is not dynamically generated. – Steve B

@SteveB 看起来是个好主意……但为什么要使用 xml？我尝试使用 xml，但我不知道在文本文件中写入 xml 的格式是什么？你可以解释吗。我们也可以只使用 .csv 吗？因为编写一个简单的 .csv 文件看起来很容易。整个解决方案将如何工作.. 说我是用户.. 点击并打开一个 php 页面.. excel 模板应该存储在哪里.. 以及它将如何弹出给用户.. 当另一个用户连接到时会发生什么相同的页面等。我想在模板文件中保留数据透视表、excel 宏和 vba、图像和图表。– 高塔

@ihightower：xml 比 CSV 更容易生成（至少使用 .Net 和 C#，这是我的主要编程语言），但我认为这也适用于 CSV。提供 Excel 模板很容易...只需将其作为静态内容提供，因为 Excel 文件不是动态生成的。– 史蒂夫 B

---

---

My finidings..

我的结论..

XML vs CSV

XML 与 CSV

CSV format for import is only good if Excel Table format is NOT required. You cannot have the alternate rows highlighting, column headers available during scroll, etc. in summary, for what i found you cannot have external data and table format at the same time.

CSV 格式仅适用于不需要 Excel 表格格式的情况。总而言之，您不能突出显示备用行、滚动期间可用的列标题等，因为我发现您不能同时拥有外部数据和表格格式。

The error you will get converting the data connection table to "Format as table" is this:

将数据连接表转换为“格式为表”时将出现的错误是：

 Your selection overlaps one or more external data ranges.
 Do you want to convert the selection to a table and remove
 all external connection?

Of course, I do not want that.. but using xml format for import data connection... the table format can stay like this screenshot:

当然，我不希望那样......但是使用xml格式进行导入数据连接......表格格式可以保持如下截图：

And, when you scroll past one screen full.. the screen will look like this.. you can see the column headers A, B, C is automatically converted to the data header (ISBN, date, Title, ...)

而且，当您滚动一整屏时.. 屏幕将如下所示.. 您可以看到列标题 A、B、C 自动转换为数据标题（ISBN、日期、标题等）

So, we should go for an xml solution.. which we are figuring out how using php. But, using CSV for testing purposes with about 10 columns x 20,000 rows data.. it works very nicely. The refresh from web server also works very well.

所以，我们应该寻找一个 xml 解决方案......我们正在弄清楚如何使用 php。但是，使用 CSV 进行大约 10 列 x 20,000 行数据的测试，效果非常好。从 Web 服务器刷新也很有效。

Put All Piece Togeter

把所有部分放在一起

I have had an internal discussion with my team and we think this will really work for us. Now, we are brainstorming on the delivery and flow of user actions to deliver the Excel template reports with data which he generated from php.

我与我的团队进行了内部讨论，我们认为这对我们真的有用。现在，我们正在就用户操作的交付和流程进行头脑风暴，以交付带有他从 php 生成的数据的 Excel 模板报告。

Ok.. What we have in start?

好的.. 我们有什么开始？

• An Excel Template (e.g. staff_analysis_template.xlsm). This will will have all the pivot table, excel macros, buttons, images, charts, everything an excel file can have in there.
• In the Template file there will be one sheet named "data". This is where the xml or csv import will end up being... readily available for all the pivot tables and charts in the file.
• A php webpage which will have list boxes and text fields for the user to filter on the data.. and he will click search... and this will provide a web page result and and a link to view the same data in the excel template file with all the resulting data in sheet: data.

• Excel 模板（例如，staff_analysis_template.xlsm）。这将包含所有数据透视表、excel 宏、按钮、图像、图表以及 excel 文件可以包含的所有内容。
• 在模板文件中将有一张名为“数据”的工作表。这就是 xml 或 csv 导入最终会... 可用于文件中的所有数据透视表和图表。
• 一个 php 网页，其中有列表框和文本字段供用户过滤数据……然后他将点击搜索……这将提供一个网页结果和一个链接来查看 Excel 模板中的相同数据文件中包含所有结果数据表：数据。

Here is the Excel Template with sample data.. in this screenshot... you can see

这是带有示例数据的 Excel 模板......在这个屏幕截图中......你可以看到

1. Sheet: datawhere the xml data generated from php will be connected and end up in.
2. Sheet: summary. where all the formulas in Excel can be used... and presented to the user which is based on the data.
3. Sheeet: PivotCan have pivot tables and charts which is based on the data and can be made to automatically refresh when the file is opened..
4. VBAis also available.. for example the button can be used to refresh the data or to do any other work.

1. Sheet：从php生成的xml数据将被连接并最终进入的数据。
2. 工作表：摘要。可以使用 Excel 中的所有公式......并根据数据呈现给用户。
3. Sheet: Pivot可以有基于数据的数据透视表和图表，并且可以在打开文件时自动刷新。
4. VBA也可用.. 例如，该按钮可用于刷新数据或执行任何其他工作。

We can basicially use everything Excel has available for us. The resulting xml file can be left on the webserver.. and also data connection can be an URL also or as a file.

我们基本上可以使用 Excel 为我们提供的一切。生成的 xml 文件可以留在网络服务器上..而且数据连接也可以是 URL 或作为文件。

sourceFile="http://xxxxx/test_data3.csv" or

sourceFile="http://xxxxx/test_data3.csv" 或

sourceFile="C:\Users\Fiaz\Documents\Magic Briefcase\Learn\excel\test_data3.csv"

sourceFile="C:\Users\Fiaz\Documents\Magic Briefcase\Learn\excel\test_data3.csv"

Need to figure out

需要弄清楚

The part that are yet to be figured out is how to connect this all together with seamless user experience.. that is the user should not be forced to save the file at first or manually connecting the xml etc.. The excel template must just open right there with the required data and all the analysis available. If user need to save it he will use save as and copy to local machine.

尚未弄清楚的部分是如何将所有这些与无缝的用户体验连接在一起..也就是说，用户不应首先被迫保存文件或手动连接 xml 等.. 必须打开 excel 模板就在那里，拥有所需的数据和所有可用的分析。如果用户需要保存它，他将使用另存为并复制到本地机器。

The idea we have for this seamless user experience is like this:

我们对于这种无缝用户体验的想法是这样的：

1. generate unique xml data file (e.g. YYYYMMDDHHMMSS_username_pagename.xml) and put it on some tmp server folder.
2. copy the template file to a tmp server folder with update connection of the xml file (for this we need to unzip the .xlsx or .xlsm file), update the connection info in (xl/connections.xml) with the file name and folder: YYYYMMDDHHMMSS_username_pagename.xml then zip it and deliver to user through web as .xlsm or .xlsx

1. 生成唯一的 xml 数据文件（例如 YYYYMMDDHHMMSS_username_pagename.xml）并将其放在某个 tmp 服务器文件夹中。
2. 将模板文件复制到带有 xml 文件更新连接的 tmp 服务器文件夹（为此我们需要解压缩 .xlsx 或 .xlsm 文件），使用文件名和文件夹更新 (xl/connections.xml) 中的连接信息: YYYYMMDDHHMMSS_username_pagename.xml 然后将其压缩并以 .xlsm 或 .xlsx 的形式通过网络交付给用户

I tried it manually step by step and it really worked.

我一步一步手动尝试了它，它确实有效。

Will be much appreciated for any ideas on this to make it really better and still keep it simple.

非常感谢您对此的任何想法，以使其真正变得更好并仍然保持简单。

Feedback required from PHPExcel author @MarkBaker:

PHPExcel作者@MarkBaker的反馈要求：

@MarkBaker: From reading through your comments in other posts for PHPExcel, I understand that we CANNOT do the same using PHPExcel (that is to use the original excel template as it is). So, the solution suggested by SteveB and elaborated by me above seems to be much more powerful for this purpose. We can keep the original excel template file in tact with ALL THE ASSETS (pivot table, vba, etc.) with in it as it is. The server need not have Excel as well.

So, can we expect this feature to be available with PHPExcel itself sometime in the future. Then we can be more confident in using the standard technique from the PHPExcel guru himself.

in my opinion PHPExcel is very useful for invoice type of reports and many others but not for business intelligence type of analysis... which requires the use of pivot tables and complicated charts and code, etc.

Please make your comments.. I will be very interested to see it from your view on how it can be made better.

@MarkBaker：通过阅读您在 PHPExcel 其他帖子中的评论，我明白我们不能使用 PHPExcel 做同样的事情（即使用原始的 excel 模板）。因此，SteveB 建议并由我在上面阐述的解决方案似乎为此目的更强大。我们可以将原始 excel 模板文件与其中的所有资产（数据透视表、vba 等）保持原样。服务器也不需要有 Excel。

那么，我们能否期待 PHPExcel 在未来的某个时候提供此功能。然后我们可以更有信心地使用 PHPExcel 大师本人的标准技术。

在我看来，PHPExcel 对于发票类型的报告和许多其他类型的报告非常有用，但不适用于商业智能类型的分析……这需要使用数据透视表和复杂的图表和代码等。

请发表您的意见.. 我将非常有兴趣从您的角度看到它如何变得更好。

Make everything as simple as possible, but not simpler. Albert Einstein

使一切尽可能简单，但不要更简单。艾尔伯特爱因斯坦

回答by Mark Baker

It's pretty easy to add with PHPExcel - load file, add data after last row, save file... unfortunately, forms and VBA code aren't supported.

使用 PHPExcel 添加非常容易——加载文件、在最后一行后添加数据、保存文件……不幸的是，不支持表单和 VBA 代码。

To retain those, you'll need to use COM, which restricts you to a Windows server with MS Excel installed.

要保留这些，您需要使用 COM，这将您限制在安装了 MS Excel 的 Windows 服务器上。

回答by Peon

I don't think you can actually appenddata to an existing excel file.

我认为您实际上无法将append数据写入现有的 excel 文件。

• Rather load all data from Excel into your array ( easy using PHPExcel );
• Add data to your array ( format it as you want );
• Create a new excel file and let user download it ( or save over the original );

• 而是将 Excel 中的所有数据加载到您的数组中（使用 PHPExcel 很容易）；
• 将数据添加到您的数组中（根据需要对其进行格式化）；
• 创建一个新的 excel 文件并让用户下载它（或保存在原始文件上）；

This will simulate append action.

这将模拟追加操作。

回答by Skrol29

OpenTBSis a PHP class PHP, that enables you to build an XLSX (or XLSM, that is XLSX with VBA macros) documents with the technical of templates.

OpenTBS是一个 PHP 类 PHP，它使您能够使用模板技术构建 XLSX（或 XLSM，即带有 VBA 宏的 XLSX）文档。

It's very easy to populate a worksheet. You can also edit forms and VBA since you know their XML structure.

填充工作表非常容易。您还可以编辑表单和 VBA，因为您知道它们的 XML 结构。

By the way OpenTBS also supports DOCX, PPTX, ODT, ODS, ODP, ...

顺便说一下，OpenTBS 还支持 DOCX、PPTX、ODT、ODS、ODP，...

• Demo
• Doc

• 演示
• 文件