Writing data to spreadsheet using poi

1. Create a project and package
2. Download the"poi-3.13"
3. Add the "xmlbeans" jar and the "poi" jars.
4. This example reads the data from a spreadsheet and once reading is done, writes the data “ReadingDone” in last column
5. In this example, spreadsheet resides at D:\Hemanth\TestData\TestData.xlsx

 

Sample Program:

 

import java.io.File;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;

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

public class ReadMain {

 public static void main(String[] args) throws Exception {

  File source = new File("D:\\Hemanth\\TestData\\TestData.xlsx");
  FileInputStream fis = new FileInputStream(source);
  XSSFWorkbook wb = new XSSFWorkbook(fis);
  int FirstRow = wb.getSheet("Sheet2").getFirstRowNum();
  int FirstCol = wb.getSheet("Sheet2").getRow(FirstRow).getFirstCellNum();
  System.out.println("FirstRow:"+FirstRow);
  System.out.println("FirstCell:"+FirstCol);
  int Lastrow = wb.getSheet("Sheet2").getLastRowNum();
  System.out.println("LastRow:" +Lastrow);
  int Lastcol = wb.getSheet("Sheet2").getRow(Lastrow).getLastCellNum();
  System.out.println("LastCol:" +Lastcol);
  String activecell = wb.getSheet("Sheet2").getActiveCell();
  System.out.println("Active Cell:" + activecell);
  ArrayList mylist = new ArrayList();
  String Value;

  for (int i=0;i<=Lastrow;i++)
  {
   for(int j=0;j<Lastcol;j++)
   {
    Value = wb.getSheet("Sheet2").getRow(i).getCell(j).getStringCellValue();
    System.out.println("Read Value:"+Value);
    mylist.add(Value);
 
   }

   wb.getSheet("Sheet2").getRow(i).createCell(Lastcol).setCellValue("ReadingDone");
  }

    System.out.println("Values:"+mylist);
   FileOutputStream fos = new FileOutputStream(source);
 
   wb.write(fos);
    wb.close();  
 
 }

}

 

Result: