How To Read, Write Xlsx File Inward Coffee - Apach Poi Example

No thing how Microsoft is doing inwards comparing amongst Google, Microsoft Office is nevertheless the most used application inwards software world. Other alternatives similar OpenOffice in addition to LiberOffice have failed to receive got off to challenge MS Office. What this hateful to a Java application developer? Because of huge popularity of MS purpose products yous oft remove to back upwardly Microsoft purpose format such equally word, Excel, PowerPoint in addition to additionally Adobe PDF. If yous are using JSP Servlet, display tag library automatically provides Excel, Word in addition to PDF support. Since JDK doesn't render direct API to read in addition to write Microsoft Excel in addition to Word document, yous receive got to rely on 3rd political party library to attain your job. Fortunately in that place are couplet of opened upwardly origin library exists to read in addition to write Microsoft Office XLS in addition to XLSX file format, Apache POI is the best one. It is widely used, has rigid community back upwardly in addition to it is characteristic rich.

You tin notice lot of examples of how to attain with Excel using Apache POI online, which agency yous volition never experience lonely in addition to has 2d Google back upwardly if yous stuck there. In this article, nosotros volition larn how to read in addition to write excel files inwards Java. As I said, Excel files has ii pop format .XLS (produced past times Microsoft Officer version prior to 2007 e.g. MS Office 2000 in addition to 2003) in addition to .XLSX (created past times Microsoft Office 2007 onwards e.g. MS Office 2010 in addition to 2013).

Fortunately Apache POI supports both format, in addition to yous tin easily create, read, write in addition to update Excel files using this library. It uses price similar workbook, worksheet, cell, row to kicking the bucket on itself aligned amongst Microsoft Excel in addition to that's why it is rattling slowly to use. Apache POI likewise provides unlike implementation classes to grip both XLS in addition to XLSX file format.




One interesting thing amongst POI is that (I don't know whether it's intentional, accidentally or real) it has got about actually funny names for its workbook implementations e.g.

1. XSSF (XML SpreadSheet Format) – Used to reading in addition to writting Open Office XML (XLSX) format files.    
2. HSSF (Horrible SpreadSheet Format) – Use to read in addition to write Microsoft Excel (XLS) format files.
3. HWPF (Horrible Word Processor Format) – to read in addition to 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 kickoff 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 packet for decoding the Microsoft Office Drawing format.

It's rattling of import that yous know amount shape of these acronyms, otherwise it would last hard to kicking the bucket on rail of which implementation is for which format. If yous are exclusively concerned nigh reading Excel files hence at-least shout out upwardly XSSF in addition to HSSF classes e.g. XSSFWorkBook in addition to HSSFWorkBook.

How to Read Excel File (XLSX) inwards Java

In our fist illustration nosotros volition learned nigh reading electrical current pop Excel file format i.e. file amongst extension .XLSX. This is a XML spread canvass format in addition to other spreadsheet software similar OpenOffice in addition to LiberOffice likewise exercise this format. In gild to read Excel file, yous remove to kickoff download Apache POI Jar files, without these your code volition neither compiler nor execute. If yous abhor to keep JARs past times yourself, exercise Maven. In Eclipse IDE, yous tin download M2Eclipse plug-in to setup Maven project. Once yous done that, add together next dependencies inwards 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 equally Norbert pointed out, The classes for OOXML format (such equally XSSF for reading .xlsx format) are inwards a unlike Jar file. You remove to include the poi-ooxml jounce inwards your project, along amongst the dependencies for it. When yous add poi-ooxml JAR equally dependency via Maven, it volition likewise add together other required dependencies past times itself. For illustration adding below XML snippet inwards pom.xml will download iv 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 yous are non using Maven hence add together next JAR files inwards 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 await like, shout out upwardly this has saved inwards .xlsx format.

add together hither is code to read that Excel file. First ii lines are rattling common, they are to read file from file organisation inwards Java, existent code starts from 3rd line. Here nosotros are passing a binary InputStream to create instance of XSSFWorkBook class, which stand upwardly for a Excel workbook. Next describe gives us a worksheet from book, in addition to from in that place nosotros are only going through each row in addition to hence each column. Cell stand upwardly for a block inwards Excel, likewise known equally cell. This is where nosotros read or write data. Cell tin last whatever type e.g. String, numeric or boolean. Before reading value yous must ascertain right type of cell. After that only telephone phone corresponding value method e.g. getStringValue() or getNumericValue() to read information from cell. This how just yous read rows in addition to columns from Excel file inwards Java. You tin encounter nosotros receive got used ii for loop, i to iterate over all rows in addition to inner loop is to kicking the bucket 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 kickoff canvass from the XLSX workbook             XSSFSheet mySheet = myWorkBook.getSheetAt(0);                         // Get iterator to all the rows inwards electrical 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 jail cellular telephone = 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 yous receive got problem to sympathise whatever of line. They are rattling elementary in addition to self-explanatory but if yous remove additional detail, only drib us a comment.

How to write XLSX File inwards Java

