How to Read, Write XLSX File in Java – Apach POI Example—reference

• Java

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 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 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 e.g.

 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;
                <span id="IL_AD2" class="IL_AD"&gt;default :

                }
            }
            System.out.println("");
        }</span></pre>






  



  



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 value in HashMap with key as row number. After that we  and insert each row at the end of last row,in other word 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
     
     
      data = new HashMap
     
     
     
      
      
      ();
            data.put("7",new Object[] {7d,"Sonya","75K","SALES","Rupert"});
            data.put("8",new Object[] {8d,"Kris","85K","Rupert"});
            data.put("9",new Object[] {9d,"Dave","90K","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);
        Sy<a href="https://www.jb51.cc/tag/stem/" target="_blank" class="keywords">stem</a>.out.println("Writing on XLSX file Finished ...");


Output

Writing on XLSX file Finished ...


     


    



















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








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












      




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

     // Ite<a href="https://www.jb51.cc/tag/rating/" target="_blank" class="keywords">rating</a> over Excel file in Java
     while (itr.hasNext()) {
         Row row = itr.next();

         // Ite<a href="https://www.jb51.cc/tag/rating/" target="_blank" class="keywords">rating</a> 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:
                 Sy<a href="https://www.jb51.cc/tag/stem/" target="_blank" class="keywords">stem</a>.out.print(cell.getStringCellValue() + "\t");
                 break;
             case Cell.CELL_TYPE_NUMERIC:
                 Sy<a href="https://www.jb51.cc/tag/stem/" target="_blank" class="keywords">stem</a>.out.print(cell.getNumericCellValue() + "\t");
                 break;
             case Cell.CELL_TYPE_BOOLEAN:
                 Sy<a href="https://www.jb51.cc/tag/stem/" target="_blank" class="keywords">stem</a>.out.print(cell.getBooleanCellValue() + "\t");
                 break;
             default:

             }
         }
         Sy<a href="https://www.jb51.cc/tag/stem/" target="_blank" class="keywords">stem</a>.out.println("");
     }

     // writing data into XLSX file
     Map<String,Object[]> newData = new HashMap<String,Object[]>();
     newData.put("7",new Object[] { 7d,"Rupert" });
     newData.put("8",new Object[] { 8d,"Rupert" });
     newData.put("9",new Object[] { 9d,"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);
     Sy<a href="https://www.jb51.cc/tag/stem/" target="_blank" class="keywords">stem</a>.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 file 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 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.




    reference from:http://java67.blogspot.jp/2014/09/how-to-read-write-xlsx-file-in-java-apache-poi-example.html




   


   


总结


以上是编程之家为你收集整理的How to Read, Write XLSX File in Java - Apach POI Example---reference全部内容，希望文章能够帮你解决How to Read, Write XLSX File in Java - Apach POI Example---reference所遇到的程序开发问题。


如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。




    本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。




    















     


     






     


     




  






    

  • 上一篇：JDK动态代理实现简单AOP--转下一篇：forward 和redirect

    •
The content of this article comes from the network collection of netizens. It is used as a learning reference. The copyright belongs to the original author.
THE END
分享
二维码
< <上一篇
下一篇>>