Example usage for org.apache.poi.ss.usermodel Sheet getRow

Introduction

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

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:com.cordys.coe.ac.fileconnector.utils.ExcelRead.java

License:Apache License

/**
 * Read records from Excel file// ww  w  . j a  v  a 2s.  c  o  m
 *
 * @param vcConfig The validator configuration object.
 * @param bUseTupleOld
 * @param filename Name of the Excel file.
 * @param doc Document conatins the request.
 * @param iResponsenode The record XML structure root node, or zero, if only validation is needed.
 * @param sheetno Sheet index of the Excel file.
 * @param startrow row index from which data to be read.
 * @param endrow   row index upto which data to be read.
 * @param startcolumn column index from which data to be read.
 * @param endcolumn column index upto which data to be read.
 */
public static void readall(ValidatorConfig vcConfig, Boolean bUseTupleOld, String filename, Document doc,
        int iResponsenode, int sheetno, int startrow, int endrow, int startcolumn, int endcolumn)
        throws FileException {

    Workbook book = null;
    Sheet sheet;
    Cell cell;
    Row row;
    FileInputStream fileinp = null;
    String sRecordName = vcConfig.mConfigMap.get("excel").lRecordList.get(0).sRecordName;
    try {
        int iRow, iCol, sheetindex, noofsheets;
        File file = new File(filename);
        fileinp = new FileInputStream(filename);
        if (file.exists()) {
            if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xls")) {
                book = (Workbook) new HSSFWorkbook(fileinp);
            } else if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xlsx")) {
                book = new XSSFWorkbook(fileinp);
            } else {
                //ERROR
                fileinp.close();
            }
        } else {
            //ERROR
            fileinp.close();
        }

        if (sheetno != -1) {
            sheetindex = sheetno;
            noofsheets = sheetindex + 1;
        } else {
            sheetindex = 0;
            noofsheets = book.getNumberOfSheets();
        }
        for (; sheetindex < noofsheets; sheetindex++) {
            sheet = book.getSheetAt(sheetindex);

            if (endrow == -1) {
                endrow = sheet.getLastRowNum();
                if (startrow == -1) {
                    startrow = 0;
                }
            } else {
                endrow = startrow + endrow - 1;
                if (endrow > sheet.getLastRowNum()) {
                    endrow = sheet.getLastRowNum();
                }
            }

            if (endcolumn == -1) {
                endcolumn = 30;
                if (startcolumn == -1) {
                    startcolumn = 0;
                }
            }
            for (int i = startrow; i <= endrow; i++) {

                row = sheet.getRow(i);

                if (row == null) {
                    int iTup = doc.createElement("tuple", iResponsenode);

                    if (bUseTupleOld) {
                        iTup = doc.createElement("old", iTup);
                    }
                    iRow = doc.createElement(sRecordName, iTup);
                    //Node.setAttribute(iRow, "id", "" + i);
                    ListIterator fieldslist = vcConfig.mConfigMap.get("excel").lRecordList.get(0).lFieldList
                            .listIterator();
                    while (fieldslist.hasNext()) {
                        FieldType excelfields = (FieldType) fieldslist.next();
                        String sColumnName = excelfields.sFieldName;

                        iCol = doc.createTextElement(sColumnName, "", iRow);
                    }
                    continue;
                }
                int iTup = doc.createElement("tuple", iResponsenode);
                if (bUseTupleOld) {
                    iTup = doc.createElement("old", iTup);
                }
                iRow = doc.createElement(sRecordName, iTup);
                ListIterator fieldslist = vcConfig.mConfigMap.get("excel").lRecordList.get(0).lFieldList
                        .listIterator();
                while (fieldslist.hasNext()) {
                    FieldType excelfields = (FieldType) fieldslist.next();
                    int iColumnIndex = Integer.parseInt(excelfields.sColumnIndex);
                    cell = row.getCell(iColumnIndex);
                    String sColumnName = excelfields.sFieldName;
                    if (cell == null) {
                        iCol = doc.createTextElement(sColumnName, "", iRow);
                        continue;
                    }
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        iCol = doc.createTextElement(sColumnName, "", iRow);
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        iCol = doc.createTextElement(sColumnName, "" + cell.getBooleanCellValue(), iRow);

                        break;
                    case Cell.CELL_TYPE_ERROR:
                        iCol = doc.createTextElement(sColumnName, "", iRow);
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        iCol = doc.createTextElement(sColumnName, "" + cell.getCellFormula(), iRow);

                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            SimpleDateFormat simpledateformat = new SimpleDateFormat(
                                    "yyyy-MM-dd 'T' HH:mm:ss.S");
                            iCol = doc.createTextElement(sColumnName,
                                    "" + simpledateformat.format(cell.getDateCellValue()), iRow);

                        } else {
                            iCol = doc.createTextElement(sColumnName, "" + cell.getNumericCellValue(), iRow);
                        }
                        break;
                    case Cell.CELL_TYPE_STRING:
                        iCol = doc.createTextElement(sColumnName, "" + cell.getStringCellValue(), iRow);
                        break;
                    default:
                        System.out.println("default");
                    }
                }
            }
        }
    } catch (FileNotFoundException e) {
        throw new FileException(e, LogMessages.FILE_NOT_FOUND);
    } catch (IOException e) {
        throw new FileException(e, LogMessages.IOEXCEPTION_WHILE_READING_FILE, filename);
    } finally {
        try {
            fileinp.close();
        } catch (IOException ex) {
            Logger.getLogger(ExcelRead.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

---

From source file:com.cpjd.roblu.csv.ToCSV.java

License:Apache License

/**
 * Called to convert the contents of the currently opened workbook into
 * a CSV file./*ww w .j  a  v  a  2  s.co  m*/
 */
private void convertToCSV() {
    Sheet sheet;
    Row row;
    int lastRowNum;
    this.csvData = new ArrayList<>();

    System.out.println("Converting files contents to CSV format.");

    // Discover how many sheets there are in the workbook....
    int numSheets = this.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 = this.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
            // for inclusion in the resylting CSV file.
            lastRowNum = sheet.getLastRowNum();
            for (int j = 0; j <= lastRowNum; j++) {
                row = sheet.getRow(j);
                this.rowToCSV(row);
            }
        }
    }
}

---

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);
    int rows = sheet.getLastRowNum();
    int cells = sheet.getRow(0).getPhysicalNumberOfCells();
    for (int i = 0; i < rows; i++) {
        Row row = sheet.getRow(i + 1);//  www  .j  a  v  a  2 s  .  c  om
        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 w w .  j ava  2 s .c o m*/
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 www .ja  v  a  2  s .  c o  m
    }
    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  www  . 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());
    }//  ww w .  ja va2 s . c om

    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 ww  . j  a v  a  2 s . co 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.
 *///  w w w .  j  av a  2  s. c  om
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();
    }/*w  w w . j a va 2 s  . c o m*/
    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;
}

---

• «
• 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
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »