Thursday, July 20, 2023

How to Read, Write XLSX File in Java - Apache POI Example

No matter how Microsoft is doing in comparison with Google, Microsoft Office is still the most used application in software world. Other alternatives like OpenOffice and LiberOffice have failed to take off to challenge MS Office. What this mean to a Java application developer? Because of huge popularity of MS office products you often need to support Microsoft office format such as word, Excel, PowerPoint and additionally Adobe PDF. If you are using JSP Servlet, display tag library automatically provides Excel, Word and PDF support. Since JDK doesn't provide direct API to read and write Microsoft Excel and Word document, you have to rely on third party library to do your job. Fortunately there are couple of open source library exists to read and write Microsoft Office XLS and XLSX file format, Apache POI is the best one. It is widely used, has strong community support and it is feature rich.

You can find lot of examples of how to do with Excel using Apache POI online, which means you will never feel alone and has instant Google support if you stuck there. In this article, we will learn how to read and write excel files in Java

As I said, Excel files has two popular format .XLS (produced by Microsoft Officer version prior to 2007 e.g. MS Office 2000 and 2003) and .XLSX (created by Microsoft Office 2007 onwards e.g. MS Office 2010 and 2013).

Fortunately Apache POI supports both format, and you can easily create, read, write and update Excel files using this library. It uses terms like workbook, worksheet, cell, row to keep itself aligned with Microsoft Excel and that's why it is very easy to use. Apache POI also provides different implementation classes to handle both XLS and XLSX file format.

One interesting thing with POI is that (I don't know whether it's intentional, accidentally or real) it has got some really funny names for its workbook implementations like 
  1. XSSF (XML SpreadSheet Format) – Used to reading and writing Open Office XML (XLSX) format files.    
  2. HSSF (Horrible SpreadSheet Format) – Use to read and write Microsoft Excel (XLS) format files.
  3. HWPF (Horrible Word Processor Format) – to read and write Microsoft Word 97 (DOC) format files.
  4. HSMF (Horrible Stupid Mail Format) – pure Java implementation for Microsoft Outlook MSG files
  5. HDGF (Horrible DiaGram Format) – One of the first pure Java implementation for Microsoft Visio binary files.    
  6. HPSF (Horrible Property Set Format) – For reading “Document Summary” information from Microsoft Office files. 
  7. HSLF (Horrible Slide Layout Format) – a pure Java implementation for Microsoft PowerPoint files.
  8. HPBF (Horrible PuBlisher Format) – Apache's pure Java implementation for Microsoft Publisher files.
  9. DDF (Dreadful Drawing Format) – Apache POI package for decoding the Microsoft Office Drawing format.
It's very important that you know full form of these acronyms, otherwise it would be difficult to keep track of which implementation is for which format. If you are only concerned about reading Excel files then at-least remember XSSF and HSSF classes e.g. XSSFWorkBook and HSSFWorkBook.



How to Read Excel File (XLSX) in Java

In our first example, we will learn about reading the current popular Excel file format i.e. file with extension .XLSX. This is a XML spreadsheet format and other spreadsheet software like OpenOffice and LiberOffice also use this format. In order to read Excel file, you need to first download Apache POI Jar files, without these your code will neither compiler nor execute. 


If you hate to maintain JARs by yourself, use Maven. In Eclipse IDE, you can download M2Eclipse plug-in to setup Maven project. Once you done that, add the following dependencies in your pom.xml (project object model) file.
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.11-beta2</version>
    </dependency>

By the way, as Norbert pointed out, The classes for OOXML format (such as XSSF for reading .xlsx format) are in a different Jar file. You need to include the poi-ooxml jar in your project, along with the dependencies for it. When you add poi-ooxml JAR as dependency via Maven, it will also add other required dependencies by itself. For example, adding below XML snippet in pom.xml will download four JAR files
<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.11-beta2</version>
</dependency>

poi-ooxml-3.11-beta2.jar
poi-ooxml-schemas-3.11-beta2.jar
xmlbeans-2.6.0.jar
stax-api-1.0.1.jar

