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:eu.learnpad.ontology.kpi.data.ExcelParser.java

public String getSPARQLQuery() throws IOException, InvalidFormatException {
    Boolean foundSparqlQuery = false;

    Workbook wb = WorkbookFactory.create(excelFile);

    for (Sheet sheet : wb) {
        if (sheet.getSheetName().equals(SHEETNAME)) {
            for (Row row : sheet) {
                for (Cell cell : row) {
                    if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                        continue;
                    }/*  w ww.ja  v a2 s  .c o m*/
                    if (!foundSparqlQuery && cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        if (cell.getRichStringCellValue().getString().equals(QUERYCELLNAME)) {
                            foundSparqlQuery = true;
                            continue;
                        }
                    }
                    if (foundSparqlQuery && cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        return cell.getRichStringCellValue().getString();
                    }
                }
            }
        }
    }
    return null;
}

From source file:eu.learnpad.ontology.kpi.data.ExcelParser.java

public List<List<String>> getDataTable() throws IOException, InvalidFormatException {
    List<List<String>> dataTable = new ArrayList<>();
    Integer rowNumber = -2;/* w w w . j  a va2s . co  m*/

    Workbook wb = WorkbookFactory.create(excelFile);

    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    for (Sheet sheet : wb) {
        if (sheet.getSheetName().equals(SHEETNAME)) {
            for (Row row : sheet) {
                //stop with the first empty row
                if (row.getCell(0) == null) {
                    break;
                }
                if (rowNumber >= -1) {
                    rowNumber++;
                    dataTable.add(new ArrayList<String>());
                }
                for (Cell cell : row) {
                    String sheetName = sheet.getSheetName();
                    String cellRow = "Row:" + cell.getRowIndex();
                    String cellColumn = "Column:" + cell.getColumnIndex();
                    Object[] o = new Object[] { sheetName, cellRow, cellColumn };
                    LOGGER.log(Level.INFO, "Processing: Sheet={0} celladress={1}", o);
                    if (rowNumber <= -1 && cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                        continue;
                    }
                    if (rowNumber == -2 && cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        if (cell.getRichStringCellValue().getString().equals(DATACELLNAME)) {
                            rowNumber = -1;
                            continue;
                        }
                    }
                    //Attributes (column headers)
                    if (rowNumber == 0) {
                        dataTable.get(rowNumber).add(cell.getRichStringCellValue().getString());
                    }

                    if (rowNumber >= 1) {

                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            dataTable.get(rowNumber).add(cell.getRichStringCellValue().getString());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                dataTable.get(rowNumber).add(cell.getDateCellValue().toString());
                            } else {
                                dataTable.get(rowNumber).add(Double.toString(cell.getNumericCellValue()));
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            dataTable.get(rowNumber).add(Boolean.toString(cell.getBooleanCellValue()));
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            switch (cell.getCachedFormulaResultType()) {
                            case Cell.CELL_TYPE_STRING:
                                dataTable.get(rowNumber).add(cell.getRichStringCellValue().getString());
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    dataTable.get(rowNumber).add(cell.getDateCellValue().toString());
                                } else {
                                    dataTable.get(rowNumber).add(Double.toString(cell.getNumericCellValue()));
                                }
                                break;
                            case Cell.CELL_TYPE_BOOLEAN:
                                dataTable.get(rowNumber).add(Boolean.toString(cell.getBooleanCellValue()));
                                break;
                            default:
                                dataTable.get(rowNumber).add("");
                            }
                            break;
                        default:
                            dataTable.get(rowNumber).add("");
                        }
                    }
                }
            }
        }
    }

    return dataTable;
}

From source file:excel.Reader.java

public Reader(File excel) {
    System.out.println("CONSTRUCTOR");
    wb = null;//w w  w.  j  a  v a2s. c  o m
    try {
        wb = WorkbookFactory.create(excel);
    } catch (IOException e) {
        System.out.println("IO Exception");
        System.out.println(e.getMessage());
    } catch (InvalidFormatException e) {
        System.out.println("Invalid Format");
        System.out.println(e.getMessage());
    }
}

From source file:ExcelCompare2.POISpreadSheet.java

License:Open Source License

private static Workbook loadSpreadSheet(String file) throws Exception {
    // assume file is excel by default
    Exception readException;/*from   www  .  ja v  a 2s.c om*/
    try {
        Workbook workbook = WorkbookFactory.create(new File(file));
        return workbook;
    } catch (IOException | InvalidFormatException | EncryptedDocumentException e) {
        readException = e;
    }
    throw new RuntimeException("Failed to read as excel file: " + file, readException);
}

