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:com.wabacus.system.dataimport.filetype.XlsFileProcessor.java

License:Open Source License

public void init(File datafile) {
    try {/*from w  w w .ja v a  2  s  . c o m*/
        bis = new BufferedInputStream(new FileInputStream(datafile));
        workbook = WorkbookFactory.create(bis);
        String sheet = xlsConfigBean.getSheet();
        if (sheet == null || sheet.trim().equals("")) {
            sheetObj = workbook.getSheetAt(0);
        } else if (Tools.isDefineKey("index", sheet)) {
            sheetObj = workbook.getSheetAt(Integer.parseInt(Tools.getRealKeyByDefine("index", sheet)));
        } else {
            sheetObj = workbook.getSheet(sheet);
        }
        if (sheetObj == null) {
            throw new WabacusDataImportException(
                    "?" + datafile.getAbsolutePath() + "?sheet");
        }
        layoutObj.init();
    } catch (FileNotFoundException e) {
        throw new WabacusDataImportException(
                "??" + datafile.getAbsolutePath(), e);
    } catch (IOException ioe) {
        throw new WabacusDataImportException("?" + datafile.getAbsolutePath() + "", ioe);
    } catch (InvalidFormatException e) {
        throw new WabacusDataImportException(
                "??" + datafile.getAbsolutePath() + "??", e);
    }
}

From source file:com.wantdo.stat.excel.poi_src.formula.CheckFunctionsSupported.java

License:Apache License

public static void main(String[] args) throws Exception {
    if (args.length < 1) {
        System.err.println("Use:");
        System.err.println("  CheckFunctionsSupported <filename>");
        return;/*  w w  w  .j  a v a  2 s  .  c  o  m*/
    }

    Workbook wb = WorkbookFactory.create(new File(args[0]));
    CheckFunctionsSupported check = new CheckFunctionsSupported(wb);

    // Fetch all the problems
    List<FormulaEvaluationProblems> problems = new ArrayList<FormulaEvaluationProblems>();
    for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) {
        problems.add(check.getEvaluationProblems(sn));
    }

    // Produce an overall summary
    Set<String> unsupportedFunctions = new TreeSet<String>();
    for (FormulaEvaluationProblems p : problems) {
        unsupportedFunctions.addAll(p.unsupportedFunctions);
    }
    if (unsupportedFunctions.isEmpty()) {
        System.out.println("There are no unsupported formula functions used");
    } else {
        System.out.println("Unsupported formula functions:");
        for (String function : unsupportedFunctions) {
            System.out.println("  " + function);
        }
        System.out.println("Total unsupported functions = " + unsupportedFunctions.size());
    }

    // Report sheet by sheet
    for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) {
        String sheetName = wb.getSheetName(sn);
        FormulaEvaluationProblems probs = problems.get(sn);

        System.out.println();
        System.out.println("Sheet = " + sheetName);

        if (probs.unevaluatableCells.isEmpty()) {
            System.out.println(" All cells evaluated without error");
        } else {
            for (CellReference cr : probs.unevaluatableCells.keySet()) {
                System.out.println(
                        " " + cr.formatAsString() + " - " + probs.unevaluatableCells.get(cr).toString());
            }
        }
    }
}

From source file:com.wantdo.stat.excel.poi_src.formula.UserDefinedFunctionExample.java

License:Apache License

