/** Create a library of cell styles */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 18); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 11); monthFont.setColor(IndexedColors.WHITE.getIndex()); 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(monthFont); style.setWrapText(true); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); 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("cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula_2", style); return styles; }
private Result populateLoansTable(Sheet savingsTransactionSheet) { Result result = new Result(); Workbook workbook = savingsTransactionSheet.getWorkbook(); CellStyle dateCellStyle = workbook.createCellStyle(); short df = workbook.createDataFormat().getFormat("dd/mm/yy"); dateCellStyle.setDataFormat(df); int rowIndex = 1; Row row; Collections.sort(savings, CompactSavingsAccount.ClientNameComparator); try { for (CompactSavingsAccount savingsAccount : savings) { row = savingsTransactionSheet.createRow(rowIndex++); writeString(LOOKUP_CLIENT_NAME_COL, row, savingsAccount.getClientName()); writeLong(LOOKUP_ACCOUNT_NO_COL, row, Long.parseLong(savingsAccount.getAccountNo())); writeString(LOOKUP_PRODUCT_COL, row, savingsAccount.getSavingsProductName()); writeDouble(LOOKUP_OPENING_BALANCE_COL, row, savingsAccount.getMinRequiredOpeningBalance()); writeDate( LOOKUP_SAVINGS_ACTIVATION_DATE_COL, row, savingsAccount.getTimeline().getActivatedOnDate().get(2) + "/" + savingsAccount.getTimeline().getActivatedOnDate().get(1) + "/" + savingsAccount.getTimeline().getActivatedOnDate().get(0), dateCellStyle); } } catch (Exception e) { result.addError(e.getMessage()); logger.error(e.getMessage()); } return result; }
/* * 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"); }
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; }
private static Map createStyles(Workbook wb) { Map styles = new HashMap(); Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 14); titleFont.setFontName("Trebuchet MS"); CellStyle style = wb.createCellStyle(); style.setFont(titleFont); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); styles.put("title", style); Font itemFont = wb.createFont(); itemFont.setFontHeightInPoints((short) 9); itemFont.setFontName("Trebuchet MS"); style = wb.createCellStyle(); style.setAlignment((short) 1); style.setFont(itemFont); styles.put("item_left", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); styles.put("item_right", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); style.setBorderRight((short) 7); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft((short) 7); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop((short) 7); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat( wb.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)")); styles.put("input_$", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); style.setBorderRight((short) 7); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft((short) 7); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop((short) 7); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0.000%")); styles.put("input_%", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); style.setBorderRight((short) 7); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft((short) 7); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop((short) 7); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0")); styles.put("input_i", style); style = wb.createCellStyle(); style.setAlignment((short) 2); style.setFont(itemFont); style.setDataFormat(wb.createDataFormat().getFormat("m/d/yy")); styles.put("input_d", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); style.setBorderRight((short) 7); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft((short) 7); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop((short) 7); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("$##,##0.00")); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern((short) 1); styles.put("formula_$", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); style.setBorderRight((short) 7); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft((short) 7); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop((short) 7); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0")); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern((short) 1); styles.put("formula_i", 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); }