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

Hi,

Welcome to Java-recent.

In this post we will discuss about reading and writing content to Excel documents(.xlsx).

Reading/Writing from .xls is discussed in my previous post @http://java-recent.blogspot.in/2013/06/readingwriting-data-fromto-excel.html

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

• XSSFWorkbook
• FileInputStream
• FileOutputStream

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

Reading data from .xlsx :-

1 public void readXLSX() throws IOException {

2 FileInputStream fis = new FileInputStream("F:\\xlsxRead.xlsx");

3 XSSFWorkbook xlsxBook = new XSSFWorkbook(fis);

4 XSSFSheet sheet = xlsxBook.getSheetAt(0);

5 Iterator<Row> rowIterate = sheet.iterator();

6 while (rowIterate.hasNext()) {

7 Row currentRow = rowIterate.next();

8 Iterator<Cell> cellIterate = currentRow.cellIterator();

9 while (cellIterate.hasNext()) {

10 Cell currentcell = cellIterate.next();

11 System.out.println(currentcell.getStringCellValue());

12 }

13 }

14 }

Explanation:-

• Line 2 creating FileInputStream object with F:\\xlsxRead.xlsx as file path
• Line 3 passing fis to XSSFWorkbook object
• Line 4 getting sheet at location zero means first sheet
• Line 6-12.iterating over rows and retrieving cell values
• cellIterator(); is used to iterate over cells
• If there are different type of cell values like numeric,comments etc. we use switch-case statements and differentiate accordingly

Now lets see writing content to .xlsx file

Writing content to .xlsx file :-

1 public void writeXLSX() throws IOException {

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 FileOutputStream fos = new FileOutputStream("F:\\xlsxRead.xlsx");

11

12 XSSFWorkbook xlsxBook = new XSSFWorkbook();

13 XSSFSheet sheet = xlsxBook.createSheet("sheet1");

14 try {

15 for (int rowCount = 0; rowCount < 2; rowCount++) {

16 //creating a row

17 XSSFRow myRow = sheet.createRow(rowCount);

18

19 for (int cellIndex = 0; cellIndex < 3; cellIndex+ +) {

20 XSSFCell myCell = myRow.createCell(cellIndex);

21 myCell.setCellValue(str[rowCount][cellIndex]);

22 }

23 }

// Writing to a fileoutput

24 xlsxBook.write(fos);

25 } finally {

26 fos.flush();

27 fos.close();

28 }

29 }

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 10 created FileoutputStream and passed F:\\xlsxRead.xlsx file location
• Line 11 and 12 created XSSFWorkbook and XSSFSheet sheet of name sheet1
• Line no 15 to 22 is used to write values,there are two for loops,outer loop for creating row and inner loop for creating cell and inserting values
• XSSFCell myCell = myRow.createCell(cellIndex); is used to create cell
• myCell.setCellValue(str[rowCount][cellIndex]); is used to insert value to cell
• Line 24 is used to write excel content to the file
• finally block is used to cleanup the resources,From Java 7 no need to explicitly write finally block
• More info on Java 7@  http://java-recent.blogspot.in/2013/05/new-features-in-java-7-java-7-comes.html

main method which calls the above methods and handle exceptions :-

public static void main(String[] args) {

XLSXReadWrite xrw = new XLSXReadWrite();

try {

xrw.readXLSX();

xrw.writeXLSX();

} 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