Read and Write Excel file by using Apache POI in Java

Perform following steps first for correct configuration:

1. Download Apache POI from here
2. Add Apache POI .jar files in your Java project as External JARs from Build path
3. For .xls type Excel file use HSSF and for .xlsx type Excel file use XSSF

Read Excel File:

Reading a file using Apache POI is very simple and involve following steps:

1. Create a file with specific location to find it
2. Create an Input stream with that file
3. Create POI Work book(HSSF/ XSSF) with that Input stream
4. Create POI Work sheet(HSSF/ XSSF) with that Work book
5. Get number of rows in the file
6. Get number of columns in the file
7. Create a 2 dimension array to store file data
8. Traverse the array and do followings,
1. Create HSSF/ XSSF row for each row in the file
2. Create HSSF/ XSSF cell for each cell in the file
3. Store the cell value in a String variable
4. Store the String in respective array cell

Lets have a look at the code doing these steps:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class SimpleFileReadWrite { public static void main(String[] args) throws IOException { File excel = new File ("D:\\Credential.xlsx"); FileInputStream fis = new FileInputStream(excel); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet ws = wb.getSheet("creden"); int rowNum = ws.getLastRowNum() + 1; int colNum = ws.getRow(0).getLastCellNum(); String [][] data = new String [rowNum] [colNum]; for(int i = 0; i <rowNum; i++){ XSSFRow row = ws.getRow(i); for (int j = 0; j < colNum; j++){ XSSFCell cell = row.getCell(j); String value = cell.toString(); data[i][j] = value; System.out.println ("the value is " + value); } } }

Write Excel File:

Writing a file using Apache POI is very simple and involve following steps:

1. Create a file with specific location to create it
2. Create an Output stream with that file
3. Create POI Work book(HSSF/ XSSF) with that Input stream
4. Create POI Work sheet(HSSF/ XSSF) with that Work book with specific name
5. Store String values in a 2 dimension array to write in file
6. Traverse the array and do followings,
1. Create HSSF/ XSSF row for each row in the file
2. Create HSSF/ XSSF cell for each cell in the file
3. Set cell value with respective array cell

Lets have a look at the code doing these steps(here I am using the same code I used for reading file. So this program read from a .xlsx file, store data in an 2 dimension array and write it in another .xlsx file):

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54

import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class SimpleFileReadWrite { public static void main(String[] args) throws IOException { File excel = new File ("D:\\Credential.xlsx"); FileInputStream fis = new FileInputStream(excel); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet ws = wb.getSheet("creden"); int rowNum = ws.getLastRowNum() + 1; int colNum = ws.getRow(0).getLastCellNum(); String [][] data = new String [rowNum] [colNum]; for(int i = 0; i <rowNum; i++){ XSSFRow row = ws.getRow(i); for (int j = 0; j < colNum; j++){ XSSFCell cell = row.getCell(j); String value = cell.toString(); data[i][j] = value; System.out.println ("the value is " + value); } } File outexcel = new File ("D:\\Output_excel.xlsx"); FileOutputStream fos = new FileOutputStream(outexcel); XSSFWorkbook owb = new XSSFWorkbook(); XSSFSheet sht = owb.createSheet("New sheet"); for(int i = 0; i <rowNum; i++){ XSSFRow orow = sht.createRow(i); for (int j = 0; j < colNum; j++){ XSSFCell ocell = orow.createCell(j); ocell.setCellValue(data[i][j]); System.out.println ("the value is " + data[i][j]); } } owb.write(fos); fos.flush(); fos.close(); } }