/* (non-Javadoc)
* @see org.springframework.jdbc.core.RowMapper#mapRow(java.sql.ResultSet, int)
*/
@Override
public GCOprice mapRow(ResultSet rs, int rowNum) throws SQLException {
GCOprice gcoprice = new GCOprice();
gcoprice.setTYPE("pius"); // : for index type
gcoprice.setSYMBOL(rs.getString("SYMBOL"));
gcoprice.setCLOSE_PRICE(rs.getDouble("CLOSEPRICE"));
gcoprice.setSOURCE_DATE(DateUtil.getJavaDate(rs.getInt("SOURCE_DATE") - 693960));
return gcoprice;
}
/**
* Get the value of the cell as a date.
*
* <p>For strings we throw an exception. For blank cells we return a null.
*
* @return the value of the cell as a date
* @throws IllegalStateException if the cell type returned by {@link #getCellType()} is
* CELL_TYPE_STRING
* @exception NumberFormatException if the cell value isn't a parsable <code>double</code>.
* @see org.zkoss.poi.ss.usermodel.DataFormatter for formatting this date into a string similar to
* how excel does.
*/
public Date getDateCellValue() {
int cellType = getCellType();
if (cellType == CELL_TYPE_BLANK) {
return null;
}
double value = getNumericCellValue();
// TODO: activate this when compiling against 3.7.
// boolean date1904 = getSheet().getXSSFWorkbook().isDate1904();
boolean date1904 = false;
return DateUtil.getJavaDate(value, date1904);
}
@Test
public void dateCells() throws Exception {
Workbook workbook = _testDataProvider.createWorkbook();
fixFonts(workbook);
Sheet sheet = workbook.createSheet();
DataFormat df = workbook.getCreationHelper().createDataFormat();
CellStyle style1 = workbook.createCellStyle();
style1.setDataFormat(df.getFormat("m"));
CellStyle style3 = workbook.createCellStyle();
style3.setDataFormat(df.getFormat("mmm"));
CellStyle style5 = workbook.createCellStyle(); // rotated text
style5.setDataFormat(df.getFormat("mmm/dd/yyyy"));
Calendar calendar = LocaleUtil.getLocaleCalendar(2010, 0, 1); // Jan 1 2010
Row row = sheet.createRow(0);
row.createCell(0).setCellValue(DateUtil.getJavaDate(0)); // default date
Cell cell1 = row.createCell(1);
cell1.setCellValue(calendar);
cell1.setCellStyle(style1);
row.createCell(2).setCellValue("1"); // column 1 should be sized as '1'
Cell cell3 = row.createCell(3);
cell3.setCellValue(calendar);
cell3.setCellStyle(style3);
row.createCell(4).setCellValue("Jan");
Cell cell5 = row.createCell(5);
cell5.setCellValue(calendar);
cell5.setCellStyle(style5);
row.createCell(6).setCellValue("Jan/01/2010");
Cell cell7 = row.createCell(7);
cell7.setCellFormula("DATE(2010,1,1)");
cell7.setCellStyle(style3); // should be sized as 'Jan'
// autosize not-evaluated cells, formula cells are sized as if the result is 0
for (int i = 0; i < 8; i++) sheet.autoSizeColumn(i);
assertEquals(sheet.getColumnWidth(2), sheet.getColumnWidth(1)); // date formatted as 'm'
assertTrue(sheet.getColumnWidth(3) > sheet.getColumnWidth(1)); // 'mmm' is wider than 'm'
assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(3)); // date formatted as 'mmm'
assertTrue(
sheet.getColumnWidth(5) > sheet.getColumnWidth(3)); // 'mmm/dd/yyyy' is wider than 'mmm'
assertEquals(
sheet.getColumnWidth(6), sheet.getColumnWidth(5)); // date formatted as 'mmm/dd/yyyy'
// YK: width of not-evaluated formulas that return data is not determined
// POI seems to conevert '0' to Excel date which is the beginng of the Excel's date system
// evaluate formulas and re-autosize
evaluateWorkbook(workbook);
for (int i = 0; i < 8; i++) sheet.autoSizeColumn(i);
assertEquals(sheet.getColumnWidth(2), sheet.getColumnWidth(1)); // date formatted as 'm'
assertTrue(sheet.getColumnWidth(3) > sheet.getColumnWidth(1)); // 'mmm' is wider than 'm'
assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(3)); // date formatted as 'mmm'
assertTrue(
sheet.getColumnWidth(5) > sheet.getColumnWidth(3)); // 'mmm/dd/yyyy' is wider than 'mmm'
assertEquals(
sheet.getColumnWidth(6), sheet.getColumnWidth(5)); // date formatted as 'mmm/dd/yyyy'
assertEquals(
sheet.getColumnWidth(4), sheet.getColumnWidth(7)); // date formula formatted as 'mmm'
workbook.close();
}