CODEWITHSUNDEEP
c#excelwinformsepplus
ejyoung
ejyoung

Reputation: 101

Creating a dynamic number of workbooks using EPPlus

I have what is essentially a database with tables owned by different groups within our larger group. I have developed an app that helps make Excel files we use to edit these tables that breaks out the tables by what group owns the table and creates an Excel workbook using EPPlus. The owners are defined in a csv file that I read in and assign to each table as I go through them. In each workbook it makes a tab for each tab that the group owns. So right now a simplified version of my code looks like:

using (ExcelPackage grp1 = new ExcelPackage())
{
    using (ExcelPackage grp2 = new ExcelPackage())
    {
        using (ExcelPackage grp3 = new ExcelPackage())
        {
            // iterate thru list of tables and owners
            foreach (string table in tablelist)
            {
                if (tableowner = group1)
                {
                    ExcelWorksheet ws = grp1.Workbook.Worksheets.Add(currenttable.Name);
                    // do stuff
                }
                if (tableowner = group2)
                {
                    ExcelWorksheet ws = grp2.Workbook.Worksheets.Add(currenttable.Name);
                    // do stuff
                }
                if (tableowner = group3)
                {
                    ExcelWorksheet ws = grp3.Workbook.Worksheets.Add(currenttable.Name);
                    // do stuff
                }        

                if (grp1.Workbook.Worksheets.Count > 0)
                {
                    FileInfo grp1fi = new FileInfo(path);
                    grp1.SaveAs(grp1fi);
                }
                if (grp2.Workbook.Worksheets.Count > 0)
                {
                    FileInfo grp2fi = new FileInfo(path);
                    grp2.SaveAs(grp2fi);
                }
                if (grp3.Workbook.Worksheets.Count > 0)
                {
                    FileInfo grp3fi = new FileInfo(path);
                    grp3.SaveAs(grp3fi);
                }    
            }
        }
    }
}

This works great as long as everything is only put into those 3 groups that I have the code in place for. But I'm running into the need to let the user define the groups to however many groups they need. So I need to be able to dynamically create the correct number of Excel files. Is this possible? I'm guessing if it is I'll have to find the number of unique groups in the csv file that I read in first. Then create the same number of lists in which to store the tables broken out by group. From there I could iterate through one of the lists and use a generic ExcelPackage that I dispose of after looping through all the tables for that group. Then move on to the second list, etc. I'm just not sure the best way to accomplish this.

Upvotes: 0

Views: 615

Answers (1)

VDWWD
VDWWD

Reputation: 35514

You can do all of this in a simple for loop right? Or am I misunderstanding the question?

int NoOfWorkBooks = 5;

for (int i = 0; i < NoOfWorkBooks; i++)
{
    using (ExcelPackage grp = new ExcelPackage())
    {
        if (tableowner == group)
        {
            ExcelWorksheet ws = grp.Workbook.Worksheets.Add(currenttable.Name);
            FileInfo grp1fi = new FileInfo(string.Format(@"c:\temp\ExcelFile_{0}.xlsx", i));
            grp.SaveAs(grp1fi);
        }
    }
}

Upvotes: 1

Related Questions