Example usage for org.apache.poi.ss.usermodel WorkbookFactory 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:com.fjn.helper.common.io.file.office.excel.ExcelMgr.java

License:Apache License

public static Excel getExcel(InputStream in) {
    Workbook wb = null;/*from  w  ww .j  ava2  s .c  om*/
    try {
        wb = WorkbookFactory.create(in);
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    if (wb == null)
        return null;
    if (wb instanceof HSSFWorkbook) {
        Excel excel = new Excel(ExcelType.XLS, false);
        excel.setWorkbook(wb);
        return excel;
    } else if (wb instanceof XSSFWorkbook) {
        Excel excel = new Excel(ExcelType.XLSX, false);
        excel.setWorkbook(wb);
        return excel;
    } else {
        Excel excel = new Excel(ExcelType.SXLSX, false);
        excel.setWorkbook(wb);
        return excel;
    }
}

From source file:com.gezipu360.cashier.bean.word.UpdateEmbeddedDoc.java

License:Apache License

/**
 * Called to update the embedded Excel workbook. As the format and structire
 * of the workbook are known in advance, all this code attempts to do is
 * write a new value into the first cell on the first row of the first
 * worksheet. Prior to executing this method, that cell will contain the
 * value 1./*from   w w  w.  j  a va 2  s . co  m*/
 *
 * @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException
 *                             Rather
 *                             than use the specific classes (HSSF/XSSF) to handle the embedded
 *                             workbook this method uses those defeined in the SS stream. As
 *                             a result, it might be the case that a SpreadsheetML file is
 *                             opened for processing, throwing this exception if that file is
 *                             invalid.
 * @throws java.io.IOException Thrown if a problem occurs in the underlying
 *                             file system.
 */
public void updateEmbeddedDoc() throws OpenXML4JException, IOException {
    Workbook workbook = null;
    Sheet sheet = null;
    Row row = null;
    Cell cell = null;
    PackagePart pPart = null;
    Iterator<PackagePart> pIter = null;
    List<PackagePart> embeddedDocs = this.doc.getAllEmbedds();
    if (embeddedDocs != null && !embeddedDocs.isEmpty()) {
        pIter = embeddedDocs.iterator();
        while (pIter.hasNext()) {
            pPart = pIter.next();
            if (pPart.getPartName().getExtension().equals(BINARY_EXTENSION)
                    || pPart.getPartName().getExtension().equals(OPENXML_EXTENSION)) {

                // Get an InputStream from the pacage part and pass that
                // to the create method of the WorkbookFactory class. Update
                // the resulting Workbook and then stream that out again
                // using an OutputStream obtained from the same PackagePart.
                workbook = WorkbookFactory.create(pPart.getInputStream());
                sheet = workbook.getSheetAt(SHEET_NUM);
                row = sheet.getRow(ROW_NUM);
                cell = row.getCell(CELL_NUM);
                cell.setCellValue(NEW_VALUE);
                workbook.write(pPart.getOutputStream());
            }
        }

        // Finally, write the newly modified Word document out to file.
        this.doc.write(new FileOutputStream(this.docFile));
    }
}

From source file:com.gezipu360.cashier.bean.word.UpdateEmbeddedDoc.java

License:Apache License

/**
 * Called to test whether or not the embedded workbook was correctly
 * updated. This method simply recovers the first cell from the first row
 * of the first workbook and tests the value it contains.
 * <p/>//from   www.j a va  2  s . c o m
 * Note that execution will not continue up to the assertion as the
 * embedded workbook is now corrupted and causes an IllegalArgumentException
 * with the following message
 * <p/>
 * <em>java.lang.IllegalArgumentException: Your InputStream was neither an
 * OLE2 stream, nor an OOXML stream</em>
 * <p/>
 * to be thrown when the WorkbookFactory.createWorkbook(InputStream) method
 * is executed.
 *
 * @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException
 *                             Rather
 *                             than use the specific classes (HSSF/XSSF) to handle the embedded
 *                             workbook this method uses those defeined in the SS stream. As
 *                             a result, it might be the case that a SpreadsheetML file is
 *                             opened for processing, throwing this exception if that file is
 *                             invalid.
 * @throws java.io.IOException Thrown if a problem occurs in the underlying
 *                             file system.
 */
public void checkUpdatedDoc() throws OpenXML4JException, IOException {
    Workbook workbook = null;
    Sheet sheet = null;
    Row row = null;
    Cell cell = null;
    PackagePart pPart = null;
    Iterator<PackagePart> pIter = null;
    List<PackagePart> embeddedDocs = this.doc.getAllEmbedds();
    if (embeddedDocs != null && !embeddedDocs.isEmpty()) {
        pIter = embeddedDocs.iterator();
        while (pIter.hasNext()) {
            pPart = pIter.next();
            if (pPart.getPartName().getExtension().equals(BINARY_EXTENSION)
                    || pPart.getPartName().getExtension().equals(OPENXML_EXTENSION)) {
                workbook = WorkbookFactory.create(pPart.getInputStream());
                sheet = workbook.getSheetAt(SHEET_NUM);
                row = sheet.getRow(ROW_NUM);
                cell = row.getCell(CELL_NUM);
                //assertEquals(cell.getNumericCellValue(), NEW_VALUE, 0.0001);
            }
        }
    }
}

From source file:com.github.camaral.sheeco.Sheeco.java

License:Apache License

/**
 * Creates a list of Java objects from a the spreadsheet. The payloadClass
 * must be annotated with {@link SpreadsheetPayload}.
 * //from w ww.  j  a va 2  s .c om
 * @param stream
 *            The spreadsheet file
 * @param payloadClass
 *            The type of the Java objects to be created
 * @return the content of the spreadsheet serialized into a list of java
 *         objects.
 * @throws SpreadsheetUnmarshallingException
 * @throws SpreasheetUnmarshallingUnrecoverableException
 */
public <T> List<T> fromSpreadsheet(final InputStream stream, final Class<T> payloadClass)
        throws SpreadsheetUnmarshallingException, SpreasheetUnmarshallingUnrecoverableException {
    try {
        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:com.github.camaral.sheeco.SheecoTest.java

License:Apache License

public void testToSpreadsheetWithSet() throws Exception {
    // given/*from  w  w  w.j av  a 2  s .  c om*/
    Set<Class<? extends Object>> payloadClasses = new HashSet<>();
    payloadClasses.add(Cat.class);
    ByteArrayOutputStream out = new ByteArrayOutputStream();

    // when
    sut.toSpreadsheet(out, payloadClasses);

    // then
    ByteArrayInputStream in = new ByteArrayInputStream(out.toByteArray());
    Workbook wb = WorkbookFactory.create(in);
    assertCatHeaders(wb);
}

From source file:com.github.camaral.sheeco.SheecoTest.java

License:Apache License

public void testToSpreadsheetWithVarArgs() throws Exception {
    // given//from   www.j  a va 2  s .  c  o m
    ByteArrayOutputStream out = new ByteArrayOutputStream();

    // when
    sut.toSpreadsheet(out, Cat.class);

    // then
    ByteArrayInputStream in = new ByteArrayInputStream(out.toByteArray());
    Workbook wb = WorkbookFactory.create(in);
    assertCatHeaders(wb);
}

From source file:com.github.crab2died.ExcelUtils.java

License:Open Source License

/**
 * ?Excel??java/*from   ww w  .j av  a  2 s .  c om*/
 *
 * @param excelPath  Excel
 * @param clazz      ({@link com.github.crab2died.annotation.ExcelField})
 * @param offsetLine Excel(0)
 * @param limitLine  ?()
 * @param sheetIndex Sheet(0)
 * @param <T>        ?
 * @return ?java?
 * @throws Excel4JException       
 * @throws IOException            
 * @throws InvalidFormatException 
 * @author Crab2Died
 */
public <T> List<T> readExcel2Objects(String excelPath, Class<T> clazz, int offsetLine, int limitLine,
        int sheetIndex) throws Excel4JException, IOException, InvalidFormatException {

    try (Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(excelPath)))) {
        return readExcel2ObjectsHandler(workbook, clazz, offsetLine, limitLine, sheetIndex);
    }
}

From source file:com.github.crab2died.ExcelUtils.java

License:Open Source License

/**
 * ?Excel??java/*from   w  w  w.ja  va 2  s.c o m*/
 *
 * @param is         Excel??
 * @param clazz      ({@link com.github.crab2died.annotation.ExcelField})
 * @param offsetLine Excel(0)
 * @param limitLine  ?()
 * @param sheetIndex Sheet(0)
 * @param <T>        ?
 * @return ?java?
 * @throws Excel4JException       
 * @throws IOException            
 * @throws InvalidFormatException 
 * @author Crab2Died
 */
public <T> List<T> readExcel2Objects(InputStream is, Class<T> clazz, int offsetLine, int limitLine,
        int sheetIndex) throws Excel4JException, IOException, InvalidFormatException {

    try (Workbook workbook = WorkbookFactory.create(is)) {
        return readExcel2ObjectsHandler(workbook, clazz, offsetLine, limitLine, sheetIndex);
    }
}

From source file:com.github.crab2died.ExcelUtils.java

License:Open Source License

/**
 * ?Excel?,{@code List[List[String]]}??/* w w w . java 2s . co m*/
 *
 * @param excelPath  ?Excel
 * @param offsetLine Excel(0)
 * @param limitLine  ?()
 * @param sheetIndex Sheet(0)
 * @return {@code List<List<String>>}??
 * @throws IOException            
 * @throws InvalidFormatException 
 * @author Crab2Died
 */
public List<List<String>> readExcel2List(String excelPath, int offsetLine, int limitLine, int sheetIndex)
        throws IOException, InvalidFormatException {

    try (Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(excelPath)))) {
        return readExcel2ObjectsHandler(workbook, offsetLine, limitLine, sheetIndex);
    }
}

From source file:com.github.crab2died.ExcelUtils.java

License:Open Source License

/**
 * ?Excel?,{@code List[List[String]]}??//  w ww.j  a  v a 2s. c om
 *
 * @param is         ?Excel??
 * @param offsetLine Excel(0)
 * @param limitLine  ?()
 * @param sheetIndex Sheet(0)
 * @return {@code List<List<String>>}??
 * @throws Excel4JException       
 * @throws IOException            
 * @throws InvalidFormatException 
 * @author Crab2Died
 */
public List<List<String>> readExcel2List(InputStream is, int offsetLine, int limitLine, int sheetIndex)
        throws Excel4JException, IOException, InvalidFormatException {

    try (Workbook workbook = WorkbookFactory.create(is)) {
        return readExcel2ObjectsHandler(workbook, offsetLine, limitLine, sheetIndex);
    }
}