Home     Products      Support      Corporate     Sign In 
Support Knowledge Base, Article 909
Product
ExcelWriter
Version
All
Title
Values of cells containing formulas are set to 0 by ExcelTemplate
Problem
If a spreadsheet populated with data has cells with formulas, the value of those cells will be reset to 0 by the template object. This can result in incorrect values if you try to pass the template to the application object and read those values. This behavior only applies to the ExcelTemplate object.
Solution

A cell can contain both a formula and a value. When formulas are entered in Microsoft Excel, the formulas are immediately calculated and the cell value is updated. ExcelWriter does not calculate formulas on the server. It sets the formula for a given cell and relies on Microsoft Excel to calculate the value when the spreadsheet is opened on the client machine.

ExcelApplication preserves both formulas and their corresponding values when it opens an existing spreadsheet. This is because ExcelApplication is not only used for writing Excel files, but also for reading Excel files (for example, an application may allow users to edit a spreadsheet and upload it back to the server).*

ExcelTemplate, on the other hand, is a lightweight object used for quickly populating a spreadsheet with data and is not designed to be used for reading values. Therefore, it does not preserve values of cells containing formulas, since it is expected that the generated spreadsheet will be opened and recalculated on the client machine.

Therefore, in order to read values from cells containing formulas that were already calculated in Excel, the spreadsheet should be opened and processed only with the ExcelApplication object.


* Note: ExcelApplication will only return accurate values for a formula that was already calculated in Excel. For formulas referencing cells that are populated with new data on the server, the value of the formula cell cannot be expected to be accurate, since the formula will not be recalculated until the ExcelWriter-generated file is opened on the client machine.
Created : 1/8/2008 5:43:48 PM (last modified : 1/8/2008 5:43:48 PM)
Rate this article!
Comments