6 Replies Latest reply: Sep 22, 2014 3:44 PM by Jamo RSS

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

    ssmahe

      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

       

        • 1. Re: Problem using <cfspreadsheet action=write> and <cfspreadsheet action=update>
          existdissolve Community Member

          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.

          • 2. Re: Problem using <cfspreadsheet action=write> and <cfspreadsheet action=update>
            ssmahe

            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-7f 87.html

            • 3. Re: Problem using <cfspreadsheet action=write> and <cfspreadsheet action=update>
              Nick_KC

              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.    

              • 4. Re: Problem using <cfspreadsheet action=write> and <cfspreadsheet action=update>
                Brynz

                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,

                • 5. Re: Problem using <cfspreadsheet action=write> and <cfspreadsheet action=update>
                  Piyush Nayak Community Member

                  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.

                  • 6. Re: Problem using <cfspreadsheet action=write> and <cfspreadsheet action=update>
                    Jamo Community Member

                    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 11)