Scribd Logo
Upload

Welcome to Scribd!

  • 112 views

    Apache Poi Read and Write Excel

    Uploaded by

    Syed
    This document provides code examples for reading from and writing to Excel files using Apache POI in Java. It first shows how to read data from an Excel file by getting the file type, opening the workbook, getting the sheet, and iterating through rows and cells to print values. It then demonstrates writing to an Excel file by creating a new row, filling it with sample data, and saving the workbook. The main methods call the readExcel and writeExcel functions respectively to perform the file operations.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Apache Poi Read and Write Excel

    Uploaded by

    Syed
    112 views9 pages
    This document provides code examples for reading from and writing to Excel files using Apache POI in Java. It first shows how to read data from an Excel file by getting the file type, opening the workbook, getting the sheet, and iterating through rows and cells to print values. It then demonstrates writing to an Excel file by creating a new row, filling it with sample data, and saving the workbook. The main methods call the readExcel and writeExcel functions respectively to perform the file operations.

    Original Description:

    Selenium

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides code examples for reading from and writing to Excel files using Apache POI in Java. It first shows how to read data from an Excel file by getting the file type, opening the workbook, getting the sheet, and iterating through rows and cells to print values. It then demonstrates writing to an Excel file by creating a new row, filling it with sample data, and saving the workbook. The main methods call the readExcel and writeExcel functions respectively to perform the file operations.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    112 views9 pages

    Apache Poi Read and Write Excel

    Uploaded by

    Syed
    This document provides code examples for reading from and writing to Excel files using Apache POI in Java. It first shows how to read data from an Excel file by getting the file type, opening the workbook, getting the sheet, and iterating through rows and cells to print values. It then demonstrates writing to an Excel file by creating a new row, filling it with sample data, and saving the workbook. The main methods call the readExcel and writeExcel functions respectively to perform the file operations.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 9

    Read data from Excel file

    Complete Example: Here we are trying to read data from excel file
    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


    Write data on Excel file
    Complete Example: Here we are trying to write data from excel file by adding
    new row in excel file
    package excelExportAndFileIO;

    import java.io.File;

    import java.io.FileInputStream;

    import java.io.FileOutputStream;

    import java.io.IOException;

    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.ss.usermodel.Sheet;

    import org.apache.poi.ss.usermodel.Workbook;

    import org.apache.poi.xssf.usermodel.XSSFWorkbook;

    public class WriteGuru99ExcelFile {

    public void writeExcel(String filePath,String fileName,String sheetName,String[]


    dataToWrite) 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 substing 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 excel sheet by sheet name

    Sheet sheet = guru99Workbook.getSheet(sheetName);

    //Get the current count of rows in excel file

    int rowCount = sheet.getLastRowNum()-sheet.getFirstRowNum();

    //Get the first row from the sheet

    Row row = sheet.getRow(0);

    //Create a new row and append it at last of sheet

    Row newRow = sheet.createRow(rowCount+1);

    //Create a loop over the cell of newly created Row

    for(int j = 0; j < row.getLastCellNum(); j++){

    //Fill data in row

    Cell cell = newRow.createCell(j);

    cell.setCellValue(dataToWrite[j]);

    //Close input stream

    inputStream.close();

    //Create an object of FileOutputStream class to create write data in excel file

    FileOutputStream outputStream = new FileOutputStream(file);

    //write data in the excel file

    guru99Workbook.write(outputStream);

    //close output stream

    outputStream.close();

    public static void main(String...strings) throws IOException{

    //Create an array with the data in the same order in which you expect to be filled in
    excel file

    String[] valueToWrite = {"Mr. E","Noida"};

    //Create an object of current class

    WriteGuru99ExcelFile objExcelFile = new WriteGuru99ExcelFile();

    //Write the file using file name , sheet name and the data to be filled

    objExcelFile.writeExcel(System.getProperty("user.dir")
    +"\\src\\excelExportAndFileIO","ExportExcel.xlsx","ExcelGuru99Demo",valueToWrite);

    You might also like