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.

Contact me @ sudheer@javarecent.com or admin@java-recent.com