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

Introduction

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

Prototype

short getFirstCellNum();

Source Link

Document

Get the number of the first cell contained in this row.

Usage

From source file:com.b2international.snowowl.snomed.core.refset.automap.XlsParser.java

License:Apache License

/**
 * Returns the first logical row which contains a logical number or string.
 * /*from  w  ww .  ja  v a 2 s  . c om*/
 * @param sheet
 * @return
 */
private int findFirstRow(Sheet sheet) {
    int i = -1;
    Iterator<Row> iterator = sheet.iterator();

    if (iterator == null || !iterator.hasNext()) {
        return -1;
    }

    int cellType = -1;
    do {
        Row row = iterator.next();
        if (row == null) {
            return -1;
        }
        short firstLogicalCell = row.getFirstCellNum();
        Cell cell = row.getCell(firstLogicalCell);
        if (cell != null) {
            cellType = cell.getCellType();
        }
        i++;
    } while (cellType != Cell.CELL_TYPE_STRING && cellType != Cell.CELL_TYPE_NUMERIC);

    return i;
}

---

From source file:com.bayareasoftware.chartengine.ds.util.ExcelInference.java

License:Apache License

private static boolean match(Row row1, Row row2) {
    if (row1.getLastCellNum() != row2.getLastCellNum() || row1.getFirstCellNum() != row2.getFirstCellNum()) {
        return false;
    }//  w ww.  ja  v  a2  s  . com
    //p("rows " + row1.getRowNum() + "/" + row2.getRowNum() + " MIGHT be a match");
    Iterator cs1 = row1.cellIterator();
    Iterator cs2 = row2.cellIterator();
    while (cs1.hasNext()) {
        HSSFCell c1 = (HSSFCell) cs1.next();
        if (!cs2.hasNext()) {
            return false;
        }
        HSSFCell c2 = (HSSFCell) cs2.next();
        if (c1.getCellNum() != c2.getCellNum())
            return false;
        if (c1.getCellType() != c2.getCellType())
            return false;
    }
    return true;
}

---

From source file:com.clican.pluto.dataprocess.engine.processes.ExcelProcessor.java

License:LGPL

public void readExcel(ProcessorContext context, ExcelExecBean execBean) throws Exception {
    InputStream is = new AutoDecisionResource(execBean.getResource()).getInputStream();
    try {/*from w w  w  .  j a v  a  2  s.  com*/
        Workbook book = WorkbookFactory.create(is);
        Sheet sheet = book.getSheet(execBean.getSheetName());
        List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
        List<String> names = new ArrayList<String>();
        Map<String, String> typeMap = execBean.getTypeMap();
        int firstRow = sheet.getFirstRowNum(), lastRow = sheet.getLastRowNum();
        for (int rowIdx = firstRow; rowIdx < lastRow; rowIdx++) {
            Row excelRow = sheet.getRow(rowIdx);

            short minColIx = excelRow.getFirstCellNum();
            short maxColIx = excelRow.getLastCellNum();

            Map<String, Object> row = new HashMap<String, Object>();

            for (int colIdx = minColIx; colIdx < maxColIx; colIdx++) {
                Cell cell = excelRow.getCell(colIdx, Row.CREATE_NULL_AS_BLANK);

                if (rowIdx == 0) {
                    names.add(cell.getStringCellValue());
                } else {
                    String type = null;
                    if (names.size() > colIdx) {
                        type = typeMap.get(names.get(colIdx));
                    }
                    if (StringUtils.isNotEmpty(type)) {
                        if (type.equals("string")) {
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            row.put(names.get(colIdx), cell.getStringCellValue().trim());
                        } else if (type.equals("double")) {
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            row.put(names.get(colIdx), cell.getNumericCellValue());
                        } else if (type.equals("int")) {
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            row.put(names.get(colIdx), (int) cell.getNumericCellValue());
                        } else if (type.equals("date")) {
                            row.put(names.get(colIdx), cell.getDateCellValue());
                        } else {
                            throw new DataProcessException("??Excel?");
                        }
                    }
                }
            }
            if (rowIdx != 0) {
                result.add(row);
            }
        }
        context.setAttribute(execBean.getResultName(), result);
    } finally {
        if (is != null) {
            is.close();
        }
    }

}

