List of usage examples for org.apache.poi.ss.usermodel Sheet autoSizeColumn
void autoSizeColumn(int column);
From source file:at.fh.swenga.firefighters.report.ExcelFireEngineReportView.java
@Override protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { List<FireEngineModel> fireEngines = (List<FireEngineModel>) model.get("fireEngines"); // create a worksheet Sheet sheet = workbook.createSheet("FireEngine Report"); // create style for header cells CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setFontName("Arial"); style.setFillForegroundColor(HSSFColor.BLUE.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.WHITE.index); style.setFont(font);//from w ww .j a v a2 s . c om // create a new row in the worksheet Row headerRow = sheet.createRow(0); // create a new cell in the row Cell cell0 = headerRow.createCell(0); cell0.setCellValue("ID"); cell0.setCellStyle(style); // create a new cell in the row Cell cell1 = headerRow.createCell(1); cell1.setCellValue("Modell"); cell1.setCellStyle(style); // create a new cell in the row Cell cell2 = headerRow.createCell(2); cell2.setCellValue("Kennzeichen"); cell2.setCellStyle(style); // create a new cell in the row Cell cell3 = headerRow.createCell(3); cell3.setCellValue("Leistung"); cell3.setCellStyle(style); // create a new cell in the row Cell cell4 = headerRow.createCell(4); cell4.setCellValue("Baujahr"); cell4.setCellStyle(style); // create a new cell in the row Cell cell5 = headerRow.createCell(5); cell5.setCellValue("Aktiv"); cell5.setCellStyle(style); // create a new cell in the row Cell cell6 = headerRow.createCell(6); cell6.setCellValue("Funktion"); cell6.setCellStyle(style); // create a new cell in the row Cell cell7 = headerRow.createCell(7); cell7.setCellValue("Feuerwehr"); cell7.setCellStyle(style); // create multiple rows with fireEngines data int rowNum = 1; for (FireEngineModel fireEngine : fireEngines) { // create the row data Row row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(fireEngine.getId()); row.createCell(1).setCellValue(fireEngine.getModel()); row.createCell(2).setCellValue(fireEngine.getLicensePlate()); row.createCell(3).setCellValue(fireEngine.getPerformance()); row.createCell(4).setCellValue(fireEngine.getBuildYear()); row.createCell(5).setCellValue(fireEngine.getActive()); row.createCell(6).setCellValue(fireEngine.getAbbreviation().getAbbreviation()); row.createCell(7).setCellValue(fireEngine.getFireBrigade().getName()); } // adjust column width to fit the content sheet.autoSizeColumn((short) 0); sheet.autoSizeColumn((short) 1); sheet.autoSizeColumn((short) 2); sheet.autoSizeColumn((short) 3); sheet.autoSizeColumn((short) 4); sheet.autoSizeColumn((short) 5); sheet.autoSizeColumn((short) 6); sheet.autoSizeColumn((short) 7); }
From source file:bandaru_excelreadwrite.WritetoExcel.java
public void writeSongsListToExcel(List<Song> songList) { /*/*from w w w. j a v a 2 s .c om*/ Use XSSF for xlsx format and for xls use HSSF */ Workbook workbook = new XSSFWorkbook(); /* create new sheet */ Sheet songsSheet = workbook.createSheet("Albums"); XSSFCellStyle my_style = (XSSFCellStyle) workbook.createCellStyle(); /* Create XSSFFont object from the workbook */ XSSFFont my_font = (XSSFFont) workbook.createFont(); /* setting cell color */ CellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); /* setting Header color */ CellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(IndexedColors.DARK_RED.getIndex()); style2.setFillPattern(CellStyle.SOLID_FOREGROUND); style2.setAlignment(style2.ALIGN_CENTER); Row rowName = songsSheet.createRow(1); /* Merging the cells */ songsSheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3)); /* Applying style to attribute name */ int nameCellIndex = 1; Cell namecell = rowName.createCell(nameCellIndex++); namecell.setCellValue("Name"); namecell.setCellStyle(style); Cell cel = rowName.createCell(nameCellIndex++); cel.setCellValue("Bandaru, Sreekanth"); /* Applying underline to Name */ my_font.setUnderline(XSSFFont.U_SINGLE); my_style.setFont(my_font); /* Attaching the style to the cell */ CellStyle combined = workbook.createCellStyle(); combined.cloneStyleFrom(my_style); combined.cloneStyleFrom(style); combined.setAlignment(combined.ALIGN_CENTER); cel.setCellStyle(combined); /* Applying colors to header */ Row rowMain = songsSheet.createRow(3); SheetConditionalFormatting sheetCF = songsSheet.getSheetConditionalFormatting(); ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("3"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.LIME.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A4:F4") }; sheetCF.addConditionalFormatting(regions, rule1); /* setting new rule to apply alternate colors to cells having same Genre */ ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("4"); PatternFormatting fill2 = rule2.createPatternFormatting(); fill2.setFillBackgroundColor(IndexedColors.LEMON_CHIFFON.index); fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regionsAction = { CellRangeAddress.valueOf("A5:F5"), CellRangeAddress.valueOf("A6:F6"), CellRangeAddress.valueOf("A7:F7"), CellRangeAddress.valueOf("A8:F8"), CellRangeAddress.valueOf("A13:F13"), CellRangeAddress.valueOf("A14:F14"), CellRangeAddress.valueOf("A15:F15"), CellRangeAddress.valueOf("A16:F16"), CellRangeAddress.valueOf("A23:F23"), CellRangeAddress.valueOf("A24:F24"), CellRangeAddress.valueOf("A25:F25"), CellRangeAddress.valueOf("A26:F26") }; /* setting new rule to apply alternate colors to cells having same Fenre */ ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule("4"); PatternFormatting fill3 = rule3.createPatternFormatting(); fill3.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index); fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regionsAdv = { CellRangeAddress.valueOf("A9:F9"), CellRangeAddress.valueOf("A10:F10"), CellRangeAddress.valueOf("A11:F11"), CellRangeAddress.valueOf("A12:F12"), CellRangeAddress.valueOf("A17:F17"), CellRangeAddress.valueOf("A18:F18"), CellRangeAddress.valueOf("A19:F19"), CellRangeAddress.valueOf("A20:F20"), CellRangeAddress.valueOf("A21:F21"), CellRangeAddress.valueOf("A22:F22"), CellRangeAddress.valueOf("A27:F27"), CellRangeAddress.valueOf("A28:F28"), CellRangeAddress.valueOf("A29:F29") }; /* Applying above created rule formatting to cells */ sheetCF.addConditionalFormatting(regionsAction, rule2); sheetCF.addConditionalFormatting(regionsAdv, rule3); /* Setting coloumn header values */ int mainCellIndex = 0; CellStyle style4 = workbook.createCellStyle(); XSSFFont my_font2 = (XSSFFont) workbook.createFont(); my_font2.setBold(true); style4.setFont(my_font2); rowMain.setRowStyle(style4); rowMain.createCell(mainCellIndex++).setCellValue("SNO"); rowMain.createCell(mainCellIndex++).setCellValue("Genre"); rowMain.createCell(mainCellIndex++).setCellValue("Rating"); rowMain.createCell(mainCellIndex++).setCellValue("Movie Name"); rowMain.createCell(mainCellIndex++).setCellValue("Director"); rowMain.createCell(mainCellIndex++).setCellValue("Release Date"); /* populating cell values */ int rowIndex = 4; int sno = 1; for (Song song : songList) { if (song.getSno() != 0) { Row row = songsSheet.createRow(rowIndex++); int cellIndex = 0; /* first place in row is Sno */ row.createCell(cellIndex++).setCellValue(sno++); /* second place in row is Genre */ row.createCell(cellIndex++).setCellValue(song.getGenre()); /* third place in row is Critic score */ row.createCell(cellIndex++).setCellValue(song.getCriticscore()); /* fourth place in row is Album name */ row.createCell(cellIndex++).setCellValue(song.getAlbumname()); /* fifth place in row is Artist */ row.createCell(cellIndex++).setCellValue(song.getArtist()); /* sixth place in row is marks in date */ if (song.getReleasedate() != null) { Cell date = row.createCell(cellIndex++); DataFormat format = workbook.createDataFormat(); CellStyle dateStyle = workbook.createCellStyle(); dateStyle.setDataFormat(format.getFormat("dd-MMM-yyyy")); date.setCellStyle(dateStyle); date.setCellValue(song.getReleasedate()); /* auto-resizing columns */ songsSheet.autoSizeColumn(6); songsSheet.autoSizeColumn(5); songsSheet.autoSizeColumn(4); songsSheet.autoSizeColumn(3); songsSheet.autoSizeColumn(2); } } } /* writing this workbook to excel file. */ try { FileOutputStream fos = new FileOutputStream(FILE_PATH); workbook.write(fos); fos.close(); System.out.println(FILE_PATH + " is successfully written"); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:biz.webgate.dominoext.poi.component.kernel.simpleviewexport.WorkbooklExportProcessor.java
License:Apache License
public void process2HTTP(ExportModel expModel, UISimpleViewExport uis, HttpServletResponse hsr, DateTimeHelper dth) {// www.j av a 2s. co m try { String strFileName = uis.getDownloadFileName(); Workbook wbCurrent = null; if (strFileName.toLowerCase().endsWith(".xlsx")) { wbCurrent = new XSSFWorkbook(); } else { wbCurrent = new HSSFWorkbook(); } HashMap<String, CellStyle> hsCS = new HashMap<String, CellStyle>(); CreationHelper cr = wbCurrent.getCreationHelper(); CellStyle csDate = wbCurrent.createCellStyle(); csDate.setDataFormat(cr.createDataFormat().getFormat(dth.getDFDate().toPattern())); CellStyle csDateTime = wbCurrent.createCellStyle(); csDateTime.setDataFormat(cr.createDataFormat().getFormat(dth.getDFDateTime().toPattern())); CellStyle csTime = wbCurrent.createCellStyle(); csTime.setDataFormat(cr.createDataFormat().getFormat(dth.getDFTime().toPattern())); hsCS.put("DATE", csDate); hsCS.put("TIME", csTime); hsCS.put("DATETIME", csDateTime); Sheet sh = wbCurrent.createSheet("SVE Export"); int nRowCount = 0; // BUILDING HEADER if (uis.isIncludeHeader()) { Row rw = sh.createRow(nRowCount); int nCol = 0; for (ExportColumn expColumn : expModel.getColumns()) { rw.createCell(nCol).setCellValue(expColumn.getColumnName()); nCol++; } nRowCount++; } // Processing Values for (ExportDataRow expRow : expModel.getRows()) { Row rw = sh.createRow(nRowCount); int nCol = 0; for (ExportColumn expColumn : expModel.getColumns()) { Cell clCurrent = rw.createCell(nCol); setCellValue(expRow.getValue(expColumn.getPosition()), clCurrent, expColumn, hsCS); nCol++; } nRowCount++; } for (int nCol = 0; nCol < expModel.getColumns().size(); nCol++) { sh.autoSizeColumn(nCol); } if (strFileName.toLowerCase().endsWith(".xlsx")) { hsr.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); } else if (strFileName.toLowerCase().endsWith("xls")) { hsr.setContentType("application/vnd.ms-excel"); } else { hsr.setContentType("application/octet-stream"); } hsr.addHeader("Content-disposition", "inline; filename=\"" + strFileName + "\""); OutputStream os = hsr.getOutputStream(); ByteArrayOutputStream bos = new ByteArrayOutputStream(); wbCurrent.write(bos); bos.writeTo(os); os.close(); } catch (Exception e) { ErrorPageBuilder.getInstance().processError(hsr, "Error during SVE-Generation (Workbook Export)", e); } }
From source file:br.com.techne.gluonsoft.eowexport.builder.ExcelBuilder.java
License:Apache License
/** * mtodo cria bytes de documento Excel// www .jav a2s.c om * @param titles * @param columnIndex * @param dataRows * @param locale * @return * @throws Exception */ public static byte[] createExcelBytes(String[] titles, String[] columnIndex, List<HashMap<String, Object>> dataRows, Locale locale) throws Exception { //Workbook wb = new HSSFWorkbook(); XSSFWorkbook wb = new XSSFWorkbook(); byte[] outBytes; try { HashMap<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Tab 1"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int indexColumn = 0; indexColumn < titles.length; indexColumn++) { Cell cell = headerRow.createCell(indexColumn); cell.setCellValue(titles[indexColumn]); if ((titles.length - 1) < indexColumn) { cell.setCellValue(""); } else cell.setCellValue(titles[indexColumn]); cell.setCellStyle(styles.get("header")); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1;//devido constar titulo, comea do indice 1 ValueCellUtil vcutil = new ValueCellUtil(locale); for (int indexRow = 0; indexRow < dataRows.size(); indexRow++, rownum++) { row = sheet.createRow(rownum); HashMap<String, Object> dataRow = dataRows.get(indexRow); if (dataRow == null) continue; List<String> keysAttribs = null; if (columnIndex.length == 0) { keysAttribs = Arrays.asList(dataRow.keySet().toArray(new String[0])); Collections.reverse(keysAttribs); } else { keysAttribs = Arrays.asList(columnIndex); } int colCt = 0; for (String keyAttrib : keysAttribs) { cell = row.createCell(colCt); String styleName; cell.setCellValue(vcutil.parseValue(dataRow.get(keyAttrib)).toString()); //zebrando tabela if (indexRow % 2 == 0) { // even row styleName = "cell_normal_even"; } else { // odd row styleName = "cell_normal_odd"; } if (indexRow == 0) { //setando auto ajuste sheet.autoSizeColumn(colCt); } cell.setCellStyle(styles.get(styleName)); colCt++; } } sheet.setZoom(75); //75% scale // Write the output to a file // write for return byte[] ByteArrayOutputStream out = new ByteArrayOutputStream(); try { wb.write(out); outBytes = out.toByteArray(); } finally { out.close(); } } finally { wb.close(); } return outBytes; }
From source file:business.SongExcelParser.java
private void songsToWorkbook(Workbook wb, ArrayList<SongContainer> songContainerList) { Row row;/*from ww w. jav a2 s . co m*/ int rowNumber = 1; Sheet sheet = wb.createSheet(); row = setRowHeader(sheet); for (SongContainer container : songContainerList) { for (Song song : container.getSongs()) { row = sheet.createRow(rowNumber); setRowInfo(row, song, container); rowNumber++; } } for (int x = 0; x < sheet.getRow(0).getPhysicalNumberOfCells(); x++) { sheet.autoSizeColumn(x); } }
From source file:ch.dbs.actions.reports.HoldingsReport.java
License:Open Source License
private HSSFWorkbook getXLSContent(final Konto k) { final Text cn = new Text(); final Workbook wb = new HSSFWorkbook(); final Sheet s = wb.createSheet(); try {//from w w w. j ava 2s . c o m // add header for XLS initXLS(wb, s); // internal holdings are visible final List<Bestand> stock = new Bestand().getAllKontoBestand(k.getId(), true, cn.getConnection()); short rowNr = 0; for (final Bestand b : stock) { rowNr++; // add holdings getXLSLine(wb, s, b, rowNr); } // adjust all columns in size short columnNr = 0; while (columnNr < 21) { s.autoSizeColumn(columnNr); columnNr++; } } finally { cn.close(); } return (HSSFWorkbook) wb; }
From source file:ch.dbs.actions.reports.KbartReport.java
License:Open Source License
private HSSFWorkbook getXLSContent(final Konto k) { final Text cn = new Text(); final Workbook wb = new HSSFWorkbook(); final Sheet s = wb.createSheet(); try {/*from ww w . j av a 2s . c om*/ // add header for XLS initXLS(wb, s); // internal holdings are visible final List<Bestand> stock = new Bestand().getAllKontoBestand(k.getId(), true, cn.getConnection()); // transform to KbartForm final KbartForm kbf = new KbartForm(); final List<KbartForm> kbarts = kbf.createKbartForms(stock, cn.getConnection()); short rowNr = 0; for (final KbartForm kbart : kbarts) { rowNr++; // add holdings getXLSLine(wb, s, kbart, rowNr); } // adjust all columns in size short columnNr = 0; while (columnNr < 16) { s.autoSizeColumn(columnNr); columnNr++; } } finally { cn.close(); } return (HSSFWorkbook) wb; }
From source file:clases.Funciones.java
private String GenerateReporte_xls(JTable t, int open) { String rutaArchivo = ""; try {//from w ww . j a v a 2s .c om char rt = p.ReturnPropiedad(p.Ruta_SaveReports) .charAt(p.ReturnPropiedad(p.Ruta_SaveReports).length() - 1); if ("/".equalsIgnoreCase(String.valueOf(rt))) { rutaArchivo = p.ReturnPropiedad(p.Ruta_SaveReports) + ReturnNombreUsuario().replace(" ", "_") + rutaArchivo + "_" + GetFechaAndHourActual().replace(":", "_").replace(" ", "_").replace("-", "_") + ".xls"; } else { rutaArchivo = p.ReturnPropiedad(p.Ruta_SaveReports) + "/" + ReturnNombreUsuario().replace(" ", "_") + rutaArchivo + "_" + GetFechaAndHourActual().replace(":", "_").replace(" ", "_").replace("-", "_") + ".xls"; } File archivoXLS = new File(rutaArchivo); if (archivoXLS.exists()) archivoXLS.delete(); archivoXLS.createNewFile(); Workbook libro = new HSSFWorkbook(); FileOutputStream archivo = new FileOutputStream(archivoXLS); Sheet hoja = libro.createSheet(ReturnDatosFisicos(this.Datos_Nombre)); for (int f = 0; f < t.getRowCount() + 1; f++) { Row fila = hoja.createRow(f); for (int c = 0; c < t.getColumnCount(); c++) { Cell celda = fila.createCell(c); if (f == 0) { celda.setCellValue(String.valueOf(t.getColumnName(c))); } else { celda.setCellValue(String.valueOf(t.getValueAt(f - 1, c))); } hoja.autoSizeColumn(c); } } libro.write(archivo); archivo.close(); if (open > 0) { Desktop.getDesktop().open(archivoXLS); } } catch (IOException ex) { Alert(ex.getMessage()); } return rutaArchivo; }
From source file:com.adobe.acs.commons.mcp.impl.ProcessErrorReportExcelServlet.java
License:Apache License
void autosize(Sheet sheet, int lastColumnIndex) { for (int i = 0; i <= lastColumnIndex; i++) { try {/*from w ww .j a v a 2 s . c o m*/ sheet.autoSizeColumn(i); } catch (Exception e) { // autosize depends on AWT stuff and can fail, but it should not be fatal LOG.warn("autoSizeColumn(" + i + ") failed: " + e.getMessage()); } int cw = sheet.getColumnWidth(i); // increase width to accommodate drop-down arrow in the header if (cw / 256 < 20) { sheet.setColumnWidth(i, 256 * 12); } else if (cw / 256 > 120) { sheet.setColumnWidth(i, 256 * 120); } } }
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 {//from w ww . jav a2 s . 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; }