List of usage examples for org.apache.poi.ss.usermodel CreationHelper createRichTextString
RichTextString createRichTextString(String text);
From source file:jdbreport.model.io.xls.poi.Excel2003Writer.java
License:Apache License
private RichTextString createRichTextFromContent(List<Content> contentList, CreationHelper createHelper, Workbook wb, short fontIndex) { StringBuilder text = new StringBuilder(); int[] idx = new int[contentList.size()]; int i = 0;//from w w w . jav a 2s . c o m for (Content content : contentList) { idx[i++] = text.length(); text.append(content.getText()); } RichTextString richText = createHelper.createRichTextString(text.toString()); richText.applyFont(fontIndex); for (int n = 0; n < contentList.size(); n++) { Content content = contentList.get(n); Font font = getFont(fontIndex, content.getAttributeSet(), wb); if (font != null) { int end = (n < idx.length - 1) ? idx[n + 1] : text.length(); richText.applyFont(idx[n], end, font); } } return richText; }
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);//from ww w .ja v a2s.c o 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 ww w.java 2 s. 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 ww . ja va 2 s. c o m 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); } }
From source file:lucee.runtime.poi.Excel.java
License:Open Source License
public void setValue(int rowNumber, int columnNumber, String value) throws CasterException { if (value == null) value = ""; Sheet sheet = workbook.getSheet(getExcelSheetName()); // get Row//from w w w . j a v a 2s . c o m Row row = sheet.getRow(rowNumber); if (row == null) row = sheet.createRow(rowNumber); // get Cell Cell cell = row.getCell(columnNumber); CellStyle style = null; if (cell != null) { style = cell.getCellStyle(); row.removeCell(cell); } cell = row.createCell(columnNumber); if (style != null) cell.setCellStyle(style); CreationHelper createHelper = workbook.getCreationHelper(); boolean isFormula = style != null && style.getDataFormatString().equals("@"); if (!isFormula && Decision.isNumeric(value)) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); double dbl = Caster.toDoubleValue(value); cell.setCellValue(dbl); _expandColumnWidth(sheet, Caster.toString(dbl), columnNumber); } else if (StringUtil.isEmpty("")) { cell.setCellType(Cell.CELL_TYPE_BLANK); cell.setCellValue(createHelper.createRichTextString("")); } else { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(createHelper.createRichTextString(value)); _expandColumnWidth(sheet, value, columnNumber); } }
From source file:monthly.organizer.MonthlyOrganizer.java
@Override public void start(Stage primaryStage) { wbo = new XSSFWorkbook(); try {// ww w. j a v a 2s . c om Button merge = new Button("merge"); Button folder = new Button("Choose Folder"); Button file = new Button("Choose Save File"); Label fileLoc = new Label("Not Selected"); Label folderLoc = new Label("Not Selected"); folder.setOnAction(new EventHandler<ActionEvent>() { @Override public void handle(ActionEvent arg0) { DirectoryChooser folderChooser = new DirectoryChooser(); folderChooser.setTitle("Choose Folder Containing Files"); selectedFolder = folderChooser.showDialog(primaryStage); //folderLoc.setText(selectedFolder.getName()); if (null == selectedFolder) folderLoc.setText("No Folder Selected"); else folderLoc.setText(selectedFolder.getName()); } }); file.setOnAction(new EventHandler<ActionEvent>() { @SuppressWarnings("deprecation") @Override public void handle(ActionEvent arg0) { FileChooser fileSaver = new FileChooser(); fileSaver.setTitle("Create Save File"); fileSaver.getExtensionFilters().addAll(new ExtensionFilter( "ExceBeaconController bc = new BeaconController();ll Files", "*.xlsx")); outputFile = fileSaver.showSaveDialog(primaryStage); //outputFile = new File("/home/camen/Desktop" + "/Final_Books" + LocalDate.now() + ".xlsx"); if (null == outputFile) fileLoc.setText("No File Selected"); else fileLoc.setText(outputFile.getName()); } }); merge.setOnAction(new EventHandler<ActionEvent>() { @Override public void handle(ActionEvent arg0) { try { outputStream = new FileOutputStream(outputFile); } catch (FileNotFoundException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } try ( Stream<Path> paths = Files.walk(Paths.get(selectedFolder.getPath()))) { paths.forEach(filePath -> { if (Files.isRegularFile(filePath) && !filePath.toString().equals(selectedFolder.toPath().toString())) { //System.out.println(filePath); fileNum++; FileInputStream inputStream; try { inputStream = new FileInputStream(filePath.toString()); wb = new XSSFWorkbook(inputStream); CreationHelper createHelper = wbo.getCreationHelper(); Sheet sheetOut = wbo.createSheet(filePath.getFileName().toString()); //add client name to beacon controller clients.add(new Client(filePath.getFileName().toString())); clientNum++; //System.out.println(clientNum + ""); Row rowOut = sheetOut.createRow(0); Cell cellOut; cellOut = rowOut.createCell(0); cellOut.setCellValue(createHelper.createRichTextString("Week")); cellOut = rowOut.createCell(1); cellOut.setCellValue(createHelper.createRichTextString("Behavior/Decel")); cellOut = rowOut.createCell(2); cellOut.setCellValue(createHelper.createRichTextString("Data Input Total")); cellOut = rowOut.createCell(3); cellOut.setCellValue(createHelper.createRichTextString("Measurment Type")); cellOut = rowOut.createCell(4); cellOut.setCellValue(createHelper.createRichTextString("Measurment Unit")); cellOut = rowOut.createCell(5); cellOut.setCellValue( createHelper.createRichTextString("Total Time for Week (Mins)")); int rowCount = 1; for (int k = 0; k < wb.getNumberOfSheets(); k++) { Sheet sheet = wb.getSheetAt(k); double weekTotal = 0; double timeTotal = 0; boolean replace = false; String decelName = null; String replaceName = null; Random rand = null; int n = 0; String measType = null; String measUnit = null; String replacedName = null; String replacedNameOld = null; LocalDateTime date = null; int weekNum = -1; int lastWeek = -1; int year = 0; int rows = sheet.getPhysicalNumberOfRows(); //System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows //+ " row(s)."); for (int r = 0; r < rows; r++) { Row row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); //System.out.println("\nROW " + row.getRowNum() + " has " + cells //+ " cell(s)."); for (int c = 0; c < cells; c++) { Cell cell = row.getCell(c); if (cell == null) { //c++; cells++; } else { switch (cell.getCellTypeEnum()) { case STRING: if (r == 0 && cell.getRichStringCellValue().getString() .contains("Replacement")) replace = true; if (r == 0 && replace == true && c == 1) { replaceName = cell.getRichStringCellValue().getString(); rand = new Random(); n = rand.nextInt(20) + 1; } else if (r == 0 && replace == false && c == 1) decelName = cell.getRichStringCellValue().getString(); else if (r == 0 && replace == true && c == 2) replacedName = cell.getRichStringCellValue() .getString(); if (r == 1 && c == 0) measType = cell.getRichStringCellValue().getString(); if (r == 1 && c == 1) measUnit = cell.getRichStringCellValue().getString(); //System.out.println(cell.getRichStringCellValue().getString()); break; case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { date = cell.getDateCellValue().toInstant() .atZone(ZoneId.systemDefault()).toLocalDate() .atTime(LocalTime.NOON); year = date.getYear(); weekNum = date.get(IsoFields.WEEK_OF_WEEK_BASED_YEAR); if (lastWeek == -1) lastWeek = weekNum; c++; cell = row.getCell(c); weekTotal += cell.getNumericCellValue(); c++; //cell = row.getCell(c); //timeTotal += cell.getNumericCellValue(); } else { System.out.println(cell.getNumericCellValue()); } break; case BOOLEAN: //System.out.println(cell.getBooleanCellValue()); break; case FORMULA: //System.out.println(cell.getCellFormula()); break; case BLANK: //System.out.println(); break; default: //System.out.println(); } //System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" //+ value); } } //System.out.println(lastWeek + " " + weekNum + " " + weekTotal); if (lastWeek != weekNum && lastWeek != -1) { //weekCount ++; rowOut = sheetOut.createRow(rowCount); Cell cell = rowOut.createCell(0); cell.setCellValue(createHelper.createRichTextString(date.getMonth() + ", Week " + weekNum + " of Year: " + date.getYear())); //create random number to choose behavior cell = rowOut.createCell(1); if (replace == true) { cell.setCellValue( createHelper.createRichTextString(replaceName)); //client.addValue((((date.getYear() - 2014)*53) + weekNum), weekTotal, Arrays.binarySearch(Client.behaviors, replaceName)); //addvalue to random behavior chosen for the week clients.get(clientNum).addValue( (((date.getYear() - 2014) * 53) + weekNum), weekTotal, n); //System.out.println((((date.getYear() - 2014)*53) + weekNum) + " " + weekTotal + " " + Client.behaviors[n] + " " + clients.get(clientNum).getName()); } else cell.setCellValue(createHelper.createRichTextString(decelName)); cell = rowOut.createCell(2); cell.setCellValue(weekTotal); cell = rowOut.createCell(3); cell.setCellValue(createHelper.createRichTextString(measType)); cell = rowOut.createCell(4); cell.setCellValue(createHelper.createRichTextString(measUnit)); //System.out.println(months[wR[weekNum].month-1] + " week " + (wR[weekNum].monthWeek) + ", " + (year+1900) + ": " + weekTotal + " Measurment Type: " + measType + " Measument Unit: " + measUnit); weekTotal = 0; lastWeek = weekNum; //newWeek = false; rowCount++; } } //if(replace == true) //System.out.println(replaceName + " for " + replacedName); //else //System.out.println(decelName); } //controller.addClient(clients.get(clientNum)); //controller.getClients().forEach((names) -> { //System.out.println(names.getName()); //}); wb.close(); inputStream.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } }); wbo.write(outputStream); wbo.close(); outputStream.close(); final FXMLLoader loader = new FXMLLoader(getClass().getResource("beacon.fxml")); final Stage stage = new Stage(StageStyle.DECORATED); stage.setScene(new Scene((Pane) loader.load())); final BeaconController controller = loader.<BeaconController>getController(); controller.addClients(clients); controller.setFields(true); stage.showAndWait(); //getHostServices().showDocument(outputFile.toURI().toURL().toExternalForm()); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } }); VBox root = new VBox(); root.setId("pane"); folder.setId("button"); file.setId("button"); merge.setId("button"); root.getStylesheets().addAll(this.getClass().getResource("buttonStyle.css").toExternalForm()); root.setPadding(new Insets(180, 200, 30, 200)); root.setAlignment(Pos.BASELINE_CENTER); root.getChildren().addAll(folder, folderLoc, file, fileLoc, merge); root.setSpacing(10); Scene scene = new Scene(root); primaryStage.setScene(scene); primaryStage.show(); } catch (Exception e) { e.printStackTrace(); } }
From source file:net.ceos.project.poi.annotated.core.CellStyleHandler.java
License:Apache License
/** * Apply the cell comment to a cell.//from w ww . ja va 2 s .co m * * @param configCriteria * the {@link XConfigCriteria} object * @param isAuthorizedComment * the extension file * @param cell * the {@link Cell} */ protected static void applyComment(final XConfigCriteria configCriteria, final Boolean isAuthorizedComment, final Cell cell) { if (StringUtils.isBlank(configCriteria.getElement().commentRules()) || StringUtils.isNotBlank(configCriteria.getElement().commentRules()) && isAuthorizedComment) { if (ExtensionFileType.XLS.equals(configCriteria.getExtension())) { final Map<Sheet, HSSFPatriarch> drawingPatriarches = new HashMap<>(); CreationHelper createHelper = cell.getSheet().getWorkbook().getCreationHelper(); HSSFSheet sheet = (HSSFSheet) cell.getSheet(); HSSFPatriarch drawingPatriarch = drawingPatriarches.get(sheet); if (drawingPatriarch == null) { drawingPatriarch = sheet.createDrawingPatriarch(); drawingPatriarches.put(sheet, drawingPatriarch); } Comment comment = drawingPatriarch .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5)); comment.setString(createHelper.createRichTextString(configCriteria.getElement().comment())); cell.setCellComment(comment); } else if (ExtensionFileType.XLSX.equals(configCriteria.getExtension())) { CreationHelper factory = configCriteria.getWorkbook().getCreationHelper(); Drawing drawing = cell.getSheet().createDrawingPatriarch(); ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString str = factory.createRichTextString(configCriteria.getElement().comment()); comment.setString(str); cell.setCellComment(comment); } } }
From source file:nl.b3p.viewer.features.ExcelDownloader.java
License:Open Source License
@Override public void init() throws IOException { wb = new XSSFWorkbook(); styles = createStyles(wb);/*from ww w . j a v a2 s . co m*/ sheet = wb.createSheet(fs.getName().toString()); //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(15f); int colNum = 0; Drawing drawing = sheet.createDrawingPatriarch(); CreationHelper factory = wb.getCreationHelper(); // When the comment box is visible, have it show in a 1x3 space ClientAnchor anchor = factory.createClientAnchor(); for (ConfiguredAttribute configuredAttribute : attributes) { if (configuredAttribute.isVisible()) { Cell cell = headerRow.createCell(colNum); String alias = attributeAliases.get(configuredAttribute.getAttributeName()); cell.setCellValue(alias); if (!alias.equals(configuredAttribute.getAttributeName())) { Comment comment = drawing.createCellComment(anchor); RichTextString str = factory.createRichTextString(configuredAttribute.getAttributeName()); comment.setString(str); cell.setCellComment(comment); } cell.setCellStyle(styles.get("header")); sheet.autoSizeColumn(colNum); colNum++; } } //freeze the first row sheet.createFreezePane(0, 1); currentRow = 1; }
From source file:om.edu.squ.squportal.portlet.tsurvey.dao.excel.TeachingSurveyExcelImpl.java
License:Open Source License
/** * //from w ww.j av a 2 s . c om * method name : getExcelSurveyReport * @param object * @param response * @param params * @param locale * @return * @throws DocumentException * @throws IOException * TeachingSurveyExcelImpl * return type : OutputStream * * purpose : Get Streaming excel object for valid/invalid survey report * * Date : Mar 16, 2016 1:23:57 PM */ public OutputStream getExcelSurveyReport(String templateName, Object object, ResourceResponse response, Map<String, String> params, Locale locale) throws DocumentException, IOException { int colHead = 0; int rowNum = 0; String paramStaffRole = params.get(Constants.CONST_ROLE_STAFF); String paramTypeSurvey = params.get(Constants.CONST_PARAM_TYPE_SURVEY); String paramSemesterCode = params.get(Constants.CONST_PARAM_SEMESTER_CODE); String titleRegion = null; Workbook workbook = new HSSFWorkbook(); CreationHelper creationHelper = workbook.getCreationHelper(); Map<String, CellStyle> styles = createStyles(workbook); Sheet sheet = null; Cell cellSH = null; List<ReportSummary> reportSummaries = (List<ReportSummary>) object; if (templateName.equals(Constants.CONST_VALID_SURVEY_REPORT)) { sheet = workbook.createSheet( UtilProperty.getMessage("prop.course.teaching.survey.report.survey.valid", null, locale)); } if (templateName.equals(Constants.CONST_INVALID_SURVEY_REPORT)) { sheet = workbook.createSheet( UtilProperty.getMessage("prop.course.teaching.survey.report.survey.invalid", null, locale)); } sheet.getPrintSetup().setLandscape(true); sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); /** Header Footer **/ Footer footer = sheet.getFooter(); Header header = sheet.getHeader(); footer.setRight("Page &P of &N"); footer.setLeft("&D"); header.setLeft(UtilProperty.getMessage("prop.course.teaching.survey.heading", null, locale)); header.setCenter(UtilProperty.getMessage("prop.course.teaching.survey.title", null, locale)); header.setRight(paramTypeSurvey + " - " + paramSemesterCode); sheet.setRepeatingRows(CellRangeAddress.valueOf("2:2")); sheet.setDisplayGridlines(true); sheet.setPrintGridlines(true); /** Title **/ Row titleRow = sheet.createRow(rowNum); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue(paramTypeSurvey + " - " + paramSemesterCode); titleCell.setCellStyle(styles.get(TITLE)); ++rowNum; titleRegion = "$A$" + rowNum + ":$O$" + rowNum; sheet.addMergedRegion(CellRangeAddress.valueOf(titleRegion)); /** Header Row **/ Row rowSubHeader = sheet.createRow(rowNum++); if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD) && !templateName.equals(Constants.CONST_INVALID_SURVEY_REPORT)) { cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.rank.university", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.rank.college", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.rank.department", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); } cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.instructor.id", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.instructor.name", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.college", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD)) { cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.department", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); } cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.course.code", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.section", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.student.registered", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.response.number", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.analysis.mean", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.analysis.response.percentage", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.analysis.mean", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.analysis.response.percentage", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); /** Report details **/ for (ReportSummary reportSummary : reportSummaries) { int colNum = 0; Row row = sheet.createRow((short) rowNum); if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD) && !templateName.equals(Constants.CONST_INVALID_SURVEY_REPORT)) { row.createCell(colNum++).setCellValue(reportSummary.getUniversityRank()); row.createCell(colNum++).setCellValue(reportSummary.getCollegeRank()); row.createCell(colNum++).setCellValue(reportSummary.getDepartmentRank()); } row.createCell(colNum++).setCellValue(Double.parseDouble(reportSummary.getEmpNumber())); row.createCell(colNum++).setCellValue(creationHelper.createRichTextString(reportSummary.getEmpName())); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(reportSummary.getCollegeCode())); if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD)) { row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(reportSummary.getDepartmentName())); } row.createCell(colNum++).setCellValue(reportSummary.getCourseCode()); row.createCell(colNum++).setCellValue(Integer.parseInt(reportSummary.getSectionNo())); row.createCell(colNum++).setCellValue(reportSummary.getRegisteredStudent()); Cell cellStudentNoResponse = row.createCell(colNum++); cellStudentNoResponse.setCellValue(reportSummary.getStudentNoResponse()); cellStudentNoResponse.setCellStyle(styles.get(FORMULA_1)); Cell cellTeachingMean = row.createCell(colNum++); cellTeachingMean.setCellValue(reportSummary.getTeachingMean()); cellTeachingMean.setCellStyle(styles.get(FORMULA_1)); Cell cellTeachingPercentageFavor = row.createCell(colNum++); cellTeachingPercentageFavor.setCellValue(reportSummary.getTeachingPercentageFavor()); cellTeachingPercentageFavor.setCellStyle(styles.get(FORMULA_1)); Cell cellQuestionMean = row.createCell(colNum++); cellQuestionMean.setCellValue(reportSummary.getQuestionMean()); cellQuestionMean.setCellStyle(styles.get(FORMULA_1)); Cell cellQuestionPercentageFavor = row.createCell(colNum++); cellQuestionPercentageFavor.setCellValue(reportSummary.getQuestionPercentageFavor()); cellQuestionPercentageFavor.setCellStyle(styles.get(FORMULA_1)); rowNum++; } response.setContentType("application/vnd.ms-excel"); OutputStream outputStream = response.getPortletOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); return null; }
From source file:om.edu.squ.squportal.portlet.tsurvey.dao.excel.TeachingSurveyExcelImpl.java
License:Open Source License
/** * // www . j a v a2 s . c om * method name : getExcelCollegeCoursesAsstDean * @param templateName * @param object * @param response * @param params * @param locale * @return * @throws DocumentException * @throws IOException * TeachingSurveyExcelImpl * return type : OutputStream * * purpose : * * Date : Jun 7, 2016 11:49:24 AM */ public OutputStream getExcelCollegeCoursesAsstDean(String templateName, Object object, ResourceResponse response, Map<String, String> params, Locale locale) throws DocumentException, IOException { int colHead = 0; int rowNum = 0; String paramTypeSurvey = params.get(Constants.CONST_PARAM_TYPE_SURVEY); String titleRegion = null; List<StudentResponse> studentResponses = (List<StudentResponse>) object; Workbook workbook = new HSSFWorkbook(); CreationHelper creationHelper = workbook.getCreationHelper(); Map<String, CellStyle> styles = createStyles(workbook); Sheet sheet = null; Cell cellSH = null; sheet = workbook .createSheet(UtilProperty.getMessage("prop.course.teaching.survey.courses.list", null, locale)); sheet.getPrintSetup().setLandscape(true); sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); /** Header Footer **/ Footer footer = sheet.getFooter(); Header header = sheet.getHeader(); footer.setRight("Page &P of &N"); footer.setLeft("&D"); header.setLeft(UtilProperty.getMessage("prop.course.teaching.survey.heading", null, locale)); header.setCenter(UtilProperty.getMessage("prop.course.teaching.survey.title", null, locale)); header.setRight(paramTypeSurvey); sheet.setRepeatingRows(CellRangeAddress.valueOf("2:2")); sheet.setDisplayGridlines(true); sheet.setPrintGridlines(true); /** Title **/ Row titleRow = sheet.createRow(rowNum); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue(paramTypeSurvey); titleCell.setCellStyle(styles.get(TITLE)); ++rowNum; titleRegion = "$A$" + rowNum + ":$O$" + rowNum; sheet.addMergedRegion(CellRangeAddress.valueOf(titleRegion)); /** Header Row **/ Row rowSubHeader = sheet.createRow(rowNum++); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.analysis.department", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper .createRichTextString(UtilProperty.getMessage("prop.course.teaching.survey.course", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.section", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.committee.member.number", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.committee.member.name", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.seats.taken", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.response.students", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.include.exclude", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); /** Report details **/ for (StudentResponse studentResponse : studentResponses) { int colNum = 0; Row row = sheet.createRow((short) rowNum); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getDepartmentName())); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getCourseCode())); row.createCell(colNum++).setCellValue(Integer.parseInt(studentResponse.getSectionNo())); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getEmpNumber())); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getEmpName())); row.createCell(colNum++).setCellValue(studentResponse.getSeatsTaken()); row.createCell(colNum++).setCellValue(studentResponse.getStudentResponse()); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getIncludeExclude())); rowNum++; } response.setContentType("application/vnd.ms-excel"); OutputStream outputStream = response.getPortletOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); return null; }