List of usage examples for org.apache.poi.ss.usermodel Workbook getCreationHelper
CreationHelper getCreationHelper();
From source file:it.greenvulcano.excel.reader.BaseReader.java
License:Open Source License
public void processExcel(InputStream in) throws ExcelException, InterruptedException { cleanUp();/*w ww . ja v a 2 s . c o m*/ Workbook workbook = null; try { // Open the workbook and then create the FormulaEvaluator and // DataFormatter instances that will be needed to, respectively, // force evaluation of formula found in cells and create a // formatted String encapsulating the cells contents. workbook = WorkbookFactory.create(in); evaluator = workbook.getCreationHelper().createFormulaEvaluator(); formatter = new DataFormatter(true); processExcel(workbook); } catch (ExcelException exc) { throw exc; } catch (Exception exc) { ThreadUtils.checkInterrupted(exc); throw new ExcelException("Error parsing WorkBook", exc); } finally { workbook = null; formatter = null; evaluator = null; } }
From source file:javafxapplication7.Main_controller.java
private void exportExcel() throws FileNotFoundException, IOException { setBounds(0, 0, 500, 500);/*from w w w . ja v a 2s .co m*/ JFileChooser dialog = new JFileChooser(); dialog.setFileSelectionMode(DIRECTORIES_ONLY); dialog.showOpenDialog(this); File file = dialog.getSelectedFile(); setVisible(true); System.out.println(file); if (file != null) { setVisible(false); Workbook wb = new HSSFWorkbook(); //Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. for (int i = 0; i < usersData.size(); i++) { Row row = sheet.createRow((short) i); // Or do it on one line. row.createCell(0).setCellValue(createHelper.createRichTextString(String.valueOf("P-41"))); row.createCell(1).setCellValue(true); } // Write the output to a file FileOutputStream fileOut = new FileOutputStream(file + "/Export.xls"); wb.write(fileOut); fileOut.close(); } }
From source file:jdbreport.model.io.xls.poi.Excel2003Writer.java
License:Apache License
private void saveSheet(Workbook wb, ReportModel model, ReportBook reportBook, String reportTitle) throws SaveReportException { CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet(reportTitle); sheet.setDisplayGridlines(reportBook.isShowGrid()); sheet.setPrintGridlines(false);//from ww w. j a va 2 s . c om sheet.setFitToPage(model.isStretchPage()); sheet.setDisplayRowColHeadings(model.isShowHeader() || model.isShowRowHeader()); ReportPage rp = model.getReportPage(); sheet.setMargin(Sheet.TopMargin, rp.getTopMargin(Units.INCH)); sheet.setMargin(Sheet.BottomMargin, rp.getBottomMargin(Units.INCH)); sheet.setMargin(Sheet.LeftMargin, rp.getLeftMargin(Units.INCH)); sheet.setMargin(Sheet.RightMargin, rp.getRightMargin(Units.INCH)); sheet.getPrintSetup().setLandscape(rp.getOrientation() == ReportPage.LANDSCAPE); short paperSize = convertPaperSize(rp.getPaperSize()); if (paperSize > 0) { sheet.getPrintSetup().setPaperSize(paperSize); } TableColumnModel cm = model.getColumnModel(); for (int c = 0; c < model.getColumnCount(); c++) { if (model.isColumnBreak(c)) { sheet.setColumnBreak(c); } //char width in points float char_width = 5.5f; sheet.setColumnWidth(c, (int) ((((ReportColumn) cm.getColumn(c)).getNativeWidth() - 2) / char_width * 256)); } fillStyles(wb, reportBook); createRows(model, sheet); drawing = sheet.createDrawingPatriarch(); for (int row = 0; row < model.getRowCount(); row++) { saveRow(wb, sheet, reportBook, model, row, createHelper); } drawing = null; }
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; String extension = FileUtils.getFileExtension(file.getAbsolutePath()); if (extension.equals("xlsx")) { wb = new XSSFWorkbook(); } else {/*from ww w .j a v a2 s . com*/ 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:magicware.scm.redmine.tools.IssueSyncApp.java
License:Apache License
public void execute(SyncItem syncItem) throws IOException, InvalidFormatException { FileInputStream in = null;//from w ww. j a v a 2s .c o m try { // ?JSON?? String issueTemplate = FileUtils.readFileAsString(syncItem.getJsonTemplate()); // ??? Matcher m = Pattern.compile(Constants.ISSUE_FIELD_VALUE_EXP).matcher(issueTemplate); List<MatchResult> mrList = new ArrayList<MatchResult>(); while (m.find()) { MatchResult mr = m.toMatchResult(); mrList.add(mr); } // ???? in = new FileInputStream(syncItem.getFilePath()); Workbook wb = WorkbookFactory.create(in); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); Sheet sheet = wb.getSheet(syncItem.getSheetName()); Row row = null; Cell cell = null; List<String> issues = new ArrayList<String>(); // ????? for (int i = sheet.getLastRowNum(); i >= (syncItem.getKeyRowBeginIdx() > 0 ? (syncItem.getKeyRowBeginIdx() - 1) : 0); i--) { // ???? row = sheet.getRow(i); if (row != null) { String keyNo = ExcelUtils.getCellContent(row.getCell(syncItem.getKeyColumnIdx() - 1), evaluator); // ?????????? if (StringUtils.isBlank(keyNo)) { break; } // ???? if (redmineClient.queryIssue(syncItem.getProjectId(), syncItem.getKeyFiledId(), keyNo) == 0) { StringBuilder newIssue = new StringBuilder(); int eolIdx = 0; for (MatchResult matchResult : mrList) { newIssue.append(issueTemplate.substring(eolIdx, matchResult.start())); int cellIndex = Integer.valueOf(matchResult.group(1)) - 1; cell = row.getCell(cellIndex); String cellvalue = ExcelUtils.getCellContent(cell, evaluator); // ? String valueMapStr = matchResult.group(3); Map<String, String> valueMap = null; if (valueMapStr != null) { valueMap = JSON.decode(valueMapStr); if (StringUtils.isNotEmpty(cellvalue) && valueMap.containsKey(cellvalue)) { cellvalue = valueMap.get(cellvalue); } else { cellvalue = valueMap.get("default"); } } if (StringUtils.isNotEmpty(cellvalue)) { cellvalue = StringEscapeUtils.escapeJavaScript(cellvalue); newIssue.append(cellvalue); } eolIdx = matchResult.end(); } newIssue.append(issueTemplate.substring(eolIdx)); issues.add(newIssue.toString()); } else { // ??? break; } } } for (int i = issues.size() - 1; i >= 0; i--) { Map<String, Issue> issueMap = JSON.decode(issues.get(i)); log.debug("create new issue >>>"); log.debug(JSON.encode(issueMap, true)); redmineClient.createNewIssue(issues.get(i)); } } finally { if (in != null) { in.close(); in = null; } } }
From source file:midas.sheeco.Sheeco.java
License:Apache License
public <T> List<T> fromSpreadsheet(final InputStream stream, final Class<T> payloadClass) throws SpreadsheetUnmarshallingException, SpreasheetUnmarshallingUnrecoverableException { try {/*from w w w. j av a 2 s. com*/ final Workbook wb = WorkbookFactory.create(stream); final Payload<T> payload = new Payload<>(payloadClass); final Sheet sheet = getSheet(payload.getName(), wb); final FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); final PayloadContext<T> ctx = new PayloadContext<>(sheet, evaluator, payload); final List<T> payloads = readPayloads(ctx); if (ctx.getViolations().isEmpty()) { return payloads; } else { throw new SpreadsheetUnmarshallingException(payloads, ctx.getViolations()); } } catch (final FileNotFoundException e) { throw new SpreasheetUnmarshallingUnrecoverableException( String.format("sheeco.serializer.file.cannot.open")); } catch (final IOException | InvalidFormatException e) { throw new SpreasheetUnmarshallingUnrecoverableException( String.format("sheeco.serializer.file.wrong.format")); } }
From source file:nc.noumea.mairie.appock.services.impl.ExportExcelServiceImpl.java
License:Open Source License
private void insertPhotoArticleCatalogueInCell(Workbook wb, Sheet sheet, ArticleCatalogue articleCatalogue, int ligne, Row row, boolean mouvementStock) throws IOException { File fichierPhotoArticleCatalogue = catalogueService .getFilePieceJointe(articleCatalogue.getPhotoArticleCatalogue()); byte[] bytes = Files.readAllBytes(fichierPhotoArticleCatalogue.toPath()); int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG); Drawing drawing = sheet.createDrawingPatriarch(); CreationHelper helper = wb.getCreationHelper(); ClientAnchor anchor = helper.createClientAnchor(); anchor.setCol1(10);// ww w . jav a 2s. c om anchor.setRow1(ligne); anchor.setCol2(11); anchor.setRow2(ligne + 1); drawing.createPicture(anchor, pictureIdx); construitLigneExportCatalogueGeneric(wb, row, null, 10, mouvementStock); }
From source file:net.cpollet.jixture.fixtures.transformers.ExcelFileFixtureTransformer.java
License:Apache License
@Override protected List<Object> parse(From fixture) { InputStream inputStream = fixture.getInputStream(); Workbook workbook = createWorkbook(inputStream); DataFormatter dataFormatter = new DataFormatter(); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); List<Object> mappings = new LinkedList<Object>(); Helpers helpers = new Helpers(); helpers.dataFormatter = dataFormatter; helpers.evaluator = evaluator;//from w ww . ja v a 2 s. c om Parameters parameters = new Parameters(); parameters.mode = fixture.getMode(); for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) { Sheet sheet = workbook.getSheetAt(sheetIndex); mappings.addAll(parseSheet(sheet, helpers, parameters)); } return mappings; }
From source file:net.sf.ahtutils.report.util.DataUtil.java
public static Object getCellValue(Cell cell) { Object value = new Object(); // Prevent a NullPointerException if (cell != null) { if (cell.getHyperlink() != null) { Workbook workbook = new XSSFWorkbook(); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Hyperlink link = cell.getHyperlink(); String address = link.getAddress(); if (logger.isTraceEnabled()) { logger.trace("Found a Hyperlink to " + cell.getHyperlink().getAddress() + " in cell " + cell.getRowIndex() + "," + cell.getColumnIndex()); }//from w w w. j av a 2s. co m cell = evaluator.evaluateInCell(cell); } // Depending on the cell type, the value is read using Apache POI methods switch (cell.getCellType()) { // String are easy to handle case Cell.CELL_TYPE_STRING: logger.trace("Found string " + cell.getStringCellValue()); value = cell.getStringCellValue(); break; // Since date formatted cells are also of the numeric type, this needs to be processed case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); DateFormat df = SimpleDateFormat.getDateInstance(); logger.trace("Found date " + df.format(date)); value = date; } else { logger.trace("Found general number " + cell.getNumericCellValue()); value = cell.getNumericCellValue(); } break; } } else { logger.trace("Found cell with NULL value"); } return value; }
From source file:net.sourceforge.jaulp.export.excel.poi.ExportExcelUtils.java
License:Apache License
/** * Creates a new CellStyle with the given date format. * * @param workbook/*from www . j a va 2s. c o m*/ * the workbook * @param dateFormat * the date format * @return the cell style */ public static CellStyle newDateCellStyle(Workbook workbook, String dateFormat) { CellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat(dateFormat)); return dateCellStyle; }