愛比的新手筆記

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;

namespace MailProcess.Utility
{
    class ExcelTool : IDisposable
    {
        private Excel.Application app = new Excel.Application() { DisplayAlerts = false };//多執行緒可能會打架, 所以物件new一個
        public Excel.Workbook Workbook { get; private set; }

        #region constructor
        public ExcelTool(string path)
        {
            Workbook = app.Workbooks.Open(path);
        }
        #endregion

        #region Method
        /// <summary>
        /// 存csv檔
        /// </summary>
        /// <param name="path"></param>
        public void SaveCSV(string path)
        {
            Workbook.SaveAs(path, Excel.XlFileFormat.xlCSV);
        }

        /// <summary>
        /// 將指定的Worksheet資料轉json
        /// </summary>
        /// <param name="worksheet">worksheet name</param>
        /// <param name="onRow">after create every Dictionary</param>
        /// <returns></returns>
        public string ToJson(string worksheet, EventHandler onRow)
        {
            Excel.Worksheet ws = string.IsNullOrEmpty(worksheet) ? Workbook.Sheets[1] : Workbook.Sheets[worksheet];
            Excel.Range rg = ws.UsedRange;
            object[,] data = rg.Value2;
            Dictionary<string, string>[] dics = new Dictionary<string, string>[rg.Rows.Count - 1];
            for (int i = 2; i <= rg.Rows.Count; i++)
            {
                var dic = new Dictionary<string, string>();
                onRow?.Invoke(dic, null);
                for (int j = 1; j <= rg.Columns.Count; j++)
                {
                    var key = Convert.ToString(data[1, j]).Trim();
                    var val = Convert.ToString(data[i, j]);
                    if (!string.IsNullOrEmpty(key) && !string.IsNullOrEmpty(val))
                        dic.Add(key, val);
                }
                dics[i - 2] = dic;
            }
            return JsonConvert.SerializeObject(dics);
        }

        /// <summary>
        /// 將指定Worksheet轉DataTable
        /// </summary>
        /// <param name="worksheet">worksheet name</param>
        /// <param name="hasCol">has column name</param>
        /// <returns></returns>
        public DataTable ToDataTable(string worksheet, bool hasCol = true)
        {
            DataTable dt = new DataTable();
            var ws = Workbook.Worksheets[worksheet];
            Excel.Range rg = ws.UsedRange;
            object[,] data = rg.Value2;

            for (int j = 1; j <= rg.Columns.Count; j++)//add columns
            {
                var name = hasCol ? Convert.ToString(data[1, j]).Trim() : "Column" + j;
                if (string.IsNullOrEmpty(name)) break;
                dt.Columns.Add(name, typeof(string));
            }

            for (int i = hasCol ? 2 : 1; i <= rg.Rows.Count; i++)//add rows
            {
                bool HasData = false;
                DataRow r = dt.NewRow();
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    var val = Convert.ToString(data[i, j + 1]);
                    if (!string.IsNullOrEmpty(val))
                    {
                        HasData = true;
                        r[j] = val;
                    }
                }
                if (HasData)
                    dt.Rows.Add(r);
            }
            return dt;
        }

        public void Dispose()
        {
            if (Workbook != null)
                Workbook.Close(false);
            if (app != null)
                app.Quit();
        }
        #endregion
    }
}

Taiwan is a country. 臺灣是我的國家