Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Read / Write Excel file (.xls or .xlsx) using Apache POI
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();
}
}
@She110ck

This comment has been minimized.

Show comment Hide comment
@She110ck

She110ck Aug 13, 2015

thank you ;)

thank you ;)

@bibekkumar005

This comment has been minimized.

Show comment Hide comment
@bibekkumar005

bibekkumar005 Aug 28, 2015

please write the dependency .....having trouble when including POI jars for hssf and xssf

please write the dependency .....having trouble when including POI jars for hssf and xssf

@hermanwhyd

This comment has been minimized.

Show comment Hide comment
@hermanwhyd

hermanwhyd Sep 8, 2015

@bibekkumar005 may be this is you needed:

<!-- POI : Excel library -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>${poi.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>${poi.version}</version>
</dependency>

please write the dependency .....having trouble when including POI jars for hssf and xssf

@bibekkumar005 may be this is you needed:

<!-- POI : Excel library -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>${poi.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>${poi.version}</version>
</dependency>

please write the dependency .....having trouble when including POI jars for hssf and xssf

@vijiniL

This comment has been minimized.

Show comment Hide comment
@vijiniL

vijiniL Dec 30, 2015

Thank you :)

vijiniL commented Dec 30, 2015

Thank you :)

@willykraisler

This comment has been minimized.

Show comment Hide comment
@willykraisler

willykraisler Jan 24, 2016

Thank you man 👍

Thank you man 👍

@SantiMA10

This comment has been minimized.

Show comment Hide comment
@SantiMA10

SantiMA10 Feb 4, 2016

very helpful

very helpful

@Sourav0512

This comment has been minimized.

Show comment Hide comment
@Sourav0512

Sourav0512 Mar 15, 2016

Type mismatch: cannot convert from Iterator to Descriptor.Iterator getting this error

Type mismatch: cannot convert from Iterator to Descriptor.Iterator getting this error

@nguyendangnghiem

This comment has been minimized.

Show comment Hide comment
@nguyendangnghiem

nguyendangnghiem Mar 17, 2016

+1 thank you !!!

+1 thank you !!!

@zhvaleuskaya

This comment has been minimized.

Show comment Hide comment
@zhvaleuskaya

zhvaleuskaya Apr 18, 2016

thank you

thank you

@hendisantika

This comment has been minimized.

Show comment Hide comment
@hendisantika

hendisantika Apr 28, 2016

Thank you very much.
It helps me a lot.
It save my time.

Thank you very much.
It helps me a lot.
It save my time.

@CodeMines

This comment has been minimized.

Show comment Hide comment
@CodeMines

CodeMines May 2, 2016

Which version of poi you have used

Which version of poi you have used

@ShreeshaN

This comment has been minimized.

Show comment Hide comment
@ShreeshaN

ShreeshaN May 5, 2016

Thanks man !

Thanks man !

@ouyang111

This comment has been minimized.

Show comment Hide comment
@ouyang111

ouyang111 May 20, 2016

thanks

thanks

@ouyang111

This comment has been minimized.

Show comment Hide comment
@ouyang111

ouyang111 May 20, 2016

very helpful

very helpful

@jcgofredo

This comment has been minimized.

Show comment Hide comment
@jcgofredo

jcgofredo May 25, 2016

thank you! 👍

thank you! 👍

@pontiyaraja

This comment has been minimized.

Show comment Hide comment
@pontiyaraja

pontiyaraja May 26, 2016

Thank you

Thank you

@ShanikaEdiriweera

This comment has been minimized.

Show comment Hide comment
@ShanikaEdiriweera

ShanikaEdiriweera May 28, 2016

.xlsx reader and writer is not working.
java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlObject

.xlsx reader and writer is not working.
java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlObject

@adilearner

This comment has been minimized.

Show comment Hide comment
@adilearner

adilearner Sep 7, 2016

It was helpful......do u have any code to read xls file using Apache POI Streaming (SXSSF) for Reading??

-Thanks

It was helpful......do u have any code to read xls file using Apache POI Streaming (SXSSF) for Reading??

-Thanks

@robin2919

This comment has been minimized.

Show comment Hide comment
@robin2919

