Author: Dave Phillips
I'm working with Excel using the Spreadsheet functions in CF 9. I have some
weird behavior going on with formulas, and I have a workaround, but I don't like
it, so I'm looking for some direction.
I have an Excel 2007 spreadsheet. The spreadsheet has simple formulas on it that
sum other cells. Here's what I do:
1. Open the Excel 2007 with ColdFusion 9 SpreadsheetRead()
2. Set some values in some of the cells
3. save the file with SpreadsheetWrite()
Now, if I open the spreadsheet with Excel 2007 (on my desktop), the cells with
the formulas have not 'recalculated' to reflect the newly entered values in the
cells which they are summing. I don't know whether this is intended behavior or
not, but it's not the behavior I want.
So, here's my workaround.
1. On my desktop, I open the file in Excel 2007.
2. I save the file 'as' Excel Type 97-2003 (.xls)
3. When I save as this type I get a dialog asking me if I want to check
compatibility when opening this file. I 'uncheck' the option and click continue.
4. I then get a dialog asking if I want this workbook to 'recalculate' every time
it is opened. I say 'YES'.
5. The file is now saved as an .xls file.
Now, when I work with this .xls file, and update the values in the respective
cells with CF and then open the final version, all the formulas reflect the
So, the bottom line is this issue is forcing me to use an older version of Excel
I know that Excel 2007 files are built differently and utilize XML, but what I
don't understand is why the recalculation is not happening automatically.
Is there some 'underlying' function in the code CF is using that will force a
'recalculate' in the Excel 2007 file when I have it open in CF? Or is there some
other solution someone has an idea for?