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

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

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

Introduction

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

Prototype

short getLastCellNum();

Source Link

Document

Gets the index of the last cell contained in this row PLUS ONE.

Usage

From source file:ro.dabuno.office.integration.Data.java

private void readExcelFile(File excelFile)
        throws EncryptedDocumentException, InvalidFormatException, IOException {
    try (Workbook wb = WorkbookFactory.create(excelFile, null, true)) {
        Sheet sheet = wb.getSheetAt(0);//www . ja  v  a 2  s . c o  m
        if (sheet == null) {
            throw new IllegalArgumentException(
                    "Provided Microsoft Excel file " + excelFile + " does not have any sheet");
        }

        final int start;
        final int end;
        { // read headers
            Row row = sheet.getRow(0);
            if (row == null) {
                throw new IllegalArgumentException("Provided Microsoft Excel file " + excelFile
                        + " does not have data in the first row in the first sheet, "
                        + "but we expect the header data to be located there");
            }

            start = row.getFirstCellNum();
            end = row.getLastCellNum();
            for (int cellnum = start; cellnum <= end; cellnum++) {
                Cell cell = row.getCell(cellnum);
                if (cell == null) {
                    // add null to the headers if there are columns without title in the sheet
                    headers.add(null);
                    log.info("Had empty header for column " + CellReference.convertNumToColString(cellnum));
                } else {
                    String value = cell.toString();
                    headers.add(value);
                    log.info("Had header '" + value + "' for column "
                            + CellReference.convertNumToColString(cellnum));
                }
            }
        }

        for (int rownum = 1; rownum <= sheet.getLastRowNum(); rownum++) {
            Row row = sheet.getRow(rownum);
            if (row == null) {
                // ignore missing rows
                continue;
            }

            List<String> data = new ArrayList<>();
            for (int colnum = start; colnum <= end; colnum++) {
                Cell cell = row.getCell(colnum);
                if (cell == null) {
                    // store null-data for empty/missing cells
                    data.add(null);
                } else {
                    final String value;
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        // ensure that numeric are formatted the same way as in the Excel file.
                        value = CellFormat.getInstance(cell.getCellStyle().getDataFormatString())
                                .apply(cell).text;
                        break;
                    default:
                        // all others can use the default value from toString() for now.
                        value = cell.toString();
                    }

                    data.add(value);
                }
            }

            values.add(data);
        }
    }
}

From source file:ro.fortsoft.dataset.xls.XlsDataSet.java

License:Apache License

protected DataSetMetaData createDefaultMetaData() {
    BaseDataSetMetaData metaData = new BaseDataSetMetaData();

    Sheet sheet = workbook.getSheetAt(sheetIndex);
    Row row = sheet.getRow(0);
    int columnCount = row.getLastCellNum();
    for (int i = 0; i < columnCount; i++) {
        Cell cell = row.getCell(i);/*from w  ww  . j a v  a2s . c  om*/
        if (cell != null) {
            int cellType = cell.getCellType();
            Class<?> valueClass = String.class;
            if (Cell.CELL_TYPE_BOOLEAN == cellType) {
                valueClass = Boolean.class;
            } else if (Cell.CELL_TYPE_NUMERIC == cellType) {
                valueClass = Double.class;
            }
            metaData.addField(cell.toString(), valueClass, i);
        } else {
            metaData.addField("Field_" + i, String.class, i);
        }
    }

    return metaData;
}

From source file:ru.spb.nicetu.tableviewer.server.XlsToHtml.java

License:Apache License

private void printSheetContent(Sheet sheet) {
    printColumnHeads();//from  w w w.  j  av  a  2  s  . co m

    out.format("<tbody>%n");
    Iterator<Row> rows = sheet.rowIterator();
    int lastNum = -1;
    while (rows.hasNext()) {
        Row row = rows.next();
        int curNum = row.getRowNum();
        if (curNum - lastNum > 1) {
            for (int i = lastNum + 2; i <= curNum; i++) {
                out.format("  <tr>%n");
                out.format("    <td class=%s>%d</td>%n", ROW_HEAD_CLASS, i);
                out.format("    <td colspan=%d style=\"%s\">&nbsp;</td>%n", (endColumn - firstColumn + 1),
                        styleSimpleContents(null, false));
                out.format("  </tr>%n");
            }
        }
        lastNum = curNum;

        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;
            boolean isNumeric = false;
            if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    style = cell.getCellStyle();
                    attrs = tagStyle(cell, style);

                    CellFormat cf = CellFormat.getInstance(style.getDataFormatString());
                    CellFormatResult result = cf.apply(cell);
                    content = result.text;
                    if (content != null && !content.equals("")
                            && (cell.getCellType() == Cell.CELL_TYPE_NUMERIC
                                    || cell.getCellType() == Cell.CELL_TYPE_FORMULA
                                            && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC)) {
                        if (DateUtil.isCellDateFormatted(cell)) {
                            // Date
                            if ("mmm-yy".equals(style.getDataFormatString())) {
                                SimpleDateFormat sdfRus = new SimpleDateFormat("MMM.yy");
                                content = sdfRus.format(cell.getDateCellValue());
                            } else if ("h:mm".equals(style.getDataFormatString())) {
                                SimpleDateFormat sdfRus = new SimpleDateFormat("HH:mm");
                                content = sdfRus.format(cell.getDateCellValue());
                            } else if (style.getDataFormatString() != null
                                    && style.getDataFormatString().contains("mm")) {
                                SimpleDateFormat sdfRus = new SimpleDateFormat("dd.MM.yyyy HH:mm:ss");
                                content = sdfRus.format(cell.getDateCellValue());
                            } else {
                                SimpleDateFormat sdfRus = new SimpleDateFormat("dd.MM.yyyy");
                                content = sdfRus.format(cell.getDateCellValue());
                            }
                        } else {
                            // Number
                            if ("- 0".equals(content.trim()))
                                content = "&nbsp;";
                            else
                                content = "<nobr>" + content.replace(",", " ").replace(".", ",") + "</nobr>";
                            isNumeric = true;
                        }
                    }

                    if (content == null || content.equals(""))
                        content = "&nbsp;";
                }
            }

            boolean isInRangeNotFirst = false;
            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                CellRangeAddress rangeAddress = sheet.getMergedRegion(j);
                if (row.getRowNum() == rangeAddress.getFirstRow() && i == rangeAddress.getFirstColumn()) {
                    if (rangeAddress.getLastRow() - row.getRowNum() > 0)
                        attrs += " rowspan=" + (rangeAddress.getLastRow() - row.getRowNum() + 1);
                    if (rangeAddress.getLastColumn() - i > 0)
                        attrs += " colspan=" + (rangeAddress.getLastColumn() - i + 1);
                    break;
                } else if (row.getRowNum() >= rangeAddress.getFirstRow()
                        && row.getRowNum() <= rangeAddress.getLastRow() && i >= rangeAddress.getFirstColumn()
                        && i <= rangeAddress.getLastColumn()) {
                    isInRangeNotFirst = true;
                    break;
                }
            }

            if (!isInRangeNotFirst) {
                out.format("    <td style=\"%s\" %s>%s</td>%n", styleSimpleContents(style, isNumeric), attrs,
                        content);
            }
        } // columns
        out.format("  </tr>%n");
    } // rows

    out.format("</tbody>%n");
}

From source file:se.softhouse.garden.oak.ExcelDecisionTableBuilder.java

License:Open Source License

private List<Integer> locateActionTables(Sheet sheet) {
    List<Integer> indexes = new ArrayList<Integer>();
    Row tableTypeRow = sheet.getRow(1);
    Row opRow = sheet.getRow(5);//  w  ww  .j a v  a2  s.  c  o m

    for (int i = tableTypeRow.getFirstCellNum(); i < tableTypeRow.getLastCellNum(); i++) {
        Cell typeRowCell = tableTypeRow.getCell(i);
        if (typeRowCell == null) {
            throw new NullPointerException("Sheet[" + sheet.getSheetName() + "] - Row:Col[1:" + i
                    + "] - Cell object is null. (#columns = " + tableTypeRow.getLastCellNum() + ")");
        }
        String cellValue = typeRowCell.getStringCellValue();
        if (cellValue == null) {
            throw new NullPointerException(
                    "Sheet[" + sheet.getSheetName() + "] - Row:Col[1:" + i + "] - Cell value is null.");
        }
        if (!cellValue.isEmpty()) {
            indexes.add(i);
        }
    }
    indexes.add((int) opRow.getLastCellNum());
    return indexes;
}

From source file:service.Read_Write_File.java

public static List<Consomation> Read_Fil_XLSX(File myFile) throws FileNotFoundException, IOException {
    List<CorpDetat> corpDetats = new ArrayList<CorpDetat>();
    List<Consomation> consomations = new ArrayList<Consomation>();

    FileInputStream fis = new FileInputStream(myFile);

    XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);

    XSSFSheet mySheet = myWorkBook.getSheetAt(0);

    Iterator<Row> rowIterator = mySheet.iterator();

    Consomation consomation = null;//  w w w. j a va 2 s  .c  o  m
    CorpDetat corpDetat = null;
    ConsomationItem consomationItem = null;
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        if (row.getRowNum() == 0) {// pour ne pas lire les  titres
            row = rowIterator.next();
        }

        int lascellNum = row.getLastCellNum();
        int firstcellNum = row.getFirstCellNum();
        System.out.println("first cell num" + firstcellNum + "  last cell num" + lascellNum);
        if (lascellNum < 3) {
            if (row.getRowNum() > 1) {
                //corpDetats.add(corpDetat);

                consomations.add(consomation);
                corpDetat = null;
                System.out.println("if dyal row celll");
            }
            consomation = new Consomation();
            consomation.setId((int) row.getCell(firstcellNum).getNumericCellValue());
            corpDetat = new CorpDetat();
            corpDetat.setTitre(row.getCell(firstcellNum + 1).getStringCellValue());

        } else {
            Post post = new Post();
            consomationItem = new ConsomationItem();

            for (int i = firstcellNum; i < lascellNum; i++) {
                Cell cell = row.getCell(i);

                switch (i) {
                case 0:
                    System.out.println("num de consomation item" + cell.getStringCellValue());
                    consomationItem.setId(cell.getStringCellValue());
                    break;
                case 1:
                    System.out.println("cell " + i + ":" + cell.getStringCellValue());
                    post.setTitre(cell.getStringCellValue());
                    break;
                case 2:
                    consomationItem.setUnite(cell.getStringCellValue());
                    break;
                case 3:
                    System.out.println("cell " + i + ":" + cell.getCellType());
                    consomationItem.setQuanite((int) cell.getNumericCellValue());
                    break;

                }
            }
            if (post != null) {
                post.setCorpdetat(corpDetat);
                consomationItem.setPost(post);
                post.setCorpdetat(corpDetat);
                corpDetat.getPosts().add(post);
                consomation.getConsomationItems().add(consomationItem);
                consomation.setCorpDetat(corpDetat);

            }
        }

    }

    consomations.add(consomation);
    // pour le dernier corp makydkholch l row li tab30

    return consomations;
}

From source file:simbad.Excel.java

public double mayorLatitud(Workbook workbook) throws FileNotFoundException, IOException {

    Sheet sheet = workbook.getSheet("Sheet1");
    double mayorLat = 0;
    Row row = sheet.getRow(0);

    for (int j = 0; j < row.getLastCellNum(); j++) {
        if (row.getCell(j).getRichStringCellValue().toString().equals("lat_ciu"))
            for (int i = 1; i < sheet.getLastRowNum(); i++) {
                Row row2 = sheet.getRow(i);
                if (i == 1
                        || Double.parseDouble((row2.getCell(j).getRichStringCellValue().toString())) > mayorLat)
                    mayorLat = Double.parseDouble(row2.getCell(j).getRichStringCellValue().toString());
            }/*from www  .  j  a va2s .c o m*/
    }
    return mayorLat;

}

From source file:simbad.Excel.java

public double menorLatitud(Workbook workbook) throws FileNotFoundException, IOException {

    Sheet sheet = workbook.getSheet("Sheet1");
    double menorLat = 0;
    Row row = sheet.getRow(0);

    for (int j = 0; j < row.getLastCellNum(); j++) {
        if (row.getCell(j).getRichStringCellValue().toString().equals("lat_ciu"))
            for (int i = 1; i < sheet.getLastRowNum(); i++) {
                Row row2 = sheet.getRow(i);
                if (i == 1
                        || Double.parseDouble((row2.getCell(j).getRichStringCellValue().toString())) < menorLat)
                    menorLat = Double.parseDouble(row2.getCell(j).getRichStringCellValue().toString());
            }/*from  w w  w. ja v  a2s. c o m*/
    }

    return menorLat;
}

From source file:simbad.Excel.java

public double mayorLongitud(Workbook workbook) throws FileNotFoundException, IOException {

    Sheet sheet = workbook.getSheet("Sheet1");

    double mayorLon = 0;
    Row row = sheet.getRow(0);

    for (int j = 0; j < row.getLastCellNum(); j++) {
        if (row.getCell(j).getRichStringCellValue().toString().equals("lon_ciu"))
            for (int i = 1; i < sheet.getLastRowNum(); i++) {
                Row row2 = sheet.getRow(i);
                if (i == 1
                        || Double.parseDouble((row2.getCell(j).getRichStringCellValue().toString())) > mayorLon)
                    mayorLon = Double.parseDouble(row2.getCell(j).getRichStringCellValue().toString());
            }/*from   w w w  . j  ava  2 s .  c om*/
    }
    return mayorLon;
}

From source file:simbad.Excel.java

public double menorLongitud(Workbook workbook) throws FileNotFoundException, IOException {

    Sheet sheet = workbook.getSheet("Sheet1");
    double menorLon = 0;
    Row row = sheet.getRow(0);

    for (int j = 0; j < row.getLastCellNum(); j++) {
        if (row.getCell(j).getRichStringCellValue().toString().equals("lon_ciu"))
            for (int i = 1; i < sheet.getLastRowNum(); i++) {
                Row row2 = sheet.getRow(i);
                if (i == 1
                        || Double.parseDouble((row2.getCell(j).getRichStringCellValue().toString())) < menorLon)
                    menorLon = Double.parseDouble(row2.getCell(j).getRichStringCellValue().toString());
            }//from w ww . j  av  a 2s . c  o m
    }
    return menorLon;

}

From source file:simbad.reporteUnificado.java

private double mayorLatitud() throws FileNotFoundException, IOException {
    Workbook workbook = new XSSFWorkbook(new FileInputStream("D:\\ejemplo_datos.xlsx"));
    Sheet sheet = workbook.getSheet("Sheet1");
    double mayorLat = 0;
    Row row = sheet.getRow(0);

    for (int j = 0; j < row.getLastCellNum(); j++) {
        if (row.getCell(j).getRichStringCellValue().toString().equals("lat_ciu"))
            for (int i = 1; i < sheet.getLastRowNum(); i++) {
                Row row2 = sheet.getRow(i);
                if (i == 1
                        || Double.parseDouble((row2.getCell(j).getRichStringCellValue().toString())) > mayorLat)
                    mayorLat = Double.parseDouble(row2.getCell(j).getRichStringCellValue().toString());
            }/*from  ww w  .java 2  s .  c om*/
    }
    return mayorLat;

}