Read data from excel using column name and insert in to SQL table in C#

I am implementing a system to upload exam marks using Excel sheet. Excel sheet content the student Index number, Name and Grade and the Exams they have taken with Pass/Fail status. Names of the exams and number of exams need to face may vary from student to student.
[Excel sheet format]

Index# | Name   | Grade | Exam 1 | Exam 2 | Exam 3 | Exam 4 
  1    | John   | 5     | Pass   | Fail   | N/A    | Pass
  2    | Tom    | 6     | Pass   | Pass   | Pass   | N/A
  3    | Bob    | 8     | N/A    | Fail   | Pass   | Pass
  4    | Harry  | 7     | Pass   | Pass   | Pass   | Pass

I need to save this data into a SQL table in this format

Index# | Name   | Grade | Exam_Name | Status
  1    | John   | 5     | Exam 1    | Pass
  1    | John   | 5     | Exam 2    | Fail
  1    | John   | 5     | Exam 4    | Pass
  2    | Tom    | 6     | Exam 1    | Pass
  2    | Tom    | 6     | Exam 2    | Pass
  2    | Tom    | 6     | Exam 3    | Pass
etc.

I manage to read the column name from the excel sheet and add it to a list using this code.

 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<string> ColumnList = new List<string>();

                foreach (DataColumn dc in data.Columns) 
                {
                    ColumnList.Add(dc.ColumnName);
                }

Now I need to read the data row by row in the excel sheet using this column names.
Can anyone help me please ?

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You can try this:

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

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:

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);

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating