批量导入功能实现

        批量导入功能就是将一个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