Home

Read Excel through Java using XSSF and SAX (Apache POI)

Last updated on

read excel through java using xssf and saxRead 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

1
2
3
4
5
<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-

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
  • Create a ExcelReader (Javadoc) object passing excel file with worksheet handler and invoke process method

1
2
3
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

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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
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

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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
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
            }
        }
    }
}

Back to Top
Tags:, , , , , ,