- Download Apache POI from here
- Add Apache POI .jar files in your Java project as External JARs from Build path
- 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:
- Create a file with specific location to find it
- Create an Input stream with that file
- Create POI Work book(HSSF/ XSSF) with that Input stream
- Create POI Work sheet(HSSF/ XSSF) with that Work book
- Get number of rows in the file
- Get number of columns in the file
- Create a 2 dimension array to store file data
- Traverse the array and do followings,
- Create HSSF/ XSSF row for each row in the file
- Create HSSF/ XSSF cell for each cell in the file
- Store the cell value in a String variable
- 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:
- Create a file with specific location to create it
- Create an Output stream with that file
- Create POI Work book(HSSF/ XSSF) with that Input stream
- Create POI Work sheet(HSSF/ XSSF) with that Work book with specific name
- Store String values in a 2 dimension array to write in file
- Traverse the array and do followings,
- Create HSSF/ XSSF row for each row in the file
- Create HSSF/ XSSF cell for each cell in the file
- 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(); } } |