If you are not using Maven then add the following JAR files in your Java program's classpath
poi-3.11-beta2.jar
commons-codec-1.9.jar
poi-ooxml-3.11-beta2.jar
poi-ooxml-schemas-3.11-beta2.jar
xmlbeans-2.6.0.jar
stax-api-1.0.1.jar

Here is how our sample Excel 2013 File looks like, remember this has saved in .xlsx format.
How to read write XLS and XLSX file format in Java

add here is code to read that Excel file. The first two lines are very common, they are to read file from the file system in Java, real code starts from 3rd line. Here we are passing a binary InputStream to create an instance of XSSFWorkBook class, which represents an Excel workbook. 

The next line gives us a worksheet from the book, and from there we are just going through each row and then each column. Cell represents a block in Excel, also known as cell. This is where we read or write data. 

A cell can be any type e.g. String, numeric or boolean. Before reading the value you must ascertain the correct type of cell. After that just call corresponding value method e.g. getStringValue() or getNumericValue() to read data from cell. 

This how exactly you read rows and columns from an Excel file in Java. You can see we have used two for loop, one to iterate over all rows and the inner loop is to go through each column.
 File myFile = new File("C://temp/Employee.xlsx");
            FileInputStream fis = new FileInputStream(myFile);

            // Finds the workbook instance for XLSX file
            XSSFWorkbook myWorkBook = new XSSFWorkbook (fis);
           
            // Return first sheet from the XLSX workbook
            XSSFSheet mySheet = myWorkBook.getSheetAt(0);
           
            // Get iterator to all the rows in current sheet
            Iterator<Row> rowIterator = mySheet.iterator();
           
            // Traversing over each row of XLSX file
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();

                // For each row, iterate through each columns
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    Cell cell = cellIterator.next();

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "\t");
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "\t");
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        System.out.print(cell.getBooleanCellValue() + "\t");
                        break;
                    default :
                 
                    }
                }
                System.out.println("");
            }
Let me know if you have trouble understanding any of lines. They are very simple and self-explanatory but if you need additional detail, just drop us a comment.

How to read and write Excel file in Java



How to write XLSX File in Java

Writing into Excel file is also similar to reading, The workbook and worksheet classes will remain same, all you will do is to create new rows, columns and cells. Once you are done creating new rows in your Excel file in memory, you need to open an output stream to write that data into your Excel File.

This will save all updates you made in an existing file or in a new file which is created by Java's File class. 

Here is a step by step code of updating an existing Excel file in Java. In first couple of lines we are creating rows in form of object array and storing them as values in HashMap with key as row number. 

After that, we loop through HashMap and insert each row at the end of the last row, in other words we are appending rows in our Excel file. Just like before reading we need to determine type of cell, we also need to do the same thing before writing data into cell. 

This is done by using instanceof keyword of Java. Once you are done with appending all rows form Map to Excel file, save the file by opening a FileOutputStream and saving data into file system.
       
           // Now, let's write some data into our XLSX file
            Map<String, Object[]> data = new HashMap<String, Object[]>();
            data.put("7", new Object[] {7d, "Sonya", "75K", "SALES", "Rupert"});
            data.put("8", new Object[] {8d, "Kris", "85K", "SALES", "Rupert"});
            data.put("9", new Object[] {9d, "Dave", "90K", "SALES", "Rupert"});
         
            // Set to Iterate and add rows into XLS file
            Set<String> newRows = data.keySet();
         
            // get the last row number to append new data          
            int rownum = mySheet.getLastRowNum();         
         
            for (String key : newRows) {
             
                // Creating a new Row in existing XLSX sheet
                Row row = mySheet.createRow(rownum++);
                Object [] objArr = data.get(key);
                int cellnum = 0;
                for (Object obj : objArr) {
                    Cell cell = row.createCell(cellnum++);
                    if (obj instanceof String) {
                        cell.setCellValue((String) obj);
                    } else if (obj instanceof Boolean) {
                        cell.setCellValue((Boolean) obj);
                    } else if (obj instanceof Date) {
                        cell.setCellValue((Date) obj);
                    } else if (obj instanceof Double) {
                        cell.setCellValue((Double) obj);
                    }
                }
            }
         
            // open an OutputStream to save written data into XLSX file
            FileOutputStream os = new FileOutputStream(myFile);
            myWorkBook.write(os);
            System.out.println("Writing on XLSX file Finished ...");


Output
Writing on XLSX file Finished ...

Here is how our updated Excel file looks after adding three more rows
How to read Excel File in Java using Apache POI



How to read Excel (XLS) file in Java

Reading XLS file is no different than reading an XLSX format file, all you need to do is to use correct workbook implementation for XLS format e.g. instead of using XSSFWorkbook and XSSFSheet , you need to use HSSFWorkbook and HSSFSheet classes from Apache POI library.  As I said before, POI has got some really funny names for different XLS formats e.g. Horrible SpreadSheet Format to represent old Microsoft Excel file format (.xls). Remembering them can be hard but you can always refer to their online Javadoc. You can reuse rest of code given in this example, for example you can use same code snippet to iterate over rows, columns and from reading/writing into a particular cell. Given they are two different format, some features will not be available on XLS file processors but all basic stuff remain same.


Error and Exception

If you happen to use incorrect classes e.g. instead of using XSSFWorkbook  to read XLSX file, if you use HSSFWorkbook then you will see following error :

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.<init>(HeaderBlock.java:131)
 at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:104)
 at org.apache.poi.poifs.filesystem.POIFSFileSystem.<init>(POIFSFileSystem.java:128)
 at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:361)
 at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:342)
 at App.main(App.java:25)



Java Program to Read/Write Excel Files using Apache POI

Here is our full Java program to read/write from existing Excel files in Java. If you are using Eclipse IDE, just create a Java Project, copy the code and paste it there. No need to create proper package structure and Java source file with the same name, Eclipse will take care of that. If you have Maven and Eclipse plugins installed, instead create a Maven Java project, this will also help you to download Apache POI Jar files.

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Sample Java program to read and write Excel file in Java using Apache POI
 *
 */
public class XLSXReaderWriter {

    public static void main(String[] args) {

        try {
            File excel = new File("C://temp/Employee.xlsx");
            FileInputStream fis = new FileInputStream(excel);
            XSSFWorkbook book = new XSSFWorkbook(fis);
            XSSFSheet sheet = book.getSheetAt(0);

            Iterator<Row> itr = sheet.iterator();

            // Iterating over Excel file in Java
            while (itr.hasNext()) {
                Row row = itr.next();

                // Iterating over each column of Excel file
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    Cell cell = cellIterator.next();

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "\t");
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "\t");
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        System.out.print(cell.getBooleanCellValue() + "\t");
                        break;
                    default:

                    }
                }
                System.out.println("");
            }

            // writing data into XLSX file
            Map<String, Object[]> newData = new HashMap<String, Object[]>();
            newData.put("7", new Object[] { 7d, "Sonya", "75K", "SALES",
                    "Rupert" });
            newData.put("8", new Object[] { 8d, "Kris", "85K", "SALES",
                    "Rupert" });
            newData.put("9", new Object[] { 9d, "Dave", "90K", "SALES",
                    "Rupert" });

            Set<String> newRows = newData.keySet();
            int rownum = sheet.getLastRowNum();

            for (String key : newRows) {
                Row row = sheet.createRow(rownum++);
                Object[] objArr = newData.get(key);
                int cellnum = 0;
                for (Object obj : objArr) {
                    Cell cell = row.createCell(cellnum++);
                    if (obj instanceof String) {
                        cell.setCellValue((String) obj);
                    } else if (obj instanceof Boolean) {
                        cell.setCellValue((Boolean) obj);
                    } else if (obj instanceof Date) {
                        cell.setCellValue((Date) obj);
                    } else if (obj instanceof Double) {
                        cell.setCellValue((Double) obj);
                    }
                }
            }

            // open an OutputStream to save written data into Excel file
            FileOutputStream os = new FileOutputStream(excel);
            book.write(os);
            System.out.println("Writing on Excel file Finished ...");

            // Close workbook, OutputStream and Excel file to prevent leak
            os.close();
            book.close();
            fis.close();

        } catch (FileNotFoundException fe) {
            fe.printStackTrace();
        } catch (IOException ie) {
            ie.printStackTrace();
        }
    }
}
Output
ID NAME SALARY DEPARTMENT MANGER 
1.0 John 70K IT Steve 
2.0 Graham 80K DATA Carl 
3.0 Sodhi 60K IT Ram 
4.0 Ram 100K IT Alex 
5.0 Carl 150K DATA Alex 
7.0 Sonya 75K SALES Rupert 
9.0 Dave 90K SALES Rupert 
8.0 Kris 85K SALES Rupert 
Writing on Excel file Finished ...

