List of usage examples for org.apache.poi.ss.usermodel CellStyle setDataFormat
void setDataFormat(short fmt);
From source file:com.actelion.research.spiritapp.ui.util.POIUtils.java
License:Open Source License
@SuppressWarnings("rawtypes") public static void exportToExcel(String[][] table, ExportMode exportMode) throws IOException { Class[] types = getTypes(table); Workbook wb = new XSSFWorkbook(); Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; DataFormat df = wb.createDataFormat(); Font font = wb.createFont();//from w ww . ja v a 2 s . c o m font.setFontName("Serif"); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 15); style = wb.createCellStyle(); style.setFont(font); styles.put("title", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 10); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(font); style.setWrapText(true); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("th", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font); style.setWrapText(true); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); 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.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font); style.setWrapText(true); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td-border", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td-double", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td-right", style); font = wb.createFont(); font.setFontName("Serif"); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td-bold", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font); style.setDataFormat(df.getFormat("d.mm.yyyy h:MM")); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td-date", style); Sheet sheet = wb.createSheet(); sheet.setFitToPage(true); Cell cell; int maxRows = 0; for (int r = 0; r < table.length; r++) { Row row = sheet.createRow(r); if (r == 0) { row.setRowStyle(styles.get("th")); } int rows = 1; for (int c = 0; c < table[r].length; c++) { cell = row.createCell(c); String s = table[r][c]; if (s == null) continue; rows = Math.max(rows, s.split("\n").length); try { if (exportMode == ExportMode.HEADERS_TOP && r == 0) { cell.setCellStyle(styles.get("th")); cell.setCellValue(s); } else if (exportMode == ExportMode.HEADERS_TOPLEFT && (r == 0 || c == 0)) { if (r == 0 && c == 0) { cell.setCellStyle(styles.get("td")); } else { cell.setCellStyle(styles.get("th")); } cell.setCellValue(s); } else if (types[c] == Double.class) { cell.setCellStyle(styles.get("td-double")); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(Double.parseDouble(s)); } else if (types[c] == String.class) { cell.setCellStyle( styles.get(exportMode == ExportMode.HEADERS_TOPLEFT ? "td-border" : "td")); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(s); } else { cell.setCellStyle(styles.get("td-right")); cell.setCellValue(s); } } catch (Exception e) { cell.setCellStyle(styles.get("td")); cell.setCellValue(s); } } maxRows = Math.max(maxRows, rows); row.setHeightInPoints(rows * 16f); } // Add footer notes if (footerData.size() > 0) { Row row = sheet.createRow(table.length); row.setHeightInPoints((footerData.size() * sheet.getDefaultRowHeightInPoints())); cell = row.createCell(0); sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), //first row (0-based) row.getRowNum(), //last row (0-based) 0, //first column (0-based) table[0].length - 1 //last column (0-based) )); //for ( String data : footerData ) { style = wb.createCellStyle(); style.setWrapText(true); cell.setCellStyle(style); cell.setCellValue(MiscUtils.flatten(footerData, "\n")); //} } footerData.clear(); autoSizeColumns(sheet); if (table.length > 0) { for (int c = 0; c < table[0].length; c++) { if (sheet.getColumnWidth(c) > 10000) sheet.setColumnWidth(c, 3000); } } if (exportMode == ExportMode.HEADERS_TOPLEFT) { for (int r = 1; r < table.length; r++) { sheet.getRow(r).setHeightInPoints(maxRows * 16f); } } File reportFile = IOUtils.createTempFile("export_", ".xlsx"); FileOutputStream out = new FileOutputStream(reportFile); wb.write(out); wb.close(); out.close(); Desktop.getDesktop().open(reportFile); }
From source file:com.adobe.acs.commons.data.SpreadsheetTest.java
License:Apache License
@BeforeClass public static void setUp() throws IOException { testWorkbook = new XSSFWorkbook(); XSSFSheet sheet = testWorkbook.createSheet("sheet 1"); createRow(sheet, header);// w w w . j ava2 s .c o m createRow(sheet, "/test/a1", "A-1"); createRow(sheet, "/test/a2", "A-2", "val"); createRow(sheet, "/test/a1/a1a", "A-1-A", "val"); createRow(sheet, "/test/a3/a3a", "A-3-A", "val"); XSSFRow valuesRow = createRow(sheet, "/some/types", "Types", "...", "12345", "one,two,three", "four;five;six", "12.345", "One Value", null, "Another Value"); XSSFCell dateCell = valuesRow.createCell(10); dateCell.setCellValue(testDate); CellStyle dateStyle = testWorkbook.createCellStyle(); dateStyle.setDataFormat(testWorkbook.createDataFormat().getFormat("YYYY-mm-dd")); dateCell.setCellStyle(dateStyle); testWorkbook.write(workbookData); workbookData.close(); InputStream dataTypesFile = SpreadsheetTest.class .getResourceAsStream("/com/adobe/acs/commons/data/spreadsheet-data-types.xlsx"); dataTypesSheet = new Spreadsheet(false, dataTypesFile); }
From source file:com.adobe.acs.commons.mcp.impl.ProcessErrorReportExcelServlet.java
License:Apache License
@SuppressWarnings("squid:S3776") protected Workbook createSpreadsheet(ManagedProcess report) { Workbook wb = new XSSFWorkbook(); String name = report.getName(); for (char ch : new char[] { '\\', '/', '*', '[', ']', ':', '?' }) { name = StringUtils.remove(name, ch); }/*www. ja va 2s. com*/ Sheet sheet = wb.createSheet(name); sheet.createFreezePane(0, 1, 0, 1); Row headerRow = sheet.createRow(0); CellStyle headerStyle = createHeaderStyle(wb); CellStyle dateStyle = wb.createCellStyle(); CreationHelper createHelper = wb.getCreationHelper(); dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyy/mm/dd h:mm:ss")); for (String columnName : Arrays.asList("Time", "Path", "Error", "Stack trace")) { Cell headerCell = headerRow.createCell(headerRow.getPhysicalNumberOfCells()); headerCell.setCellValue(columnName); headerCell.setCellStyle(headerStyle); } Collection<ArchivedProcessFailure> rows = report.getReportedErrorsList(); //make rows, don't forget the header row for (ArchivedProcessFailure error : rows) { Row row = sheet.createRow(sheet.getPhysicalNumberOfRows()); Cell c; c = row.createCell(0); c.setCellValue(error.time); c.setCellStyle(dateStyle); c = row.createCell(1); c.setCellValue(error.nodePath); c = row.createCell(2); c.setCellValue(error.error); c = row.createCell(3); c.setCellValue(error.stackTrace); } autosize(sheet, 4); sheet.setAutoFilter(new CellRangeAddress(0, 1 + rows.size(), 0, 3)); return wb; }
From source file:com.aistor.common.utils.excel.ExportExcel.java
License:Open Source License
/** * ?/*w w w. j av a2 s. co m*/ * @param row * @param column ? * @param val * @param align ??1?23?? * @return ? */ public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType) { Cell cell = row.createCell(column); CellStyle style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); if (align == 1) { style.setAlignment(CellStyle.ALIGN_LEFT); } else if (align == 2) { style.setAlignment(CellStyle.ALIGN_CENTER); } else if (align == 3) { style.setAlignment(CellStyle.ALIGN_RIGHT); } 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; }
From source file:com.AllenBarr.CallSheetGenerator.Generator.java
License:Open Source License
public int generateSheet(File file, Contributor contrib) { //create workbook file final String fileName = file.toString(); final Workbook wb; if (fileName.endsWith(".xlsx")) { wb = new XSSFWorkbook(); } else if (fileName.endsWith(".xls")) { wb = new HSSFWorkbook(); } else {/* w w w . j a va2s. c om*/ return 1; } //create sheet final Sheet sheet = wb.createSheet("Call Sheet"); final Header header = sheet.getHeader(); header.setCenter("Anderson for Iowa Call Sheet"); //add empty cells final Row[] row = new Row[22 + contrib.getDonationsLength()]; final Cell[][] cell = new Cell[6][22 + contrib.getDonationsLength()]; for (int i = 0; i < (22 + contrib.getDonationsLength()); i++) { row[i] = sheet.createRow((short) i); for (int j = 0; j < 6; j++) { cell[j][i] = row[i].createCell(j); } } //populate cells with data //column 1 cell[0][0].setCellValue(contrib.getName()); cell[0][3].setCellValue("Sex:"); cell[0][4].setCellValue("Party:"); cell[0][5].setCellValue("Phone #:"); cell[0][6].setCellValue("Home #:"); cell[0][7].setCellValue("Cell #:"); cell[0][8].setCellValue("Work #:"); cell[0][10].setCellValue("Email:"); cell[0][12].setCellValue("Employer:"); cell[0][13].setCellValue("Occupation:"); cell[0][15].setCellValue("Past Contact:"); cell[0][17].setCellValue("Notes:"); cell[0][21].setCellValue("Contribution History:"); //column 2 cell[1][3].setCellValue(contrib.getSex()); cell[1][4].setCellValue(contrib.getParty()); cell[1][5].setCellValue(contrib.getPhone()); cell[1][6].setCellValue(contrib.getHomePhone()); cell[1][7].setCellValue(contrib.getCellPhone()); cell[1][8].setCellValue(contrib.getWorkPhone()); cell[1][9].setCellValue("x" + contrib.getWorkExtension()); cell[1][10].setCellValue(contrib.getEmail()); cell[1][12].setCellValue(contrib.getEmployer()); cell[1][13].setCellValue(contrib.getOccupation()); cell[1][17].setCellValue(contrib.getNotes()); //column 4 cell[3][3].setCellValue("Salutation:"); cell[3][4].setCellValue("Age:"); cell[3][5].setCellValue("Spouse:"); cell[3][7].setCellValue("Address:"); cell[3][10].setCellValue("TARGET:"); //column 5 cell[4][0].setCellValue("VANID:"); cell[4][3].setCellValue(contrib.getSalutation()); cell[4][4].setCellValue(contrib.getAge()); cell[4][5].setCellValue(contrib.getSpouse()); cell[4][7].setCellValue(contrib.getStreetAddress()); cell[4][8].setCellValue(contrib.getCity() + ", " + contrib.getState() + " " + contrib.getZip()); //column 6 cell[5][0].setCellValue(contrib.getVANID()); //contribution cells for (int i = 0; i < contrib.getDonationsLength(); i++) { cell[0][i + 22].setCellValue(contrib.getDonation(i).getDonationDate()); cell[1][i + 22].setCellValue(contrib.getDonation(i).getRecipient()); cell[5][i + 22].setCellValue(contrib.getDonation(i).getAmount()); } //format cells //Name cell sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); final CellStyle leftBoldUnderline14Style = wb.createCellStyle(); final Font boldUnderline14Font = wb.createFont(); boldUnderline14Font.setBoldweight(Font.BOLDWEIGHT_BOLD); boldUnderline14Font.setUnderline(Font.U_SINGLE); boldUnderline14Font.setFontHeightInPoints((short) 14); boldUnderline14Font.setFontName("Garamond"); leftBoldUnderline14Style.setFont(boldUnderline14Font); leftBoldUnderline14Style.setAlignment(CellStyle.ALIGN_LEFT); cell[0][0].setCellStyle(leftBoldUnderline14Style); //field name cells final CellStyle rightBold10Style = wb.createCellStyle(); final Font bold10Font = wb.createFont(); bold10Font.setBoldweight(Font.BOLDWEIGHT_BOLD); bold10Font.setFontHeightInPoints((short) 10); bold10Font.setFontName("Garamond"); rightBold10Style.setFont(bold10Font); rightBold10Style.setAlignment(CellStyle.ALIGN_RIGHT); for (int i = 3; i < 22; i++) { cell[0][i].setCellStyle(rightBold10Style); } sheet.addMergedRegion(new CellRangeAddress(21, 21, 0, 1)); for (int i = 3; i < 11; i++) { cell[3][i].setCellStyle(rightBold10Style); } cell[4][0].setCellStyle(rightBold10Style); //field content cells final CellStyle left10Style = wb.createCellStyle(); final Font garamond10Font = wb.createFont(); garamond10Font.setFontHeightInPoints((short) 10); garamond10Font.setFontName("Garamond"); left10Style.setFont(garamond10Font); left10Style.setAlignment(CellStyle.ALIGN_LEFT); for (int i = 3; i < 5; i++) { cell[1][i].setCellStyle(left10Style); } //phone number cells final CellStyle phoneStyle = wb.createCellStyle(); phoneStyle.setFont(garamond10Font); phoneStyle.setAlignment(CellStyle.ALIGN_LEFT); final CreationHelper createHelper = wb.getCreationHelper(); phoneStyle.setDataFormat(createHelper.createDataFormat().getFormat("[<=9999999]###-####;(###) ###-####")); for (int i = 5; i < 9; i++) { cell[1][i].setCellStyle(phoneStyle); sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2)); } cell[1][9].setCellStyle(left10Style); //email through past contact for (int i = 10; i < 16; i++) { cell[1][i].setCellStyle(left10Style); } //notes CellStyle noteStyle = wb.createCellStyle(); noteStyle.cloneStyleFrom(left10Style); noteStyle.setWrapText(true); cell[1][17].setCellStyle(noteStyle); //column E for (int i = 3; i < 11; i++) { cell[4][i].setCellStyle(left10Style); } //VanID Cell final CellStyle right10Style = wb.createCellStyle(); right10Style.setFont(garamond10Font); right10Style.setAlignment(CellStyle.ALIGN_RIGHT); cell[5][0].setCellStyle(right10Style); //Notes cell sheet.addMergedRegion(new CellRangeAddress(17, 19, 1, 5)); //contribution cells final CellStyle date10Style = wb.createCellStyle(); date10Style.setFont(garamond10Font); date10Style.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy")); date10Style.setBorderBottom(CellStyle.BORDER_THIN); date10Style.setBorderTop(CellStyle.BORDER_THIN); date10Style.setBorderLeft(CellStyle.BORDER_THIN); date10Style.setBorderRight(CellStyle.BORDER_THIN); final CellStyle contributionStyle = wb.createCellStyle(); contributionStyle.cloneStyleFrom(left10Style); contributionStyle.setBorderBottom(CellStyle.BORDER_THIN); contributionStyle.setBorderTop(CellStyle.BORDER_THIN); contributionStyle.setBorderLeft(CellStyle.BORDER_THIN); contributionStyle.setBorderRight(CellStyle.BORDER_THIN); final CellStyle money10Style = wb.createCellStyle(); money10Style.setFont(garamond10Font); money10Style.setDataFormat( createHelper.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)")); money10Style.setBorderBottom(CellStyle.BORDER_THIN); money10Style.setBorderTop(CellStyle.BORDER_THIN); money10Style.setBorderLeft(CellStyle.BORDER_THIN); money10Style.setBorderRight(CellStyle.BORDER_THIN); for (int i = 22; i < 22 + contrib.getDonationsLength(); i++) { cell[0][i].setCellStyle(date10Style); cell[1][i].setCellStyle(contributionStyle); cell[2][i].setCellStyle(contributionStyle); cell[3][i].setCellStyle(contributionStyle); cell[4][i].setCellStyle(contributionStyle); sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 4)); cell[5][i].setCellStyle(money10Style); } //resize columns sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); try { FileOutputStream fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { return 1; } catch (IOException ex) { return 1; } return 0; }
From source file:com.b510.excel.client.BusinessPlan.java
License:Apache License
/** * create a library of cell styles// ww w . j a v a 2s .co m */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); DataFormat df = wb.createDataFormat(); CellStyle style; Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); styles.put("header", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); style.setDataFormat(df.getFormat("d-mmm")); styles.put("header_date", style); Font font1 = wb.createFont(); font1.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font1); styles.put("cell_b", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font1); styles.put("cell_b_centered", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font1); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_b_date", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font1); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_g", style); Font font2 = wb.createFont(); font2.setColor(IndexedColors.BLUE.getIndex()); font2.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font2); styles.put("cell_bb", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font1); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_bg", style); Font font3 = wb.createFont(); font3.setFontHeightInPoints((short) 14); font3.setColor(IndexedColors.DARK_BLUE.getIndex()); font3.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font3); style.setWrapText(true); styles.put("cell_h", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setWrapText(true); styles.put("cell_normal", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); styles.put("cell_normal_centered", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setWrapText(true); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_normal_date", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setIndention((short) 1); style.setWrapText(true); styles.put("cell_indented", style); style = createBorderedStyle(wb); style.setFillForegroundColor(IndexedColors.BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); styles.put("cell_blue", style); return styles; }
From source file:com.b510.excel.client.LoanCalculator.java
License:Apache License
/** * cell styles used for formatting calendar sheets *//*from w w w . j av a 2s .c o m*/ 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) 14); titleFont.setFontName("Trebuchet MS"); style = wb.createCellStyle(); style.setFont(titleFont); style.setBorderBottom(CellStyle.BORDER_DOTTED); 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(CellStyle.ALIGN_LEFT); style.setFont(itemFont); styles.put("item_left", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(itemFont); styles.put("item_right", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(itemFont); style.setBorderRight(CellStyle.BORDER_DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(CellStyle.BORDER_DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(CellStyle.BORDER_DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(CellStyle.BORDER_DOTTED); 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(CellStyle.ALIGN_RIGHT); style.setFont(itemFont); style.setBorderRight(CellStyle.BORDER_DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(CellStyle.BORDER_DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(CellStyle.BORDER_DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(CellStyle.BORDER_DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0.000%")); styles.put("input_%", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(itemFont); style.setBorderRight(CellStyle.BORDER_DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(CellStyle.BORDER_DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(CellStyle.BORDER_DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(CellStyle.BORDER_DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0")); styles.put("input_i", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(itemFont); style.setDataFormat(wb.createDataFormat().getFormat("m/d/yy")); styles.put("input_d", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(itemFont); style.setBorderRight(CellStyle.BORDER_DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(CellStyle.BORDER_DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(CellStyle.BORDER_DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(CellStyle.BORDER_DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("$##,##0.00")); style.setBorderBottom(CellStyle.BORDER_DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); styles.put("formula_$", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(itemFont); style.setBorderRight(CellStyle.BORDER_DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(CellStyle.BORDER_DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(CellStyle.BORDER_DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(CellStyle.BORDER_DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0")); style.setBorderBottom(CellStyle.BORDER_DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); styles.put("formula_i", style); return styles; }
From source file:com.b510.excel.client.TimesheetDemo.java
License:Apache License
/** * Create a library of cell styles/* w ww .ja v a2s. c o m*/ */ 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; }
From source file:com.bc.util.XlsxExporter.java
public static void WriteInvoiceToFile(File output, CustomerOrder order, ArrayList<CustomerOrderItem> items) { try {/*from w w w. j ava 2 s. c om*/ if (output.exists()) { log.info(output.getName() + " exists. Deleting"); output.delete(); log.info("Deleted " + output.getName()); } log.info("Creating xlsx file..."); FileOutputStream fos = new FileOutputStream(output); XSSFWorkbook workBook = new XSSFWorkbook(); XSSFSheet sheet = workBook.createSheet("Order"); CellStyle style = workBook.createCellStyle(); style.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.ALIGN_FILL); Font font = workBook.createFont(); font.setColor(IndexedColors.WHITE.getIndex()); style.setFont(font); String[] columnHeaders = { "Invoice", "Salesman", "Customer Name", "Customer Code", "PO", "Ship Date", "Post Date", "ISBN", "ISBN13", "Title", "List Price", "Price", "Quantity", "Shipped", "Discount", "Extended Price" }; log.info("Creating header row & columns"); Row row = sheet.createRow(0); for (int i = 0; i < columnHeaders.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(columnHeaders[i]); cell.setCellStyle(style); sheet.setColumnWidth(i, 4500); } sheet.setColumnWidth(9, 13500); log.info("Writing " + items.size() + " records"); XSSFDataFormat decimalFormat = workBook.createDataFormat(); CellStyle dstyle = workBook.createCellStyle(); dstyle.setDataFormat(decimalFormat.getFormat("0.00")); int i = 1; for (CustomerOrderItem orderItem : items) { Row drow = sheet.createRow(i++); Hibernate.initialize(order.getCustomerOrderItems()); String strValue; Float floatValue; Integer intVal; Cell cInvoice = drow.createCell(0); strValue = order.getInvoiceNumber(); if (strValue == null) strValue = ""; cInvoice.setCellValue(order.getInvoiceNumber()); Cell cSalesman = drow.createCell(1); strValue = order.getSalesman(); if (strValue == null) strValue = ""; cSalesman.setCellValue(strValue); Cell cCustomerName = drow.createCell(2); strValue = order.getCustomer().getCompanyName(); if (strValue == null) strValue = ""; cCustomerName.setCellValue(strValue); Cell cCustomerCode = drow.createCell(3); strValue = order.getCustomerCode(); if (strValue == null) strValue = ""; cCustomerCode.setCellValue(strValue); Cell cPo = drow.createCell(4); strValue = order.getPoNumber(); if (strValue == null) strValue = ""; cPo.setCellValue(strValue); Cell cShipDate = drow.createCell(5); Date d = order.getShipDate(); if (d == null) cShipDate.setCellValue(""); else cShipDate.setCellValue("" + d.getMonth() + "/" + d.getDay() + "/" + (1900 + d.getYear())); Cell cPostDate = drow.createCell(6); d = order.getPostDate(); if (d == null) cPostDate.setCellValue(""); else cPostDate.setCellValue("" + d.getMonth() + "/" + d.getDay() + "/" + (1900 + d.getYear())); Hibernate.initialize(orderItem.getInventoryItem()); InventoryItem item = orderItem.getInventoryItem(); //orderItem.getInventoryItem(); if (item != null) { Cell cIsbn = drow.createCell(7); strValue = item.getIsbn(); if (strValue == null) strValue = ""; cIsbn.setCellValue(strValue); Cell cIsbn13 = drow.createCell(8); strValue = item.getIsbn13(); if (strValue == null) strValue = ""; cIsbn13.setCellValue(strValue); Cell cTitle = drow.createCell(9); strValue = item.getTitle(); if (strValue == null) strValue = ""; cTitle.setCellValue(strValue); Cell cListPrice = drow.createCell(10); floatValue = item.getListPrice(); cListPrice.setCellStyle(dstyle); if (floatValue == null) floatValue = 0.0f; cListPrice.setCellValue(floatValue); Cell cPrice = drow.createCell(11); floatValue = item.getSellingPrice(); cPrice.setCellStyle(dstyle); if (floatValue == null) floatValue = 0.0f; cPrice.setCellValue(floatValue); } Cell cQuantity = drow.createCell(12); intVal = orderItem.getQuantity(); log.info("Quantity : " + intVal); if (intVal == null) intVal = 0; cQuantity.setCellValue(intVal); Cell cShipped = drow.createCell(13); intVal = orderItem.getFilled(); log.info("Shipped QTY : " + intVal); if (intVal == null) intVal = 0; cShipped.setCellValue(intVal); Cell cDiscount = drow.createCell(14); cDiscount.setCellStyle(dstyle); floatValue = orderItem.getDiscount(); if (floatValue == null) floatValue = 0.0f; cDiscount.setCellValue(floatValue); Cell cExtendedPrice = drow.createCell(15); cExtendedPrice.setCellStyle(dstyle); BigDecimal dValue = orderItem.getTotalPrice(); if (dValue == null) dValue = BigDecimal.ZERO; cExtendedPrice.setCellValue(dValue.doubleValue()); } workBook.write(fos); log.info("Finished writing data, closing..."); fos.close(); log.info("Completed exporting data to " + output.getAbsolutePath()); } catch (Exception ex) { Logger.getLogger(XlsxExporter.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.catexpress.util.FormatosPOI.java
public CellStyle estiloHeader(Workbook wb, int tipo) { Font fuente = wb.createFont(); CreationHelper createHelper = wb.getCreationHelper(); fuente.setFontName("Calibri"); fuente.setBold(true);/*from ww w.j av a 2 s . c om*/ if (tipo == TITULO || tipo == SUCURSAL || tipo == LABEL || tipo == FECHA) { fuente.setFontHeightInPoints((short) 16); } else if (tipo == USUARIO) { fuente.setFontHeightInPoints((short) 20); } CellStyle estiloCelda = wb.createCellStyle(); estiloCelda.setFont(fuente); if (tipo == TITULO || tipo == USUARIO || tipo == LABEL || tipo == FECHA) { estiloCelda.setFillForegroundColor(IndexedColors.WHITE.getIndex()); } else if (tipo == SUCURSAL) { estiloCelda.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); } estiloCelda.setFillPattern(CellStyle.SOLID_FOREGROUND); if (tipo == LABEL || tipo == FECHA) { estiloCelda.setWrapText(false); } else if (tipo == TITULO || tipo == SUCURSAL || tipo == USUARIO) { estiloCelda.setWrapText(true); } estiloCelda.setAlignment(HorizontalAlignment.CENTER); if (tipo == LABEL || tipo == FECHA) { estiloCelda.setVerticalAlignment(VerticalAlignment.BOTTOM); } else if (tipo == TITULO || tipo == SUCURSAL || tipo == USUARIO) { estiloCelda.setVerticalAlignment(VerticalAlignment.CENTER); } if (tipo == SUCURSAL) { estiloCelda.setBorderBottom(BorderStyle.MEDIUM); estiloCelda.setBorderTop(BorderStyle.MEDIUM); estiloCelda.setBorderLeft(BorderStyle.MEDIUM); estiloCelda.setBorderRight(BorderStyle.MEDIUM); } if (tipo == FECHA) { estiloCelda.setDataFormat(createHelper.createDataFormat().getFormat("dd \"de\" mmmm \"de\" yyyy")); } return estiloCelda; }