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:com.krawler.esp.servlets.XLSDataExtractor.java

License:Open Source License

public JSONObject parseXLS(String filename, int sheetNo)
        throws FileNotFoundException, IOException, JSONException {
    JSONObject jobj = new JSONObject();
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet = wb.getSheetAt(sheetNo);

    int startRow = 0;
    int maxRow = sheet.getLastRowNum();
    int maxCol = 0;
    int noOfRowsDisplayforSample = 20;
    if (noOfRowsDisplayforSample > sheet.getLastRowNum()) {
        noOfRowsDisplayforSample = sheet.getLastRowNum();
    }//from  w w w  . j a  va2  s. co  m

    JSONArray jArr = new JSONArray();
    for (int i = 0; i <= noOfRowsDisplayforSample; i++) {
        Row row = sheet.getRow(i);
        JSONObject obj = new JSONObject();
        JSONObject jtemp1 = new JSONObject();
        if (row == null) {
            jArr.put(obj);
            continue;
        }
        if (maxCol < row.getLastCellNum())
            maxCol = row.getLastCellNum();
        for (int j = 0; j < row.getLastCellNum(); j++) {
            Cell cell = row.getCell(j);
            if (cell == null)
                continue;
            String colHeader = new CellReference(i, j).getCellRefParts()[2];
            String val = null;
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                val = Double.toString(cell.getNumericCellValue());
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    java.util.Date df = HSSFDateUtil.getJavaDate(Double.parseDouble(val));
                    String df_full = "yyyy-MM-dd";
                    DateFormat sdf = new SimpleDateFormat(df_full);
                    val = sdf.format(df);
                }
                break;
            case Cell.CELL_TYPE_STRING:
                val = cell.getRichStringCellValue().getString();
                break;
            }
            if (i == 0) { // List of Headers (Consider first row as Headers)
                jtemp1 = new JSONObject();
                jtemp1.put("header", val);
                jtemp1.put("index", j);
                jobj.append("Header", jtemp1);
            }
            obj.put(colHeader, val);
        }
        jArr.put(obj);
    }
    jobj.put("startrow", startRow);
    jobj.put("maxrow", maxRow);
    jobj.put("maxcol", maxCol);
    jobj.put("index", sheetNo);
    jobj.put("data", jArr);
    jobj.put("filename", filename);

    jobj.put("msg", "Image has been successfully uploaded");
    jobj.put("lsuccess", true);
    jobj.put("valid", true);
    return jobj;
}

From source file:com.krawler.esp.servlets.XLSDataExtractor.java

License:Open Source License

public JSONObject parseXLS1(String filename, int sheetNo)
        throws FileNotFoundException, IOException, JSONException {
    JSONObject jobj = new JSONObject();
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet = wb.getSheetAt(sheetNo);
    ArrayList<String> arr = new ArrayList<String>();
    int startRow = 0;
    int maxRow = sheet.getLastRowNum();
    int maxCol = 0;

    JSONArray jArr = new JSONArray();
    try {//from  ww  w . j a  v  a  2s.  co  m
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            JSONObject jtemp1 = new JSONObject();
            if (row == null) {
                jArr.put(obj);
                continue;
            }
            if (maxCol < row.getLastCellNum())
                maxCol = row.getLastCellNum();
            for (int j = 0; j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                String val = null;
                if (cell == null) {
                    arr.add(val);
                    continue;
                }
                ;
                String colHeader = new CellReference(i, j).getCellRefParts()[2];
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    val = Double.toString(cell.getNumericCellValue());
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        java.util.Date df = HSSFDateUtil.getJavaDate(Double.parseDouble(val));
                        String df_full = "yyyy-MM-dd";
                        DateFormat sdf = new SimpleDateFormat(df_full);
                        val = sdf.format(df);
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    val = cell.getRichStringCellValue().getString();
                    break;
                }
                if (i == 0) { // List of Headers (Consider first row as Headers)
                    jtemp1 = new JSONObject();
                    jtemp1.put("header", val);
                    jtemp1.put("index", j);
                    jobj.append("Header", jtemp1);
                    obj.put(colHeader, val);
                    arr.add(val);
                } else {
                    if (arr.get(j) != null)
                        obj.put(arr.get(j), val);
                }

            }
            jArr.put(obj);
        }
    } catch (Exception ex) {
        Logger.getLogger(XLSDataExtractor.class.getName()).log(Level.SEVERE, null, ex);
    }
    jobj.put("startrow", startRow);
    jobj.put("maxrow", maxRow);
    jobj.put("maxcol", maxCol);
    jobj.put("index", sheetNo);
    jobj.put("data", jArr);
    jobj.put("filename", filename);

    jobj.put("msg", "Image has been successfully uploaded");
    jobj.put("lsuccess", true);
    jobj.put("valid", true);
    return jobj;
}

