August 13, 2012

Read Data from Excel File

How can i read all columns from excel file and then place that columns into my database table using store procedure. I successfully copy all the data from excel file but i am confused how to put that data into database table using store procedure? Is theres any loop required?

C#.Net

public void CreateFromExcel()
{
FileInfo MyFile = new FileInfo("D:\\MgsData.xls");
string Value = null;
string dgData = null;
FileName = MyFile.Name;
DGInsertTable.Rows.Clear();

string cnstr = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + MyFile + ";Extended Properties=Excel 8.0";
OleDbConnection oledbConn = new OleDbConnection(cnstr);

DataTable dtSheet = new DataTable();

oledbConn.Open();
dtSheet = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

//Dim rowscount As Integer = dtSheet.Rows.Count
//Dim RowName As String = dtSheet.Rows("TABLE_NAME").ToString()


foreach (DataRow row in dtSheet.Rows) {
Value = row["TABLE_NAME"].ToString();

string strSQL = "SELECT * FROM [" + Value + "]";

txtTableName.Text = Value.Remove(Value.Length - 1);

OleDbCommand cmd = new OleDbCommand(strSQL, oledbConn);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);

da.Fill(ds);

dt = ds.Tables[0];

int rows = dt.Rows.Count - 2;

for (i = 0; i <= rows - 1; i++) {
GridView1.Rows.Add();
for (int j = 0; j <= 6; j++) {
dgData = dt.Rows[i][j].ToString();
GridView1.Item(j, i).Value = dgData;
}
}

}
}
public object Procedure(string ProcedureName)
{
object functionReturnValue = null;
var _with1 = Declaration;
try {
_with1.cn = Connection.GetConnection();
_with1.comm.CommandText = "";
_with1.comm.Connection = _with1.cn;
_with1.comm.CommandType = System.Data.CommandType.StoredProcedure;
_with1.comm.CommandText = ProcedureName;
Lines();
_with1.comm.ExecuteNonQuery();
_with1.cn.Close();
} catch (Exception ex) {
if (Information.Err().Number != 0)
Interaction.MsgBox(Information.Err().Description, MsgBoxStyle.Critical);
return functionReturnValue;
}
return functionReturnValue;
}

public void Lines()
{
foreach (GridViewRow row in GridView1.Rows) {
Parameters("@TMasterID", SqlDbType.Int, row.Cells(0).Text);
Parameters("@MobileNumber", SqlDbType.VarChar, row.Cells(1).Text);
Parameters("@ModemNumber", SqlDbType.VarChar, row.Cells(2).Text);
Parameters("@PortName", SqlDbType.VarChar, row.Cells(3).Text);
}


}

public void Parameters(string Col, System.Data.SqlDbType dType, string Value)
{
Declaration.comm.Parameters.Add(Col, dType).Value = Value;
}


VB.Net

Public Sub CreateFromExcel() 
Dim MyFile As FileInfo = New FileInfo("D:\MgsData.xls")
Dim Value, dgData As String
FileName = MyFile.Name
DGInsertTable.Rows.Clear()

Dim cnstr As String = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & MyFile & ";Extended Properties=Excel 8.0"
Dim oledbConn As OleDbConnection = New OleDbConnection(cnstr)

Dim dtSheet As DataTable = New DataTable()

oledbConn.Open()
dtSheet = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

'Dim rowscount As Integer = dtSheet.Rows.Count
'Dim RowName As String = dtSheet.Rows("TABLE_NAME").ToString()


For Each row As DataRow In dtSheet.Rows
Value = row("TABLE_NAME").ToString()

Dim strSQL As String = "SELECT * FROM [" & Value & "]"

txtTableName.Text = Value.Remove(Value.Length - 1)

Dim cmd As OleDbCommand = New OleDbCommand(strSQL, oledbConn)
Dim ds As DataSet = New DataSet()
Dim dt As DataTable = New DataTable()
Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)

da.Fill(ds)

dt = ds.Tables(0)

Dim rows As Integer = dt.Rows.Count - 2

For i = 0 To rows - 1
GridView1.Rows.Add()
For j As Integer = 0 To 6
dgData = dt.Rows(i)(j).ToString
GridView1.Item(j, i).Value = dgData
Next
Next

Next
End Sub
Public Function Procedure(ByVal ProcedureName As String)
With Declaration
Try
.cn = Connection.GetConnection()
.comm.CommandText = ""
.comm.Connection = .cn
.comm.CommandType = Data.CommandType.StoredProcedure
.comm.CommandText = ProcedureName
Lines()
.comm.ExecuteNonQuery()
.cn.Close()
Catch ex As Exception
If Err.Number <> 0 Then MsgBox(Err.Description, MsgBoxStyle.Critical)
Exit Function
End Try
End With
End Function

Public Sub Lines()
For Each row As GridViewRow In GridView1.Rows
Parameters("@TMasterID", SqlDbType.Int, row.Cells(0).Text )
Parameters("@MobileNumber", SqlDbType.VarChar, row.Cells(1).Text )
Parameters("@ModemNumber", SqlDbType.VarChar, row.Cells(2).Text )
Parameters("@PortName", SqlDbType.Varchar, row.Cells(3).Text )
Next

End Sub

Public Sub Parameters(ByVal Col As String, ByVal dType As Data.SqlDbType, ByVal Value As String)
Declaration.comm.Parameters.Add(Col, dType).Value = Value
End Sub