asp.net 上传Excel文件显示在 GvidView

前台页面

View Code

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    上传Excel文件:
    <asp:FileUpload ID="fuExcel" runat="server" />
     
    <asp:Button ID="btnUp" runat="server" Text="上传" onclick="btnUp_Click" />
     
    <asp:Label ID="lblMes" runat="server" Text=""></asp:Label>
    <div>
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
    </div>
    </form>
</body>
</html>

后台代码

View Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;
using System.Collections;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    // 上传按钮
    protected void btnUp_Click(object sender, EventArgs e)
    {
        bool b = Upload(fuExcel);  // 上传excel文件
        if (!b)
        {
            return;
        }
        string name = fuExcel.FileName;
        string filepath = Server.MapPath("~/upload/") + name;
        DataSet ds = ExcelDataSource(filepath, ExcelSheetName(filepath)[0].ToString());
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }

    //上传文件方法
    private bool Upload(FileUpload myFileUpload)
    {
        bool flag = false;
        //是否允许上载
        bool fileAllow = false;
        //设定允许上载的扩展文件名类型
        string[] allowExtensions = { ".xls" };

        //取得网站根目录路径
        string path = HttpContext.Current.Request.MapPath("~/upload/");
        //检查是否有文件案
        if (myFileUpload.HasFile)
        {
            //取得上传文件之扩展文件名，并转换成小写字母
            string fileExtension = System.IO.Path.GetExtension(myFileUpload.FileName).ToLower();
            //检查扩展文件名是否符合限定类型
            for (int i = 0; i < allowExtensions.Length; i++)
            {
                if (fileExtension == allowExtensions[i])
                {
                    fileAllow = true;
                }
            }

            if (fileAllow)
            {
                try
                {
                    //存储文件到文件夹
                    myFileUpload.SaveAs(path + myFileUpload.FileName);
                    lblMes.Text = "文件导入成功";
                    flag = true;
                }
                catch (Exception ex)
                {
                    lblMes.Text += ex.Message;
                    flag = false;
                }
            }
            else
            {
                lblMes.Text = "不允许上载：" + myFileUpload.PostedFile.FileName + "，只能上传xls的文件，请检查！";
                flag = false;
            }
        }
        else
        {
            lblMes.Text = "请选择要导入的excel文件!";
            flag = false;
        }
        return flag;
    }

    //该方法实现从Excel中导出数据到DataSet中，其中filepath为Excel文件的绝对路径， sheetname为excel文件中的表名
    public DataSet ExcelDataSource(string filepath, string sheetname)
    {
        string strConn;
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
        OleDbConnection conn = new OleDbConnection(strConn);
        OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
        DataSet ds = new DataSet();
        oada.Fill(ds);
        conn.Close();
        return ds;
    }

    //获得Excel中的所有sheetname。
    public ArrayList ExcelSheetName(string filepath)
    {
        ArrayList al = new ArrayList();
        string strConn;
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
        OleDbConnection conn = new OleDbConnection(strConn);
        conn.Open();
        DataTable sheetNames = conn.GetOleDbSchemaTable
        (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
        conn.Close();
        foreach (DataRow dr in sheetNames.Rows)
        {
            al.Add(dr[2]);
        }
        return al;
    }

}

asp.net 上传Excel文件 显示在 GvidView

asp.net 上传Excel文件显示在 GvidView