Java : Read / Write Excel file (.xls or .xlsx) using Apache POI ~ it career solutions {for techincal stuff}

http://www.javaxp.com/2012/10/java-read-write-excel-file-xls-or-xlsx.html

Thursday, October 18, 2012

Java : Read / Write Excel file (.xls or .xlsx) using Apache POI

We will see how we can read or write excel file (.xls or .xlsx) using Apache POI. To know more about Apache POI, click here.

You can download latest version of JAR files from http://poi.apache.org/download.html.

In our case we are using Apache POI 3.8. To run below example you will need to download poi-bin-3.8-20120326.zip file from http://poi.apache.org/download.html. You will get below JAR files, add those in your claspath.

dom4j-1.6.1.jar
xmlbeans-2.3.0.jar
poi-3.8-20120326.jar
poi-ooxml-3.8-20120326.jar
poi-ooxml-schemas-3.8-20120326.jar

Please see the self explanatory Java code.

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.util.Iterator;

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;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadWriteExcelFile {

	public static void readXLSFile() throws IOException

	{

		InputStream ExcelFileToRead = new FileInputStream("C:/Test.xls");

		HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead);

		HSSFSheet sheet=wb.getSheetAt(0);

		HSSFRow row; 

		HSSFCell cell;

		Iterator rows = sheet.rowIterator();

		while (rows.hasNext())

		{

			row=(HSSFRow) rows.next();

			Iterator cells = row.cellIterator();

			while (cells.hasNext())

			{

				cell=(HSSFCell) cells.next();

				if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)

				{

					System.out.print(cell.getStringCellValue()+" ");

				}

				else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)

				{

					System.out.print(cell.getNumericCellValue()+" ");

				}

				else

				{

					//U Can Handel Boolean, Formula, Errors

				}

			}

			System.out.println();

		}

	}

	public static void writeXLSFile() throws IOException {

		String excelFileName = "C:/Test.xls";//name of excel file

		String sheetName = "Sheet1";//name of sheet

		HSSFWorkbook wb = new HSSFWorkbook();

		HSSFSheet sheet = wb.createSheet(sheetName) ;

		//iterating r number of rows

		for (int r=0;r < 5; r++ )

		{

			HSSFRow row = sheet.createRow(r);

			//iterating c number of columns

			for (int c=0;c < 5; c++ )

			{

				HSSFCell cell = row.createCell(c);

				cell.setCellValue("Cell "+r+" "+c);

			}

		}

		FileOutputStream fileOut = new FileOutputStream(excelFileName);

		//write this workbook to an Outputstream.

		wb.write(fileOut);

		fileOut.flush();

		fileOut.close();

	}

	public static void readXLSXFile() throws IOException

	{

		InputStream ExcelFileToRead = new FileInputStream("C:/Test.xlsx");

		XSSFWorkbook  wb = new XSSFWorkbook(ExcelFileToRead);

		XSSFWorkbook test = new XSSFWorkbook(); 

		XSSFSheet sheet = wb.getSheetAt(0);

		XSSFRow row; 

		XSSFCell cell;

		Iterator rows = sheet.rowIterator();

		while (rows.hasNext())

		{

			row=(XSSFRow) rows.next();

			Iterator cells = row.cellIterator();

			while (cells.hasNext())

			{

				cell=(XSSFCell) cells.next();

				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING)

				{

					System.out.print(cell.getStringCellValue()+" ");

				}

				else if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)

				{

					System.out.print(cell.getNumericCellValue()+" ");

				}

				else

				{

					//U Can Handel Boolean, Formula, Errors

				}

			}

			System.out.println();

		}

	}

	public static void writeXLSXFile() throws IOException {

		String excelFileName = "C:/Test.xlsx";//name of excel file

		String sheetName = "Sheet1";//name of sheet

		XSSFWorkbook wb = new XSSFWorkbook();

		XSSFSheet sheet = wb.createSheet(sheetName) ;

		//iterating r number of rows

		for (int r=0;r < 5; r++ )

		{

			XSSFRow row = sheet.createRow(r);

			//iterating c number of columns

			for (int c=0;c < 5; c++ )

			{

				XSSFCell cell = row.createCell(c);

				cell.setCellValue("Cell "+r+" "+c);

			}

		}

		FileOutputStream fileOut = new FileOutputStream(excelFileName);

		//write this workbook to an Outputstream.

		wb.write(fileOut);

		fileOut.flush();

		fileOut.close();

	}

	public static void main(String[] args) throws IOException {

		writeXLSFile();

		readXLSFile();

		writeXLSXFile();

		readXLSXFile();

	}

}

view rawReadWriteExcelFile.java hosted with ❤ by GitHub

