Example usage for org.apache.poi.ss.usermodel Row getCell

List of usage examples for org.apache.poi.ss.usermodel Row getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getCell

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Row getCell.

Prototype

Cell getCell(int cellnum);

Source Link

Document

Get the cell representing a given column (logical cell) 0-based.

Usage

From source file:com.ctb.importdata.ImportSFDataProcessor.java

public static ArrayList<SalesForceLicenseData> readDataFromXLSFile(String fileName) {
    File sfDataFile = new File(fileName);
    FileInputStream fileInputStream = null;
    ArrayList<SalesForceLicenseData> sfLicenseDataList = null;
    SalesForceLicenseData sfld = null;//from   w w w. j av  a2  s . co m

    if (sfDataFile.exists()) {
        //System.out.println("Reading data from .xls file started.");
        logger.info("Reading data from .xls file : Started :: Timestamp >> "
                + new Date(System.currentTimeMillis()));
        try {
            //read the file in to stream
            fileInputStream = new FileInputStream(sfDataFile);

            //Create Workbook instance holding reference to .xls file
            HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);

            //Get first/desired sheet from the workbook
            HSSFSheet sheet = workbook.getSheetAt(0);
            sfLicenseDataList = new ArrayList<SalesForceLicenseData>();

            if (sheet != null) {
                int totalRows = sheet.getPhysicalNumberOfRows();
                //System.out.println("Total no. of physical rows in file = "+ totalRows);
                logger.info("Total no. of physical rows in file = " + totalRows);

                Row headerRow = sheet.getRow(0);
                Cell headerCell;
                Cell dataCell;
                if (headerRow == null) {
                    //System.out.println("No file header content found.") ;
                    logger.info("No file header content found.");
                } else {
                    int totalHeaderColumns = headerRow.getPhysicalNumberOfCells();
                    //System.out.println("Total no. of header cells = "+ totalHeaderColumns);
                    logger.info("Total no. of header cells = " + totalHeaderColumns);

                    for (int rowCtr = 1; rowCtr < totalRows; rowCtr++) {
                        //System.out.println("Row No. >> "+rowCtr);
                        Row dataRow = sheet.getRow(rowCtr);
                        if (dataRow != null) {
                            int totalRowColumns = dataRow.getPhysicalNumberOfCells();
                            //System.out.println("Total no. of current data row cells = "+ totalRowColumns);
                            //logger.info("Total no. of current data row cells = "+ totalRowColumns);
                            logger.info(
                                    "Row No. [" + rowCtr + "] :: Header Column Count = [" + totalHeaderColumns
                                            + "] :: Current Data Row Column Count = [" + totalRowColumns + "]");
                            //Discard dummy rows in spreadsheet if the count of row columns not equal to header columns
                            if (totalHeaderColumns == totalRowColumns) {
                                boolean isCustomerIdBlank = dataRow.getCell(0)
                                        .getCellType() == Cell.CELL_TYPE_BLANK ? true : false;
                                boolean isOrgNodeIdBlank = dataRow.getCell(5)
                                        .getCellType() == Cell.CELL_TYPE_BLANK ? true : false;
                                //System.out.println("isCustomerIdBlank >> "+isCustomerIdBlank+" :: isOrgNodeIdBlank >> "+isOrgNodeIdBlank);
                                logger.info("Row No. [" + rowCtr + "] :: isCustomerIdBlank >> "
                                        + isCustomerIdBlank + " :: isOrgNodeIdBlank >> " + isOrgNodeIdBlank);
                                //Condition to skip row for SF data object population if customer id or orgnode id is blank
                                if (!isCustomerIdBlank && !isOrgNodeIdBlank) {
                                    sfld = new SalesForceLicenseData();

                                    // For each row, loop through each column
                                    for (int colCtr = 0; colCtr < totalHeaderColumns; colCtr++) {
                                        //System.out.println("Column No. >> "+colCtr);
                                        headerCell = headerRow.getCell(colCtr);
                                        dataCell = dataRow.getCell(colCtr);
                                        if (dataCell != null) {
                                            //System.out.println("dataCell.getCellType() >> "+dataCell.getCellType());
                                            switch (dataCell.getCellType()) {
                                            case Cell.CELL_TYPE_BOOLEAN:
                                                //Do nothing
                                                //System.out.println(dataCell.getBooleanCellValue());
                                                break;

                                            case Cell.CELL_TYPE_NUMERIC:
                                                //System.out.println(dataCell.getNumericCellValue());
                                                populateSFDataNumericColValue(sfld, dataCell, headerCell);
                                                break;

                                            case Cell.CELL_TYPE_STRING:
                                                //System.out.println(dataCell.getStringCellValue());
                                                populateSFDataStrColValue(sfld, dataCell, headerCell);
                                                break;

                                            case Cell.CELL_TYPE_BLANK:
                                                populateSFDataBlankColValue(sfld, dataCell, headerCell);
                                                break;

                                            default:
                                                System.out.println(dataCell);
                                                break;
                                            }
                                        }
                                    }

                                    sfLicenseDataList.add(sfld);
                                }
                            }
                        }
                    }

                }
            }

        } catch (FileNotFoundException e) {
            logger.error("FileNotFoundException : occurred while procesing :: Filename >> [" + fileName + "]");
            e.printStackTrace(); // unexpected
        } catch (IOException e) {
            logger.error("IOException : occurred while procesing :: Filename >> [" + fileName + "]");
            e.printStackTrace();
        } finally {
            try {
                if (fileInputStream != null)
                    fileInputStream.close();
            } catch (IOException e) {
                logger.error("IOException : occurred while closing file input stream.");
                e.printStackTrace();
            }
        }
        //System.out.println("Reading data from .xls file completed.");
        logger.info("Reading data from .xls file : Completed :: Timestamp >> "
                + new Date(System.currentTimeMillis()));
    } else {
        //System.out.println("File does not exists");
        logger.error("File does not exists :: Filename >> [" + fileName + "]");
    }
    return sfLicenseDataList;
}

