CFSpreadsheet日期格式问题

Question

我在本地运行ColdFusion 10，在最近从Oracle转换为MSSQL语言的数据库中遇到两个问题。

我要做的是使用cfspreadsheet将数据从数据库输出到电子表格中。表中有3列，这些列包含日期/时间戳。

问题#1:代码在转换之前运行良好，但在转换之后，所有日期/时间戳都以科学记数格式显示。我试着用cast convert修改我的CFQUERY，但这样做只是将日期/时间戳字段显示为'YYYY/MM/DD‘，而不是我想要的格式'MM/DD/YYYY HH:MM AM/MM’。当我双击excel中的单元格时，我看到了正确的格式，但默认情况下它显示为'YYYY/MM/DD.‘’。这里有什么建议吗？

问题#2:如果应该使用我指定的日期/时间格式的列或特定单元格为空或null，我会收到一个'‘是一个无效的日期或时间字符串错误。

下面是我在cfquery中用于强制转换/转换的代码：

            ,CAST(CONVERT(varchar(20), GYMSTARTDATE, 22) AS datetime) AS GymStartDateTime
        ,CAST(CONVERT(varchar(20), GYMENDDATE, 22) AS datetime) AS GymEndDateTime

Answer 1

如果你想明白了，请让我知道。这是我在2011年提供的示例代码的链接。它不适用于ColdFusion 9或10。我还列出了所有官方的“内置”(但不支持)日期格式。

http://pastebin.com/aQnembR3

<cfset q = queryNew("Name,Date", "varchar,date")>
<cfloop index="x" from="1" to="10">
    <cfset queryAddRow(q)>
    <cfset querySetCell(q, "Name", "Name #x#")>
    <cfset querySetCell(q, "Date", now())>
</cfloop>

<cfset dfStyle = {dataformat = "m/d/yy h:mm"}>

<cfset o = spreadsheetNew('dateTest', true)>
<cfset spreadsheetAddRows(o, q)>
<cfset spreadsheetFormatColumn(o, dfStyle, 2)>

<cfset bin = spreadsheetReadBinary(o)>

<cfheader name="Content-Disposition" value="attachment; filename=dateTest_#dateFormat(now(), 'ddmmmyyyy')#.xlsx">
<cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" variable="#bin#" reset="true">

Answer 2

如果看不到数据样本和实际代码，就很难说清楚。但是，我尝试使用CF10和SQL Server2005重现您所描述的问题，但无法重现。所有的值都显示为字符串，而不是科学记数法。

听起来你的数据或环境中有一些我们没有意识到的不同之处。请放一个小例子来重现这个问题，我们可以进一步调试它。

测试1:

<!--- raw dates --->
<cfquery name="qry" datasource="#dsn#">
    SELECT  StartDate, EndDate
    FROM    YourTable
</cfquery>

<cfspreadsheet action="write" 
      filename="c:/test.xls" 
      query="qry" 
      overwrite="true" /> 

结果1: (单元格类型:常规)

STARTDATE                   ENDDATE
2013-08-20 14:19:28.907     2013-09-03 14:19:28.907
2013-08-25 14:19:30.293     2013-09-03 02:19:30.293

测试2:

<!--- convert / datetime --->
<cfquery name="qry" datasource="#dsn#">
SELECT CAST(CONVERT(varchar(20), StartDate, 22) AS datetime) AS GymStartDateTime
          , CAST(CONVERT(varchar(20), EndDate, 22) AS datetime) AS GymEndDateTime
FROM   YourTable
</cfquery>

<cfspreadsheet action="write" 
      filename="c:/test2.xls" 
      query="qry"  
      overwrite="true" /> 

结果2: (单元格类型:常规)

GYMSTARTDATETIME            GYMENDDATETIME
2013-08-20 14:19:28.0       2013-09-03 14:19:28.0
2013-08-25 14:19:30.0       2013-09-03 02:19:30.0

测试3:

<!--- convert / string --->
<cfquery name="qry" datasource="#dsn#">
SELECT  CONVERT(varchar(20), StartDate, 22) AS GymStartDateTime
           , CONVERT(varchar(20), EndDate, 22) AS GymEndDateTime
FROM   YourTable
</cfquery>

<cfspreadsheet action="write" 
      filename="c:/test3.xls" 
      query="qry" 
      overwrite="true" /> 

结果3: (单元格类型:常规)

GYMSTARTDATETIME            GYMENDDATETIME
08/20/13  2:19:28 PM        09/03/13  2:19:28 PM
08/25/13  2:19:30 PM        09/03/13  2:19:30 AM