Create HashMap from excel data in C#

Question

I am new to C# and I have an excel sheet with 2 columns and I want to create a hashmap out of the date in that sheet. let say the excel sheet looks like

 A         Value1
 A         Value2
 A         Value3
 B         value4
 B         value5

where A and B is in the first column and values in the 2nd column

I want to create a hashmap like

HashMap<String, List<String>>();

Where the String will be the key and List will be the values of that key

Any ideas please?

Thanks

Here's how to read in excel data in C# http://stackoverflow.com/questions/12996234/optimal-way-to-read-an-excel-file-xls-xlsx — Leon Husmann, Oct 18 '16 at 13:24

score 1 · Accepted Answer · edited May 23 '17 at 12:16

I am using this article as a reference. So, what you are looking for might look like this:

Check out how I use a Dictionary instead of a HashMap

//import the references needed. Checkout the article

public static void getExcelFile()
{
    //Create COM Objects. Create a COM object for everything that is referenced
    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\wearybands\test.xlsx");
    Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
    Excel.Range xlRange = xlWorksheet.UsedRange;

    int rowCount = xlRange.Rows.Count;

    //IMPORTANT SECTION
    var dictionary = new Dictionary<string, List<string>>();
    //iterate over the rows and columns as it appears in the file
    //excel is not zero based!!
    for (int i = 1; i <= rowCount; i++)
    {
        //it would be nice if we add some null checking to these variables
        //so, check the article
        var col1 = xlRange.Cells[i, 1].Value2.ToString();
        var col2 = xlRange.Cells[i, 2].Value2.ToString();

        if (dictionary.ContainsKey(col1))
        {
            var existingList = dictionary[col1];
            existingList.Add(col2);
        } 
        else{
            var newList = new List<string>();
            newList.Add(col2);
            dictionary.Add(col1, newList);
        }

    }
    //Do whatever you'd like with the dictionary
    //It now contains this data: A -> [Value1, Value2, Value3], B -> [Value4, Value5]

    //END OF IMPORTANT SECTION
    //cleanup
    GC.Collect();
    GC.WaitForPendingFinalizers();

    //rule of thumb for releasing com objects:
    //never use two dots, all COM objects must be referenced and released individually
    //ex: [somthing].[something].[something] is bad

    //release com objects to fully kill excel process from running in the background
    Marshal.ReleaseComObject(xlRange);
    Marshal.ReleaseComObject(xlWorksheet);

    //close and release
    xlWorkbook.Close();
    Marshal.ReleaseComObject(xlWorkbook);
    //quit and release
    xlApp.Quit();


    Marshal.ReleaseComObject(xlApp);
}

Note: If you are working with dates and performing calculations with those dates is important to you, I would use a DateTime instead of a string

Create HashMap from excel data in C#

1 Answers1