Apache POI
In this post, you will learn how to use Apache POI to read and write data in Excel files. We'll cover the essentials of handling spreadsheets with Java, from creating and modifying files to extracting and updating data. By the end, you'll be able to efficiently manage your Excel data programmatically with ease.
5/8/20242 min read
Before starting lets cover some basic excel terms:
Workbook- This is basically simple excel file.
Sheet- This is referred to sheets inside excel file(workbook).
Ex- Sheet 1, Sheet 2 and Sheet 3 in image on right.
Row-This is a horizontal line of cells that runs from left to right across the worksheet.
Ex- We can see row number 42, 43, 44, 45 and 46 in image on right.
Column-This is a vertical line of cells that runs from top to bottom across the worksheet.
Ex- We can see column A, column B and column C in image on right.
Cell-This is the basic unit of a worksheet where you can enter and manipulate data.
There are two formats in which excel file can be created:
xlsx- These are excel version 2007 or later, and to handle these file we use XSSFWorkbook class of Apache POI.
xls-These are excel version 2003 or before, and to handle these file we use HSSFWorkbook class of Apache POI.
Let's try coding same thing.
1- So we will create new object of XSSFWorkbook class( considering we are using 2007 later version of excel).
XSSFWorkbook workbook = new XSSFWorkbook();
You can consider now workbook is created in the memory now.
2-After this we will use createSheet() method which takes name:String as parameter.
Sheet sheet=workbook.createSheet("Sheet 1");
You can consider now inside workbook there is one sheet created of name "Sheet 1".
3-Now we will use sheet.createRow() method which take index:int as parameter.
Row row=sheet.createRow(0);
You can consider now inside sheet one there is one row created.
FYI- I have passed 0 as it is zero based indexing.
4-Now lets add some values in row zero, for this we will use createCell() method which accept columnIndex:int. So what this means is like in step 3 we created row 0, now with createCell() we are passing in row zero column indexes at which cells will be created and we will set value at that index with setCellValue() method which accepts value:Type.
row.createCell(0).setCellValue("firstName");// This means at row zero, create cell/column at index 0 and add value "firstName".
row.createCell(1).setCellValue("secondName");// This means at row zero, create cell/column at index 1 and add value "secondName".
row.createCell(2).setCellValue("department");// This means at row zero, create cell/column at index 2 and add value "department".
5-Now till now whatever we were doing is happening in memory, now its time to copy this in-memory excel workbook data to proper excel file. To do this we will use workbook.write() method, which accepts FileOutputStream where we will pass name.
workbook.write(new FileOutputStream("demo.xlsx"));// This will copy your in memory changes you did till step 4 to a new file name demo.xlsx.
6-Close workbook instance.
workbook.close();
