Example usage for org.apache.poi.ss.usermodel WorkbookFactory create

List of usage examples for org.apache.poi.ss.usermodel WorkbookFactory create

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. WorkbookFactory
  6. create

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel WorkbookFactory create.

Prototype

public static Workbook create(File file) throws IOException, EncryptedDocumentException

Source Link

Document

Creates the appropriate HSSFWorkbook / XSSFWorkbook from the given File, which must exist and be readable.

Usage

From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticmi.read.MiExcelImportProcess.java

License:Open Source License

private Workbook readWorkbook(InputStream in) {
    Workbook result = null;/*from w  w w.j a v a2s .  co m*/
    try {
        result = WorkbookFactory.create(in);
    } catch (InvalidFormatException e) {
        String msg = MessageFormat.format("Error reading excel workbook: InvalidFormatException {0}",
                e.getMessage());
        LOGGER.error(msg);
        throw new IteraplanTechnicalException(IteraplanErrorMessages.GENERAL_TECHNICAL_ERROR, msg);
    } catch (IOException e) {
        String msg = MessageFormat.format("Error reading excel workbook: IOException {0}", e.getMessage());
        LOGGER.error(msg);
        throw new IteraplanTechnicalException(IteraplanErrorMessages.GENERAL_TECHNICAL_ERROR, msg);
    } finally {
        IOUtils.closeQuietly(in);
    }
    return result;
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.ExcelWorkbook.java

License:Open Source License

/**
 * Load a workbook from an InputStream//from   w w  w . j av  a 2 s.c  om
 */
protected void loadWorkbookFromInputStream(InputStream excelWorkbookInputStream) {

    if (excelWorkbookInputStream == null) {
        String msg = "The input stream is null.";
        LOGGER.debug(msg);
        throw new IteraplanTechnicalException(IteraplanErrorMessages.INVALID_EXCEL_TEMPLATE, msg);
    } else {
        LOGGER.debug("Loading Input File");
    }

    try {
        this.wb = WorkbookFactory.create(excelWorkbookInputStream);
    } catch (IOException iex) {
        LOGGER.debug(iex);
        throw new IteraplanTechnicalException(IteraplanErrorMessages.INVALID_EXCEL_TEMPLATE,
                "The Excel file could not be read.", iex);
    } catch (InvalidFormatException fex) {
        LOGGER.debug(fex);
        throw new IteraplanTechnicalException(IteraplanErrorMessages.INVALID_EXCEL_TEMPLATE,
                "Unknown file format.", fex);
    }

    LOGGER.debug("Input file loaded");
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.nettoExport.NettoExcelTransformerTest.java

License:Open Source License

/**
 * Test method for {@link de.iteratec.iteraplan.businesslogic.exchange.nettoExport.NettoExcelTransformer#transform(java.util.List, java.io.OutputStream, de.iteratec.iteraplan.model.TypeOfBuildingBlock)}.
 *///  w w  w  .  j  a v  a2s .co m
@Test
public void testTransform() {
    NettoTransformer inst2007op = NettoExcelTransformer.newInstance(createSimpleOverviewPageTableStructure(),
            NettoExcelTransformer.ExcelVersion.EXCEL_VERSION_2007);
    NettoTransformer inst2003sr = NettoExcelTransformer.newInstance(
            createSimpleSpreadsheetReportTableStructure(),
            NettoExcelTransformer.ExcelVersion.EXCEL_VERSION_2003);

    assertNotNull("Can't create netto transformer for overview page table structure with excel 2007",
            inst2007op);
    assertNotNull("Can't create netto transformer for spreadsheet report table structure with excel 2003",
            inst2003sr);

    List<BuildingBlock> sourceList = new ArrayList<BuildingBlock>();
    String firstInfstrElemName = "Infrastructure Element for UnitTest";
    String lastInfstrElemDesc = "Last comment";
    sourceList.add(testDataHelper.createInfrastructureElement(firstInfstrElemName, "Some comment"));
    sourceList.add(
            testDataHelper.createInfrastructureElement("Another Infrastructure Element", "Some more comment"));
    sourceList.add(testDataHelper.createInfrastructureElement("Yet another Infrastructure Element",
            "Even more comment"));
    sourceList
            .add(testDataHelper.createInfrastructureElement("Last Infrastructure Element", lastInfstrElemDesc));

    ByteArrayOutputStream bufferA = new ByteArrayOutputStream();
    ByteArrayOutputStream bufferB = new ByteArrayOutputStream();
    inst2007op.transform(sourceList, bufferA, TypeOfBuildingBlock.INFRASTRUCTUREELEMENT);
    inst2003sr.transform(sourceList, bufferB, TypeOfBuildingBlock.INFRASTRUCTUREELEMENT);

    InputStream in2007 = new ByteArrayInputStream(bufferA.toByteArray());
    InputStream in2003 = new ByteArrayInputStream(bufferB.toByteArray());

    // Excel version
    try {
        assertTrue("Generated excel file is not version 2007.", POIXMLDocument.hasOOXMLHeader(in2007));
        assertTrue("Generated excel file is not version 2003.", POIFSFileSystem.hasPOIFSHeader(in2003));
    } catch (IOException e1) {
        fail("Can't read excel header from buffers.");
    }

    Workbook workbook2007 = null;
    Workbook workbook2003 = null;
    try {
        workbook2007 = WorkbookFactory.create(in2007);
        workbook2003 = WorkbookFactory.create(in2003);
    } catch (Exception e) {
        fail("Can't open generated excel workbook.");
    }

    assertNotNull("Could not create excel workbook instance from generated output (excel 2007).", workbook2007);
    assertNotNull("Could not create excel workbook instance from generated output (excel 2003).", workbook2003);

    assertSame("Number of sheets is not equal 1  (excel 2007).",
            Integer.valueOf(workbook2007.getNumberOfSheets()), Integer.valueOf(1));
    assertSame("Number of sheets is not equal 1  (excel 2003).",
            Integer.valueOf(workbook2003.getNumberOfSheets()), Integer.valueOf(1));

    String stringCellValue2007 = null;
    String stringCellValue2003 = null;
    String stringCellValueDesc2007 = null;
    String stringCellValueDesc2003 = null;
    Sheet sheet2007;
    Sheet sheet2003;
    Row firstDataRow2007;
    Row firstDataRow2003;
    Row lastDataRow2007;
    Row lastDataRow2003;
    Cell nameCell2007;
    Cell nameCell2003;
    Cell descCell2007;
    Cell descCell2003;

    try {
        sheet2007 = workbook2007.getSheetAt(0);
        sheet2003 = workbook2003.getSheetAt(0);

        firstDataRow2007 = sheet2007.getRow(1);
        firstDataRow2003 = sheet2003.getRow(1);

        lastDataRow2007 = sheet2007.getRow(4);
        lastDataRow2003 = sheet2003.getRow(4);

        nameCell2007 = firstDataRow2007.getCell(0);
        nameCell2003 = firstDataRow2003.getCell(0);

        descCell2007 = lastDataRow2007.getCell(1);
        descCell2003 = lastDataRow2003.getCell(1);

        stringCellValue2007 = nameCell2007.getStringCellValue();
        stringCellValue2003 = nameCell2003.getStringCellValue();

        stringCellValueDesc2007 = descCell2007.getStringCellValue();
        stringCellValueDesc2003 = descCell2003.getStringCellValue();
    } catch (Exception e) {
        fail("Wrong structure inside workbook/sheet/row.");
    }

    assertEquals(
            "String in generated excel 2007 workbook does not match the string from the first element in the List of BuildingBlocks.",
            stringCellValue2007, firstInfstrElemName);
    assertEquals(
            "String in generated excel 2003 workbook does not match the string from the first element in the List of BuildingBlocks.",
            stringCellValue2003, firstInfstrElemName);

    assertEquals(
            "String in generated excel 2007 workbook does not match the string from the last element in the List of BuildingBlocks.",
            stringCellValueDesc2007, lastInfstrElemDesc);
    assertEquals(
            "String in generated excel 2003 workbook does not match the string from the last element in the List of BuildingBlocks.",
            stringCellValueDesc2003, lastInfstrElemDesc);
}

From source file:de.ks.idnadrev.expimp.xls.XlsxExporterTest.java

License:Apache License

@Test
public void testExportThoughts() throws Exception {
    File tempFile = File.createTempFile("thoughtExport", ".xlsx");
    EntityExportSource<Thought> source = new EntityExportSource<>(getAllIds(), Thought.class);
    XlsxExporter exporter = new XlsxExporter();
    exporter.export(tempFile, source);/* ww  w  .  j a  v a  2s  .  c  om*/

    Workbook wb = WorkbookFactory.create(tempFile);
    Sheet sheet = wb.getSheetAt(0);
    assertEquals(Thought.class.getName(), sheet.getSheetName());
    int lastRowNum = sheet.getLastRowNum();
    assertEquals(COUNT, lastRowNum);
    Row firstRow = sheet.getRow(0);

    ArrayList<String> titles = new ArrayList<>();
    firstRow.cellIterator().forEachRemaining(col -> titles.add(col.getStringCellValue()));
    assertThat(titles.size(), greaterThanOrEqualTo(3));
    log.info("Found titles {}", titles);

    String creationTime = PropertyPath.property(Thought.class, t -> t.getCreationTime());
    String name = PropertyPath.property(Thought.class, t -> t.getName());
    String description = PropertyPath.property(Thought.class, t -> t.getDescription());

    assertTrue(titles.contains(creationTime));
    assertTrue(titles.contains(name));
    assertTrue(titles.contains(description));

    int nameColumn = titles.indexOf(name);
    ArrayList<String> names = new ArrayList<String>(COUNT);
    for (int i = 1; i <= COUNT; i++) {
        Row row = sheet.getRow(i);
        names.add(row.getCell(nameColumn).getStringCellValue());
    }
    Collections.sort(names);
    assertEquals("Thought000", names.get(0));
    assertEquals("Thought141", names.get(COUNT - 1));

    Date excelDate = sheet.getRow(1).getCell(titles.indexOf(creationTime)).getDateCellValue();

    Thought thought = PersistentWork.forName(Thought.class, "Thought000");

    Timestamp timestamp = java.sql.Timestamp.valueOf(thought.getCreationTime());
    Date creationDate = new Date(timestamp.getTime());
    assertEquals(creationDate, excelDate);
}

From source file:de.ks.idnadrev.expimp.xls.XlsxExporterTest.java

License:Apache License

@Test
public void testExportToManyRelation() throws Exception {
    File tempFile = File.createTempFile("taskExportTest", ".xlsx");
    EntityExportSource<Task> tasks = new EntityExportSource<>(PersistentWork.idsFrom(Task.class), Task.class);
    EntityExportSource<Tag> tags = new EntityExportSource<>(PersistentWork.idsFrom(Tag.class), Tag.class);
    XlsxExporter exporter = new XlsxExporter();
    exporter.export(tempFile, tasks, tags);

    Workbook wb = WorkbookFactory.create(tempFile);
    Sheet taskSheet = wb.getSheet(Task.class.getName());
    Sheet tagSheet = wb.getSheet(Tag.class.getName());
    assertNotNull(taskSheet);/*from w  w w  . j a  v  a 2s.c o  m*/
    assertNotNull(tagSheet);

    Row firstRow = taskSheet.getRow(0);
    int pos = 0;
    Iterator<Cell> cellIterator = firstRow.cellIterator();

    String property = PropertyPath.property(Task.class, t -> t.getTags());
    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        if (cell.getStringCellValue().equals(property)) {
            break;
        }
        pos++;
    }
    assertNotEquals(Task.class.getSimpleName() + "." + property + " not exported", firstRow.getLastCellNum(),
            pos);

    Cell cell = taskSheet.getRow(1).getCell(pos);
    String[] split = StringUtils.split(cell.getStringCellValue(), ToManyColumn.SEPARATOR);
    assertEquals(2, split.length);
    assertTrue(Arrays.asList(split).contains("tag" + ToManyColumn.SEPARATOR_REPLACEMENT + "1"));
    assertTrue(Arrays.asList(split).contains("tag2"));
}

From source file:de.teststory.jspwiki.worksheetplugin.WorksheetPlugin.java

License:Apache License

@SuppressWarnings({ "rawtypes", "unchecked" })
@Override/*from w  w  w. j  av a  2 s. co m*/
public String execute(WikiContext context, Map params) throws PluginException {
    log.info("WorksheetPlugin executed");
    try {
        // Analyze Parameters
        String src = getCleanParameter(params, PARAM_SRC);
        if (src == null) {
            throw new PluginException("Parameter '" + PARAM_SRC + "' is required for Workbook plugin");
        }
        int worksheetId = -1;
        String s = getCleanParameter(params, PARAM_WORKSHEET_ID);
        if (s != null) {
            try {
                worksheetId = Integer.valueOf(s).intValue();
            } catch (NumberFormatException e) {
                throw new PluginException("Parameter '" + PARAM_WORKSHEET_ID + "' must be numeric: " + s);
            }
        }
        String worksheetName = getCleanParameter(params, PARAM_WORKSHEET_NAME); // may be null

        // Load the workbook
        WikiEngine engine = context.getEngine();
        AttachmentManager mgr = engine.getAttachmentManager();
        Attachment att = mgr.getAttachmentInfo(context, src);
        if (att == null) {
            throw new PluginException("Attachment '" + src + "' not found.");
        }
        InputStream inStream = mgr.getAttachmentStream(context, att);
        Workbook wb = WorkbookFactory.create(inStream);
        if (wb == null) {
            throw new PluginException("Could not load Workbook '" + src + "'");
        }

        // find worksheet
        Sheet sheet = findSheet(wb, worksheetId, worksheetName);
        if (sheet == null) {
            throw new PluginException(
                    "Could not find Worksheet. Index=" + worksheetId + ", name='" + worksheetName + "'");
        }

        StringBuilder sb = new StringBuilder();
        ToHtml toHtml = ToHtml.create(wb, sb);
        sb.append("<style type=\"text/css\">");
        toHtml.printStyles();
        sb.append("</style>");
        toHtml.printSheet(sheet);
        return sb.toString();

    } catch (ProviderException e) {
        throw new PluginException("Attachment info failed: " + e.getMessage(), e);
    } catch (IOException e) {
        throw new PluginException("Attachment info failed: " + e.getMessage(), e);
    } catch (EncryptedDocumentException e) {
        throw new PluginException("Attachment info failed: " + e.getMessage(), e);
    } catch (InvalidFormatException e) {
        throw new PluginException("Attachment info failed: " + e.getMessage(), e);
    }
}

From source file:demons.studentsmanagesystem.excel.poi.PoiItemReader.java

License:Apache License

/**
 * Open the underlying file using the {@code WorkbookFactory}. We keep track of the used {@code InputStream} so that
 * it can be closed cleanly on the end of reading the file. This to be able to release the resources used by
 * Apache POI.//from www. j  av  a2  s  .  co m
 *
 * @param resource the {@code Resource} pointing to the Excel file.
 * @throws Exception is thrown for any errors.
 */
@Override
protected void openExcelFile(final Resource resource) throws Exception {
    workbookStream = resource.getInputStream();
    if (!workbookStream.markSupported() && !(workbookStream instanceof PushbackInputStream)) {
        throw new IllegalStateException(
                "InputStream MUST either support mark/reset, or be wrapped as a PushbackInputStream");
    }
    this.workbook = WorkbookFactory.create(workbookStream);
    this.workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK);
}

