读取excel到数据库里面

//读取excel数据到dataTable里面
public DataTable ReadExcelDataToDataTable(string path)
{
DataTable dt = new DataTable();
string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" + path + ";Extended Properties='Excel 8.0;Imex=1' ";
OleDbConnection oc = new OleDbConnection(strCon);
oc.Open();
string sql = "select * from [企业登记注册类型代码$]";
OleDbDataAdapter oda = new OleDbDataAdapter(sql,oc);
DataSet ds = new DataSet();
oda.Fill(ds, "data");
oc.Close();
dt = ds.Tables["data"];
dt.Rows.RemoveAt(0);

return dt;
}
//dataTable中的数据写入到数据库中 第一种方式excel中的字段必须与数据库中的字段保持一致
public void WriteToSqlDatabase(string path)
{
SqlBulkCopy sbc = new SqlBulkCopy(conString,SqlBulkCopyOptions.UseInternalTransaction);
sbc.DestinationTableName = "RegistType";//数据库中的表名
DataTable dt = ReadExcelDataToDataTable(path);
sbc.WriteToServer(dt);
}
//dataTable中的数据写入到数据库中 第二种方式
public void WriteDatabase(string path)
{
SqlConnection con = new SqlConnection(conString);
con.Open();
string sql = "insert into RegistType(registId,registTypeName)"+ "values(@registId,@registTypeName)";
SqlCommand insertCommand = new SqlCommand(sql,con);
insertCommand.Parameters.Add("@registId",SqlDbType.VarChar,20,"registId");
insertCommand.Parameters.Add("@registTypeName",SqlDbType.VarChar,50,"registTypeName");
SqlDataAdapter sda = new SqlDataAdapter(insertCommand);
sda.InsertCommand = insertCommand;
DataTable dt = ReadExcelDataToDataTable(path);
sda.Update(dt);
}

//未完整仅供参考

转载于:https://www.cnblogs.com/cygnet/p/3897168.html