public static void main(String[] args) {

    if (args.length != 2) {
        System.out.println("usage: UserDefinedFunctionExample fileName cellId");
        return;// w  w w.java 2  s  .co  m
    }

    System.out.println("fileName: " + args[0]);
    System.out.println("cell: " + args[1]);

    File workbookFile = new File(args[0]);

    try {
        FileInputStream fis = new FileInputStream(workbookFile);
        Workbook workbook = WorkbookFactory.create(fis);
        fis.close();

        String[] functionNames = { "calculatePayment" };
        FreeRefFunction[] functionImpls = { new CalculateMortgage() };

        UDFFinder udfToolpack = new DefaultUDFFinder(functionNames, functionImpls);

        // register the user-defined function in the workbook
        workbook.addToolPack(udfToolpack);

        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

        CellReference cr = new CellReference(args[1]);
        String sheetName = cr.getSheetName();
        Sheet sheet = workbook.getSheet(sheetName);
        int rowIdx = cr.getRow();
        int colIdx = cr.getCol();
        Row row = sheet.getRow(rowIdx);
        Cell cell = row.getCell(colIdx);

        CellValue value = evaluator.evaluate(cell);

        System.out.println("returns value: " + value);

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

From source file:com.waveconn.Excel2MySQL.java

License:Apache License

void dbImport() {
    FileInputStream excel_file = null;
    try {/*from  w  w  w  . ja  v a 2s . c o  m*/
        excel_file = new FileInputStream(new File(excel_file_path));
    } catch (FileNotFoundException e) {
        System.out.println("File not found: " + excel_file_path);
        System.exit(-3);
    }

    try {
        workbook = WorkbookFactory.create(excel_file);
        evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        formatter = new DataFormatter(true);

        Sheet sheet = null;
        Row row = null;
        int lastRowNum = 0;

        System.out.println("Reading excel file content from " + excel_file_path);

        // Discover how many sheets there are in the workbook....
        int numSheets = workbook.getNumberOfSheets();

        // and then iterate through them.
        for (int i = 0; i < numSheets; i++) {

            // Get a reference to a sheet and check to see if it contains any rows.
            sheet = workbook.getSheetAt(i);
            if (sheet.getPhysicalNumberOfRows() > 0) {

                // Note down the index number of the bottom-most row and
                // then iterate through all of the rows on the sheet starting
                // from the very first row - number 1 - even if it is missing.
                // Recover a reference to the row and then call another method
                // which will strip the data from the cells and build lines
                lastRowNum = sheet.getLastRowNum();

                int start = 0;
                if (!is_read_first_line)
                    start = 1;

                for (int j = start; j <= lastRowNum; j++) {
                    row = sheet.getRow(j);
                    this.rowToData(row);
                }
            }
        }

    } catch (IOException e) {
        e.printStackTrace();
        System.out.println("IOException: " + excel_file_path);
        System.exit(-4);
    } catch (InvalidFormatException e) {
        e.printStackTrace();
        System.out.println("Invalid Format: " + excel_file_path);
        System.exit(-5);
    } finally {
        if (excel_file != null) {
            try {
                excel_file.close();
            } catch (IOException e) {
                e.printStackTrace();
                System.out.println("IOException: " + excel_file_path);
                System.exit(-6);
            }
        }
    }

    //put valid rows into DB
    System.out.println("Inserting valid rows into DB table " + db_url + "/" + db_table);
    insertDB();

    System.out.println();

    //save invalid rows if any
    int errs = errorRows.size();
    if (errs > 0) {
        saveError();
    } else {
        System.out.println("There is no invalid row");
    }
}

From source file:com.wuliu.biz.util.export.strategy.CarIndexExport.java

License:Open Source License

public String export(String folderPath, String templateName, List<WuliuMergedOrderModel> mergedOrders)
        throws Exception {

    List<List<WuliuMergedOrderModel>> mergedOrderLists = split(mergedOrders);

    if (CollectionUtils.isEmpty(mergedOrderLists)) {
        return null;
    }/*from w  w w. j  a v  a  2 s .com*/

    File template = new File(this.getClass().getClassLoader().getResource(templateName).getFile());
    InputStream inp = new FileInputStream(template);
    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(0);
    fillSheet(sheet, mergedOrders);

    File file = new File(folderPath, getName(mergedOrders));
    try {
        FileOutputStream outputStream = new FileOutputStream(file);
        wb.write(outputStream);
        outputStream.flush();
        outputStream.close();
        wb.close();
        System.out.println("success");
    } catch (Exception e) {
        System.out.println("It cause Error on WRITTING excel workbook: ");
        e.printStackTrace();
    }
    return file.getAbsolutePath();
}

From source file:com.wuliu.biz.util.export.strategy.WholeOrderExport.java

License:Open Source License

public String export(String folderPath, String templateName, List<WuliuMergedOrderModel> mergedOrders)
        throws Exception {
    File folder = createFolder(folderPath);
    List<List<WuliuMergedOrderModel>> mergedOrderLists = split(mergedOrders);

    if (CollectionUtils.isEmpty(mergedOrderLists)) {
        return null;
    }/*from  w w w.jav  a 2  s .  co m*/

    for (List<WuliuMergedOrderModel> item : mergedOrderLists) {
        File template = new File(this.getClass().getClassLoader().getResource(templateName).getFile());
        InputStream inp = new FileInputStream(template);
        Workbook wb = WorkbookFactory.create(inp);
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        Sheet sheet = wb.getSheetAt(0);
        fillSheet(sheet, item);
        evaluate(sheet, evaluator);

        File file = new File(folder, getName(item));
        try {
            FileOutputStream outputStream = new FileOutputStream(file);
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
            wb.close();
            System.out.println("success");
        } catch (Exception e) {
            System.out.println("It cause Error on WRITTING excel workbook: ");
            e.printStackTrace();
        }
    }
    return folder.getAbsolutePath();
}

From source file:com.xandrev.altafitcalendargenerator.XLSExtractor.java

public HashMap<Integer, ArrayList<TimeTrack>> importExcelSheet(String fileName) {
    HashMap<Integer, String> tmpHours = new HashMap<Integer, String>();
    HashMap<Integer, ArrayList<TimeTrack>> out = new HashMap<Integer, ArrayList<TimeTrack>>();
    init(out);//from   w  w w  . j  a  v a 2s .  com
    try {
        Workbook workBook = WorkbookFactory.create(new FileInputStream(fileName));
        Sheet sheet = workBook.getSheetAt(0);
        Iterator rowIter = sheet.rowIterator();
        int rowIdx = 0;
        boolean started = false;
        boolean finished = false;
        while (rowIter.hasNext() && !finished) {
            XSSFRow row = (XSSFRow) rowIter.next();
            if (row != null && !started) {
                XSSFCell cell = row.getCell(0);
                if (cell != null) {
                    String value = cell.getStringCellValue();
                    if (value == null || value.isEmpty() || !"HORA".equals(value)) {
                        rowIdx++;
                        started = true;
                        continue;
                    }
                }
                row = (XSSFRow) rowIter.next();
            }

            Iterator<Cell> cellIter = row.cellIterator();
            int cellIndex = 0;
            while (cellIter.hasNext()) {
                XSSFCell cell = (XSSFCell) cellIter.next();
                if (cell != null) {
                    String value = cell.getStringCellValue();
                    installHashMap(tmpHours, out, cellIndex, rowIdx, value);
                }
                cellIndex++;
            }
            rowIdx++;
        }

    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
    return out;
}

From source file:com.xn.interfacetest.service.impl.TestCaseServiceImpl.java

License:Open Source License

private StringBuffer readExcel(String path) throws Exception {
    // ?Excel/*from   w  w  w  . jav  a  2s  .c  o  m*/
    InputStream excelFile = new FileInputStream(path);

    //?????
    StringBuffer failCaseNumbers = new StringBuffer("");
    try {
        Workbook wb = WorkbookFactory.create(new File(path));
        Sheet sheet = wb.getSheetAt(0);
        // 
        int rowNum = sheet.getLastRowNum() + 1;
        logger.info("rowNum" + rowNum);

        //?1
        Row row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells(); //?
        logger.info("colNum" + colNum);

        // 2,
        for (int i = 1; i < rowNum; i++) {
            TestCaseDto caseDto = new TestCaseDto();
            logger.info("??" + i);
            row = sheet.getRow(i);
            //???
            //?---?
            String number = getCellFormatValue(row.getCell(0)) + "";
            //?
            if (!checkCaseNumberUnique(number)) {
                failCaseNumbers.append("?").append(number)
                        .append("??");
                continue;
            }
            caseDto.setNumber(number);

            //?---??
            caseDto.setName(getCellFormatValue(row.getCell(1)) + "");

            //?---??
            caseDto.setDescription(getCellFormatValue(row.getCell(2)) + "");

            //?---?id,?id?id?
            if (StringUtils.isBlank(getCellFormatValue(row.getCell(3)) + "")
                    || !checkInterfaceIdExist(Long.parseLong(getCellFormatValue(row.getCell(3)) + ""))) {
                failCaseNumbers.append("?").append(number)
                        .append("???id")
                        .append(row.getCell(3)).append("");
                continue;
            }
            caseDto.setInterfaceId(Long.parseLong(getCellFormatValue(row.getCell(3)) + ""));

            //5?---?
            caseDto.setCustomParams(getCellFormatValue(row.getCell(4)) + "");
            caseDto.setCustomParamsType(ParamsGroupTypeEnum.CUSTOM.getId());

            //6?---?
            caseDto.setCustomParamsType(AppendParamEnum.getIdByName(getCellFormatValue(row.getCell(5)) + ""));

            //10-
            if ("SINGLE".equals(getCellFormatValue(row.getCell(9)))
                    || "MUTIPLE".equals(getCellFormatValue(row.getCell(9)))) {
                caseDto.setType(getCellFormatValue(row.getCell(9)) + "");
            } else {
                failCaseNumbers.append("?").append(number)
                        .append("???\"MUTIPLE\"\"SINGLE\"");
                continue;
            }

            caseDto = this.save(caseDto);
            logger.info("?" + caseDto.toString());
            //7?---?
            String assertJson = getCellFormatValue(row.getCell(6)) + "";
            if (StringUtils.isNotBlank(assertJson)) {
                try {
                    //??
                    saveParamsAsserts(assertJson, caseDto);
                } catch (Exception e) {
                    logger.error("?", e);
                    failCaseNumbers.append("?").append(number).append(
                            "???,??");
                }
            }
            //8-?
            String prepareStr = getCellFormatValue(row.getCell(7)) + "";
            if (StringUtils.isNotBlank(prepareStr)) {
                try {
                    saveDataOperate(prepareStr, caseDto.getId(), OperationTypeEnum.PREPARE.getId(),
                            failCaseNumbers);
                    caseDto.setDataPrepare(1);
                    //
                    update(caseDto);
                } catch (Exception e) {
                    logger.error("?sql", e);
                    failCaseNumbers.append("?").append(number).append(
                            "????,??"
                                    + e.getMessage() + "");
                }
            }

            //9-?
            String clearStr = getCellFormatValue(row.getCell(8)) + "";
            if (StringUtils.isNotBlank(clearStr)) {
                try {
                    saveDataOperate(clearStr, caseDto.getId(), OperationTypeEnum.CLEAR.getId(),
                            failCaseNumbers);
                    caseDto.setDataClear(1);
                    //
                    update(caseDto);

                } catch (Exception e) {
                    logger.error("?sql", e);
                    failCaseNumbers.append("?").append(number).append(
                            "????,??"
                                    + e.getMessage() + "");
                }
            } else {
                continue;
            }

        }
    } catch (FileNotFoundException e) {
        logger.error("excel", e);
        throw e;
    } catch (IOException e) {
        logger.error("?excel", e);
        throw e;
    } finally {
        return failCaseNumbers;
    }
}

From source file:controllers.TargetController.java

License:Open Source License

private static void excelParser(File inputFile) throws Throwable {

    FileInputStream file = new FileInputStream(inputFile);

    //Create Workbook instance holding reference to .xls[x] file
    Workbook workbook = WorkbookFactory.create(file);

    //Get first/desired sheet from the workbook
    Sheet sheet = workbook.getSheetAt(0);

    // Check total row:
    if (sheet.getPhysicalNumberOfRows() <= 1) {
        throw new Exception("Sheet should have at least one row.");
    }//w  ww . jav a  2s. c  o m
    Logger.debug("Sheet has " + sheet.getPhysicalNumberOfRows() + " rows.");

    //Iterate through each rows one by one
    Iterator<Row> rowIterator = sheet.iterator();

    // Header row:
    Row header = rowIterator.next();
    Logger.debug("HEADER: " + header);
    // TODO Check header row is right.

    // And the rest:
    StringBuilder sb = new StringBuilder();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();

        // Get
        Target target = new Target();
        target.title = row.getCell(0).getStringCellValue();
        target.fieldUrls = new ArrayList<FieldUrl>();
        // Check URL
        FieldUrl url = new FieldUrl(row.getCell(1).getStringCellValue());
        target.fieldUrls.add(url);
        FieldUrl existingFieldUrl = FieldUrl.findByUrl(url.url);
        if (existingFieldUrl != null) {
            String error = "Row # " + row.getRowNum() + ": CONFLICT - URL " + existingFieldUrl.url
                    + " is already part of target " + existingFieldUrl.target.id + "\n";
            Logger.debug(error);
            sb.append(error);
            continue;
        }
        //Collection c = new Collection();
        //c.name = 

        // 
        System.out.println(target);

        // TODO Merge with controllers.ApplicationController.bulkImport() code to avoid repetition.
        target.revision = Const.INITIAL_REVISION;
        target.active = true;

        target.selectionType = Const.SelectionType.SELECTION.name();

        if (target.noLdCriteriaMet == null) {
            target.noLdCriteriaMet = Boolean.FALSE;
        }

        if (target.keySite == null) {
            target.keySite = Boolean.FALSE;
        }

        if (target.ignoreRobotsTxt == null) {
            target.ignoreRobotsTxt = Boolean.FALSE;
        }

        // Save - disabled right now, as we do not want this live as yet.
        /*
        target.runChecks();
        target.save();
        */

        //
        System.out.println(target);
    }
    workbook.close();
    file.close();

    // And report errors
    if (sb.length() > 0) {
        throw (new Exception(sb.toString()));
    }
}

From source file:crygetter.gui.MainWindow.java

/**
 * Loads Cry Data (order affect)/*from w w  w. j a v  a 2s .c o  m*/
 */
private void loadCryData() {

    try {

        Workbook wb = WorkbookFactory.create(getClass().getResourceAsStream("/cryData.xlsx"));
        Sheet sheet = wb.getSheetAt(0);

        Row orderNameRow = sheet.getRow(1);
        cryOrderData = new LinkedHashMap<>();

        for (int i = 2;; i++) {

            Cell orderNameCell = orderNameRow.getCell(i);

            if (orderNameCell != null) {

                List<String> affectList = new ArrayList<>();

                for (int j = 2;; j++) {

                    Row orderValueRow = sheet.getRow(j);

                    if (orderValueRow != null) {

                        Cell cryValueCell = orderValueRow.getCell(1);
                        Cell orderValueCell = orderValueRow.getCell(i);

                        if (orderValueCell != null && !orderValueCell.getStringCellValue().equals("")) {

                            String value = orderValueCell.getStringCellValue();

                            if (value.equals("A") || value.equals("P")) {
                                affectList.add("Cry" + cryValueCell.getStringCellValue());
                            }

                        }

                    } else {
                        break;
                    }

                }

                if (!affectList.isEmpty()) {
                    cryOrderData.put(orderNameCell.getStringCellValue(), affectList);
                }

            } else {
                break;
            }

        }

    } catch (IOException | InvalidFormatException exc) {
        Utils.showExceptionMessage(this, exc);
    }

}