C#讀取Excel方法總結

在c#中，有2種方法可以讀取Excel。

一、使用OleDb

/// using System.Data.OleDb
/// <summary>
/// 返回Excel資料來源
/// </summary>
/// <param name="filename">檔案路徑</param>
/// <returns></returns>
static public DataSet ExcelToDataSet(string filename)
    {
        DataSet ds;
        string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                        "Extended Properties=Excel 8.0;" +
                        "data source=" + filename;
        OleDbConnection myConn = new OleDbConnection(strCon);
        string strCom = " SELECT * FROM [Sheet1$]";
        myConn.Open();
        OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
        ds = new DataSet();
        myCommand.Fill(ds);
        myConn.Close();
        return ds;
    }

二、使用COM

///匯入Microsoft.Excel
///using Excel= Microsoft.Office.Interop.Excel;
///using System.Diagnostics;

public class ExcelHelper
{
    private Excel._Application excelApp;
    private string fileName = string.Empty;
    private Excel.WorkbookClass wbclass;
    public ExcelHelper(string _filename)
    {
        excelApp = new Excel.Application();
        object objOpt = System.Reflection.Missing.Value;
        wbclass = (Excel.WorkbookClass)excelApp.Workbooks.Open(_filename, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);
    }
    /**/
    /// <summary>
    /// 所有sheet的名稱列表
    /// </summary>
    /// <returns></returns>
    public List<string> GetSheetNames()
    {
        List<string> list = new List<string>();
        Excel.Sheets sheets = wbclass.Worksheets;
        string sheetNams = string.Empty;
        foreach (Excel.Worksheet sheet in sheets)
        {
            list.Add(sheet.Name);
        }
        return list;
    }
    public Excel.Worksheet GetWorksheetByName(string name)
    {
        Excel.Worksheet sheet = null;
        Excel.Sheets sheets = wbclass.Worksheets;
        foreach (Excel.Worksheet s in sheets)
        {
            if (s.Name == name)
            {
                sheet = s;
                break;
            }
        }
        return sheet;
    }
    /**/
    /// <summary>
    ///
    /// </summary>
    /// <param name="sheetName">sheet名稱</param>
    /// <returns></returns>
    public Array GetContent(string sheetName)
    {
        Excel.Worksheet sheet = GetWorksheetByName(sheetName);
        //獲取A1 到AM24範圍的單元格
        Excel.Range rang = sheet.get_Range("A1", "AM24");
        //讀一個單元格內容
        //sheet.get_Range("A1", Type.Missing);
        //不為空的區域,列,行數目
        //   int l = sheet.UsedRange.Columns.Count;
        // int w = sheet.UsedRange.Rows.Count;
        //  object[,] dell = sheet.UsedRange.get_Value(Missing.Value) as object[,];
        System.Array values = (Array)rang.Cells.Value2;
        return values;
    }

    public void Close()
    {
        excelApp.Quit();
        excelApp = null;
    }

}