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=[, ]}, 
    
    

    
    
    


12 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
    Replies
    1. File file = new File(filepath);

      Delete
  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