From source file:exceloperator.ExcelOperator.java

/**
 *
 * @param filePath : //w w w  .jav a 2 s.c o m
 * @param args  
 * @throws IOException
 * @throws InvalidFormatException  ?
 */
public void WriteExcel(String filePath, String[] args) throws IOException, InvalidFormatException {
    try {
        inp = new FileInputStream(filePath);
        wb = WorkbookFactory.create(inp);
        sheet = wb.getSheetAt(0);
        Row row = sheet.createRow(0);
        Cell cell;
        for (int i = 0; i < 20; i++) {
            cell = row.createCell(i);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(args[i]);
        }
        OutputStream out = new FileOutputStream(filePath);
        //fileOut = new FileOutputStream(filePath);
        wb.write(out);
    } catch (InvalidFormatException e) {
        System.out.println("WriteExcel() ERRO in tag\n");
    }
}

From source file:exceloperator.ExcelOperator.java

public void WriteExcel(String filePath, String cont, int r) {
    String[] couple = cont.split(" ");
    try {/*from   w  w  w  .j  a v a  2s  .co m*/
        inp = new FileInputStream(filePath);
        wb = WorkbookFactory.create(inp);
        sheet = wb.getSheetAt(0);
        Row row = sheet.createRow(r);
        Cell cell;
        cell = row.createCell(0);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(couple[0]);
        cell = row.createCell(1);
        cell = row.createCell(2);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(couple[1]);
        cell = row.createCell(3);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(couple[2]);
        cell = row.createCell(4); // ???
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(couple[5]);
        cell = row.createCell(5); // ??
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(couple[3]);
        cell = row.createCell(6);
        cell = row.createCell(7); // ??
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(couple[6]);
        cell = row.createCell(8);
        cell = row.createCell(9);
        cell = row.createCell(10); // 
        cell.setCellType(Cell.CELL_TYPE_STRING);
        if (couple[5].length() > 13) {
            cell.setCellValue(couple[5].substring(6, 10) + "-" + couple[5].substring(10, 12) + "-"
                    + couple[5].substring(12, 14));
        } else {
            cell.setCellValue("erro");
        }
        cell = row.createCell(11); // ??
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(couple[7]);

        cell = row.createCell(12); // ???
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(couple[14]);
        cell = row.createCell(13); // ??
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(couple[12]);
        cell = row.createCell(0xe);
        cell = row.createCell(15); // ??
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(couple[15]);
        cell = row.createCell(16);
        cell = row.createCell(17);
        cell = row.createCell(18); // 
        cell.setCellType(Cell.CELL_TYPE_STRING);
        if (couple[14].length() > 13) {
            cell.setCellValue(couple[14].substring(6, 10) + "-" + couple[14].substring(10, 12) + "-"
                    + couple[14].substring(12, 14));
        } else {
            cell.setCellValue("erro");
        }
        cell = row.createCell(19); // ??
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(couple[16]);
        fileOut = new FileOutputStream(filePath);
        wb.write(fileOut);
        fileOut.close();
    } catch (IOException e) {
        System.out.print("Excel.Write() ERRO, Exist IOException in " + couple[0] + " line\n");
        // e.printStackTrace();
    } catch (InvalidFormatException e) {
        System.out.print("Excel.Write() ERRO, ExistInvalidFormatException in " + couple[0] + " line\n");
        // e.printStackTrace();
    }
}

From source file:fi.hsl.parkandride.itest.AbstractReportingITest.java

License:EUPL

protected Workbook readWorkbookFrom(Response whenPostingToReportUrl) {
    try (ByteArrayInputStream bais = new ByteArrayInputStream(whenPostingToReportUrl.asByteArray())) {
        return WorkbookFactory.create(bais);
    } catch (IOException | InvalidFormatException e) {
        e.printStackTrace();/*from   www.j a v a 2 s.  com*/
        throw new AssertionFailedError(e.getMessage());
    }
}

From source file:fi.semantum.strategia.widget.Database.java

License:Open Source License