robin2919 Nov 16, 2016

Can you please provide the link for jar files

Can you please provide the link for jar files

@jalvarada

This comment has been minimized.

Show comment Hide comment
@jalvarada

jalvarada Nov 20, 2016

Does anyone why this error shows up when I run the file?

Error: Could not find or load main class excel.Excel
C:\Users\jos\AppData\Local\NetBeans\Cache\8.2\executor-snippets\run.xml:53: Java returned: 1
BUILD FAILED (total time: 0 seconds)

jalvarada commented Nov 20, 2016

Does anyone why this error shows up when I run the file?

Error: Could not find or load main class excel.Excel
C:\Users\jos\AppData\Local\NetBeans\Cache\8.2\executor-snippets\run.xml:53: Java returned: 1
BUILD FAILED (total time: 0 seconds)

@aakashsinghec90

This comment has been minimized.

Show comment Hide comment
@aakashsinghec90

aakashsinghec90 Apr 10, 2017

@ShanikaEdiriweera: Please download the dependency for XMLbeans too.

aakashsinghec90 commented Apr 10, 2017

@ShanikaEdiriweera: Please download the dependency for XMLbeans too.

@tayorh27

This comment has been minimized.

Show comment Hide comment
@tayorh27

tayorh27 May 5, 2017

Please what is the full dependency sure has this compile 'com.mcxiaoke.volley:library:1.0.17'???

tayorh27 commented May 5, 2017

Please what is the full dependency sure has this compile 'com.mcxiaoke.volley:library:1.0.17'???

@mvictorl

This comment has been minimized.

Show comment Hide comment
@mvictorl

This comment has been minimized.

Show comment Hide comment
@mvictorl

mvictorl May 5, 2017

Please, how read data if some cells are merged (by row)?

mvictorl commented May 5, 2017

Please, how read data if some cells are merged (by row)?

@sadev5264

This comment has been minimized.

Show comment Hide comment
@sadev5264

sadev5264 May 28, 2017

Thank you !!

Thank you !!

@guptaankit95

This comment has been minimized.

Show comment Hide comment
@guptaankit95

guptaankit95 Jun 7, 2017

not able to pass ExcelFileToRead into the XSSFWorkbook. showing error (remove argument to match xssfworkbook) please help

not able to pass ExcelFileToRead into the XSSFWorkbook. showing error (remove argument to match xssfworkbook) please help

@ranjithkumarrm

This comment has been minimized.

Show comment Hide comment
@ranjithkumarrm

ranjithkumarrm Jul 5, 2017

Do we need MS Excel Application to be installed in local/server ,To run the code working ??

Do we need MS Excel Application to be installed in local/server ,To run the code working ??

@12342222

This comment has been minimized.

Show comment Hide comment
@12342222

12342222 Jul 20, 2017

Hi Sir/Madam

xlsx(Excel) format file Read content issues that attaching file from Email and store into local drive, Please any help on this and that really appreciated.

Below java code now we are using

	  String contentType = message.getContentType();
										String attachFiles = "";

