How to Read and Write XL Files in Java

Below Examples shows how to write and read XLSX and XLS files using Java .

package com.sun;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import org.apache.poi.openxml4j.opc.OPCPackage;
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;
/**
 * Created with IntelliJ IDEA.
 * User: javaroots
 * Date: 10/21/13
 * Time: 11:46 AM
 * To change this template use File | Settings | File Templates.
 */
public class XLSReader {
    public static void main(String[] args) throws IOException 
    {
        XSSFWorkbook workbook = new XSSFWorkbook();
     XSSFSheet sheet = workbook.createSheet("Employee Data");
        
        //This data needs to be written (Object[])
        Map data = new TreeMap();
        data.put("1", new Object[] {"ID", "NAME", "LASTNAME"});
        data.put("2", new Object[] {1, "Amit", "Shukla"});
        data.put("3", new Object[] {2, "Lokesh", "Gupta"});
        data.put("4", new Object[] {3, "John", "Adwards"});
        data.put("5", new Object[] {4, "Brian", "Schultz"});
        
        
        
        //Iterate over data and write to sheet
        Set keyset = data.keySet();
        int rownum = 0;
        for (String key : keyset)
        {
            Row row = sheet.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 Integer)
                    cell.setCellValue((Integer)obj);
            }
        }
        try
        {
           //Write the workbook in file system
            FileOutputStream out = new FileOutputStream(new File("C://xlFileMoreThan65000Test.xlsx"));
            
            workbook.write(out);
            out.close();
         
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        
        
    }
}

package com.sun;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import org.apache.poi.openxml4j.opc.OPCPackage;
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;
/**
 * Created with IntelliJ IDEA.
 * User: javaroots
 * Date: 10/21/13
 * Time: 11:46 AM
 * To change this template use File | Settings | File Templates.
 */
public class XLSReader {
    public static void main(String[] args) throws IOException 
    {
       
        try
        {
           
            long startTime = System.currentTimeMillis();
            for(int i =0 ;i<10 ;i++)
            {
             FileInputStream in = new FileInputStream(new File("C://xlFileMoreThan65000Test.xlsx"));
                
              OPCPackage pkg = OPCPackage.open("C://xlFileMoreThan65000Test.xlsx");
             
             
             
                XSSFWorkbook workbook1 = new XSSFWorkbook(pkg);
                XSSFSheet sheet1 = workbook1.getSheetAt(0);
                int newRow = sheet1.getLastRowNum();
                System.out.println("last row is : " + newRow);
                for(int j=0;j <1000 ;j++)
                {
                 newRow = newRow + 1 ;
                    
                    Row row = sheet1.createRow(newRow);
                    
                    Object [] objArr = data.get("2");
                    
                    int cellnum = 0;
                    for (Object obj : objArr)
                    {
                     Cell cell = row.createCell(cellnum++);
                        cell.setCellValue("abc");
                    }
                }
                FileOutputStream out = new FileOutputStream(new File("C://xlFileMoreThan65000Test.xlsx"));
                workbook1.write(out);
                out.close();
                in.close();
                pkg.close();
            }
            
            System.out.println("Time taken is " + (System.currentTimeMillis()-startTime));
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        
        
    }
}

Post Comments And Suggestions !!!