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:simbad.reporteUnificado.java

private double menorLatitud() throws FileNotFoundException, IOException {
    Workbook workbook = new XSSFWorkbook(new FileInputStream("D:\\ejemplo_datos.xlsx"));
    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 .j ava  2s. c  om*/
    }

    return menorLat;
}

From source file:simbad.reporteUnificado.java

private double mayorLongitud() throws FileNotFoundException, IOException {
    Workbook workbook = new XSSFWorkbook(new FileInputStream("D:\\ejemplo_datos.xlsx"));
    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   ww  w . j  a va  2  s .c o m
    }
    return mayorLon;
}

From source file:simbad.reporteUnificado.java

private double menorLongitud() throws FileNotFoundException, IOException {
    Workbook workbook = new XSSFWorkbook(new FileInputStream("D:\\ejemplo_datos.xlsx"));
    Sheet sheet = workbook.getSheet("Sheet1");
    double menorLon = 0;
    Row row = sheet.getRow(0);
    mayorLatitud(workbook);//from w  w w .  j  a va 2s  .  c  o  m
    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());
            }
    }
    return menorLon;

}

From source file:soc.scar.service.excel.ProjectExcelService.java

public List<FeatureValue> getFeatureValue(Row row, Feature feature) {
    List<FeatureValue> featuresValueByFeatureList = new ArrayList<>();
    for (int i = 2; i < row.getLastCellNum(); i++) {
        Cell cell = row.getCell(i);/* w w  w.  ja v a  2 s .  co m*/
        if (cell != null && !row.getCell(i).getStringCellValue().equalsIgnoreCase(EMPTY3)) {
            FeatureValue featureValue = new FeatureValue();
            featureValue.setFeatureValue(row.getCell(i).getStringCellValue());
            featuresValueByFeatureList.add(featureValue);
            modulesList.get(i - 2).addFeaturesValue(featureValue);
        }
        if (cell == null) {
            FeatureValue featureValue = new FeatureValue();
            featureValue.setFeatureValue("");
            featuresValueByFeatureList.add(featureValue);
            modulesList.get(i - 2).addFeaturesValue(featureValue);
        }

    }
    return featuresValueByFeatureList;
}

From source file:sol.neptune.elisaboard.service.VPlanToHtml.java

License:Apache License

private void ensureColumnBounds(Sheet sheet) {
    if (gotBounds) {
        return;// w w  w .  ja  va  2s.  c  o  m
    }

    Iterator<Row> iter = sheet.rowIterator();
    firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0);
    endColumn = 0;
    while (iter.hasNext()) {
        Row row = iter.next();
        short firstCell = row.getFirstCellNum();
        if (firstCell >= 0) {
            firstColumn = Math.min(firstColumn, firstCell);
            endColumn = Math.max(endColumn, row.getLastCellNum());
        }
    }

    if (maxColumns > 0 && endColumn > maxColumns) {
        endColumn = maxColumns;
    }
    gotBounds = true;
}

From source file:sol.neptune.elisaboard.service.VPlanToHtml.java

License:Apache License