From source file:com.cx.test.FromHowTo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Class[] clazz = new Class[] { String.class, String.class, String.class, String.class, Integer.class,
            String.class, String.class };
    InputStream stream = new FileInputStream(new File("C:\\Users\\Administrator\\Desktop\\menu.xlsx"));
    Workbook wb = new XSSFWorkbook(stream);
    Sheet sheet = wb.getSheetAt(0);//from   w  w w  .  j  a  v a2s .c  om
    int rows = sheet.getLastRowNum();
    int cells = sheet.getRow(0).getPhysicalNumberOfCells();
    for (int i = 0; i < rows; i++) {
        Row row = sheet.getRow(i + 1);
        for (int j = 0; j < cells; j++) {
            Cell cell = row.getCell(j);
            Object obj = null;
            if (cell != null) {
                obj = getCellValue(cell, clazz[j]);
            }
            switch (j) {
            case 0:
                System.out.println("000000000-----" + obj);
                break;
            case 1:
                System.out.println("1111111111111" + obj);
                break;
            default:
                break;
            }
        }
    }
}

From source file:com.dataart.spreadsheetanalytics.engine.ConverterUtils.java

License:Apache License

/**
 * Gets an instance of a Workbook ({@link ConverterUtils#newWorkbook(InputStream)}, creates copy of original file, 
 * clears all the cell values, but preserves formatting.
 *//*  w  ww. j a  v  a  2s. c om*/
static Workbook clearContent(final Workbook book) {
    ByteArrayOutputStream originalOut = new ByteArrayOutputStream();

    try {
        book.write(originalOut);
    } catch (IOException e) {
        throw new CalculationEngineException(e);
    }

    InputStream originalIn = new ByteArrayInputStream(copyOf(originalOut.toByteArray(), originalOut.size()));

    Workbook w = ConverterUtils.newWorkbook(originalIn);
    Sheet s = w.getSheetAt(0); //TODO: only one sheet is supported

    for (int i = s.getFirstRowNum(); i <= s.getLastRowNum(); i++) {
        Row r = s.getRow(i);
        if (r == null) {
            continue;
        }

        for (int j = r.getFirstCellNum(); j <= r.getLastCellNum(); j++) {
            Cell c = r.getCell(j);
            if (c == null) {
                continue;
            }

            c.setCellType(CELL_TYPE_BLANK);
        }
    }

    return w;
}

From source file:com.dataart.spreadsheetanalytics.engine.ConverterUtils.java

License:Apache License

/** Does cell of a given address copy from {@link Sheet} to {@link IDataModel}. */
static void copyCell(ICellAddress address, Sheet from, IDataModel to) {
    if (from == null) {
        return;//from w w  w  .java  2  s . c om
    }
    Row fromRow = from.getRow(address.a1Address().row());
    if (fromRow == null) {
        return;
    }
    Cell fromCell = fromRow.getCell(address.a1Address().column());
    if (fromCell == null) {
        return;
    }

    DmCell toCell = new DmCell();
    toCell.setAddress(address);
    toCell.setContent(resolveCellValue(fromCell));

    to.setCell(address, toCell);
}

From source file:com.dataart.spreadsheetanalytics.engine.DataModelConverters.java

License:Apache License