// String saveDirectory = (String) resources.get(SystemRqstAppConstants.WebConstants.CUSTOMERITEMVENDORPO_PATH);
String saveDirectory ="D:/ResumeFiles/";
List errorsList= null;
String messageContent = "";
logger.info(":::::Timecards Auto Update before Attchments:::::");
if (contentType.contains("multipart")) {
// content may contain attachments
String client="";
if(subject.contains("PFIZER") || subject.contains("Pfizer") || subject.contains("pfizer"))
client="Pfizer";
else if(subject.contains("CSC") || subject.contains("Csc") || subject.contains("csc"))
client="CSC";
logger.info("Timecards Auto Update client name: "+client);
Multipart multiPart = (Multipart) message.getContent();
int numberOfParts = multiPart.getCount();
for (int partCount = 0; partCount < numberOfParts; partCount++) {
MimeBodyPart part = (MimeBodyPart) multiPart.getBodyPart(partCount);
if (Part.ATTACHMENT.equalsIgnoreCase(part.getDisposition())) {
// this part is attachment
fileName = part.getFileName();
attachFiles += fileName + ", ";
saveDirectory = saveDirectory+client+"/";
File folder = new File(saveDirectory);
if(!folder.exists())
folder.mkdir();
String destFilePath = saveDirectory+fileName;
logger.info("Timecards Auto Update destFilePath: "+destFilePath);
FileOutputStream fileOutStream = new FileOutputStream(destFilePath);

							            		        byte abyte0[] = new byte[4096];
							            				int length=0; 
							            				input = part.getInputStream();
							            				DataInputStream in = new DataInputStream(input);
							            				while ((in != null) && ((length = in.read(abyte0)) != -1)){
							            					 fileOutStream.write(abyte0, 0, length);
							            			    }
							            			    input.close();
								            			in.close();

// my_xlsx_workbook.write(fileOutStream);
fileOutStream.flush();
fileOutStream.close();

Thanks in Advance
Rama

12342222 commented Jul 20, 2017

Hi Sir/Madam

xlsx(Excel) format file Read content issues that attaching file from Email and store into local drive, Please any help on this and that really appreciated.

Below java code now we are using

	  String contentType = message.getContentType();
										String attachFiles = "";

// String saveDirectory = (String) resources.get(SystemRqstAppConstants.WebConstants.CUSTOMERITEMVENDORPO_PATH);
String saveDirectory ="D:/ResumeFiles/";
List errorsList= null;
String messageContent = "";
logger.info(":::::Timecards Auto Update before Attchments:::::");
if (contentType.contains("multipart")) {
// content may contain attachments
String client="";
if(subject.contains("PFIZER") || subject.contains("Pfizer") || subject.contains("pfizer"))
client="Pfizer";
else if(subject.contains("CSC") || subject.contains("Csc") || subject.contains("csc"))
client="CSC";
logger.info("Timecards Auto Update client name: "+client);
Multipart multiPart = (Multipart) message.getContent();
int numberOfParts = multiPart.getCount();
for (int partCount = 0; partCount < numberOfParts; partCount++) {
MimeBodyPart part = (MimeBodyPart) multiPart.getBodyPart(partCount);
if (Part.ATTACHMENT.equalsIgnoreCase(part.getDisposition())) {
// this part is attachment
fileName = part.getFileName();
attachFiles += fileName + ", ";
saveDirectory = saveDirectory+client+"/";
File folder = new File(saveDirectory);
if(!folder.exists())
folder.mkdir();
String destFilePath = saveDirectory+fileName;
logger.info("Timecards Auto Update destFilePath: "+destFilePath);
FileOutputStream fileOutStream = new FileOutputStream(destFilePath);

							            		        byte abyte0[] = new byte[4096];
							            				int length=0; 
							            				input = part.getInputStream();
							            				DataInputStream in = new DataInputStream(input);
							            				while ((in != null) && ((length = in.read(abyte0)) != -1)){
							            					 fileOutStream.write(abyte0, 0, length);
							            			    }
							            			    input.close();
								            			in.close();

// my_xlsx_workbook.write(fileOutStream);
fileOutStream.flush();
fileOutStream.close();

Thanks in Advance
Rama
@prs978

This comment has been minimized.

Show comment Hide comment
@prs978

prs978 Jul 25, 2017

unable to save as the xlsx file by using the Apache POI Objects and other java logic and please help on this that really great appreciation. Regards
Rama

prs978 commented Jul 25, 2017

unable to save as the xlsx file by using the Apache POI Objects and other java logic and please help on this that really great appreciation. Regards
Rama

@amisiuryk

This comment has been minimized.

Show comment Hide comment
@amisiuryk

amisiuryk Aug 10, 2017

@prs978 To create xlsx file you have to use XSSF**** files instead of HSSF*

@prs978 To create xlsx file you have to use XSSF**** files instead of HSSF*

@ramutha

This comment has been minimized.

Show comment Hide comment
@ramutha

ramutha Sep 19, 2017

Hi.

I am facing this error while executing the above code.

Cell 0 0 Cell 0 1 Cell 0 2 Cell 0 3 Cell 0 4
Cell 1 0 Cell 1 1 Cell 1 2 Cell 1 3 Cell 1 4
Cell 2 0 Cell 2 1 Cell 2 2 Cell 2 3 Cell 2 4
Cell 3 0 Cell 3 1 Cell 3 2 Cell 3 3 Cell 3 4
Cell 4 0 Cell 4 1 Cell 4 2 Cell 4 3 Cell 4 4
Exception in thread "main" java.lang.NoClassDefFoundError: org/openxmlformats/schemas/spreadsheetml/x2006/main/CTSheet
at com.ReadWrite.Excel.ReadWriteExcelFile.writeXLSXFile(ReadWriteExcelFile.java:135)
at com.ReadWrite.Excel.ReadWriteExcelFile.main(ReadWriteExcelFile.java:165)
Caused by: java.lang.ClassNotFoundException: org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
... 2 more

ramutha commented Sep 19, 2017

Hi.

I am facing this error while executing the above code.

Cell 0 0 Cell 0 1 Cell 0 2 Cell 0 3 Cell 0 4
Cell 1 0 Cell 1 1 Cell 1 2 Cell 1 3 Cell 1 4
Cell 2 0 Cell 2 1 Cell 2 2 Cell 2 3 Cell 2 4
Cell 3 0 Cell 3 1 Cell 3 2 Cell 3 3 Cell 3 4
Cell 4 0 Cell 4 1 Cell 4 2 Cell 4 3 Cell 4 4
Exception in thread "main" java.lang.NoClassDefFoundError: org/openxmlformats/schemas/spreadsheetml/x2006/main/CTSheet
at com.ReadWrite.Excel.ReadWriteExcelFile.writeXLSXFile(ReadWriteExcelFile.java:135)
at com.ReadWrite.Excel.ReadWriteExcelFile.main(ReadWriteExcelFile.java:165)
Caused by: java.lang.ClassNotFoundException: org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
... 2 more

@arvindbakaya

This comment has been minimized.

Show comment Hide comment
@arvindbakaya

arvindbakaya Sep 19, 2017

I get the below error while running this code :
Exception in thread "main" java.lang.NoSuchFieldError: RETURN_NULL_AND_BLANK
at org.apache.poi.xssf.usermodel.XSSFWorkbook.(XSSFWorkbook.java:147)
at excelExportAndFileIO.ReadExcelFile.writeXLSXFile(ReadExcelFile.java:136)
at excelExportAndFileIO.ReadExcelFile.main(ReadExcelFile.java:166)

I get the below error while running this code :
Exception in thread "main" java.lang.NoSuchFieldError: RETURN_NULL_AND_BLANK
at org.apache.poi.xssf.usermodel.XSSFWorkbook.(XSSFWorkbook.java:147)
at excelExportAndFileIO.ReadExcelFile.writeXLSXFile(ReadExcelFile.java:136)
at excelExportAndFileIO.ReadExcelFile.main(ReadExcelFile.java:166)

@arjunpatelcs13

This comment has been minimized.

Show comment Hide comment
@arjunpatelcs13

arjunpatelcs13 Oct 5, 2017

great Thanks

great Thanks

@jenniferzoe123

This comment has been minimized.

Show comment Hide comment
@jenniferzoe123

jenniferzoe123 Oct 17, 2017

I am loading a large 5000 row excell file with extention .xlsx, it takes more time maybe more than 10 mins, how to dignose this issue, i am using your above function readXLSXFile() , Plz help.

I am loading a large 5000 row excell file with extention .xlsx, it takes more time maybe more than 10 mins, how to dignose this issue, i am using your above function readXLSXFile() , Plz help.

@vikassun

This comment has been minimized.

Show comment Hide comment
@vikassun

vikassun Nov 8, 2017

Hi Madan,
Very helpful Example. Thanks a lot.

Please help me solving one issue i am facing while incorporating your code in one of my project!!

--> Reading .xlsx file
code does work when i run this code from independent fresh dynamic web project. But it does not work when i run from my existing project!

I am working on some client Project where it was required to have this functionality of reading .xlsx file where i tried incorporating this code but it cries with ERROR!! When i placed reading .xls code it is perfectly working.

Note: a) I have used same POI jar in my independent dynamic web project and in client Project.
b) When i coped and run .xls code it does work in Client Project as well whereas .xlsx code does not

ERROR Message I get
java.lang.NoSuchFieldError: OOXML_FILE_HEADER
at org.apache.poi.poifs.filesystem.FileMagic.(FileMagic.java:40)
at org.apache.poi.openxml4j.opc.internal.ZipHelper.openZipStream(ZipHelper.java:208)
at org.apache.poi.openxml4j.opc.ZipPackage.(ZipPackage.java:98)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:324)
at org.apache.poi.util.PackageHelper.open(PackageHelper.java:37)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.(XSSFWorkbook.java:295)
at com.ibm.ericsson.pdm.webapp.action.SearchProductAction.baseExecute(SearchProductAction.java:167)
at com.ibm.ericsson.pdm.webapp.BASEAction.execute(BASEAction.java:100)
at org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:58)
at org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:67)
at org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:51)
at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
at org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:304)
at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
at org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:283)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:449)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at com.ibm.ericsson.pdm.webapp.AuthenticationFilter.doFilter(AuthenticationFilter.java:125)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:218)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:506)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:962)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1115)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)

