I have a request from a client to generate an Excel spreadsheet from a query. I have the query kicking out the fields and I can generate the Excel file without a hitch. The problem comes when the client takes that Excel file and then tries to manipulate it.
The majority of the trouble comes from fields that should be marked as currency or dates. I am, with some struggle, able to generate a "real" date field. Before this Excel was not sorting the dates properly. I was able to call an Excel formula by using the code below. DateValue forces Excel to acknowledge this as a real date field. However, this fails when this file is manipulated through Excel.
<cfset SpreadsheetSetCellFormula(s
,"DATEVALUE(#Chr(34)##Replacement_ETD##Chr(34)#)"
, therow
, 9)>
The next problem is the currency field. I can't get Excel to acknowledge the values as a currency. It always comes up custom. When this is set, the SUM function won't work in Excel. You can add the fields individually like A1+B1+C1 = TOTAL. However, this won't be helpful when there are 200 rows.
I was able to get a suggestion from another CF programmer who had a similar situation. He generated the Excel file first with the proper headings and set the columns to their proper fields such as date and currency, etc.
The next step would be to fill in the fields row by row and they should be properly formatted.
Code:
<cfset filename = expandPath("./reports/arrivals.xlsx")>
<cfspreadsheet action="read" src = "#filename#" name = "s" >
<cfset therow = 0>
<cfoutput query="myExcel" startrow="1">
<cfset therow = myExcel.currentrow + 1>
<cfset SpreadsheetSetCellValue(s, Incumbent, therow, 1)>
<cfset SpreadsheetSetCellValue(s, Section, therow, 2)>
<cfset SpreadsheetSetCellValue(s, Position_Number, therow, 3)>
<cfset SpreadsheetSetCellValue(s, Position_Title, therow, 4)>
<cfset SpreadsheetSetCellValue(s, Incumbent_Emplyment_Type, therow, 5)>
<cfset SpreadsheetSetCellValue(s, Incumbent_ETD, therow, 6)>
<cfset SpreadsheetSetCellValue(s, Tour_Comments, therow, 7)>
<cfset SpreadsheetSetCellValue(s, Replacement, therow, 8)>
<cfset SpreadsheetSetCellValue(s, Replacement_ETA, therow, 9)>
</cfoutput>
<cfheader name="content-disposition" value="attachment; filename=Departures_(#DateFormat(now(),'mmddyy')#).xls">
<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(s)#" reset="true">
The data in the cells has already been properly formatted. When this file is generated and streamed to the user the columns are not formatted as expected.
Does anyone else know if this method will work or have a better suggestion on getting CF to generate a proper date and currency field for Excel to acknowledge?
Adobe ColdFusion v10 running on RHEL 5.
Per request here is some code using queryNew that will generate code dates and currency.
Step one: I created an Excel file with the first row frozen and it has the column header. Column one has been designated as the date the format is long date - mm/dd/yyy; Column two is Dollar which as been set to currency.
I read that file then fill in the rows and stream the file to the user for download.
<cfset filename = expandPath("./reports/Test.xlsx")>
<cfspreadsheet action="read" src = "#filename#" name = "s" >
<cfset myQuery = QueryNew("MyDate, Dollar", "Date, Decimal")>
<cfset newRow = QueryAddRow(MyQuery, 5)>
<cfset temp = QuerySetCell(myQuery, "MyDate", "03-11-2000", 1)>
<cfset temp = QuerySetCell(myQuery, "Dollar", "403.45", 1)>
<cfset temp = QuerySetCell(myQuery, "MyDate", "01-01-2009", 2)>
<cfset temp = QuerySetCell(myQuery, "Dollar", "603.22", 2)>
<cfset temp = QuerySetCell(myQuery, "MyDate", "09-21-2013", 3)>
<cfset temp = QuerySetCell(myQuery, "Dollar", "103.55", 3)>
<cfset temp = QuerySetCell(myQuery, "MyDate", "01-15-2005", 4)>
<cfset temp = QuerySetCell(myQuery, "Dollar", "3.33", 4)>
<cfset temp = QuerySetCell(myQuery, "MyDate", "07-22-2003", 5)>
<cfset temp = QuerySetCell(myQuery, "Dollar", "13.75", 5)>
<cfset therow = 0>
<cfoutput query="myQuery" startrow="1">
<cfset therow = myQuery.currentrow + 1>
<cfset SpreadsheetSetCellValue(s, DateFormat(MyDate, 'mm/dd/yyyy'), therow, 1)>
<cfset SpreadsheetSetCellValue(s, Dollar, therow, 2)>
#myQuery.currentrow# <br>
#myQuery.MyDate# <br>
#myQuery.Dollar# <br>
</cfoutput>
<cfheader name="content-disposition" value="attachment;
filename=Departures_(#DateFormat(now(),'mmddyy')#).xls">
<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(s)#" reset="true">
You can open the file in MS Excel or in Google Sheets. Test one, with the first row frozen, we should be able to sort on the date field. My results are: The dates are not being sorted properly. On column 2 with the currency, if we try to do a SUM that does work! This had not worked before but it does now.
Also, when I try to open the file I am given the warning that this file is corrupt and Excel will try to open it. I get no such warning on Google Sheets.
CF can be a bit quirky when working with date cells. Excel is pretty good about guessing the correct cell type when a value is entered manually. However, it is a little trickier with CF. Since CF is relatively typeless, it does not always match up values and cell types correctly. Using functions that utilize a query object, instead of SpreadsheetSetCellValue(), usually produces better results. Most likely because query objects contains both values and data types. Though as of CF11, SpreadsheetSetCellValue supports a new data parameter, which allows you to specify both the value and cell data type. Since you are using CF10, try using SpreadsheetAddRows to populate the values instead.
Regarding, the warning that the file is corrupt, it is caused by the fact that the actual file content and the file extension in the download code do not match. The code is reading in an .xlsx file, but the download claims it is an .xls (application/msexcel) file. To get rid of the error, make sure the two match.
Here is a working example tested with CF11
<!---
Test.xlsx contains two columns, with headers on row 1
- Column A format: *m/d/yyyy
- Column B format: number with 2 decimal places
--->
<cfspreadsheet action="read" src="c:/temp/Test.xlsx" name="sheet" >
<cfset myQuery = QueryNew("")>
<cfset QueryAddColumn(MyQuery, "Dollar", "Decimal", [ 403.45, 703.22, 103.55, 3.33, 13.75] )>
<cfset QueryAddColumn(MyQuery, "MyDate", "date", [ parseDateTime("2000-03-11", "yyyy-mm-dd")
, parseDateTime("2009-01-01", "yyyy-mm-dd")
, parseDateTime("2013-09-21", "yyyy-mm-dd")
, parseDateTime("2005-01-15", "yyyy-mm-dd")
, parseDateTime("2003-07-22", "yyyy-mm-dd")] ) >
<cfset spreadsheetAddRows(sheet, myQuery)>
<cfset spreadsheetFormatColumn(sheet, {dataFormat="m/d/yy"}, 1)>
<cfset spreadsheetFormatColumn(sheet, {dataFormat="##,####0.00"}, 2)>
<cfheader name="content-disposition" value="attachment; filename=Departures_(#DateFormat(now(),'mmddyy')#).xlsx">
<cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" variable="#spreadsheetReadBinary(sheet)#" reset="true">