From source file:dk.cubing.liveresults.action.admin.ScoresheetAction.java

License:Open Source License

/**
 * @return//  w  w w . java  2  s. co  m
 */
public String generateScoresheet() {
    if (!getFormats().isEmpty() && !getTimeFormats().isEmpty() && !getRound1().isEmpty()) {
        try {
            // load WCA template from file
            InputStream is = ServletActionContext.getServletContext()
                    .getResourceAsStream(getSpreadSheetFilename());
            Workbook workBook;
            workBook = WorkbookFactory.create(is);
            is.close();

            // build special registration sheet
            generateRegistrationSheet(workBook, getCompetition());

            // build result sheets
            generateResultSheets(workBook, getCompetition(), getFormats(), getTimeFormats(), getRound1(),
                    getRound2(), getRound3(), getRound4());

            // set default selected sheet
            workBook.setActiveSheet(workBook.getSheetIndex(SHEET_TYPE_REGISTRATION));

            // output generated spreadsheet
            log.debug("Ouputting generated workbook");
            out = new ByteArrayOutputStream();
            workBook.write(out);
            out.close();

            return Action.SUCCESS;
        } catch (InvalidFormatException e) {
            log.error("Spreadsheet template are using an unsupported format.", e);
        } catch (IOException e) {
            log.error("Error reading spreadsheet template.", e);
        }
        return Action.ERROR;
    } else {
        return Action.INPUT;
    }
}

