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;
}

Cannot get a STRING value from a NUMERIC cell. How can I resolve this issue in selenium webdriver using POI?

Exception in thread "main" java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell
at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:1003)
at org.apache.poi.xssf.usermodel.XSSFCell.getRichStringCellValue(XSSFCell.java:389)
at org.apache.poi.xssf.usermodel.XSSFCell.getStringCellValue(XSSFCell.java:341)
at upload_download_Excel_DB.ProcessData.main(ProcessData.java:42)

==========================================================

This exception is occurred when code try to read Numeric value as string. Apache POI will not convert from Numeric to String.

---

There are two solution :

1. First convert cell type to string and Read it. Please check below code :

import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.util.Locale;

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

    public class ReadExcel {

    public static void main(String[] args) throws Exception {
        File src=new File("C:\\Users\\Sagar\\Desktop\\TestInputData.xlsx");

        FileInputStream fis=new FileInputStream(src);

        XSSFWorkbook wb=new XSSFWorkbook(fis);

        XSSFSheet sheet1=wb.getSheetAt(0);
        int rowcount=sheet1.getLastRowNum();
        System.out.println("Total Row" + rowcount);

        for(int i=0;i<rowcount+1;i++) {

            System.out.println(i);
            //GET CELL
            Cell cell1 = sheet1.getRow(i).getCell(0);   
            //SET AS STRING TYPE
            cell1.setCellType(Cell.CELL_TYPE_STRING);
            String data0= cell1.getStringCellValue();
            System.out.println("Test Data From Excel : "+data0);
        }

        wb.close();
      }
    }

2. Get type first and print by its option. Please see the code:
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.util.Locale;

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

    public class ReadExcel {

    public static void main(String[] args) throws Exception {
        File src=new File("C:\\Users\\Sagar\\Desktop\\TestInputData.xlsx");

        FileInputStream fis=new FileInputStream(src);

        XSSFWorkbook wb=new XSSFWorkbook(fis);

        XSSFSheet sheet1=wb.getSheetAt(0);
        int rowcount=sheet1.getLastRowNum();
        System.out.println("Total Row " + rowcount);

        for(int i=0;i<rowcount+1;i++) {


            Cell cell1 = sheet1.getRow(i).getCell(0);   

            switch (cell1.getCellType()) {

            case Cell.CELL_TYPE_STRING:
                System.out.println(cell1.getRichStringCellValue().getString());
                break;

            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell1)) {
                    System.out.println(cell1.getDateCellValue());
                } else {
                    System.out.println(cell1.getNumericCellValue());
                }
                break;

            case Cell.CELL_TYPE_BOOLEAN:
                System.out.println(cell1.getBooleanCellValue());
                break;

            case Cell.CELL_TYPE_FORMULA:
                System.out.println(cell1.getCellFormula());
                break;

            default:
                System.out.println();
        }

            //String data0= cell1.getStringCellValue();
            //System.out.println("Test Data From Excel : "+data0);
        }

        wb.close();


    }

    }

Create & Read XML File

===========================Read_XML =============================
package XML_Parsing;

import java.io.File;

import javax.xml.parsers.DocumentBuilder;

import javax.xml.parsers.DocumentBuilderFactory;

import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Document;

import org.w3c.dom.Element;

import org.w3c.dom.Node;

import org.w3c.dom.NodeList;

public class Read_XML {

public static void main(String[] args) throws ParserConfigurationException 

{

  File xmlfile = new File("D:\\work\\Java Work Space\\files\\dummy.xml");

  

  DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance();

  DocumentBuilder documentBuilder = documentBuilderFactory.newDocumentBuilder();

  Document document = documentBuilder.newDocument();

  

  document.getDocumentElement().normalize();

  

  NodeList list = document.getElementsByTagName("Developer");

  for(int i =0;i<list.getLength();i++)

  {

  Node node = list.item(i);

  

  if(node.getNodeType() == Node.ELEMENT_NODE)

  {

  Element element = (Element) node;

  

  System.out.println("ID: "+element.getAttribute("id"));

  System.out.println("Name: "+element.getElementsByTagName("Name").item(0).getTextContent());

  System.out.println("SurName: "+element.getElementsByTagName("Surname").item(0).getTextContent());

  }

  }

}

}

===========================Create_XML ===============================

package XML_Parsing;

import java.io.File;

import javax.xml.parsers.DocumentBuilder;

import javax.xml.parsers.DocumentBuilderFactory;

import javax.xml.parsers.ParserConfigurationException;

import javax.xml.transform.Transformer;

import javax.xml.transform.TransformerConfigurationException;

import javax.xml.transform.TransformerException;

import javax.xml.transform.TransformerFactory;

import javax.xml.transform.dom.DOMSource;

import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.Attr;

import org.w3c.dom.Document;

import org.w3c.dom.Element;