I am using apache POI 3.17 lib..

My doubt : its a struts 1 Project , might be some where some jars are clashed

Pls help

vikassun commented Nov 8, 2017

Hi Madan,
Very helpful Example. Thanks a lot.

Please help me solving one issue i am facing while incorporating your code in one of my project!!

--> Reading .xlsx file
code does work when i run this code from independent fresh dynamic web project. But it does not work when i run from my existing project!

I am working on some client Project where it was required to have this functionality of reading .xlsx file where i tried incorporating this code but it cries with ERROR!! When i placed reading .xls code it is perfectly working.

Note: a) I have used same POI jar in my independent dynamic web project and in client Project.
b) When i coped and run .xls code it does work in Client Project as well whereas .xlsx code does not

ERROR Message I get
java.lang.NoSuchFieldError: OOXML_FILE_HEADER
at org.apache.poi.poifs.filesystem.FileMagic.(FileMagic.java:40)
at org.apache.poi.openxml4j.opc.internal.ZipHelper.openZipStream(ZipHelper.java:208)
at org.apache.poi.openxml4j.opc.ZipPackage.(ZipPackage.java:98)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:324)
at org.apache.poi.util.PackageHelper.open(PackageHelper.java:37)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.(XSSFWorkbook.java:295)
at com.ibm.ericsson.pdm.webapp.action.SearchProductAction.baseExecute(SearchProductAction.java:167)
at com.ibm.ericsson.pdm.webapp.BASEAction.execute(BASEAction.java:100)
at org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:58)
at org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:67)
at org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:51)
at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
at org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:304)
at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
at org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:283)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:449)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at com.ibm.ericsson.pdm.webapp.AuthenticationFilter.doFilter(AuthenticationFilter.java:125)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:218)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:506)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:962)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1115)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)

