Read Excel File Cell By Cell
package com.softwaretestingo.file;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcelFile
{
public static void main(String[] args) throws IOException
{
File file=new File("D:\\Workspace\\Automation\\SeleniumPractice\\Resources\\ReadExcelFile.xlsx");
FileInputStream fi=new FileInputStream(file);
XSSFWorkbook wb=new XSSFWorkbook(fi);
XSSFSheet sheet=wb.getSheet("login");
//Number Of Rows
XSSFRow row=sheet.getRow(1);
Cell cell=row.getCell(1);
System.out.println(sheet.getRow(0).getCell(0)+" "+sheet.getRow(0).getCell(1));
System.out.println(sheet.getRow(1).getCell(0)+" "+sheet.getRow(1).getCell(1));
System.out.println(sheet.getRow(2).getCell(0)+" "+sheet.getRow(2).getCell(1));
}
}
Read All Cell Values
package com.softwaretestingo.file;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadAllCellValue
{
public static void main(String[] args) throws IOException
{
File file=new File("D:\\Workspace\\Automation\\SeleniumPractice\\Resources\\ReadExcelFile.xlsx");
FileInputStream fi=new FileInputStream(file);
@SuppressWarnings("resource")
XSSFWorkbook wb=new XSSFWorkbook(fi);
XSSFSheet sheet=wb.getSheet("login");
//Top Read The Row Values
for(int i=sheet.getFirstRowNum();i<=sheet.getLastRowNum();i++)
{
//To Read The Cell Values Of Each Row
for(int j=sheet.getRow(i).getFirstCellNum();j<sheet.getRow(i).getLastCellNum();j++)
{
System.out.print(sheet.getRow(i).getCell(j)+"\t");
}
System.out.println();
}
}
}
Read Excel File without using XSSFWorkbook & XSSFSheet
package com.softwaretestingo.file;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcelFile
{
public static void main(String[] args) throws IOException
{
File file=new File("D:\\Workspace\\Automation\\SeleniumPractice\\Resources\\ReadExcelFile.xlsx");
FileInputStream fi=new FileInputStream(file);
XSSFWorkbook wb=new XSSFWorkbook(fi);
XSSFSheet sheet=wb.getSheet("login");
//Number Of Rows
XSSFRow row=sheet.getRow(1);
Cell cell=row.getCell(1);
//System.out.println(sheet.getRow(0).getCell(0)+" "+sheet.getRow(0).getCell(1));
System.out.println(sheet.getRow(1).getCell(0)+" "+sheet.getRow(1).getCell(1));
System.out.println(sheet.getRow(2).getCell(0)+" "+sheet.getRow(2).getCell(1));
}
}
Write In Excel File
package com.softwaretestingo.file;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class WriteExcelFile
{
public static void main(String[] args) throws IOException
{
File file=new File("D:\\Workspace\\Automation\\SeleniumPractice\\Resources\\WriteExcelFile.xlsx");
FileInputStream fis=new FileInputStream(file);
@SuppressWarnings("resource")
XSSFWorkbook wb=new XSSFWorkbook(fis);
XSSFSheet sheet=wb.getSheet("TestData");
int lastRowNo=sheet.getLastRowNum();
System.out.println(lastRowNo);
//Go to The Last Cell Number
for(int i=0;i<=lastRowNo;i++)
{
//Create a New Row & Write the Valuev
sheet.createRow(lastRowNo+1).createCell(0).setCellValue("TestData");
}
fis.close();
FileOutputStream fos=new FileOutputStream(file);
wb.write(fos);
fos.close();
}
}
