Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook.

Prototype

public XSSFWorkbook(PackagePart part) throws IOException 

Source Link

Document

Constructs a XSSFWorkbook object using Package Part.

Usage

From source file:com.iana.boesc.utility.BOESCUtil.java

License:Open Source License

public static XSSFSheet getWorkSheet(File incoming_file) throws IOException {
    FileInputStream file = new FileInputStream(incoming_file);
    //Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    //Get first/desired sheet from the workbook
    XSSFSheet sheet = workbook.getSheetAt(0);
    return sheet;
}

---

From source file:com.infovity.iep.loader.util.SupplierLoaderUtil.java

public static ArrayList<String[]> getDataFromFile(File inputFile, String sheetName) {
    ArrayList<String[]> data = new ArrayList<String[]>();
    File selectedFile = null;// w  ww.  j ava2s  .  c  om
    FileInputStream fis = null;
    ;
    XSSFWorkbook workbook = null;
    //inputFile = new File("C:\\Users\\INFOVITY-USER-029\\Desktop\\DataLoader\\Consolidated Supplier Data Capture Template v4.0.xlsx");
    boolean sheetFound = false;
    XSSFSheet sheet = null;
    try {

        int sheetIndex = -1;
        fis = new FileInputStream(inputFile);
        workbook = new XSSFWorkbook(fis);

        int noOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < noOfSheets; i++) {
            sheet = workbook.getSheetAt(i);
            if (sheet.getSheetName().equals(sheetName)) {
                sheetFound = true;
                sheetIndex = i;
                selectedFile = inputFile;
                break;
            }
        }
        XSSFWorkbook myWorkBook;

        try {
            myWorkBook = new XSSFWorkbook(selectedFile);
            // Return first sheet from the XLSX workbook
            // XSSFSheet mySheet = myWorkBook.getSheetAt(0);
            // Get iterator to all the rows in current sheet
            Iterator<Row> rowIterator = sheet.iterator();
            String query;
            String[] values = null;
            // Traversing over each row of XLSX file
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                if (rowHasData(row) && (row.getRowNum() >= 9)) {
                    int endColumn = row.getLastCellNum();
                    int startColumn = row.getFirstCellNum();
                    // For each row, iterate through each columns
                    values = new String[endColumn + 2];
                    for (int i = startColumn; i < endColumn; i++) {
                        String cellValue;
                        Cell cell = row.getCell(i);
                        values[0] = Integer.toString(row.getRowNum() + 1);
                        if (cell != null) {
                            if (cell.getCellType() == cell.CELL_TYPE_NUMERIC
                                    && DateUtil.isCellDateFormatted(cell)) {
                                DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
                                Date cellDateValue = cell.getDateCellValue();
                                cellValue = df.format(cellDateValue);
                            } else {
                                cell.setCellType(cell.CELL_TYPE_STRING);
                                cellValue = cell.getStringCellValue().replaceAll("'", "");
                            }
                            if (!"".equals(cellValue) && cellValue != null) {
                                values[i + 1] = cellValue;
                            } else if (cellValue.isEmpty() || "".equals(cellValue)) {
                                values[i + 1] = "";
                            }
                        } else {
                            values[i + 1] = "";
                        }
                    }
                    data.add(values);
                }

            }
        } catch (InvalidFormatException ex) {
            Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex);
        }
    } catch (IOException ex) {
        Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex);
    } finally {
        try {
            fis.close();
            workbook.close();
        } catch (IOException ex) {
            Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex);
        }
    }

    return data;
}

---

From source file:com.inspiracode.nowgroup.scspro.xl.source.ExcelFile.java

License:Open Source License

public List<LogMessage> validateFieldNames() {
    List<LogMessage> result = new ArrayList<LogMessage>();
    FileInputStream fis = null;//from  ww w  . j a v  a  2s.  c  o m
    Workbook wb = null;
    try {
        fis = new FileInputStream(file);
        // Get the workbook instance for XLS file

        if (".xls".equals(file.getName().substring(file.getName().length() - 4))) {
            wb = new HSSFWorkbook(fis);
        } else if ("xlsx".equals(file.getName().substring(file.getName().length() - 4))) {
            wb = new XSSFWorkbook(fis);
        } else {
            throw new IllegalArgumentException("Received file does not have a standard excel extension.");
        }

        // Get each sheet from the workbook
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            result.addAll(validateFieldNames(wb.getSheetAt(i)));
        }

    } catch (Exception e) {
        log.error("Error al validar campos en excel: [" + e.getMessage() + "]", e);
        result.add(new LogMessage("Validacin de encabezados",
                "Error al validar campos en excel: [" + e.getMessage() + "]"));
    } finally {
        try {
            fis.close();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        try {
            wb.close();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
    }

    return result;
}

---

From source file:com.inspiracode.nowgroup.scspro.xl.source.ExcelFile.java

License:Open Source License

public List<LogMessage> readPurchaseOrders() {

    List<LogMessage> result = new ArrayList<LogMessage>();
    FileInputStream fis = null;/*from w  w  w .ja  v a 2s  .c o m*/
    Workbook wb = null;
    try {
        fis = new FileInputStream(file);
        // Get the workbook instance for XLS file

        if (".xls".equals(file.getName().substring(file.getName().length() - 4))) {
            wb = new HSSFWorkbook(fis);
        } else if ("xlsx".equals(file.getName().substring(file.getName().length() - 4))) {
            wb = new XSSFWorkbook(fis);
        } else {
            throw new IllegalArgumentException("Received file does not have a standard excel extension.");
        }

        // Get each sheet from the workbook
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            result.addAll(readPurchaseOrders(wb.getSheetAt(i)));
        }

    } catch (Exception e) {
        log.error("Error al validar campos en excel: [" + e.getMessage() + "]", e);
        result.add(new LogMessage("Validacin de ordenes de compra",
                "Error al validar las Ordenes de compra: [" + e.getMessage() + "]"));
    } finally {
        try {
            fis.close();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        try {
            wb.close();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
    }

    return result;
}

---

From source file:com.jaeksoft.searchlib.parser.XlsxParser.java

License:Open Source License

@Override
protected void parseContent(StreamLimiter streamLimiter, LanguageEnum lang) throws IOException {

    XSSFWorkbook workbook = new XSSFWorkbook(streamLimiter.getNewInputStream());
    XSSFExcelExtractor excelExtractor = null;
    try {/*from   w  ww . j a v a 2s  .  co  m*/
        excelExtractor = new XSSFExcelExtractor(workbook);
        ParserResultItem result = getNewParserResultItem();

        CoreProperties info = excelExtractor.getCoreProperties();
        if (info != null) {
            result.addField(ParserFieldEnum.title, info.getTitle());
            result.addField(ParserFieldEnum.creator, info.getCreator());
            result.addField(ParserFieldEnum.subject, info.getSubject());
            result.addField(ParserFieldEnum.description, info.getDescription());
            result.addField(ParserFieldEnum.keywords, info.getKeywords());
        }

        excelExtractor.setIncludeCellComments(true);
        excelExtractor.setIncludeHeadersFooters(true);
        excelExtractor.setIncludeSheetNames(true);
        String content = excelExtractor.getText();
        result.addField(ParserFieldEnum.content, StringUtils.replaceConsecutiveSpaces(content, " "));

        result.langDetection(10000, ParserFieldEnum.content);
    } finally {
        IOUtils.close(excelExtractor);
    }

}

---

From source file:com.jaspersoft.ireport.designer.connection.gui.XlsxDataSourceConnectionEditor.java

License:Open Source License

private void jButtonGetColumnsActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonGetColumnsActionPerformed
    try {//from   w  ww .  j  a v a 2  s  .c  o m

        if (jTextFieldFilename.getText().length() > 0) {
            Workbook workbook = new XSSFWorkbook(new FileInputStream(new File(jTextFieldFilename.getText())));
            Sheet sheet = workbook.getSheetAt(0);

            DefaultTableModel dtm = (DefaultTableModel) jTable1.getModel();
            dtm.setRowCount(0);

            Row row = sheet.getRow(0);
            Map<String, Integer> columnNames = new HashMap<String, Integer>();
            for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {
                Cell cell = row.getCell(columnIndex);
                String columnName = "";
                if (cell != null) {
                    columnName = cell.toString();
                } else {
                    columnName = "COLUMN_" + columnIndex;
                }

                if (columnName != null && columnName.trim().length() > 0) {
                    dtm.addRow(new Object[] { columnName, new Integer(columnIndex) });
                }
            }

            jTable1.updateUI();

            jCheckBoxFirstRowAsHeader.setSelected(true);
        }
    } catch (Exception ex) {
        JOptionPane.showMessageDialog(this, ex.getMessage(),
                I18n.getString("XlsxDataSourceConnectionEditor.Message.Exception"), JOptionPane.ERROR_MESSAGE); //"message.title.exception"
    }
}

---

From source file:com.jitendrasinghnz.excelreadutility.ExcelReadStringArrayXSLX.java

License:Open Source License

public ExcelReadStringArrayXSLX(String excelFilePath, String worksheetName)
        throws FileNotFoundException, Exception {
    mFilePath = excelFilePath;/*  w w w  .j a  v a 2s  .c  o  m*/
    mWorksheetName = worksheetName;
    mExcelFile = new File(mFilePath);

    if (mExcelFile.exists() && !mExcelFile.isDirectory()) {

        try {
            mFileInputStream = new FileInputStream(mExcelFile);
        } catch (FileNotFoundException fileNotFoundException) {
            System.out.println("File path name is incorrect or file does not exist");
        }
        try {
            mXssfWorkbook = new XSSFWorkbook(mFileInputStream);
            mXssfSheet = mXssfWorkbook.getSheet(worksheetName);
        } catch (IOException ioe) {
            System.out.println("Error in opening file " + mFilePath);
        }

    } else {
        throw new FileNotFoundException("File path name is incorrect or file " + mFilePath + " does not exist\n"
                + "Have you typed the file path name correctly ? e.g.: For Windows user an example file path would be C:\\\\foouser\\\\foodocuments\\\\foofilename.xls or \n if you are GNU/Linux user an example file path would be /foouser/foodocuments/foofilename.xls");
    }
    if (mXssfSheet == null) {
        throw new Exception("Worksheet with name " + mWorksheetName + " does not exist in " + mFilePath);
    }

}

---

From source file:com.joalgoca.validatorLayout.layoutDefinition.XLSXDocumentLayout.java

@Override
public ResponseValidator validateDocument(InputStream inputStream) {
    ResponseValidator response;// ww  w  . jav a  2s. c  om
    StringBuilder stringBuilder = new StringBuilder();
    if (isReadyToValidate() && inputStream != null) {
        HashMap rowsType = new HashMap();
        for (int i = 0; i < documentValidator.getListRowValidator().size(); i++) {
            rowsType.put(documentValidator.getListRowValidator().get(i).getName(), i);
        }
        try {
            int rownum = 0;
            int wrong = 0;
            int right = 0;
            int skip = 0;
            OPCPackage pkg = OPCPackage.open(inputStream);
            XSSFWorkbook workBook = new XSSFWorkbook(pkg);
            FormulaEvaluator evaluator = workBook.getCreationHelper().createFormulaEvaluator();
            XSSFSheet xssfSheet = workBook.getSheetAt(0);
            for (Row row : xssfSheet) {
                String rowType = row.getCell(0).getStringCellValue();
                if (rowsType.containsKey(rowType)) {
                    RowValidator rowValidator = documentValidator.getListRowValidator()
                            .get((int) rowsType.get(rowType));
                    int columnNum = rowValidator.getListItemValidator().size();
                    if (row.getLastCellNum() == columnNum) {
                        String[] values = new String[columnNum];
                        int i = 0;
                        for (Cell cell : row) {
                            switch (evaluator.evaluateInCell(cell).getCellType()) {
                            case Cell.CELL_TYPE_NUMERIC:
                                values[i] = cell.getNumericCellValue() + "";
                                break;
                            case Cell.CELL_TYPE_STRING:
                                values[i] = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_FORMULA:
                                values[i] = "";
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                values[i] = "";
                                break;
                            }
                            i++;
                        }
                        ResponseValidator responseValidator = rowValidator.validate(values);
                        if (!responseValidator.isSuccess()) {
                            wrong++;
                            stringBuilder.append("{\"row\":").append(rownum).append(",\"message\":")
                                    .append(responseValidator.getMessage()).append(",");
                        } else
                            right++;
                    } else {
                        wrong++;
                        stringBuilder.append("{\"row\":").append(rownum)
                                .append(",\"success\":false,\"message\":\"Line wrong size\"},").toString();
                    }
                } else {
                    skip++;
                    stringBuilder.append("{\"row\":").append(rownum)
                            .append(",\"success\":false,\"message\":\"Unknow row type\"},").toString();
                }
                rownum++;

            }

            response = new ResponseValidator(wrong == 0,
                    "{\"skip\":" + skip + ",\"wrong\":" + wrong + ",\"right\":" + right + ",\"count\":" + rownum
                            + ",\"errorMessages\":["
                            + (stringBuilder.toString().length() > 0
                                    ? stringBuilder.substring(0, stringBuilder.toString().length() - 1)
                                    : "")
                            + "]}");
        } catch (Exception ex) {
            Logger.getLogger(FlatFixedDocumentLayout.class.getName()).log(Level.SEVERE, null, ex);
            response = new ResponseValidator(false, stringBuilder.append("\"success\":false,\"message\":\"")
                    .append(ex.getMessage()).append("\"}").toString());
        } finally {
            try {
                inputStream.close();
            } catch (IOException ex) {
                response = new ResponseValidator(false, stringBuilder.append("\"success\":false,\"message\":\"")
                        .append(ex.getMessage()).append("\"}").toString());
            }
        }
    } else {
        response = new ResponseValidator(false,
                stringBuilder.append("\"success\":false,\"message\":\"No configuration loaded\"}").toString());
    }
    return response;
}

---

From source file:com.jwm123.loggly.reporter.ReportGenerator.java

License:Apache License

public ReportGenerator(File reportFile) throws IOException, IllegalArgumentException {
    if (reportFile.exists()) {
        InputStream in = null;// w w w . java  2 s  .c om
        try {
            in = new FileInputStream(reportFile);
            if (reportFile.getName().endsWith(".xls")) {
                POIFSFileSystem fs = new POIFSFileSystem(in);
                workbook = new HSSFWorkbook(fs);
            } else if (reportFile.getName().endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(in);
            } else {
                throw new IllegalArgumentException("Invalid report filename. " + reportFile.getName());
            }
        } finally {
            IOUtils.closeQuietly(in);
        }
    } else {
        if (reportFile.getName().endsWith(".xls")) {
            workbook = new HSSFWorkbook();
        } else if (reportFile.getName().endsWith(".xlsx")) {
            workbook = new XSSFWorkbook();
        } else {
            throw new IllegalArgumentException("Invalid report filename. " + reportFile.getName());
        }
    }
}

---

From source file:com.khodev.sc2.quiz.Quiz.java

License:Open Source License

public Quiz(String xls) throws IOException {
    FileInputStream file = new FileInputStream(xls);
    Workbook workbook = new XSSFWorkbook(file);
    Sheet sheet = workbook.getSheetAt(0);
    boolean headerSkipped = false;
    for (Row row : sheet) {
        if (!headerSkipped) {
            headerSkipped = true;/*from  w w w  .  ja v a 2 s . co m*/
            continue;
        }
        questions.add(new Question(row.getCell(2).toString(), row.getCell(3).toString()));
    }

}

---

• «
• 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
• »