cfSpreadsheet 2位数年 [英] cfSpreadsheet 2-digit years
问题描述
在ColdFusion 11中，我使用cfSpreadsheet将.xls文件转换为查询对象。以下是我的演示电子表格的屏幕截图：
一旦创建查询对象，我使用此代码：
< cfspreadsheet action =readsrc = demo_spreadsheet.xls
excludeHeaderRow =true
headerrow =1query =demo_query/>
< cfdump var =＃demo_query＃>
...我得到这些结果：
请注意，电子表格中我所有的4位数年份现在都是2位数年份？当我使用以下代码输出查询对象中的数据时：
< ul>
< cfoutput query =demo_query>
< li>＃name＃ - #dateformat（start_date，'medium'）＃< / li>
< / cfoutput>
< / ul>
...我得到以下输出（好的，我是新来的，所以我可以'发布超过两个屏幕截图，所以你必须相信我这个副本/粘贴）：
- Alpha - 2007年1月1日
- Bravo - 1972年2月2日
- 查理 - 2017年3月3日
- 1984年
- Echo - 2029年12月31日
- Foxtrot - 1930年1月1日
- ，1930年
1907年现在是2007年，1917年现在是2017年，1929年现在是2029年，2030年现在是1930年。 1930年1月1日前的任何一年的年份为20xx年，12月31日之后，2029年为19xx年。
我错过了什么？我以为我们把这种事情和Y2K搞定了。有没有一个简单的设置，我有不正确的地方？我已经把谷歌从这个问题出来，我找不到任何东西。
任何建议将是最受欢迎的。
电子表格单元格正在使用内置的
更新：至于您的CF代码为什么显示不同于您预期的年份，这是由于CF处理不明确的日期字符串。重要的是要注意，CFSpreadsheet返回的查询包含字符串，而不是日期对象。当您将这些字符串传递到 DateFormat 中时，CF必须首先解释字符串并将其转换为日期对象，然后才能应用日期掩码。
代码
< CFSCRIPT>
//获取要读取的工作表
cfSheet = SpreadSheetRead（c：/temp/demo_spreadsheet.xls）;
workbook = cfSheet.getWorkBook（）;
sheetIndex = workbook.getActiveSheetIndex（）;
sheet = workbook.getSheetAt（sheetIndex）;
//用于区分日期和数字的实用程序
dateUtil = createObject（java，org.apache.poi.ss.usermodel.DateUtil）;
//处理行和列
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;
}
// ...处理其他类型CELL_TYPE_BLANK，CELL_TYPE_ERROR，CELL_TYPE_FORMULA
data [COL& col.column] = col
}
//此行已完成。显示所有值
WriteDump（data）;
}
< / cfscript>
In ColdFusion 11, I'm using cfSpreadsheet to convert a .xls file into a query object. Here is a screenshot of my demo spreadsheet:
I use this code to see the query 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 these results:
Notice that all of my 4-digit years from the spreadsheet are now 2-digit years? When I go to output the data in the query object using this code:
<ul>
<cfoutput query="demo_query">
<li>#name# - #dateformat(start_date, 'medium')#</li>
</cfoutput>
</ul>
... I get the following output (ok, I'm new here, so I can't post more than two screen shots so you'll have to trust me on this copy/paste):
- Alpha - Jan 1, 2007
- Bravo - Feb 2, 1972
- Charlie - Mar 3, 2017
- Delta - Apr 4, 1984
- Echo - Dec 31, 2029
- Foxtrot - Jan 1, 1930
- Golf - Jan 1, 1930
The year 1907 is now 2007, 1917 is now 2017, 1929 is now 2029, and 2030 is now 1930. It appears that the year of any date before Jan 1, 1930 is read as 20xx, and after Dec 31, 2029 is read as 19xx.
Am I missing something? I thought we figured this kind of thing out with Y2K. Is there a simple setting somewhere that I have incorrect? I've Googled the heck out of this issue and I can't find anything about it.
Any advice would be most welcome.
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:
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:
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>
这篇关于cfSpreadsheet 2位数年的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
