0

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

1

1 Answer 1

1

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

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.