Images and formatting not working on a multi-tabbed XLSx file generated with ColdFusion

Question

The code below will generate the file with two notable errors:

The SpreadSheetSetColumnWidth appears to stop working after 21 lines on the second tab
The image does not appear on the second tab

Hopefully this is just a rookie mistake and not something along the lines of the date formatting issue. Any help or constructive criticism to fix/improve would be greatly welcomed.

<!--- We cannot directly reference the image --->
<cfimage source="img/A2LA_Logo.jpg" name="A2LA_Logo">
<!--- We need an absolute path, so get the current directory path.  --->
<cfset theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "Final_Report.xlsx">

<cfscript> 
/*********************************************************************** Page 1 ***********************************************************************/
//Create a new Excel spreadsheet object - SpreadsheetNew("Tab name", "yes=2007+ no=2003");
    Tab1 = SpreadsheetNew("Final Report Page 1","yes"); 

    SpreadSheetSetRowHeight(Tab1,1,45);
    SpreadsheetSetCellValue(Tab1,"Final Test Report",1,1); SpreadsheetMergeCells(Tab1,1,1,1,10);
    SpreadsheetAddImage(Tab1, A2LA_Logo,"jpg","1,12,2,20"); 
    SpreadsheetSetCellValue(Tab1,"Confidential",1,21); SpreadsheetMergeCells(Tab1,1,1,21,30);
</cfscript>

<cfscript>  
/*********************************************************************** Page 2 ***********************************************************************/
//Create a new Excel spreadsheet object - SpreadsheetNew("Tab name", "yes=2007+ no=2003");
    Tab2 = SpreadsheetNew("Final Report Page 2","yes"); 

    SpreadSheetSetRowHeight(Tab2,1,45);
    SpreadsheetSetCellValue(Tab2,"Final Test Report",1,1); SpreadsheetMergeCells(Tab2,1,1,1,10);
    SpreadsheetAddImage(Tab1, A2LA_Logo,"jpg","1,12,2,20"); 
    SpreadsheetSetCellValue(Tab2,"Confidential",1,21); SpreadsheetMergeCells(Tab2,1,1,21,30);
</cfscript> 

<!--- There must be a better way --->
<cfscript> 
    for(index=1; index LTE 30; index++) {SpreadSheetSetColumnWidth(Tab1,index,3);}
    for(index=1; index LTE 30; index++) {SpreadSheetSetColumnWidth(Tab2,index,3);}
</cfscript> 

<!--- Write the spreadsheet to a file, replacing any existing file, then append the other tabs.  --->
<cfspreadsheet action="write" filename="#theFile#" name="Tab1" overwrite=true>
<cfspreadsheet action="update" filename="#theFile#" name="Tab2">

<!--- Needs to be at the bottom --->
<cfheader name="Content-disposition" value="attachment;filename=Final_Report.xlsx">
<cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" file="#theFile#">
<!--- Clean up your mess after it is served to the browser --->
<cffile action="delete" file="#theFile#">

IMO `action=update` is kind of buggy. As a troubleshooting step, try saving each tab to a *separate* file with `action=write` (not update). Any change? — Leigh, Feb 28 '13 at 21:01
(Edit) RE: *2) image does not appear on the second tab* Because you are adding it to `Tab1` twice. *1) SpreadSheetSetColumnWidth appears to stop working after 21 lines on the second tab* Not sure what you mean by that. `SpreadSheetSetColumnWidth(sheet, column, width)` applies the given with to the entire column. Can you elaborate or post a screen shot? — Leigh, Feb 28 '13 at 22:06
@Leigh RE: 1)I'll have to try saving each tab separately tommorow. I suspect that will work for the image. How would I go about merging the tabs into a single workbook? — BTThomas, Feb 28 '13 at 22:27
@Leigh RE: 2) Tab1 being used for both was a copy and paste error, it still does not work with tab2. RE: 1) I'll add a screen shot tommorow, the width of first 21 columns is correctly set and columns 22-30 remain at the default width. — BTThomas, Feb 28 '13 at 22:41
The images work when saved separately, so I am guessing it is an `update` bug. My usual recommendation is "do not use `update` if you can avoid it". [Create a single workbook, add multiple sheets and save it with a single `write` action](http://stackoverflow.com/questions/15010472/spreadsheet-cell-formatting/15027832#15027832). — Leigh, Feb 28 '13 at 22:45
@Leigh Spot on, do you wan to put that in as an answer. I don't understand why fully, but it works and at the end of the day thats what counts. — BTThomas, Mar 01 '13 at 13:31

score 2 · Accepted Answer · edited May 23 '17 at 12:27

(From the comments)

The images show up when saved separately. So I am guessing it is an update bug. My usual recommendation is "do not use update if you can avoid it". Create a single workbook, add multiple sheets and save it with a single write action.

Update: As for why, in your original code, you are creating two separate workbook objects. Conceptually think of it like creating two separate Excel files, with one sheet each. Then trying to merge them into one file:

     c:/path/to/MyWorkbookNamedTab1.xlsx   

          | Sheet1 / 

     c:/path/to/MyWorkbookNamedTab2.xlsx   

          | Sheet1 /

That is different than creating a single workbook, with multiple sheets (like in the link above):

     c:/path/to/MySingleWorkbook.xlsx   

          | Sheet1 / Sheet2 /

There is no easy way to merge separate files (or workbooks). You basically have to copy everything from one workbook into another (which is a lot more involved than it sounds). That is what action=update does. Somewhere in the copying process it loses one of the images. I am not sure if it that is due to a CF bug one in POI.

In any case, the simplest solution is to avoid the need for copying altogether. If you stick with a single workbook (and add multiple sheets to it) then there is no need for copying or merging. Everything is already contained in one workbook. So all action=write has to do is save it to disk, exactly "as is".

Images and formatting not working on a multi-tabbed XLSx file generated with ColdFusion

1 Answers1