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版本。
只要继承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 }
推荐阅读
-
C#封装的常用文件操作类实例
-
C#实现的Excel文件操作类实例
-
C#使用iTextSharp封装的PDF文件操作类实例
-
C#封装的常用文件操作类实例
-
C#使用iTextSharp封装的PDF文件操作类实例
-
C#实现的Excel文件操作类实例
-
C#实现的文件操作封装类完整实例【删除,移动,复制,重命名】
-
C#_Excel数据读取与写入_自定义解析封装类_支持设置标题行位置&使用excel表达式收集数据&单元格映射&标题映射&模板文件的参数数据替换(第二版-增加深度读取和更新功能)
-
C#(.NET)数据访问连接、查询、插入等操作的封装类
-
C#实现的文件操作封装类完整实例【删除,移动,复制,重命名】