C#封裝的VSTO Excel操作類(20180903更新)

自己在用的Excel操作類，因為經常在工作中要操作Excel文件，可是使用vba實現起來實在是不方便，而且編寫也很困難，拼接一個字元串都看的眼花。

這個時候C#出現了，發現使用C#來操作Excel非常方便，比VBA不知道高到哪裡去了，而且直接就可以上手，所以我就把常用的一些操作封裝成了一個類，編譯成DLL方便在各個項目中調用。

其實使用第三方控制項也可以實現相應的功能，而且某些控制項也是使用Visual Studio Tools for Office (VSTO)中同樣風格的介面，直接就可以上手，不過好用的都是要付費的。這裡不做討論。

首先要添加程式集引用：Microsoft.Office.Interop.Excel，因為我們使用的是OFFICE2016，所以選擇15.0.0.0版本。

只要繼承Excel這個抽象類並實現handler方法即可。

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Diagnostics;
  4 using System.Runtime.InteropServices;
  5 using Microsoft.Office.Interop.Excel;
  6 using System.IO;
  7 using static System.IO.File;
  8 
  9 namespace ExcelHelper
 10 {
 11     /*
 12      2018-08-17 13:43:53
 13      [email protected]
 14          */
 15     /// <summary>
 16     /// Excel抽象類，封裝了常用的方法，只需要實現Hanlder方法即可。
 17     /// </summary>
 18     public abstract class Excel
 19     {
 20         /// <summary>
 21         /// 實例化Excel對象
 22         /// </summary>
 23         /// <param name="debugMode">設置Debug模式（Excel可見性，屏幕刷新，不提示警告窗體）</param>
 24         protected Excel(bool debugMode = true)
 25         {
 26             try
 27             {
 28                 ExcelApp = GetExcelApplication();
 29                 DebugMode = debugMode;
 30             }
 31             catch (InvalidCastException)
 32             {
 33                 throw new COMException("對不起,沒有獲取到本機安裝的Excel對象，請嘗試修複或者安裝Office2016後使用本軟體！");
 34             }
 35         }
 36 
 37         /// <summary>
 38         /// 顯示Excel視窗
 39         /// </summary>
 40         public void Show()
 41         {
 42             if (!ExcelApp.Visible)
 43             {
 44                 ExcelApp.Visible = true;
 45             }
 46         }
 47 
 48         /// <summary>
 49         /// 獲取Excel對象，如果不存在則打開
 50         /// </summary>
 51         /// <returns>返回一個Excel對象</returns>
 52         public Application GetExcelApplication()
 53         {
 54             Application application;
 55             try
 56             {
 57                 application = (Application)Marshal.GetActiveObject("Excel.Application");//嘗試取得正在運行的Excel對象
 58                 Debug.WriteLine("Get Running Excel");
 59             }
 60             //沒有打開Excel則會報錯
 61             catch (COMException)
 62             {
 63                 application = CreateExcelApplication();//打開Excel
 64                 Debug.WriteLine("Create new Excel");
 65             }
 66             Debug.WriteLine(application.Version);//列印Excel版本
 67             return application;
 68         }
 69 
 70         /// <summary>
 71         /// 創建一個Excel對象
 72         /// </summary>
 73         /// <param name="visible">是否顯示Excel，預設為True</param>
 74         /// <param name="caption">標題欄</param>
 75         /// <returns>返回創建好的Excel對象</returns>
 76         public Application CreateExcelApplication(bool visible = true, string caption = "New Application")
 77         {
 78             var application = new Application
 79             {
 80                 Visible = visible,
 81                 Caption = caption
 82             };
 83             return application;
 84         }
 85 
 86         /// <summary>
 87         /// 退出Excel
 88         /// </summary>
 89         public void Exit()
 90         {
 91             if (ExcelApp.Workbooks.Count > 0)
 92             {
 93                 ExcelApp.DisplayAlerts = false;
 94                 ExcelApp.Workbooks.Close(); //關閉所有工作簿
 95             }
 96             ExcelApp.Quit(); //退出Excel
 97             ExcelApp.DisplayAlerts = true;
 98         }
 99         /// <summary>
100         /// 殺死Excel進程
101         /// </summary>
102         public void Kill()
103         {
104             if (ExcelApp.Workbooks.Count > 0)
105             {
106                 ExcelApp.DisplayAlerts = false;
107                 ExcelApp.Workbooks.Close(); //關閉所有工作簿
108             }
109             ExcelApp.Quit();
110             GC.Collect();
111             KeyMyExcelProcess.Kill(ExcelApp);
112         }
113         /// <summary>
114         /// Excel實例對象
115         /// </summary>
116         public Application ExcelApp { get; }
117 
118         /// <summary>
119         /// 獲取workbook對象
120         /// </summary>
121         /// <param name="name">工作簿全名</param>
122         /// <returns></returns>
123         public Workbook GetWorkbook(string name)
124         {
125             var wbk = ExcelApp.Workbooks[name];
126             return wbk;
127         }
128 
129         /// <summary>
130         /// 獲取workbook對象
131         /// </summary>
132         /// <param name="index">索引</param>
133         /// <returns></returns>
134         public Workbook GetWorkbook(int index)
135         {
136             var wbk = ExcelApp.Workbooks[index];
137             return wbk;
138         }
139 
140         /// <summary>
141         /// 獲取workbook活動對象
142         /// </summary>
143         /// <returns></returns>
144         public Workbook GetWorkbook()
145         {
146             var wbk = ExcelApp.ActiveWorkbook;
147             return wbk;
148         }
149 
150         /// <summary>
151         /// 打開工作簿
152         /// </summary>
153         /// <param name="path"></param>
154         /// <returns></returns>
155         public Workbook OpenFromFile(string path)
156         {
157             var workbook = ExcelApp.Workbooks.Open(path);
158             return workbook;
159         }
160 
161         /// <summary>
162         /// 添加工作簿
163         /// </summary>
164         /// <returns></returns>
165         public Workbook AddWorkbook()
166         {
167             var workbook = ExcelApp.Workbooks.Add();
168             return workbook;
169         }
170 
171         /// <summary>
172         /// 保存工作簿
173         /// </summary>
174         /// <param name="workbook"></param>
175         /// <param name="path"></param>
176         public void SaveWorkbook(Workbook workbook, string path)
177         {
178             workbook.SaveAs(path);
179         }
180 
181         /// <summary>
182         /// 關閉工作簿
183         /// </summary>
184         /// <param name="workbook"></param>
185         public void CloseWorkbook(Workbook workbook)
186         {
187             workbook.Close(false, Type.Missing, Type.Missing);
188         }
189 
190         /// <summary>
191         /// 打開或者查找表
192         /// </summary>
193         /// <param name="path"></param>
194         /// <param name="filename"></param>
195         /// <returns></returns>
196         public Workbook OpenAndFindWorkbook(string path, string filename)
197         {
198             var pathFull = Path.Combine(path, filename);
199             string fileName;
200             if (!Exists(pathFull))
201             {
202                 pathFull = Directory.GetFiles(path, filename)[0];
203                 fileName = Path.GetFileName(pathFull);
204             }
205             else
206             {
207                 fileName = Path.GetFileName(filename);
208             }
209 
210 
211             Workbook res = null;
212             //遍歷所有已打開的工作簿
213             foreach (Workbook ws in ExcelApp.Workbooks)
214             {
215                 if (ws.Name != fileName) continue;
216                 res = GetWorkbook(fileName); //OpenFromFile(umts_path).Worksheets[1];
217                 break;
218             }
219 
220             //如果沒有找到就直接打開文件
221             return res ?? (OpenFromFile(pathFull));
222         }
223 
224         /// <summary>
225         /// 打開或者查找表
226         /// </summary>
227         /// <param name="filename">文件名全路徑</param>
228         /// <returns></returns>
229         public Workbook OpenAndFindWorkbook(string filename)
230         {
231             var pathFull = filename;
232             string fileName;
233             var path = Path.GetDirectoryName(filename);
234             if (!Exists(pathFull))
235             {
236                 pathFull = Directory.GetFiles(path ?? throw new InvalidOperationException(), filename)[0];
237                 fileName = Path.GetFileName(pathFull);
238             }
239             else
240             {
241                 fileName = Path.GetFileName(filename);
242             }
243 
244 
245             Workbook res = null;
246             //遍歷所有已打開的工作簿
247             foreach (Workbook ws in ExcelApp.Workbooks)
248             {
249                 if (ws.Name != fileName) continue;
250                 res = GetWorkbook(fileName); //OpenFromFile(umts_path).Worksheets[1];
251                 break;
252             }
253 
254             //如果沒有找到就直接打開文件
255             return res ?? (OpenFromFile(pathFull));
256         }
257 
258         /// <summary>
259         /// 複製列到另一張表
260         /// </summary>
261         /// <param name="sourceWorksheet">源表</param>
262         /// <param name="sourceRows">源列</param>
263         /// <param name="sourceStart">起始位置</param>
264         /// <param name="newWorksheet">目的表</param>
265         /// <param name="newRows">目的列</param>
266         /// <param name="newStart">目的位置</param>
267         public void CopyRow2OtherSheet(Worksheet sourceWorksheet, string[] sourceRows, int sourceStart,
268             Worksheet newWorksheet, string[] newRows, int newStart)
269         {
270             int intrngEnd = GetEndRow(sourceWorksheet);
271             if (newRows != null && (sourceRows != null && sourceRows.Length == newRows.Length))
272             {
273                 for (int i = 0; i < sourceRows.Length; i++)
274                 {
275                     var rg = sourceRows[i] + sourceStart + ":" + sourceRows[i] + intrngEnd;
276                     sourceWorksheet.Range[rg]
277                         .Copy(newWorksheet.Range[newRows[i] + newStart]);
278                     //  new_worksheet.Cells[65536, new_rows[i]].End[XlDirection.xlUp].Offset(1, 0).Resize(intrngEnd, 1).Value = source_worksheet.Cells[2, source_rows[i]].Resize(intrngEnd, new_rows[i]).Value;
279                 }
280             }
281             else
282             {
283                 Console.WriteLine("Error source_rows length not is new_rows length!");
284             }
285         }
286 
287         /// <summary>
288         /// 複製列到另一張表
289         /// </summary>
290         /// <param name="sourceWorksheet">源表</param>
291         /// <param name="sourceRows">源列</param>
292         /// <param name="sourceStart">起始位置</param>
293         /// <param name="newWorksheet">目的表</param>
294         /// <param name="newRows">目的列</param>
295         /// <param name="newStart">目的位置</param>
296         public void CopyRow2OtherSheet(Worksheet sourceWorksheet, int[] sourceRows, int sourceStart, Worksheet newWorksheet,
297             int[] newRows, int newStart)
298         {
299             int intrngEnd = GetEndRow(sourceWorksheet);
300             if (sourceRows.Length == newRows.Length)
301             {
302                 for (int i = 0; i < sourceRows.Length; i++)
303                 {
304                     newWorksheet.Cells[65536, newRows[i]].End[XlDirection.xlUp].Offset(sourceStart, 0).Resize(intrngEnd, sourceStart)
305                         .Value = sourceWorksheet.Cells[newStart, sourceRows[i]].Resize(intrngEnd, newRows[i]).Value;
306                 }
307             }
308             else
309             {
310                 Console.WriteLine("Error source_rows length not is new_rows length!");
311             }
312         }
313 
314         /// <summary>
315         /// 複製表頭到另一個sheet中
316         /// </summary>
317         /// <param name="sourceWorksheet">表頭所在的sheet</param>
318         /// <param name="newWorksheet">要複製到的sheet</param>
319         /// <param name="start">起始位置</param>
320         public void CopyHeader(Worksheet sourceWorksheet, Worksheet newWorksheet, int start = 1)
321         {
322             if (sourceWorksheet.Rows != null)
323                 sourceWorksheet.Rows[start].Copy(newWorksheet.Cells[1, 1]); //把數據表的表頭複製到新表中
324         }
325 
326         /// <summary>
327         /// 設置特定列的數據
328         /// </summary>
329         /// <param name="worksheet">源表</param>
330         /// <param name="row">要設置的列號</param>
331         /// <param name="len">長度</param>
332         /// <param name="value">要設的值</param>
333         /// ///
334         public void SetSheetRow(Worksheet worksheet, int row, int len, string value)
335         {
336             //int intrngEnd = this.GetEndRow(worksheet);//取特定列最後一列的長度
337             worksheet.Cells[65536, row].End[XlDirection.xlUp].Offset(1, 0).Resize(len, 1).Value = value;
338         }
339 
340         /// <summary>
341         /// 取有效列的最後一列的長度
342         /// </summary>
343         /// <param name="worksheet"></param>
344         /// <returns></returns>
345         public int GetEndRow(Worksheet worksheet)
346         {
347             int res = worksheet.UsedRange.Rows.Count;
348             return res;
349         }
350 
351         /// <summary>
352         /// 插入圖片
353         /// </summary>
354         /// <param name="path">圖片路徑</param>
355         /// <param name="worksheet">要插入的表</param>
356         /// <param name="range">要插入的range</param>
357         public void AddPic(string path, Worksheet worksheet, Range range)
358         {
359             this.AddPic(path, worksheet, range, range.Width, range.Height);
360         }
361 
362         /// <summary>
363         /// 插入圖片
364         /// </summary>
365         /// <param name="path">圖片路徑</param>
366         /// <param name="worksheet">要插入的表</param>
367         /// <param name="range">要插入的range</param>
368         /// <param name="width">圖片的寬度</param>
369         /// <param name="height">圖片的高度</param>
370         public void AddPic(string path, Worksheet worksheet, Range range, int width, int height)
371         {
372             worksheet.Shapes.AddPicture(path, Microsoft.Office.Core.MsoTriState.msoCTrue,
373                     Microsoft.Office.Core.MsoTriState.msoCTrue, range.Left, range.Top, width, height).Placement =
374                 XlPlacement.xlMoveAndSize;
375         }
376 
377         /// <summary>
378         /// 批量插入圖片
379         /// </summary>
380         /// <param name="pngdic">單元格範圍-圖片名</param>
381         /// <param name="imgBase">圖片根目錄</param>
382         /// <param name="worksheet">要插入圖片的worksheet</param>
383         /// <returns>返回處理好的圖片日誌</returns>
384         public string InsertMultipleImages(Dictionary<string, string> pngdic, string imgBase, Worksheet worksheet)
385         {
386             string msg = null;
387             foreach (var s in pngdic)
388             {
389                 string imgPath = Path.Combine(imgBase, s.Value);
390                 if (!Exists(imgPath))
391                 {
392                     continue;
393                 }
394 
395                 Range range = worksheet.Range[s.Key];
396                 AddPic(imgPath, worksheet, range);
397                 msg = s.Value + "\t" + s.Key + "\t\t\t" + range.Left.ToString() + "\t" + range.Top.ToString() + "\n";
398             }
399 
400             return msg;
401         }
402 
403         /// <summary>
404         /// 主要實現這個方法
405         /// </summary>
406         /// <param name="path">要打開的文件路徑</param>
407         public abstract void Handler(string path = null);
408         /// <summary>
409         /// 開啟或者關閉屏幕刷新
410         /// </summary>
411         public bool ScreenUpdating
412         {
413             get => ExcelApp.ScreenUpdating;
414             set => ExcelApp.ScreenUpdating = value;
415         }
416         /// <summary>
417         /// Excel可見性
418         /// </summary>
419         public bool Visible
420         {
421             get => ExcelApp.Visible;
422             set => ExcelApp.Visible = value;
423         }
424         /// <summary>
425         /// 是否顯示警告窗體
426         /// </summary>
427         public bool DisplayAlerts
428         {
429             get => ExcelApp.DisplayAlerts;
430             set => ExcelApp.DisplayAlerts = value;
431         }
432         private bool