private void printSheetContent(Sheet sheet) {
    /* skip column heads */
    //printColumnHeads();

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

        out.format("  <tr>%n");
        /* skip first col*/
        /*/*  w ww .j  a va 2 s  .com*/
         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 (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                        final Date date = cell.getDateCellValue();
                        System.out.println("Date: " + date);
                        System.out.println(new Date());

                    }
                    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:species.formatReader.SpreadsheetWriter.java

public static void writeDataInSheet(Workbook wb, JSONArray gridData, int sheetNo, String writeContributor,
        String contEmail, JSONArray orderedArray) {
    //System.out.println("================================" + writeContributor +"===" + contEmail );
    /*if(writeContributor.equals("true")){
    JSONObject r =  gridData.getJSONObject(0);
    if(!r.has("contributor")){// w  w w.ja v a  2 s.c  o  m
        for(int k = 0; k < gridData.length();k++){
            JSONObject r1 =  gridData.getJSONObject(k);
            r1.put("contributor", contEmail);
        }
    }
    }*/
    Sheet sheet = wb.getSheetAt(sheetNo);
    Iterator<Row> rowIterator = sheet.iterator();
    int index = 0;
    int i = 0;
    boolean headerRow = true;
    //System.out.println("===JSON ARRAY LENGTH==============");
    //System.out.println(gridData.length());
    int gDataSize = gridData.length();
    JSONObject rowData = gridData.getJSONObject(index);
    Iterator<String> keys = rowData.keys();
    int numKeys = 0;
    while (keys.hasNext()) {
        String kk = keys.next();
        numKeys++;
    }
    String[] keysArray = new String[numKeys];
    //String[] keysArray = orderedArray;
    for (int k = 0; k < numKeys; k++) {
        keysArray[k] = orderedArray.getString(k);
    }
    Row row = rowIterator.next();
    for (int a = 0; a < numKeys; a++) {
        Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK);
        //System.out.println("======PRINTING THIS TO HEADER CELL===== " + keysArray[a]);
        cell.setCellValue(keysArray[a]);
        i++;
    }
    int lastHeaderCellNum = row.getLastCellNum();
    for (int j = i; j <= lastHeaderCellNum; j++) {
        Cell cell = row.getCell(j, Row.CREATE_NULL_AS_BLANK);
        cell.setCellValue("");
    }

    for (int k = 0; k < gDataSize; k++) {
        //System.out.println("REACHED FOR LOOP");
        rowData = gridData.getJSONObject(index);
        //mapRow.put(gridData.get(count));
        //rowIterator.hasNext();
        //
        if (rowIterator.hasNext()) {
            row = rowIterator.next();
        } else {
            row = sheet.createRow(k + 1);
            for (int a = 0; a < numKeys; a++) {
                Cell cell = row.createCell(a);
            }
        }
        i = 0;
        //System.out.println("============ "); 
        //System.out.println(gridData);

        //for ( Map.Entry<String, String> entry : mapRow.entrySet()) {
        //while( keys.hasNext() ){
        for (int a = 0; a < numKeys; a++) {
            //String key = (String)keys.next();
            Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK);
            //System.out.println ("=====PRINTING THIS TO NORMAL CELL====== " + rowData.getString(keysArray[a]));
            cell.setCellValue(rowData.getString(keysArray[a]));
            i++;
        }
        index++;
        headerRow = false;
        // rest cells in that row overwritten with empty string
        int lastCellNum = row.getLastCellNum();
        for (int j = i; j <= lastCellNum; j++) {
            Cell cell = row.getCell(j, Row.CREATE_NULL_AS_BLANK);
            cell.setCellValue("");
        }
    }
    //overwrite rest row data in sheet
    while (rowIterator.hasNext()) {
        row = rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            cell.setCellValue("");
        }
    }
    return;
}

From source file:sqlitemanager.Excel2Dataset.java

