使用c＃和ASP.NET将两张数据从excel导入到两个sql server表中 [英] import two sheets data from excel to two sql server table using c# and ASP.NET
问题描述
我成功将excel数据（Sheet1）导入一个sql server表...现在我想将多张excel文件数据导入sql server到他们（很多表）各自的表...我正在尝试一些但我无法做到这一点......我的代码写在下面..
I am successfully import excel data(Sheet1) to one sql server table... now i want to import many sheet of excel file data into sql server to their (many tables) respective tables... Some what i am trying but i cant able to do this... My code written below..
<asp:FileUpload ID="FlUploadcsv" runat="server" />
<asp:Button ID="btnIpload" runat="server" Text="Import" OnClick="btnIpload_Click" />
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
<br />
<asp:GridView ID="gvEmployee" runat="server" width="100%"> <HeaderStyle BackColor="#89A0FE" />
</asp:GridView>
c＃：
public partial class _Default：System.Web.UI.Page
{
SqlConnection conn = new SqlConnection（Data Source = User-PC; Initial Catalog = master; Integrated Security = True ）;
protected void Page_Load（object sender，EventArgs e）
{
if（！IsPostBack）
{
BindGrid（）;
}
}
void ImporttoDatatable（）
{
试试
{
if（FlUploadcsv.HasFile）
{
string FileName = FlUploadcsv.FileName;
string path = string.Concat（Server.MapPath（ 〜/ Document /+ FlUploadcsv.FileName））;
FlUploadcsv.PostedFile.SaveAs（path）;
OleDbConnection OleDbcon = new OleDbConnection（Provider = Microsoft.ACE。 OLEDB.12.0;数据源=+路径+;扩展属性= Excel 12.0;）;
OleDbCommand cmd1 =新OleDbCommand（SELECT * FROM [Sheet1 $]，OleDbcon）;
OleDbCommand cmd2 =新的OleDbCommand（选择*来自[Sheet2 $]，OleDbcon）;
// OleDbCommand cmd3 =新的OleDbCommand（选择*来自[Sheet3 $]，OleDbcon）;
OleDbDataAdapter oad1 = new OleDbDataAdapter（cmd1）;
OleDbDataAdapter oad2 = new OleDbDataAdapter（cmd2）;
// OleDbDataAdapter oad3 = new OleDbDataAdapter（cmd3）;
OleDbcon.Open（）;
//为数据工作表创建DbDataReader
DbDataReader dr1 = cmd1.ExecuteReader（）;
DbDataReader dr2 = cmd2.ExecuteReader（）;
// DbDataReader dr3 = cmd3.ExecuteReader（）;
// SQL Server连接字符串
string constr = @Data Source = User-PC; Initial Catalog = master; Integrated Security = True;
//批量复制到SQL Server
SqlBulkCopy bulkInsert = new SqlBulkCopy（constr）;
bulkInsert.DestinationTableName =EmployeeDetails;
bulkInsert.WriteToServer（dr1）;
bulkInsert .DestinationTableName =EmpImport;
bulkInsert.WriteToServer（dr2）;
// bulkInsert.WriteToServer（dr3）;
OleDbcon.Close （）;
//Array.ForEach(Directory.GetFiles((Server.MapPath(\"~//Document/））），File.Delete）;
Label1 .ForeColor = Color.Green;
Label1.Text =已成功插入;
}
其他
{
Label1.ForeColor = Color.Red;
Label1.Text =请选择文件;
}
}
catch（例外情况）
{
}
}
void BindGrid（）
{
DataSet ds = new DataSet（）;
conn.Open（）;
string cmdstr =从EmployeeDetails中选择*;
SqlCommand cmd = new SqlCommand（cmdstr，conn）;
SqlDataAdapter adp = new SqlDataAdapter（cmd）; < br $>
string cmdstr2 =select * from EmpImport;
SqlCommand cmd2 = new SqlCommand（cmdstr2，conn）;
SqlDataAdapter adp2 = new SqlDataAdapter（cmd）;
adp.Fill（ds） ;
gvEmployee.DataSource = ds;
gvEmployee.DataBind（）;
ds.Dispose（） ;
conn.Close（）;
}
protected void btnIpload_Click（object sender，EventArgs e）
{
ImporttoDatatable（）;
BindGrid（）;
}
}
c#:
public partial class _Default : System.Web.UI.Page
{
SqlConnection conn = new SqlConnection("Data Source=User-PC;Initial Catalog=master;Integrated Security=True");
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}
void ImporttoDatatable()
{
try
{
if (FlUploadcsv.HasFile)
{
string FileName = FlUploadcsv.FileName;
string path = string.Concat(Server.MapPath("~/Document/" + FlUploadcsv.FileName));
FlUploadcsv.PostedFile.SaveAs(path);
OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
OleDbCommand cmd1 = new OleDbCommand("SELECT * FROM [Sheet1$]", OleDbcon);
OleDbCommand cmd2 = new OleDbCommand("Select * from [Sheet2$]", OleDbcon);
// OleDbCommand cmd3 = new OleDbCommand("Select * from [Sheet3$]", OleDbcon);
OleDbDataAdapter oad1 = new OleDbDataAdapter(cmd1);
OleDbDataAdapter oad2 = new OleDbDataAdapter(cmd2);
// OleDbDataAdapter oad3 = new OleDbDataAdapter(cmd3);
OleDbcon.Open();
// Create DbDataReader to Data Worksheet
DbDataReader dr1 = cmd1.ExecuteReader();
DbDataReader dr2 = cmd2.ExecuteReader();
// DbDataReader dr3 = cmd3.ExecuteReader();
// SQL Server Connection String
string constr = @"Data Source=User-PC;Initial Catalog=master;Integrated Security=True";
// Bulk Copy to SQL Server
SqlBulkCopy bulkInsert = new SqlBulkCopy(constr);
bulkInsert.DestinationTableName = "EmployeeDetails";
bulkInsert.WriteToServer(dr1);
bulkInsert.DestinationTableName = "EmpImport";
bulkInsert.WriteToServer(dr2);
// bulkInsert.WriteToServer(dr3);
OleDbcon.Close();
//Array.ForEach(Directory.GetFiles((Server.MapPath("~/Document/"))), File.Delete);
Label1.ForeColor = Color.Green;
Label1.Text = "successfully inserted";
}
else
{
Label1.ForeColor = Color.Red;
Label1.Text = "Please select the File";
}
}
catch (Exception ex)
{
}
}
void BindGrid()
{
DataSet ds = new DataSet();
conn.Open();
string cmdstr = "Select * from EmployeeDetails";
SqlCommand cmd = new SqlCommand(cmdstr, conn);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
string cmdstr2 = "select * from EmpImport";
SqlCommand cmd2 = new SqlCommand(cmdstr2, conn);
SqlDataAdapter adp2 = new SqlDataAdapter(cmd);
adp.Fill(ds);
gvEmployee.DataSource = ds;
gvEmployee.DataBind();
ds.Dispose();
conn.Close();
}
protected void btnIpload_Click(object sender, EventArgs e)
{
ImporttoDatatable();
BindGrid();
}
}
推荐答案
，OleDbcon）;
OleDbCommand cmd2 = new OleDbCommand（Select * from [Sheet2
", OleDbcon);
OleDbCommand cmd2 = new OleDbCommand("Select * from [Sheet2
，OleDbcon） ;
// OleDbCommand cmd3 =新的OleDbCommand（选择*来自[Sheet3
", OleDbcon);
// OleDbCommand cmd3 = new OleDbCommand("Select * from [Sheet3
，OleDbcon）;
OleDbDataAdapter oad1 = new OleDbDataAdapter（cmd1）;
OleDbDataAdapter oad2 = new OleDbDataAdapter（cmd2）;
// OleDbDataAdapter oad3 = new OleDbDataAdapter（cmd3）;
OleDbcon.Open（）;
//为数据工作表创建DbDataReader
DbDataReader dr1 = cmd1 .ExecuteReader（）;
DbDataReader dr2 = cmd2.ExecuteReader（）;
// DbDataReader dr3 = cmd3.ExecuteReader（）;
< br $>
// SQL Server连接字符串
string constr = @数据源=用户PC;初始目录=主数据;集成安全性=真;
//批量复制到SQL Server
SqlBulkCopy bulkInsert = new SqlBulkCopy（constr）;
bulkInsert.DestinationTableName =EmployeeDetails;
bulkInsert.WriteToServer（dr1）;
bulkInsert.DestinationTableName =EmpImport;
bulkInsert.WriteToServer（dr2）;
// bulkInsert。 WriteToServer（dr3）;
OleDbcon.Close（）;
//Array.ForEach(Directory.GetFiles((Server.MapPath(\"~//Document/）） ），File.Delete）;
Label1.ForeColor = Color.Green;
Label1.Text =已成功插入;
}
else
{
Label1.ForeColor = Color.Red;
Label1.Text =请选择文件;
}
}
catch（例外情况）
{
}
}
void BindGrid（）
{
DataSet ds = new DataSet（）;
conn.Open（）;
string cmdstr =从EmployeeDetails中选择*;
SqlCommand cmd = new SqlCommand（cmdstr，conn）;
SqlDataAdapter adp = new SqlDataAdapter（cmd）;
string cmdstr2 =select * from EmpImport;
SqlCommand cmd2 = new SqlCommand（cmdstr2，conn）;
SqlDataAdapter adp2 = new SqlDataAdapter（cmd）;
adp.Fill（ds） ;
gvEmployee.DataSource = ds;
gvEmployee.DataBind（）;
ds.Dispose（） ;
conn.Close（）;
}
protected void btnIpload_Click（object sender，EventArgs e）
{
ImporttoDatatable（）;
BindGrid（）;
}
}
", OleDbcon);
OleDbDataAdapter oad1 = new OleDbDataAdapter(cmd1);
OleDbDataAdapter oad2 = new OleDbDataAdapter(cmd2);
// OleDbDataAdapter oad3 = new OleDbDataAdapter(cmd3);
OleDbcon.Open();
// Create DbDataReader to Data Worksheet
DbDataReader dr1 = cmd1.ExecuteReader();
DbDataReader dr2 = cmd2.ExecuteReader();
// DbDataReader dr3 = cmd3.ExecuteReader();
// SQL Server Connection String
string constr = @"Data Source=User-PC;Initial Catalog=master;Integrated Security=True";
// Bulk Copy to SQL Server
SqlBulkCopy bulkInsert = new SqlBulkCopy(constr);
bulkInsert.DestinationTableName = "EmployeeDetails";
bulkInsert.WriteToServer(dr1);
bulkInsert.DestinationTableName = "EmpImport";
bulkInsert.WriteToServer(dr2);
// bulkInsert.WriteToServer(dr3);
OleDbcon.Close();
//Array.ForEach(Directory.GetFiles((Server.MapPath("~/Document/"))), File.Delete);
Label1.ForeColor = Color.Green;
Label1.Text = "successfully inserted";
}
else
{
Label1.ForeColor = Color.Red;
Label1.Text = "Please select the File";
}
}
catch (Exception ex)
{
}
}
void BindGrid()
{
DataSet ds = new DataSet();
conn.Open();
string cmdstr = "Select * from EmployeeDetails";
SqlCommand cmd = new SqlCommand(cmdstr, conn);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
string cmdstr2 = "select * from EmpImport";
SqlCommand cmd2 = new SqlCommand(cmdstr2, conn);
SqlDataAdapter adp2 = new SqlDataAdapter(cmd);
adp.Fill(ds);
gvEmployee.DataSource = ds;
gvEmployee.DataBind();
ds.Dispose();
conn.Close();
}
protected void btnIpload_Click(object sender, EventArgs e)
{
ImporttoDatatable();
BindGrid();
}
}
这篇关于使用c＃和ASP.NET将两张数据从excel导入到两个sql server表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
