2012-09-28 09:09
浏览 17


I think this is really a question about approach.

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.

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.

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?

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.

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.

图片转代码服务由CSDN问答提供 功能建议


我们有一个 来自第三方的核心Excel电子表格,它提供了一些原始数据的复杂分析。 原始数据直接从客户那里到达第二个电子表格。

我们正在寻找一个基于Web服务器的自动化系统,可以上传原始数据Excel文件并执行分析, 生成的报告。

生成报告没有问题,但实际上在应用程序中使用核心Excel电子表格进行数学运算有多少里程(例如使用PHPExcel)? 或者总是在代码中复制分析是最好的方法吗?

我们的想法是分析(来自第三方)是相当复杂的,所以需要花费很多 取消在PHP中复制,其次部分分析经常被第三方调整,这意味着我们会定期向我们提供新版本的Excel。

我可以想象使用PHPExcel来做 像这样的事情,在内存中创建工作表并将原始数据合并为第二个工作表,然后读出计算结果,但这是否现实 - 它会太慢 - 或者只是根本无法实现我的想象?


  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • douzhan1031 2012-09-28 09:25

    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".

    • 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

    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.

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

    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.

    点赞 打赏 评论
  • dongqichang7988 2012-09-28 09:28

    Not really a solution to your problem, but the preferred approach would be you build the calculations PHP-side, and then get clients/third-parties to submit data in a specified format (and ideally as a CSV file), and then you parse the data and perform any necessary calculations.

    点赞 打赏 评论

相关推荐 更多相似问题