Read, Write Excel With Java – POI

Apache POI project consists of APIs for manipulating various file formats such as Open Office XML (ECMA-376) and Microsoft’s OLE 2 Compound Document formats using pure Java. It provides pure Java libraries for reading and writing files in Microsoft Office formats, such as Word, PowerPoint and Excel. POI supports the ISO/IEC 29500:2008 Office Open XML file formats since version 3.5.

Apache-POI’s XSSF component can read .xlsx files. JExcel only supports the older .xls file format.

Apache-POI also offers an event based API which can be used to drastically reduce the memory footprint when reading but using it is more complicated than just reading the file directly.

Write Excel With Java – POI


import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;

public class PoiWriteExcelFile {

public static void main(String[] args) {
try {
FileOutputStream fileOut = new FileOutputStream("test.xls");
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet worksheet = workbook.createSheet("Worksheet");

HSSFRow row = worksheet.createRow((short) 0);

HSSFCell cellA1 = row.createCell(0);
cellA1.setCellValue("Hello");
HSSFCellStyle styleOfCell = workbook.createCellStyle();
styleOfCell.setFillForegroundColor(HSSFColor.AQUA.index);
styleOfCell.setFillPattern(HSSFCellStyle.BORDER_THIN);
cellA1.setCellStyle(styleOfCell);

HSSFCell cellB1 = row.createCell(1);
cellB1.setCellValue("World");
styleOfCell = workbook.createCellStyle();
styleOfCell.setFillForegroundColor(HSSFColor.AQUA.index);
styleOfCell.setFillPattern(HSSFCellStyle.BORDER_THIN);
cellB1.setCellStyle(styleOfCell);

HSSFCell cellC1 = row.createCell(2);
cellC1.setCellValue("Happy");
styleOfCell = workbook.createCellStyle();
styleOfCell.setFillForegroundColor(HSSFColor.AQUA.index);
styleOfCell.setFillPattern(HSSFCellStyle.BORDER_THIN);
cellC1.setCellStyle(styleOfCell);

HSSFCell cellD1 = row.createCell(3);
cellD1.setCellValue(new Date());
styleOfCell = workbook.createCellStyle();
styleOfCell.setDataFormat(HSSFDataFormat
.getBuiltinFormat("m/d/yy h:mm"));
styleOfCell.setFillForegroundColor(HSSFColor.AQUA.index);
styleOfCell.setFillPattern(HSSFCellStyle.BORDER_THIN);
cellD1.setCellStyle(styleOfCell);

row = worksheet.createRow(1);
row.createCell(0).setCellValue(Calendar.getInstance().getTime().toString());
row.createCell(1).setCellValue("a string");
row.createCell(2).setCellValue(true);
row.createCell(3).setCellType(Cell.CELL_TYPE_ERROR);

workbook.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}

}

}

Read Excel With Java – POI


import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class PoiReadExcelFile {
public static void main(String[] args) {
try {
FileInputStream iStream = new FileInputStream("test.xls");
HSSFWorkbook workbook = new HSSFWorkbook(iStream);
HSSFSheet worksheet = workbook.getSheet("Worksheet");
//get first row
HSSFRow row = worksheet.getRow(0);
HSSFCell cellA1 = row.getCell(0);
System.out.println("A1 "+cellA1.getStringCellValue());
HSSFCell cellB1 = row.getCell(1);
System.out.println("B1 "+cellB1.getStringCellValue());
HSSFCell cellC1 = row.getCell(2);
System.out.println("C1 "+cellC1.getStringCellValue());
HSSFCell cellD1 = row.getCell(3);
System.out.println("D1 "+cellD1.getDateCellValue());
//get next row
row = worksheet.getRow(1);
HSSFCell cellA2 = row.getCell(0);
System.out.println("A2 "+cellA2.getStringCellValue());
HSSFCell cellB2 = row.getCell(1);
System.out.println("B2 "+cellB2.getStringCellValue());
HSSFCell cellC2 = row.getCell(2);
System.out.println("C2 "+cellC2.getBooleanCellValue());
HSSFCell cellC3 = row.getCell(3);
System.out.println("D2 "+cellC3.getErrorCellValue());

} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}

OUTPUT :

A1 Hello
B1 World
C1 Happy
D1 Sat Dec 31 08:55:00 IST 2011
A2 Sat Dec 31 08:55:00 IST 2011
B2 a string
C2 true
D2 15

Hope you will like this. Cheers… 😉