That's all about how to read and write Excel files in Java. We have learned to read/write both XLS and XLSX format in Java, which is key to support old Microsoft Excel files created using the Microsoft Office version prior to 2007. Though there are couple of other alternative libraries to read Excel files from Java program, but Apache POI is the best one and you should use it whenever possible. Let me know if you face any problem while running this program in your Eclipse IDE or from command prompt. Just make sure to include right set of JAR in your CLASSPATH, alternatively used Maven to download JAR. 


64 comments:

  1. Better implementation is JSpreadsheet API

    http://www.independentsoftde/jspreadsheet

    ReplyDelete
    Replies
    1. Well, that lib doesn't have any documentation. A few examples only goes so far.

      Delete
  2. The classes for the OOXML file formats (such as XSSF for .xlsx) are in a different Jar file. You need to include the poi-ooxml jar in your project, along with the dependencies for it.


    org.apache.poi
    poi-ooxml
    3.11-beta2

    ReplyDelete
    Replies
    1. Indeed, Thanks Norbert for pointing that out. Some how I had overlooked that dependency, as Maven takes care of the itself. Corrected Now.

      Delete
    2. Yes boss you are right.
      i have downloaded by this link
      http://www.java2s.com/Code/JarDownload/poi/poi-ooxml-3.9.jar.zip
      But i am getting 1 skipped test

      Delete
    3. Downloded from
      http://www.java2s.com/Code/JarDownload/poi/poi-ooxml-3.9.jar.zip
      But getting 1test case skipped

      Delete
  3. Is there a "clean" way how can we detect whether the .xls or .xlsx file is encrypted with the password? If so, how can we pass the password before reading?

    ReplyDelete
  4. This was very useful, thanks. Well done, it saved me lots of time

    ReplyDelete
  5. (update) double execution will add 7 8 7 8 9 rows, instead of 7 8 9 7 8 9. we can do ++rownum, instead of rownum ++

    ReplyDelete
  6. This was very useful, thanks you!

    ReplyDelete
  7. How do you read color and font style using above method?

    ReplyDelete
  8. how solve
    Exception in thread "AWT-EventQueue-0" java.lang.NoClassDefFoundError: org/apache/poi/UnsupportedFileFormatException
    at java.lang.ClassLoader.defineClass1(Native Method)
    at java.lang.ClassLoader.defineClass(Unknown Source)
    at java.security.SecureClassLoader.defineClass(Unknown Source)
    at java.net.URLClassLoader.defineClass(Unknown Source)
    at java.net.URLClassLoader.access$100(Unknown Source)
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    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)
    at ReadExcel.readXLSXFile(ReadExcel.java:17)
    at HandleAction.actionPerformed(HandleAction.java:32)
    at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
    at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
    at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
    at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
    at java.awt.Component.processMouseEvent(Unknown Source)
    at javax.swing.JComponent.processMouseEvent(Unknown Source)
    at java.awt.Component.processEvent(Unknown Source)
    at java.awt.Container.processEvent(Unknown Source)
    at java.awt.Component.dispatchEventImpl(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Window.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
    at java.awt.EventQueue.access$000(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue$4.run(Unknown Source)
    at java.awt.EventQueue$4.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue.dispatchEvent(Unknown Source)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.run(Unknown Source)
    Caused by: java.lang.ClassNotFoundException: org.apache.poi.UnsupportedFileFormatException
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    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)
    ... 50 more

    ReplyDelete
    Replies
    1. @Ashok, it clearly saying that file format is not supported. Can you past the format of your file you are trying to read or write?

      Delete
  9. I Got Error Like this:
    Exception in thread "main" java.lang.NoClassDefFoundError: org/openxml4j/exceptions/OpenXML4JException
    at exel.Exel.main(Exel.java:29)
    Caused by: java.lang.ClassNotFoundException: org.openxml4j.exceptions.OpenXML4JException
    at java.net.URLClassLoader$1.run(URLClassLoader.java:372)
    at java.net.URLClassLoader$1.run(URLClassLoader.java:361)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:360)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    ... 1 more
    Java Result: 1

    ReplyDelete
    Replies
    1. This error occured mainly because you are trying to read XLSX file but you have only poi.jar in your classpath. Since XLSX is OpenXML format, you also need poi-ooxml.jar files e.g. poi-ooxml-3.11.jar. All the file related to reading XLSX file e.g. XSSFWorkBook and Even OpenXML4JException are in this JAR.

      Just put poi-ooxml-3.11.jar to solve java.lang.NoClassDefFoundError: org/openxml4j/exceptions/OpenXML4JException.

      Delete
  10. Hi:
    Execution of below code Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
    String CellData = Cell.getStringCellValue();

    I'm getting the below error Class File Editor:

    The source attachment does not contain the source for the XSSFSheet class. You can change source attachment by clicking Change Attached source below.

    Any clue why I'm getting this? I don't get this consistently, some time it works for the first 2 calls of those methods but on 3rd call, it returns error like this.

    Thanks,
    Sudhakar

    ReplyDelete
    Replies
    1. Hello Nadodi, you need to attach source for poi-ooxml-3.11.jar in your Eclipse. If you are not sure how to attach JAR to source in Eclipse, just search in this blog or Javarevisited, you will get the tutorial.

      Delete
  11. Hello Admin, How to ignore first row, which is basically header while reading XLSX file in Java? Or does POI already ignores column headers while reading?

    ReplyDelete
    Replies
    1. You can ignore fist rows by using row numbers. If your excel file has header, just ignore first row, which has number zero. Here is another example of reading/writing Excel file in Java

      Delete
  12. Just remember, when you read numeric cell value from Excel file using getNumericCellValue() it reutns a double, so if you need to store it into an integer or long variable, you need to cast this value to long.

    ReplyDelete
  13. Hello, how to make an XLSX just like as download button, so there will be a dialogue box for choose the directory location..
    thanks a lot..

    ReplyDelete
  14. I'm generating excel file(.xlsx) using apache POI but if i open it directly when ever it generated, It is missing some data,Once I clicked save and then open it is giving all data. Pls anyone help me.Thanks in Advance

    ReplyDelete
    Replies
    1. Never heard of this problem before it may be due to the sheer size of data OS would be taking some time to write it down, but its just a guess.

      Delete
    2. Apache POI defaut use a Buffer to write in the file. the data is write to your disk when the buffer is full or when you specify it whith 'close'.
      (sorry for my english)

      Delete
  15. Good example,one minor isse : resource leak -> workbook not closed

    ReplyDelete
    Replies
    1. good catch, workbook should be closed after reading or writing is finished. Can we use JDK 7 try with resource with POI XLS and XLSX workbook as well? this will ensure that workbook and worksheet will be closed automatically.

      Delete
  16. How to read a xlsx file which is password protected?

    ReplyDelete
    Replies
    1. @Sagar, you can open and read a password protected xlsx file in Java either by using jExcelAPI or by using Apache POI API, both provides encryption decryption support.

      You can use org.apache.poi.hssf.record.crypt to open password protected HSSF i.e. XLS file and org.apache.poi.poifs.crypt to open password protected XSSF or XLSX file.

      don't forget to provide password by calling org.apache.poi.hssf.record.crypto.Biff8EncryptionKey.setCurrentUserPassword(password); method.

      See here to learn more about how to deal with password protected xls and and xlsx file in Java using POI

      Delete
  17. Hi,

    while reading the excel 2007 i am getting below error:

    Exception in thread "AWT-EventQueue-0" java.lang.AbstractMethodError: javax.xml.parsers.DocumentBuilderFactory.setFeature(Ljava/lang/String;Z)V
    at org.apache.poi.util.DocumentHelper.trySetSAXFeature(DocumentHelper.java:62)
    at org.apache.poi.util.DocumentHelper.(DocumentHelper.java:56)
    at org.apache.poi.openxml4j.opc.internal.marshallers.ZipPartMarshaller.marshallRelationshipPart(ZipPartMarshaller.java:120)
    at org.apache.poi.openxml4j.opc.ZipPackage.saveImpl(ZipPackage.java:464)
    at org.apache.poi.openxml4j.opc.OPCPackage.save(OPCPackage.java:1425)
    at org.apache.poi.POIXMLDocument.write(POIXMLDocument.java:201)
    at awb.parser.ReadExcel.performReadExcel(ReadExcel.java:51)
    at awb.parser.ProVParser92Version.parse(ProVParser92Version.java:64)
    at awb.parser.AbstractParser.instantiateParser(AbstractParser.java:38)
    at awb.config.Config.loadProjectDirectory(Config.java:48)
    at awb.common.ImportModel.jButton2ActionPerformed(ImportModel.java:214)
    at awb.common.ImportModel.access$4(ImportModel.java:189)
    at awb.common.ImportModel$3.actionPerformed(ImportModel.java:84)
    at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
    at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
    at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
    at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
    at java.awt.Component.processMouseEvent(Unknown Source)
    at javax.swing.JComponent.processMouseEvent(Unknown Source)
    at java.awt.Component.processEvent(Unknown Source)
    at java.awt.Container.processEvent(Unknown Source)

    ReplyDelete
    Replies
    1. It looks you are using a feature which is not supported by Apache POI library, other than that, I can't deduce any more information from this stack trace. If you could provide some more context e.g. what you are doing, what does your XLS file contains and which version of Apache POI you are using, we can help better.

      Delete
  18. Hi,
    I need read Excel 2010. I have use library Poi 3.14 and java 1.6.0_43
    When I read Excel file java return next exception. What I still miss. Thank you.

    Exception in thread "AWT-EventQueue-0" java.lang.AbstractMethodError: org.apache.xerces.dom.DeferredElementNSImpl.getTextContent()Ljava/lang/String;
    at org.apache.poi.openxml4j.opc.internal.unmarshallers.PackagePropertiesUnmarshaller.readElement(PackagePropertiesUnmarshaller.java:146)
    at org.apache.poi.openxml4j.opc.internal.unmarshallers.PackagePropertiesUnmarshaller.loadCreated(PackagePropertiesUnmarshaller.java:162)
    at org.apache.poi.openxml4j.opc.internal.unmarshallers.PackagePropertiesUnmarshaller.unmarshall(PackagePropertiesUnmarshaller.java:124)
    at org.apache.poi.openxml4j.opc.OPCPackage.getParts(OPCPackage.java:726)
    at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:280)
    at org.apache.poi.util.PackageHelper.open(PackageHelper.java:37)
    at org.apache.poi.xssf.usermodel.XSSFWorkbook.(XSSFWorkbook.java:274)
    at com.miki.poi.imp.impl.OrderImportXLSX.(OrderImportXLSX.java:13)
    at com.miki.poi.imp.TestOrderImport.loadFile(TestOrderImport.java:97)

    ReplyDelete
  19. how we have increase the row in xlsx file because its also store 60000 row.

    ReplyDelete
  20. Hi, I am updating the .xlsx excel file and it takes lot of time at line "book.write(os);". How can we reduce the time, because it takes about 5minutes to update the excel of size 300KB.

    ReplyDelete
  21. Excelente mi pana, simplemente Genial, felicitaciones, me sirvio perfecto gracias.

    ReplyDelete
  22. what is the jar stax-api used for?

    ReplyDelete
    Replies
    1. Hello @Unknown, stax-api contains StAX parser, one of the parse for XML processing e.g. DOM and SAX.

      Delete
  23. what will be the code, if i want to fetch the data from excel
    display only Name whose salary range between 60k to 100k in eclipse.

    ReplyDelete
    Replies
    1. Hello lalita, you need to first get the value and then you need check if salary is in between 60K and 100K and then you need to print. The business logic will only be applied once you retrieve the value.

      Delete
  24. Excellent explanation. Very much useful !!

    ReplyDelete
    Replies
    1. Thanks Mukesh, glad you find my tutorials useful.

      Delete
  25. HI,
    I need advise on how to create a xlsx file using java.
    I am able to save and open .xls file but when try to with .xlsx format, the file will corrupt. Is there any other command that can help to save the file using .xlsx?
    Below are the comands that i use
    public class TestFile01 {

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

    DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHMM");
    Date today = Calendar.getInstance().getTime();
    String date= dateFormat .format(today);
    // int date1 =Integer.parseInt(date);
    BufferedWriter logWriter = new BufferedWriter (new FileWriter ("C:/temp/JAVA/" + "ApachePOITest-" + date + ".lsx" , true));


    logWriter.close();
    }

    //System.out.println("DONE");
    }

    ReplyDelete
    Replies
    1. I think you have a typo in your code, you can see the extension is ".lsx" instead of ".xls" but I suggest you to use Apache POI library for creating XLS file, as shown in this article.

      Delete
  26. how do you handle empty spaces?
    I´m reading from a xlsx. everything works fine but empty spaces.
    I tried with Cell.CELL_TYPE_BLANK, but is not working.

    any idea?

    ReplyDelete
    Replies
    1. Hello Daniel, Can you explain bit more e.g. what is the problem and what do you want?
      Cell cell = row.getCell(cn, Row.CREATE_NULL_AS_BLANK);

      AFAIK, Apache POI RowIterator and CellIterator does not support iterating over NULL cells or rows but you can iterate over physically defined cells (which can be BLANK). If the cell is missing from the file and you want to generate blank cell then you can use the Row.getCell([int], Row.CREATE_NULL_AS_BLANK), which will give you blank cell. Alternatively, you can use the Cell.columnIndex value while iterating to check for jumping numbers. You also need to specify MissingCellPolicy for this to get it work.

      But, as I said, I don't if this is your requirement. Please share more details.

      Delete
  27. Hello i have written the first code above and it is genereting error below:
    Exception in thread "main" java.io.FileNotFoundException: reportform.xlsx (The system cannot find the file specified)
    at java.io.FileInputStream.open(Native Method)
    at java.io.FileInputStream.(FileInputStream.java:138)
    at read.read1.main(read1.java:13)
    Java Result: 1
    what could be the problem.

    ReplyDelete
    Replies
    1. Hello @Unknown, Java is not able to find the file "reportform.xlsx", which means either file name is incorrect or path of the file is not correct. please give full path to the file.

      Delete
  28. Hey folks, this code gave me a lot of intro to understanding how to access Excel data.
    But right now, I need to know, how i can identify positive and negative value in 200+ rows and 3 columns, and the matching numbers (for eg: A2=54 and B6= -54) should be made zero..
    Can anyone of you guide me to the code?

    ReplyDelete
  29. InputStream ExcelFileToRead = new FileInputStream("C:/Test.xlsx");
    XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);

    error in second line not able to pass ExcelFileToRead object into the xssfworkbook . please suggest something (eclipse hint cant take file argument in the xssfworkbook)

    ReplyDelete
    Replies
    1. Hi, did you figure out how to fix this issue because I am having the same problem. thanks

      Delete
  30. 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

    ReplyDelete
  31. Thank you, this very helpful!

    ReplyDelete
  32. hi why the happen like this?

    Exception in thread "main" java.lang.Error: Unresolved compilation problems:
    CELL_TYPE_STRING cannot be resolved or is not a field
    CELL_TYPE_NUMERIC cannot be resolved or is not a field
    CELL_TYPE_BOOLEAN cannot be resolved or is not a field

    at testReadExcel.TestReadExcel.main(TestReadExcel.java:41)

    ReplyDelete
  33. Hi I wrote the same code, the excel sheet is getting downloaded in the file path which I provided directly.However, I want to add dialogue box which will ask the user to either open the file or save the file. For that I used this lines of code:

    response.setHeader("Content-Type", "application/csv;charset=utf-8," );
    response.setHeader("Content-Transfer-Encoding", "binary; charset=utf-8,");
    response.addHeader("Content-Disposition", String.format("attachment; filename=tracker.csv"));

    dialogue box is coming but the excel sheet is empty.
    Any solution for this?

    Thankyou

    ReplyDelete
  34. Can we detect cell formatting like background color with Apache POI?

    ReplyDelete
  35. If you're concerned about the size of your Excel files or just require faster performance, then I would suggest trying GemBox.Spreadsheet for Java:


    com.gembox
    gembox-spreadsheet
    1.1.1154

    ReplyDelete
  36. Hi, I am getting the below error.
    FAILED: ExcelHandling
    org.apache.poi.EmptyFileException: The supplied file was empty (zero bytes long).

    Please find my code below:
    @Test
    public void ExcelHandling() throws Exception {

    // Navigate to URL
    driver.get(config.getProperty("TestSiteURL"));


    // Create the file object for Writable Excel
    File file = new File(System.getProperty("user.dir")+"\\src\\main\\java\\com\\Banking\\TestData\\Selenium_Test_Data.xlsx");

    // We have to use FileOutPutStream object for writing the data into excel
    FileOutputStream fos = new FileOutputStream(file);

    // Access the excel file
    FileInputStream fis2 = new FileInputStream(System.getProperty("user.dir")+"\\src\\main\\java\\com\\Banking\\TestData\\Selenium_Test_Data.xlsx");

    // Get the worksheet from excel
    XSSFWorkbook workbook = new XSSFWorkbook(fis2);

    // Get the worksheet from workbook
    XSSFSheet worksheet = workbook.getSheet("MultipleLoginFunctionality");

    // We have to perform same action multiple times, then we use for loop
    for(int i=1;i<worksheet.getLastRowNum()+1;i++) {


    //Read the username from excel sheet and store into userID variable

    String userID=worksheet.getRow(i).getCell(1).getStringCellValue();

    //Read the password from excel sheet and store into password variable

    String password=worksheet.getRow(i).getCell(2).getStringCellValue();


    getObject("lgn_usrid").clear();
    getObject("lgn_usrid").sendKeys(userID);

    getObject("lgn_pwd").clear();
    getObject("lgn_pwd").sendKeys(password);

    getObject("lgn_btn").click();
    Thread.sleep(5000);

    try {
    String atxt = getObject("lgn_scs").getText();

    if(atxt.equals("Dashboard")){
    System.out.println("Login successful");
    worksheet.getRow(i).createCell(3).setCellValue("Pass");
    getObject("logout").click();
    }
    }

    catch(Exception e) {
    System.out.println("Invalid Credentials");
    Thread.sleep(5000);
    worksheet.getRow(i).createCell(3).setCellValue("Fail");
    takeScreenshot(userID);
    }

    }
    workbook.write(fos);
    fos.close();
    }
    }

    ReplyDelete
  37. i want to access xlsx to xls in java plzz give me some solution?

    ReplyDelete
  38. HOw can i write the data from Column 4. COuld you please tell me.

    ReplyDelete
    Replies
    1. try iterating over until 4th column then writing

      Delete
  39. i m getting the error 500 while i m heating the url it shows that .xlsx file(the system cannot find the file specified)

    ReplyDelete
  40. How to store the data in a variable that is retrieved from excel

    ReplyDelete
  41. You have the audacity to criticize Microsoft when you present a Java solution that will literally take days to wade through a hierarchy of library dependencies and get it all working. Your solution amounts to horseshit. At least Microsoft solutions are productive and not a pile if iffy crap!

    ReplyDelete

Feel free to comment, ask questions if you have any doubt.