How to read data from Excel files (.xls or .xlsx) using JAVA?

Question: How to read data from Excel files (.xls or .xlsx) using JAVA?

Code: 
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelHandling
{

public static void main(String[] args) throws IOException
{
String filePath="input_Excel//testData.xlsx";
loadFile(filePath);
}

public static void loadFile(String filePath) throws IOException
{
File file=new File(filePath);                                                     // Creating File Object
String fileExtension=FilenameUtils.getExtension(filePath);  // Getting extension of file
int totalRows=0;
Iterator<Row> rowIterator=null;

if(fileExtension.equalsIgnoreCase("xlsx"))
{
FileInputStream fis=new FileInputStream(file);
XSSFWorkbook doc =new XSSFWorkbook(fis);
XSSFSheet sheet=doc.getSheet("Sheet1");
        totalRows=sheet.getLastRowNum();
        rowIterator=sheet.rowIterator();
}
else if(fileExtension.equalsIgnoreCase("xls"))
{
FileInputStream fis =new FileInputStream(file);
HSSFWorkbook doc=new HSSFWorkbook(fis);
HSSFSheet sheet=doc.getSheet("Sheet1");
totalRows=sheet.getLastRowNum();
rowIterator=sheet.rowIterator();
}

System.out.println("Total Row Count : "+totalRows);
System.out.println("\nContent from document =>");
while(rowIterator.hasNext())
{
Iterator<Cell> cellIterator=rowIterator.next().cellIterator();
while(cellIterator.hasNext())
{
String cellData=cellIterator.next().toString();
System.out.print(cellData+"\t");
}
System.out.println();
}
}
}

Please do comment and share the post with your friends and colleagues. For any query or question, you can also email me at ashu.kumar940@gmail.com. 

Also, read How to read word document file using JAVA?

Other Blogs: 
https://agilehelpdoc.blogspot.in/

How to print Hello World as Olleh Dlrow in JAVA?

Question: Print Hello World as 'Olleh Dlrow'.

Problem: Write a program which reverses the each word in a sentence and also while printing first letter should be in upper case. Input can contain any number of words as mentioned below:


Input String:- 
             Hello World
             ask for help
            You Live Only Once

Output would be as:-
            Olleh Dlrow
            Ksa Rof Pleh
            Uoy Evil Ylno Ecno

Code:
public class ReverseWord
{

public static void main(String[] args)
{
// Step #1: Taking multiple Strings in the array.
String[] strArray={"Hello World","ask for help","You Live Only Once"};

for (String string : strArray)
{
reverseString(string);
}

}

public static void reverseString(String string)
{
// Step #2: Extracting words from a sentence.
String[] getWords=string.split(" ");

for (String word : getWords)
{
// Step #3: Getting reverse of each word.
String revString=new StringBuffer(word).reverse().toString();

// Step #4: Creating first letter in Upper case and rest in Lower case for each word.
revString=revString.substring(0, 1).toUpperCase()+revString.substring(1).toLowerCase();

// Step #5: Printing the expected Output
System.out.print(revString+" ");
}
System.out.println();
}
}

If anybody has any alternate solution please Post Your Answer in the comment box.

Please do comment and share the post with your friends and colleagues. For any query or question, you can also email me at ashu.kumar940@gmail.com.

Other Blogs:
https://agilehelpdoc.blogspot.in/

How to get Xpath of simple text which is outside the HTML Tags?

Question: How to get Xpath of simple text which is outside the HTML Tags?

Problem:

Need to extract xpaths, so that user can identify node 'Chapter 1' by using the text Chapter 1 in the xpath and without using the text Chapter 1 in the xpath.

Reference URL:

https://agilehelpdoc.blogspot.in/

Reference Screenshot:

HTML Code:

<div style="text-align: Center;">

        <span id="chapNo" style="color: #351c75;"/>

        Chapter 1

</div>

 

Answers:

1. Access the text directlty using the Xpath. Note that, if there are multiple instances having same HTML structure, this will return all nodes. In that case we have to use loop to navigate all the nodes.
//span[@id='chapNo']/following-sibling::text()

2. We can also use the on screen text 'Chapter 1' to find all the nodes with specific text. Xpath mentioned below will return all the nodes with same HTML structure and the text specified by the developer.
//div[text()[contains(.,'Chapter 1')]] 


Please do comment and share the post with your friends and colleagues. For any query or question, you can also mail us at ashu.kumar940@gmail.com.

Other Blogs:
https://agilehelpdoc.blogspot.in/