From source file:com.kybelksties.excel.ExcelSheetTableModel.java

License:Open Source License

private void findNumberOfColumns() {
    numberOfColumns = 0;//from w  w  w .  ja va 2 s. co  m
    if (workbook != null) {
        Iterator<Row> iter = getSheet().rowIterator();
        while (iter.hasNext()) {
            Row row = iter.next();
            // According to documentation, this method gets the index of
            // the last cell. This value is increased BY ONE, so for
            // example, with maximum index of 5 will be 6. I think this has
            // been done to simplify iteration over rowcell.
            int lastCol = row.getLastCellNum();
            if (numberOfColumns < lastCol) {
                numberOfColumns = lastCol;
            }
        }
    }
}

From source file:com.kybelksties.excel.ExcelSheetTableModel.java

License:Open Source License

/**
 * Insert a row at a given index./*from  w w w.jav a2s. c  om*/
 *
 * @param createAtIndex row-number of the cell at which to create a new row
 * @param sourceRow     the row to insert
 */
public void insertRowAt(int createAtIndex, Row sourceRow) {
    Row newRow = getRow(createAtIndex);
    if (newRow != null) {
        // shift all rows >= createAtIndex up by one
        getSheet().shiftRows(createAtIndex, getSheet().getLastRowNum(), 1);
    } else {
        newRow = getSheet().createRow(createAtIndex);
    }

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell = newRow.createCell(i);

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            continue;
        }

        // Copy style from old cell and apply to new cell
        CellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        newCell.setCellStyle(newCellStyle);

        // If there is a cell comment, copy
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        // Set the cell data value
        switch (oldCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(oldCell.getCellFormula());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        }
    }

    // If there are are any merged regions in the source row, copy to new row
    for (int i = 0; i < getSheet().getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = getSheet().getMergedRegion(i);
        if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                    (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                    cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
            getSheet().addMergedRegion(newCellRangeAddress);
        }
    }
}

From source file:com.lnganalysis.fileupload.util.ReadExcelFile.java

private String getHeaderValues(Sheet sheet, int columnNo) {
    logger.info("Class - ReadExcelFile - getHeaderValues()");
    Row headerRow = sheet.getRow(0);
    int cellCount = headerRow.getLastCellNum();
    String[] headers = new String[cellCount];

    for (int i = 0; i < cellCount; i++)
        headers[i] = headerRow.getCell(i).toString();

    String columName = headers[columnNo];
    return columName;
}

From source file:com.lulu.ofarm.test.ImportControllerTest.java

@Test
public void getBeanFromExcel() throws FileNotFoundException, IOException {
    //1.Excel  //from   ww  w .ja  v a  2s  .c o m
    //      POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:/FTP/test.xls"));  

    //2.Excel  
    Workbook wb = new HSSFWorkbook(new FileInputStream("F:/reposibility_new.xls"));
    //3.Excel  
    Sheet sheet = wb.getSheetAt(0);
    //  
    int trLength = sheet.getLastRowNum();
    //4.Excel  
    Row row = sheet.getRow(0);
    //  
    int tdLength = row.getLastCellNum();
    //5.Excel?  
    Cell cell = row.getCell((short) 1);
    //6.??  
    //CellStyle cellStyle = cell.getCellStyle();  
    for (int i = 2; i < trLength; i++) {
        //Excel  
        Row row1 = sheet.getRow(i);

        for (int j = 0; j < tdLength; j++) {
            Cell cell1 = row1.getCell(j);
            /** 
             * ?ExcelCannot get a text value from a numeric cell 
             * ?String? 
             */
            if (cell1 != null) {
                cell1.setCellType(Cell.CELL_TYPE_STRING);
            }
            System.out.println(cell1.getStringCellValue());
        }

        //Excel?  

        //?  
        //      OutfallPolluateResourceBean resource = new OutfallPolluateResourceBean();
        //      resource.setRivername(row1.getCell(1).getStringCellValue());
        //      resource.setArea(row1.getCell(2).getStringCellValue());
        //      resource.setLeftorrightbank(row1.getCell(3).getStringCellValue());
        //      resource.setOutfalltype(row1.getCell(4).getStringCellValue());
        //      resource.setOutfallcode(row1.getCell(5).getStringCellValue());
        //      resource.setSecondaryunit(row1.getCell(6).getStringCellValue());
        //      resource.setStreetname(row1.getCell(7).getStringCellValue());
        //      resource.setStreetmanager(row1.getCell(8).getStringCellValue());
        //      resource.setVillage(row1.getCell(9).getStringCellValue());
        //      resource.setVillagemanager(row1.getCell(10).getStringCellValue());
        //      resource.setPosition(row1.getCell(11).getStringCellValue());
        //      resource.setCoordinate(row1.getCell(12).getStringCellValue());
        //      resource.setOutfallsize(row1.getCell(13).getStringCellValue());
        //      resource.setOutfallshape(row1.getCell(14).getStringCellValue());
        //      resource.setPolldescription(row1.getCell(15).getStringCellValue());
        //      
        //      resource.setRectificationmeasures(row1.getCell(16).getStringCellValue());
        //      resource.setDrainageTo(row1.getCell(17).getStringCellValue());
        //      resource.setTherectificationresponsibilityunit(row1.getCell(18).getStringCellValue());
        //      resource.setTimeofcompletion(row1.getCell(19).getStringCellValue());
        //      resource.setRemark(row1.getCell(20).getStringCellValue());
        //      System.err.println(resource);
        //      service.save(resource);

        //       PollutantSourceBean source = new PollutantSourceBean();
        //      source.setRivername(row1.getCell(1).getStringCellValue());
        //      source.setArea(row1.getCell(2).getStringCellValue());
        //      source.setPollsourcename(row1.getCell(3).getStringCellValue());
        //      source.setStreetname(row1.getCell(4).getStringCellValue());
        //      source.setStreetmanager(row1.getCell(5).getStringCellValue());
        //      source.setVillage(row1.getCell(6).getStringCellValue());
        //      source.setVillagemanager(row1.getCell(7).getStringCellValue());
        //      source.setPollsourcetype(row1.getCell(8).getStringCellValue());
        //      source.setOutfalltype(row1.getCell(9).getStringCellValue());
        //      source.setOutfallcode(row1.getCell(10).getStringCellValue());
        //      source.setPosition(row1.getCell(11).getStringCellValue());
        //      source.setCoordinate(row1.getCell(12).getStringCellValue());
        //      source.setPolldescription(row1.getCell(13).getStringCellValue());
        //      source.setDrainageto(row1.getCell(14).getStringCellValue());
        //      source.setPolldischarginglicense(row1.getCell(15).getStringCellValue());
        //      source.setDrainaglicense(row1.getCell(16).getStringCellValue());
        //      source.setHasmeasures(row1.getCell(17).getStringCellValue());
        //      source.setRectificationmeasures(row1.getCell(18).getStringCellValue());
        //      source.setTherectificationresponsibilityunit(row1.getCell(19).getStringCellValue());
        //      source.setTimeofcompletion(row1.getCell(20).getStringCellValue());
        //      source.setRemark(row1.getCell(21).getStringCellValue());
        //      System.out.println(source);

        //      service.save(source);

    }
}

From source file:com.mimp.controllers.reporte.java

