Wednesday 19 July 2017

Textual Representation of logo

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