1

I'm using <cfspreadsheet> to output a database query to an Excel spreadsheet. When these spreadsheets are created manually it is possible to highlight all the cells and 'Format as table'. This means when the table header is clicked, the user can sort the table ascending and descending.

Is it possible to specify this formatting in the ColdFusion code when generating the Excel file?

blarg
  • 3,773
  • 11
  • 42
  • 71
  • I'm not sure that I understand the necessity for this? Once in Excel you can select the cells and sort by any column you wish. If you are wanting the generated spreadsheet to be sorted in a particular way when it is created then modify your query to have it sorted correctly before calling ``. – Miguel-F Apr 26 '13 at 14:21
  • Just to make it nicer looking for the end user. – blarg Apr 26 '13 at 14:26
  • You can format columns `SpreadsheetFormatColumn` and format rows `SpreadsheetFormatRow`. See [SpreadsheetFormatCell for the formatting specifics](http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-6747.html) Not sure there is much flexibility on formatting the spreadsheet entirely like you are asking. – Miguel-F Apr 26 '13 at 14:31

1 Answers1

1

If you are on Coldfusion 9, you can use SpreadSheetNew, then SpreadSheetAddRow, SpreadSheetFormat functions to style an excel spreadsheet from a data set

 <cfset sObj = spreadsheetNew("myreport","yes")>
 <cfset SpreadsheetAddRow(sObj, "Column_1, ... , Column27")>

 <cfset SpreadsheetFormatRow(sObj, {bold=TRUE, alignment="center"}, 1)>

 <cfset spreadsheetAddRows(sObj, qMyQuery)>
    <cfheader name="content-disposition" value="attachment; filename=report_#Dateformat(NOW(),"MMDDYYYY")#.xlsx">

BE WARNED however, this can be extremely taxing to the JVM, I had a query i was creating an xls with, applying only two styles (bold, text-center) to the header row, and any query over 700 rows would shut down the entire server via JVM memory loss...here is my SO question about it, with related code/answer SpreadsheetAddRows failing on moderate size query

It has been documented with adobe as being a bug

Community
  • 1
  • 1
Jay Rizzi
  • 4,196
  • 5
  • 42
  • 71
  • *It has been documented with adobe as being a bug* What is the bug number? – Leigh Apr 26 '13 at 16:40
  • I do not have the exact bug id, i will look for it, here is an extremely similar bug id, i encountered the same issue even though my query did not have a date-field https://bugbase.adobe.com/index.cfm?event=bug&id=3432184 – Jay Rizzi Apr 26 '13 at 18:24