Skip navigation
ssmahe
Currently Being Moderated

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

Jan 21, 2011 1:17 AM

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

 

 
Replies
  • Currently Being Moderated
    Jan 21, 2011 5:15 AM   in reply to ssmahe

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    May 24, 2013 12:37 PM   in reply to ssmahe

    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.    

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points