Summary

Excel 2 JavaRead 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 provided 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:

<dependency>
  <groupId>com.myjeeva.poi</groupId>
  <artifactId>excelReader</artifactId>
  <version>1.2</version>
</dependency>

Need an excelReader jar file instead, click here to download.

ExcelReader project scour 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:

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("Id\tName\tHeight\tEmail Address\t\tDOB\t\tSalary");
		System.out.println("--\t----\t------\t-------------\t\t---\t\t------");
		for (PersonVO p : persons) {
			System.out.println(String.format("%s\t%s\t%s\t%s\t%s\t%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
			}
		}
	}
}

 

Tagged with →  
  • http://mymtc.midlandstech.edu Jim

    Thanks for the sample code. It only took a tweak or two (change the map values and modify the PersonVO setters and getters)to be able to read my .xlsx file. Built using NetBeans 7.2. Needed two jar files not included with Tomcat and NetBeans dom4j-1.x.jar and xmlbeans-2.x.0.jar

    • http://www.myjeeva.com Jeevanandam Madanagopal

      You’re Welcome Jim, thanks for the details of built using NetBeans.

      Cheers,
      Jeeva

      • Christopher Daniel

        Hi Jeevan,
        Your example is really awesome. But the example ExcelRowCallbackDemo.java is giving only the first sheet result

        • http://myjeeva.com/ Jeevanandam M.

          Thanks. I just noticed that. Will add support for sheet wise and all sheets processing in day or two.

          Will be available in v1.3 :)

        • http://myjeeva.com/ Jeevanandam M.

          @disqus_EA7G6Oixi3:disqus – excelReader v1.2 version is released. I have published the library to Maven Central Repo too.

  • Ashwath

    Hi Jeeva,

    Could you please let me know, what are the library files I need to use in-order to use this code snippet.

    Thanks
    Ashwath

    • http://www.myjeeva.com Jeevanandam Madanagopal

      Hello Ashwath – Code files are in GitHub repo. Please refer pom.xml

      Cheers,
      Jeeva

  • Akshay

    Hi Jeeva,

    I need read all type of excel file and then convert into CSV. Which API should i use to read both type of excel’s including the format as xls and xlsx.

    Regards,
    Akshay

    • http://www.myjeeva.com Jeevanandam Madanagopal

      Hello Akshay -

      It’s not feasible with unified code, since the file formats are fundamentally different. You’ll have to write code using the HSSF package to read XLS files and XSSF package to read XLSX.

      Cheers,
      Jeeva