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.projectswg.tools.controllers.MainController.java

License:Open Source License

private void handlePopulateList(File source) throws IOException, InvalidFormatException {
    Workbook workbook = WorkbookFactory.create(source);
    outputSelectionList.clear();//www .  ja va  2  s. c om
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        outputSelectionList.add(getFileItem(source.getAbsolutePath(), workbook.getSheetAt(i)));
    }
    workbook.close();
}

---

From source file:com.projectswg.tools.ExcelToIff.java

License:Open Source License

private static void convertWorkbook(String path) {
    File file = new File(path);
    if (!file.exists()) {
        System.err.println(String.format("Could not convert %s as it doesn't exist!", path));
        return;// ww w.  j  a  v  a2  s .  c  o m
    }

    try {
        Workbook workbook = WorkbookFactory.create(file);
        System.out.println("Converting sheets from workbook " + file.getAbsolutePath());
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            path = file.getAbsolutePath().split("\\.")[0] + "_" + sheet.getSheetName() + ".iff";
            convertSheet(new File(path), sheet);
        }
        System.out.println("Conversion for workbook " + file.getAbsolutePath() + " completed.");
    } catch (IOException | InvalidFormatException e) {
        e.printStackTrace();
    }
}

---

From source file:com.projectswg.tools.ExcelToIff.java

License:Open Source License

private static void convertSheet(String path, String sheetStr) {
    File file = new File(path);
    if (!file.exists()) {
        System.err.println(String.format("Could not convert %s as it doesn't exist!", path));
        return;//from   www  . j a va 2  s .  co m
    }

    try {
        Workbook workbook = WorkbookFactory.create(file);
        Sheet sheet = workbook.getSheet(sheetStr);
        if (sheet == null)
            sheet = workbook.getSheetAt(Integer.valueOf(sheetStr));
        if (sheet == null) {
            System.err.println(String.format("Could not convert %s as there is no sheet name or id that is %s",
                    path, sheetStr));
        }
        System.out
                .println("Converting sheet " + sheet.getSheetName() + " in workbook " + file.getAbsolutePath());
        convertSheet(new File(file.getAbsolutePath().split("\\.")[0] + "_" + sheet.getSheetName() + ".iff"),
                sheet);
        System.out.println("Conversion for sheet " + sheet.getSheetName() + " in workbook "
                + file.getAbsolutePath() + " completed.");
    } catch (IOException | InvalidFormatException e) {
        e.printStackTrace();
    }
}

---

From source file:com.qihang.winter.poi.cache.ExcelCache.java

License:Apache License

public static Workbook getWorkbook(String url, Integer[] sheetNums, boolean needAll) {
    InputStream is = null;/* w w  w. ja v  a2 s  .  c o m*/
    List<Integer> sheetList = Arrays.asList(sheetNums);
    try {
        is = com.qihang.winter.poi.cache.manager.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 (InvalidFormatException e) {
        LOGGER.error(e.getMessage(), e);
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
    } finally {
        try {
            is.close();
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
        }
    }
    return null;
}

---

From source file:com.qualogy.qafe.service.DocumentServiceImpl.java

License:Apache License

private DocumentOutput handleExcel2003(DocumentParameter parameter) throws IOException {
    DocumentOutput out = null;/*  w  ww  .  j  av  a2s .c om*/
    String uuid = UUIDHelper.generateUUID();
    POIFSFileSystem fs = new POIFSFileSystem(new ByteArrayInputStream(parameter.getData()));
    Workbook workbook = WorkbookFactory.create(fs);
    Sheet sheet = workbook.getSheetAt(0);
    out = handleExcelData(sheet, parameter.isFirstFieldHeader());
    out.setUuid(uuid);
    return out;
}

---

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.POIExcerpterAndMerger.java

License:Apache License

private Workbook open(File f) throws IOException {
    try {/*from www .j  a v  a  2  s . c om*/
        return WorkbookFactory.create(f);
    } catch (InvalidFormatException e) {
        throw new IOException("File broken", e);
    }
}

---

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.POIExcerpterAndMerger.java

License:Apache License

private Workbook open(ContentReader reader) throws IOException {
    // If we can use a FileChannel, do
    if (reader.getMimetype().equals(MimetypeMap.MIMETYPE_EXCEL)) {
        NPOIFSFileSystem fs = new NPOIFSFileSystem(reader.getFileChannel());
        return WorkbookFactory.create(fs);
    }//from  www  . j a  v  a  2s .  c  o  m

    // Otherwise, ContentReader doesn't offer a File
    // So, we have to go via the InputStream
    try {
        return WorkbookFactory.create(reader.getContentInputStream());
    } catch (InvalidFormatException e) {
        throw new IOException("File broken", e);
    }
}

---

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.TestPOIExcerpter.java

License:Apache License

@Test
public void excerptGoesReadOnly() throws Exception {
    for (Workbook wb : new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }) {
        FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();

        Sheet s = wb.createSheet("Test");

        // Numeric formulas
        Row r1 = s.createRow(0);//  ww w.  j  av a  2  s.c om
        Cell c1 = r1.createCell(0);
        Cell c2 = r1.createCell(1);
        Cell c3 = r1.createCell(2);
        Cell c4 = r1.createCell(3);

        c1.setCellValue(1);
        c2.setCellValue(2);
        c3.setCellFormula("A1+B1");
        c4.setCellFormula("(A1+B1)*B1");

        // Strings, booleans and errors
        Row r2 = s.createRow(1);
        Cell c21 = r2.createCell(0);
        Cell c22 = r2.createCell(1);
        Cell c23 = r2.createCell(2);
        Cell c24 = r2.createCell(3);

        c21.setCellValue("Testing");
        c22.setCellFormula("CONCATENATE(A2,A2)");
        c23.setCellFormula("FALSE()");
        c24.setCellFormula("A1/0");

        // Ensure the formulas are current
        eval.evaluateAll();

        // Run the excerpt
        File tmp = File.createTempFile("test", ".xls");
        wb.write(new FileOutputStream(tmp));

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        excerpter.excerpt(new int[] { 0 }, tmp, baos);

        // Check
        Workbook newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray()));
        assertEquals(1, newwb.getNumberOfSheets());

        s = newwb.getSheetAt(0);
        r1 = s.getRow(0);
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(0).getCellType());
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(1).getCellType());
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(2).getCellType());
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(3).getCellType());

        assertEquals(1.0, s.getRow(0).getCell(0).getNumericCellValue(), 0.001);
        assertEquals(2.0, s.getRow(0).getCell(1).getNumericCellValue(), 0.001);
        assertEquals(3.0, s.getRow(0).getCell(2).getNumericCellValue(), 0.001);
        assertEquals(6.0, s.getRow(0).getCell(3).getNumericCellValue(), 0.001);

        r2 = s.getRow(1);
        assertEquals(Cell.CELL_TYPE_STRING, r2.getCell(0).getCellType());
        assertEquals(Cell.CELL_TYPE_STRING, r2.getCell(1).getCellType());
        assertEquals(Cell.CELL_TYPE_BOOLEAN, r2.getCell(2).getCellType());
        assertEquals(Cell.CELL_TYPE_BLANK, r2.getCell(3).getCellType());

        assertEquals("Testing", s.getRow(1).getCell(0).getStringCellValue());
        assertEquals("TestingTesting", s.getRow(1).getCell(1).getStringCellValue());
        assertEquals(false, s.getRow(1).getCell(2).getBooleanCellValue());
    }
}

---

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.TestPOIExcerpter.java

License:Apache License

@Test
public void excerptRemovesUnUsed() throws Exception {
    String[] names = new String[] { "a", "b", "ccc", "dddd", "e", "f", "gg" };

    for (Workbook wb : new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }) {
        // Create some dummy content
        for (String sn : names) {
            Sheet s = wb.createSheet(sn);
            s.createRow(0).createCell(0).setCellValue(sn);
        }//from  w w w .j  a v  a2 s .  com

        // Excerpt by index
        File tmp = File.createTempFile("test", ".xls");
        wb.write(new FileOutputStream(tmp));

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        int[] excI = new int[] { 0, 1, 2, 4, 5 };
        excerpter.excerpt(excI, tmp, baos);

        // Check
        Workbook newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray()));
        assertEquals(5, newwb.getNumberOfSheets());
        assertEquals(names[excI[0]], newwb.getSheetName(0));
        assertEquals(names[excI[1]], newwb.getSheetName(1));
        assertEquals(names[excI[2]], newwb.getSheetName(2));
        assertEquals(names[excI[3]], newwb.getSheetName(3));
        assertEquals(names[excI[4]], newwb.getSheetName(4));

        assertEquals(names[excI[0]], newwb.getSheetAt(0).getRow(0).getCell(0).getStringCellValue());
        assertEquals(names[excI[1]], newwb.getSheetAt(1).getRow(0).getCell(0).getStringCellValue());
        assertEquals(names[excI[2]], newwb.getSheetAt(2).getRow(0).getCell(0).getStringCellValue());
        assertEquals(names[excI[3]], newwb.getSheetAt(3).getRow(0).getCell(0).getStringCellValue());
        assertEquals(names[excI[4]], newwb.getSheetAt(4).getRow(0).getCell(0).getStringCellValue());

        // Excerpt by name
        String[] excN = new String[] { "b", "ccc", "f", "gg" };
        baos = new ByteArrayOutputStream();
        excerpter.excerpt(excN, tmp, baos);

        newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray()));
        assertEquals(4, newwb.getNumberOfSheets());
        assertEquals(excN[0], newwb.getSheetName(0));
        assertEquals(excN[1], newwb.getSheetName(1));
        assertEquals(excN[2], newwb.getSheetName(2));
        assertEquals(excN[3], newwb.getSheetName(3));

        assertEquals(excN[0], newwb.getSheetAt(0).getRow(0).getCell(0).getStringCellValue());
        assertEquals(excN[1], newwb.getSheetAt(1).getRow(0).getCell(0).getStringCellValue());
        assertEquals(excN[2], newwb.getSheetAt(2).getRow(0).getCell(0).getStringCellValue());
        assertEquals(excN[3], newwb.getSheetAt(3).getRow(0).getCell(0).getStringCellValue());

        // Can't excerpt by invalid index
        try {
            excerpter.excerpt(new int[] { 0, 10 }, tmp, null);
            fail();
        } catch (IllegalArgumentException e) {
        }

        // Can't excerpt by invalid name
        try {
            excerpter.excerpt(new String[] { "a", "invalid" }, tmp, null);
            fail();
        } catch (IllegalArgumentException e) {
        }
    }
}