Below are known errors and exceptions faced while writing above code

1. XSSFWorkbook cannot be resolved to a type

Solution : add poi-ooxml-3.8-20120326.jar in classpath. You can download from above link.

2. Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlException
at com.test.TestExcel.writeXLSXFile(TestExcel.java:135)
at com.test.TestExcel.main(TestExcel.java:165)
Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlException
at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
... 2 more

Solution: Add xmlbeans-2.3.0.jar in classpath

3. Exception in thread "main" java.lang.NoClassDefFoundError: org/dom4j/DocumentException
at org.apache.poi.openxml4j.opc.OPCPackage.init(OPCPackage.java:154)
at org.apache.poi.openxml4j.opc.OPCPackage.(OPCPackage.java:141)
at org.apache.poi.openxml4j.opc.Package.(Package.java:54)
at org.apache.poi.openxml4j.opc.ZipPackage.(ZipPackage.java:67)
at org.apache.poi.openxml4j.opc.OPCPackage.create(OPCPackage.java:293)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.newPackage(XSSFWorkbook.java:316)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.(XSSFWorkbook.java:169)
at com.test.TestExcel.writeXLSXFile(TestExcel.java:135)
at com.test.TestExcel.main(TestExcel.java:165)
Caused by: java.lang.ClassNotFoundException: org.dom4j.DocumentException
at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
... 9 more

Solution: Add dom4j-1.6.1.jar in classpath

4. Exception in thread "main" java.lang.NoClassDefFoundError: org/openxmlformats/schemas/spreadsheetml/x2006/main/CTSheet
at com.test.TestExcel.writeXLSXFile(TestExcel.java:135)
at com.test.TestExcel.main(TestExcel.java:165)
Caused by: java.lang.ClassNotFoundException: org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet
at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
... 2 more

Solution: Add poi-ooxml-schemas-3.8-20120326.jar in classpath

5. Caused by: org.apache.poi.openxml4j.exceptions.InvalidFormatException: Package should contain a content type part [M1.13]
at org.apache.poi.openxml4j.opc.ZipPackage.getPartsImpl(ZipPackage.java:148)
at org.apache.poi.openxml4j.opc.OPCPackage.getParts(OPCPackage.java:623)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:230)
at org.apache.poi.util.PackageHelper.open(PackageHelper.java:39)
... 33 more

Exception in thread "main" org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
at org.apache.poi.poifs.storage.HeaderBlock.(HeaderBlock.java:131)
at org.apache.poi.poifs.storage.HeaderBlock.(HeaderBlock.java:104)
at org.apache.poi.poifs.filesystem.POIFSFileSystem.(POIFSFileSystem.java:138)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.(HSSFWorkbook.java:327)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.(HSSFWorkbook.java:308)
at com.test.ReadWriteExcelFile.readXLSFile(ReadWriteExcelFile.java:24)
at com.test.ReadWriteExcelFile.main(ReadWriteExcelFile.java:163)

Solution :Check the excel file type, whether its xls or xlsx and call appropriate method as shown in above program

6. Exception in thread "main" java.lang.IllegalStateException: Cannot get a text value from a numeric cell
at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:845)
at org.apache.poi.xssf.usermodel.XSSFCell.getRichStringCellValue(XSSFCell.java:294)
at org.apache.poi.xssf.usermodel.XSSFCell.getStringCellValue(XSSFCell.java:246)
at com.test.ReadWriteExcelFile.readXLSXFile(ReadWriteExcelFile.java:121)
at com.test.ReadWriteExcelFile.main(ReadWriteExcelFile.java:167)

Solution: Check for cell.getCellType(). if HSSFCell.CELL_TYPE_STRING then call cell.getStringCellValue(), if HSSFCell.CELL_TYPE_NUMERIC then call cell.getNumericCellValue() and so on.

File Zip
Excel File
Metering
Packages
Solution
Links
Blog

Creative Writing
Download Latest
Database
File Zip
Excel File

17 comments:

AnonymousFebruary 17, 2013 at 11:55 AM

Excellent....
Reply
Yasira p aFebruary 23, 2013 at 10:32 AM

This comment has been removed by the author.
Reply
AnonymousMarch 5, 2013 at 7:21 AM

I go to see daily some web pages and sites to read articles or reviews, but this website offers
quality based content.

Look at my website; college term papers
Reply
AnonymousMarch 26, 2013 at 2:23 PM

very nice...
Reply
AnonymousApril 7, 2013 at 7:25 PM

Yes indeed good web content.You mean to help folks and not just put content of some page.KEEP IT UP BRO !!!
Reply
AnonymousApril 30, 2013 at 6:19 AM