public static Database load(Main main, String databaseId) {

    Database result = null;/* w w w.jav a 2 s .c o  m*/

    synchronized (Database.class) {

        try {

            Map<String, EnumerationDatatype> enumerations = new HashMap<String, EnumerationDatatype>();

            try {
                File file = new File(Main.baseDirectory(), "database.xlsx");
                FileInputStream fis = new FileInputStream(file);
                Workbook book = WorkbookFactory.create(fis);
                fis.close();
                Sheet sheet = book.getSheetAt(0);
                for (int rowN = sheet.getFirstRowNum(); rowN <= sheet.getLastRowNum(); rowN++) {
                    Row row = sheet.getRow(rowN);
                    Cell cell = row.getCell(0, Row.RETURN_BLANK_AS_NULL);
                    if (cell != null) {
                        if ("Monivalinta".equals(cell.toString())) {
                            Cell id = row.getCell(1, Row.RETURN_BLANK_AS_NULL);
                            if (id == null)
                                continue;
                            Cell traffic = row.getCell(2, Row.RETURN_BLANK_AS_NULL);
                            if (traffic == null)
                                continue;
                            int count = row.getLastCellNum() - 3;
                            if (traffic.toString().length() != count)
                                continue;

                            List<String> values = new ArrayList<String>();
                            for (int i = 0; i < count; i++) {
                                Cell val = row.getCell(3 + i, Row.RETURN_BLANK_AS_NULL);
                                if (val != null)
                                    values.add(val.toString());
                            }
                            enumerations.put(id.toString(),
                                    new EnumerationDatatype(result, id.toString(), values, traffic.toString()));

                        }
                    }
                }

            } catch (Exception e) {
            }

            File f = new File(Main.baseDirectory(), databaseId);
            FileInputStream fileIn = new FileInputStream(f);
            ObjectInputStream in = new ObjectInputStream(fileIn);
            result = (Database) in.readObject();
            in.close();
            fileIn.close();

            result.databaseId = databaseId;

            main.setDatabase(result);

            migrate(main, enumerations);
            validate(main);

            result.lastModified = new Date(f.lastModified());

        } catch (IOException i) {

            i.printStackTrace();
            result = create(main, databaseId);

        } catch (ClassNotFoundException c) {

            System.out.println("Database class not found");
            c.printStackTrace();
            result = create(main, databaseId);

        }

        result.touchBackup();

        result.updateTags();

        try {

            if (!Lucene.indexExists(databaseId)) {

                Lucene.startWrite(databaseId);
                for (Base b : result.enumerate()) {
                    Lucene.set(databaseId, b.uuid, b.searchText(result));
                }
                Lucene.endWrite();

            }

        } catch (Throwable t) {

            t.printStackTrace();

        }

    }

    return result;

}

From source file:fll.util.ExcelCellReader.java

License:Open Source License

private static Workbook createWorkbook(final InputStream file) throws IOException, InvalidFormatException {
    try (final InputStream stream = new PushbackInputStream(file)) {
        final Workbook workbook = WorkbookFactory.create(stream);
        return workbook;
    }/* ww  w .  j  av a  2  s. c om*/
}

From source file:fr.sc.crator.internal.storage.CRAStorageHandlerImpl.java

License:Open Source License

/**
 * {@inheritDoc}/*from  w ww  .  j  a  va 2s .co m*/
 * @see fr.sc.crator.storage.CRAStorageHandler#readCRA(fr.sc.crator.model.CRAtor, int, java.lang.String)
 */
@Override
public CRA readCRA(CRAtor crator, int weekNumber, String source) {
    try {
        FileInputStream file = new FileInputStream(new File(source));
        logger.log(CRAtorLogger.LOG_DEBUG, "Trying to read file " + source);
        Workbook wb = WorkbookFactory.create(file);
        file.close();
        Sheet sheet = wb.getSheetAt(0);
        logger.log(CRAtorLogger.LOG_DEBUG, "Instanciating a SpreadCRA");
        SpreadCRA cra = CratorFactory.eINSTANCE.createSpreadCRA();
        cra.setSheet(sheet);
        cra.setSource(source);
        cra.setWeekNumber(weekNumber);
        CRAWeek week = CratorFactory.eINSTANCE.createCRAWeek();
        double workedDay = sheet.getRow(1).getCell(1).getNumericCellValue();
        week.setWorkedLoad(workedDay);
        cra.setWeek(week);
        logger.log(CRAtorLogger.LOG_DEBUG, "Loading existing data");
        loadWeek(crator, cra);
        return cra;
    } catch (InvalidFormatException e) {
        logger.log(CRAtorLogger.LOG_ERROR,
                "An error occured during loading CRA of " + source + " file. Error: " + e.getMessage());
    } catch (IOException e) {
        logger.log(CRAtorLogger.LOG_ERROR,
                "An error occured during loading CRA of " + source + " file. Error: " + e.getMessage());
    }
    VoidCRA result = CratorFactory.eINSTANCE.createVoidCRA();
    crator.getCras().add(result);
    return result;
}