C#实现客户端数据导入导出
网站开发中经常遇到大量数据导入数据库,或者数据库导出到文档中。本文结合项目实际,总结出比较实用的Excel文件导入到数据库和数据库导出到Excel表格中,供大家学习参考。
一、数据库表与Excel表结构
1、Excel表结构
2、数据库Fms_Employee表结构
3、前台设计
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="InfoImport" Text="导入" runat="server" onclick="InfoImport_Click"/>
<asp:Button ID="InfoExport" Text="导出" runat="server" onclick="InfoExport_Click" />
注意:要导入的Excel表列必须与数据库Fms_Employee表的属性列一一对应。
二、Excel表数据导入到MS SQL数据库
protected void InfoImport_Click(object sender, EventArgs e)
{
try
{
string fileName = FileUpload1.FileName;
string savePath = Server.MapPath(("~\\upfiles\\")+fileName);
FileUpload1.SaveAs(savePath);
string strCon = "Provider=Microsoft.Jet.Oledb.4.0;"+"Data Source="+savePath+";Extended Properties='Excel 8.0;HDR=yes;IMEX=1'";
OleDbConnection odc=new OleDbConnection(strCon);
odc.Open();
string strCmd=string.Format("select * from [{0}$]","sheet1");
OleDbDataAdapter oda=new OleDbDataAdapter(strCmd,odc);
DataSet ds=new DataSet();
oda.Fill(ds,"sheet1");
foreach(DataRow dr in ds.Tables[0].Rows)
{
InsertToSql(dr);
}
Response.Write("<script>alert('导入成功!');</script>");
}
catch
{
Response.Write("<script>alert('导入的Excel文件格式不正确!');</script>");
}
}
private void InsertToSql(DataRow dr)
{
SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["ConnectionDataBase"]);
string employeeID = dr[0].ToString();
string domainName = dr[1].ToString();
string name = dr[2].ToString();
string roleID = dr[3].ToString();
string departID = dr[4].ToString();
string department = dr[5].ToString();
con.Open();
string sql = "insert into Fms_Employee Values('" + employeeID + "','" + domainName + "','" + name + "','" + roleID + "','" + departID + "','" + department+"')";
SqlCommand com = new SqlCommand(sql,con);
com.ExecuteNonQuery();
con.Close();
}
三、Excel表数据导入到MS SQL数据库
protected void InfoExport_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["ConnectionDataBase"]);
string sql = "select * from Fms_Employee order by EmployeeID";
SqlDataAdapter sda = new SqlDataAdapter(sql,con);
DataSet ds = new DataSet();
sda.Fill(ds, "Fms_Employee");
DataTable dt = ds.Tables["Fms_Employee"];
StringWriter sw = new StringWriter();
sw.WriteLine("职工编号\t域账号\t职工姓名\t角色编号\t部门编号\t部门名称");
foreach (DataRow dr in dt.Rows)
{
sw.WriteLine(dr[0]+"\t"+dr[1]+"\t"+dr[2]+"\t"+dr[3]+"\t"+dr[4]+"\t"+dr[5]);
}
sw.Close();
Response.AddHeader("content-disposition",string.Format("attachment;filename={0}","Employee.xls"));
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.Write(sw);
Response.End();
}