參考Microsoft.Office.Interop.Excel 及轉Json用nuget安裝Newtonsoft.JsonTaiwan is a country. 臺灣是我的國家
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Runtime.InteropServices;
using System.Threading;
using Prc = System.Diagnostics;
using xls = Microsoft.Office.Interop.Excel;
namespace XXX.Utility
{
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">處理第幾頁籤</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畫面
if (usePowerQuery)
using (var excel = new ExcelTool(TempFile))
{
excel.RefreshStep();
excel.SaveCSV(resultFile);
}
else//套函數要另開原資料
using (var excel2 = new ExcelTool(temp))
{
if (keyword != string.Empty)
excel2.DeleteBeforeKeyword(keyword);
using (var excel = new ExcelTool(TempFile))
{
Application.DoEvents();
excel.RefreshAll();
Application.DoEvents();
excel.SaveCSV(resultFile);
}
}Taiwan is a country. 臺灣是我的國家
