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.google.gdt.handler.impl.ExcelHandler.java

License:Open Source License

/**
 * /*from ww w  .j a  va 2s.  com*/
 * @param inputFile
 * @throws IOException
 * @throws InvalidFormatException
 */
@Override
public void handle(String inputFile, ProgressLevel pLevel) throws IOException, InvalidFormatException {
    String outPutFile = getOuputFileName(inputFile);
    OutputStream outputStream = new FileOutputStream(outPutFile);
    InputStream is = new FileInputStream(inputFile);

    Workbook wb = WorkbookFactory.create(is);
    List<Sheet> sheets = getSheets(wb);

    pLevel.setTrFileName(outPutFile);

    //iterate over sheet
    for (int index = 0; index < sheets.size(); index++) {
        Sheet sheet = sheets.get(index);

        if (sheets.size() > 1) {
            pLevel.setString("Translating sheet " + (index + 1) + "/" + sheets.size());
        }

        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();
        int rowCount = lastRowNum - firstRowNum;
        // check for empty sheet, don't perform any operation
        if (rowCount == 0) {
            continue;
        }
        pLevel.setValue(0);
        pLevel.setMaxValue(rowCount);
        pLevel.setStringPainted(true);

        int pBarUpdate = 0;
        //iterate over row
        for (Row row : sheet) {
            //iterate over cells
            for (Cell cell : row) {
                if (isInterrupted) {
                    outputStream.close();
                    new File(outPutFile).delete();
                    pLevel.setString("cancelled");
                    return;
                }

                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    String inputText = cell.getStringCellValue();
                    String translatedTxt = inputText;
                    try {
                        translatedTxt = translator.translate(inputText);
                        cell.setCellValue(translatedTxt);
                    } catch (Exception e) {
                        logger.log(Level.SEVERE,
                                "Input File : " + inputFile + " cannot translate the text : " + inputText, e);
                        continue;
                    }
                }
            } //cell iteration ends
            pBarUpdate++;
            pLevel.setValue(pBarUpdate);
        } //row iteration ends
        pLevel.setValue(rowCount);
    }
    pLevel.setString("done");

    wb.write(outputStream);
    outputStream.close();
}

From source file:com.googlecode.testcase.annotation.handle.toexcel.strategy.ToHtmlWithExcel.java

License:Apache License

/**
 * Creates a new converter to HTML for the given workbook.  This attempts to
 * detect whether the input is XML (so it should create an {@link
 * XSSFWorkbook} or not (so it should create an {@link HSSFWorkbook}).
 *
 * @param in     The input stream that has the workbook.
 * @param output Where the HTML output will be written.
 *
 * @return An object for converting the workbook to HTML.
 *///ww w  . j  a va 2s.c  o  m
private static ToHtmlWithExcel create(InputStream in, Appendable output) throws IOException {
    try {
        Workbook wb = WorkbookFactory.create(in);
        return new ToHtmlWithExcel(wb, output);
    } catch (InvalidFormatException e) {
        throw new IllegalArgumentException("Cannot create workbook from stream", e);
    }
}

From source file:com.hurence.logisland.processor.excel.ExcelExtract.java

License:Apache License

/**
 * Extract every matching sheet from the raw excel input stream.
 *
 * @param inputStream an inputstream that will be closed once consumed.
 * @return a stream of {@link Record} each containing the stream raw data.
 *///  w  w w  .  j a v a  2 s.c o  m
private Stream<Record> handleExcelStream(InputStream inputStream) {
    List<Record> ret = new ArrayList<>();
    try {
        try (Workbook workbook = WorkbookFactory.create(inputStream)) {
            Iterator<Sheet> iter = workbook.sheetIterator();
            while (iter.hasNext()) {
                String sheetName = "unknown";
                List<String> headerNames = null;

                try {
                    Sheet sheet = iter.next();
                    sheetName = sheet.getSheetName();
                    if (toBeSkipped(sheetName)) {
                        LOGGER.info("Skipped sheet {}", sheetName);
                        continue;
                    }
                    LOGGER.info("Extracting sheet {}", sheetName);
                    int count = 0;
                    for (Row row : sheet) {
                        if (row == null) {
                            continue;
                        }
                        if (configuration.getHeaderRowNumber() != null
                                && configuration.getHeaderRowNumber().equals(row.getRowNum())) {
                            headerNames = extractFieldNamesFromRow(row);

                        }
                        if (count++ < configuration.getRowsToSkip()) {
                            continue;
                        }
                        Record current = handleRow(row, headerNames);
                        current.setField(Fields.rowNumber(row.getRowNum()))
                                .setField(Fields.sheetName(sheetName));
                        ret.add(current);
                    }

                } catch (Exception e) {
                    LOGGER.error("Unrecoverable exception occurred while processing excel sheet", e);
                    ret.add(new StandardRecord().addError(ProcessError.RECORD_CONVERSION_ERROR.getName(),
                            String.format("Unable to parse sheet %s: %s", sheetName, e.getMessage())));
                }
            }
        }
    } catch (InvalidFormatException | NotOfficeXmlFileException ife) {
        LOGGER.error("Wrong or unsupported file format.", ife);
        ret.add(new StandardRecord().addError(ProcessError.INVALID_FILE_FORMAT_ERROR.getName(),
                ife.getMessage()));
    } catch (IOException ioe) {
        LOGGER.error("I/O Exception occurred while processing excel file", ioe);
        ret.add(new StandardRecord().addError(ProcessError.RUNTIME_ERROR.getName(), ioe.getMessage()));

    } finally {
        IOUtils.closeQuietly(inputStream);
    }
    return ret.stream();
}

From source file:com.hurry.excel.html.Excel2Html.java

License:Apache License

/**
 * Creates a new converter to HTML for the given workbook. This attempts to detect whether the input is XML (so it should create an {@link XSSFWorkbook} or
 * not (so it should create an {@link HSSFWorkbook} ).
 * /*from w  w  w  . ja v  a  2 s.  c om*/
 * @param in
 *            The input stream that has the workbook.
 * @param output
 *            Where the HTML output will be written.
 * 
 * @return An object for converting the workbook to HTML.
 */
public static Excel2Html create(InputStream in, Appendable output) throws IOException {
    try {
        Workbook wb = WorkbookFactory.create(in);
        return create(wb, output);
    } catch (InvalidFormatException e) {
        throw new IllegalArgumentException("Cannot create workbook from stream", e);
    }
}

From source file:com.ibm.db2j.GExcel.java

License:Open Source License

/**
 * Initialize the attributes :/*from   w  w  w.  ja  v a 2 s. co  m*/
 * 
 * - inputStream
 * - workbook
 * - evaluator
 * - sheet
 * - firstRowIsMetaData
 * 
 * - firstColumnIndex
 * - firstRowIndex
 * - lastColumnIndex
 * - lastRowIndex
 * 
 * @param fileName
 * @param spreadsheetName
 * @param firstCellRange
 * @param lastCellRange
 * @param interpretFirstLineAsMetaData
 * @throws SQLException
 */
public void initialize(String fileName, String spreadsheetName, String firstCellRange, String lastCellRange,
        boolean interpretFirstLineAsMetaData) throws SQLException {
    try {
        inputStream = new FileInputStream(fileName);
        workbook = WorkbookFactory.create(inputStream);
        evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        sheet = findSpreadsheet(workbook, spreadsheetName);
        firstRowIsMetaData = interpretFirstLineAsMetaData;

        if (firstCellRange != null && lastCellRange != null) {
            CellReference firstCell = new CellReference(firstCellRange);

            // Deduce last row number if it was not specified
            if (lastCellRange.matches("[a-zA-Z]+")) {
                lastCellRange += (sheet.getLastRowNum() + 1); //Note: getLastRowNum is 0-based
                stopScanOnFirstEmptyRow = true;
                logger.logInfo("Deduced last row in Excel table: " + lastCellRange
                        + " - but scans will end on first empty row");
            }

            CellReference lastCell = new CellReference(lastCellRange);

            firstColumnIndex = firstCell.getCol();
            firstRowIndex = firstCell.getRow(); // + (firstRowIsMetaData?1:0);
            lastColumnIndex = lastCell.getCol();
            lastRowIndex = lastCell.getRow();
        } else {
            Row firstRow = locateFirstRow(sheet);

            if (firstRow == null) {
                throw new SQLException("Empty spreadsheet !");
            }

            firstRowIndex = firstRow.getRowNum(); // + (firstRowIsMetaData?1:0);
            lastRowIndex = sheet.getLastRowNum();
            firstColumnIndex = firstRow.getFirstCellNum(); //Note: getFirstCellNum is 0-based
            lastColumnIndex = firstRow.getLastCellNum() - 1; //Note: getLastCellNum is 1-based
        }

        //System.out.println("sheet: " + sheet.getSheetName() + ", firstcolindex: " + firstColumnIndex + ", lastcolindex: " + lastColumnIndex + ", firstrowindex: " + firstRowIndex + ", lastrowindex: " + lastRowIndex);
    } catch (Exception e) {
        throw new SQLException(e.getMessage());
    }
}

From source file:com.ifeng.vdn.ip.repository.app.IPCheckApp.java

License:Apache License

public static void main(String[] args) {
    Workbook wb = null;/*from  w w  w.ja v a2 s.c o m*/
    PrintWriter pw = null;
    try {

        pw = new PrintWriter(new FileOutputStream("src/test/resources/data/CDN_BAD.txt"), true);

        AliIPAddressChecker ipChecker = new AliIPAddressChecker();

        wb = WorkbookFactory.create(new FileInputStream("src/test/resources/data/CDN_BAD.xlsx"));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();
        Cell ipCell = null;
        //Cell locCell = null;

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

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);
            ipCell = row.getCell(0);

            ips.add(ipCell.getStringCellValue());
        }

        for (String ip : ips) {
            AliIPBean bean = (AliIPBean) ipChecker.ipcheck(ip);
            pw.println(ip + "-" + bean.getIpString());
        }

    } catch (InvalidFormatException | IOException e) {
        e.printStackTrace();
    } finally {
        if (wb != null)
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        if (pw != null) {
            pw.flush();
            pw.close();
        }
    }

}

From source file:com.ifeng.vdn.ip.repository.service.impl.AliBatchIPAddressCheckerTest.java

License:Apache License

@Test
public void wrap() {
    Workbook wb = null;//w w w  .  j a v a  2 s.  c om
    try {
        BatchIPAddressChecker<AliIPBean> checker = new AliBatchIPAddressChecker();

        wb = WorkbookFactory.create(new FileInputStream("src/test/resources/data/ip_18.xlsx"));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();
        Cell ipCell = null;
        Cell locCell = null;

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

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);
            ipCell = row.getCell(1);

            ips.add(ipCell.getStringCellValue());
        }

        List<AliIPBean> locations = checker.check(ips);
        String location = "";

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);
            locCell = row.getCell(3);

            try {
                location = locations.get(i).getIpString();
            } catch (Exception e) {
                location = "ERROR";
            }

            locCell.setCellValue(location);
        }

        /*if(location.length() == (totalRows - 1)){
                   
        }else {
           log.error("Batch executed error");
           throw new RuntimeException("Batch executed error. Some one IP location not be checked.");
        }*/

        wb.write(new FileOutputStream("src/test/resources/data/ip_18_Alibaba.xlsx"));

    } catch (InvalidFormatException | IOException e) {
        e.printStackTrace();
    } finally {
        if (wb != null)
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
    }

}

From source file:com.ifeng.vdn.ip.repository.service.impl.AliDataFactoryTest.java

License:Apache License

@Test
public void importDataFromExcel() {
    Workbook wb = null;//from  ww  w . j  a v a 2 s .co m
    String input = "src/test/resources/data/ip_18_Alibaba.xlsx";

    try {

        List<IPModel> ipList = new ArrayList<IPModel>();

        wb = WorkbookFactory.create(new FileInputStream(input));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();

        Cell ipCell = null;
        Cell totalCell = null;
        Cell locCell = null;

        String location = "";
        int total;
        String ipString = "";

        String[] items = null;

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);

            ipCell = row.getCell(1);
            totalCell = row.getCell(2);
            locCell = row.getCell(3);

            try {
                String country = "";
                String area = "";
                String region = "";
                String city = "";
                String isp = "";
                IPModel model = new IPModel();

                ipString = ipCell.getStringCellValue();
                total = (int) totalCell.getNumericCellValue();
                location = locCell.getStringCellValue();

                items = location.split(" ");
                if (items != null) {
                    if (items[0] != null) {
                        country = items[0];
                    }
                    if (items[1] != null) {
                        area = items[1];
                    }
                    if (items[2] != null) {
                        region = items[2];
                    }
                    if (items[3] != null) {
                        city = items[3];
                    }
                    if (items[4] != null) {
                        isp = items[4];
                    }

                    model.setIp(ipString);
                    model.setCountry(country.trim());
                    model.setArea(area.trim());
                    model.setRegion(region.trim());
                    model.setCity(city.trim());
                    model.setIsp(isp.trim());
                    model.setTotal(total);

                    ipList.add(model);
                }

                log.info("IP: {}, Total{}, location: {}", ipString, total, location);

                if ((i % 1000) == 0) {
                    aliDataFactory.importData(ipList);
                    ipList = new ArrayList<IPModel>();
                }

            } catch (Exception e) {
                e.getMessage();
                //log.error(e.getMessage(), e);

            }

        }

        if (ipList.size() > 0) {
            aliDataFactory.importData(ipList);
        }

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

}

From source file:com.ifeng.vdn.ip.repository.service.impl.AliIPAddressCheckerTest.java

License:Apache License

public void wrap(String input, String output) {
    Workbook wb = null;//from   ww w  .  j av  a2s . c o m
    PrintWriter pw = null;
    try {
        pw = new PrintWriter(output, "UTF-8");

        IPAddressChecker checker = new AliIPAddressChecker();
        wb = WorkbookFactory.create(new FileInputStream(input));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();

        Cell ipCell = null;
        Cell locCell = null;

        String location = "";
        String ipString = "";

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);

            ipCell = row.getCell(1);
            locCell = row.getCell(3);

            try {
                ipString = ipCell.getStringCellValue();
                AliIPBean ip = (AliIPBean) checker.ipcheck(ipString);
                location = ip.getIpString();
            } catch (Exception e) {
                log.error(e.getMessage(), e);
                location = "ERROR";
            } finally {
                //locCell.setCellValue(location);

                pw.append(ipString + " " + location);
                pw.println();
            }
        }

        wb.write(new FileOutputStream(output));

    } catch (InvalidFormatException | IOException e) {
        log.error(e.getMessage(), e);
    } finally {
        if (wb != null)
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        pw.flush();
        pw.close();
    }
}

From source file:com.ifeng.vdn.ip.repository.service.impl.IPAddressBaiduCheckerTest.java

License:Apache License

@Test
public void wrap() {

    Workbook wb = null;//from  ww w  .  j  av a  2s  .  c  om

    try {
        IPAddressBaiduChecker checker = new IPAddressBaiduChecker();

        wb = WorkbookFactory.create(new FileInputStream("src/test/resources/data/ip_16.xlsx"));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();

        Cell ipCell = null;
        Cell locCell = null;

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);

            ipCell = row.getCell(1);
            locCell = row.getCell(3);

            BaiduIPBean ip = (BaiduIPBean) checker.ipcheck(ipCell.getStringCellValue());
            List<IPLocation> locations = ip.getData();

            if (locations != null && locations.size() > 0) {

                try {
                    locCell.setCellValue(locations.get(0).getLocation());
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

        }

        wb.write(new FileOutputStream("src/test/resources/data/ip_16_loc.xlsx"));

    } catch (InvalidFormatException | IOException e) {
        e.printStackTrace();
    } finally {
        if (wb != null)
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
    }

}