Copy link to clipboard
Copied
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.
<cfspreadsheet action="write" filename="#ExpandPath('./final_report.xls')#" name="spreadsheetObj1" overwrite=true>
<cfspreadsheet action="update" filename="#ExpandPath('./final_report.xls')#" name="spreadsheetObj2">
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.
--->
<cfxml variable="xmlData">
<workbook>
<!--- Define the global CSS classes. --->
<classes>
<!--- Global TD-Cell style. --->
<class
name="cell"
value="font: 12pt arial ;"
/>
<!--- Additional CSS styles. --->
<class
name="header"
value="border-bottom: 2px solid black ; font-weight: bold ;"
/>
<class
name="row"
value="border-bottom: 1px dotted gray ;"
/>
</classes>
<!--- Define the sheets. --->
<sheets>
<sheet>
<name>Sheet 1</name>
<!--- Define the rows. --->
<rows>
<row class="header">
<cell>
Name
</cell>
<cell>
Hair Color
</cell>
<cell>
Hotness
</cell>
</row>
<row class="row">
<cell>
Christina Cox
</cell>
<cell>
Dirty Blonde
</cell>
<cell type="numeric" format="0.0">
9.0
</cell>
</row>
<row class="row">
<cell>
Maura Tierney
</cell>
<cell>
Brunette
</cell>
<cell type="numeric" format="0.0">
8.0
</cell>
</row>
<row class="row">
<cell>
Maria Bello
</cell>
<cell>
Brunette
</cell>
<cell type="numeric" format="0.0">
9.5
</cell>
</row>
</rows>
</sheet>
<sheet>
<name>Sheet 2</name>
<!--- Define the rows. --->
<rows>
<row class="header">
<cell>
Name
</cell>
<cell>
Hair Color
</cell>
<cell>
Hotness
</cell>
</row>
<row class="row">
<cell>
Christina Cox
</cell>
<cell>
Dirty Blonde
</cell>
<cell type="numeric" format="0.0">
9.0
</cell>
</row>
<row class="row">
<cell>
Maura Tierney
</cell>
<cell>
Brunette
</cell>
<cell type="numeric" format="0.0">
8.0
</cell>
</row>
<row class="row">
<cell>
Maria Bello
</cell>
<cell>
Brunette
</cell>
<cell type="numeric" format="0.0">
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 ]"
) />
<!--- Get the sheet nodes. --->
<cfset arrSheets = XmlSearch(
xmlData,
"/workbook/sheets/*"
) />
<!--- Loop over the sheet nodes. --->
<cfloop
index="intSheet"
from="1"
to="#ArrayLen( arrSheets )#"
step="1">
<!--- Get a short-hand pointer to the current sheet. --->
<cfset xmlSheet = arrSheets[ intSheet ] />
<!--- Grab the name text nodes from this sheet. --->
<cfset arrNames = XmlSearch( xmlSheet, "./name/text()" ) />
<!--- Grab the rows noes from this sheet. --->
<cfset arrRows = XmlSearch( xmlSheet, "./rows/row/" ) />
<!--- Check to see if we found a sheet name. --->
<cfif ArrayLen( arrNames )>
<!--- We found a sheet name, so set the value. --->
<cfset strSheetName = arrNames[ 1 ].XmlValue />
<cfoutput>Name of the sheet : #strSheetName# </cfoutput>
<cfelse>
<!--- No sheet name was found, so use default. --->
<cfset strSheetName = "Sheet1" />
</cfif>
<!---
Create an Excel sheet in the current workbook
with the given name.
--->
<cfset objSheet = objWorkbook.CreateSheet(
JavaCast( "string", strSheetName )
) />
<!--- Loop over the row nodes. --->
<cfloop
index="intRow"
from="1"
to="#ArrayLen( arrRows )#"
step="1">
<!--- Get a short-hand pointer to the current row. --->
<cfset xmlRow = arrRows[ intRow ] />
<!--- Grab all the cells for this row. --->
<cfset arrCells = XmlSearch( xmlRow, "./cell/" ) />
<!--- Create the row in the current sheet. --->
<cfset objRow = objSheet.CreateRow(
JavaCast( "int", (intRow - 1) )
) />
<!--- Loop over the cell nodes. --->
<cfloop
index="intCell"
from="1"
to="#ArrayLen( arrCells )#"
step="1">
<!--- Get a short hand pointer to the cell. --->
<cfset xmlCell = arrCells[ intCell ] />
<cfoutput>#xmlCell.XmlText#</cfoutput>
<!--- Create a cell in the current row. --->
<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>
<cfset objSheet = JavaCast( "null", 0 ) />
</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" )
)
) />
<!--- Write the workout data to the file stream. --->
<cfset objWorkbook.Write(
objFileOutputStream
) />
<!---
Close the file output stream. This will release any
locks on the file and finalize the process.
--->
<cfset objFileOutputStream.Close() />
<!--- Formating the Excel file --->
<br/> Reading the excel file using CFSPREADSHEET <br/>
<cfspreadsheet action="read" src="#ExpandPath('./xml_to_excel.xls')#" sheetname="Sheet 1" name="spreadsheetObj1"/>
<cfspreadsheet action="read" src="#ExpandPath('./xml_to_excel.xls')#" sheetname="Sheet 1" name="spreadsheetObj2"/>
<cfoutput><br/><table>#spreadsheetObj1#</table></cfoutput>
<cfscript>
format1 = StructNew();
format1.bold="true";
SpreadsheetFormatRow(spreadsheetObj1 , format1, 1);
SpreadsheetFormatRow(spreadsheetObj2 , format1, 1);
</cfscript>
<cfspreadsheet action="write" filename="#ExpandPath('./final_report.xls')#" name="spreadsheetObj1" overwrite=true>
<cfspreadsheet action="update" filename="#ExpandPath('./final_report.xls')#" name="spreadsheetObj2">
<br/>Download excel file : <a href="./final_report.xls">Excel File</a>
</body>
</html>
Thanks in advance
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec17cba-7f87.html
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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:
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,
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.