用户上传EXCEL文件 取值转换成DataSet 代码直接可用

 

首先在网页放上以下控件以便于显示信息用来查看
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click"></asp:Button>
<asp:DataGrid ID="DataGrid1" runat="server"></asp:DataGrid>

 
            //判断是否上传了文件 if里面的代码需要在一个上传按钮的点击事件里面
            if (FileUpload1.HasFile)
            {
                //指定上传文件在服务器上的保存路径
                string savePath = Server.MapPath("~/App_Data/");
                //检查服务器上是否存在这个物理路径，如果不存在则创建
                if (!System.IO.Directory.Exists(savePath))
                {
                    //需要注意的是，需要对这个物理路径有足够的权限，否则会报错
                    //另外，这个路径应该是在网站之下，而将网站部署在C盘却把上传文件保存在D盘
                    System.IO.Directory.CreateDirectory(savePath);
                }
                savePath = savePath + "\\" + FileUpload1.FileName;
                FileUpload1.SaveAs(savePath);//保存文件

               //调用方法  转换成DataSet
                DataSet ds = ReadExcel(savePath);

                //输出结果
                Response.Write("<script>alert('" + bianlidatatable(ds.Tables[0]) + "')</script>");

            }
       

 //传来一个DataTable  直接与数据库交互返回结果
        protected string bianlidatatable(DataTable dt)
        {
            string jieguo = null;
            company c = null;
            for (int i = 0; i < dt.Rows.Count; i++)//逐行加
            {
                if (dt.Rows[i][1].ToString() != "serial")
                {

                    //取出DataTable的某一行 某一列  赋给MODEL
                    c = new company();

                    c.serial = dt.Rows[i][0].ToString();
                    c.quyu = dt.Rows[i][1].ToString();
                    c.name = dt.Rows[i][2].ToString();
                    c.addr = dt.Rows[i][3].ToString();
                    c.faren = dt.Rows[i][4].ToString();
                    c.企业负责人 = dt.Rows[i][5].ToString();
                    c.质量负责人 = dt.Rows[i][6].ToString();
                    c.经营方式 = dt.Rows[i][7].ToString();
                    c.经营范围 = dt.Rows[i][8].ToString();
                    c.仓库地址 = dt.Rows[i][9].ToString();
                    c.许可证号 = dt.Rows[i][10].ToString();
                    c.发证机关 = dt.Rows[i][11].ToString();
                    c.许可证发证日期 = Convert.ToDateTime(dt.Rows[i][12]);
                    c.许可证有效日期 = Convert.ToDateTime(dt.Rows[i][13]);

                     //和数据库交互 返回结果
                    if (indexManager.companyshidoucunzai(c.name.ToString()) > 0)
                    {
                        if (indexManager.updatecompany(c, "xiugai") > 0)
                        {
                            jieguo = "您好。。。您的企业信息已成功更新";
                        }
                        else
                        {
                            jieguo = "您好。。。您的信息更新失败请稍后再试";
                        }
                    }
                    else
                    {
                        if (indexManager.updatecompany(c, "add") > 0)
                        {
                            jieguo = "您好。。。您的企业信息已成功添加";
                        }
                        else
                        {
                            jieguo = "您好。。。您的信息添加失败请稍后再试";
                        }
                    }
                }
            }
            return jieguo;
        }

        /// <summary>
        /// 读取Excel数据到DataSet
        /// </summary>
        /// <param name="strFileName">带路径名称</param>
        /// <returns></returns>
        private static DataSet ReadExcel(string strFileName)
        {

                //匹配
            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;Persist Security Info=False\"";
            strConnection = string.Format(strConnection, strFileName);
            OleDbConnection con = new OleDbConnection(strConnection);
            con.Open();
            try
            {
                DataTable schema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                //下面取得第一个表名  
                string strTableName = schema.Rows[0]["TABLE_NAME"].ToString();

                OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + strTableName + "]", con);
                DataSet ds = new DataSet();
                da.Fill(ds);

                da.Dispose();
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }