Problem using <cfspreadsheet action=write> and <cfspreadsheet action=update>

Report · Jan 21, 2011

Hi,

I am having a problem formating an Excel file with multiple sheets using cfspreadsheet.

I created the excel file from an XML string using POI. Things work perfect till here.

To format the sheets, I am reading each sheet into a different cfspreadsheet object, formating them and then trying to create a new Excel file.

This is where the problem arises. When I do <cfspreadsheet action="write"> the excel file "final_report.xls" should be created with only "Sheet1" having the formated content. But in my case it also writes "Sheet2" in the unformatted form. Then when the <cfspreadsheet action="update"> executes I am getting an error that says that Sheet2 already exists and hence cannot be updated. If I were to do a <cfspreadsheet action="write"> for the second spreadsheet object then I end up with formated Sheet2 and unformatted Sheet1.

This is a really weird behavior as the documentation mentions "To write multiple sheets to a single file, use the write action to create the file and save the first sheet and use the update action to add each additional sheet."

Please suggest a solution.

Here is my code

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>

<head>

<title>XML to Excel</title>

</head>

<body>

<h3>XML to Excel Test</h3><br/>

<!---

Create the ColdFusion XML object that will be used

to create a Microsoft Excel document using ColdFusion

and the POI library.

--->

<class

name="cell"

value="font: 12pt arial ;"

/>

<class

name="header"

value="border-bottom: 2px solid black ; font-weight: bold ;"

/>

<class

name="row"

value="border-bottom: 1px dotted gray ;"

/>

</classes>

<sheet>

<name>Sheet 1</name>

<rows>

<cell>

Name

</cell>

<cell>

Hair Color

</cell>

<cell>

Hotness

</cell>

</row>

<cell>

Christina Cox

</cell>

<cell>

Dirty Blonde

</cell>

9.0

</cell>

</row>

<cell>

Maura Tierney

</cell>

<cell>

Brunette

</cell>

8.0

</cell>

</row>

<cell>

Maria Bello

</cell>

<cell>

Brunette

</cell>

9.5

</cell>

</row>

</rows>

</sheet>

<sheet>

<name>Sheet 2</name>

<rows>

<cell>

Name

</cell>

<cell>

Hair Color

</cell>

<cell>

Hotness

</cell>

</row>

<cell>

Christina Cox

</cell>

<cell>

Dirty Blonde

</cell>

9.0

</cell>

</row>

<cell>

Maura Tierney

</cell>

<cell>

Brunette

</cell>

8.0

</cell>

</row>

<cell>

Maria Bello

</cell>

<cell>

Brunette

</cell>

9.5

</cell>

</row>

</rows>

</sheet>

</sheets>

</workbook>

</cfxml>

<!---

Create a microsoft Excel workbook through the

POI system.

--->

<cfset objWorkbook = CreateObject(

"java",

"org.apache.poi.hssf.usermodel.HSSFWorkbook"

).Init()

/>

<!---

Get global CSS classes that have both a name

and a value attribute.

--->

<cfset arrCSS = XmlSearch(

xmlData,

"/workbook/classes/*[ @name and @value ]"

) />

<cfset arrSheets = XmlSearch(

xmlData,

"/workbook/sheets/*"

) />

<cfloop

index="intSheet"

from="1"

to="#ArrayLen( arrSheets )#"

step="1">

<cfoutput>Name of the sheet : #strSheetName# </cfoutput>

</cfif>

<!---

Create an Excel sheet in the current workbook

with the given name.

--->

<cfset objSheet = objWorkbook.CreateSheet(

JavaCast( "string", strSheetName )

) />

<cfloop

index="intRow"

from="1"

to="#ArrayLen( arrRows )#"

step="1">

<cfset objRow = objSheet.CreateRow(

JavaCast( "int", (intRow - 1) )

) />

<cfloop

index="intCell"

from="1"

to="#ArrayLen( arrCells )#"

step="1">

<cfoutput>#xmlCell.XmlText#</cfoutput>

<cfset objCell = objRow.CreateCell(

JavaCast( "int", (intCell - 1) )

) />

<!---

Set the cell value. Here is where would check

any kind of explicit data types and formatting.

However, for this proof of concept, we are only

going to deal with the String data type.

--->

<cfset objCell.SetCellValue(

JavaCast(

"string",

Trim( xmlCell.XmlText )

)

) />

</cfloop>

</cfloop>

<!---

ASSERT: At this point, we have populated our workbook with

zero or more sheets of data. Now, we have to write the

workbook to the file system.

--->

<!---

Create a file output stream to which we will write

the new workbook binary.

--->

<cfset objFileOutputStream = CreateObject(

"java",

"java.io.FileOutputStream"

).Init(

JavaCast(

"string",

ExpandPath( "./xml_to_excel.xls" )

)

) />

