how to create Dictionary for excel in c#

Question

I have excel like this :

ProductID	SomeExplanation	AnotherColumn1	AnotherColumn2	AnotherColumn3
1	X	6	A	65465
2	Y	5	B	6556
3	Z	7	C	65465

I want to create Dictionary that key values(which are ProductID, SomeExplanation,AnotherColumn1,AnotherColumn2, AnotherColumn3) and this dictionary must have List of values (for example dictionary key : ProductId and it's values : 1,2,3 etc..) and I think there must be List that containes all dictionaries.

I am using aspose library for excel and .net framework 4.5 .

Aspose returning the it's cell values as an object.

So my first question how can create List of dictionaries, and these dictionaries must have list of values (List<Dictionary<key,List of values>>) and how to add values to this List of dictionary ?

My second question with that : how can I fill this list of dictionaries with aspose worksheet ?

Answer 1

This is method that accept Aspose Worksheet as a parameter this worksheet parameter can be one of the excel files' worksheet.

I want to iterate through all cell and assign values to dictionary, and this values belong to its header(0 row and columnOrder)

For example: there is a list called myExcelContainer and this list is a series of Excel columns and also this columns is an dictionary that contains key of value (Excel header - for example ProductId) and the values [1, 2, 3] under the Excel header.

 public List<Dictionary<string, List<object>>> GenerateExcelDictionary(Worksheet worksheet)
        {
            var columnMax = worksheet.Cells.MaxDataColumn;
            var rowMax = worksheet.Cells.MaxDataRow;
            var myExcelContainer = new List<Dictionary<string, List<object>>>();
            var columnKeyWithValues = new Dictionary<string, List<object>>();
            for (int column = 0; column < columnMax; column++)
            {
                var columnName = worksheet.Cells[0, column].Value.ToString().Replace(" ", string.Empty);
                columnKeyWithValues.Add(columnName, new List<object>());
            }

            for (int column = 0; column < columnMax; column++)
            {
                var values = new List<object>();
                for (int row = 1;row < rowMax;row++)
                {
                    values.Add(worksheet.Cells[row, column]);
                }
                columnKeyWithValues[worksheet.Cells[0, column].Value.ToString()] = values;
            }
            myExcelContainer.Add(columnKeyWithValues);
            return myExcelContainer;
        }

this is the excel container :

var myExcelContainer = new List<Dictionary<string, List<object>>>();

But If you can improve the algortihm performance I want you to share, please.

My english not great :) .