How to Read and Write Data to excel sheet in java

[lokanath60]

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;
import java.util.Scanner;
 
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.ss.usermodel.WorkbookFactory;
 
 
public class ReadWriteXL
{
public static void main(String[] args) throws InvalidFormatException, IOException{
    ArrayList arr=new ArrayList();
File f=new File("D:\\temp\\TEXT\\email.txt");
Scanner in=new Scanner(f);
System.out.println("Read Data From The Txt file ");
while(in.hasNext())
{    
arr.add(in.next());
}
System.out.println("Data From ArrayList");
System.out.println(arr);
 
 
System.out.println("Write data to an Excel Sheet");
FileOutputStream fos=new FileOutputStream("D:/temp/1.xls");
HSSFWorkbook workBook = new HSSFWorkbook();
HSSFSheet spreadSheet = workBook.createSheet("email");
HSSFRow row;
HSSFCell cell;
for(int i=0;i<arr.size();i++){
    row = spreadSheet.createRow((short) i);
cell = row.createCell(i);
System.out.println(arr.get(i));
cell.setCellValue(arr.get(i).toString());
}
System.out.println("Done");
workBook.write(fos);
arr.clear();
System.out.println("ReadIng From Excel Sheet");
 
FileInputStream fis = null;
    fis = new FileInputStream("D:/temp/1.xls");
 
    HSSFWorkbook workbook = new HSSFWorkbook(fis);
    HSSFSheet sheet = workbook.getSheetAt(0);
    Iterator rows = sheet.rowIterator();
 
    while (rows.hasNext()) {
        HSSFRow row1 = (HSSFRow) rows.next();
        Iterator cells = row1.cellIterator();
        while (cells.hasNext()) {
            HSSFCell cell1 = (HSSFCell) cells.next();
            arr.add(cell1);
        }
}
System.out.println(arr);
 
 
 
 
 
}}
 
 
 

[Oralloy]

lokanath60,

Nice bit of code - does it work, or is there a problem we can help you with?

I needed this about three years ago, and fell on my nose, so I'm glad to see your work.

Are you thinking of turning this into an article of some sort?

Cheers,
Oralloy

[lokanath60]

It is working fine ..I have posted it only because any one who is searing for "How to Read and Write Data to excel sheet in java" he can find this answer

[Oralloy]

lokanath60,

Have you considered writing an article on creating and manipulating spreadsheets using Java?

I had a problem about three years ago where I wanted to generate custom spreadsheets based on the content of the Atlassian Crucible tool's database.

Basically, the extension that I put into Crucible was supposed to update one page of a complex spreadsheet based on the comments in the tool.

Since I couldn't reliable manipulate the spreadsheet directly from Java, I ended up building a VBA form in the spreadsheet that interrogated Crucible through its REST interface. The resulting data were then pushed into the spreadsheet using VBA.

Oralloy