获得EXCEL的SHEET名

最新推荐文章于 2025-04-17 11:31:52 发布

原创最新推荐文章于 2025-04-17 11:31:52 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #datagrid #dataset #object #microsoft #webform

.NET 专栏收录该内容

7 篇文章

订阅专栏

此博客展示了在ASP.NET中实现Excel数据读取与展示的代码。通过引用COM组件，利用按钮点击事件获取Excel文件，遍历工作表添加到下拉列表，选择工作表后使用OleDb连接数据库，将数据填充到DataSet并绑定到DataGrid进行展示。

//需要引用COM组件Microsoft Excel 11.0 Object Library
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Xml;
using System.Reflection;
namespace GetInfo
{
/// <summary>
/// WebForm1 的摘要说明。
/// </summary>
public class GetInfo : System.Web.UI.Page
{
  protected System.Web.UI.WebControls.Button Button1;
  protected System.Web.UI.HtmlControls.HtmlInputFile File_Excel;
  protected System.Web.UI.WebControls.DataGrid DataGrid1;
  protected System.Web.UI.WebControls.DropDownList DropDownList1;
  protected DataSet New_ds;
  protected static string FileFullName;

  private void Page_Load(object sender, System.EventArgs e)
  {
   // 在此处放置用户代码以初始化页面
   if(!this.IsPostBack)
   {
    DropDownList1.Items.Add("选择工作表");
   }
  }

  #region Web 窗体设计器生成的代码
  override protected void OnInit(EventArgs e)
  {
   //
   // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
   //
   InitializeComponent();
   base.OnInit(e);
  }

  /// <summary>
  /// 设计器支持所需的方法 - 不要使用代码编辑器修改
  /// 此方法的内容。
  /// </summary>
  private void InitializeComponent()
  {
   this.Button1.Click += new System.EventHandler(this.Button1_Click);
   this.DropDownList1.SelectedIndexChanged += new System.EventHandler(this.DropDownList1_SelectedIndexChanged);
   this.Load += new System.EventHandler(this.Page_Load);

}
#endregion

  private void Button1_Click(object sender, System.EventArgs e)
  {
   string FileName;
   string[] SplitString;
   FileFullName = this.File_Excel.Value;
   //获得文件名
   SplitString = this.File_Excel.Value.Split('//');
   FileName= SplitString[SplitString.Length-1].ToString();
   //获取不带扩展名的文件名即工作表名
   FileName=(FileName.Split('.'))[0].ToString();
   try
   {
    Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
    Excel.Workbook oBook = oExcel.Workbooks.Open(FileFullName,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
    foreach(Excel.Worksheet sheet in oBook.Sheets)
    {
     DropDownList1.Items.Add(new ListItem(sheet.Name,sheet.Name));
    }

   }
   catch
   {
    Response.Write("格式不正确");
   }
  }

  private void DropDownList1_SelectedIndexChanged(object sender, System.EventArgs e)
  {
   if(DropDownList1.SelectedIndex != 0)
   {
    OleDbConnection OleDbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileFullName + ";Extended Properties=Excel 8.0;");
    OleDbDataAdapter OleAdp = new OleDbDataAdapter("select * from [" + DropDownList1.SelectedItem.Value + "$]",OleDbConn);
    New_ds = new DataSet();
    OleAdp.Fill(New_ds);
    DataGrid1.DataSource = New_ds;
    DataGrid1.DataBind();
   }
   DropDownList1.SelectedIndex = 0;
  }
}
}