Most likely your spreadsheet cells are using the built in regional format *m/d/yy, meaning the displayed value (or in this case "read") may vary depending on the environment or client used.
Date and time formats that begin with an asterisk (*) respond to
changes in regional date and time settings that are specified in
Control Panel. Formats without an asterisk are not affected by Control
Panel settings.
That seems to be what is happening with cfspreadsheet. Not sure why Excel displays a four digit year, rather than two digit, for format *m/d/yy. However, CF/POI are returning the correct result according to the Excel specifications. Notice if you switch the cell format to the non-regional, four digit year ie m/d/yyyy the output is what you expected:
![Comparison of regional and non-regional formats]()
![Non-regional Date Format]()
Update: As to why your CF code displays different years than you expected, it is due to how ambiguous date strings are handled by CF. It is important to note, the query returned by CFSpreadsheet contains strings, not date objects. When you pass those strings into DateFormat, CF must first interpret the strings and convert them into date objects before it can apply the date mask. According to CF's rules, two digit years are interpreted as follows:
A string containing a date/time value formatted according to U.S.
locale conventions. Can represent a date/time in the range 100 AD–9999
AD. Years 0-29 are interpreted as 2000-2029; years 30-99 are
interpreted as 1930-1999.
Honestly, CFSpreadsheet is designed to provide a simple way to read and write spreadsheets without a lot of bells and whistles. AFAIK, it does not support changing the way the 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 (ie read the spreadsheet with CF, and apply a new cell format). That is probably the simplest option.
If you want more flexibility in terms of code, you can also use spreadsheet functions instead of cfspreadsheet. Though in this specific case, I think they too lack the necessary features. So you might look into using the underlying POI library and a bit of java code. This thread demonstrates how to obtain all kinds of details about the spreadsheet cells and values. It could easily be modified to build your own query or an array of structures containing the values, formats, etcetera:
![Array of structures containing cell data]()
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>
