批量导入功能实现

来源：互联网发布：视频亮度调节软件编辑：程序博客网时间：2024/05/16 12:02

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

阅读全文

0 0