4

My question is basically the same as this one, but I'm not in a position to change the date format in the Excel file:

If I format the cell as date english (NZ), ie Date Type "*14/03/2001", it displays right in the spreadsheet, but when I try to upload it switched the day and month. But If I change the format to a custom "dd/mm/yyyy" format [it works fine].

So the question is this: Can I change the way <cfspreadsheet /> handles date formats? Or even better, get a Date object directly from the Excel import.

EDIT:

I found a solution by using POI:

<cfset fileIS = createObject( "java", "java.io.FileInputStream" ).init( "#request.site.sImportPath#\#variables.file#" ) />
<cfset excelFS = createObject( "java", "org.apache.poi.poifs.filesystem.POIFSFileSystem" ).init( fileIS ) />
<cfset workBook = CreateObject( "java", "org.apache.poi.hssf.usermodel.HSSFWorkbook" ).init( excelFS ) />
<cfset sheet = workBook.getSheet( "mySheetName" ) />

<cfset myDateValue = sheet.getRow( 20 ).getCell( 2 ).getDateCellValue() />

When using getDateCellValue() you get the actual date as a usable ColdFusion date back. It would've been nice if <cfspreadsheet /> did this natively.

Community
  • 1
  • 1
Mingo
  • 113
  • 1
  • 7
  • What did you set with `SetLocale()`? – Tomalak Oct 25 '13 at 11:36
  • I tried both en_US and nl_NL, no change. – Mingo Oct 25 '13 at 11:59
  • If you are receiving a predictable format, you can use ParseDateTime to cast it to a date object. – Dan Bracuk Oct 25 '13 at 12:11
  • I can't really rely on the format the date was entered in, some actually are properly date formatted, some use the asterisk method. I already had a function in place that tried to figure out in what format the date was entered, but in the end there's always the 5/4/2013 date that's not going to be predictable. – Mingo Oct 25 '13 at 12:55
  • If I were in the situation where something was ambiguous, I would ask the person for whom I was doing the job to decide what to do about it. – Dan Bracuk Oct 25 '13 at 13:25
  • I am not positive, but I do not think you can change the way regional formats are handled without changing the locale at the jvm or o/s level. *RE: Or even better, get a Date object directly from the Excel import.* You can, but not with CFSpreadsheet or any of the built-in functions. You have to [do it yourself](http://stackoverflow.com/questions/9028511/reading-column-format-with-cfspreadsheet/9030012#9030012). – Leigh Oct 25 '13 at 13:29
  • Thanks Leigh, I ended up doing exactly that (see my edit.) – Mingo Oct 25 '13 at 13:48
  • 1
    Yes, it would be nice if CF exposed more of this type of functionality. That above looks good. Just keep in mind the rows/cells/values might be `null`. Could you post that as an "answer", so it is more visible? (Answering your own question is allowed :). – Leigh Oct 25 '13 at 14:42
  • BTW, Welcome to Stack Overflow. Good first question :) – Leigh Oct 25 '13 at 14:57
  • 1
    After YEARS of lurking finally decided to start asking and answering questions here. Thanks for your help, I've added the answer. – Mingo Oct 25 '13 at 18:04

1 Answers1

2

I found a solution by using POI:

<cfset fileIS = createObject( "java", "java.io.FileInputStream" ).init( "#request.site.sImportPath#\#variables.file#" ) />
<cfset excelFS = createObject( "java", "org.apache.poi.poifs.filesystem.POIFSFileSystem" ).init( fileIS ) />
<cfset workBook = CreateObject( "java", "org.apache.poi.hssf.usermodel.HSSFWorkbook" ).init( excelFS ) />
<cfset sheet = workBook.getSheet( "mySheetName" ) />

<cfset myDateValue = sheet.getRow( 20 ).getCell( 2 ).getDateCellValue() />

When using getDateCellValue() you get the actual date as a usable ColdFusion date back. It would've been nice if did this natively.

Mingo
  • 113
  • 1
  • 7