导入Excel后绑定GridView实例

http://blog.csdn.net/loveheronly/article/details/6715552

项目中经常用到导入导出的例子，前面做了导出的例子，现在把导入Excel的数据的例子也把它晾出来，不足之处，请大家多多指教。

1.导入前的视图Excel和页面视图

 

3.前台代码

<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="Label1" runat="server" Text="请选择上传文件（.xls）"></asp:Label>
        <asp:FileUpload ID="fUExcel" runat="server" />
        <asp:Button ID="btnExcel" runat="server"  Text="确定" OnClick="btnExcel_Click" />
     <asp:GridView ID="gvExcelData" runat="server" BackColor="LightGoldenrodYellow" 
            BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" 
            GridLines="None">
         <FooterStyle BackColor="Tan" />
         <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" 
             HorizontalAlign="Center" />
         <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
         <HeaderStyle BackColor="Tan" Font-Bold="True" />
         <AlternatingRowStyle BackColor="PaleGoldenrod" />
    
    </asp:GridView>
    </div>
    </form>
</body>

4.后台代码

protected void btnExcel_Click(object sender, EventArgs e)
    {
        HttpPostedFile p = fUExcel.PostedFile;
        string filename = DateTime.Now.ToString("yyyyMMddHHmm") + Path.GetFileName(p.FileName);
        if (!Directory.Exists(Server.MapPath(@"~/upload" + "//")))
            Directory.CreateDirectory(Server.MapPath(@"~/upload" + "//"));
        string filePath = Server.MapPath(@"~/upload" + "//" + filename);
        fUExcel.SaveAs(filePath);
        if (filename != "")
        {
            if (filename.Contains("xls"))
            {
                OleDbConnection oleCon = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source='" + filePath + "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'");
                oleCon.Open();
                string Sql = "SELECT   *   FROM   [Sheet1$] ";
                OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, oleCon);
                DataSet ds = new DataSet();
                mycommand.Fill(ds, "[Sheet1$]");
                oleCon.Close();
                int count = ds.Tables["[Sheet1$]"].Rows.Count;
                for (int i = 0; i < count; i++)
                {
                    string ItemId = ds.Tables["[Sheet1$]"].Rows[i]["项目Id"].ToString().Trim();
                    string Fixnumber = ds.Tables["[Sheet1$]"].Rows[i]["用户号码"].ToString().Trim();
                    string LinkMan = ds.Tables["[Sheet1$]"].Rows[i]["联系人姓名"].ToString().Trim();
              //这里可以写插入数据库的代码
                }
                gvExcelData.DataSource = ds;
                gvExcelData.DataBind();
                Response.Write("<script>window.alert('导入数据成功！');</script>");
            }
            else
            {
                Response.Write("<script>window.alert('请检查您选择的文件是否为Excel文件！');</script>");
                return;
            }
        }
        else
        {
            Response.Write("<script>window.alert('请先选择导入文件后，再执行导入！');</script>");
            return;
        }

    }

5.注意得引入命名空间

using System.IO;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Sql;

6.最后一点就是你要导入的Excel表的表头是

项目Id、用户号码、联系人姓名

posted @ 2016-07-12 17:22  小A永不败  阅读(435)  评论(0)  编辑  收藏  举报