---

From source file:com.rapidminer.operator.nio.model.Excel2007ResultSet.java

License:Open Source License

/**
 * The constructor to build an ExcelResultSet from the given configuration. The calling operator
 * might be null. It is only needed for error handling.
 *///from  ww  w  .  ja  v a2  s.co m
public Excel2007ResultSet(Operator callingOperator, ExcelResultSetConfiguration configuration)
        throws OperatorException {
    // reading configuration
    columnOffset = configuration.getColumnOffset();
    rowOffset = configuration.getRowOffset();
    currentRow = configuration.getRowOffset() - 1;

    timeZone = configuration.getTimezone();
    dateFormat = configuration.getDatePattern();
    try {
        workbookInputStream = new FileInputStream(configuration.getFile());
        workbook = WorkbookFactory.create(workbookInputStream);
    } catch (IOException | InvalidFormatException e) {
        throw new UserError(callingOperator, "file_consumer.error_loading_file");
    }

    // check range
    if (columnOffset > configuration.getColumnLast() || rowOffset > configuration.getRowLast()
            || columnOffset < 0 || rowOffset < 0) {
        throw new UserError(callingOperator, 223, Tools.getExcelColumnName(columnOffset) + rowOffset + ":"
                + Tools.getExcelColumnName(configuration.getColumnLast()) + configuration.getRowLast());
    }

    // check file presence
    if (configuration.getFile() == null) {
        throw new UserError(callingOperator, "file_consumer.no_file_defined");
    }
    try {
        sheet = configuration.selectSheetFrom(workbook);
    } catch (IndexOutOfBoundsException | IllegalArgumentException e) {
        throw new UserError(callingOperator, 953, configuration.getSheet() + 1);
    } catch (ExcelSheetSelection.SheetNotFoundException e) {
        throw new UserError(callingOperator, 321, configuration.getFile(), e.getMessage());
    }
    Row row = sheet.getRow(sheet.getFirstRowNum());
    if (row == null) {
        totalNumberOfColumns = 0;
        totalNumberOfRows = 0;
    } else {
        totalNumberOfColumns = Math.min(configuration.getColumnLast(),
                sheet.getRow(sheet.getFirstRowNum()).getLastCellNum() - 1) - columnOffset + 1;
        totalNumberOfRows = Math.min(configuration.getRowLast(), sheet.getLastRowNum()) - rowOffset + 1;
    }

    if (totalNumberOfColumns < 0 || totalNumberOfRows < 0) {
        throw new UserError(callingOperator, 404);
    }

    emptyColumns = new boolean[totalNumberOfColumns];
    emptyRows = new boolean[totalNumberOfRows];

    // filling offsets
    Arrays.fill(emptyColumns, true);
    Arrays.fill(emptyRows, true);

    // determine offsets and emptiness
    boolean foundAny = false;
    for (int r = 0; r < totalNumberOfRows; r++) {
        for (int c = 0; c < totalNumberOfColumns; c++) {
            if (emptyRows[r] || emptyColumns[c]) {
                final Row rowItem = sheet.getRow(r + rowOffset);
                if (rowItem == null) {
                    continue;
                }
                final Cell cell = rowItem.getCell(c + columnOffset);
                if (cell == null) {
                    continue;
                }
                boolean empty;
                try {
                    empty = "".equals(cell.getStringCellValue().trim());
                } catch (IllegalStateException e) {
                    empty = false;
                }
                if (!empty) {
                    foundAny = true;
                    emptyRows[r] = false;
                    emptyColumns[c] = false;
                }
            }
        }
    }
    if (!foundAny) {
        throw new UserError(callingOperator, 302, configuration.getFile().getPath(),
                "spreadsheet seems to be empty");
    }

    // retrieve attribute names: first count columns
    int numberOfAttributes = 0;
    List<Integer> nonEmptyColumnsList = new LinkedList<>();
    for (int i = 0; i < totalNumberOfColumns; i++) {
        if (!emptyColumns[i]) {
            numberOfAttributes++;
            nonEmptyColumnsList.add(i);
        }
    }

    // retrieve or generate attribute names
    attributeNames = new String[nonEmptyColumnsList.size()];

    if (!configuration.isEmulatingOldNames()) {
        for (int i = 0; i < numberOfAttributes; i++) {
            attributeNames[i] = Tools.getExcelColumnName(nonEmptyColumnsList.get(i));
        }
    } else {
        // emulate old 5.0.x style
        for (int i = 0; i < numberOfAttributes; i++) {
            if (!emptyColumns[i]) {
                attributeNames[i] = "attribute_" + i;
            }
        }
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »