Creating excel with multiple sheets using Dictionary should support configuration / sheets

[dnlb1]

Idea: First element of the array (config) should be assign to the first sheet. - Second is for the second sheet.

For example:

The DynamicColumns in the config should be an array in an array or List in a List !!!, and assigns it to the sheets.

Imagination:

var dynamicColumns = new List<List<DynamicExcelColumn>>();//add the column to the columns
var dynamicColumn = new List<DynamicExcelColumn>(); //u can add elements

//Missing step - Adding DynamicExcelColumn to the list. (Rules)

dynamicColumns.Add(dynamicColumn);

var config = new OpenXmlConfiguration()
      {
        DynamicColumns = dynamicColumns //The first element of the list - for the 1. sheet, the second for the second sheet.
      };

//Data is from parameter
var sheets = new Dictionary<string, object>();
      foreach (var item in data)
      {
        if (sheets.ContainsKey(item.SheetName))
        {
          item.SheetName = $"{item.SheetName} ({sheets.Count(x => x.Key == item.SheetName)})";
        }
        sheets.Add(item.SheetName, item.Data);
      }

using (var writer = new MemoryStream())
      {
        MiniExcel.SaveAs(writer, sheets, configuration: config);
        return writer.ToArray();
      }

The problem with the current implementation

The current implementation works very well, but only with 1 sheet or with many sheets IF!! the different sheets dont contain same property names

• This type of configuration is contain the rule for all sheet. You can't add unique rules for different sheets. Why is it bad?

For example: I want to create an excel - I have got a User with basic properties. Name/Age--> This is my 1. export model (also my 1. sheet)

class UserExcelExportModel
{
public string Name {get; set;}
public int Age {get; set;}
}

var config = new OpenXmlConfiguration
            {
                DynamicColumns = new DynamicExcelColumn[] { 
                    new DynamicExcelColumn("Name"){Index=1,Width=10},
                    new DynamicExcelColumn("Age"){Index=2,Width=10},
                }
            };
      var users = new UserExcelExportModel[] { new UserExcelExportModel() {FullName = "2", Age = 3 } }; //Can contain more data.

Now, create the second sheet with a new model with the same property names, but we add one more property. - Gender The code should look like this:

class UserExcelExportModelWithGender
{
public string Name {get; set;}
public int Age {get; set;}
public string Gender{get; set;}
}

On the second sheet i want to place the name property to the 3. index, and the first index will be the gender.

var config = new OpenXmlConfiguration
            {
                DynamicColumns = new DynamicExcelColumn[] { 
                    new DynamicExcelColumn("Name"){Index=1,Width=10}, //first sheet config
                    new DynamicExcelColumn("Age"){Index=2,Width=10}, //first sheet config

                    new DynamicExcelColumn("Name"){Index=3,Width=10}, //Second sheet config
                    new DynamicExcelColumn("Age"){Index=2,Width=10}, //Second sheet config
                    new DynamicExcelColumn("Gender"){Index=1,Width=10}, //Second sheet config
                }
            };
      var users = new UserExcelExportModel[] { new UserExcelExportModel() {FullName = "David", Age = 3 } }; //Can contain more data.
      var usersWithGender= new UserExcelExportModelWithGender[] { new UserExcelExportModel() {FullName = "David", Age = 3 , Gender="Male"} }; 
 
      var sheets = new Dictionary<string, object>
   {
        ["users"] = users,
        ["usersWithGender"] = usersWithGender
    };

      using (var writer = new MemoryStream())
      {
        MiniExcel.SaveAs(writer, sheets, configuration: config);
      }

With this configuration we get 2 problem.

1. The first is - "Identical keys cannot be added." --> I added the "Name" and "Age" property twice. MiniExcel will throw an exception.
2. The second is - If the first problem is fine, then the Gender property will override the Name property on the first sheet. (On the first sheet we dont have gender property but miniexcel will implement an empty column.)

I hope it is understandable, this would be a great function with DynamicColumns.