excel - 无法使用 ColdFusion 创建正确的 Excel 电子表格

Question

客户请求我根据查询生成 Excel 电子表格。我有查询踢出字段，并且可以顺利生成 Excel 文件。当客户端获取该 Excel 文件然后尝试对其进行操作时，就会出现问题。

大部分问题来自应标记为货币或日期的字段。经过一番努力，我能够生成一个“真实的”日期字段。在此之前，Excel 无法正确对日期进行排序。我可以使用下面的代码调用 Excel 公式。 DateValue 强制 Excel 承认这是一个真实的日期字段。但是，当通过 Excel 操作该文件时，此操作会失败。

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

下一个问题是货币字段。我无法让 Excel 承认这些值是货币。它总是出现定制。设置此值后，SUM 函数将无法在 Excel 中运行。您可以单独添加字段，例如 A1+B1+C1 = TOTAL。但是，当有 200 行时，这将没有帮助。

我从另一位有类似情况的 CF 程序员那里得到了建议。他首先生成具有正确标题的 Excel 文件，并将列设置为正确的字段，例如日期和货币等。

下一步是逐行填写字段，并且格式应正确。

代码:

<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">

单元格中的数据已正确格式化。当生成此文件并将其传输给用户时，列的格式不符合预期。

还有其他人知道此方法是否有效，或者有更好的建议让 CF 生成正确的日期和货币字段以供 Excel 确认吗？

在 RHEL 5 上运行的 Adob​​e ColdFusion v10。

这里的每个请求都是一些使用 queryNew 的代码，它将生成代码日期和货币。

第一步:我创建了一个 Excel 文件，其中第一行被卡住，并且具有列标题。第一列被指定为日期，格式为长日期 - mm/dd/yyy；第二列是已设置为货币的美元。

我读取该文件，然后填写行并将文件流式传输给用户以供下载。

<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">

您可以在 MS Excel 或 Google 表格中打开该文件。测试一个，第一行卡住，我们应该能够对日期字段进行排序。我的结果是:日期没有正确排序。在货币的第 2 列中，如果我们尝试进行有效的 SUM!这以前不起作用，但现在可以了。

此外，当我尝试打开该文件时，我收到警告，指出该文件已损坏，Excel 将尝试打开它。我在 Google 表格上没有收到此类警告。

Answer 1

CF 在处理日期单元格时可能有点奇怪。当手动输入值时，Excel 非常擅长猜测正确的单元格类型。然而，对于 CF 来说有点棘手。由于 CF 相对无类型，因此它并不总是正确匹配值和单元格类型。使用利用查询对象的函数而不是 SpreadsheetSetCellValue() 通常会产生更好的结果。最有可能的原因是查询对象同时包含值和数据类型。尽管从 CF11 开始，SpreadsheetSetCellValue支持新的数据参数，该参数允许您指定值和单元格数据类型。由于您使用的是 CF10，请尝试使用 SpreadsheetAddRows来填充值。

关于文件已损坏的警告，这是由于实际文件内容与下载代码中的文件扩展名不匹配造成的。代码正在读取 .xlsx 文件，但下载声称它是 .xls (application/msexcel) 文件。要消除错误，请确保两者匹配。

这是一个使用 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">