C# OleDb讀取Excel資料

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;

namespace SYS_TEST.BaseClass
{
    ///OleDB方式
    ///優點：将Excel直接當做資料源處理，通過SQL直接讀取内容，讀取速度較快。
    ///缺點：讀取資料方式不夠靈活，無法直接讀取某一個單元格，隻有将整個Sheet頁讀取出來後（結果為Datatable）再在Datatable中根據行列數來擷取指定的值。
    public class OLEDBClass
    {
        /// <summary>
        /// 擷取EXCEL的Sheet表名
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static object[] GetExcelSheetName(string fileName)
        {
            ArrayList sheetNames = new ArrayList();
            try
            {
                if (File.Exists(fileName))
                {
                    OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + fileName);
                    conn.Open();
                    DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    conn.Close();
                    for (int i = 0; i != dt.Rows.Count; i++)
                    {
                        sheetNames.Add(dt.Rows[i]["Table_Name"].ToString());
                    }
                }
            }
            catch { }
            return sheetNames.ToArray();
        }
        /// <summary>
        /// Excel轉換成DataSet
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static DataSet ExcelToDataSet(string fileName)
        {
            DataSet ds = new DataSet();
            try
            {
                if (File.Exists(fileName))
                {
                    OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + fileName);
                    conn.Open();
                    string comm = "select * from [sheet1$]";
                    OleDbDataAdapter myCommand = new OleDbDataAdapter(comm, conn);
                    myCommand.Fill(ds, "table1");
                    conn.Close();
                }
            }
            catch { }
            return ds;
        }
        /// <summary>
        /// DataSet更新到Excel
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="oldDs"></param>
        public static void DataSetToExcel(string fileName, DataSet oldDs)
        {
            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + fileName);
            conn.Open();
            string comm = "select * from [sheet1$]";
            OleDbDataAdapter myCommand = new OleDbDataAdapter(comm, conn);
            OleDbCommandBuilder builder = new OleDbCommandBuilder(myCommand);
            //QuotePrefix和QuoteSuffix主要是對builder生成InsertComment指令時使用。
            builder.QuotePrefix = "[";//擷取insert語句中保留字元（起始位置）
            builder.QuoteSuffix = "]";//擷取insert語句中保留字元（結束位置）

            DataSet newDs = new DataSet();
            myCommand.Fill(newDs, "table1");
            for (int i = 0; i < oldDs.Tables[0].Rows.Count; i++)
            {
                //此處不能使用ImportRow方法将一行導入到newDs中，因為ImportRow将保留原來DataRow的所有設定(DataRowState狀态不變)。
                //在使用ImportRow後newds内有值但不能更新到Excel中，因為所有導入行的DataRowState!=Added
                DataRow newDr = newDs.Tables["Table1"].NewRow();
                for (int j = 0; j < newDs.Tables[0].Columns.Count; j++)
                {
                    newDr[j] = oldDs.Tables[0].Rows[i][j];
                }
                newDs.Tables["Table1"].Rows.Add(newDr);
            }
            myCommand.Update(newDs, "Table1");
            conn.Close();
        }
        /// <summary>
        /// 将Excel讀取到DataSet（多Sheet表）
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static DataSet ExcelToDataTable(string fileName)
        {
            //選擇源
            string strConn = "";
            strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\"";
            if (Path.GetExtension(fileName).Trim().ToUpper() == ".XLSX")
            {
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
            }
            //定義資料集
            DataSet ds = new DataSet();
            //連接配接資料源
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            for (int i = 0; i < schemaTable.Rows.Count; i++)
            {
                string strSheetName = schemaTable.Rows[i][2].ToString().Trim().Trim('\'');
                if (strSheetName.Contains("$") && !strSheetName.EndsWith("$"))
                    continue;
                if (schemaTable.Rows[i][3].ToString().Trim().ToUpper().Equals("TABLE"))
                {
                    //Sql語句
                    string strExcel = string.Format("select * from [{0}]", strSheetName);
                    //适配到資料源
                    OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
                    adapter.Fill(ds, strSheetName);
                }
            }
            conn.Close();
            //删除空的Excel表單
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                DataTable dt = ds.Tables[i];
                if (dt.Columns.Count == 1 && dt.Columns[0].ColumnName.Trim().ToUpper() == "F1")
                {
                    ds.Tables.Remove(dt);
                    i--;
                }
            }
            return ds;
        }
    }
}