/**
 * For given {@link Workbook} does convert everything to new {@link DataModel} structure.
 * Does copy all supported fields (for supported fields see {@link DataModel} class.
 *//*from   w ww. j av  a 2 s .c o m*/
static IDataModel toDataModel(final Workbook workbook) {
    if (workbook == null) {
        return null;
    }

    //add custom functions information
    workbook.addToolPack(getUdfFinder());

    Sheet s = workbook.getSheetAt(0); //TODO: only one sheet is supported
    if (s == null) {
        return null;
    }

    IDataModel dm = new DataModel(s.getSheetName());

    for (int i = s.getFirstRowNum(); i <= s.getLastRowNum(); i++) {
        Row r = s.getRow(i);
        if (r == null) {
            continue;
        }

        DmRow row = new DmRow(i);
        dm.setRow(i, row);

        for (int j = r.getFirstCellNum(); j < r.getLastCellNum(); j++) {
            Cell c = r.getCell(j);
            if (c == null) {
                continue;
            }

            DmCell cell = new DmCell();
            row.setCell(j, cell);

            cell.setAddress(new CellAddress(dm.getDataModelId(), A1Address.fromRowColumn(i, j)));
            cell.setContent(ConverterUtils.resolveCellValue(c));
        }
    }

    EvaluationWorkbook evaluationWbook = ConverterUtils.newEvaluationWorkbook(workbook);

    for (int nIdx = 0; nIdx < workbook.getNumberOfNames(); nIdx++) {
        Name name = workbook.getNameAt(nIdx);

        String reference = name.getRefersToFormula();
        if (reference == null) {
            continue;
        }

        if (A1Address.isAddress(removeSheetFromNameRef(reference))) {
            dm.setNamedAddress(name.getNameName(), A1Address.fromA1Address(removeSheetFromNameRef(reference)));
        } else if (isFormula(reference, evaluationWbook)) {
            dm.setNamedValue(name.getNameName(), new CellValue(FORMULA_PREFIX + reference));
        } else {
            dm.setNamedValue(name.getNameName(), CellValue.from(reference));
        }
    }

    return dm;
}

From source file:com.dataart.spreadsheetanalytics.engine.DataModelConverters.java

License:Apache License

/** Convertes plain {@link IDataModel} to new {@link XSSFWorkbook} with formatting provided. */
static Workbook toWorkbook(final IDataModel dataModel, final Workbook formatting) {
    Workbook result = formatting == null ? ConverterUtils.newWorkbook()
            : ConverterUtils.clearContent(formatting);

    Sheet wbSheet = result.getSheet(dataModel.getName());
    if (wbSheet == null) {
        wbSheet = result.createSheet(dataModel.getName());
    }/*w  ww .ja  v  a  2 s .  c o  m*/

    dataModel.getNamedAddresses().forEach((k, v) -> {
        Name name = result.createName();
        name.setNameName(k);

        name.setRefersToFormula(createPoiNameRef(v.address(), dataModel.getName()));
    });

    dataModel.getNamedValues().forEach((k, v) -> {
        Name name = result.createName();
        name.setNameName(k);

        String refString = v.get() == null ? "" : v.get().toString();
        if (refString.startsWith(FORMULA_PREFIX)) {
            refString = refString.substring(1);
        }

        name.setRefersToFormula(refString);
    });

    for (int rowIdx = dataModel.getFirstRowIndex(); rowIdx <= dataModel.getLastRowIndex(); rowIdx++) {
        IDmRow dmRow = dataModel.getRow(rowIdx);
        if (dmRow == null) {
            continue;
        }
        Row wbRow = wbSheet.getRow(rowIdx);
        if (wbRow == null) {
            wbRow = wbSheet.createRow(rowIdx);
        }

        for (int cellIdx = dmRow.getFirstColumnIndex(); cellIdx <= dmRow.getLastColumnIndex(); cellIdx++) {
            IDmCell dmCell = dmRow.getCell(cellIdx);
            if (dmCell == null) {
                continue;
            }

            Cell wbCell = wbRow.getCell(cellIdx);
            if (wbCell == null) {
                wbCell = wbRow.createCell(cellIdx);
            }

            ConverterUtils.populateCellValue(wbCell, dmCell.getContent());
        }
    }

    return result;
}

From source file:com.dataart.spreadsheetanalytics.engine.DataSetConverters.java

License:Apache License

/**
 * Converts a {@link Workbook} to new {@link IDataSet}.
 * Ignores empty rows./*from w  w  w.  j  a  v a  2s.c o m*/
 * 
 * @throws {@link CalculationEngineException} if {@link Workbook} contains formulas or Cell references.
 */
