By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,979 Members | 943 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,979 IT Pros & Developers. It's quick & easy.

How to Read and Write Data to excel sheet in java

P: 6
Expand|Select|Wrap|Line Numbers
  1. import java.io.File;
  2. import java.io.FileInputStream;
  3. import java.io.FileNotFoundException;
  4. import java.io.FileOutputStream;
  5. import java.io.FileReader;
  6. import java.io.IOException;
  7. import java.util.ArrayList;
  8. import java.util.Collections;
  9. import java.util.Iterator;
  10. import java.util.List;
  11. import java.util.Scanner;
  12.  
  13. import org.apache.poi.hssf.usermodel.HSSFCell;
  14. import org.apache.poi.hssf.usermodel.HSSFRichTextString;
  15. import org.apache.poi.hssf.usermodel.HSSFRow;
  16. import org.apache.poi.hssf.usermodel.HSSFSheet;
  17. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  18. import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
  19. import org.apache.poi.ss.usermodel.Cell;
  20. import org.apache.poi.ss.usermodel.Row;
  21. import org.apache.poi.ss.usermodel.Sheet;
  22. import org.apache.poi.ss.usermodel.Workbook;
  23. import org.apache.poi.ss.usermodel.WorkbookFactory;
  24.  
  25.  
  26. public class ReadWriteXL
  27. {
  28. public static void main(String[] args) throws InvalidFormatException, IOException{
  29.     ArrayList arr=new ArrayList();
  30. File f=new File("D:\\temp\\TEXT\\email.txt");
  31. Scanner in=new Scanner(f);
  32. System.out.println("Read Data From The Txt file ");
  33. while(in.hasNext())
  34. {    
  35. arr.add(in.next());
  36. }
  37. System.out.println("Data From ArrayList");
  38. System.out.println(arr);
  39.  
  40.  
  41. System.out.println("Write data to an Excel Sheet");
  42. FileOutputStream fos=new FileOutputStream("D:/temp/1.xls");
  43. HSSFWorkbook workBook = new HSSFWorkbook();
  44. HSSFSheet spreadSheet = workBook.createSheet("email");
  45. HSSFRow row;
  46. HSSFCell cell;
  47. for(int i=0;i<arr.size();i++){
  48.     row = spreadSheet.createRow((short) i);
  49. cell = row.createCell(i);
  50. System.out.println(arr.get(i));
  51. cell.setCellValue(arr.get(i).toString());
  52. }
  53. System.out.println("Done");
  54. workBook.write(fos);
  55. arr.clear();
  56. System.out.println("ReadIng From Excel Sheet");
  57.  
  58. FileInputStream fis = null;
  59.     fis = new FileInputStream("D:/temp/1.xls");
  60.  
  61.     HSSFWorkbook workbook = new HSSFWorkbook(fis);
  62.     HSSFSheet sheet = workbook.getSheetAt(0);
  63.     Iterator rows = sheet.rowIterator();
  64.  
  65.     while (rows.hasNext()) {
  66.         HSSFRow row1 = (HSSFRow) rows.next();
  67.         Iterator cells = row1.cellIterator();
  68.         while (cells.hasNext()) {
  69.             HSSFCell cell1 = (HSSFCell) cells.next();
  70.             arr.add(cell1);
  71.         }
  72. }
  73. System.out.println(arr);
  74.  
  75.  
  76.  
  77.  
  78.  
  79. }}
  80.  
  81.  
  82.  
May 31 '13 #1
Share this Question
Share on Google+
3 Replies


Oralloy
Expert 100+
P: 983
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
May 31 '13 #2

P: 6
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
Jun 2 '13 #3

Oralloy
Expert 100+
P: 983
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
Jun 2 '13 #4

Post your reply

Sign in to post your reply or Sign up for a free account.