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.exilant.exility.core.XlxUtil.java

License:Open Source License

/***
 * get data types of column based on actual values in the sheet
 * /*from   w w  w.  j a  v  a  2  s.co m*/
 * @param sheet
 * @param nbrCells
 * @param rowStart
 * @param rowEnd
 * @return
 */
private DataValueType[] getExilityTypes(Sheet sheet, int nbrCells) {
    DataValueType[] types = new DataValueType[nbrCells];

    // though NULL is default (as of now that is the first one in ENUM) let
    // us explicitly populate it
    for (int i = 0; i < nbrCells; i++) {
        types[i] = DataValueType.NULL;
    }

    int rowStart = sheet.getFirstRowNum();
    int rowEnd = sheet.getLastRowNum();
    int nbrFound = 0;

    // which cell to start? We will go by the first cell of the first
    // physucal row
    Row firstRow = sheet.getRow(sheet.getFirstRowNum());
    int startingCellIdx = firstRow.getFirstCellNum();
    int endCellIdx = startingCellIdx + nbrCells;
    for (int i = rowStart; i <= rowEnd; i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }

        for (int j = startingCellIdx; j < endCellIdx; j++) {
            // do we already know this type?
            if (types[j] != DataValueType.NULL) {
                continue;
            }

            Cell cell = row.getCell(j, Row.RETURN_BLANK_AS_NULL);
            if (cell == null) {
                continue;
            }
            types[j] = this.getExilityType(cell);
            nbrFound++;
            if (nbrFound == nbrCells) {
                return types;
            }
        }
    }

    // we will treat unknown ones as text
    for (int i = 0; i < nbrCells; i++) {
        if (types[i] == DataValueType.NULL) {
            types[i] = DataValueType.TEXT;
        }
    }

    return types;
}

---

From source file:com.faizod.aem.component.core.servlets.datasources.impl.ExcelDatasourceParser.java

License:Apache License

@Override
public Map<Object, List<Object>> parseMultiColumn(InputStream inputStream) {
    Map<Object, List<Object>> map = new LinkedHashMap<Object, List<Object>>();

    // read in the Excel file
    try {//ww  w  .jav  a 2  s  .com
        Workbook workbook = WorkbookFactory.create(inputStream);
        Sheet sheet = workbook.getSheetAt(0);

        Iterator<Row> rows = sheet.iterator();
        while (rows.hasNext()) {
            Row row = rows.next();
            List<Cell> cells = new ArrayList<Cell>();
            short lineMin = row.getFirstCellNum();
            short lineMax = row.getLastCellNum();

            for (short index = lineMin; index < lineMax; index++)
                cells.add(row.getCell(index));

            Object label = "";
            switch (cells.get(0).getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                label = cells.get(0).getNumericCellValue();
                break;
            case Cell.CELL_TYPE_STRING:
                label = "" + (cells.get(0).getStringCellValue());
                break;
            default:
                break;
            }

            List<Object> values = new ArrayList<Object>();

            for (short index = 1; index < (lineMax - lineMin); index++) {
                Object value;

                switch (cells.get(index).getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = cells.get(index).getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    value = cells.get(index).getNumericCellValue();
                    break;
                default:
                    value = new Object();
                    break;
                }
                values.add(value);
            }
            map.put(label, values);
        }
    } catch (IOException e) {
        LOG.error("Unable to read datasource.", e);
        throw new DatasourceException("Unable to read datasource.", e);
    } catch (InvalidFormatException e) {
        LOG.error("File Format not supported.", e);
        throw new DatasourceException("File Format not supported.", e);
    }
    return map;
}

---

From source file:com.fjn.helper.common.io.file.office.excel.ExcelUtil.java

License:Apache License

/**
 * ????/*from  w ww . j  a va2s .c o  m*/
 *
 * @param sheet
 * @param rowIndex
 * @param style
 * @return
 */
public static boolean setRowStyle(Sheet sheet, int rowIndex, CellStyle style) {
    if (sheet != null) {
        Row row = sheet.getRow(rowIndex);
        if (row != null) {
            short firstColumnIndex = row.getFirstCellNum();
            short lastColumnIndex = row.getLastCellNum();
            for (short colunmIndex = firstColumnIndex; colunmIndex < lastColumnIndex; colunmIndex++) {
                CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
                Cell cell = row.getCell(colunmIndex);
                if (cell != null) {
                    cellStyle.cloneStyleFrom(cell.getCellStyle());
                    cellStyle.cloneStyleFrom(style);
                    cell.setCellStyle(cellStyle);
                }
            }
        }
    }

    return true;
}

---

From source file:com.fjn.helper.common.io.file.office.excel.ListExcelSheetEditor.java

License:Apache License

/**
 * ????//  w  w w .  ja  va  2 s . co  m
 * @param sheet
 * @param rowIndex
 * @param style
 * @return
 */
