Excel file operation:
There are mainly four terminologies
to access data in Excel.
WoorkBook: Excel workbook
Sheet: Excel sheet
Row: rows in a sheet
Cell: cells in a row
Apache POI is a powerful Java
library to work with Microsoft Office Excel. The above four are defined as an
individual interface in Apache POI. All classes required to access excel file are
implemented from these interfaces.
There are two types of classes
to access Excel file:
All classes prefixed with HSSF
are used to access Microsoft Excel 2003 file or older (.xls).
All classes prefixed with XSSF
are used to access Microsoft Excel 2007
file or later (.xlsx).
HSSFWorkbook: Is a class implemented from WoorkBook interface represents XLS workbook .
HSSFSheet: Is a class implemented from Sheet interface represents a sheet in an XLS file .
HSSFRow: Is a class implemented from Row interface represents a row in the sheet of XLS file
HSSFCell: Is a class implemented from Cell interface represents a cell in a row of XLS file.
XSSFWorkbook: Is a class implemented from WoorkBook interface representsof XLSX file.
XSSFSheet: Is a class implemented from Sheet interface representsa sheet in an XLSX file
XSSFRow: Is a class implemented from Row interface representsa row in the sheet of XLSX file
XSSFCell: Is a class implemented from Cell interface represents a cell in a row of XLSX file.
import java.io.File;
import
java.io.FileInputStream;
import
java.io.FileOutputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook;
import
org.apache.poi.ss.usermodel.Sheet;
import
org.apache.poi.ss.usermodel.Row;
import
org.apache.poi.ss.usermodel.Cell;
import
org.apache.poi.ss.usermodel.Workbook;
import
org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class XLFileOperation {
public static Workbook workbook;
public Sheet sheet;
public static Row row;
public static Cell cell;
public String filePath="D:\\Automation\\AutomationFramework\\JavaProgram\\InputFiles";
public String fileName="DataEngine.xlsx";
public void excelRead(String sheetName){
FileInputStream
excelFile=null;
try{
File
xlfile = new File(filePath+"\\"+fileName);
excelFile=new FileInputStream(xlfile);
//Find the file
extension by splitting file name in substring
and getting only extension name
String fileExtension = fileName.substring(fileName.indexOf("."));
if(fileExtension.trim().equals(".xlsx")){
// Create a
workbook instance for XLSX file
workbook= new XSSFWorkbook(excelFile);
}
else if (fileExtension.trim().equals(".xls")){
// Create a
workbook instance for XLS file
workbook= new HSSFWorkbook(excelFile);
}
else
System.out.println("Invalid file
:"+
fileExtension);
for (int sn=0; sn < workbook.getNumberOfSheets();
sn++) {
System.out.println("Name of Sheet
no "
+ sn + " is " + workbook.getSheetName(sn));
}
// Get the sheet
from the workbook
sheet=workbook.getSheet(sheetName);
System.out.println("Reading Sheet
:"+sheetName);
//Get the number of
rows in the sheet
int rowCount=sheet.getLastRowNum();
System.out.println("No of rows in
sheet "+ sheetName+" is :" + (rowCount+1));
//traverse each row
one by one
for(int rowC=0;rowC<=rowCount;rowC++){
//Get the current
row
row=sheet.getRow(rowC);
//Get the last cell
number
int cellCount=row.getLastCellNum();
System.out.println("Numbers of
cells in current row is :" + cellCount);
//Traverce
each column in the current row
for(int colC=0;colC<=cellCount-1;colC++){
//Get the current
cell
cell=row.getCell(colC);
String
cellValue="";
if(cell!=null){
switch (cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC:
Double
icell=cell.getNumericCellValue();
cellValue=new Double(icell).toString();
System.out.println("Cell value is
: "+cellValue);
break;
case Cell.CELL_TYPE_BLANK:
cellValue="";
System.out.println("Cell value is
Blank: ");
break;
case Cell.CELL_TYPE_STRING:
cellValue=cell.getStringCellValue();
System.out.println("Cell value is
: "+cellValue);
break;
default:
System.out.println("Cell value is
other type");
cellValue=cell.getStringCellValue();
break;
}
}
}
}
}
catch(NullPointerException
ne){
System.out.println("Unable to
read the sheet : Null Pointer Exception " + ne.getMessage());
}
catch(Exception e){
System.out.println("Exception
caught :" + e.getMessage());
e.printStackTrace();
}
finally{
try {
excelFile.close();
}
catch (IOException e) {
// TODO Auto-generated
catch block
e.printStackTrace();
}
}
}
//Write in a given
excel sheet
public void
excelSheetWrite(String sheetName, String[] inputData){
FileInputStream
excelFile=null;
try{
File
xlfile = new File(filePath+"\\"+fileName);
excelFile=new FileInputStream(xlfile);
//Find the file
extension by splitting file name in substring
and getting only extension name
String fileExtension = fileName.substring(fileName.indexOf("."));
if(fileExtension.trim().equals(".xlsx")){
// Create a workbook
instance for XLSX file
workbook= new XSSFWorkbook(excelFile);
}
else if (fileExtension.trim().equals(".xls")){
// Create a
workbook instance for XLS file
workbook= new HSSFWorkbook(excelFile);
}
else
System.out.println("Invalid file
:"+
fileExtension);
// Get the sheet from the workbook
sheet=workbook.getSheet(sheetName);
int rowCount=sheet.getLastRowNum();
//Create a row at
the end of the sheet
row=sheet.createRow(rowCount+1);
System.out.println(inputData.length);
//Create cell for
each element you want to add and then write the value in that cell
for(int i=0;i<inputData.length;i++){
cell=row.createCell(i);
cell.setCellValue(inputData[i]);
}
excelFile.close();
//Create an object
of FileOutputStream class to write data in excel file
FileOutputStream
fo=new FileOutputStream(xlfile);
//write data in the
excel file
workbook.write(fo);
fo.flush();
fo.close();
}catch(Exception e){
e.printStackTrace();
}
finally{
System.out.println("daf");
}
}
//Write into a perticular
cell in a sheet
public void
excelWriteCell(String sheetName, String cellValue, int rowNum, int colNum){
FileInputStream
excelFile=null;
try{
File
xlfile = new File(filePath+"\\"+fileName);
excelFile=new FileInputStream(xlfile);
//Find the file
extension by splitting file name in substring
and getting only extension name
String fileExtension = fileName.substring(fileName.indexOf("."));
if(fileExtension.trim().equals(".xlsx")){
// Create a
workbook instance for XLSX file
workbook= new XSSFWorkbook(excelFile);
}
else if (fileExtension.trim().equals(".xls")){
//Create a workbook
instance for XLS file
workbook= new HSSFWorkbook(excelFile);
}
else
System.out.println("Invalid file
:"+
fileExtension);
// Get the sheet from the workbook
sheet=workbook.getSheet(sheetName);
//int
rowCount=sheet.getLastRowNum();
//Create a row at
the end of the sheet
row=sheet.getRow(rowNum);
cell=row.getCell(colNum, row.RETURN_BLANK_AS_NULL);
if(cell==null){
cell=row.createCell(colNum);
cell.setCellValue(cellValue);
}
else{
cell.setCellValue(cellValue);
}
excelFile.close();
//Create an object
of FileOutputStream class to write data in excel file
FileOutputStream
fo=new FileOutputStream(xlfile);
//write data in the
excel file
workbook.write(fo);
fo.flush();
fo.close();
}catch(Exception e){
e.printStackTrace();
}
}
//Create a new
sheet and then write in it.
public void excelWrite(String sheetName, Object[][] Employees){
FileInputStream
excelFile=null;
try{
File
xlfile = new File(filePath+"\\"+fileName);
excelFile=new FileInputStream(xlfile);
//Find the file
extension by splitting file name in substring
and getting only extension name
String fileExtension = fileName.substring(fileName.indexOf("."));
if(fileExtension.trim().equals(".xlsx")){
// Create a workbook
instance for XLSX file
workbook= new XSSFWorkbook(excelFile);
}
else if (fileExtension.trim().equals(".xls")){
// Create a
workbook instance for XLS file
workbook= new HSSFWorkbook(excelFile);
}
else
System.out.println("Invalid file
:"+
fileExtension);
// Get the sheet from the workbook
sheet=workbook.createSheet(sheetName);
//Create a row at
the end of the sheet
int rowNum = 0;
for (Object[] Employee : Employees) {
Row row = sheet.createRow(rowNum++);
int colNum = 0;
for (Object field : Employee) {
Cell cell = row.createCell(colNum++);
if (field instanceof String) {
cell.setCellValue((String)
field);
} else if (field instanceof Integer) {
cell.setCellValue((Integer)
field);
}
}
}
excelFile.close();
//Create an object
of FileOutputStream class to write data in excel file
FileOutputStream
fo=new FileOutputStream(xlfile);
//write data in the
excel file
workbook.write(fo);
fo.flush();
fo.close();
}catch(Exception e){
e.printStackTrace();
}
}
//Read from a perticular
cell from a sheet
public void excelReadCell(String
sheetName, int rowNum, int colNum){
FileInputStream
excelFile=null;
try{
File
xlfile = new File(filePath+"\\"+fileName);
excelFile=new FileInputStream(xlfile);
//Find the file
extension by splitting file name in substring
and getting only extension name
String fileExtension = fileName.substring(fileName.indexOf("."));
if(fileExtension.trim().equals(".xlsx")){
// Create a
workbook instance for XLSX file
workbook= new XSSFWorkbook(excelFile);
}
else if (fileExtension.trim().equals(".xls")){
workbook= new HSSFWorkbook(excelFile);
// Create a
workbook instance for XLS file
}
else
System.out.println("Invalid file
:"+
fileExtension);
for (int sn=0; sn < workbook.getNumberOfSheets();
sn++) {
System.out.println("Sheet " + sn + " is called
" +
workbook.getSheetName(sn));
}
// Get the sheet
from the workbook
sheet=workbook.getSheet(sheetName);
System.out.println("Reading the
Sheet :"+sheetName);
//Get the numbers
of rows in the sheet
int rowCount=sheet.getLastRowNum();
System.out.println("No of rows in
sheet "+ sheetName+" is :" + rowCount);
//Get the current
row
row=sheet.getRow(rowNum);
//Get the last cell
number
int cellCount=row.getLastCellNum();
System.out.println("Numbers of
cell in current row is :" + cellCount);
cell=row.getCell(colNum);
String
cellValue="";
if(cell!=null){
switch (cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC:
Double
icell=cell.getNumericCellValue();
cellValue=new Double(icell).toString();
System.out.println("Cell value is
: "+cellValue);
break;
case Cell.CELL_TYPE_BLANK:
cellValue="";
System.out.println("Cell value is
Blank: ");
break;
case Cell.CELL_TYPE_STRING:
cellValue=cell.getStringCellValue();
System.out.println("Cell value is
: "+cellValue);
break;
default:
System.out.println("Cell value is
other type");
cellValue=cell.getStringCellValue();
break;
}
}
excelFile.close();
//}
// }
}
catch(NullPointerException
ne){
System.out.println("Unable to
read the sheet : Null Pointer Exception " + ne.getMessage());
}
catch(Exception e){
System.out.println("Exception
caught :" + e.getMessage());
e.printStackTrace();
}
}
public static void main(String[] args) {
// TODO Auto-generated
method stub
Object[][]
Employees = {
{"Name", "EmpCode", "Phone"},
{"Suresh", "X345", 121},
{"Ramesh", "X346", 141},
{"Rajesh", "X347", 181},
{"Rupesh", "X348", 111},
};
String[]
inputData={"Name","Address","Phone"};
XLFileOperation
exl=new XLFileOperation();
exl.excelRead("Test
Cases");
//exl.excelSheetWrite("Test
Cases", inputData);
//exl.excelReadCell("Test
Cases", 4, 0);
//exl.excelWriteCell("Test
Cases","Suresh Kumar Das", 1, 3);
//exl.closeFIle();
//exl.excelWrite("new
Sheet4", Employees);
}
}
No comments:
Post a Comment