From source file:dk.cubing.liveresults.action.admin.ScoresheetAction.java

License:Open Source License

/**
 * @return/* w  ww .  ja  va  2 s.c o m*/
 */
public String exportResults() {
    if (competitionId != null) {
        Competition competitionTemplate = getCompetitionService().find(competitionId);
        if (competitionTemplate == null) {
            log.error("Could not load competition: {}", competitionId);
            return Action.ERROR;
        }
        setCompetition(competitionTemplate);

        try {
            // load WCA template from file
            InputStream is = ServletActionContext.getServletContext()
                    .getResourceAsStream(getSpreadSheetFilename());
            Workbook workBook;
            workBook = WorkbookFactory.create(is);
            is.close();

            // build special registration sheet
            generateRegistrationSheet(workBook, getCompetition());

            // build result sheets
            generateResultSheets(workBook, getCompetition());

            // set default selected sheet
            workBook.setActiveSheet(workBook.getSheetIndex(SHEET_TYPE_REGISTRATION));

            // email or just output to pdf?
            if (isSubmitResultsToWCA()) {
                // write workbook to temp file
                File temp = File.createTempFile(getCompetitionId(), ".xls");
                temp.deleteOnExit();
                OutputStream os = new FileOutputStream(temp);
                workBook.write(os);
                os.close();

                // Create the attachment
                EmailAttachment attachment = new EmailAttachment();
                attachment.setPath(temp.getPath());
                attachment.setDisposition(EmailAttachment.ATTACHMENT);
                attachment.setName(getCompetitionId() + ".xls");

                // send email
                MultiPartEmail email = new MultiPartEmail();
                email.setCharset(Email.ISO_8859_1);
                email.setHostName(getText("email.smtp.server"));
                if (!getText("email.username").isEmpty() && !getText("email.password").isEmpty()) {
                    email.setAuthentication(getText("email.username"), getText("email.password"));
                }
                email.setSSL("true".equals(getText("email.ssl")));
                email.setSubject("Results from " + getCompetition().getName());
                email.setMsg(getText("admin.export.message",
                        new String[] { getCompetition().getName(), getCompetition().getOrganiser() }));
                email.setFrom(getCompetition().getOrganiserEmail(), getCompetition().getOrganiser());
                email.addTo(getText("admin.export.resultsteamEmail"), getText("admin.export.resultsteam"));
                email.addCc(getCompetition().getOrganiserEmail(), getCompetition().getOrganiser());
                email.addCc(getCompetition().getWcaDelegateEmail(), getCompetition().getWcaDelegate());
                email.attach(attachment);
                email.send();

                return Action.SUCCESS;
            } else {
                // output generated spreadsheet
                log.debug("Ouputting generated workbook");
                out = new ByteArrayOutputStream();
                workBook.write(out);
                out.close();

                return "spreadsheet";
            }
        } catch (InvalidFormatException e) {
            log.error("Spreadsheet template are using an unsupported format.", e);
        } catch (IOException e) {
            log.error("Error reading spreadsheet template.", e);
        } catch (EmailException e) {
            log.error(e.getMessage(), e);
        }
        return Action.ERROR;
    } else {
        return Action.INPUT;
    }
}