I am using apache POI 3.17 lib..

My doubt : its a struts 1 Project , might be some where some jars are clashed

Pls help

@chintamahesh

This comment has been minimized.

Show comment Hide comment
@chintamahesh

chintamahesh Nov 22, 2017

Many Thanks

Many Thanks

@callicoder

This comment has been minimized.

Show comment Hide comment
@callicoder

callicoder Dec 27, 2017

This is great. But It's better to use a WorkbookFactory to create a Workbook instance. This way, you won't need to use format specific classes like HSSFWorkbook and XSSFWorkbook and your program will work for both .xls and .xlsx files without writing separate methods.

private void readExcelFile(filePath) {
    Workbook workbook = WorkbookFactory.create(new File(filePath));
    Sheet sheet = workbook.getSheetAt(0);
    DataFormatter dataFormatter = new DataFormatter();
    sheet.forEach(row -> {
        row.forEach(cell -> {
            String cellValue = dataFormatter.formatCellValue(cell);
            System.out.print(cellValue + "\t");
        });
        System.out.println();
    });
}

The complete code can be found at The CalliCoder Blog.

callicoder commented Dec 27, 2017

This is great. But It's better to use a WorkbookFactory to create a Workbook instance. This way, you won't need to use format specific classes like HSSFWorkbook and XSSFWorkbook and your program will work for both .xls and .xlsx files without writing separate methods.

