Read Excel through Java using XSSF and SAX (Apache POI); ready to use Java library/classes for reading XLSX file into Java List Object or process row wise data conveniently. You may think how this article differs from plethora of articles over internet.
Of-course it has uniqueness within:
- Ready to use Java Classes for reading excel Workbook, as simple!
- Advantages of XSSF Streaming API type (aka SAX); CPU & Memory efficiency for reading
- Simply described usage & benefits via Test classes ExcelWorkSheetHandlerTest & ExcelWorkSheetRowCallbackHandlerTest
- Javadocs for ExcelReader, ExcelWorkSheetHandler<T> & ExcelXSSFRowCallbackHandler
- Excel Template Header Validation (Row Zero is header)
- Skip No. of rows
- Excel Callback handlers for in-depth processing/handling excel row values – ExcelSheetCallback and ExcelRowContentCallback
Obtaining ExcelReader Library & Source Code
For Maven based project can easily refer below pom definition:
v1.3 released to maven central on Jun 04, 2018
<dependency> <groupId>com.myjeeva.poi</groupId> <artifactId>excelReader</artifactId> <version>1.3</version> </dependency>
Need an excelReader jar file instead, click here to download.
ExcelReader project scoure code as excelReader-master.zip, click here to download OR alternative you can clone it from GitHub-
$ git clone [email protected]:jeevatkm/excelReader.git
How to Use ExcelReader
It is simple and easy to utilize and take advantage of theses classes. Will describe usage in two steps:
- Choose appropriate Excel worksheet handler for your need
- ExcelWorkSheetHandler<T> – Javadoc : Generic Excel WorkSheet handler to transfers all rows into List of Objects for given POJO mapping
- ExcelWorkSheetRowCallbackHandler – Javadoc : Generic Excel WorkSheet handler with capable of sending each row as Map Object via process method (Contributed by Doug Ayers).
- Create a ExcelReader (Javadoc) object passing excel file with worksheet handler and invoke process method
ExcelReader reader = new ExcelReader("Sample-Person-Data.xlsx", sheetRowCallbackHandler, sheetCallback);
reader.process();
Note: ExcelWorkSheetHandler implements the Interface XSSFSheetXMLHandler.SheetContentsHandler for processing the excel work sheet.
Example Code from ExcelWorkSheetHandlerTest.java
public class ExcelWorkSheetHandlerTest {
private static final Log LOG = LogFactory.getLog(ExcelWorkSheetHandlerTest.class);
/**
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
String SAMPLE_PERSON_DATA_FILE_PATH = "src/test/resources/Sample-Person-Data.xlsx";
// Input File initialize
File file = new File(SAMPLE_PERSON_DATA_FILE_PATH);
InputStream inputStream = new FileInputStream(file);
// Excel Cell Mapping
Map<String, String> cellMapping = new HashMap<String, String>();
cellMapping.put("HEADER",
"Person Id,Name,Height,Email Address,DOB,Salary");
cellMapping.put("A", "personId");
cellMapping.put("B", "name");
cellMapping.put("C", "height");
cellMapping.put("D", "emailId");
cellMapping.put("E", "dob");
cellMapping.put("F", "salary");
// The package open is instantaneous, as it should be.
OPCPackage pkg = null;
try {
ExcelWorkSheetHandler<PersonVO> workSheetHandler = new ExcelWorkSheetHandler<PersonVO>(PersonVO.class, cellMapping);
pkg = OPCPackage.open(inputStream);
ExcelSheetCallback sheetCallback = new ExcelSheetCallback() {
private int sheetNumber = 0;
@Override
public void startSheet(int sheetNum) {
this.sheetNumber = sheetNum;
System.out.println("Started processing sheet number=" + sheetNumber);
}
@Override
public void endSheet() {
System.out.println("Processing completed for sheet number=" + sheetNumber);
}
};
System.out.println("Constructor: pkg, workSheetHandler, sheetCallback");
ExcelReader example1 = new ExcelReader(pkg, workSheetHandler, sheetCallback);
example1.process();
if (workSheetHandler.getValueList().isEmpty()) {
// No data present
LOG.error("sHandler.getValueList() is empty");
} else {
LOG.info(workSheetHandler.getValueList().size()
+ " no. of records read from given excel worksheet successfully.");
// Displaying data ead from Excel file
displayPersonList(workSheetHandler.getValueList());
}
System.out.println("nConstructor: filePath, workSheetHandler, sheetCallback");
ExcelReader example2 = new ExcelReader(SAMPLE_PERSON_DATA_FILE_PATH,
workSheetHandler, sheetCallback);
example2.process();
System.out.println("nConstructor: file, workSheetHandler, sheetCallback");
ExcelReader example3 = new ExcelReader(file, workSheetHandler, null);
example3.process();
} catch (RuntimeException are) {
LOG.error(are.getMessage(), are.getCause());
} catch (InvalidFormatException ife) {
LOG.error(ife.getMessage(), ife.getCause());
} catch (IOException ioe) {
LOG.error(ioe.getMessage(), ioe.getCause());
} finally {
IOUtils.closeQuietly(inputStream);
try {
if (null != pkg) {
pkg.close();
}
} catch (IOException e) {
// just ignore IO exception
}
}
}
private static void displayPersonList(List<PersonVO> persons) {
System.out.println("IdtNametHeighttEmail AddressttDOBttSalary");
System.out.println("--t----t------t-------------tt---tt------");
for (PersonVO p : persons) {
System.out.println(String.format("%st%st%st%st%st%s",
p.getPersonId(), p.getName(), p.getHeight(),
p.getEmailId(), p.getDob(), p.getSalary()));
}
}
}
Example code from ExcelWorkSheetRowCallbackHandlerTest.java
public class ExcelWorkSheetRowCallbackHandlerTest {
private static final Log LOG = LogFactory
.getLog(ExcelWorkSheetRowCallbackHandlerTest.class);
public static void main(String[] args) throws Exception {
String SAMPLE_PERSON_DATA_FILE_PATH = "src/test/resources/Sample-Person-Data.xlsx";
File file = new File(SAMPLE_PERSON_DATA_FILE_PATH);
InputStream inputStream = new FileInputStream(file);
// The package open is instantaneous, as it should be.
OPCPackage pkg = null;
try {
ExcelWorkSheetRowCallbackHandler sheetRowCallbackHandler = new ExcelWorkSheetRowCallbackHandler(
new ExcelRowContentCallback() {
@Override
public void processRow(int rowNum, Map<String, String> map) {
// Do any custom row processing here, such as save
// to database
// Convert map values, as necessary, to dates or
// parse as currency, etc
System.out.println("rowNum=" + rowNum + ", map=" + map);
}
});
pkg = OPCPackage.open(inputStream);
ExcelSheetCallback sheetCallback = new ExcelSheetCallback() {
private int sheetNumber = 0;
@Override
public void startSheet(int sheetNum) {
this.sheetNumber = sheetNum;
System.out.println("Started processing sheet number=" + sheetNumber);
}
@Override
public void endSheet() {
System.out.println("Processing completed for sheet number=" + sheetNumber);
}
};
System.out.println("Constructor: pkg, sheetRowCallbackHandler, sheetCallback");
ExcelReader example1 = new ExcelReader(pkg,
sheetRowCallbackHandler, sheetCallback);
example1.process();
System.out.println("nConstructor: filePath, sheetRowCallbackHandler, sheetCallback");
ExcelReader example2 = new ExcelReader(SAMPLE_PERSON_DATA_FILE_PATH, sheetRowCallbackHandler, sheetCallback);
example2.process();
System.out.println("nConstructor: file, sheetRowCallbackHandler, sheetCallback");
ExcelReader example3 = new ExcelReader(file,
sheetRowCallbackHandler, null);
example3.process();
} catch (RuntimeException are) {
LOG.error(are.getMessage(), are.getCause());
} catch (InvalidFormatException ife) {
LOG.error(ife.getMessage(), ife.getCause());
} catch (IOException ioe) {
LOG.error(ioe.getMessage(), ioe.getCause());
} finally {
IOUtils.closeQuietly(inputStream);
try {
if (null != pkg) {
pkg.close();
}
} catch (IOException e) {
// just ignore IO exception
}
}
}
}