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

    
    
    


27 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
  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
  7. Hi nazia , could ypu please tell me the same process to read data from CSV files and store the data

    ReplyDelete
  8. which jar files are required for above program

    ReplyDelete
  9. can we pick exact cell data with this code?

    ReplyDelete
  10. 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
  11. This comment has been removed by the author.

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

    ReplyDelete
  13. public List> toListObj(File fileName, String sheetName) {
    List> returnedData = new ArrayList<>();
    HashMap> outerMap = loadExcelLines(fileName);
    LinkedHashMap data = outerMap.get(sheetName);
    for (int i = 0; i < data.keySet().size(); i++) {
    List cells = data.get(i);
    List row = new ArrayList<>();
    for (XSSFCell cell : cells) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
    row.add("");
    break;
    case Cell.CELL_TYPE_STRING:
    row.add(cell.getStringCellValue());
    break;
    case Cell.CELL_TYPE_NUMERIC:
    row.add(cell.getNumericCellValue());
    break;
    case Cell.CELL_TYPE_FORMULA:
    row.add(cell.getNumericCellValue());
    break;
    default:
    row.add(cell.getStringCellValue());
    break;
    }
    }
    returnedData.add(row);
    }
    return returnedData;
    }

    ReplyDelete
  14. Thanks your code work well.
    But Im not able to print by index the value from linkedList.
    My code is at www.stackoverflow.com/questions/5647212

    ReplyDelete
    Replies
    1. www.stackoverflow.com/questions/56047212

      Delete
  15. Hi there,

    i looking for code to retrieve the Hashmap. Please advise me to get as below output.

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

    ReplyDelete
    Replies
    1. Hi have you got solution to this?

      Delete
  16. Hi Sir,
    great code.
    But i am trying to run it from main and getting error while returning outerMap.

    ReplyDelete
  17. Its such as you read my thoughts! You seem to understand a lot approximately this, such as you wrote the ebook in it or something. I think that you just could do with some percent to pressure the message house a little bit, however other than that, this is fantastic blog. An excellent read. I'll definitely be back.

    ReplyDelete