List of usage examples for org.apache.poi.ss.usermodel Sheet autoSizeColumn
void autoSizeColumn(int column);
From source file:functions.excels.exports.TemoinsParPeriodeExcel.java
License:Apache License
public TemoinsParPeriodeExcel(Map<String, String> info, List<TemoinsParPeriode> temoins) throws IOException { super();/*w ww . j a v a 2s .co m*/ Sheet sheet = wb.createSheet("Tmoins par priode"); Espece espece = Espece.find.byId(Integer.parseInt(info.get("espece"))); SousGroupe sous_groupe = SousGroupe.find.byId(Integer.parseInt(info.get("sous_groupe"))); Groupe groupe = Groupe.find.byId(Integer.parseInt(info.get("groupe"))); StadeSexe stade_sexe = StadeSexe.find.byId(Integer.parseInt(info.get("stade"))); String maille = info.get("maille"); String date1 = info.get("jour1") + "/" + info.get("mois1") + "/" + info.get("annee1"); String date2 = info.get("jour2") + "/" + info.get("mois2") + "/" + info.get("annee2"); String titre = "Liste des tmoins ayant fait une observation" + crLf; if (espece != null) titre += "de " + espece.espece_nom; else if (sous_groupe != null) titre += "de " + sous_groupe; else if (groupe != null) titre += "de " + groupe; if (stade_sexe != null) titre += " au stade " + stade_sexe; if (!maille.equals("")) titre += " dans la maille " + maille; titre += " du " + date1 + " au " + date2; titre += crLf + " (" + temoins.size() + " tmoin(s) pour " + TemoinsParPeriode.getSomme(temoins) + " tmoignage(s))"; int page = 0; int ligne = 7; this.collerLogoEtTitre(page, titre); Row rowHead = sheet.createRow(ligne); rowHead.createCell(0).setCellValue("Tmoin"); rowHead.createCell(1).setCellValue("Nbre tm."); ligne++; boolean ecritAGauche = true; for (TemoinsParPeriode temoin : temoins) { if (ecritAGauche) { Row row = sheet.createRow(ligne); row.createCell(0).setCellValue(temoin.temoin.toString()); row.createCell(1).setCellValue(temoin.nombreDeTemoignages); ligne++; } else { Row row = sheet.getRow(ligne); row.createCell(3).setCellValue(temoin.temoin.toString()); row.createCell(4).setCellValue(temoin.nombreDeTemoignages); ligne++; } if (ligne % LIGNES == (LIGNES - 2)) { if (ecritAGauche) { ecritAGauche = !ecritAGauche; ligne -= (LIGNES - 9); Row row = sheet.getRow(ligne); row.createCell(3).setCellValue("Tmoin"); row.createCell(4).setCellValue("Nbre tm."); ligne++; } else { ecritAGauche = !ecritAGauche; //On crit le pied de page this.piedDePage(page); //On fait une nouvelle page ligne += 9; page++; this.collerLogoEtTitre(page, titre); Row row = sheet.createRow(ligne); row.createCell(0).setCellValue("Tmoin"); row.createCell(1).setCellValue("Nbre tm."); ligne++; } } } this.piedDePage(page); sheet.setColumnWidth(0, 7937); sheet.autoSizeColumn(1); sheet.setColumnWidth(2, 256); sheet.setColumnWidth(3, 7937); sheet.autoSizeColumn(4); }
From source file:gr.open.loglevelsmanager.loglevel.LogLevelExporter.java
License:Open Source License
public static Workbook generateExcel(long groupId, boolean first, boolean showTypes, LinkedHashMap<String, String> dataHeaders) throws SystemException { Workbook book = new HSSFWorkbook(); Sheet sheet = book.createSheet(); int itRow = 0; int itCell = 0; Row row = null;/*from ww w . j ava 2 s .co m*/ Cell cell = null; row = sheet.createRow(itRow); itRow++; if (showTypes) { Row row2 = sheet.createRow(itRow); itRow++; Cell cell2 = null; for (String key : dataHeaders.keySet()) { // Head cell = row.createCell(itCell); cell.setCellValue(key); // Types cell2 = row2.createCell(itCell); cell2.setCellValue(dataHeaders.get(key)); itCell++; } } else { for (String key : dataHeaders.keySet()) { // Head cell = row.createCell(itCell); cell.setCellValue(key); itCell++; } } List<LogLevel> listEntities = LogLevelLocalServiceUtil.findAllInGroup(groupId); if (first) { if (listEntities.size() > 0) { row = sheet.createRow(itRow); LogLevel entity = listEntities.get(0); row = generateRow(entity, row); itRow++; } } else { for (LogLevel entity : listEntities) { row = sheet.createRow(itRow); row = generateRow(entity, row); itRow++; } } // Autosize columns for (int x = 0; x < (itCell + 1); x++) { sheet.autoSizeColumn(x); } return book; }
From source file:gt.org.ms.api.utils.ExcelHelper.java
public static <T> void writeMapToExcel(OutputStream out, List<Map<String, Object>> data, List<FieldDto> fields) { if (fields.isEmpty()) { throw ExceptionsManager.newInternalErrorException("fields_config", "Configuracion de campos de excel no es valida!", null); }// ww w. j av a2 s. co m HSSFWorkbook workbook = null; try { workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet(); int rowCount = 0; int columnCount = 0; Row row = sheet.createRow(rowCount++); Collections.sort(fields); for (FieldDto field : fields) { Cell cell = row.createCell(columnCount++); cell.setCellValue(field.getTitle()); } for (Map<String, Object> t : data) { row = sheet.createRow(rowCount++); columnCount = 0; for (FieldDto field : fields) { Cell cell = row.createCell(columnCount); Object value = t.get(field.getName()); System.out.println("writing >> " + value); if (value != null) { if (value instanceof String) { cell.setCellValue((String) value); } else if (value instanceof Long) { cell.setCellValue((Long) value); } else if (value instanceof Integer) { cell.setCellValue((Integer) value); } else if (value instanceof Double) { cell.setCellValue((Double) value); } else { cell.setCellValue(value.toString()); } } sheet.autoSizeColumn(columnCount); columnCount++; } } workbook.write(out); out.flush(); } catch (IOException e) { e.printStackTrace(System.err); throw ExceptionsManager.newInternalErrorException("excel_exporter", "Error generando archivo de excel", e); } }
From source file:io.vulpine.lib.kalo.Kalo.java
License:Apache License
public <T> Workbook poi(final Collection<T> items, final Class<T> type, final String sheetName, final Workbook workbook) { final Sheet sheet = workbook.createSheet(sheetName); final PropertyAggregator map = ClassUtils.parse(type); final Collection<Imu> sorted = sortColumns(map.columns()); int row = 1;//from ww w .j ava 2s.com WorkbookUtil.parseHeaders(sheet, map, sorted, new PoiConfig().getHeaderStyle(workbook, null)); for (final T item : items) { final Row sheetRow = sheet.createRow(row++); int col = 0; for (final Imu imu : sorted) { final Cell cell = sheetRow.createCell(col++); final CellType cType = PoiUtil.translateType(imu); cell.setCellType(cType); switch (cType) { case BOOLEAN: cell.setCellValue((Boolean) imu.getValue(item)); break; case NUMERIC: cell.setCellValue(((Number) imu.getValue(item)).doubleValue()); break; default: cell.setCellValue(String.valueOf(imu.getValue(item))); } } } for (int i = 0; i < sorted.size(); i++) sheet.autoSizeColumn(i); return workbook; }
From source file:it.eng.spagobi.engines.console.exporter.types.ExporterExcel.java
License:Mozilla Public License
private void adjustToColumnContent(Sheet sheet, int first, int last) { for (int i = first; i <= last; i++) { sheet.autoSizeColumn(i); }//ww w.j ava2 s . com }
From source file:it.unitn.elisco.utils.Utilities.java
public static Workbook getExcelFromQuestionList(String workbookName, List<Question> questions) { // Create EXCEL File (Workbook with sheets) Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet(workbookName); // Create styles for cells CellStyle questionStyle = workbook.createCellStyle(); questionStyle.setWrapText(true);/*from w w w . j a v a2 s. co m*/ questionStyle.setVerticalAlignment(VerticalAlignment.CENTER); CellStyle othersStyle = workbook.createCellStyle(); othersStyle.setAlignment(HorizontalAlignment.CENTER); othersStyle.setVerticalAlignment(VerticalAlignment.CENTER); // Create header row Row headerRow = sheet.createRow(0); headerRow.createCell(0).setCellValue("ID"); headerRow.createCell(1).setCellValue("TAG"); headerRow.createCell(2).setCellValue("DOMANDA"); headerRow.createCell(3).setCellValue("APRROVATA (SI/NO)"); headerRow.getCell(0).setCellStyle(othersStyle); headerRow.getCell(1).setCellStyle(othersStyle); headerRow.getCell(2).setCellStyle(othersStyle); headerRow.getCell(3).setCellStyle(othersStyle); int rownum = 1; for (Question question : questions) { // Create a row Row row = sheet.createRow(rownum++); // Create cells for id and question and set their values row.createCell(0).setCellValue(question.getId()); row.createCell(1).setCellValue(question.getTag()); row.createCell(2).setCellValue(question.getBody()); // Create empty cell for admin input row.createCell(3); // Set cell styles row.getCell(0).setCellStyle(othersStyle); row.getCell(1).setCellStyle(othersStyle); row.getCell(2).setCellStyle(questionStyle); row.getCell(3).setCellStyle(othersStyle); } sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); sheet.setColumnWidth(2, 100 * 256); // Set questionStyle column width to 100 characters sheet.autoSizeColumn(3); return workbook; }
From source file:javafxapplication12.FXMLDocumentController.java
public void parse(File file, ArrayList<Check> list) throws FileNotFoundException, IOException { Workbook book = new HSSFWorkbook(); Sheet sheet = book.createSheet("new Sheet"); int i = 0;/*from w w w . j a v a 2s . co m*/ while (i != list.size()) { Row row = sheet.createRow(i); row.createCell(0).setCellValue(list.get(i).getId()); row.createCell(1).setCellValue(list.get(i).getCondition()); row.createCell(2).setCellValue(list.get(i).getResult()); row.createCell(3).setCellValue(list.get(i).getDate().toString()); row.createCell(4).setCellValue(list.get(i).getResponsible()); i++; } sheet.autoSizeColumn(1); FileOutputStream out = new FileOutputStream(file); book.write(out); out.close(); }
From source file:jgnash.convert.exportantur.ssf.AccountExport.java
License:Open Source License
public static void exportAccount(final Account account, final String[] columnNames, final LocalDate startDate, final LocalDate endDate, final File file) { Objects.requireNonNull(account); Objects.requireNonNull(startDate); Objects.requireNonNull(endDate); Objects.requireNonNull(file); Objects.requireNonNull(columnNames); final String extension = FileUtils.getFileExtension(file.getAbsolutePath()); try (final Workbook wb = extension.equals("xlsx") ? new XSSFWorkbook() : new HSSFWorkbook()) { final CreationHelper createHelper = wb.getCreationHelper(); // create a new sheet final Sheet s = wb.createSheet(account.getName()); // create 2 fonts objects final Font defaultFont = wb.createFont(); final Font headerFont = wb.createFont(); defaultFont.setFontHeightInPoints((short) 10); defaultFont.setColor(IndexedColors.BLACK.getIndex()); headerFont.setFontHeightInPoints((short) 11); headerFont.setColor(IndexedColors.BLACK.getIndex()); headerFont.setBold(true);/*ww w .j a v a 2 s. co m*/ // create header cell styles final CellStyle headerStyle = wb.createCellStyle(); // Set the other cell style and formatting headerStyle.setBorderBottom(BorderStyle.THIN); headerStyle.setBorderTop(BorderStyle.THIN); headerStyle.setBorderLeft(BorderStyle.THIN); headerStyle.setBorderRight(BorderStyle.THIN); headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); DataFormat df_header = wb.createDataFormat(); headerStyle.setDataFormat(df_header.getFormat("text")); headerStyle.setFont(headerFont); headerStyle.setAlignment(HorizontalAlignment.CENTER); final CellStyle dateStyle = wb.createCellStyle(); dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm/dd/yy")); dateStyle.setFont(defaultFont); final CellStyle timestampStyle = wb.createCellStyle(); timestampStyle.setDataFormat(createHelper.createDataFormat().getFormat("YYYY-MM-DD HH:MM:SS")); timestampStyle.setFont(defaultFont); final CellStyle textStyle = wb.createCellStyle(); textStyle.setFont(defaultFont); final CellStyle amountStyle = wb.createCellStyle(); amountStyle.setFont(defaultFont); amountStyle.setAlignment(HorizontalAlignment.RIGHT); final DecimalFormat format = (DecimalFormat) NumericFormats .getFullCommodityFormat(account.getCurrencyNode()); final String pattern = format.toLocalizedPattern().replace("", account.getCurrencyNode().getPrefix()); final DataFormat df = wb.createDataFormat(); amountStyle.setDataFormat(df.getFormat(pattern)); // Create headers int row = 0; Row r = s.createRow(row); for (int i = 0; i < columnNames.length; i++) { Cell c = r.createCell(i); c.setCellValue(createHelper.createRichTextString(columnNames[i])); c.setCellStyle(headerStyle); } // Dump the transactions for (final Transaction transaction : account.getTransactions(startDate, endDate)) { r = s.createRow(++row); int col = 0; // date Cell c = r.createCell(col, CellType.STRING); c.setCellValue(DateUtils.asDate(transaction.getLocalDate())); c.setCellStyle(dateStyle); // timestamp c = r.createCell(++col, CellType.STRING); c.setCellValue(DateUtils.asDate(transaction.getTimestamp())); c.setCellStyle(timestampStyle); // number c = r.createCell(++col, CellType.STRING); c.setCellValue(transaction.getNumber()); c.setCellStyle(textStyle); // payee c = r.createCell(++col, CellType.STRING); c.setCellValue(transaction.getPayee()); c.setCellStyle(textStyle); // memo c = r.createCell(++col, CellType.STRING); c.setCellValue(transaction.getMemo()); c.setCellStyle(textStyle); // account c = r.createCell(++col, CellType.STRING); c.setCellValue(getAccountColumnValue(transaction, account)); c.setCellStyle(textStyle); // clr, strip any zero width spaces c = r.createCell(++col, CellType.STRING); c.setCellValue(transaction.getReconciled(account).toString().replaceAll(ZERO_WIDTH_SPACE, "")); c.setCellStyle(textStyle); final BigDecimal amount = transaction.getAmount(account); // increase c = r.createCell(++col, CellType.NUMERIC); if (amount.signum() >= 0) { c.setCellValue(amount.doubleValue()); } c.setCellStyle(amountStyle); // decrease c = r.createCell(++col, CellType.NUMERIC); if (amount.signum() < 0) { c.setCellValue(amount.abs().doubleValue()); } c.setCellStyle(amountStyle); // balance c = r.createCell(++col, CellType.NUMERIC); c.setCellValue(account.getBalanceAt(transaction).doubleValue()); c.setCellStyle(amountStyle); } // autosize the column widths final short columnCount = s.getRow(1).getLastCellNum(); // autosize all of the columns + 10 pixels for (int i = 0; i <= columnCount; i++) { s.autoSizeColumn(i); s.setColumnWidth(i, s.getColumnWidth(i) + 10); } Logger.getLogger(AccountExport.class.getName()).log(Level.INFO, "{0} cell styles were used", wb.getNumCellStyles()); // Save final String filename; if (wb instanceof XSSFWorkbook) { filename = FileUtils.stripFileExtension(file.getAbsolutePath()) + ".xlsx"; } else { filename = FileUtils.stripFileExtension(file.getAbsolutePath()) + ".xls"; } try (final OutputStream out = Files.newOutputStream(Paths.get(filename))) { wb.write(out); } catch (final Exception e) { Logger.getLogger(AccountExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e); } } catch (final IOException e) { Logger.getLogger(AccountExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e); } }
From source file:jgnash.convert.exports.ssf.AccountExport.java
License:Open Source License
public static void exportAccount(final Account account, final String[] columnNames, final LocalDate startDate, final LocalDate endDate, final File file) { Objects.requireNonNull(account); Objects.requireNonNull(startDate); Objects.requireNonNull(endDate); Objects.requireNonNull(file); Objects.requireNonNull(columnNames); final String extension = FileUtils.getFileExtension(file.getAbsolutePath()); try (final Workbook wb = extension.equals("xlsx") ? new XSSFWorkbook() : new HSSFWorkbook()) { final CreationHelper createHelper = wb.getCreationHelper(); // create a new sheet final Sheet s = wb.createSheet(account.getName()); // create 2 fonts objects final Font defaultFont = wb.createFont(); final Font headerFont = wb.createFont(); defaultFont.setFontHeightInPoints((short) 10); defaultFont.setColor(IndexedColors.BLACK.getIndex()); headerFont.setFontHeightInPoints((short) 11); headerFont.setColor(IndexedColors.BLACK.getIndex()); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); // create header cell styles final CellStyle headerStyle = wb.createCellStyle(); // Set the other cell style and formatting headerStyle.setBorderBottom(CellStyle.BORDER_THIN); headerStyle.setBorderTop(CellStyle.BORDER_THIN); headerStyle.setBorderLeft(CellStyle.BORDER_THIN); headerStyle.setBorderRight(CellStyle.BORDER_THIN); headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); DataFormat df_header = wb.createDataFormat(); headerStyle.setDataFormat(df_header.getFormat("text")); headerStyle.setFont(headerFont); headerStyle.setAlignment(CellStyle.ALIGN_CENTER); final CellStyle dateStyle = wb.createCellStyle(); dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm/dd/yy")); dateStyle.setFont(defaultFont);/*from w w w .ja va 2s . c o m*/ final CellStyle textStyle = wb.createCellStyle(); textStyle.setFont(defaultFont); final CellStyle amountStyle = wb.createCellStyle(); amountStyle.setFont(defaultFont); amountStyle.setAlignment(CellStyle.ALIGN_RIGHT); final DecimalFormat format = (DecimalFormat) CommodityFormat .getFullNumberFormat(account.getCurrencyNode()); final String pattern = format.toLocalizedPattern().replace("", account.getCurrencyNode().getPrefix()); final DataFormat df = wb.createDataFormat(); amountStyle.setDataFormat(df.getFormat(pattern)); // Create headers int row = 0; Row r = s.createRow(row); for (int i = 0; i < columnNames.length; i++) { Cell c = r.createCell(i); c.setCellValue(createHelper.createRichTextString(columnNames[i])); c.setCellStyle(headerStyle); } // Dump the transactions for (final Transaction transaction : account.getTransactions(startDate, endDate)) { r = s.createRow(++row); int col = 0; // date Cell c = r.createCell(col); c.setCellType(Cell.CELL_TYPE_STRING); c.setCellValue(DateUtils.asDate(transaction.getLocalDate())); c.setCellStyle(dateStyle); // number c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_STRING); c.setCellValue(transaction.getNumber()); c.setCellStyle(textStyle); // payee c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_STRING); c.setCellValue(transaction.getPayee()); c.setCellStyle(textStyle); // memo c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_STRING); c.setCellValue(transaction.getMemo()); c.setCellStyle(textStyle); // account c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_STRING); c.setCellValue(getAccountColumnValue(transaction, account)); c.setCellStyle(textStyle); // clr c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_STRING); c.setCellValue(transaction.getReconciled(account).toString()); c.setCellStyle(textStyle); final BigDecimal amount = transaction.getAmount(account); // increase c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_NUMERIC); if (amount.signum() >= 0) { c.setCellValue(amount.doubleValue()); } c.setCellStyle(amountStyle); // decrease c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_NUMERIC); if (amount.signum() < 0) { c.setCellValue(amount.abs().doubleValue()); } c.setCellStyle(amountStyle); // balance c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_NUMERIC); c.setCellValue(account.getBalanceAt(transaction).doubleValue()); c.setCellStyle(amountStyle); } // autosize the column widths final short columnCount = s.getRow(1).getLastCellNum(); // autosize all of the columns + 10 pixels for (int i = 0; i <= columnCount; i++) { s.autoSizeColumn(i); s.setColumnWidth(i, s.getColumnWidth(i) + 10); } Logger.getLogger(AccountExport.class.getName()).log(Level.INFO, "{0} cell styles were used", wb.getNumCellStyles()); // Save final String filename; if (wb instanceof XSSFWorkbook) { filename = FileUtils.stripFileExtension(file.getAbsolutePath()) + ".xlsx"; } else { filename = FileUtils.stripFileExtension(file.getAbsolutePath()) + ".xls"; } try (final FileOutputStream out = new FileOutputStream(filename)) { wb.write(out); } catch (final Exception e) { Logger.getLogger(AccountExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e); } } catch (IOException e) { Logger.getLogger(AccountExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e); } }
From source file:jgnash.engine.budget.BudgetResultsExport.java
License:Open Source License
public static void exportBudgetResultsModel(final File file, final BudgetResultsModel model) { Resource rb = Resource.get(); Workbook wb;//from w w w . j a va 2s . c om String extension = FileUtils.getFileExtension(file.getAbsolutePath()); if (extension.equals("xlsx")) { wb = new XSSFWorkbook(); } else { wb = new HSSFWorkbook(); } CreationHelper createHelper = wb.getCreationHelper(); // create a new sheet Sheet s = wb.createSheet(model.getBudget().getName()); // create header cell styles CellStyle headerStyle = wb.createCellStyle(); // create 2 fonts objects Font amountFont = wb.createFont(); Font headerFont = wb.createFont(); amountFont.setFontHeightInPoints((short) 10); amountFont.setColor(IndexedColors.BLACK.getIndex()); headerFont.setFontHeightInPoints((short) 11); headerFont.setColor(IndexedColors.BLACK.getIndex()); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); DataFormat df = wb.createDataFormat(); // Set the other cell style and formatting headerStyle.setBorderBottom(CellStyle.BORDER_THIN); headerStyle.setBorderTop(CellStyle.BORDER_THIN); headerStyle.setBorderLeft(CellStyle.BORDER_THIN); headerStyle.setBorderRight(CellStyle.BORDER_THIN); headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); headerStyle.setDataFormat(df.getFormat("text")); headerStyle.setFont(headerFont); headerStyle.setAlignment(CellStyle.ALIGN_CENTER); int row = 0; Row r = s.createRow(row); // create period headers for (int i = 0; i < model.getDescriptorList().size(); i++) { Cell c = r.createCell(i * 3 + 1); c.setCellValue( createHelper.createRichTextString(model.getDescriptorList().get(i).getPeriodDescription())); c.setCellStyle(headerStyle); s.addMergedRegion(new CellRangeAddress(row, row, i * 3 + 1, i * 3 + 3)); } { int col = model.getDescriptorList().size() * 3 + 1; Cell c = r.createCell(col); c.setCellValue(createHelper.createRichTextString(rb.getString("Title.Summary"))); c.setCellStyle(headerStyle); s.addMergedRegion(new CellRangeAddress(row, row, col, col + 2)); } // create results header columns row++; r = s.createRow(row); { Cell c = r.createCell(0); c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Account"))); c.setCellStyle(headerStyle); for (int i = 0; i <= model.getDescriptorList().size(); i++) { c = r.createCell(i * 3 + 1); c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Budgeted"))); c.setCellStyle(headerStyle); c = r.createCell(i * 3 + 2); c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Change"))); c.setCellStyle(headerStyle); c = r.createCell(i * 3 + 3); c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Remaining"))); c.setCellStyle(headerStyle); } } // must sort the accounts, otherwise child structure is not correct List<Account> accounts = new ArrayList<>(model.getAccounts()); Collections.sort(accounts); // create account rows for (Account account : accounts) { CellStyle amountStyle = wb.createCellStyle(); amountStyle.setFont(amountFont); DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(account.getCurrencyNode()); String pattern = format.toLocalizedPattern().replace("", account.getCurrencyNode().getPrefix()); amountStyle.setDataFormat(df.getFormat(pattern)); row++; int col = 0; r = s.createRow(row); CellStyle cs = wb.createCellStyle(); cs.cloneStyleFrom(headerStyle); cs.setAlignment(CellStyle.ALIGN_LEFT); cs.setIndention((short) (model.getDepth(account) * 2)); Cell c = r.createCell(col); c.setCellValue(createHelper.createRichTextString(account.getName())); c.setCellStyle(cs); List<CellReference> budgetedRefList = new ArrayList<>(); List<CellReference> changeRefList = new ArrayList<>(); List<CellReference> remainingRefList = new ArrayList<>(); for (int i = 0; i < model.getDescriptorList().size(); i++) { BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), account); c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_NUMERIC); c.setCellValue(results.getBudgeted().doubleValue()); c.setCellStyle(amountStyle); CellReference budgetedRef = new CellReference(row, col); budgetedRefList.add(budgetedRef); c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_NUMERIC); c.setCellValue(results.getChange().doubleValue()); c.setCellStyle(amountStyle); CellReference changeRef = new CellReference(row, col); changeRefList.add(changeRef); c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_FORMULA); c.setCellStyle(amountStyle); c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString()); CellReference remainingRef = new CellReference(row, col); remainingRefList.add(remainingRef); } // add summary columns addSummaryCell(r, ++col, budgetedRefList, amountStyle); addSummaryCell(r, ++col, changeRefList, amountStyle); addSummaryCell(r, ++col, remainingRefList, amountStyle); } // add group summary rows for (AccountGroup group : model.getAccountGroupList()) { CellStyle amountStyle = wb.createCellStyle(); amountStyle.setFont(amountFont); amountStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); amountStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); amountStyle.setBorderBottom(CellStyle.BORDER_THIN); amountStyle.setBorderTop(CellStyle.BORDER_THIN); amountStyle.setBorderLeft(CellStyle.BORDER_THIN); amountStyle.setBorderRight(CellStyle.BORDER_THIN); DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(model.getBaseCurrency()); String pattern = format.toLocalizedPattern().replace("", model.getBaseCurrency().getPrefix()); amountStyle.setDataFormat(df.getFormat(pattern)); row++; int col = 0; r = s.createRow(row); CellStyle cs = wb.createCellStyle(); cs.cloneStyleFrom(headerStyle); cs.setAlignment(CellStyle.ALIGN_LEFT); Cell c = r.createCell(col); c.setCellValue(createHelper.createRichTextString(group.toString())); c.setCellStyle(cs); List<CellReference> budgetedRefList = new ArrayList<>(); List<CellReference> changeRefList = new ArrayList<>(); List<CellReference> remainingRefList = new ArrayList<>(); for (int i = 0; i < model.getDescriptorList().size(); i++) { BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), group); c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_NUMERIC); c.setCellValue(results.getBudgeted().doubleValue()); c.setCellStyle(amountStyle); CellReference budgetedRef = new CellReference(row, col); budgetedRefList.add(budgetedRef); c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_NUMERIC); c.setCellValue(results.getChange().doubleValue()); c.setCellStyle(amountStyle); CellReference changeRef = new CellReference(row, col); changeRefList.add(changeRef); c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_FORMULA); c.setCellStyle(amountStyle); c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString()); CellReference remainingRef = new CellReference(row, col); remainingRefList.add(remainingRef); } // add summary columns addSummaryCell(r, ++col, budgetedRefList, amountStyle); addSummaryCell(r, ++col, changeRefList, amountStyle); addSummaryCell(r, ++col, remainingRefList, amountStyle); } // force evaluation FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateAll(); short columnCount = s.getRow(1).getLastCellNum(); // autosize all of the columns + 10 pixels for (int i = 0; i <= columnCount; i++) { s.autoSizeColumn(i); s.setColumnWidth(i, s.getColumnWidth(i) + 10); } // Save String filename = file.getAbsolutePath(); if (wb instanceof XSSFWorkbook) { filename = FileUtils.stripFileExtension(filename) + ".xlsx"; } else { filename = FileUtils.stripFileExtension(filename) + ".xls"; } try (FileOutputStream out = new FileOutputStream(filename)) { wb.write(out); } catch (Exception e) { Logger.getLogger(BudgetResultsExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e); } }