public boolean setRowStyle(int rowIndex, CellStyle style) {
    Sheet sheet = excelSheet.sheet;
    if (sheet != null) {
        Row row = sheet.getRow(rowIndex);
        if (row != null) {
            short firstColumnIndex = row.getFirstCellNum();
            short lastColumnIndex = row.getLastCellNum();
            for (short colunmIndex = firstColumnIndex; colunmIndex < lastColumnIndex; colunmIndex++) {
                CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
                Cell cell = row.getCell(colunmIndex);
                if (cell != null) {
                    cellStyle.cloneStyleFrom(cell.getCellStyle());
                    cellStyle.cloneStyleFrom(style);
                    cell.setCellStyle(cellStyle);
                }
            }
        }
    }

    return true;
}

---

From source file:com.github.camaral.sheeco.Sheeco.java

License:Apache License

private boolean isBlankRow(final Row row) {
    for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); i++) {
        final Cell cell = row.getCell(i);
        if (cell != null && row.getCell(i).getCellType() != Cell.CELL_TYPE_BLANK) {
            return false;
        }/* w  w w.  j av a 2  s. c  o m*/
    }
    return true;
}

---

From source file:com.googlecode.testcase.annotation.handle.toexcel.strategy.ToHtmlWithExcel.java

License:Apache License

private void printSheetContent(Sheet sheet) {
    printColumnHeads(sheet);/* ww  w .  j ava  2 s  .  c  o  m*/

    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;
            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.hurry.excel.html.Excel2Html.java

License:Apache License

private void printSheetContent(Sheet sheet) {
    printColumnHeads();/*from   ww w.  ja va  2  s  .  c o m*/

    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;
            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.ibm.db2j.GExcel.java

License:Open Source License

/**
 * Initialize the attributes :/* w ww.java  2s. c  om*/
 * 
 * - inputStream
 * - workbook
 * - evaluator
 * - sheet
 * - firstRowIsMetaData
 * 
 * - firstColumnIndex
 * - firstRowIndex
 * - lastColumnIndex
 * - lastRowIndex
 * 
 * @param fileName
 * @param spreadsheetName
 * @param firstCellRange
 * @param lastCellRange
 * @param interpretFirstLineAsMetaData
 * @throws SQLException
 */
public void initialize(String fileName, String spreadsheetName, String firstCellRange, String lastCellRange,
        boolean interpretFirstLineAsMetaData) throws SQLException {
    try {
        inputStream = new FileInputStream(fileName);
        workbook = WorkbookFactory.create(inputStream);
        evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        sheet = findSpreadsheet(workbook, spreadsheetName);
        firstRowIsMetaData = interpretFirstLineAsMetaData;

        if (firstCellRange != null && lastCellRange != null) {
            CellReference firstCell = new CellReference(firstCellRange);

            // Deduce last row number if it was not specified
            if (lastCellRange.matches("[a-zA-Z]+")) {
                lastCellRange += (sheet.getLastRowNum() + 1); //Note: getLastRowNum is 0-based
                stopScanOnFirstEmptyRow = true;
                logger.logInfo("Deduced last row in Excel table: " + lastCellRange
                        + " - but scans will end on first empty row");
            }

            CellReference lastCell = new CellReference(lastCellRange);

            firstColumnIndex = firstCell.getCol();
            firstRowIndex = firstCell.getRow(); // + (firstRowIsMetaData?1:0);
            lastColumnIndex = lastCell.getCol();
            lastRowIndex = lastCell.getRow();
        } else {
            Row firstRow = locateFirstRow(sheet);

            if (firstRow == null) {
                throw new SQLException("Empty spreadsheet !");
            }

            firstRowIndex = firstRow.getRowNum(); // + (firstRowIsMetaData?1:0);
            lastRowIndex = sheet.getLastRowNum();
            firstColumnIndex = firstRow.getFirstCellNum(); //Note: getFirstCellNum is 0-based
            lastColumnIndex = firstRow.getLastCellNum() - 1; //Note: getLastCellNum is 1-based
        }

        //System.out.println("sheet: " + sheet.getSheetName() + ", firstcolindex: " + firstColumnIndex + ", lastcolindex: " + lastColumnIndex + ", firstrowindex: " + firstRowIndex + ", lastrowindex: " + lastRowIndex);
    } catch (Exception e) {
        throw new SQLException(e.getMessage());
    }
}

---

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;/*from w w  w  .j a v  a 2  s  .c  o  m*/
    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.infovity.iep.loader.util.SupplierLoaderUtil.java

public static boolean rowHasData(Row row) {
    short cellNumber;
    boolean nonBlankRowFound = false;
    for (cellNumber = row.getFirstCellNum(); cellNumber <= row.getLastCellNum(); cellNumber++) {
        Cell cell = row.getCell(cellNumber);
        if (cell != null && row.getCell(cellNumber).getCellType() != cell.CELL_TYPE_BLANK) {
            nonBlankRowFound = true;/*from ww w.  j a  v  a 2 s  .c om*/
        }
    }
    return nonBlankRowFound;
}

---

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