private static void copyRowStyle(Sheet worksheet, int sourceRowNum, int destinationRowNum) {
        // Coge la fila antigua y nueva
        Row newRow = worksheet.getRow(destinationRowNum);
        Row sourceRow = worksheet.getRow(sourceRowNum);

        //Si existe una fila en el detino, pasa todas las filas 1 ms abajo antes de crear la nueva columna
        if (newRow != null) {
            worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
        } else {/*from w  w w. j av  a2 s.c  o m*/
            newRow = worksheet.createRow(destinationRowNum);
        }

        // Hace un loop entre las celdas de cada columna para aadir una por una a la nueva
        for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
            // Copia la antigua y nueva celda
            Cell oldCell = sourceRow.getCell(i);
            Cell newCell = newRow.createCell(i);

            // Si la anterior celda es null, evalua la siguiente celda defrente
            if (oldCell == null) {
                newCell = null;
                continue;
            }

            // Usa el estilo de la celda antigua
            newCell.setCellStyle(oldCell.getCellStyle());

            // Establece el tipo de valor de la celda
            newCell.setCellType(oldCell.getCellType());

            // Establece el valor de la celda
            //            switch (oldCell.getCellType()) {
            //                case Cell.CELL_TYPE_BLANK:
            //                    break;
            //                case Cell.CELL_TYPE_BOOLEAN:
            //                    newCell.setCellValue(oldCell.getBooleanCellValue());
            //                    break;
            //                case Cell.CELL_TYPE_ERROR:
            //                    newCell.setCellErrorValue(oldCell.getErrorCellValue());
            //                    break;
            //                case Cell.CELL_TYPE_FORMULA:
            //                    newCell.setCellFormula(oldCell.getCellFormula());
            //                    break;
            //                case Cell.CELL_TYPE_NUMERIC:
            //                    newCell.setCellValue(oldCell.getNumericCellValue());
            //                    break;
            //                case Cell.CELL_TYPE_STRING:
            //                    newCell.setCellValue(oldCell.getRichStringCellValue());
            //                    break;
            //            }
        }
    }

From source file:com.mimp.controllers.reporte.java

private static void copyRow(Sheet worksheet, int sourceRowNum, int destinationRowNum) {
        // Coge la fila antigua y nueva
        Row newRow = worksheet.getRow(destinationRowNum);
        Row sourceRow = worksheet.getRow(sourceRowNum);

        //Si existe una fila en el detino, pasa todas las filas 1 ms abajo antes de crear la nueva columna
        if (newRow != null) {
            worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
        } else {//from  w  ww. ja  v  a2  s  .  co  m
            newRow = worksheet.createRow(destinationRowNum);
        }

        // Hace un loop entre las celdas de cada columna para aadir una por una a la nueva
        for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
            // Copia la antigua y nueva celda
            Cell oldCell = sourceRow.getCell(i);
            Cell newCell = newRow.createCell(i);

            // Si la anterior celda es null, evalua la siguiente celda defrente
            if (oldCell == null) {
                newCell = null;
                continue;
            }

            // Usa el estilo de la celda antigua
            newCell.setCellStyle(oldCell.getCellStyle());

            // Establece el tipo de valor de la celda
            newCell.setCellType(oldCell.getCellType());

            // Establece el valor de la celda
            switch (oldCell.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                newCell.setCellErrorValue(oldCell.getErrorCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                newCell.setCellFormula(oldCell.getCellFormula());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                newCell.setCellValue(oldCell.getRichStringCellValue());
                break;
            }
        }
    }

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

public int getLastColumn(Sheet sheet) {
    int lastRow = sheet.getLastRowNum();
    int lastColumn = 1;
    for (int i = 0; i < lastRow; ++i) {
        Row row = sheet.getRow(i);
        if (row != null) {
            int col = row.getLastCellNum();
            if (col > lastColumn) {
                lastColumn = col;/*from  w  w w. jav a  2s  .com*/
            }
        }
    }
    return lastColumn - 1;
}

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

public void clearRange(int sheetIndex, int[] coords) {
    Sheet sheet = getSheet(sheetIndex);//  w ww. j  a  va  2s  .co  m
    for (int i = coords[0]; i <= coords[2]; i++) {
        Row row = sheet.getRow(i);
        if (row == null)
            continue;
        for (int j = coords[1]; j <= coords[3]; j++) {
            Cell cell = row.getCell(j);
            if (cell != null)
                row.removeCell(cell);
        }
        if (row.getLastCellNum() < 0)
            sheet.removeRow(row);
    }
}