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();
}
}

thank you ;)

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 commented Dec 30, 2015

Thank you :)

Thank you man 👍

very helpful

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

+1 thank you !!!

thank you

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

Which version of poi you have used

Thanks man !

thanks

very helpful

thank you! 👍

Thank you

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

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

-Thanks

Can you please provide the link for jar files

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 commented Apr 10, 2017

@ShanikaEdiriweera: Please download the dependency for XMLbeans too.

tayorh27 commented May 5, 2017

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

mvictorl commented May 5, 2017

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

Thank you !!

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

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

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 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

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

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

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)

great Thanks

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 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

Many Thanks

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.

Thank you!

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)

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);
    }`

Thanks
I fond a solution :)

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