Hi KhaliqEPS,
Refer below article to import Excel sheet to DataTable.
Once you read the excel to DataTable you will insert the record in database using the below code by looping through the row and column.
C#
// Dummy DataTable for explanation.
// Get the DataTable by reading the excel file.
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] { new DataColumn("productionorder"),
new DataColumn("9/13/2018"),
new DataColumn("9/14/2018"),
new DataColumn("9/15/2018"),
new DataColumn("9/16/2018"),
new DataColumn("9/17/2018")});
dt.Rows.Add("3022221", "STN1", "STN3", "STN4", "STN5", "");
dt.Rows.Add("3022222", "STN1", "STN2", "STN3", "STN4", "STN5");
dt.Rows.Add("3022223", "STN1", "STN2", "STN3", "STN4", "STN5");
dt.Rows.Add("3022224", "STN1", "STN3", "STN4", "", "");
for (int row = 0; row < dt.Rows.Count; row++)
{
for (int column = 1; column < dt.Columns.Count; column++)
{
if (!string.IsNullOrEmpty(dt.Rows[row][column].ToString()))
{
string production_order = dt.Rows[row]["productionorder"].ToString();
string station_id = dt.Rows[row][column].ToString();
DateTime creation_date = Convert.ToDateTime(dt.Columns[column].ColumnName);
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string query = "INSERT INTO ProductionOrder VALUES(@Order,@Station,@CreationDate)";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Parameters.AddWithValue("@Order", production_order);
cmd.Parameters.AddWithValue("@Station", station_id);
cmd.Parameters.AddWithValue("@CreationDate", creation_date);
using (SqlConnection con = new SqlConnection(conString))
{
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
}
}
VB.Net
' Dummy DataTable for explanation.
' Get the DataTable by reading the excel file.
Dim dt As DataTable = New DataTable()
dt.Columns.AddRange(New DataColumn() {New DataColumn("productionorder"), New DataColumn("9/13/2018"), New DataColumn("9/14/2018"), New DataColumn("9/15/2018"), New DataColumn("9/16/2018"), New DataColumn("9/17/2018")})
dt.Rows.Add("3022221", "STN1", "STN3", "STN4", "STN5", "")
dt.Rows.Add("3022222", "STN1", "STN2", "STN3", "STN4", "STN5")
dt.Rows.Add("3022223", "STN1", "STN2", "STN3", "STN4", "STN5")
dt.Rows.Add("3022224", "STN1", "STN3", "STN4", "", "")
For row As Integer = 0 To dt.Rows.Count - 1
For column As Integer = 1 To dt.Columns.Count - 1
If Not String.IsNullOrEmpty(dt.Rows(row)(column).ToString()) Then
Dim production_order As String = dt.Rows(row)("productionorder").ToString()
Dim station_id As String = dt.Rows(row)(column).ToString()
Dim creation_date As DateTime = Convert.ToDateTime(dt.Columns(column).ColumnName)
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim query As String = "INSERT INTO ProductionOrder VALUES(@Order,@Station,@CreationDate)"
Using cmd As SqlCommand = New SqlCommand(query)
cmd.Parameters.AddWithValue("@Order", production_order)
cmd.Parameters.AddWithValue("@Station", station_id)
cmd.Parameters.AddWithValue("@CreationDate", creation_date)
Using con As SqlConnection = New SqlConnection(conString)
cmd.Connection = con;
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
End If
Next
Next