Thursday, October 8, 2015

Reading the Excel file and Storing data in HashMap

Excel data is look like as below and having Multiple sheets-


UserState UserID LoginID Secure/Unsecured  InBoundTo InboundFrom
Avaliable,  1000654789U258963147852
UnAvaliable 1001 123456 S 147852 258963




import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;

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

public class ExcelReader
{

    public static HashMap loadExcelLines(File fileName)
    {
        // Used the LinkedHashMap and LikedList to maintain the order
        HashMap<String, LinkedHashMap<Integer, List>> outerMap = new LinkedHashMap<String, LinkedHashMap<Integer, List>>();

        LinkedHashMap<Integer, List> hashMap = new LinkedHashMap<Integer, List>();

        String sheetName = null;
        // Create an ArrayList to store the data read from excel sheet.
        // List sheetData = new ArrayList();
        FileInputStream fis = null;
        try
        {
            fis = new FileInputStream(fileName);
            // Create an excel workbook from the file system
            XSSFWorkbook workBook = new XSSFWorkbook(fis);
            // Get the first sheet on the workbook.
            for (int i = 0; i < workBook.getNumberOfSheets(); i++)
            {
                XSSFSheet sheet = workBook.getSheetAt(i);
                // XSSFSheet sheet = workBook.getSheetAt(0);
                sheetName = workBook.getSheetName(i);

                Iterator rows = sheet.rowIterator();
                while (rows.hasNext())
                {
                    XSSFRow row = (XSSFRow) rows.next();
                    Iterator cells = row.cellIterator();

                    List data = new LinkedList();
                    while (cells.hasNext())
                    {
                        XSSFCell cell = (XSSFCell) cells.next();
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        data.add(cell);
                    }
                    hashMap.put(row.getRowNum(), data);

                    // sheetData.add(data);
                }
                outerMap.put(sheetName, hashMap);
                hashMap = new LinkedHashMap<Integer, List>();
            }

        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
        finally
        {
            if (fis != null)
            {
                try
                {
                    fis.close();
                }
                catch (IOException e)
                {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }

        return outerMap;

    }
}



OuterMap will return the data of all your sheets in HashMap

as below-

HashMap of HashMap
Sheet1 as Key and its corresponding data as hashmap with row no as key and corresponding data in list

{Sheet1={
    0=[UserState, UserID, LoginID, Secure/Unsecured, InBoundTo, InboundFrom], 
    1=[Avaliable, 1000, 654789, U, 258963, 147852, ], 
    2=[UnAvaliable, 1000, 123456, S, 147852, 258963, ], 3=[], 4=[], 5=[]}, 
    
  Sheet2 as Key and its corresponding data as hashmap with row no as key and corresponding data in list  
Sheet2={
    0=[UserID, Password, Extention, ACDID, Env, Life_Cycle, Web_URL], 
    1=[XYZ, xyz, 71000, 66001, Cisco, DEV, https://www.facebook.com/, ], 
    2=[ABC, abc, 71000, 66001, Cisco, DEV, https://www.facebook.com/, ], 3=[, , ], 7=[, ]}, 
    
 Sheet3 as Key and its corresponding data as hashmap with row no as key and corresponding data in list   
Sheet3={
    0=[Outbound, State, ClientID, PPID, SecurityState], 
    1=[7777777, Avaliable, 0000, 8888, U, ], 
    2=[777777, Avaliable, 0000, 8888, S, ], 3=[, ]}, 
    
    

    
    
    


11 comments:

  1. Hi, I get the whole array. However, what if I only want to get the Column name as key and the corresponding row value as value.
    example:
    UserID=1000

    ReplyDelete
    Replies
    1. hey have u got the solution for this problem?

      Delete
  2. Can you please tell us how to pass the filepath or filename in the above program

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. hello @unknown , use can follow the below procedure after the function loadExcelLines.(Note:change filepath accordingly)

    public static void main(String args[])
    {
    HashMap> mymap=loadExcelLines("F:\\java tutorial\\excelparse\\Sample.xlsx");
    System.out.println("Reading excel file"+mymap);
    System.out.println("Excel parsing done***");
    }

    also change the return type of the loadExcelLines from File to String.

    let me know if it works.

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. i am unable to publish the type of Hashmap which String, LinkedHashMap. so add it accordingly.

    ReplyDelete