Example usage for org.apache.poi.util LocaleUtil setUserTimeZone

Introduction

In this page you can find the example usage for org.apache.poi.util LocaleUtil setUserTimeZone.

Prototype

public static void setUserTimeZone(TimeZone timezone) 

Source Link

Document

As time zone information is not stored in any format, it can be set before any date calculations take place.

Usage

From source file:com.haulmont.cuba.gui.export.ExcelExporter.java

License:Apache License

protected void formatValueCell(HSSFCell cell, @Nullable Object cellValue,
        @Nullable MetaPropertyPath metaPropertyPath, int sizersIndex, int notificationRequired, int level,
        @Nullable Integer groupChildCount) {
    if (cellValue == null) {
        return;//from  w  w w.j av  a2 s  .c  om
    }

    String childCountValue = "";
    if (groupChildCount != null) {
        childCountValue = " (" + groupChildCount + ")";
    }

    if (cellValue instanceof IdProxy) {
        cellValue = ((IdProxy) cellValue).get();
    }

    if (cellValue instanceof Number) {
        Number n = (Number) cellValue;
        final Datatype datatype = Datatypes.getNN(n.getClass());
        String str;
        if (sizersIndex == 0) {
            str = createSpaceString(level) + datatype.format(n);
            cell.setCellValue(str);
        } else {
            try {
                str = datatype.format(n);
                Number result = (Number) datatype.parse(str);
                if (result != null) {
                    if (n instanceof Integer || n instanceof Long || n instanceof Byte || n instanceof Short) {
                        cell.setCellValue(result.longValue());
                        cell.setCellStyle(integerFormatCellStyle);
                    } else {
                        cell.setCellValue(result.doubleValue());
                        cell.setCellStyle(doubleFormatCellStyle);
                    }
                }
            } catch (ParseException e) {
                throw new RuntimeException("Unable to parse numeric value", e);
            }
            cell.setCellType(CellType.NUMERIC);
        }
        if (sizers[sizersIndex].isNotificationRequired(notificationRequired)) {
            sizers[sizersIndex].notifyCellValue(str, stdFont);
        }
    } else if (cellValue instanceof Date) {
        Class javaClass = null;
        boolean supportTimezones = false;
        TimeZone timeZone = userSessionSource.getUserSession().getTimeZone();
        if (metaPropertyPath != null) {
            MetaProperty metaProperty = metaPropertyPath.getMetaProperty();
            if (metaProperty.getRange().isDatatype()) {
                javaClass = metaProperty.getRange().asDatatype().getJavaClass();
            }
            Boolean ignoreUserTimeZone = metadataTools.getMetaAnnotationValue(metaProperty,
                    IgnoreUserTimeZone.class);
            supportTimezones = timeZone != null && Objects.equals(Date.class, javaClass)
                    && !Boolean.TRUE.equals(ignoreUserTimeZone);
        }
        Date date = (Date) cellValue;
        if (supportTimezones) {
            TimeZone currentTimeZone = LocaleUtil.getUserTimeZone();
            try {
                LocaleUtil.setUserTimeZone(timeZone);
                cell.setCellValue(date);
            } finally {
                if (Objects.equals(currentTimeZone, TimeZone.getDefault())) {
                    LocaleUtil.resetUserTimeZone();
                } else {
                    LocaleUtil.setUserTimeZone(currentTimeZone);
                }
            }
        } else {
            cell.setCellValue(date);
        }

        if (Objects.equals(java.sql.Time.class, javaClass)) {
            cell.setCellStyle(timeFormatCellStyle);
        } else if (Objects.equals(java.sql.Date.class, javaClass)) {
            cell.setCellStyle(dateFormatCellStyle);
        } else {
            cell.setCellStyle(dateTimeFormatCellStyle);
        }
        if (sizers[sizersIndex].isNotificationRequired(notificationRequired)) {
            String str = Datatypes.getNN(Date.class).format(date);
            sizers[sizersIndex].notifyCellValue(str, stdFont);
        }
    } else if (cellValue instanceof Boolean) {
        String str = "";
        if (sizersIndex == 0) {
            str += createSpaceString(level);
        }
        str += ((Boolean) cellValue) ? trueStr : falseStr;
        cell.setCellValue(new HSSFRichTextString(str));
        if (sizers[sizersIndex].isNotificationRequired(notificationRequired)) {
            sizers[sizersIndex].notifyCellValue(str, stdFont);
        }
    } else if (cellValue instanceof EnumClass) {
        String nameKey = cellValue.getClass().getSimpleName() + "." + cellValue.toString();
        final String message = sizersIndex == 0
                ? createSpaceString(level) + messages.getMessage(cellValue.getClass(), nameKey)
                : messages.getMessage(cellValue.getClass(), nameKey);

        cell.setCellValue(message + childCountValue);
        if (sizers[sizersIndex].isNotificationRequired(notificationRequired)) {
            sizers[sizersIndex].notifyCellValue(message, stdFont);
        }
    } else if (cellValue instanceof Entity) {
        Entity entityVal = (Entity) cellValue;
        String instanceName = entityVal.getInstanceName();
        String str = sizersIndex == 0 ? createSpaceString(level) + instanceName : instanceName;
        str = str + childCountValue;
        cell.setCellValue(new HSSFRichTextString(str));
        if (sizers[sizersIndex].isNotificationRequired(notificationRequired)) {
            sizers[sizersIndex].notifyCellValue(str, stdFont);
        }
    } else if (cellValue instanceof Collection) {
        String str = "";
        cell.setCellValue(new HSSFRichTextString(str));
        if (sizers[sizersIndex].isNotificationRequired(notificationRequired)) {
            sizers[sizersIndex].notifyCellValue(str, stdFont);
        }
    } else {
        String strValue = cellValue == null ? "" : cellValue.toString();
        String str = sizersIndex == 0 ? createSpaceString(level) + strValue : strValue;
        str = str + childCountValue;
        cell.setCellValue(new HSSFRichTextString(str));
        if (sizers[sizersIndex].isNotificationRequired(notificationRequired)) {
            sizers[sizersIndex].notifyCellValue(str, stdFont);
        }
    }
}

---

From source file:org.teiid.translator.excel.TestExcelExecution.java

License:Apache License

@Test
public void testTime() throws Exception {
    VirtualFileConnection connection = Mockito.mock(VirtualFileConnection.class);
    Mockito.stub(connection.getFiles("names.xls")).toReturn(
            JavaVirtualFile.getFiles("names.xlsx", new File(UnitTestUtil.getTestDataPath(), "names.xlsx")));

    String ddl = commonDDL.replace("14", "6");
    ArrayList results = helpExecute(ddl, connection, "select \"time\" from Sheet1");
    //typed as time
    assertEquals("[[10:12:14]]", results.toString());

    ddl = ddl.replace("\"time\" time", "\"time\" string");
    results = helpExecute(ddl, connection, "select \"time\" from Sheet1", true);
    //typed as string with formatting - Excel format
    assertEquals("[[10:12:14 AM]]", results.toString());

    TimeZone timeZone = TimeZone.getTimeZone("America/New_York");
    TimeZone defaultTz = TimeZone.getDefault();
    TimestampWithTimezone.resetCalendar(timeZone); //$NON-NLS-1$
    LocaleUtil.setUserTimeZone(timeZone);
    try {//from www.j a  v  a 2 s  .c o  m
        results = helpExecute(ddl, connection, "select \"time\" from Sheet1", false);
        //typed as string without formatting - SQL / Teiid format
        //note this seems like an issue with poi - if the sheet is not using 1904 dates, then it will start the calendar at the 0 day, not the first.
        //however this behavior is slightly better than the previous, which would have shown the numeric value instead
        assertEquals("[[1899-12-31 10:12:14.0]]", results.toString());
    } finally {
        TimestampWithTimezone.resetCalendar(null);
        LocaleUtil.setUserTimeZone(defaultTz);
    }
}

---