POI Excel WorkBook Chart text (Class textRecord) Bug

[BrianO]

Apparently using CF 7 or 8 with POI for Excel converts sheet reference formulas in a chart text box (POI class textRecord) on a chart area to text even if it has a formula reference. I did see that anything the chart itself references works fine, including Chart Options and Source Data.

I have a workbook that I edit a particular sheet i.e. 'DataSheet' using POI successfully with ColdFusion (CF7.02 and CF8). The problem is I have a chart on another sheet in the workbook with a formula referencing 'DataSheet' i.e. 'Chart Title=DataSheet!$AA$2'. Every time I use POI to modify 'DataSheet', my formulas convert the previous value to text, but the formula in Chart Title= [blank]. The main formula value I needed refers to when the data was last updated by POI that I store in the 'DataSheet'. There is a Macro and the chart itself and their references are both fine, just the standalone formula references. Any ideas why this occurs or how to fix it without recreating the formulas each time? I tried protecting the workbook and chart sheets, but that did not help. Also I see how to set formulas in a worksheet, but not for a TextRecord. How could I do that? Here is some of my code that may be of interest:
<!--- store the packages in a variable for more compact code --->
<cfset loHssfPkg = "org.apache.poi.hssf.usermodel">
<cfset loPoifsPkg = "org.apache.poi.poifs.filesystem">
<!--- Modify a multi-sheet Excel report using POI-HSSF --->
<!--- Create the file input stream. --->
<cfset loFile = createObject("java","java.io.FileInputStream").init("#lcExcelFileFullPath#") />
<!--- Create the Excel file system object. This object is responsible
for reading in the given Excel file. A handle to the file. --->
<cfset loFs = createObject("java","#loPoifsPkg#.POIFSFileSystem").init("#loFile#") />
<!--- Create a handle to the workbook. --->
<cfset loWorkBook = CreateObject("java", "#loHssfPkg#.HSSFWorkbook").init(loFs)>

<!--- Get total number of sheets in the workbook. --->
<cfset loSheetCount = loWorkBook.GetNumberOfSheets()>
<!--- Now that we have a sheets collection lets get the one we
want to update --->
<!--- This is an index loop, but is like a while loop here. Java is 0-based. --->
<cfloop index= "loSheetNum" from="0" to="#loSheetCount#" step = "1">
<!--- Obtain the name of the specific sheet in the workbook --->
<cfset loSheetName = loWorkBook.GetSheetName(JavaCast( "int", loSheetNum)) />
<!--- Search for a match to our sheet of interest. --->
<cfif UCASE(loSheetName) EQ loDataSheetName>
<!--- Get a reference to the Sheets in the Excel spreadsheet. It looks like
org.apache.poi.hssf.usermodel.HSSFSheet@af4653 .--->
<cfset loSheet = loWorkBook.GetSheetAt(JavaCast( "int", loSheetNum)) />
<!--- Break out of loop if condition is met. This is like a while loop. --->
<cfbreak>
</cfif>

</cfloop>
[Here I make my mods to the worksheet]
...
<!--- Get a handle on the file location for the workbook. --->
<cfset loFileOut = createObject("java","java.io.FileOutputStream").init("#lcExcelFileFullPath#")/>
<!--- Write the output to the file --->
<cfset loWorkBook.write(loFileOut) />

<!--- Close the file output stream. This will release any locks on
the file and finalize the process. --->
<cfset loFileOut.flush() />
<cfset loFileOut.close() />

[Guest]

I see you posted this in 2003, but I'm putting what I've found for anyone else out there like me that couldn't find the answer and kept looping back to this email.

I am using POI 3.0.1

I created a template spreadsheet that had 3 worksheets: a chart, raw data, and a work area that organized the data for use with the chart. The chart looked fine in the template. I ran my application using POI to update the raw data area and when I opened the file. The work area values were now text with the yellow exclamation mark, indicating a problem.

Solution:
I found that if I used the Worksheet name then it didn't do this. So, originally it was "=A1", but if I changed it to "=DataSheet!A1" then this problem did not occur. Maybe this was fixed since 2003.

Another problem:
I still had a problem because even though the cell format was good and the data in the work area is valid, the chart did not update. I had 2 charts, one updated and one did not. The one that worked was a Column Chart, the one that didn't work was the Bar chart. I tried several things to refresh the chart:
- I recorded a Macro to execute CTRL+SHIFT+ALT+F9, which is supposed to refresh everything and execute that when the document loaded by calling it from Workbook_Open() in the code of the spreadsheet and that did not work.
- I also tried using Application.CalculateFullRebuild in the Workbook_Open() function and that didn't work either.

Solution:
The only way I could get the chart to update after the data changed was to point the chart series to cells that did not have formulas in them. So, I ran my application that uses POI to write the numbers that the chart needed directly into the cells that the Chart was pointing to and it worked?!?! For some reason it doesn't seem that the chart recognized that the values changed when it referred to formulas. So, this is what I am using. If anyone has a better answer, please let me know.

[BrianO]

Actually the post was 08/09/2007 and I still have the same problem. I've been a member since '03. Your first solution " changed it to "=DataSheet!A1" .." is what I was doing. Also I was doing your solution #2 except once it grabs the value, it converts the formula in the chart to text, making it useless the next time. I went to Adobe MAX and spoke with the developers. Their suggestion was to use the .Net integration in CF8, since it seems to be a POI/Excel bug. My client is still on CF7, so I'm stuck for now, but will be trying to understand the .Net integration solution in the meantime. I see the topic is in Forte's latest book Adobe ColdFusion 8: web application construction kit Vol.3. Chapter 74 - Integrating with .NET and chapter 76 - Integrating with Microsoft Office. If we could contact a POI-HSSF developer, perhaps they could fix the bug. I have not had any luck.

[Guest]

Hi Brian,

I thought this was from years past and was throwing comments into nowhere. I was surprised to hear a response. You could tell I never post anything. It is so easy for "them" (not just targeting Adobe) to say that it's not their problem, it's POI.

I'm coming from the opposite side of the spectrum of programming than you are. You are using Coldfusion, and I am using POI from RPG on an iSeries main frame. So, let's eliminate the proprietary stuff and target POI as being the problem using straight-up Java and POI.

I believe the problem lies in the code that you did NOT provide, "making modifications to the cell values".

In my RPG program, I prototype the call to the Java Method and specify the parameter type to be numeric, so I force it to acknowledge a number.

Maybe ColdFusion is passing everything to POI as a string. Does your modification code look like this?

cell.setCellValue(new HSSFRichTextString("777");
OR
cell.setCellValue(777);

If it does look like the second line, then it should be good. Unless, the parameter is a variable that Coldfusion sets as a string. Maybe you could explictly define the variable type and pass that in the variable.

int tempInt = 777;
cell.setCellValue(tempInt);

Anyhow. If you include the cell modification code in the next response, I will see if I could find anything. I will put up another post (when I have time) to illustrate the chart update problem that I have been having that is definately an excel thing, not a POI thing.

[BrianO]

I don't think I'm communicating the issue. I did provide the formula that is in a text box on a chart on a seperate sheet in the workbook. The issue is POI related to the chart formula that I'm not even touching directly. It's as if POI takes the chart and says "Oh you have a formula in it. Forget that. I'll make it text." Even though I never touched the Chart sheet directly in the workbook. I only modified the sheet it refers to in the same workbook.
From my above post: "...The problem is I have a chart on another sheet in the workbook with a formula referencing 'DataSheet' i.e. 'Chart Title=DataSheet!$AA$2'. Every time I use POI to modify 'DataSheet', my formulas convert the previous value to text, but the formula in Chart Title= [blank]. The main formula value I needed refers to when the data was last updated by POI that I store in the 'DataSheet'. There is a Macro and the chart itself and their references are both fine, just the standalone formula references. ..."

Also note I also said: "...Also I see how to set formulas in a worksheet, but not for a TextRecord. How could I do that? ..." Where the TextRecord is object embedded in the chart itself that holdes the problem formula, not in a cell. If it were in a cell, it would be no problem. I even offered that as an option to my client, but they said no.

[Guest]

For worksheets containing formula use:

     sheet.setForceFormulaRecalculation(true);

This will update all cell values and charts will render correctly.