1

I'm using Coldfusion 10 and I have a test code to create a spreadsheet and format a colum with value "8:30" to format "h:mm". Code as below:

<cfset sObj = spreadsheetNew("true")>

<CFSET SpreadsheetFormatCell(sObj,{dataFormat="h:mm", alignment="right"}, 1, 2)>

<CFSET timehmm =  "8:30">
<cfset spreadsheetSetCellValue(sObj,"#timehmm#", 1, 2)>

<cfset SpreadSheetSetColumnWidth(sObj, 2, 11)>

<cfspreadsheet action="write" name="sObj" filename="..\TEST.xlsx" overwrite="true">

<cflocation url="EXCEL/TEST.xlsx" addtoken="no">

The problem is, in the excel file, I click on the column with value 8:30, the value show on the insert function input box is 8:30 which it should be '8:30:00 AM' as I already formatted this cell.

If I type into the same cell with the same value '8:30' and I click the cell again. I can see the value in the insert function box is correct which is '8:30:00 AM'.

Please help to point out what's wrong with my code as I need to set this format to the cell so it can be sum up later. Currently the cell seems like it's been identified as text format and cannot be sum.

Thanks.

Adrienne
  • 13
  • 2

2 Answers2

1

I do not think the code is doing anything wrong per se. I just do not think it possible with basic CF10 functions alone. CF seems to implicitly handle date/time strings correctly, but not "time" strings. It may be supported in CF11, using the new datatype parameter.

A workaround is to use the POI DateUtil class to convert a time string into a numeric value which Excel can interpret. Note: Time strings must use a 24-hour clock

<cfset sObj = spreadsheetNew("true")>
<cfset util = createObject("java", "org.apache.poi.ss.usermodel.DateUtil")>

<cfset SpreadsheetFormatCell(sObj,{dataFormat="h:mm"}, 1, 2)>
<cfset timeValue = util.convertTime("8:30")>
<cfset spreadsheetSetCellValue(sObj, timeValue, 1, 2)>
Leigh
  • 28,765
  • 10
  • 55
  • 103
0

According to the docs, h:mm AM/PM is a separate format from h:mm. Did you try h:mm AM/PM?

Raymond Camden
  • 10,661
  • 3
  • 34
  • 68