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:ch.swissbytes.Service.business.Spreadsheet.ToCSV.java

License:Apache License

/**
 * Open an Excel workbook ready for conversion.
 *
 * @param file An instance of the File class that encapsulates a handle
 *        to a valid Excel workbook. Note that the workbook can be in
 *        either binary (.xls) or SpreadsheetML (.xlsx) format.
 * @throws java.io.FileNotFoundException Thrown if the file cannot be located.
 * @throws java.io.IOException Thrown if a problem occurs in the file system.
 * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException Thrown
 *         if invalid xml is found whilst parsing an input SpreadsheetML
 *         file./*w  ww . j  a  va 2  s .  c o  m*/
 */
private void openWorkbook(File file) throws FileNotFoundException, IOException, InvalidFormatException {
    FileInputStream fis = null;
    try {
        System.out.println("Opening workbook [" + file.getName() + "]");

        fis = new FileInputStream(file);

        // Open the workbook and then create the FormulaEvaluator and
        // DataFormatter instances that will be needed to, respectively,
        // force evaluation of forumlae found in cells and create a
        // formatted String encapsulating the cells contents.
        this.workbook = WorkbookFactory.create(fis);
        this.evaluator = this.workbook.getCreationHelper().createFormulaEvaluator();
        this.formatter = new DataFormatter(true);
    } finally {
        if (fis != null) {
            fis.close();
        }
    }
}

From source file:cherry.foundation.download.TableDownloadTemplateTest.java

License:Apache License

@Test
public void testDownloadXlsNoHeader() throws InvalidFormatException, IOException {
    // /*from  ww w. jav a2  s . c  o m*/
    MockHttpServletResponse response = new MockHttpServletResponse();
    // 
    tableDownloadOperation.downloadXls(response, "test_{0}.xls", new LocalDateTime(2015, 1, 23, 12, 34, 56),
            null, createCommonClause(), createOrderByClause(), constant("TEST00"));
    // 
    assertEquals("application/vnd.ms-excel", response.getContentType());
    assertEquals("application/vnd.ms-excel", response.getHeader("Content-Type"));
    assertEquals("attachment; filename=\"test_20150123123456.xls\"; filename*=UTF-8''test_20150123123456.xls",
            response.getHeader("Content-Disposition"));
    try (InputStream in = new ByteArrayInputStream(response.getContentAsByteArray());
            Workbook workbook = WorkbookFactory.create(in);
            ExcelReader reader = new ExcelReader(workbook)) {
        String[] record;
        record = reader.read();
        assertEquals(1, record.length);
        assertEquals("TEST00", record[0]);
        assertNull(reader.read());
    }
}

From source file:cherry.foundation.download.TableDownloadTemplateTest.java

License:Apache License

@Test
public void testDownloadXlsWithHeader() throws InvalidFormatException, IOException {
    // //ww  w  . java2  s. co m
    MockHttpServletResponse response = new MockHttpServletResponse();
    // 
    tableDownloadOperation.downloadXls(response, "test_{0}.xls", new LocalDateTime(2015, 1, 23, 12, 34, 56),
            asList("HEAD0"), createCommonClause(), createOrderByClause(), constant("TEST00"));
    // 
    assertEquals("application/vnd.ms-excel", response.getContentType());
    assertEquals("application/vnd.ms-excel", response.getHeader("Content-Type"));
    assertEquals("attachment; filename=\"test_20150123123456.xls\"; filename*=UTF-8''test_20150123123456.xls",
            response.getHeader("Content-Disposition"));
    try (InputStream in = new ByteArrayInputStream(response.getContentAsByteArray());
            Workbook workbook = WorkbookFactory.create(in);
            ExcelReader reader = new ExcelReader(workbook)) {
        String[] record;
        record = reader.read();
        assertEquals(1, record.length);
        assertEquals("HEAD0", record[0]);
        record = reader.read();
        assertEquals(1, record.length);
        assertEquals("TEST00", record[0]);
        assertNull(reader.read());
    }
}

From source file:cherry.foundation.download.TableDownloadTemplateTest.java

License:Apache License

@Test
public void testDownloadXlsxNoHeader() throws InvalidFormatException, IOException {
    // // w ww.j  a  va 2s .  com
    MockHttpServletResponse response = new MockHttpServletResponse();
    // 
    tableDownloadOperation.downloadXlsx(response, "test_{0}.xlsx", new LocalDateTime(2015, 1, 23, 12, 34, 56),
            null, createCommonClause(), createOrderByClause(), constant("TEST00"));
    // 
    assertEquals("application/vnd.ms-excel", response.getContentType());
    assertEquals("application/vnd.ms-excel", response.getHeader("Content-Type"));
    assertEquals("attachment; filename=\"test_20150123123456.xlsx\"; filename*=UTF-8''test_20150123123456.xlsx",
            response.getHeader("Content-Disposition"));
    try (InputStream in = new ByteArrayInputStream(response.getContentAsByteArray());
            Workbook workbook = WorkbookFactory.create(in);
            ExcelReader reader = new ExcelReader(workbook)) {
        String[] record;
        record = reader.read();
        assertEquals(1, record.length);
        assertEquals("TEST00", record[0]);
        assertNull(reader.read());
    }
}

From source file:cherry.foundation.download.TableDownloadTemplateTest.java

License:Apache License

