使用Excel在PHP应用程序中进行核心分析? [英] Use Excel to do core analysis in a PHP application?

问题描述

我认为这真的是关于方法的问题.

I think this is really a question about approach.

我们有一个来自第三方的核心Excel电子表格，它提供了一些原始数据的复杂分析.原始数据直接从客户的第二个电子表格中获取.

We have a core Excel spreadsheet that comes in from a third party, and it provides some complex analysis of raw data. The raw data arrives directly from customers in a second spreadsheet.

我们正在寻求创建一个基于Web服务器的自动化系统，可以在其中上传原始数据Excel文件并进行分析，并生成报告.

We are looking to create an automated web-server based system where the raw data Excel file can be uploaded and the analysis performed, and a report generated.

生成报告没问题，但是实际上在应用程序中使用核心Excel电子表格本身进行数学运算(例如，使用PHPExcel)有什么意义吗?还是最好的方法始终是用代码复制分析?

The report generation is no problem, but is there any mileage in actually using the core Excel spreadsheet itself within the application to do the mathematics (using PHPExcel for example)? Or is the best approach always to replicate the analysis in code?

我们的想法是，分析(来自第三方)相当复杂，因此在PHP中进行复制需要花费大量的时间，而第二部分的分析通常会被第三方进行调整.我们会定期向我们提供新版本的Excel.

Our thinking is that the analysis (which comes in from a third party) is fairly complex, so will take quite a lot of unpicking to replicate in PHP, and secondly parts of the analysis are often being tweaked by the third party meaning new versions of the Excel are regularly provided to us.

我可以想象使用PHPExcel进行类似的操作，在内存中创建工作表并将原始数据合并为第二个工作表，然后读取计算出的结果，但这是现实的-太慢了吗-还是只是根本不符合我的想象?

I could imagine using PHPExcel to do something like this, creating the sheet in memory and merging the raw data in as a second worksheet, then reading out the calculated results, but is this realistic - would it be too slow - or just simply not work how I am imagining?

任何想法都将不胜感激.

Any thoughts would be much appreciated.

推荐答案

根据Excel公式的复杂性，使用PHPExcel的计算引擎可能是完全可行的，但您需要注意一些潜在的陷阱".

Depending on the complexity of the Excel formulae, it can be perfectly viable to use PHPExcel's calculation engine, but you'd need to watch out for a few potential "gotchas".

• 并非完全支持所有Excel功能(提供了一个列表 在PHPExcel文档中，但我认为356个函数中有316个 现在受支持)
• 分析工具包中的Excel函数不是 BIFF(.xls)工作簿受支持；尽管他们将与 OfficeOpenXML(xlsx)工作簿
• 用户定义的函数不是 支持
• 不支持宏和其他VB代码
• 数组公式 尚不支持(这是计划中的重要部分 将于今年年底开始重新计算计算引擎)
• 不支持单元格引用的R1C1格式

• Not all Excel functions are fully supported (There is a list provided in the PHPExcel documentation, but I think 316 functions out of 356 are now supported)
• Excel functions from the analysis toolpack are not supported for BIFF (.xls) workbooks; though they will work with OfficeOpenXML (xlsx) workbooks
• User-defined functions are not supported
• Macros and other VB code are not supported
• Array formulae are not yet supported (this is a significant part of the planned rewrite of the calculation engine due to start by the end of the year)
• R1C1 format for cell references is not supported

它相对较慢(这是需要重写的部分原因).当然，您可以编写PHP代码来更有效地执行所需的公式，因为您可以根据自己的特定要求进行编码. PHPExcel是否会太慢?只有您可以确定太慢"的实际含义.

It is relatively slow (part of the reason it needs a rewrite). You could certainly write PHP code that would execute the formulae you need more efficiently, because you could code to your specific requirements. Whether PHPExcel would be too slow? Only you can determine what "too slow" actually means.

请注意，通常建议提交大量的内存或耗时任务的请求以在Web服务器外部运行

Note that it's often better to submit requests for extensive memory- or time-consuming tasks to run outside of the web server

另一方面，如果您的客户曾经更改过电子表格(并且您表示这很频繁)，那么使用PHPExcel的内置计算引擎意味着它仍然可以正常工作而没有问题，而自定义编写的代码则需要修改. 正如您已经指出的那样，复杂的公式可能需要大量工作才能转换为PHP代码.而在电子表格中取消选择对公式所做的更改将特别困难.

On the other hand, if your client ever changes their spreadsheet (and you indicate that this is fairly frequent), then using PHPExcel's built-in calculation engine means that it would still work without problems, whereas custom-written code would need modification. As you've already pointed out, complex formulae could require a lot of work converting to PHP code; and unpicking changes made to formulae in a spreadsheet would be particularly difficult.

这篇关于使用Excel在PHP应用程序中进行核心分析?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！