1. Start with New Excel file
2. Rename sheet1 to “interview”
3. Save as “xlsx” named “testMaster”
4. Save as “xls” name “oldExcel”
5. Run CF below, then open each.  “xls” will autocalc cells properly.  “xlxs” WILL NOT- Cells display as “0” although formula is correctly embedded and does calc with keystroke “cntrl-alt-f9”

<cfset DummyID = randrange(12345,99999)>

<cfset excelFileToUse = “dldExcel.xls”>
<cfset excelFileToUse = “d:testMaster.xlsx”>

<cfset fileExt = listLast(excelFileToUse,”.”)>

<cfset CompanyLink = “HYPERLINK(“”http://localhost/customers/customer.cfm?customerID=156″”,””My Company””)”>
<cfset contactLink = “HYPERLINK(“”http://localhost//customers/customer.cfm?customerID=156″”,””Johnny Walker””)”>
<cfset quoteLink = “HYPERLINK(“”http://localhost/quotes/oldquote.cfm?quoteID=15263″”,””15626″”)”>

<!— Read QUOTE Sheet —>
<cfset SpreadsheetObj = SpreadSheetRead(excelFileToUse)>

<cfset SpreadsheetSetActiveSheet(SpreadsheetObj, “interview”)>

<!— Hard code variable rather than using CF variable. NO difference —>
<cfset SpreadsheetSetCellFormula(SpreadsheetObj, ‘HYPERLINK(“http://localhost/customers/customer.cfm?customerID=156″,”My Company”)’, 1,2)>

<!— Use CF variable. NO difference —>
<cfset SpreadsheetSetCellFormula(SpreadsheetObj, contactLink, 2,2)>
<cfset SpreadsheetSetCellFormula(SpreadsheetObj, quoteLink, 3,2)>

<!— Try some basic math to see if formulas carry over, xlsx no good —>
<cfset SpreadsheetSetCellFormula(SpreadsheetObj, ‘134+442’, 1,3)>
<cfset SpreadsheetSetCellFormula(SpreadsheetObj, “154*342”, 2,3)>
<cfset SpreadsheetSetCellFormula(SpreadsheetObj, “14/62”, 3,3)>

<cfset SpreadsheetSetColumnWidth(SpreadsheetObj, 2, 24)>
<cfset SpreadsheetSetColumnWidth(SpreadsheetObj, 3, 24)>

<!— Write excel file based on what version of Excel was read in.
.xls – autocalculates correctly when opening file as advertised.
.xlsx- NO autocalculation. Cells DISPLAY as “0” BUT formula IS embedded into cell properly. Cntrl-Alt-F9 (calc sheet) WILL calculate formulas correctly.
—>
<cfset spreadsheetwrite(SpreadsheetObj, “d:temp#DummyID#.#fileExt#”)>