<cfset objWorkbook.Write(

objFileOutputStream

) />

<!---

Close the file output stream. This will release any

locks on the file and finalize the process.

--->

<br/> Reading the excel file using CFSPREADSHEET <br/>

<cfoutput><br/><table>#spreadsheetObj1#</table></cfoutput>

format1 = StructNew();

format1.bold="true";

SpreadsheetFormatRow(spreadsheetObj1 , format1, 1);

SpreadsheetFormatRow(spreadsheetObj2 , format1, 1);

</cfscript>

<br/>Download excel file : <a href="./final_report.xls">Excel File</a>

</body>

</html>

Thanks in advance

Report · Jan 21, 2011

Didn't look over the entirety of your code, but based on your initial statements, I'm curious: why are you reading each sheet into its own spreadsheet object? With the cfspreadsheet methods in CF9, you can add multiple worksheets to the same workbook object and switch between the sheets to apply conditional formatting within each sheet. At the end of it, you can then write the single workbook to a single file.

Report · Jan 24, 2011

The reason for reading each sheet into its own spreadsheet object is that each spreadsheet object represents an Excel sheet and not the entire workbook. These are the usage steps as in the Coldfusion 9 documentation:

Each ColdFusion spreadsheet object represents Excel sheet:

To read an Excel file with multiple sheets, use multiple cfspreadsheet tags with the read option and specify different name and sheet or sheetname attributes for each sheet.
To write multiple sheets to a single file, use the write action to create the file and save the first sheet and use the update action to add each additional sheet.
To update an existing file, read all sheets in the file, modify one or more sheets, and use the contents, and use the write action and Update actions (for multiple sheet files) to rewrite the entire file.

http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec17cba-7f87.html

Report · May 24, 2013

I'm having the exact same problem. I lose formatting after reading all of the sheets into objects, then writing/updating them to form a master sheet. The documentation is pitiful, and there are no examples anywhere on the web for doing this. What I'm doing is pretty simple... I open up the first worksheet in a workbook, populate cells, and then write the entire spreadsheet but none of the other sheets will recognize my data from the first worksheet, unless I read every single sheet of the Excel file then write/update them all out again which causes me to lose formatting on all pages except the first worksheet.... I have wasted literally days on this.

Report · Sep 07, 2014

Yeah well, its a case of me too.

I have to create a spreadsheet file that contains one sheet of statistics for each salesperson.

So I loop over the list of salespeople and create a worksheet object for each one.

On the first, I write out the spreadsheet using cfspreadsheet, and on subsequent, I perform an update, which should have the effect to add the new sheet. Each sheet has a unique name.

I am finding myriad small issues such as:

One Sheet 1, all my formatting works just fine. for example row one consisting of the heading, font size 20 automatically sets the row height
On subsequent sheets, using the exact same code, row one refuses to automatically set the row height, and so I have to insert a spreadsheetsetrowheight command.
I set all of my column widths to suit client specifications, however I am finding that the column widths vary all over the place, and refuse to respond to the spreadsheetsetcolumnwidth command.

But the main issue is that when I have multiple sheets, when the xls or xlsx file is opened using Excel, there is an error and Excel tells me:

"Office has detected a problem with this file. Editing it may be dangerous. To help keep your computer safe this file has been opened in Protected View....

Now, when the file eventually opens, it looks just fine (except for inconsistent column widths and row heights) and all the data is there.

Now, I am doing this using CF10 on my PC (Windows 8.1 + CF10 developers edition). The plan is to move it onto the staging server that runs CF9 shortly.

Now, the reason I am using cfspreadsheet is that we were unable to produce a professional looking result using PDF format via cfdocument.

So, looks like the same thing is going to stop me using cfspreadsheet.

I don't have time for this.

Seems to me that both these tools are plastic toys that kids might get trapped into using. Is there in fact a ColdFusion tool for producing either PDF or XLS files that will produce professional looking results without lots of time wasting idiosyncracies ?

Cheers,

Report · Sep 09, 2014

Brynz,

Those two issues are are fixed in CF11.

The fix for the second one (spreadsheetsetcolumnwidth issue) should be made available in the next CF10 update.

Report · Sep 22, 2014

Has the "textwrap=true" style rule been fixed? (It's "false" by default.) It was introduced in CF9, but doesn't work properly even if spreadsheetsetcolumnwidth is used.

ColdFusion, jQuery, iPhone • ColdFusion CFSpreadsheet textwrap=true is broken in CF9 & 10 (Fixed in ...

Report · Jul 30, 2015

The only way for now is to use the coldfusion "wrap" method. Apply the wrap method to the cell value and you will see the width adjusted according to the wrap length. I know this is not a perfect but until there is no fix, this is a temp solution.

Adobe Community

Problem using <cfspreadsheet action=write> and <cfspreadsheet action=update>