bc2

package upload_download_Excel_DB;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ProcessData
{
static String WI_NAME = "1234";
static Connection con= null;

public static void main(String[] args)
{
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr");
Statement st = con.createStatement();

con.setAutoCommit(false);
PreparedStatement pstm = null;
FileInputStream fis = new FileInputStream("E:\\Root\\Newgen\\ShareCapital.xlsm");

XSSFWorkbook wb = new XSSFWorkbook(fis);

System.out.println("1");
XSSFSheet sheet1 = wb.getSheetAt(0);
int rowcount=sheet1.getLastRowNum();
System.out.println("Rowcount>>"+rowcount);
XSSFRow row ;
System.out.println(">>>>"+sheet1.getLastRowNum());
Cell cell= null;
DataFormatter formatter = new DataFormatter();
for(int i =6; i<=rowcount+1; i++)  //FOR SHEET 1 || SHARE CAPITAL
{
row = sheet1.getRow(i);
if(row!= null)
{
String sno = formatter.formatCellValue(row.getCell(0));
System.out.println(sno);

String class_name = formatter.formatCellValue(row.getCell(1));
//int result = Integer.parseInt(class_name);
System.out.println(class_name);
}
 
 
 



System.out.println("########Before insert_excel_sharedcapital()");

//insert_(WI_NAME,S_No,Share_Class_Number,Share_Class_Code,Currency_Code,Issued_Value,Number_issued,Voting_Rights,Share_Type_Description);
}


con.close();
/*
* XSSFFactory fs = new POIFSFileSystem( input ); XSSFWorkbook wb = new
* XSSFWorkbook(fs); XSSFSheet sheet = wb.getSheetAt(0); Row row;
*/
} catch (ClassNotFoundException e) {
System.out.println(e);
} catch (SQLException ex) {
System.out.println(ex);
} catch (IOException ioe) {
System.out.println(ioe);
}

}
private static void insert_excel_sharedcapital(String WI_NAME,String s_No,
String share_Class_Number, int share_Class_Code,
String currency_Code, String issued_Value, int number_issued,
int voting_Rights, String share_Type_Description)
    {
    System.out.println("Before Inserting into NG_EXP_AR_SCS_CO");

String tableName = "NG_EXP_AR_SCS_CO";
String colNames = "WINAME,s_no,share_class_number,share_class_code,currency_code,issued_value,number_issued,voting_rights,share_type_description";

String colValues = "'"+WI_NAME+",'"+s_No+"','"+share_Class_Number+"',"+share_Class_Code+",'"+currency_Code+"','"+issued_Value+"',"+number_issued+",'"+voting_Rights+"','"+share_Type_Description+",";

//String insertXML = XMLGen.APInsert(objWF.cabinetName, objWF.sessionID, tableName, colValues); //-----------------------------doubt----------------4 PARAMETERS




    }


}

BC

https://stackoverflow.com/questions/2645566/finding-the-last-row-in-an-excel-spreadsheet

The only way to know for sure is to test the rows. Here's the solution I'm using for the same problem:

int lastRowIndex = -1;
if( sheet.getPhysicalNumberOfRows() > 0 )
{
    // getLastRowNum() actually returns an index, not a row number
    lastRowIndex = sheet.getLastRowNum();

    // now, start at end of spreadsheet and work our way backwards until we find a row having data
    for( ; lastRowIndex >= 0; lastRowIndex-- ){
        Row row = sheet.getRow( lastRowIndex );
        if( row != null ){
            break;
        }
    }
}

Note: this doesn't check for rows that appear to be empty but aren't, such as cells that have an empty string in them. For that, you need a more complete solution like:

private int determineRowCount()
{
    this.evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    this.formatter = new DataFormatter( true );

    int lastRowIndex = -1;
    if( sheet.getPhysicalNumberOfRows() > 0 )
    {
        // getLastRowNum() actually returns an index, not a row number
        lastRowIndex = sheet.getLastRowNum();

        // now, start at end of spreadsheet and work our way backwards until we find a row having data
        for( ; lastRowIndex >= 0; lastRowIndex-- )
        {
            Row row = sheet.getRow( lastRowIndex );
            if( !isRowEmpty( row ) )
            {
                break;
            }
        }
    }
    return lastRowIndex;
}

/**
 * Determine whether a row is effectively completely empty - i.e. all cells either contain an empty string or nothing.
 */
private boolean isRowEmpty( Row row )
{
    if( row == null ){
        return true;
    }

    int cellCount = row.getLastCellNum() + 1;
    for( int i = 0; i < cellCount; i++ ){
        String cellValue = getCellValue( row, i );
        if( cellValue != null && cellValue.length() > 0 ){
            return false;
        }
    }
    return true;
}

/**
 * Get the effective value of a cell, formatted according to the formatting of the cell.
 * If the cell contains a formula, it is evaluated first, then the result is formatted.
 * 
 * @param row the row
 * @param columnIndex the cell's column index
 * @return the cell's value
 */
private String getCellValue( Row row, int columnIndex )
{
    String cellValue;
    Cell cell = row.getCell( columnIndex );
    if( cell == null ){
        // no data in this cell
        cellValue = null;
    }
    else{
        if( cell.getCellType() != Cell.CELL_TYPE_FORMULA ){
            // cell has a value, so format it into a string
            cellValue = this.formatter.formatCellValue( cell );
        }
        else {
            // cell has a formula, so evaluate it
            cellValue = this.formatter.formatCellValue( cell, this.evaluator );
        }
    }
    return cellValue;
}