愛比的新手筆記

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Runtime.InteropServices;
using System.Threading;
using xls = Microsoft.Office.Interop.Excel;
using Prc = System.Diagnostics;

class ExcelTool : IDisposable
{
	private static xls.Application app = null;
	public xls.Workbook Workbook { get; private set; }

	#region constructor
	public ExcelTool(string path, bool visible = false)
	{
		int max = 3;//只試3次
		for (int i = 1; i <= max; i++)
			try
			{
				if ((Workbook = Init(path)) != null)
					break;
				else
					Thread.Sleep(1000);
			}
			catch (COMException)
			{
				using (Prc.Process process = Prc.Process.Start("taskkill", "/F /IM excel.exe"))
				{
					process.WaitForExit();
				}
				app = null;
				if (i == max)
					throw;
				Thread.Sleep(1000);
			}
		app.Visible = visible;
	}

	private xls.Application CreateApp()
	{
		try
		{
			return (xls.Application)Marshal.GetActiveObject("Excel.Application");
		}
		catch (COMException)
		{
			return new xls.Application();
		}
	}

	private xls.Workbook Init(string path)
	{//儘量用同一個application,函數link另一張已開啟excel才會更新數值
		app = app ?? CreateApp();
		app.DisplayAlerts = false;
		app.ScreenUpdating = false;
		//如果有開啟就用現成的
		string name = Path.GetFileName(path);
		foreach (xls.Workbook wb in app.Workbooks)
			if (wb.Name == name)
				return wb;
		return app.Workbooks.Open(path);
	}
	#endregion

	#region Method
	/// <summary>
	/// 全部重新整理
	/// </summary>
	public void RefreshAll()
	{
		Workbook.RefreshAll();
		app.CalculateUntilAsyncQueriesDone();
	}

	/// <summary>
	/// 多個powerquery要一個個重新整理才不會卡死
	/// </summary>
	public void RefreshStep()
	{
		foreach (xls.WorkbookConnection con in Workbook.Connections)
		{
			con.Refresh();
			while (con.OLEDBConnection.Refreshing)//power query is OLEDBConnection
				Thread.Sleep(1000);
		}
	}

	/// <summary>
	/// 刪掉關鍵字以前的row
	/// </summary>
	/// <param name="keyword">關鍵字</param>
	/// <param name="sheetIdx">從1起算,目標在第幾sheet</param>
	public void DeleteBeforeKeyword(string keyword, int sheetIdx = 1)
	{
		var worksheet = Workbook.Worksheets[sheetIdx];
		xls.Range foundRange = worksheet.Cells.Find(what: keyword,
													  LookIn: xls.XlFindLookIn.xlValues,
													  LookAt: xls.XlLookAt.xlPart,
													  SearchOrder: xls.XlSearchOrder.xlByRows,
													  SearchDirection: xls.XlSearchDirection.xlNext,
													  MatchCase: false,
													  SearchFormat: false);
		if (foundRange == null) return;
		int i = foundRange.Row;
		for (int j = 1; j < i; j++)
			worksheet.Rows[1].Delete();
	}

	/// <summary>
	/// 存csv檔
	/// </summary>
	/// <param name="path"></param>
	public void SaveCSV(string path)
	{//Local: true 依看到的格式儲存
		Workbook.SaveAs(path, xls.XlFileFormat.xlCSV, Local: true);
		Thread.Sleep(1000);
	}

	/// <summary>
	/// 存xlsx檔
	/// </summary>
	/// <param name="path"></param>
	public void SaveXLSX(string path)
	{
		Workbook.SaveAs(path, xls.XlFileFormat.xlOpenXMLWorkbook);
		Thread.Sleep(1000);
	}

	/// <summary>
	/// 將指定的Worksheet資料轉json
	/// </summary>
	/// <param name="worksheet">worksheet name</param>
	/// <param name="onRow">after create every empty dictionary</param>
	/// <returns></returns>
	public string ToJson(string worksheet, EventHandler onRow)
	{
		xls.Worksheet ws = string.IsNullOrEmpty(worksheet) ? Workbook.Sheets[1] : Workbook.Sheets[worksheet];
		xls.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];
		xls.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);
			Marshal.ReleaseComObject(Workbook);
		}
	}
	#endregion
}

若遇到需要同時開2個Excel更新函數或只開1個excel更新powerquery, 可以這樣寫
ps. excel powerquery使用Table.Buffer函數時 RefreshAll就會卡住, 建議不用此函數或使用RPA操作EXCEL畫面

//要等候而且不卡住UI, 就寫ACTION來處理
Action Refreshed = () => { doing = false; };
Action wait = () =>
{
	while (doing)
	{
		Thread.Sleep(1000);
		Application.DoEvents();
	}
};

if (usePowerQuery)
	using (var excel = new ExcelTool(TempFile))
	{
		excel.RefreshAll(Refreshed);
		wait();
		excel.SaveCSV(resultFile);
	}
else//套函數要另開原資料
	using (var excel2 = new ExcelTool(temp))
	{
		if (keyword != string.Empty)
			excel2.DeleteBeforeKeyword(keyword);
		using (var excel = new ExcelTool(TempFile))
		{
			excel.RefreshAll(Refreshed);
			wait();
			excel.SaveCSV(resultFile);
		}
	}

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