C# 操作EXCEL 类 可以实现导入导出功能 折腾了好长时间 现在两种方法显示出来

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

using System.Runtime.InteropServices;
using System.Data.OleDb;

namespace MyHelper
{

 

   /// 说明如下注释的是另一种方法。。。大多数情况下能用一般要求要安装office2003 

  // 报错情况下要求要组件注册如果。你的组件服务实在启动不了。又找不到其他方法建议选择现在这种方法
    public class ExcelOper
    {
     
    

        [DllImport("kernel32")]  
        public static extern void GetWindowsDirectory(System.Text.StringBuilder WinDir, int count);
        /// <summary>
        /// 在指定的ComboBox中帮定某个Excel文件中的Sheet页面名称
        /// </summary>
        /// <param name="frm">窗体</param>
        /// <param name="cb">ComboBox</param>
        /// <param name="ExelFileName">Excel文件全路径名</param>
        public static void FillExcelSheetToCombo(System.Web.UI.WebControls.DropDownList ddl, string ExelFileName)
        {
            try
            {
                ddl.Items.Clear();
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ExelFileName + ";" + "Extended Properties=Excel 8.0;";
                System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
                conn.Open();
                System.Data.DataTable tb = new System.Data.DataTable();
                tb = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                for (int k = 0; k < tb.Rows.Count; k++)
                {
                    ddl.Items.Add(tb.Rows[k]["TABLE_NAME"].ToString().Replace("'", "").Replace("$", ""));
                }

                //Microsoft.Office.Interop.Excel = Excel
                //Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                //app.Visible = false;
                //Microsoft.Office.Interop.Excel.Workbook wkb = app.Workbooks.Open(ExelFileName,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing
                //                                                                   );
                //foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in wkb.Sheets)
                //{
                //    ddl.Items.Add(sheet.Name);
                //}
                //app.Quit();

    
            }
            catch (Exception ex)
            {
                HttpContext.Current.Response.Write(ex.Message+ex.StackTrace);
            }
        }

 

        /// </summary>
        /// <param name="ExcelFileName">Excel文件</param>
        /// <param name="SheetName">Sheet名</param>
        /// <returns>null：出错 ,否则返回DataTable</returns>
        public static System.Data.DataTable ExcelToDSStuct(string ExcelFileName, string SheetName)
        {
            Microsoft.Office.Interop.Excel.Application app = null;
            try
            {
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ExcelFileName + ";" + "Extended Properties=Excel 8.0;";
                System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
                conn.Open();
                System.Data.DataTable tb = new System.Data.DataTable();
                string strExcel = "select * from [" + SheetName + "$] ";
                System.Data.OleDb.OleDbDataAdapter adpt = new System.Data.OleDb.OleDbDataAdapter(strExcel, strConn);
                adpt.Fill(tb);
                conn.Close();
                return tb;

                //app = new Microsoft.Office.Interop.Excel.Application();
                //app.Visible = false;
                打开Excel文件
                //Microsoft.Office.Interop.Excel.Workbook wkb = app.Workbooks.Open(ExcelFileName,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing,
                //                                                                   Type.Missing
                //                                                                   );
                //Microsoft.Office.Interop.Excel.Worksheet sht = null;
                //for (int i = 1; i <= wkb.Sheets.Count; i++)
                //    if (((Microsoft.Office.Interop.Excel.Worksheet)wkb.Sheets[i]).Name == SheetName)
                //        sht = (Microsoft.Office.Interop.Excel.Worksheet)wkb.Sheets[i];
                //if (sht == null)
                //{
                //    app.Quit();
                //    return null;
                //}

                生成Table
                //System.Data.DataTable tb = new System.Data.DataTable();
                生成Table表头

                //for (int i = 1; i <= sht.UsedRange.Columns.Count; i++)
                //{
                //    tb.Columns.Add(((Microsoft.Office.Interop.Excel.Range)sht.Cells[1, i]).Text.ToString(), "".GetType());
                //}
                填写数据
                //for (int i = 2; i <= sht.UsedRange.Rows.Count; i++)
                //{

                //    System.Data.DataRow r = tb.Rows.Add();
                //    for (int j = 1; j <= sht.UsedRange.Columns.Count; j++)
                //    {
                //        Microsoft.Office.Interop.Excel.Range c = (Microsoft.Office.Interop.Excel.Range)sht.Cells[i, j];
                //        //  c.NumberFormat = "@";
                //        r[j - 1] = c.Text.ToString();
                //    }
                //}
                //app.Quit();
                //return tb;

            }
            catch
            {
                if (app != null)
                    app.Quit();
                return null;
            }

        }

    }
}