CfSpreadsheet 2-digit years

Question

In ColdFusion 11, I use cfSpreadsheet to convert the .xls file to a request object. Here is a screenshot of my demo table:

I use this code to see the request object as soon as it is created:

<cfspreadsheet action="read" src="demo_spreadsheet.xls" excludeHeaderRow="true" headerrow="1" query="demo_query"/> <cfdump var="#demo_query#"> 

... and I get the following results:

Notice that all of my 4-digit years from the table now represent 2-digit years? When I go to output data to the request object using this code:

 <ul> <cfoutput query="demo_query"> <li>#name# - #dateformat(start_date, 'medium')#</li> </cfoutput> </ul> 

... I get the following output (well, I'm new to here, so I can't post more than two screenshots, so you have to trust me with this copy / paste):

• Alpha - January 1, 2007
• Bravo - February 2, 1972
• Charlie - March 3, 2017
• Delta - April 4, 1984
• Echo - December 31, 2029
• Foxtrot - January 1, 1930
• Golf - January 1, 1930

In 1907, in 2007, in 1917, in 1917, 1929 - in 2029, and 2030 - in 1930. It seems that the year of any date until January 1, 1930 reads like 20xx, and after December 31, 2029 it reads like 19xx.

Am I missing something? I thought we figured it out with Y2K. Is there somewhere a simple parameter that I have is wrong? I was looking for this question and there is nothing I can do about it.

Any advice would be appreciated.

Answer 1

Most likely, your spreadsheet cells use the built-in regional format *m/d/yy , which means the displayed value (or in this case "read") may vary depending on the environment or client.

Date and time formats starting with an asterisk (*) correspond to changes in the regional date and time settings specified in the Control Panel. Format management without an asterisk is independent of the Panel Settings control.

This is similar to what happens with cfspreadsheet. You do not know why Excel displays a four-digit year, and not two digits, for the *m/d/yy format. However, CF / POIs return the correct result according to Excel specifications. Please note if you change the cell format to a non-regional, four-digit year, i.e. m/d/yyyy , then the result will be what you expected:

Update: As for why your CF code displays different years than you expected, this is due to the way CF handles ambiguous date strings. It is important to note that the query returned by CFSpreadsheet contains strings, not date objects. When you pass these strings to DateFormat , CF must first interpret the strings and convert them to date objects before it can apply the date mask. In accordance with CF rules, double-digit years are interpreted as follows:

A string containing a date / time value formatted in accordance with US regional conventions. May represent a date / time in the range of 100 AD-9999 AD. Years 0-29 are interpreted as 2000-2029; years 30-99 is interpreted as 1930-1999.

Honestly, CFSpreadsheet is designed to provide an easy way to read and write spreadsheets without a lot of calls and whistles. AFAIK, it does not support changing the way that cell values ​​are interpreted. If you want to force a four-digit year, you must change the spreadsheet to use a non-regional date format, either manually or programmatically (i.e. Read the spreadsheet using CF and apply the new cell format). This is probably the easiest option.

If you need extra flexibility in terms of code, you can also use spreadsheet functions instead of cfspreadsheet. Although in this particular case, I think that they also lack the necessary functions. This way you can learn to use the base POI library and some Java code. This thread demonstrates how to get all kinds of details about cells and spreadsheet values. It can be easily changed to create your own query or an array of structures containing values, formats, etc.:

Code:

 <cfscript> // get the sheet you want to read cfSheet = SpreadSheetRead("c:/temp/demo_spreadsheet.xls"); workbook = cfSheet.getWorkBook(); sheetIndex = workbook.getActiveSheetIndex(); sheet = workbook.getSheetAt( sheetIndex ); // utility used to distinguish between dates and numbers dateUtil = createObject("java", "org.apache.poi.ss.usermodel.DateUtil"); // process the rows and columns rows = sheet.rowIterator(); while (rows.hasNext()) { currentRow = rows.next(); data = {}; cells = currentRow.cellIterator(); while (cells.hasNext()) { currentCell = cells.next(); col = {}; col.value = ""; col.type = ""; col.column = currentCell.getColumnIndex()+ 1; col.row = currentCell.getRowIndex()+ 1; col.format = currentCell.getCellStyle().getDataFormatString(); if (currentCell.getCellType() EQ currentCell.CELL_TYPE_STRING) { col.value = currentCell.getRichStringCellValue().getString(); col.type = "string"; } else if (currentCell.getCellType() EQ currentCell.CELL_TYPE_NUMERIC) { if (DateUtil.isCellDateFormatted(currentCell)) { col.value = currentCell.getDateCellValue(); col.type = "date"; } else { col.value = currentCell.getNumericCellValue(); col.type = "number"; } } else if (currentCell.getCellType() EQ currentCell.CELL_TYPE_BOOLEAN) { col.value = currentCell.getBooleanCellValue(); col.type = "boolean"; } // ... handle other types CELL_TYPE_BLANK, CELL_TYPE_ERROR, CELL_TYPE_FORMULA data["COL"& col.column] = col; } // this row is finished. display all values WriteDump(data); } </cfscript>