Hello There. I found your weblog the use of msn. That is a really neatly written article.
I will make sure to bookmark it and return to learn extra
of your helpful info. Thanks for the post. I will certainly return.

Here is my blog; cccam
Reply
AnonymousMay 2, 2013 at 1:11 AM

Thanks for another wonderful post. Where else may just anyone get that
kind of information in such a perfect way of writing?
I've a presentation next week, and I am at the search for such information.

Feel free to surf to my web blog - latest card sharing news
Reply
AnonymousMay 2, 2013 at 1:50 PM

sir ,i think POIFSFIleSystem's object is not working beacause after

FileInputStream in=new FileInputStream(filename);
POIFSFileSystem poi=new POIFSFileSystem(in);<-after this line the cursore goes to last line on even in catch block...plz help what i'll do...
Reply
Replies
AnonymousMay 2, 2013 at 6:49 PM

It's very trouble-free to find out any matter on web as compared to books, as I found this post at this site.

Here is my webpage: cccam for free
Reply
Akhilesh ShivkumarMay 27, 2013 at 5:32 PM

After adding all those jars,, leads my pp to crash frequently,,, due to DVM 655536 error. Since Dalvic can execute only 655536 methods within one APK. Can you tell me any solution for this....please
Reply
nareshJune 19, 2013 at 11:09 PM

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/poi/hssf/u
sermodel/HSSFWorkbook
at Sample1.writeXLSFile(Sample1.java:20)
at Sample1.main(Sample1.java:48)
Caused by: java.lang.ClassNotFoundException: org.apache.poi.hssf.usermodel.HSSFW
orkbook
at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
at java.lang.ClassLoader.loadClass(ClassLoader.java:423)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
at java.lang.ClassLoader.loadClass(ClassLoader.java:356)
... 2 more error
Reply
Replies
nareshJune 19, 2013 at 11:59 PM

Please Respond To This MSG

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/poi/hssf/u
sermodel/HSSFWorkbook
at ReadWriteExcelFile.writeXLSFile(ReadWriteExcelFile.java:63)
at ReadWriteExcelFile.main(ReadWriteExcelFile.java:160)
Caused by: java.lang.ClassNotFoundException: org.apache.poi.hssf.usermodel.HSSFW
orkbook
at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
at java.lang.ClassLoader.loadClass(ClassLoader.java:423)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
at java.lang.ClassLoader.loadClass(ClassLoader.java:356)
... 2 more
Reply
bharat batraSeptember 5, 2013 at 12:56 PM

Hey, when I try to compile the code here, I get the following errors:
ReadWriteExcelFile.java:7: package org.apache.poi.hssf.usermodel does not exist
import org.apache.poi.hssf.usermodel.HSSFCell;
^
ReadWriteExcelFile.java:8: package org.apache.poi.hssf.usermodel does not exist
import org.apache.poi.hssf.usermodel.HSSFRow;
^
ReadWriteExcelFile.java:9: package org.apache.poi.hssf.usermodel does not exist
import org.apache.poi.hssf.usermodel.HSSFSheet;
^
ReadWriteExcelFile.java:10: package org.apache.poi.hssf.usermodel does not exist
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
^
ReadWriteExcelFile.java:12: package org.apache.poi.xssf.usermodel does not exist
import org.apache.poi.xssf.usermodel.XSSFCell;
^
ReadWriteExcelFile.java:13: package org.apache.poi.xssf.usermodel does not exist
import org.apache.poi.xssf.usermodel.XSSFRow;
^
ReadWriteExcelFile.java:14: package org.apache.poi.xssf.usermodel does not exist
import org.apache.poi.xssf.usermodel.XSSFSheet;

ReadWriteExcelFile.java:134: cannot find symbol
symbol : class XSSFSheet
location: class ReadWriteExcelFile
XSSFSheet sheet = wb.createSheet(sheetName) ;
^
ReadWriteExcelFile.java:139: cannot find symbol
symbol : class XSSFRow
location: class ReadWriteExcelFile
XSSFRow row = sheet.createRow(r);
^
ReadWriteExcelFile.java:144: cannot find symbol
symbol : class XSSFCell
location: class ReadWriteExcelFile
XSSFCell cell = row.createCell(c);

Any Ideas?
Reply
ArtemSivcevSeptember 12, 2013 at 9:00 PM

Добра тебе, добрый человек! Спасибо за все эти библиотеки! 2 е**ных дня у меня ушло на это,но я нашел все,что мне нужно было! Thx!!!
Reply
AnonymousNovember 8, 2013 at 1:12 PM

Awesome! That's what i was looking for! Thank you to share your knowledge ;)
Reply