How to dynamically add (or) insert values to data column in c#?

Question

I am iterating an excel file cell by cell. Each file has its own number of columns. Based on excel cell count, we are dynamically creating the columns to the data table. This part is working fine.

I will have to insert each cell value to the data column. How to dynamically add (or) insert values to data column in c#?

In an assumption, excel file has 2 rows and 3 colums

FirstName LastName Location
---------------------------
Albert     B         Miami
Jackson    C         Tampa

I will have to populate the data table / data column with these cell values. Foreach loop iteration is working fine and picking up each cell value. I am stuck on inserting each cell value to the data column / data row.

int iCellCount = 3;  // In this example, i am defining cell count = 3 as static value. In the real time, cell count is picked from the excel file.
var cellValue = string.Empty;
DataTable dt = new DataTable();
foreach (ExcelReportCell excelCell in excelRow) // Iterating the excel cell row by row in the Excel Sheet
{
    cellValue = excelCell.GetText().ToString(); // cellValue is assigned dynamically through excel file cell by cell iteration logic
    for (int i = 1; i <= iCellCount; i++)
    {
        dt.Columns.Add();
        // Expected to assign each column values
    }
}

Answer 1

You need to add all the columns (make your method independent of the column name, so no hard coded strings) and then add all the respective values.

DataTable dt = new DataTable();
List<string> colList = new List<string>();

// Loop through column collection 
// Add all your columns to data table first
foreach (ExcelReportColumn eachColumn in excelColumn)
{
    dt.Columns.Add(eachColumn, typeof(string));
    colList.Add(eachColumn);
}

DataRow newRow;
int currentCol = 0;
// Then add your data rows
foreach (ExcelReportCell excelCell in excelRow)
{
    newRow = dt.NewRow();
    // Magic Method: You need to know the column name for the the current cell
    string columnName = colList[currentCol]; 
    newRow[columnName] = excelCell;
    dt.Rows.Add(newRow);
    currentCol++;
}