~楓花雪岳~: [C#] 利用 OleDB 來存取 Excel

星期六, 12月 28, 2019

[C#] 利用 OleDB 來存取 Excel

學習在 C# 上透過 OleDB 存取 Excel

Sample Excel Data

Sample Code

// 必須加入該命名空間
using System.Data.OleDb;

namespace XLSByOleDB
{
    class Program
    {
        static void Main(string[] args)
        {
            string FileFullName = @"D:\XlsDemo.xlsx";

            string ExcelConnectionString = GetExcelConnectionString(FileFullName);

            DataTable sheetsInfo = GetExcelSheetNames(ExcelConnectionString);

            string sheetName;
            foreach (DataRow sheet in sheetsInfo.Rows)
            {
                sheetName = sheet["Table_Name"].ToString();
                // OR sheetName = sheet[2].ToString();
                Console.WriteLine($"SheetName：{sheetName}　相關資料");

                DataTable data = GetExcelData(ExcelConnectionString, sheetName);
                foreach (DataRow row in data.Rows)
                {
                    // 欄位1 為該 Sheet 第一筆資料，也就是欄位名稱
                    Console.WriteLine(row["欄位1"].ToString());
                }
            }
        }

        /// <summary>
        /// 產生 Excel 檔案連線字串
        /// </summary>
        /// <param name="FileFullName">Excel 檔案完整路徑</param>
        /// <returns>Excel 檔案連線字串</returns>
        private static string GetExcelConnectionString(string FileFullName)
        {
            OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder
            {
                Provider = "Microsoft.ACE.OLEDB.12.0",
                DataSource = FileFullName
            };

            string Extension = Path.GetExtension(FileFullName);
            string ExcelVersion;
            string XML;
            if (Extension == ".xlsx")
            {
                ExcelVersion = "12.0";
                XML = nameof(XML);
            }
            else
            {
                ExcelVersion = "8.0";
                XML = string.Empty;
            }
            builder["Extended Properties"] = $"Excel {ExcelVersion} {XML};HDR=YES;IMEX=1";

            return builder.ConnectionString;
        }

        /// <summary>
        /// 抓取 Excel 內全部 Sheet 
        /// </summary>
        /// <param name="connectionString">Excel 檔案連線字串</param>
        /// <returns>Excel 內全部 Sheet</returns>
        private static DataTable GetExcelSheetNames(string connectionString)
        {
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                // OR
                // DataTable dt = conn.GetSchema("Tables");
                return dt;
            }
        }

        /// <summary>
        /// 取得指定 Sheet 內資料
        /// </summary>
        /// <param name="connectionString">Excel 檔案連線字串</param>
        /// <param name="sheetName">指定 Sheet 名稱</param>
        /// <returns>指定 Sheet 內資料</returns>
        private static DataTable GetExcelData(string connectionString, string sheetName)
        {
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                // Sheet名稱，必需用中括號 [] 包起來
                string TSQL = $"SELECT * FROM [{sheetName}]";
                OleDbDataAdapter da = new OleDbDataAdapter(TSQL, connectionString);
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
        }

    }
}

結果