Writing  into Excel file is likewise similar to reading, The workbook in addition to worksheet classes volition rest same, all yous volition attain is to create novel rows, columns in addition to cells. Once yous are done creating novel rows inwards your Excel file inwards memory, yous remove to opened upwardly an output current to write that information into your Excel File. This volition salve all update yous made inwards existing file or inwards a novel file which is created past times Java's File class. Here is measuring past times measuring code of updating an existing Excel file inwards Java. In kickoff couplet of lines nosotros are creating rows inwards shape of object array in addition to storing them equally value inwards HashMap amongst key equally row number. After that nosotros loop through HashMap in addition to insert each row at the terminate of final row, inwards other give-and-take nosotros are appending rows inwards our Excel file. Just similar earlier reading nosotros remove to create upwardly one's heed type of cell, nosotros likewise remove to attain the same thing earlier writing information into cell. This is done past times using instanceof keyword of Java. Once yous are done amongst appending all rows shape Map to Excel file, salve the file past times opening a FileOutputStream in addition to saving information into file system.
       
           // Now, let's write about information into our XLSX file

            Map<String, Object[]> information = 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 in addition to add together rows into XLS file             Set<String> newRows = data.keySet();                       // acquire the final row set out to append novel information                       int rownum = mySheet.getLastRowNum();                                for (String key : newRows) {                               // Creating a novel Row inwards existing XLSX sheet                 Row row = mySheet.createRow(rownum++);                 Object [] objArr = data.get(key);                 int cellnum = 0;                 for (Object obj : objArr) {                     Cell jail cellular telephone = 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);                     }                 }             }                       // opened upwardly an OutputStream to salve written information into XLSX file             FileOutputStream bone = 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 afterwards adding 3 to a greater extent than rows

How to read Excel (XLS) file inwards Java

Reading XLS file is no unlike than reading an XLSX format file, all yous remove to attain is to exercise right workbook implementation for XLS format e.g. instead of using XSSFWorkbook in addition to XSSFSheet , yous remove to exercise HSSFWorkbook in addition to HSSFSheet classes from Apache POI library.  As I said before, POI has got about actually funny names for unlike XLS formats e.g. Horrible SpreadSheet Format to stand upwardly for sometime Microsoft Excel file format (.xls). Remembering them tin last hard but yous tin ever refer to their online Javadoc. You tin reuse remainder of code given inwards this example, for illustration yous tin exercise same code snippet to iterate over rows, columns in addition to from reading/writing into a exceptional cell. Given they are ii unlike format, about features volition non last available on XLS file processors but all basic materials rest same.

Error in addition to Exception

If yous tumble out to exercise wrong classes e.g. instead of using XSSFWorkbook  to read XLSX file, if yous use HSSFWorkbook then you volition encounter next mistake :

Exception inwards thread "main" org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied information appears to last inwards the Office 2007+ XML. You are calling the component of POI that deals amongst OLE2 Office Documents. You remove to telephone phone a unlike component of POI to procedure this information (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 amount Java programme to read/write from existing Excel file inwards Java. If yous are using Eclipse IDE, only create a Java Project, re-create the code in addition to glue it there. No remove to create proper packet structure in addition to Java origin file amongst same name, Eclipse volition receive got aid of that. If yous receive got Maven in addition to Eclipse plugin installed, instead create a Maven Java project, this volition likewise aid yous 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 programme to read in addition to write Excel file inwards 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 mass = new XSSFWorkbook(fis);             XSSFSheet canvass = book.getSheetAt(0);              Iterator<Row> itr = sheet.iterator();              // Iterating over Excel file inwards Java             while (itr.hasNext()) {                 Row row = itr.next();                  // Iterating over each column of Excel file                 Iterator<Cell> cellIterator = row.cellIterator();                 while (cellIterator.hasNext()) {                      Cell jail cellular telephone = 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 information 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 jail cellular telephone = 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);                     }                 }             }              // opened upwardly an OutputStream to salve written information into Excel file             FileOutputStream bone = new FileOutputStream(excel);             book.write(os);             System.out.println("Writing on Excel file Finished ...");              // Close workbook, OutputStream in addition to Excel file to preclude 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 information technology Steve  2.0 Graham 80K DATA Carl  3.0 Sodhi 60K information technology Ram  4.0 Ram 100K information technology 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 nigh how to read in addition to write Excel file inwards Java. We receive got learned to read/write both XLS and XLSX format inwards Java, which is key to back upwardly sometime Microsoft Excel files created using Microsoft Office version prior to 2007. Though in that place are couplet of other choice libraries to read Excel files from Java program, but Apache POI is the best i in addition to yous should exercise it whenever possible. Let me know if yous human face upwardly whatever employment land running this programme inwards your Eclipse IDE or from ascendance prompt. Just brand for certain to include right ready of JAR inwards your CLASSPATH, alternatively used Maven to download JAR. 

Further Learning
Complete Java Masterclass
Java Fundamentals: The Java Language
Java In-Depth: Become a Complete Java Engineer!