@Test
public void testDownloadXlsxWithHeader() throws InvalidFormatException, IOException {
    // //from w  w  w .j  a  va2 s.c  om
    MockHttpServletResponse response = new MockHttpServletResponse();
    // 
    tableDownloadOperation.downloadXls(response, "test_{0}.xlsx", new LocalDateTime(2015, 1, 23, 12, 34, 56),
            asList("HEAD0"), createCommonClause(), createOrderByClause(), constant("TEST00"),
            constantAs("TEST01", path(String.class, "head1")));
    // 
    assertEquals("application/vnd.ms-excel", response.getContentType());
    assertEquals("application/vnd.ms-excel", response.getHeader("Content-Type"));
    assertEquals("attachment; filename=\"test_20150123123456.xlsx\"; filename*=UTF-8''test_20150123123456.xlsx",
            response.getHeader("Content-Disposition"));
    try (InputStream in = new ByteArrayInputStream(response.getContentAsByteArray());
            Workbook workbook = WorkbookFactory.create(in);
            ExcelReader reader = new ExcelReader(workbook)) {
        String[] record;
        record = reader.read();
        assertEquals(2, record.length);
        assertEquals("HEAD0", record[0]);
        assertEquals("HEAD1", record[1]);
        record = reader.read();
        assertEquals(2, record.length);
        assertEquals("TEST00", record[0]);
        assertEquals("TEST01", record[1]);
        assertNull(reader.read());
    }
}

From source file:cherry.goods.excel.ExcelFactory.java

License:Apache License

/**
 * Excel??<br />//from   w  ww .  j  a  v  a  2  s .c o  m
 * ????????
 * 
 * @param in ?
 * @return Excel
 * @throws InvalidFormatException ?
 * @throws IOException ?
 */
public static Workbook open(InputStream in) throws InvalidFormatException, IOException {
    return WorkbookFactory.create(in);
}

From source file:cherry.goods.excel.ExcelFactory.java

License:Apache License

/**
 * Excel<br />// w ww .j a  v a2 s .c  o m
 * ?????????
 * 
 * @param file ?
 * @return Excel
 * @throws InvalidFormatException ?
 * @throws FileNotFoundException ?????
 * @throws IOException ?
 */
public static Workbook load(File file) throws InvalidFormatException, FileNotFoundException, IOException {
    try (InputStream in = new FileInputStream(file)) {
        return WorkbookFactory.create(in);
    }
}

From source file:cherry.goods.telno.SoumuExcelParser.java

License:Apache License

/**
 * ?//from  w  ww  .  j ava2s  . com
 * 
 * @param in ?
 * @return ?????? (6?)?????
 * @throws InvalidFormatException ???
 * @throws IOException ??
 */
public Map<String, Pair<String, String>> parse(InputStream in) throws InvalidFormatException, IOException {
    Map<String, Pair<String, String>> map = new LinkedHashMap<>();
    try (Workbook workbook = WorkbookFactory.create(in)) {
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            Integer numberCol = null;
            Integer areaCodeCol = null;
            Integer localCodeCol = null;
            boolean preparing = true;
            for (Row row : sheet) {
                if (preparing) {
                    for (Cell cell : row) {
                        String value = cell.getStringCellValue();
                        if (numberLabel.equals(value)) {
                            numberCol = cell.getColumnIndex();
                        }
                        if (areaCodeLabel.equals(value)) {
                            areaCodeCol = cell.getColumnIndex();
                        }
                        if (localCodeLabel.equals(value)) {
                            localCodeCol = cell.getColumnIndex();
                        }
                    }
                    if (numberCol != null && areaCodeCol != null && localCodeCol != null) {
                        preparing = false;
                    }
                } else {
                    String number = getCellValue(row, numberCol.intValue());
                    String areaCode = getCellValue(row, areaCodeCol.intValue());
                    String localCode = getCellValue(row, localCodeCol.intValue());
                    if (isNotEmpty(number) && isNotEmpty(areaCode) && isNotEmpty(localCode)) {
                        map.put(number, Pair.of(areaCode, localCode));
                    }
                }
            }
        }
    }
    return map;
}

From source file:cherry.gradle.task.generator.GenerateDto.java

License:Apache License

private List<TypeDef> parseDef(File file) throws IOException {
    WorkbookParser parser = new SheetBasedParser();
    List<TypeDef> list = new LinkedList<>();
    try (InputStream in = new FileInputStream(file)) {
        Workbook workbook = WorkbookFactory.create(in);
        for (TypeDef typeDef : parser.parse(workbook)) {
            if (typeDef.getFullyQualifiedClassName() != null) {
                list.add(typeDef);//from w  w w. j a v a  2 s .c  o  m
            }
        }
    } catch (InvalidFormatException ex) {
        throw new IllegalArgumentException(ex);
    }
    return list;
}

From source file:cn.afterturn.easypoi.cache.ExcelCache.java

License:Apache License

public static Workbook getWorkbook(String url, Integer[] sheetNums, boolean needAll) {
    InputStream is = null;//  w w  w  .java  2 s  .c  om
    List<Integer> sheetList = Arrays.asList(sheetNums);
    try {
        is = POICacheManager.getFile(url);
        Workbook wb = WorkbookFactory.create(is);
        // sheet
        if (!needAll) {
            for (int i = wb.getNumberOfSheets() - 1; i >= 0; i--) {
                if (!sheetList.contains(i)) {
                    wb.removeSheetAt(i);
                }
            }
        }
        return wb;
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
    } finally {
        try {
            is.close();
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
        }
    }
    return null;
}