public class Create_XML {

public static void main(String[] args) throws ParserConfigurationException, TransformerException 

{

  DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance();

  DocumentBuilder documentBuilder = documentBuilderFactory.newDocumentBuilder();

  

  Document document = documentBuilder.newDocument();

  Element element = document.createElement("Developer");

  

  Attr attr = document.createAttribute("Id");

  attr.setValue("1");

  element.setAttributeNode(attr);

  

  Element name = document.createElement("Name");

  name.appendChild(document.createTextNode("Amit"));

  element.appendChild(name);

  

  Element surname = document.createElement("surname");

  surname.appendChild(document.createTextNode("Manjhi"));

  element.appendChild(surname);

  

  Element hobby = document.createElement("hobby");

  hobby.appendChild(document.createTextNode("Coding"));

  element.appendChild(hobby);

  

  Element color = document.createElement("color");

  color.appendChild(document.createTextNode("yellow"));

  element.appendChild(color);

  

  TransformerFactory transformerfactory = TransformerFactory.newInstance();

  Transformer transformer = transformerfactory.newTransformer();

  DOMSource  source = new DOMSource(document);

  

  StreamResult streamresult = new StreamResult(new File("D:\\work\\Java Work Space\\files\\demo2.xml"));

  transformer.transform(source, streamresult);

  System.out.println("DONE.....................");

}

}

===========================ReadXMLFile ============================

package XML_Parsing;

import javax.xml.parsers.DocumentBuilderFactory;

import javax.xml.parsers.DocumentBuilder;

import org.w3c.dom.Document;

import org.w3c.dom.NodeList;

import org.w3c.dom.Node;

import org.w3c.dom.Element;

import java.io.File;

public class ReadXMLFile {

  public static void main(String argv[]) {

    try {

File fXmlFile = new File("D:\\work\\Java Work Space\\files\\demo1.xml");

DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();

DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();

Document doc = dBuilder.parse(fXmlFile);

doc.getDocumentElement().normalize();

System.out.println("Root element :" + doc.getDocumentElement().getNodeName());

NodeList nList = doc.getElementsByTagName("staff");

System.out.println("----------------------------");

for (int temp = 0; temp < nList.getLength(); temp++) {

Node nNode = nList.item(temp);

System.out.println("\nCurrent Element :" + nNode.getNodeName());

if (nNode.getNodeType() == Node.ELEMENT_NODE) {

Element eElement = (Element) nNode;

System.out.println("Staff id : " + eElement.getAttribute("id"));

System.out.println("First Name : " + eElement.getElementsByTagName("firstname").item(0).getTextContent());

System.out.println("Last Name : " + eElement.getElementsByTagName("lastname").item(0).getTextContent());

System.out.println("Nick Name : " + eElement.getElementsByTagName("nickname").item(0).getTextContent());

System.out.println("Salary : " + eElement.getElementsByTagName("salary").item(0).getTextContent());

}

}

    } catch (Exception e) {

e.printStackTrace();

    }

  }

}

Select Query

package jdbc;

import java.sql.*;

public class SelectQuery
{
public static void main(String[] args) throws ClassNotFoundException, SQLException
{
String driverClassName="oracle.jdbc.driver.OracleDriver";
String url="jdbc:oracle:thin:@localhost:1521:orcl";
String user="hr";
String password="hr";
String query ="select * from COUNTRIES";
int count=0;
Class.forName(driverClassName);
Connection con = DriverManager.getConnection(url, user, password);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(query);
while(rs.next())
{
System.out.println(rs.getString(1)+"..."+rs.getString(2)+"..."+rs.getInt(3));
count++;
}
System.out.println("No. of Rows = "+count);
con.close();
}

}

Procedure Call

package jdbc;
import java.sql.*;
public class ProcedureCall_Salary {

public static void main(String[] args) throws SQLException , ClassNotFoundException
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","hr","hr");
CallableStatement cst =con.prepareCall("{call proc_getsal(?,?)}");
cst.setInt(1,101);                 
cst.registerOutParameter(2,Types.INTEGER);
cst.execute();
//System.out.println("Salary is :"+cst.getInt(2));
con.close();

}

}

Procedure_Call

package jdbc;
import java.sql.*;
public class Proc_Call
{

public static void main(String[] args) throws ClassNotFoundException, SQLException
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con= DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr");
CallableStatement cst = con.prepareCall("{call proc_sum(?,?,?)}");
cst.setInt(1,100);
cst.setInt(2,500);
cst.registerOutParameter(3,Types.INTEGER);
cst.execute();
System.out.println("Sum Is: "+cst.getInt(3));
con.close();

}

}

Query_Formatting

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class Insert_QueryFormatting {

public static void main(String[] args) throws ClassNotFoundException ,SQLException
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","hr","hr");
Statement st = con.createStatement();
Scanner sc = new Scanner(System.in);
System.out.println("Enter  FBI Code:");
int code = sc.nextInt();
System.out.println("Enter FBI Name:");
String name = sc.next();
System.out.println("Enter FBI Latitude:");
int lati = sc.nextInt();
System.out.println("Enter FBI Longitude:");
int longi = sc.nextInt();
String query = String.format("Insert into JDBC_SAMPLE_FBI values(%d,'%s',%d,%d)",code,name,lati,longi);
System.out.println(query);
int count = st.executeUpdate(query);
if(count>0)
{System.out.println("Data Inserted in JDBC_SAMPLE_FBI table");}
else {System.out.println("Something went wrong, pleaase contact Customer Services");}

}

}