Create new worksheets based on row data values

Question

I have a data set that I want to split into specific worksheets based on row values and export via my C# web app.

Data columns (example): SerialNumber, ProductType, Location, Date

I need to loop through the record set sorted by column Location and then detect when that Location value changes so I can create a new worksheet and put data for each distinct Location in separate tabs/worksheets.

I have been using the LoadFromDataTable method to pop data from my entire dataset into one worksheet but not sure this is the right method for an iteration

What would I use while looping through my dataset so I can write data into the first sheet while evaluating the row value of the "location" field and then dynamically trigger new tabs/sheets as needed?

I am a new to C# and am teaching myself (basically starting the rebuild of my app with this one task of multi-tab excel exports) I am familiar with this type of looping in vb script in my classic .asp app.

Can anyone point me to an example of something like this in C# with dynamically spawned worksheets based on row values?

Answer 1

I used CopytoDataTable to splt the data sets and create new worksheets

            var uniqueList = dt.AsEnumerable().Select(x => x.Field<string>("ProdType")).Distinct();
            List<string> myList = new List<string>();
            myList = uniqueList.ToList();

            DataTable[] array = new DataTable[myList.Count()];
            int index = 0;
            foreach (string item in myList)
            {
                var Result = from x in dt.AsEnumerable()
                             where x.Field<string>("ProdType") == item
                             select x;
                DataTable table = Result.CopyToDataTable();
                array[index] = table;

                ExcelWorksheet ws = pck.Workbook.Worksheets.Add(item);
                ws.Cells["A1"].LoadFromDataTable(table, true);
                index++;
            }