学步园

http://www.cnblogs.com/itvip/archive/2011/03/16/1986116.html

 

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelToDataSet
{
    /// <summary>
    /// 目前测试的com是office2003
    /// 应用com组件：Microsoft Excel 11.0 Object Library
    /// </summary>
    public partial class ExcelToDataSet : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
          
        }

        protected void btnInputExcel_Click(object sender, EventArgs e)
        {
            string FileType = this.FileUpExcel.PostedFile.ContentType;

            //if (FileType == "application/vnd.ms-excel") 
            if(FileType=="application/octet-stream")
            {
                this.GvExcel.DataSource = CreateDataSource();
                this.GvExcel.DataBind();
            }
            else
            {
                this.lbMsg.Text = "<font color='red'>请输入Excel文件！</font>";
            }

        }
        //创建数据源
        private DataSet CreateDataSource()
        {
            //客户端路径
            //string path = this.FileUpExcel.PostedFile.FileName;
            string path = (string)Session["path"];
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";
            OleDbConnection olecon = new OleDbConnection(strCon);
            //OleDbDataAdapter myda = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strCon);
            OleDbDataAdapter myda = new OleDbDataAdapter("SELECT * FROM [" + this.ddlExcelSheetName.SelectedValue + "$]", strCon);
            DataSet myds = new DataSet();
            myda.Fill(myds);
            return myds;
        }

        //加载sheet表名到dropdown中
        protected void AddSheetNameToDrop()
        {
            this.ddlExcelSheetName.Items.Clear();//清空DropDownList中的值
            if (this.FileUpExcel.HasFile)
            {
                string pathName = this.FileUpExcel.PostedFile.FileName;
                Session["path"] = pathName;
                object MissingValue = Type.Missing;
                Excel.Application ep = new Excel.ApplicationClass();
                Excel.Workbook ew = ep.Workbooks.Open(pathName, MissingValue,
                MissingValue, MissingValue, MissingValue,
                MissingValue, MissingValue, MissingValue,
                MissingValue, MissingValue, MissingValue,
                MissingValue, MissingValue, MissingValue,
                MissingValue);
                // Excel.Worksheet ews;
                int count = ew.Worksheets.Count;
                //读出表的数量
                //Response.Write(count.ToString());
                //遍历出excel文件中的所有的表名
                for (int i = 1; i <= count; i++)
                {
                    // ews = (Excel.Worksheet)ew.Worksheets[i];
                    string sheetName = ((Excel.Worksheet)ew.Worksheets[i]).Name;
                    //绑定到ddl控件中
                    this.ddlExcelSheetName.Items.Add(sheetName);
                }
                //把excel进程干掉，恨死我拉搞了n长时间
                ew.Close(false, Type.Missing, Type.Missing);
                ep.Workbooks.Close();
                ep.Quit();         
                System.Runtime.InteropServices.Marshal.ReleaseComObject(ep);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(ew);
                ep = null;
                ew = null;
                GC.Collect();//强制对所有代码垃圾进行回收   
            }
        }

        protected void btnAddSheet_Click(object sender, EventArgs e)
        {
            AddSheetNameToDrop();
        }
              
    }
}