/*
* Don't hardcode US Locale for datetimes in Excel. Experiment a bit.
*/
public static void issue26() throws InvalidFormatException, IOException {
System.out.println("Testing issue26 =================================");
Workbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("Sheet1");
Row row = sheet.createRow(0);
// first cell
Cell cell0 = row.createCell(0);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss"));
cell0.setCellValue(new Date(1385903303326L)); // 12/1/2013 08:08 AM
cell0.setCellStyle(cellStyle);
// second cell using another format with French locale
CellStyle cs2 = wb.createCellStyle();
String excelFormatPrefix = DateFormatConverter.getPrefixForLocale(Locale.FRENCH);
System.out.println("The LOCALE prefix is: " + excelFormatPrefix);
String excelFormatPattern = excelFormatPrefix + "dd MMM, yyyy;@";
System.out.println(
"Converted pattern in FRENCH locale is: "
+ DateFormatConverter.convert(Locale.FRENCH, "m/d/yy h:mm:ss"));
DataFormat df = wb.createDataFormat();
cs2.setDataFormat(df.getFormat(excelFormatPattern));
Cell cell1 = row.createCell(1);
cell1.setCellValue(new Date(1385903303326L));
cell1.setCellStyle(cs2);
FileOutputStream out = new FileOutputStream("/tmp/issue26_out.xlsx");
wb.write(out);
out.close();
System.out.println("Wrote /tmp/issue26_out.xlsx");
}
/**
* Creates a new instance.
*
* @param workbook target workbook
* @throws IllegalArgumentException if some parameters were {@code null}
*/
WorkbookInfo(Workbook workbook) {
if (workbook == null) {
throw new IllegalArgumentException("workbook must not be null"); // $NON-NLS-1$
}
this.workbook = workbook;
Font font = workbook.createFont();
commonStyle = workbook.createCellStyle();
commonStyle.setFont(font);
commonStyle.setBorderTop(BorderStyle.THIN);
commonStyle.setBorderBottom(BorderStyle.THIN);
commonStyle.setBorderLeft(BorderStyle.THIN);
commonStyle.setBorderRight(BorderStyle.THIN);
titleStyle = workbook.createCellStyle();
titleStyle.cloneStyleFrom(commonStyle);
titleStyle.setLocked(true);
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
titleStyle.setAlignment(HorizontalAlignment.CENTER);
CreationHelper helper = workbook.getCreationHelper();
DataFormat df = helper.createDataFormat();
dataStyle = workbook.createCellStyle();
dataStyle.cloneStyleFrom(commonStyle);
dateDataStyle = workbook.createCellStyle();
dateDataStyle.cloneStyleFrom(commonStyle);
dateDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd")); // $NON-NLS-1$
timeDataStyle = workbook.createCellStyle();
timeDataStyle.cloneStyleFrom(commonStyle);
timeDataStyle.setDataFormat(df.getFormat("hh:mm:ss")); // $NON-NLS-1$
datetimeDataStyle = workbook.createCellStyle();
datetimeDataStyle.cloneStyleFrom(commonStyle);
datetimeDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd hh:mm:ss")); // $NON-NLS-1$
}
public CellStyle getNumberFormat(
int j,
Map<Integer, CellStyle> decimalFormats,
Sheet sheet,
CreationHelper createHelper,
CellType celltype) {
int mapPosition = j;
if (celltype.equals(CellType.TOTAL)) {
mapPosition = j + 90000;
} else if (celltype.equals(CellType.SUBTOTAL)) {
mapPosition = j + 80000;
} else if (celltype.equals(CellType.CF)) {
mapPosition = j + 60000;
}
if (decimalFormats.get(mapPosition) != null) return decimalFormats.get(mapPosition);
if (celltype.equals(CellType.CF)) {
j = this.getCalculatedFieldDecimals();
}
String decimals = "";
for (int i = 0; i < j; i++) {
decimals += "0";
}
CellStyle cellStyle = this.buildDataCellStyle(sheet);
DataFormat df = createHelper.createDataFormat();
String format = "#,##0";
if (decimals.length() > 0) {
format += "." + decimals;
}
cellStyle.setDataFormat(df.getFormat(format));
if (celltype.equals(CellType.TOTAL)) {
cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
}
if (celltype.equals(CellType.CF)) {
cellStyle.setFillForegroundColor(IndexedColors.DARK_YELLOW.getIndex());
}
if (celltype.equals(CellType.SUBTOTAL)) {
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
}
decimalFormats.put(mapPosition, cellStyle);
return cellStyle;
}
/**
* 添加一个单元格
*
* @param row 添加的行
* @param column 添加列号
* @param val 添加值
* @param align 对齐方式（1：靠左；2：居中；3：靠右）
* @return 单元格对象
*/
public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType) {
Cell cell = row.createCell(column);
CellStyle style = styles.get("data" + (align >= 1 && align <= 3 ? align : ""));
try {
if (val == null) {
cell.setCellValue("");
} else if (val instanceof String) {
cell.setCellValue((String) val);
} else if (val instanceof Integer) {
cell.setCellValue((Integer) val);
} else if (val instanceof Long) {
cell.setCellValue((Long) val);
} else if (val instanceof Double) {
cell.setCellValue((Double) val);
} else if (val instanceof Float) {
cell.setCellValue((Float) val);
} else if (val instanceof Date) {
DataFormat format = wb.createDataFormat();
style.setDataFormat(format.getFormat("yyyy-MM-dd"));
cell.setCellValue((Date) val);
} else {
if (fieldType != Class.class) {
cell.setCellValue(
(String) fieldType.getMethod("setValue", Object.class).invoke(null, val));
} else {
cell.setCellValue(
(String)
Class.forName(
this.getClass()
.getName()
.replaceAll(
this.getClass().getSimpleName(),
"fieldtype." + val.getClass().getSimpleName() + "Type"))
.getMethod("setValue", Object.class)
.invoke(null, val));
}
}
} catch (Exception ex) {
log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString());
cell.setCellValue(val.toString());
}
cell.setCellStyle(style);
return cell;
}
public void setCellMoney(int numRow, int numCol) {
try {
if (wb.getSheetAt(0) != null) {
Sheet aSheet = wb.getSheetAt(0);
DataFormat format = wb.createDataFormat();
Row row = aSheet.getRow((short) numRow);
Cell csCell = row.getCell((short) numCol);
CellStyle style = wb.createCellStyle();
style.setDataFormat(format.getFormat("0.00"));
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);
csCell.setCellStyle(style);
}
} catch (Exception e) {
System.out.println("insertDataToExcel" + e);
}
}
private static Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
short borderColor = IndexedColors.GREY_50_PERCENT.getIndex();
CellStyle style;
DataFormat format = wb.createDataFormat();
Font titleFont = wb.createFont();
titleFont.setFontName("Tahoma");
titleFont.setFontHeightInPoints((short) 12);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_LEFT);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFont(titleFont);
styles.put("title", style);
Font headFont = wb.createFont();
headFont.setFontName("Tahoma");
headFont.setFontHeightInPoints((short) 11);
headFont.setColor(IndexedColors.WHITE.getIndex());
headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(headFont);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setWrapText(true);
styles.put("header", style);
Font groupFont = wb.createFont();
groupFont.setFontName("Tahoma");
groupFont.setFontHeightInPoints((short) 11);
groupFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_LEFT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setWrapText(true);
style.setFont(groupFont);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("groupleft", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setWrapText(true);
style.setFont(groupFont);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("groupright", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setWrapText(true);
/* style.setFont(groupFont); */
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("groupcenter", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setWrapText(true);
style.setDataFormat(format.getFormat("#,##0"));
/* style.setFont(groupFont); */
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("groupnumber", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_LEFT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setWrapText(true);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
/* style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
*/ styles.put("cellleft", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setWrapText(true);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("cellcenter", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setWrapText(true);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("cellright", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setWrapText(true);
style.setDataFormat(format.getFormat("#,##0"));
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("cellnumber", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setWrapText(true);
style.setDataFormat(format.getFormat("#,##0.00"));
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("cellnumber2", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_LEFT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setWrapText(true);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
styles.put("celltop", style);
return styles;
}
/*
* (non-Javadoc)
*
* @see
* com.toolsverse.etl.connector.DataSetConnector#prePersist(com.toolsverse
* .etl.connector.DataSetConnectorParams, com.toolsverse.etl.common.DataSet,
* com.toolsverse.etl.driver.Driver)
*/
@SuppressWarnings("resource")
public void prePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver)
throws Exception {
String fileName = null;
OutputStream out = null;
if (params.getOutputStream() == null) {
fileName =
SystemConfig.instance()
.getPathUsingAppFolders(
params.getFileName(
dataSet.getOwnerName() != null ? dataSet.getOwnerName() : dataSet.getName(),
".xls",
true));
params.setRealFileName(fileName);
out = new FileOutputStream(fileName);
if (params.getTransactionMonitor() != null) params.getTransactionMonitor().addFile(fileName);
} else out = params.getOutputStream();
params.setOut(out);
Workbook workbook = new HSSFWorkbook();
params.setWorkbook(workbook);
Sheet sheet =
workbook.createSheet(
Utils.isNothing(params.getSheetName()) ? dataSet.getName() : params.getSheetName());
params.setSheet(sheet);
Font labelFont = workbook.createFont();
labelFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
CellStyle labelCellStyle = workbook.createCellStyle();
labelCellStyle.setFont(labelFont);
DataFormat dateTimeFormat = workbook.createDataFormat();
CellStyle dateTimeCellStyle = workbook.createCellStyle();
dateTimeCellStyle.setDataFormat(dateTimeFormat.getFormat(params.getDateTimeFormat()));
params.setDateTimeCellStyle(dateTimeCellStyle);
DataFormat dateFormat = workbook.createDataFormat();
CellStyle dateCellStyle = workbook.createCellStyle();
dateCellStyle.setDataFormat(dateFormat.getFormat(params.getDateFormat()));
params.setDateCellStyle(dateCellStyle);
DataFormat timeFormat = workbook.createDataFormat();
CellStyle timeCellStyle = workbook.createCellStyle();
timeCellStyle.setDataFormat(timeFormat.getFormat(params.getTimeFormat()));
params.setTimeCellStyle(timeCellStyle);
// column names
Row excelRow = sheet.createRow(0);
// metadata
int col = 0;
for (FieldDef fieldDef : dataSet.getFields().getList()) {
if (!fieldDef.isVisible()) continue;
Cell labelCell = excelRow.createCell(col++, Cell.CELL_TYPE_STRING);
labelCell.setCellStyle(labelCellStyle);
labelCell.setCellValue(fieldDef.getName());
}
params.setPrePersistOccured(true);
}