如何将多个excel表导入sql server多个数据库表？ [英] how to Import multiple excel sheets into sql server multiple databse table?
问题描述
<asp:FileUpload ID="FlUploadcsv" runat="server" />
<asp:Button ID="btnIpload" runat="server" Text="Import" OnClick="btnIpload_Click" />
<br />
<asp:GridView ID="empdet" 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）;
DataSet ds;
DataTable Dt;
protected void Page_Load（object sender，EventArgs e）
{
if（！IsPostBack）
{
BindGrid （）;
}
}
private void ImporttoDatatable（）
{
试试
{
if（FlUploadcsv.HasFile）
{
string FileName = FlUploadcsv.FileName;
string path = string.Concat（Server.MapPath（〜/ Document /+ FlUploadcsv.FileName））;
FlUploadcsv.PostedFile .SaveAs（路径）;
OleDbConnection OleDbcon = new OleDbConnection（Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ path +; Extended Properties = Excel 12.0;）;
OleDbCommand cmd1 =新的OleDbCommand（SELECT * FROM [Sheet1 $]，OleDbcon）;
OleDbCommand cmd2 = new OleDbCommand（Select * from [Sheet2 $]，OleDbcon）;
OleDbDataAdapter oad1 = new OleDbDataAdapter（cmd1）;
OleDbDataAdapter oad2 = new OleDbDataAdapter（ cmd2）;
ds = new DataSet（）;
oad1.Fill（ds）;
Dt = ds.Tables [0];
}
}
catch（Exception ex）
{
}
}
私人布尔ValidateDate（字符串日期）
{
尝试
{
string [] dateParts = date.Split（' /'）;
DateTime testDate = new DateTime（Convert.ToInt32（dateParts [2]），Convert.ToInt32（dateParts [1]），Convert.ToInt32（dateParts [0]））;
返回true;
}
catch
{
返回false;
}
}
private void InsertData（）
{
for（int i = 0;我< Dt.Rows.Count; i ++）
{
DataRow row = Dt.Rows [i];
int columnCount = Dt.Columns.Count;
string [] columns = new string [columnCount];
for（int j = 0; j< columnCount; j ++）
{
columns [j] = row [j] .ToString（）;
}
conn.Open（）;
string sql =INSERT INTO EmpImport（EmployeeID，Name，Nameation，DateOfBirth，City）;
sql + =VALUES（'columns + 0] +'，'+ columns [1 ] +'，'+ columns [2] +'，Convert（varchar（10），'+ columns [3] +'，103），'+ columns [4] +'）;
SqlCommand cmd1 = new SqlCommand（sql，conn）;
cmd1.ExecuteNonQuery（）;
string sql2 =INSERT INTO Empdet（Id ，名称，城市）;
sql2 + =VALUES（' + columns [0] +'，'+ columns [1] +'，'+ columns [2] +'）;
SqlCommand cmd2 = new SqlCommand（sql2，conn ）;
cmd2.ExecuteNonQuery（）;
conn.Close（）;
}
}
protected void btnIpload_Click（object sender，EventArgs e）
{
ImporttoDatatable （）;
CheckData（）;
InsertData（）;
BindGrid（）;
}
private void BindGrid（）
{
DataSet ds = new DataSet（）;
conn.Open（）;
string cmdstr =从EmpImport中选择*;
SqlDataAdapter adp = new SqlDataAdapter（cmdstr，conn）;
adp.Fill（ds）;
string cmdstr2 =从Empdet中选择*;
SqlDataAdapter adp2 = new SqlDataAdapter（cmdstr2，conn）;
adp.Fill（ds）;
empdet.DataSource = ds;
empdet.DataBind（）;
ds.Dispose（）;
conn.Close（）;
} < br $>
}
那是我的代码...我成功将单页excel文件导入到sql server的单个表中，但是我想将多张excel文件上传到sql server的多表...请任何人打电话给我怎么做..
C#:
public partial class _Default : System.Web.UI.Page
{
SqlConnection conn = new SqlConnection("Data Source=User-PC;Initial Catalog=master;Integrated Security=True");
DataSet ds;
DataTable Dt;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}
private 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);
OleDbDataAdapter oad1 = new OleDbDataAdapter(cmd1);
OleDbDataAdapter oad2 = new OleDbDataAdapter(cmd2);
ds = new DataSet();
oad1.Fill(ds);
Dt = ds.Tables[0];
}
}
catch (Exception ex)
{
}
}
private bool ValidateDate(string date)
{
try
{
string[] dateParts = date.Split('/');
DateTime testDate = new DateTime(Convert.ToInt32(dateParts[2]), Convert.ToInt32(dateParts[1]), Convert.ToInt32(dateParts[0]));
return true;
}
catch
{
return false;
}
}
private void InsertData()
{
for (int i = 0; i < Dt.Rows.Count; i++)
{
DataRow row = Dt.Rows[i];
int columnCount = Dt.Columns.Count;
string[] columns = new string[columnCount];
for (int j = 0; j < columnCount; j++)
{
columns[j] = row[j].ToString();
}
conn.Open();
string sql = "INSERT INTO EmpImport(EmployeeID,Name,Designation,DateOfBirth,City)";
sql += "VALUES('" + columns[0] + "','" + columns[1] + "','" + columns[2] + "',Convert(varchar(10),'" + columns[3] + "',103),'" + columns[4] + "')";
SqlCommand cmd1 = new SqlCommand(sql, conn);
cmd1.ExecuteNonQuery();
string sql2 = "INSERT INTO Empdet(Id,Name,City)";
sql2 += "VALUES('" + columns[0] + "','" + columns[1] + "','" + columns[2] + "')";
SqlCommand cmd2 = new SqlCommand(sql2, conn);
cmd2.ExecuteNonQuery();
conn.Close();
}
}
protected void btnIpload_Click(object sender, EventArgs e)
{
ImporttoDatatable();
CheckData();
InsertData();
BindGrid();
}
private void BindGrid()
{
DataSet ds = new DataSet();
conn.Open();
string cmdstr = "Select * from EmpImport";
SqlDataAdapter adp = new SqlDataAdapter(cmdstr, conn);
adp.Fill(ds);
string cmdstr2 = "Select * from Empdet";
SqlDataAdapter adp2 = new SqlDataAdapter(cmdstr2, conn);
adp.Fill(ds);
empdet.DataSource = ds;
empdet.DataBind();
ds.Dispose();
conn.Close();
}
}
That is my code... i am successfully imported single sheet of excel file into single table of sql server, but i want to upload multi sheet of excel file into multi table of sql server... Pls anyone tel me how to do that..
推荐答案
，OleDbcon）;
OleDbCommand cmd2 = new OleDbCommand（Select * from [Sheet2
", OleDbcon);
OleDbCommand cmd2 = new OleDbCommand("Select * from [Sheet2
，OleDbcon）;
OleDbDataAdapter oad1 = new OleDbDataAdapter（cmd1）;
OleDbDataAdapter oad2 = new OleDbDataAdapter（cmd2）;
ds = new DataSet（）;
oad1.Fill（ds）;
Dt = ds.Tables [0];
}
}
catch（例外情况）
{
}
}
private bool ValidateDate（字符串日期）
{
尝试
{
string [] dateParts = date.Split（'/'）;
DateTime testDate = new DateTime（Convert.ToInt32（dateParts [2]），Convert.ToInt32（dateParts [ 1]），Convert.ToInt32（dateParts [0]））;
返回true;
}
catch
{
返回false;
}
}
私有空InsertData（）
{
for（int i = 0;我< Dt.Rows.Count; i ++）
{
DataRow row = Dt.Rows [i];
int columnCount = Dt.Columns.Count;
string [] columns = new string [columnCount];
for（int j = 0; j< columnCount; j ++）
{
columns [j] = row [j] .ToString（）;
}
conn.Open（）;
string sql =INSERT INTO EmpImport（EmployeeID，Name，Nameation，DateOfBirth，City）;
sql + =VALUES（'columns + 0] +'，'+ columns [1 ] +'，'+ columns [2] +'，Convert（varchar（10），'+ columns [3] +'，103），'+ columns [4] +'）;
SqlCommand cmd1 = new SqlCommand（sql，conn）;
cmd1.ExecuteNonQuery（）;
string sql2 =INSERT INTO Empdet（Id ，名称，城市）;
sql2 + =VALUES（' + columns [0] +'，'+ columns [1] +'，'+ columns [2] +'）;
SqlCommand cmd2 = new SqlCommand（sql2，conn ）;
cmd2.ExecuteNonQuery（）;
conn.Close（）;
}
}
protected void btnIpload_Click（object sender，EventArgs e）
{
ImporttoDatatable （）;
CheckData（）;
InsertData（）;
BindGrid（）;
}
private void BindGrid（）
{
DataSet ds = new DataSet（）;
conn.Open（）;
string cmdstr =从EmpImport中选择*;
SqlDataAdapter adp = new SqlDataAdapter（cmdstr，conn）;
adp.Fill（ds）;
string cmdstr2 =从Empdet中选择*;
SqlDataAdapter adp2 = new SqlDataAdapter（cmdstr2，conn）;
adp.Fill（ds）;
empdet.DataSource = ds;
empdet.DataBind（）;
ds.Dispose（）;
conn.Close（）;
} < br $>
}
那是我的代码...我成功将单页excel文件导入到sql server的单个表中，但是我想将多张excel文件上传到sql server的多表...请任何人打电话给我怎么做..
", OleDbcon);
OleDbDataAdapter oad1 = new OleDbDataAdapter(cmd1);
OleDbDataAdapter oad2 = new OleDbDataAdapter(cmd2);
ds = new DataSet();
oad1.Fill(ds);
Dt = ds.Tables[0];
}
}
catch (Exception ex)
{
}
}
private bool ValidateDate(string date)
{
try
{
string[] dateParts = date.Split('/');
DateTime testDate = new DateTime(Convert.ToInt32(dateParts[2]), Convert.ToInt32(dateParts[1]), Convert.ToInt32(dateParts[0]));
return true;
}
catch
{
return false;
}
}
private void InsertData()
{
for (int i = 0; i < Dt.Rows.Count; i++)
{
DataRow row = Dt.Rows[i];
int columnCount = Dt.Columns.Count;
string[] columns = new string[columnCount];
for (int j = 0; j < columnCount; j++)
{
columns[j] = row[j].ToString();
}
conn.Open();
string sql = "INSERT INTO EmpImport(EmployeeID,Name,Designation,DateOfBirth,City)";
sql += "VALUES('" + columns[0] + "','" + columns[1] + "','" + columns[2] + "',Convert(varchar(10),'" + columns[3] + "',103),'" + columns[4] + "')";
SqlCommand cmd1 = new SqlCommand(sql, conn);
cmd1.ExecuteNonQuery();
string sql2 = "INSERT INTO Empdet(Id,Name,City)";
sql2 += "VALUES('" + columns[0] + "','" + columns[1] + "','" + columns[2] + "')";
SqlCommand cmd2 = new SqlCommand(sql2, conn);
cmd2.ExecuteNonQuery();
conn.Close();
}
}
protected void btnIpload_Click(object sender, EventArgs e)
{
ImporttoDatatable();
CheckData();
InsertData();
BindGrid();
}
private void BindGrid()
{
DataSet ds = new DataSet();
conn.Open();
string cmdstr = "Select * from EmpImport";
SqlDataAdapter adp = new SqlDataAdapter(cmdstr, conn);
adp.Fill(ds);
string cmdstr2 = "Select * from Empdet";
SqlDataAdapter adp2 = new SqlDataAdapter(cmdstr2, conn);
adp.Fill(ds);
empdet.DataSource = ds;
empdet.DataBind();
ds.Dispose();
conn.Close();
}
}
That is my code... i am successfully imported single sheet of excel file into single table of sql server, but i want to upload multi sheet of excel file into multi table of sql server... Pls anyone tel me how to do that..
这篇关于如何将多个excel表导入sql server多个数据库表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
