In many business application developments, there's often a need to implement Excel import and export functionality. This article provides a comprehensive guide on how to accomplish this using Apache POI, a popular Java library for working with Microsoft Office documents.
Apache POI Overview
Microsoft Excel has long been a preferred tool for data management due to its user-friendly interface and intuitive data storage capabilities. With the rise of programming languages like Java, the need for libraries that can interact with Excel files became apparent. Apache POI, originally part of the Jakarta POI project, was later open-sourced to the Apache Foundation and has become the industry stadnard for Excel manipulation in Java.
Setting Up Dependencies
To get started with Apache POI, you'll need to add the following dependencies to your project:
<dependencies>
<!-- For .xls files (Excel 2003) -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.3</version>
</dependency>
<!-- For .xlsx files (Excel 2007 and later) -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>
<!-- Date formatting utility -->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.10</version>
</dependency>
</dependencies>
Exporting Excel Files
Apache POI provides three main approaches for exporting data to Excel files, each with different characteristics:
- HSSF: For Excel 2003 (.xls) format, limited to 65,536 rows
- XSSF: For Excel 2007+ (.xlsx) format, with no row limit but higher memory usage
- SXSSF: Streaming extension of XSSF, optimized for large datasets with lower memory footprint
HSSF Export Example
public class LegacyExcelExporter {
private static final String OUTPUT_PATH = "C:/temp/";
public static void main(String[] args) throws IOException {
long startTime = System.currentTimeMillis();
// Create workbook
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("Data");
// Write data
for (int rowNum = 0; rowNum < 65536; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue("Row" + rowNum + "_Col" + cellNum);
}
}
// Save file
try (FileOutputStream out = new FileOutputStream(OUTPUT_PATH + "legacy_data.xls")) {
workbook.write(out);
}
long endTime = System.currentTimeMillis();
System.out.println("Export completed in: " + (endTime - startTime) / 1000 + " seconds");
}
}
XSSF Export Example
public class ModernExcelExporter {
private static final String OUTPUT_PATH = "C:/temp/";
public static void main(String[] args) throws IOException {
long startTime = System.currentTimeMillis();
// Create workbook
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("LargeDataset");
// Write data
for (int rowNum = 0; rowNum < 100000; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 15; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(Math.random() * 1000);
}
}
// Save file
try (FileOutputStream out = new FileOutputStream(OUTPUT_PATH + "large_dataset.xlsx")) {
workbook.write(out);
}
long endTime = System.currentTimeMillis();
System.out.println("Export completed in: " + (endTime - startTime) / 1000 + " seconds");
}
}
SXSSF Export Example
public class StreamingExcelExporter {
private static final String OUTPUT_PATH = "C:/temp/";
public static void main(String[] args) throws IOException {
long startTime = System.currentTimeMillis();
// Create streaming workbook
Workbook workbook = new SXSSFWorkbook(100); // Keep 100 rows in memory
Sheet sheet = workbook.createSheet("StreamingData");
// Write data
for (int rowNum = 0; rowNum < 500000; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 8; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue("Record_" + rowNum);
}
}
// Save file
try (FileOutputStream out = new FileOutputStream(OUTPUT_PATH + "streaming_data.xlsx")) {
workbook.write(out);
}
// Clean up temporary files
((SXSSFWorkbook) workbook).dispose();
long endTime = System.currentTimeMillis();
System.out.println("Export completed in: " + (endTime - startTime) / 1000 + " seconds");
}
}
Importing Excel Files
Similar to export functionality, Apache POI offers three approaches for importing data from Excel files, corresponding to the three export formats:
HSSF Import Example
public class LegacyExcelImporter {
private static final String FILE_PATH = "C:/temp/legacy_data.xls";
public static void main(String[] args) throws IOException {
// Open file
try (FileInputStream in = new FileInputStream(FILE_PATH)) {
Workbook workbook = new HSSFWorkbook(in);
Sheet sheet = workbook.getSheetAt(0);
// Process first row as header
Row headerRow = sheet.getRow(0);
List<String> headers = new ArrayList<>();
for (Cell cell : headerRow) {
headers.add(getCellValueAsString(cell));
}
// Process data rows
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum);
if (row != null) {
Map<String, String> rowData = new HashMap<>();
for (int cellNum = 0; cellNum < headers.size(); cellNum++) {
Cell cell = row.getCell(cellNum);
rowData.put(headers.get(cellNum), getCellValueAsString(cell));
}
// Process the row data (e.g., save to database)
System.out.println("Processing row: " + rowData);
}
}
}
}
private static String getCellValueAsString(Cell cell) {
if (cell == null) return "";
switch (cell.getCellType()) {
case STRING:
return cell.getStringCellValue();
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
return new DateTime(cell.getDateCellValue()).toString("yyyy-MM-dd");
} else {
return String.valueOf(cell.getNumericCellValue());
}
case BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());
case FORMULA:
return cell.getCellFormula();
default:
return "";
}
}
}
XSSF Import Example
public class ModernExcelImporter {
private static final String FILE_PATH = "C:/temp/large_dataset.xlsx";
public static void main(String[] args) throws IOException {
// Open file
try (FileInputStream in = new FileInputStream(FILE_PATH)) {
Workbook workbook = new XSSFWorkbook(in);
Sheet sheet = workbook.getSheet("LargeDataset");
// Process all rows
DataFormatter formatter = new DataFormatter();
for (Row row : sheet) {
List<String> rowData = new ArrayList<>();
for (Cell cell : row) {
rowData.add(formatter.formatCellValue(cell));
}
// Process the row data (e.g., save to database)
System.out.println("Processing row: " + rowData);
}
}
}
}
SXSSF Import Example
public class StreamingExcelImporter {
private static final String FILE_PATH = "C:/temp/streaming_data.xlsx";
public static void main(String[] args) throws Exception {
// Open package
OPCPackage pkg = OPCPackage.open(FILE_PATH);
XSSFReader reader = new XSSFReader(pkg);
StylesTable styles = reader.getStylesTable();
SharedStringsTable sst = new SharedStringsTable(pkg);
// Create XML reader
XMLReader xmlReader = SAXHelper.newXMLReader();
xmlReader.setContentHandler(new SheetContentsHandler(sst));
// Parse sheets
Iterator<InputStream> sheets = reader.getSheetsData();
while (sheets.hasNext()) {
try (InputStream sheetStream = sheets.next()) {
InputSource source = new InputSource(sheetStream);
xmlReader.parse(source);
}
}
}
private static class SheetContentsHandler extends DefaultHandler {
private SharedStringsTable sst;
private String cellReference;
private StringBuilder value;
private boolean isCellValue;
public SheetContentsHandler(SharedStringsTable sst) {
this.sst = sst;
}
@Override
public void startElement(String uri, String localName, String qName, Attributes attributes) {
if ("v".equals(qName)) {
isCellValue = true;
value = new StringBuilder();
} else if ("c".equals(qName)) {
cellReference = attributes.getValue("r");
}
}
@Override
public void characters(char[] ch, int start, int length) {
if (isCellValue) {
value.append(ch, start, length);
}
}
@Override
public void endElement(String uri, String localName, String qName) {
if ("v".equals(qName)) {
String cellValue = processCellValue(value.toString());
System.out.println(cellReference + ": " + cellValue);
isCellValue = false;
}
}
private String processCellValue(String value) {
if (value.isEmpty()) return "";
try {
int idx = Integer.parseInt(value);
return sst.getItemAt(idx).getString();
} catch (NumberFormatException e) {
return value;
}
}
}
}