static IDataSet toDataSet(final Workbook workbook) {
    Sheet sheet = workbook.getSheetAt(0); //TODO: this works only for single sheet documents
    DataSet dataSet = new DataSet(sheet.getSheetName());

    for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
        IDsRow dsRow = dataSet.addRow();
        Row row = sheet.getRow(i);
        for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
            Cell wbCell = row.getCell(j);
            if (wbCell != null && wbCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                throw new CalculationEngineException("DataSet should not contain formulas");
            }
            IDsCell cell = dsRow.addCell();
            cell.setValue(ConverterUtils.resolveCellValue(wbCell));
        }
    }
    return dataSet;
}

From source file:com.dataart.spreadsheetanalytics.engine.DependencyExtractors.java

License:Apache License

/**
 * Extracts {@link IDataModel} from {@link Workbook} at given {@link ICellAddress}.
 * Useful for formula. If given {@link ICellAddress} contains formula it can be parsed.
 * Based on this parsed information a new {@link IDataModel} might be created.
 *//*from www.  jav a 2s  .c  o  m*/
static IDataModel toDataModel(final Workbook book, final ICellAddress address) {
    if (book == null || address == null) {
        return null;
    }
    if (address instanceof A1RangeAddress) {
        throw new CalculationEngineException(
                "A1RangeAddress is not supported, only one cell can be converted to DataModel.");
    }

    Sheet s = book.getSheetAt(0); /* TODO: only one sheet is supported */
    Row r = s.getRow(address.a1Address().row());
    if (r == null) {
        return null;
    }
    Cell c = r.getCell(address.a1Address().column());
    if (c == null || CELL_TYPE_FORMULA != c.getCellType()) {
        return null;
    }

    return createDataModelFromCell(s, create((XSSFWorkbook) book),
            fromRowColumn(c.getRowIndex(), c.getColumnIndex()));
}

From source file:com.dataart.spreadsheetanalytics.engine.DependencyExtractors.java

License:Apache License

/** For given cell address gives a list of this cell's dependencies. */
static List<IA1Address> resolveCellDependencies(IA1Address cellAddress, Sheet sheet,
        FormulaParsingWorkbook workbook) {
    Row row = sheet.getRow(cellAddress.row());
    if (row == null) {
        return emptyList();
    }/*  ww w . j  a v  a2  s. com*/
    Cell cell = row.getCell(cellAddress.column());
    if (cell == null) {
        return emptyList();
    }

    if (CELL_TYPE_FORMULA != cell.getCellType()) {
        return singletonList(cellAddress);
    }

    List<IA1Address> dependencies = new LinkedList<>();

    for (Ptg ptg : parse(cell.getCellFormula(), workbook, CELL, 0)) { /* TODO: only one sheet is supported */

        if (ptg instanceof RefPtg) {
            RefPtg ref = (RefPtg) ptg;

            dependencies.addAll(
                    resolveCellDependencies(fromRowColumn(ref.getRow(), ref.getColumn()), sheet, workbook));

        } else if (ptg instanceof AreaPtg) {
            AreaPtg area = (AreaPtg) ptg;

            for (int r = area.getFirstRow(); r <= area.getLastRow(); r++) {
                for (int c = area.getFirstColumn(); c <= area.getLastColumn(); c++) {
                    dependencies.addAll(resolveCellDependencies(fromRowColumn(r, c), sheet, workbook));
                }
            }
        }

        dependencies.add(cellAddress);
    }

    return dependencies;
}

From source file:com.dataart.spreadsheetanalytics.engine.graph.PoiDependencyGraphBuilder.java

License:Apache License

public static IExecutionGraph buildDependencyGraph(IDataModel dataModel, IA1Address cell) {
    if (dataModel == null) {
        throw new CalculationEngineException("DataModel and PoiModel are required to build dependency graph");
    }//from   ww  w  . j  ava  2s  . co m

    PoiDependencyGraphBuilder db = new PoiDependencyGraphBuilder(dataModel);

    Sheet s = db.poiBook.getSheetAt(0); //TODO: works for only one sheet workbooks
    if (s == null) {
        return null;
    }
    Row r = s.getRow(cell.row());
    if (r == null) {
        return null;
    }
    Cell c = r.getCell(cell.column());
    if (c == null) {
        return null;
    }

    ExecutionGraphVertex v = ExecutionGraph
            .createVertex(A1Address.fromRowColumn(c.getRowIndex(), c.getColumnIndex()).address());
    db.state.addVertex(v);

    if (CELL_TYPE_FORMULA == c.getCellType()) {
        db.collect(v, c.getCellFormula());
    }

    return db.state;
}