How to Read the excel file
- How to handle excel file using POI (Maven POM Dependency)To read or write an Excel, Apache provide a very famous library POI. This library is capable enough to read and write both XLS and XLSX file format of excel.To read XLS files an HSSF implementation is provided by POI library.To read XLSX , XSSF implementation of POI library will be the choice. Let's study these implementations in detail.
If you are using maven in your project the maven dependency will be
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
Or you can simply download the latest version POI jars from http://poi.apache.org/download.html & download poi-bin-3.10-FINAL-20140208.zip
When you download the zip file for this jar , you need to unzip it and add these all jars to the class path of your project.
Classes and Interfaces in POI:
Following is a list of different Java Interfaces and classes in POI for reading XLS and XLSX file-
- Workbook: XSSFWorkbook and HSSFWorkbook classes implement this interface.
- XSSFWorkbook: Is a class representation of XLSX file.
- HSSFWorkbook: Is a class representation of XLS file.
- Sheet: XSSFSheet and HSSFSheet classes implement this interface.
- XSSFSheet: Is a class representing a sheet in a XLSX file.
- HSSFSheet: Is a class representing a sheet in a XLS file.
- Row: XSSFRow and HSSFRow classes implement this interface.
- XSSFRow: Is a class representing a row in sheet of XLSX file.
- HSSFRow: Is a class representing a row in sheet of XLS file.
- Cell: XSSFCell and HSSFCell classes implement this interface.
- XSSFCell: Is a class representing a cell in a row of XLSX file.
- HSSFCell: Is a class representing a cell in a row of XLS file.
Read/Write operation-
For our example we will consider below given excel file format
Read data from Excel file
Complete Example: Here we are trying to read data from excel file
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
| package excelExportAndFileIO;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class ReadGuru99ExcelFile { public void readExcel(String filePath,String fileName,String sheetName) throws IOException{ //Create a object of File class to open xlsx file File file = new File(filePath+"\\"+fileName); //Create an object of FileInputStream class to read excel file FileInputStream inputStream = new FileInputStream(file); Workbook guru99Workbook = null; //Find the file extension by spliting file name in substring and getting only extension name String fileExtensionName = fileName.substring(fileName.indexOf(".")); //Check condition if the file is xlsx file if(fileExtensionName.equals(".xlsx")){ //If it is xlsx file then create object of XSSFWorkbook class guru99Workbook = new XSSFWorkbook(inputStream); } //Check condition if the file is xls file else if(fileExtensionName.equals(".xls")){ //If it is xls file then create object of XSSFWorkbook class guru99Workbook = new HSSFWorkbook(inputStream); } //Read sheet inside the workbook by its name Sheet guru99Sheet = guru99Workbook.getSheet(sheetName); //Find number of rows in excel file int rowCount = guru99Sheet.getLastRowNum()-guru99Sheet.getFirstRowNum(); //Create a loop over all the rows of excel file to read it for (int i = 0; i < rowCount+1; i++) { Row row = guru99Sheet.getRow(i); //Create a loop to print cell values in a row for (int j = 0; j < row.getLastCellNum(); j++) { //Print excel data in console System.out.print(row.getCell(j).getStringCellValue()+"|| "); } System.out.println(); } } //Main function is calling readExcel function to read data from excel file public static void main(String...strings) throws IOException{ //Create a object of ReadGuru99ExcelFile class ReadGuru99ExcelFile objExcelFile = new ReadGuru99ExcelFile(); //Prepare the path of excel file String filePath = System.getProperty("user.dir")+"\\src\\excelExportAndFileIO"; //Call read file method of the class to read data objExcelFile.readExcel(filePath,"ExportExcel.xlsx","ExcelGuru99Demo"); }} |
Note: We are not using the TestNG framework here. Run the class as Java Application
Read data from Excel file
Complete Example: Here we are trying to read data from excel file
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
|
package yatra;
import java.io.File;
import java.io.FileInputStream;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class keyword {
public static void main(String[] args) throws Exception {
excelread();
}
private static void excelread() throws Exception {
// TODO Auto-generated method stub
File file=new File("D:\\eclips\\yatra\\src\\excelreadfile.xlsx");
FileInputStream fin=new FileInputStream(file);
@SuppressWarnings("resource")
XSSFWorkbook wb=new XSSFWorkbook(fin);
XSSFSheet sheet=wb.getSheet("Sheet1");
int rownum=sheet.getLastRowNum()+1;
int colnum=sheet.getRow(0).getLastCellNum();
String[][] data=new String[rownum][colnum];
for(int i=0; i<rownum; i++){
XSSFRow row=sheet.getRow(i);
for(int j=0; j<colnum; j++)
{
XSSFCell cell=row.getCell(j);
if(cell==null)// Blank cell
{
continue;
}
else
{
String value=cellToString(cell);
data[i][j]=value;
System.out.println(value);
}
}
}
}
private static String cellToString(XSSFCell cell) {
// TODO Auto-generated method stub
int type=cell.getCellType();
Object result = null;
switch (type) {
case 0:
result=cell.getNumericCellValue();
break;
case 1:
result=cell.getStringCellValue();
break;
default:
throw new RuntimeException("No format find");
}
return result.toString() ;
}
}
|
No comments:
Post a Comment