private void readExcelFile(filePath) {
    Workbook workbook = WorkbookFactory.create(new File(filePath));
    Sheet sheet = workbook.getSheetAt(0);
    DataFormatter dataFormatter = new DataFormatter();
    sheet.forEach(row -> {
        row.forEach(cell -> {
            String cellValue = dataFormatter.formatCellValue(cell);
            System.out.print(cellValue + "\t");
        });
        System.out.println();
    });
}

The complete code can be found at The CalliCoder Blog.

@baofengyv

This comment has been minimized.

Show comment Hide comment
@baofengyv

baofengyv Jan 31, 2018

Thank you!

Thank you!

@XxZhang2017

This comment has been minimized.

Show comment Hide comment
@XxZhang2017

XxZhang2017 Feb 3, 2018

I add the xmlbeas-2.60.jar. But I get this error. WARNING: Illegal reflective access by org.apache.poi.util.DocumentHelper (file:/Users/zhangxue/java_lib/poi-3.17/poi-ooxml-3.17.jar) to method com.sun.org.apache.xerces.internal.util.SecurityManager.setEntityExpansionLimit(int)

I add the xmlbeas-2.60.jar. But I get this error. WARNING: Illegal reflective access by org.apache.poi.util.DocumentHelper (file:/Users/zhangxue/java_lib/poi-3.17/poi-ooxml-3.17.jar) to method com.sun.org.apache.xerces.internal.util.SecurityManager.setEntityExpansionLimit(int)

@SoumiaMokrane

This comment has been minimized.

Show comment Hide comment
@SoumiaMokrane

SoumiaMokrane Feb 11, 2018

Slt;
I have a problem in my code :
Exception in thread "AWT-EventQueue-0" 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)
this is my code , please help me
` try {
//here f : is file generate from JTextField
InputStream fis = new FileInputStream(f);
HSSFWorkbook workbook= new HSSFWorkbook(fis);
HSSFSheet sheet = workbook.getSheetAt(0);
FormulaEvaluator forlulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
for (Row row : sheet) {
for (Cell cell : row) {
switch(forlulaEvaluator.evaluateInCell(cell).getCellType()){
case Cell.CELL_TYPE_NUMERIC:
System.out.println(cell.getNumericCellValue()+"\t\t");
break;
case Cell.CELL_TYPE_STRING:
System.out.println(cell.getStringCellValue()+"\t\t");
break;
}

            }
            
        }
    } catch (FileNotFoundException ex) {
        Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
    }`

Slt;
I have a problem in my code :
Exception in thread "AWT-EventQueue-0" 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)
this is my code , please help me
` try {
//here f : is file generate from JTextField
InputStream fis = new FileInputStream(f);
HSSFWorkbook workbook= new HSSFWorkbook(fis);
HSSFSheet sheet = workbook.getSheetAt(0);
FormulaEvaluator forlulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
for (Row row : sheet) {
for (Cell cell : row) {
switch(forlulaEvaluator.evaluateInCell(cell).getCellType()){
case Cell.CELL_TYPE_NUMERIC:
System.out.println(cell.getNumericCellValue()+"\t\t");
break;
case Cell.CELL_TYPE_STRING:
System.out.println(cell.getStringCellValue()+"\t\t");
break;
}

            }
            
        }
    } catch (FileNotFoundException ex) {
        Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
    }`
@SoumiaMokrane

This comment has been minimized.

Show comment Hide comment
@SoumiaMokrane

SoumiaMokrane Feb 11, 2018

Thanks
I fond a solution :)

Thanks
I fond a solution :)

@kkgouda

This comment has been minimized.

Show comment Hide comment
@kkgouda

kkgouda Mar 8, 2018

Hi,
Please share all the apache poi dependency maven.

kkgouda commented Mar 8, 2018

Hi,
Please share all the apache poi dependency maven.

@pratikbutani

This comment has been minimized.

Show comment Hide comment
@pratikbutani

pratikbutani Apr 4, 2018

Try it for Gradle Dependency in Android Studio:

implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '3.9'

pratikbutani commented Apr 4, 2018

Try it for Gradle Dependency in Android Studio:

implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '3.9'

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment