Earlier in this post, we introduced Apache POI- a Java API useful for interacting with Microsoft office documents.
Now we’ll see how can we read and write to an excel file using the API.

Writing an excel file

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

Create a workbook
Create a sheet in workbook
Create a row in sheet
Add cells in sheet
Repeat step 3 and 4 to write more data
// import statements
public class POIforprutorWrite {
public static void main(String[] args)
{
// Blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();

// Create a blank sheet
XSSFSheet sheet = workbook.createSheet("student Details");

// This data needs to be written (Object[])
Map<String, Object[]> data = new TreeMap<String, Object[]>();
data.put("1", new Object[]{ "ID", "NAME", "LASTNAME" });
data.put("2", new Object[]{ 1, "Suraj", "Kumar" });
data.put("3", new Object[]{ 2, "Anuj", "kumar" });
data.put("4", new Object[]{ 3, "Siddhart", "kumar" });
data.put("5", new Object[]{ 4, "Abhay", "Singh" });

// Iterate over data and write to sheet
Set<String> keyset = data.keySet();
int rownum = 0;
for (String key : keyset) {
// this creates a new row in the sheet
Row row = sheet.createRow(rownum++);
Object[] objArr = data.get(key);
int cellnum = 0;
for (Object obj : objArr) {
// this line creates a cell in the next column of that row
Cell cell = row.createCell(cellnum++);
if (obj instanceof String)
cell.setCellValue((String)obj);
else if (obj instanceof Integer)
cell.setCellValue((Integer)obj);
}
}
try {
// this Writes the workbook prutorcontribute
FileOutputStream out = new FileOutputStream(new File("prutorcontribute.xlsx"));
workbook.write(out);
out.close();
System.out.println("prutorcontribute.xlsx written successfully on disk.");
}
catch (Exception e) {
e.printStackTrace();
}
}
}
Reading an excel file

Reading an excel file is also very simple if we divide this in steps.

Create workbook instance from excel sheet
Get to the desired sheet
Increment row number
iterate over all cells in a row
repeat step 3 and 4 until all data is read
// import statements
public class POIforprutorRead {
public static void main(String[] args)
{
try {
FileInputStream file = new FileInputStream(new File("prutorcontribute.xlsx"));

// Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);

// Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);

// Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
// For each row, iterate through all the columns
Iterator<Cell> cellIterator = row.cellIterator();

while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
// Check the cell type and format accordingly
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "t");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "t");
break;
}
}
System.out.println("");
}
file.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
}
result:
Output

Sometimes you need to read a file at different location: Here’s how you can do it:

private static final String FILE_NAME
= "C:UserspankajDesktopprojectOutputmobilitymodel.xlsx";
public static void write() throws IOException, InvalidFormatException
{
InputStream inp = new FileInputStream(FILE_NAME);
Workbook wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheetAt(0);
........
}
You can append to the existing file using following code:

private static final String FILE_NAME
= "C:UserspankajDesktopprojectOutputblo.xlsx";
public static void write() throws IOException, InvalidFormatException
{
InputStream inp = new FileInputStream(FILE_NAME);
Workbook wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheetAt(0);
int num = sheet.getLastRowNum();
Row row = sheet.createRow(++num);
row.createCell(0).setCellValue("xyz");
.....
..
// Now this Write the output to a file
FileOutputStream fileOut = new FileOutputStream(FILE_NAME);
wb.write(fileOut);
fileOut.close();
}