read the data from excel using the name of the column and insert the data into the sql table in c

You can try this:

For this example I have created a class to collect the results by rows that will be destined to SQL:

C#

public class Result
{

    public string Index { get; set; }

    public string Name { get; set; }

    public string Grade { get; set; }

    public string Exam_Name { get; set; }

    public string Status { get; set; }

}

And the code for read the Excel file:

C#

System.Data.OleDb.OleDbConnection Sconn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Savelocation + ("" + FileName + "") + ";Extended Properties=Excel 8.0");

var adapter = new OleDbDataAdapter("SELECT * FROM [Marks$]", Sconn);
var ds = new DataSet();
adapter.Fill(ds, "myTable");
DataTable data = ds.Tables["myTable"];
List<Result> r = new List<Result>();

foreach (DataRow row in data.Rows)
{

    for (int i = 3; i < data.Columns.Count; i++)
    {
        string indexCell = (string)row["Index"],
                nameCell =  (string)row["Name"],
                gradeCell = (string)row["Grade"],
                examCell =  data.Columns[i].ColumnName,
                passCell =  (string)row[i];

        r.Add(new Result()
        {
            Index = indexCell,
            Name = nameCell,
            Grade = gradeCell,
            Exam_Name = examCell,
            Status = passCell
        });

        // Or call your logic for send this data to SQL and INSERT the data
    }
                
}

Console.Write(r);

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top