Stupid Excel CSV tricks department. I'm exporting some data as CSV that will be opened in Excel. The insuranceNumber column has data that's sometimes all numbers, "123456789012" for instance. When you open the file in Excel, those all-numbers cells are shown in scientific notation, like "123457E+11", and are right aligned. If you edit the cell, all that's there are the numbers, it's just Excel "detecting" how it "should" be formatted and doing that. How can I prevent that from happening? Double quotes around the data doesn't help. Worst case, I can tell them that's how it is, they have to format it in Excel, which is easy, but if I can fix it I'd like to. Thoughts?

short answer - you can't as your data is a CSV so Excel will do whatever it sees fit. If you export as Excel then you can specify that it's a number and stop it "being helpful" we have the same issue with things like "01234". It's "01234" in the CSV, but Excel will say "Oh - I see you made a mistake there I'll fix that for you and make it 1234, there you go - aren't I helpful"

Try a single quote ' before each value

If you choose to import the CSV into Excel you get a bunch of choices, but if you just open the csv file (which is an associated file extension of Excel) then you have no control.

Ha me too!

Actually, I think Excel is amazingly cool, for a million things, lots of which mere mortals can do without reading the manual or anything. But if it's not doing what you want, it can be ornery about it. @aliaspooryorik When you say "We have two export options, CSV and Excel", how are you generating the Excel format version? cfspreadhseet? DataTables?

We use https://www.forgebox.io/view/spreadsheet-cfml

I haven't worked w cfspreadsheet before, so I have questions. 1. How do you make a reasonable user experience out of the user wanting a download? 2. How can you make the header row anything besides the actual db column names? 3. How can you choose the columns you want and their order, outside of QoQ before calling cfspreadsheet?

1. A download button 🙂 2. Yes, you can do whatever you like (you can also do that with the ACF spreadsheet functions as well to be fair) 3. As above

OK I'm thick, didn't see arguments to cfspreadsheet to do any of that. How?

Don't think you can with just the

cfspreadsheet

tag, you need to use https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-s/spreadsheetsetcellvalue.html

Hmmm, I check out Spreadsheet CFML, ddon't care for the Adobe API. My query renderer cfc makes it so simple to download a csv, with a reasonable user experience and everything, I'm hopine that lib is similar, I don't need or want to faff around much.

I'd imagine the code would be similar to your CSV one. So as you loop and add a row to you CSV, you'd just do

myspreadsheet.addRow(...)

instead

Ooo, downloadFileFromQuery() is very close to ideal for me, since I do have a query. There are a few things my query rendering cfc does that aren't covered: • Choose columns to include and their order • Set column names for header row • Query has lastName and firstName, cells should have "#lastName#, #firstName#". ◦ I can preprocess the query to create a column like that if need be, but my cfc lets you provide a function to render a column's data, and it has access to the whole query, so I just use that. Even without those tweaks, it might be worth doing. However, before any of that, I'm talking to the customer about whether the CSV idiosyncrasies I've been whining about are even an issue for them.

@Dave Merrill You could keep using your existing CSV generating code for the column selection etc and then just pass the result to the spreadsheet-cfml library to tweak the column format and offer the download.

spreadsheet = New spreadsheet.Spreadsheet() //instantiate the library
newline = Chr( 13 ) & Chr( 10 ) //Lucee has newline() but ACF doesn't
csv = 'insuranceNumber#newline#123456789012' // use your generated CSV here
workbook = spreadsheet.workbookFromCsv( csv=csv, firstRowIsHeader=true, xmlFormat=true )
spreadsheet
	.formatColumn( workbook, { dataFormat: "0" }, 1 ) // try and stop Excel using scientific notation
	.download( workbook, "csvToSpreadsheetTest" )

@cfsimplicity Cool! A bit roundabout, but I'm a huge fan of Done, and Using Existing Stuff That Works, so I may take this for a spin. Customer says they can deal w the plain csv, so it's not an emergency. Still, I know they'd like to not have to fiddle w that one numbers that aren't really numbers column by hand, so...