@Test public void testDateFormat() throws Exception { TimeZone tz = LocaleUtil.getUserTimeZone(); LocaleUtil.setUserTimeZone(TimeZone.getTimeZone("CET")); try { runFormatTests( "DateFormatTests.xlsx", new CellValue() { public Object getValue(Cell cell) { return cell.getDateCellValue(); } }); } finally { LocaleUtil.setUserTimeZone(tz); } }
/** * Creates a format tracking wrapper around the given listener, using the {@link * Locale#getDefault() default locale} for the formats. */ public FormatTrackingHSSFListener(HSSFListener childListener) { this(childListener, LocaleUtil.getUserLocale()); }
@AfterClass public static void resetLocale() { LocaleUtil.setUserLocale(userLocale); }
@BeforeClass public static void initLocale() { userLocale = LocaleUtil.getUserLocale(); LocaleUtil.setUserLocale(Locale.ROOT); }
@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(); }