public static List<DataTable> readExcel(String inPath, boolean hasIntColumns, int colsHasInt) {
    List<DataTable> out = new ArrayList();
    try {/*  ww  w  . j  av  a 2  s  .co  m*/

        // Create a work book reference
        Workbook workbook = null;
        if (inPath.endsWith(".xls")) {
            workbook = new HSSFWorkbook(new FileInputStream(inPath));
        } else if (inPath.endsWith(".xlsx")) {
            workbook = new XSSFWorkbook(new FileInputStream(inPath));
        } else {
            System.err.println("No XLS or XLSX file found!");
            return out;
        }

        //Create a count of the sheets in the file
        short sheetsCount = (short) workbook.getNumberOfSheets();

        //create a reference of sheet, cell, first head, last head, head name, 
        //sheet name, row count and row content
        Sheet sheet;
        Row row;
        Cell cell;
        int firstIndex = Integer.MIN_VALUE;
        int lastIndex = Integer.MAX_VALUE;
        String[] headName;
        fieldType[] fieldTypes;

        String sheetName;

        int rowCount;

        Object cellValue;

        for (int i = 0; i < sheetsCount; i++) {
            sheetName = workbook.getSheetName(i);
            try {
                sheet = workbook.getSheetAt(i);
                rowCount = sheet.getLastRowNum() + 1;
                if (rowCount < 1) {
                    break;
                }

                //                row = sheet.getRow(0);
                //                for (int j = 0; j < rowCount; j++) {
                //                    row = sheet.getRow(j);
                //                    if (firstIndex < row.getFirstCellNum()) {
                //                        firstIndex = row.getFirstCellNum();
                //                    }
                //                    if (lastIndex > row.getLastCellNum()) {
                //                        lastIndex = row.getLastCellNum();
                //                    }
                //                }
                row = sheet.getRow(0); // Head row
                firstIndex = row.getFirstCellNum();
                lastIndex = row.getLastCellNum();
                headName = new String[lastIndex];
                fieldTypes = new fieldType[lastIndex];
                List<String> names = new ArrayList();

                for (int index = firstIndex; index < lastIndex; index++) {
                    String name = row.getCell(index).toString();
                    if (names.contains(name)) {
                        JOptionPane.showMessageDialog(null, String.format("Field \"%s\" duplicated!", name),
                                "Notice", JOptionPane.ERROR_MESSAGE);
                        return null;
                    } else {
                        names.add(name);
                    }
                    headName[index] = name;
                    fieldTypes[index] = fieldType.Double;
                }

                // Detect field types
                for (int k = 1; k < rowCount; k++) {
                    row = sheet.getRow(k);

                    if (row == null) {
                        break;
                    }

                    for (int index = firstIndex; index < lastIndex; index++) {
                        if (fieldTypes[index] != fieldType.String) {
                            if (row.getCell(index) != null) {
                                fieldTypes[index] = fieldType
                                        .getType(getCellType(row.getCell(index).getCellType()));
                            } else {
                                fieldTypes[index] = fieldType.String;
                            }
                        }
                    }
                }

                DataTable tempTable = new DataTable(sheetName);

                for (int index = firstIndex; index < lastIndex; index++) {
                    tempTable.addField(headName[index], fieldTypes[index]);
                }

                for (int k = 1; k < rowCount; k++) {
                    row = sheet.getRow(k);

                    if (row == null) {
                        break;
                    }
                    tempTable.addRecord();

                    for (int index = firstIndex; index < lastIndex; index++) {
                        cell = row.getCell(index);
                        if (fieldTypes[index] == fieldType.Double) {
                            try {
                                cellValue = cell.getNumericCellValue();
                            } catch (Exception e) {
                                System.err.println(String.format("Error reading Sheet: %s, Row: %d, Column: %d",
                                        cell.getSheet().getSheetName(), cell.getRowIndex(),
                                        cell.getColumnIndex()));
                                cellValue = cell.getStringCellValue().trim();
                            }
                        } else if (fieldTypes[index] == fieldType.Integer) {
                            try {
                                cellValue = (int) cell.getNumericCellValue();
                            } catch (Exception e) {
                                System.err.println(String.format("Error reading Sheet: %s, Row: %d, Column: %d",
                                        cell.getSheet().getSheetName(), cell.getRowIndex(),
                                        cell.getColumnIndex()));
                                cellValue = cell.getStringCellValue().trim();
                            }
                        } else {
                            if (cell == null) {
                                cellValue = "";
                            } else {
                                try {
                                    try {
                                        cellValue = cell.getNumericCellValue();
                                    } catch (Exception e) {
                                        cellValue = cell.getStringCellValue().trim();
                                    }
                                } catch (Exception e) {
                                    System.err.println(
                                            String.format("Error reading Sheet: %s, Row: %d, Column: %d",
                                                    cell.getSheet().getSheetName(), cell.getRowIndex(),
                                                    cell.getColumnIndex()));
                                    cellValue = cell.getNumericCellValue();
                                }
                            }
                        }
                        tempTable.getField(index).set(tempTable.getRecordCount() - 1, cellValue);
                    }
                }

                if (hasIntColumns) {
                    DataTable table = new DataTable(tempTable.getName());
                    List<Integer> updateFields = new ArrayList();
                    if (colsHasInt < 1) { // 0 or negative means check all columns
                        colsHasInt = tempTable.getRecordCount();
                    }
                    int cols4Check = Math.min(colsHasInt, tempTable.getRecordCount());

                    for (int j = 0; j < cols4Check; j++) {
                        Field f = tempTable.getField(j);
                        if (f.getType() != fieldType.Double) {
                            continue;
                        }
                        boolean isIntColumn = true;
                        for (int recNum = 0; recNum < tempTable.getRecordCount(); recNum++) {
                            double value = Double.valueOf(f.get(recNum).toString());
                            double checkValue = Double.valueOf(String.valueOf((int) value));
                            if (value != checkValue) {
                                isIntColumn = false;
                                break;
                            }
                        }

                        if (isIntColumn) {
                            updateFields.add(j);
                        }
                    }

                    for (int j = 0; j < tempTable.getFieldCount(); j++) {
                        fieldType type = tempTable.getField(j).getType();
                        if (updateFields.contains(j)) {
                            type = fieldType.Integer;
                        }
                        table.addField(tempTable.getField(j).getName(), type);
                    }

                    for (int recNum = 0; recNum < tempTable.getRecordCount(); recNum++) {
                        table.addRecord();
                        for (int col = 0; col < tempTable.getFieldCount(); col++) {
                            Object rowItem;

                            if (updateFields.contains(col)) {
                                Double value = (double) tempTable.getRecord(recNum).get(col);
                                rowItem = value.intValue();
                            } else {
                                rowItem = tempTable.getRecord(recNum).get(col);
                            }
                            table.getField(col).set(table.getRecordCount() - 1, rowItem);
                        }
                    }
                    out.add(table);
                } else {
                    out.add(tempTable);
                }
            } catch (Exception e) {
                Logger.getLogger(Excel2Dataset.class.getName()).log(Level.SEVERE, null, e);
                JOptionPane.showMessageDialog(null, String.format("Loading sheet %s error!", sheetName),
                        "Notice", JOptionPane.ERROR_MESSAGE);
            }
        }
    } catch (Exception ex) {
        Logger.getLogger(Excel2Dataset.class.getName()).log(Level.SEVERE, null, ex);
    }
    return out;
}

From source file:step.datapool.excel.ExcelDataPoolImpl.java

License:Open Source License

private int addHeader(Sheet sheet, String header) {
    if (configuration.getHeaders().get()) {
        Row row = sheet.getRow(0);
        Cell cell = row.createCell(Math.max(0, row.getLastCellNum()));
        cell.setCellValue(header);//  ww  w. ja va 2s  .co m
        updated = true;
        return cell.getColumnIndex();
    } else {
        throw new RuntimeException("Unable to create header for excel configured not to use headers.");
    }
}

From source file:temp.ExcelReader.java

public static void readFromXLSXFile(File file) {
    try {/*ww  w  .  j a  v a 2s  .c  o  m*/
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));

        int sheetCount = workbook.getNumberOfSheets();

        System.out.println(sheetCount);

        XSSFSheet sheet = workbook.getSheetAt(0);

        Iterator<Row> rowItertor = sheet.iterator();

        while (rowItertor.hasNext()) {
            Row row = rowItertor.next();

            for (int i = 0; i < row.getLastCellNum(); i++) {
                System.out.print(row.getCell(i) + "|"); //this you won't miss any cells! right way to do
            }

            System.out.println("");

        }

    } catch (IOException ex) {
        Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, ex);
    }
}