批量导入功能就是将一个excel文件中的每一行数据导入进数据库中,其中excel文件的数据格式要符合一定的要求。其功能的实现包括四步,校验、上载、读取、导入。
校验是指对文件的格式、文件数据以及文件的大小进行验证,检验其是否符合要求。
上载是指利用FileUpload.PostedFile.SaveAs(string path)保存上载文件的内容。
读取是指将excel中的数据读取到DataTable中。
导入即是将DataTable中的每行数据插入到相应的数据库数据表中。
<form id="form1" runat="server" width="80%">
<div>
<table title="import example" style="">
<tr>
<td>
Batch import
</td>
</tr>
<tr style="width:1400px">
<td style="text-align:right">
Excel:
</td>
<td style="text-align:left">
<asp:FileUpload runat="server" ID="upload"/>
</td>
<td style="text-align:left">
<a href="../ExcelSource/template.xlsx">Download template file</a>
</td>
<td style="text-align:left">
<asp:Button runat="server" OnClick="Unnamed_Click" ID="importBtn" Text="Import"/>
</td>
</tr>
</table>
</div>
</form>
importContent.aspx
protected void Unnamed_Click(object sender, EventArgs e)
{
//校验
string filename=upload.PostedFile.FileName;
if (filename=="")
{
ClientScript.RegisterStartupScript(GetType(),"", "<script language='javascript'>alert('select a file!')</alert></script>");
}
FileInfo fileinfo = new FileInfo(filename);
string fileextention = fileinfo.Extension;
if(fileextention!=".xls"&&fileextention!=".xlsx")
{
ClientScript.RegisterStartupScript(GetType(), "", "<script language='javascript'>alert('not excel')</script>");
}
//上载
string savepath = Server.MapPath("~") + @"Excel\" + fileinfo.Name + DateTime.Now.Ticks.ToString();
upload.PostedFile.SaveAs(savepath);
//读取
DataTable dt = readExcel(savepath);
//导入到数据库
if (dt!=null&&dt.Rows.Count!=0)
{
int res = import(dt);
ClientScript.RegisterStartupScript(GetType(),"",string.Format("<script language='javascript'>alert('{0}rows has been modified!')</script>",res));
}
}
//导入数据库函数
private int import(DataTable dt)
{
string conn = "Data Source=HEX208;Initial Catalog=treeviewTest;Integrated Security=True";
SqlConnection sqlconn = new SqlConnection(conn);
SqlCommand cmd = null;
int res = 0;
try
{
sqlconn.Open();
foreach (DataRow row in dt.Rows)
{
string objectid = row["objectID"].ToString();
string objectvalue = row["objectValue"].ToString();
string parentid = row["parentID"].ToString();
string sqlstr = string.Format("insert into objectTable (OBJECTID,OBJECTVALUE,OBJECTPARENTID) VALUES ('{0}','{1}','{2}')", objectid, objectvalue, parentid);
cmd = new SqlCommand(sqlstr, sqlconn);
res += cmd.ExecuteNonQuery();
}
sqlconn.Close();
}
catch
{
if (sqlconn.State == ConnectionState.Open)
{
sqlconn.Close();
}
return 0;
}
finally
{
sqlconn.Dispose();
}
return res;
}
//读取Excel数据函数
private DataTable readExcel(string savepath)
{
string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savepath + ";Extended Properties='Excel 12.0;HDR=yes;IMEX=1'";
OleDbConnection odbconn = new OleDbConnection(conn);
OleDbDataAdapter odbdap;
DataSet ds=new DataSet();
DataTable dt = new DataTable();
try
{
odbconn.Open();
string strsql = "select * from [Sheet1$]";
odbdap = new OleDbDataAdapter(strsql,odbconn);
odbdap.Fill(ds);
dt = ds.Tables[0];
odbconn.Close();
}
catch (Exception ex)
{
if (odbconn.State==ConnectionState.Open)
{
odbconn.Close();
}
return null;
}
finally
{
odbconn.Dispose();
}
return dt;
}
importContent.aspx.cs