Monday, June 17, 2013

Reading/Writing data from/to excel sheets(.xls) using Java

Hi,
Welcome to Java-recent.

In this post we will discuss about reading and writing content to Excel documents(.xls).
Reading/Writing from .xlsx will be discussed in my next post

Prerequisites :-
  • Apache-poi jars
    The latest version jars can be downloaded from     http://poi.apache.org/
  • A Java development IDE like Eclipse etc.
  • Configure the downloaded jars in the build-path of IDE

The main classes involved in reading/writing contents to excel(.xls) file are
  • HSSFWorkbook
  • FileInputStream
  • FileOutputStream

First we will see how to read content from .xls file

Reading data from .xls :-

1 public void readXls() throws IOException {
2 FileInputStream fis = new
3 FileInputStream("F:\\ReadXls.xls");
4 HSSFWorkbook hwb = new HSSFWorkbook(fis);
5 // HSSFSheet sheet=hwb.getNumberOfSheets();
6 HSSFSheet sheet = hwb.getSheetAt(0);
7 Iterator<Row> rows = sheet.iterator();
8 while (rows.hasNext()) {
9 Row currentRow = rows.next();
10 Iterator<Cell> column = currentRow.cellIterator();
11
12 while (column .hasNext()) {
13
14 Cell currentCell = column.next();
15 switch (currentCell.getCellType()) {
16 case Cell.CELL_TYPE_STRING:
17 System.out.println(currentCell.getStringCellValue());
18 break;
19 case Cell.CELL_TYPE_NUMERIC:
20 System.out.println(currentCell.getNumericCellValue());
21 break;
22 case Cell.CELL_TYPE_BOOLEAN:
23 System.out.println(currentCell.getBooleanCellValue());
24 break;
25 case Cell.CELL_TYPE_ERROR:
26 System.out.println(currentCell.getErrorCellValue());
27 break;
28 }

29 }

30 }
31 }

Explanation:-
  • line 2 an 3 we are creating fileInputStream by passing location of .xls file(F:\\ReadXls.xls)
  • Line 4 creating HSSFWorkbook object and passing fis as argument
  • After this we will first retrieve sheets using getSheetAt(sheet index) or to get number of sheets getNumberOfSheets()
  • Then we use Iterator for iterating over rows and cell values(Line 7 and Line 10)
  • Two while loops are there one for Row iterating and other for cell iterating
  • A cell may contain different types of values like String values,numeric values,comments etc.
  • Depending on their type they are retrieved using switch-case statements and according printing the values to console(Line no 15 to Line no 27)

The iterator will first take first row and then iterate across cell values,if we want a specific cell value or row values Apache-poi has methods like getCell(int),getRow(int) etc.

Using fileInputStream will produce checked exception IOException.


Now we will look at writing content to .xls file.

Writing content to .xls file :-

1 public void writeXls() throws IOException {
//created array for storing values
2 String str[][] = new String[2][3];
3 str[0][0] = "SNo";
4 str[0][1] = "Name";
5 str[0][2] = "EmailId";
6 str[1][0] = "1";
7 str[1][1] = "Java-recent";
8 str[1][2] = "sudheer@javarecent.com";
9
10 HSSFWorkbook myWorkBook = new HSSFWorkbook();
11 HSSFSheet sheet = myWorkBook.createSheet(“sheet1”);
12
13 for (int rowCount = 0; rowCount < 2; rowCount++) {
14 HSSFRow myRow = sheet.createRow(rowCount);
15
16 for (int cellCount = 0; cellCount < 3; cellCount++) {
17 Cell myCell = myRow.createCell(cellCount);
18 myCell.setCellValue(str[rowCount][cellCount]);
19
20 }
21
22 }
23 FileOutputStream fos = new FileOutputStream("F:\\writexls.xls");
24 try {
25
26 myWorkBook.write(fos);
27 } finally {
28 fos.close();
29 }
30 }

Explanation :-
  • Line no 2 to 8 created a two dimensional array for storing values to be inserted into excel with row size 2 and column size 3
  • Line no 11 - 12 created a HSSFWorkbook object and a sheet named “sheet1”
  • After creating a sheet our next step is to create rows and cells
  • Line no 13 – 20 , there are two for loops,outer for loop for creating rows and inner for loop for creating cells and inserting respective values in it
  • Two rows and three columns will be created
  • Line no 18 is used for setting content in a cell
  • After inserting desired values we need to use FileOutputStream to push data into the file
  • myWorkBook.write(fos); is used to write data to excel file ,if there is no file at a particular location a new one will gets created
  • If already there is a file the contents will be added/replaces as per rows and cell values
  • finally block is used to close all the resources used in the program fos.close();y way from Java 7 there is no need to mention finally block

A main method that calls the above methods and handle exceptions :-
public static void main(String[] args) {
ExcelReader excelReader = new ExcelReader();
try {
// excelReader.readXls();
excelReader.writeXls();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}


For more details on formatting cells etc refer @     http://poi.apache.org/




Happy Learning

Please provide your valuable comments on this article and share it across your network.


Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Like and Share