在OpenXML sdk2中以编程方式从公式计算单元格值 [英] calculate cell values from formula programmatically in OpenXML sdk2

问题描述

我正在使用OpenXML SDK2.0进行excel计算，并且卡在1件东西上.

I'm using OpenXML SDK2.0 for excel calculations and stuck with 1 thing.

基本上，我正在用数据库中的数据填充excel文件并将其发送给用户...该位工作正常.现在，我想在其中添加公式后获得计算出的单元格值，但无需打开excel文档... 有没有办法用openxml sdk2.0填充excel文件并立即获得计算值?

Basically I'm populating an excel file with data from database and sending it to the user...that bit works fine. Now I want to get the calculated cell value after I'm putting a formula in it, but without opening the excel document... Is there a way to populate excel file with openxml sdk2.0 and get calculated values back straight away?

也许有人遇到类似问题或有可行的解决方案！

Maybe someone has encountered similar problem or has a working solution !

谢谢:)

推荐答案

spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;  
spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true; 

当您手动打开Excel时，这将很好地工作.但是，也无法以编程方式以编程方式获取单元格值.如果要通过Open Xml SDK获取单元格值，则必须通过以下方式打开excel文件: Excel应用程序，并保存文件.然后可以通过编程获取单元格值.

this will work very well,when you open the Excel manually.but also can't Programmatically get the cell value with formulae.If you want to get the cell value by Open Xml SDK , have to open the excel file backgroud by Excel Application,and save the file. then can get the cell value programmatically.

这是来自Office开发中心的信息:

This is the Information from Office Development Center:

如何自动重新计算电子表格中单元格的公式值?

具有公式的单元格会将缓存的值存储在标记中，并将缓存的值显示为结果.如果公式引用的单元格值以编程方式更改，则具有该公式的单元格将不知道该更改.然后该公式的缓存值已过时，应进行更新.自动重新计算需要运行时布局功能，SDK不支持该功能.这种情况的解决方法是:删除公式单元格的缓存值，然后调用Excel在后端为您重新计算该值. Excel服务可以在这里提供帮助.

A cell with a formula will store the cached value in the markup and show the cached value as the result. If the cell value referenced by the formula is changed programmatically, the cell with the formula has no idea of that change. The cached value of this formula is then obsolete, and should be updated. The automatic recalculation requires runtime layout functionality, which is not supported in the SDK. The workaround for this situation is to: delete the cached value of the formula cell, and then invoke Excel to recalculate the value for you at backend. The Excel service could help here.

这篇关于在OpenXML sdk2中以编程方式从公式计算单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！