---

From source file:com.cn.util.Units.java

public static boolean isEmptyRowForExcel(Row row) {
    for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
        Cell cell = row.getCell(c);/*from  www.  j a va  2 s  . c o m*/
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
            return false;
        }
    }
    return true;
}

---

From source file:com.common.report.util.html.ToHtml.java

License:Apache License

private void printSheetContent(Sheet sheet) {
    //        printColumnHeads();

    out.format("<tbody>%n");
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
        Row row = rows.next();

        out.format("  <tr>%n");
        out.format("    <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1);
        for (int i = firstColumn; i < endColumn; i++) {
            String content = "&nbsp;";
            String attrs = "";
            CellStyle style = null;/*from   w  w  w .  j a v a  2  s.com*/
            if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    style = cell.getCellStyle();
                    attrs = tagStyle(cell, style);
                    //Set the value that is rendered for the cell
                    //also applies the format
                    CellFormat cf = CellFormat.getInstance(style.getDataFormatString());
                    CellFormatResult result = cf.apply(cell);
                    content = result.text;
                    if (content.equals(""))
                        content = "&nbsp;";
                }
            }
            out.format("    <td class=%s %s>%s</td>%n", styleName(style), attrs, content);
        }
        out.format("  </tr>%n");
    }
    out.format("</tbody>%n");
}

---

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.
 *//*  ww  w .java  2 s.  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.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.
 *//*www  . j a  v  a2s  .co 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.DataSetConverters.java

License:Apache License

/**
 * Converts a {@link Workbook} to new {@link IDataSet}.
 * Ignores empty rows./*from w ww  .ja v  a2  s .c  om*/
 * 
 * @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.eleven0eight.xls2json.App.java

License:Open Source License

public String convertXlsToJson(FileInputStream fis) throws Exception {

    Workbook workbook = WorkbookFactory.create(fis);
    Sheet sheet = workbook.getSheetAt(0);
    JSONObject json = new JSONObject();
    JSONArray items = new JSONArray();
    ArrayList cols = new ArrayList();

    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        JSONObject item = new JSONObject();

        for (short colIndex = row.getFirstCellNum(); colIndex <= row.getLastCellNum(); colIndex++) {
            Cell cell = row.getCell(colIndex);
            if (cell == null) {
                continue;
            }//from  w ww .  j av a  2s. com
            if (i == 0) { // header
                cols.add(colIndex, cell.getStringCellValue());
            } else {
                item.put((String) cols.get(colIndex), cell.getStringCellValue());
            }
        }
        if (item.length() > 0) {
            items.put(item);
        }
    }
    json.put("items", items);
    return json.toString();

}

---

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

/***
 * Get contents of a sheet into text rows and columns
 * /*from ww  w . j  av a 2  s  .  co  m*/
 * @param sheet
 * @return
 */
private String[][] getRawData(Sheet sheet, boolean expectValueInFirstColumn) {

    // let us get a normalized rows/columns out of this sheet.
    int firstRowIdx = sheet.getFirstRowNum();
    Row firstRow = sheet.getRow(firstRowIdx);
    int firstCellIdx = firstRow.getFirstCellNum();
    int lastCellAt = firstRow.getLastCellNum();
    int nbrCells = lastCellAt - firstCellIdx;

    int lastRow = sheet.getLastRowNum();

    List<String[]> rawData = new ArrayList<String[]>();
    for (int rowNbr = firstRowIdx; rowNbr <= lastRow; rowNbr++) {
        Row row = sheet.getRow(rowNbr);
        if (row == null || row.getPhysicalNumberOfCells() == 0) {
            Spit.out(
                    "row at " + rowNbr + "is empty. while this is not an error, we certianly discourage this.");
            continue;
        }

        String[] rowData = this.getTextValues(row, firstCellIdx, nbrCells);
        if (rowData == null) {
            continue;
        }
        if (expectValueInFirstColumn) {
            String firstData = rowData[0];
            if (firstData == null || firstData.length() == 0) {
                Spit.out("row at" + rowNbr + " has its first column empty, and hence the row is ignored");
                continue;
            }
        }
        rawData.add(rowData);
    }

    if (rawData.size() > 0) {
        return rawData.toArray(new String[0][0]);
    }
    return null;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• »