From source file:dk.cubing.liveresults.uploader.parser.ExcelParser.java

License:Open Source License

/**
 * @param competition//from  www  . j  av a 2s.c  o m
 * @param filename
 * @return
 * @throws ResultsFileParserException
 * @throws IllegalStateException
 */
public Competition parse(Competition competition, String filename)
        throws ResultsFileParserException, IllegalStateException {
    try {
        // load excel work book
        FileInputStream fi = new FileInputStream(filename);
        Workbook workBook = WorkbookFactory.create(fi);
        fi.close();

        // validate spreadsheet format
        if (isValidSpreadsheet(workBook)) {

            // parse competition details
            competition = parseCompetitionDetails(workBook, competition);

            // parse competitors
            List<Competitor> competitors = parseCompetitors(workBook);
            if (!competitors.isEmpty()) {
                competition.setCompetitors(competitors);
            }

            // parse events
            List<Event> events = parseEvents(workBook);
            if (!events.isEmpty()) {
                competition.setEvents(events);
            }

            return competition;
        } else {
            throw new ResultsFileParserException("Results file are not based on the WCA template!");
        }

    } catch (FileNotFoundException e) {
        log.error("Results file not found: {}", filename);
        throw new ResultsFileParserException(e.getLocalizedMessage(), e);
    } catch (InvalidFormatException e) {
        log.error("Results file has an invalid format.", e);
        throw new ResultsFileParserException(e.getLocalizedMessage(), e);
    } catch (IOException e) {
        log.error("Error reading results file.", e);
        throw new ResultsFileParserException(e.getLocalizedMessage(), e);
    } catch (IllegalStateException e) {
        log.error(e.getLocalizedMessage(), e);
        throw e;
    } catch (Exception e) {
        log.error(e.getLocalizedMessage(), e);
        throw new ResultsFileParserException(e.getLocalizedMessage(), e);
    }
}