How to create a list of DataTables from a DataTable based on Key?

Question

I have an Excel data which looks like below. Using ClosedXML, I've read the data into DataTable. Now again I need to store records into separate DataTable based on CID value. i.e., Records which are having same CID value should be stored into separate DataTable. And all those DataTable needs to be added to a list of DataTable.

Name	CID	ComapanyName	CCode	Address	Remarks
Anto	12	ABC Corp Cmp	ABCCo	AvenueSt
Anuj	13	XYZ Corp Cmp	XYZCo	AvenueSt	Fail
Kathy	12	ABC Corp Cmp	ABCCo	AvenueSt	Fail
Frank	12	ABC Corp Cmp	ABCCo	AvenueSt	Fail
Henry	13	XYZ Corp Cmp	XYZCo	AvenueSt	Fail
Chris	14	CTF Corp Cmp	CTFCo	AvenueSt	Fail

After logic it should look like below.

Table 1:

Name	CID	ComapanyName	CCode	Address	Remarks
Anto	12	ABC Corp Cmp	ABCCo	AvenueSt
Kathy	12	ABC Corp Cmp	ABCCo	AvenueSt	Fail
Frank	12	ABC Corp Cmp	ABCCo	AvenueSt	Fail

Table 2:

Name	CID	ComapanyName	CCode	Address	Remarks
Anuj	13	XYZ Corp Cmp	XYZCo	AvenueSt	Fail

Table 3:

Name	CID	ComapanyName	CCode	Address	Remarks
Chris	14	CTF Corp Cmp	CTFCo	AvenueSt	Fail

ClosedXML code to read Excel data into DataTable:

private static System.Data.DataTable ReadExcelData(string filePath)
        {
            try
            {
                System.Data.DataTable dataTable = new System.Data.DataTable();
                using (Stream stream = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
                {
                    using (XLWorkbook workBook = new XLWorkbook(stream))
                    {

                        var workSheet = workBook.Worksheet(1);
                        dataTable.TableName = workSheet.Name;

                        int lastRowIndex = workSheet.LastRowUsed().RowNumber();
                        int lastColumnIndex = workSheet.LastColumnUsed().ColumnNumber();
                        bool header = false;

                        foreach (IXLRow row in workSheet.Rows(1, lastRowIndex))
                        {
                            if (!header)
                            {
                                foreach (IXLCell cell in row.Cells(1, lastColumnIndex))
                                {
                                    dataTable.Columns.Add(cell.GetFormattedString());
                                }
                                header = true;
                            }
                            dataTable.Rows.Add();
                            int i = 0;
                            foreach (IXLCell cell in row.Cells(1, lastColumnIndex))
                            {
                                dataTable.Rows[dataTable.Rows.Count - 1][i] = cell.GetFormattedString();
                                i++;
                            }
                        }
                        dataTable.Rows.RemoveAt(0);
                    }
                }

                return dataTable;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

Answer 1

Following is the approach I suggest to create all the tables in one single loop and return list of those table. You don't need to create a big datatable with all the rows in it.

The approach is to maintain a dictionary of DataTable where key is the value from CID column.

For every row, check if the dictionary has the current CID value as key. If not, then create a new datatable with name, add columns to it and add values from the current of excel row and add that table to the dictionary with current CID as key. If the dictionary contains the current CID value as key, add values from the current row to that table.

private static List<System.Data.DataTable> ReadExcelData(string filePath)
    {
        // Table dictionary
        var tableDict = new Dictionary<string, DataTable>();
        try
        {
            using (Stream stream = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
            {
                using (XLWorkbook workBook = new XLWorkbook(stream))
                {
                    var workSheet = workBook.Worksheet(1);

                    int lastRowIndex = workSheet.LastRowUsed().RowNumber();
                    int lastColumnIndex = workSheet.LastColumnUsed().ColumnNumber();
                    bool header = false;

                    // List of column names.
                    // This will be used for creating new tables with column names.
                    var colNames = new List<string>();

                    foreach (IXLRow row in workSheet.Rows(1, lastRowIndex))
                    {
                        if (!header)
                        {
                            foreach (IXLCell cell in row.Cells(1, lastColumnIndex))
                            {
                                // Add column names to the list from the first row from the work sheet.
                           colNames.Add(cell.GetFormattedString());                                   
                            }
                            header = true;
                        }
                        // Check if the CID (value from column 1 from current row)                            
                         if(!tableDict.ContainsKey(row.Cells(1).GetFormattedString()))
                        {
                            // Create new DataTable if the dictionary doesn't have the key.
                            var dt = new DataTable();
                            // Add columns to It.
                            foreach(var colName in  colNames)
                            {
                                dt.Columns.Add(colName);
                            }
                            // Adding table to the dictionary.
                            tableDict.Add(row.Cells(1).GetFormattedString(), dt);
                        }

                        var dataTable = tableDict[row.Cells(1).GetFormattedString()];

                        dataTable.Rows.Add();
                        int i = 0;
                        foreach (IXLCell cell in row.Cells(1, lastColumnIndex))
                        {
                            dataTable.Rows[dataTable.Rows.Count - 1][i] = cell.GetFormattedString();
                            i++;
                        }
                    }
                }
            }

            return tableDict.Values.ToList();
        }
        catch (Exception ex)
        {
            return null;
        }
    }