• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Trouble with cfspreadsheet and excel files

Explorer ,
May 24, 2016 May 24, 2016

Copy link to clipboard

Copied

Hi,

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 this:

<cfset SpreadsheetSetCellFormula(s,"DATEVALUE(#Chr(34)##Replacement_ETD##Chr(34)#)",therow,9)>

Date value forces Excel to acknowledge this as a real date field. However, this fails when this file is manipulated thru excel. The next problem is the currency field. I can't get excel to acknowledge this 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. 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

Thanks

Views

1.4K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

LEGEND , May 24, 2016 May 24, 2016

For some reason, you have to format twice - once before the data is inserted, and once after.

This is especially true if you have any data values that are strictly integers.  These, even if you format the cell ahead of time to be 'Text', will still align to the right, as integers do.  However, if you reapply the formatting after the data is inserted, then it should format properly.

HTH,

^_^

PS.. THANK YOU for taking the time to code for (cf)spreadsheetsetcellvalue!!  Too many people get lazy and jus

...

Votes

Translate

Translate
LEGEND ,
May 24, 2016 May 24, 2016

Copy link to clipboard

Copied

For some reason, you have to format twice - once before the data is inserted, and once after.

This is especially true if you have any data values that are strictly integers.  These, even if you format the cell ahead of time to be 'Text', will still align to the right, as integers do.  However, if you reapply the formatting after the data is inserted, then it should format properly.

HTH,

^_^

PS.. THANK YOU for taking the time to code for (cf)spreadsheetsetcellvalue!!  Too many people get lazy and just chuck a query object at a cfspreadsheet tag.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 25, 2016 May 25, 2016

Copy link to clipboard

Copied

@WolfShade Thanks for the reply. One follow up.

Are you talking about using SpreadsheetFormatCell or SpreadsheetFormatColumn? I have had some success with format column before but I still end up with data that the client still has issues with?

Thanks for the reply.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 25, 2016 May 25, 2016

Copy link to clipboard

Copied

LATEST

FormatCell, FormatColumn, FormatColumns, FormatRow, FormatRows.  If there is anything that Excel (or the CF workings for Excel) don't automatically format correctly, you'll most likely have to use the format twice.  Which could be slightly easier if you put all the formatting into a function and refer to the function once before populating the data and once after.

HTH,

^_^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation