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

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. 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.excel.javafx.frames.MainFrame.java

private void getSourceFileHeaders(File file) {
    try {/*from   w  w  w .  j ava2s  .c om*/
        FileInputStream sourceFile1 = new FileInputStream(file);
        XSSFWorkbook workbook1 = new XSSFWorkbook(sourceFile1);
        sourceSheetSelector.removeAllItems(); //to clear existing headers
        for (int sheetno = 0; sheetno < workbook1.getNumberOfSheets(); sheetno++) {
            sourceSheetSelector.addItem(workbook1.getSheetName(sheetno));
        }
        //sourceColumnSelector();     // to fill columnSelection
    } catch (FileNotFoundException ex) {
        Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:com.excel.javafx.frames.MainFrame.java

private void getDestinationFileHeaders(File file) {
    try {/*from ww w  . j av  a 2s.co  m*/
        FileInputStream destFile = new FileInputStream(file);
        XSSFWorkbook workbook1 = new XSSFWorkbook(destFile);
        destSheetSelector.removeAllItems(); //to clear existing headers

        for (int sheetno = 0; sheetno < workbook1.getNumberOfSheets(); sheetno++) {
            destSheetSelector.addItem(workbook1.getSheetName(sheetno));
        }
        //destinationColumnSelector();     // to fill columnSelection
    } catch (FileNotFoundException ex) {
        Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:com.excel.javafx.frames.MainFrame.java

private void CompareBtnActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_CompareBtnActionPerformed
    try {/*from   www.  j  a  va2  s . co  m*/
        // get input excel files
        FileInputStream sourceFile = new FileInputStream(sourceFileName);
        FileInputStream destFile = new FileInputStream(destFileName);

        // Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook sourcebook = new XSSFWorkbook(sourceFile);
        XSSFWorkbook destbook = new XSSFWorkbook(destFile);

        // Get first/desired sheet from the workbook
        XSSFSheet sourceSheet = sourcebook.getSheet(sourceSheetSelector.getSelectedItem().toString());
        XSSFSheet destSheet = destbook.getSheet(destSheetSelector.getSelectedItem().toString());

        // Compare sheets
        if (compareTwoSheets(sourceSheet, destSheet)) {
            System.out.println("\n\nThe two excel sheets are Equal");
        } else {
            System.out.println("\n\nThe two excel sheets are Not Equal");
        }

        //close files
        sourceFile.close();
        destFile.close();

        //update table values
        int emptyrow = 0;
        for (int rownum = 0; rownum < rowcount; rownum++) {
            if (!tableModel.getValueAt(rownum, 1).toString().equals("")) {
                emptyrow++;
            }
        }
        for (int rowno = 0; rowno <= expectedValue.size(); rowno++) {
            tableModel.setValueAt(sourceFileName.getName(), emptyrow, 1);
            jTable1.setValueAt(destFileName.getName(), emptyrow, 2);
            jTable1.setValueAt(sourceRow.get(rowno), emptyrow, 3);
            jTable1.setValueAt(destRow.get(rowno), emptyrow, 4);
            jTable1.setValueAt(sourceColumnList.getSelectedValue(), emptyrow, 5);
            jTable1.setValueAt(destColumnList.getSelectedValue(), emptyrow, 6);
            jTable1.setValueAt(expectedValue.get(rowno), emptyrow, 7);
            jTable1.setValueAt(actualValue.get(rowno), emptyrow, 8);
        }

    } catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:com.excel.javafx.frames.MainFrame.java

private void sourceColumnSelector() {
    FileInputStream sourceFile1 = null;
    final DefaultListModel model = new DefaultListModel();

    try {/*from   ww  w .  j a v a2 s .  com*/
        sourceFile1 = new FileInputStream(sourceFileName);
        XSSFWorkbook workbook1 = new XSSFWorkbook(sourceFile1);
        XSSFSheet sheet = workbook1.getSheet(sourceSheetSelector.getSelectedItem().toString());
        int columncount = sheet.getRow(0).getLastCellNum();

        for (int columnno = 0; columnno < columncount; columnno++) {
            model.addElement(sheet.getRow(0).getCell(columnno).toString());
        }
        sourceColumnList.setModel(model);

    } catch (FileNotFoundException ex) {
        Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            sourceFile1.close();
        } catch (IOException ex) {
            Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

From source file:com.excel.javafx.frames.MainFrame.java

private void destinationColumnSelector() {
    FileInputStream destFile1 = null;
    final DefaultListModel model = new DefaultListModel();

    try {/*from   w  ww .  j  ava2  s  . c  o  m*/
        destFile1 = new FileInputStream(destFileName);
        XSSFWorkbook workbook1 = new XSSFWorkbook(destFile1);
        XSSFSheet sheet = workbook1.getSheet(destSheetSelector.getSelectedItem().toString());
        int columncount = sheet.getRow(0).getLastCellNum();

        for (int columnno = 0; columnno < columncount; columnno++) {
            model.addElement(sheet.getRow(0).getCell(columnno).toString());
        }
        destColumnList.setModel(model);

    } catch (FileNotFoundException ex) {
        Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            destFile1.close();
        } catch (IOException ex) {
            Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

From source file:com.fanniemae.ezpie.data.connectors.ExcelConnector.java

License:Open Source License

@Override
public Boolean open() {
    _filename = _session.requiredAttribute(_dataSource, "Filename");
    _sheetName = _session.optionalAttribute(_dataSource, "SheetName");
    _columnNameAddress = _session.optionalAttribute(_dataSource, "ColumnNameLocation");
    _dataAddress = _session.optionalAttribute(_dataSource, "DataLocation");
    _addFilename = StringUtilities.toBoolean(_session.optionalAttribute(_dataSource, "AddFilenameColumn"),
            false);/*from   w  w  w  . jav  a2  s  .c om*/
    _allTypesStrings = StringUtilities.toBoolean(_session.optionalAttribute(_dataSource, "AllStrings"), true);

    try {
        File file = new File(_filename);
        if (file.exists() && file.isFile()) {
            _session.addLogMessage("", "Excel File Size", String.format("%,d bytes", file.length()));
        }
        _filenameOnly = file.getName();
        _fis = new FileInputStream(file);
        _workbook = new XSSFWorkbook(_fis);

        StringBuilder sb = new StringBuilder();
        String newLine = System.getProperty("line.separator");
        int numberSheets = _workbook.getNumberOfSheets();
        for (int i = 0; i < numberSheets; i++) {
            if (i > 0)
                sb.append(newLine);
            sb.append(String.format("%s", _workbook.getSheetName(i)));
        }
        _session.addLogMessage("", "Worksheets Found", sb.toString());
        _sheet = StringUtilities.isNotNullOrEmpty(_sheetName) ? _workbook.getSheet(_sheetName)
                : _workbook.getSheetAt(0);
        if (_sheet == null) {
            _session.addLogMessage(Constants.LOG_WARNING_MESSAGE, "Worksheet",
                    String.format("%s worksheet not found, Skipping this file.", _sheetName));
            return false;
        }
        readSchema();

        if (_addFilename) {
            _dataSchema = (String[][]) ArrayUtilities.resizeArray(_dataSchema, _dataSchema.length + 1);
            _dataSchema[_dataSchema.length - 1] = new String[] { SOURCE_FILENAME_COLUMN, "String" };
            _dataTypes = (DataType[]) ArrayUtilities.resizeArray(_dataTypes, _dataTypes.length + 1);
            _dataTypes[_dataTypes.length - 1] = DataType.StringData;
            _columnCount = _dataSchema.length;
        }

        StringBuilder schemaReport = new StringBuilder();
        for (int i = 0; i < _dataSchema.length; i++) {
            if (i > 0)
                schemaReport.append(newLine);
            schemaReport.append(String.format("%s (%s)", _dataSchema[i][0], _dataSchema[i][1]));
        }
        _session.addLogMessage("", "Data Schema", schemaReport.toString());
    } catch (FileNotFoundException e) {
        throw new PieException(String.format("%s file not found.", _filename), e);
    } catch (Exception e) {
        throw new PieException(String.format("Problem reading the workbook or sheet. %s", e.getMessage()), e);
    }
    return null;
}

From source file:com.femsa.kof.csi.util.XlsAnalizer.java

public List<Xtmpinddl> analizeXlsIndi(UploadedFile file, final DcsUsuario usuario, List<DcsCatPais> paises,
        List<DcsCatIndicadores> indicadores) throws DCSException {
    Workbook excelXLS = null;/*w  w  w .  jav  a  2 s  . co m*/
    List<Xtmpinddl> listaCarga = null;
    try {
        String extension = getExtension(file.getFileName());
        Iterator<Row> rowIterator;
        if (extension.equalsIgnoreCase("xlsx")) {
            excelXLS = new XSSFWorkbook(file.getInputstream());
        } else if (extension.equalsIgnoreCase("xls")) {
            excelXLS = new HSSFWorkbook(file.getInputstream());
        }
        int numberOfSheets = excelXLS != null ? excelXLS.getNumberOfSheets() : 0;
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = excelXLS != null ? excelXLS.getSheetAt(i) : null;
            rowIterator = sheet != null ? sheet.iterator() : null;
            if (sheet != null && i == 0) {
                listaCarga = this.analizeSheetIndi(rowIterator, usuario, sheet.getSheetName(), paises,
                        indicadores);
                if (!listaCarga.isEmpty()) {
                    loadedSheets.add(sheet.getSheetName().trim().toUpperCase());
                } else {
                    omittedSheets.add(sheet.getSheetName().trim().toUpperCase() + ", Empty");
                }
            } else {
                String mensaje = sheet != null ? sheet.getSheetName().trim().toUpperCase() + ", not valid."
                        : "Not valid.";
                omittedSheets.add(mensaje);
            }
        }
    } catch (IOException ex) {
        Logger.getLogger(getClass().getName()).log(Level.SEVERE, "Error IO", ex);
        throw new DCSException("An error ocurred while analizing the file: " + ex.getMessage());
    } finally {
        try {
            file.getInputstream().close();
        } catch (IOException ex) {
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "Error IO", ex);
            throw new DCSException("An error ocurred while analizing the file: " + ex.getMessage());
        }
    }
    return listaCarga;
}

From source file:com.femsa.kof.csi.util.XlsAnalizer.java

public List<XtmpinddlFlota> analizeXlsFlota(UploadedFile file, final DcsUsuario usuario,
        List<DcsCatPais> paises) throws DCSException {
    Workbook excelXLS = null;//from w  w  w  .  jav  a 2 s .  co m
    List<XtmpinddlFlota> listaCarga = null;
    try {
        String extension = getExtension(file.getFileName());
        Iterator<Row> rowIterator;
        if (extension.equalsIgnoreCase("xlsx")) {
            excelXLS = new XSSFWorkbook(file.getInputstream());
        } else if (extension.equalsIgnoreCase("xls")) {
            excelXLS = new HSSFWorkbook(file.getInputstream());
        }
        int numberOfSheets = excelXLS != null ? excelXLS.getNumberOfSheets() : 0;
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = excelXLS != null ? excelXLS.getSheetAt(i) : null;
            rowIterator = sheet != null ? sheet.iterator() : null;
            if (sheet != null && i == 0) {
                listaCarga = this.analizeSheetFlota(rowIterator, usuario, sheet.getSheetName(), paises);
                if (!listaCarga.isEmpty()) {
                    loadedSheets.add(sheet.getSheetName().trim().toUpperCase());
                } else {
                    omittedSheets.add(sheet.getSheetName().trim().toUpperCase() + ", Empty");
                }
            } else {
                String mensaje = sheet != null ? sheet.getSheetName().trim().toUpperCase() + ", not valid."
                        : "Not valid.";
                omittedSheets.add(mensaje);
            }
        }
    } catch (IOException ex) {
        Logger.getLogger(getClass().getName()).log(Level.SEVERE, "Error IO", ex);
        throw new DCSException("An error ocurred while analizing the file: " + ex.getMessage());
    } finally {
        try {
            file.getInputstream().close();
        } catch (IOException ex) {
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "Error IO", ex);
            throw new DCSException("An error ocurred while analizing the file: " + ex.getMessage());
        }
    }
    return listaCarga;
}

From source file:com.fingence.slayer.service.impl.AssetLocalServiceImpl.java

License:Open Source License

public void loadPricingData(long userId, File excelFile, ServiceContext serviceContext, int type) {

    System.out.println("inside Load Pricing Data....");
    if (Validator.isNull(excelFile))
        return;//  w w  w. j a  v  a2 s. c o  m

    InputStream is = null;
    try {
        is = new FileInputStream(excelFile);
    } catch (FileNotFoundException e) {
        //e.printStackTrace();
    }

    if (Validator.isNull(is))
        return;

    // Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = null;
    try {
        workbook = new XSSFWorkbook(is);
    } catch (IOException e) {
        e.printStackTrace();
    }

    if (Validator.isNull(workbook))
        return;

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

    Iterator<Row> rowIterator = sheet.iterator();
    Map<Integer, Long> columnNames = new HashMap<Integer, Long>();
    int columnCount = 0;

    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();

        columnCount = row.getPhysicalNumberOfCells();

        _log.debug("processing row ==> " + row.getRowNum());
        System.out.println("processing row ==> " + row.getRowNum());

        int rowNum = row.getRowNum();

        if (rowNum == 0)
            continue;

        if (rowNum == 1) {
            for (int i = 0; i < columnCount; i++) {
                Cell cell = row.getCell(i);
                if (Validator.isNull(cell))
                    continue;

                String id_isin = CellUtil.getString(cell);

                Asset asset = null;
                try {
                    asset = assetPersistence.fetchByIdISIN(id_isin);
                } catch (SystemException e) {
                    e.printStackTrace();
                }

                if (Validator.isNull(asset))
                    continue;

                columnNames.put(i, asset.getAssetId());
            }
            continue;
        }

        if (rowNum > 1 && rowNum < 14)
            continue;

        System.out.println("going to process data...");

        Iterator<Integer> itr = columnNames.keySet().iterator();

        //for (int i=3; i < columnCount; i++){

        while (itr.hasNext()) {

            int i = itr.next();
            Date date = CellUtil.getDate(row.getCell(i));

            if (Validator.isNull(date))
                continue;

            long assetId = 0l;
            try {
                assetId = columnNames.get(i);
            } catch (Exception e) {
                _log.debug(e.getMessage() + ": There is an exception...");
                continue;
            }

            double value = CellUtil.getDouble(row.getCell(++i));

            History history = null;
            try {
                history = historyPersistence.fetchByAssetId_Date_Type(assetId, date, type);
                _log.debug("history record already present...");
            } catch (SystemException e) {
                e.printStackTrace();
            }

            if (Validator.isNull(history)) {
                long recId = 0l;
                try {
                    recId = counterLocalService.increment(History.class.getName());
                } catch (SystemException e) {
                    e.printStackTrace();
                }

                history = historyLocalService.createHistory(recId);
                history.setAssetId(assetId);
                history.setType(type);
                history.setValue(value);
                history.setLogDate(date);

                if (type == IConstants.HISTORY_TYPE_BOND_CASHFLOW) {
                    double principal = CellUtil.getDouble(row.getCell(++i));
                    history.setPrincipal(principal);
                }

                try {
                    history = historyLocalService.addHistory(history);
                } catch (SystemException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

From source file:com.fingence.slayer.service.impl.AssetLocalServiceImpl.java

License:Open Source License

public void loadDividends(long userId, File excelFile, ServiceContext serviceContext) {

    System.out.println("inside Load Dividends Data....");
    if (Validator.isNull(excelFile))
        return;//from w w w  .  j  a  va 2  s . c o m

    InputStream is = null;
    try {
        is = new FileInputStream(excelFile);
    } catch (FileNotFoundException e) {
        //e.printStackTrace();
    }

    if (Validator.isNull(is))
        return;

    // Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = null;
    try {
        workbook = new XSSFWorkbook(is);
    } catch (IOException e) {
        e.printStackTrace();
    }

    if (Validator.isNull(workbook))
        return;

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

    Iterator<Row> rowIterator = sheet.iterator();
    Map<Integer, Long> columnNames = new HashMap<Integer, Long>();
    int columnCount = 0;

    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();

        columnCount = row.getPhysicalNumberOfCells();

        _log.debug("processing row ==> " + row.getRowNum());
        System.out.println("processing row ==> " + row.getRowNum());

        if (row.getRowNum() == 0)
            continue;

        if (row.getRowNum() == 1) {
            for (int i = 0; i < columnCount; i++) {
                Cell cell = row.getCell(i);
                if (Validator.isNull(cell))
                    continue;

                String id_isin = CellUtil.getString(cell);

                Asset asset = null;
                try {
                    asset = assetPersistence.fetchByIdISIN(id_isin);
                } catch (SystemException e) {
                    e.printStackTrace();
                }

                if (Validator.isNull(asset))
                    continue;

                columnNames.put(i, asset.getAssetId());
            }
            continue;
        }

        for (int i = 0; i < columnCount; i++) {
            Date declaredDate = CellUtil.getDate(row.getCell(i));

            if (Validator.isNull(declaredDate))
                continue;

            long assetId = 0l;
            try {
                assetId = columnNames.get(i);
            } catch (Exception e) {
                _log.debug(e.getMessage() + ": There is an exception...");
                continue;
            }

            Date exDate = CellUtil.getDate(row.getCell(++i));

            Date recordDate = CellUtil.getDate(row.getCell(++i));

            Date payableDate = CellUtil.getDate(row.getCell(++i));

            double amount = CellUtil.getDouble(row.getCell(++i));
            String frequency = CellUtil.getString(row.getCell(++i));
            String type = CellUtil.getString(row.getCell(++i));

            Dividend dividend = null;
            try {
                dividend = dividendPersistence.fetchByAssetId_DeclaredDate(assetId, declaredDate);
                _log.debug("dividend record already present...");
            } catch (SystemException e) {
                e.printStackTrace();
            }

            if (Validator.isNull(dividend)) {

                long recId = 0l;
                try {
                    recId = counterLocalService.increment(Dividend.class.getName());
                } catch (SystemException e) {
                    e.printStackTrace();
                }

                dividend = dividendPersistence.create(recId);
            }

            // update the record
            dividend.setDeclaredDate(declaredDate);
            dividend.setExDate(exDate);
            dividend.setAssetId(assetId);
            dividend.setRecordDate(recordDate);
            dividend.setPayableDate(payableDate);
            dividend.setAmount(amount);
            dividend.setFrequency(frequency);
            dividend.setType(type);

            try {
                dividend = dividendLocalService.updateDividend(dividend);
                System.out.println("dividend new history records..." + dividend);
            } catch (SystemException e) {
                e.printStackTrace();
            }
        }
    }
}