欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

C#封装的VSTO Excel操作类(20180903更新)

程序员文章站 2024-01-22 13:08:34
自己在用的Excel操作类,因为经常在工作中要操作Excel文件,可是使用vba实现起来实在是不方便,而且编写也很困难,拼接一个字符串都看的眼花。 这个时候C#出现了,发现使用C#来操作Excel非常方便,比VBA不知道高到哪里去了,而且直接就可以上手,所以我就把常用的一些操作封装成了一个类,编译成 ......

自己在用的excel操作类,因为经常在工作中要操作excel文件,可是使用vba实现起来实在是不方便,而且编写也很困难,拼接一个字符串都看的眼花。

这个时候c#出现了,发现使用c#来操作excel非常方便,比vba不知道高到哪里去了,而且直接就可以上手,所以我就把常用的一些操作封装成了一个类,编译成dll方便在各个项目中调用。

其实使用第三方控件也可以实现相应的功能,而且某些控件也是使用visual studio tools for office (vsto)中同样风格的接口,直接就可以上手,不过好用的都是要付费的。这里不做讨论。

 

首先要添加程序集引用:microsoft.office.interop.excel,因为我们使用的是office2016,所以选择15.0.0.0版本。

C#封装的VSTO Excel操作类(20180903更新)

 

只要继承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      luoc@zhiweicl.com
 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 _debugmode;
433         /// <summary>
434         /// 设置debug模式
435         /// </summary>
436         public bool debugmode
437         {
438             get => _debugmode;
439             set
440             {
441                 _debugmode = value;
442                 //设置是否显示警告窗体
443                 displayalerts = value;
444                 //设置是否显示excel
445                 visible = value;
446                 //禁止刷新屏幕
447                 screenupdating = value;
448             }
449         }
450     }
451     /// <summary>
452     /// 关闭excel进程
453     /// </summary>
454     public class keymyexcelprocess
455     {
456         [dllimport("user32.dll", charset = charset.auto)]
457         public static extern int getwindowthreadprocessid(intptr hwnd, out int id);
458         public static void kill(application excel)
459         {
460             try
461             {
462                 intptr t = new intptr(excel.hwnd);   //得到这个句柄,具体作用是得到这块内存入口
463                 getwindowthreadprocessid(t, out var k);   //得到本进程唯一标志k
464                 system.diagnostics.process p = system.diagnostics.process.getprocessbyid(k);   //得到对进程k的引用
465                 p.kill();     //关闭进程k
466             }
467             catch (exception e)
468             {
469                 console.writeline(e.message);
470             }
471 
472         }
473     }
474 }