C#封装的VSTO Excel操作类
自己在用的Excel操作类，因为经常在工作中要操作Excel文件，可是使用vba实现起来实在是不方便，而且编写也很困难，拼接一个字符串都看的眼花。
这个时候C#出现了，发现使用C#来操作Excel非常方便，比VBA不知道高到哪里去了，而且直接就可以上手，所以我就把常用的一些操作封装成了一个类，编译成DLL方便在各个项目中调用,或者作为excel加载项，写的不好的轻喷，毕竟我是门外汉一个。
其实使用第三方控件也可以实现相应的功能，而且某些控件也是使用Visual Studio Tools for Office (VSTO)中同样风格的接口，直接就可以上手，不过好用的都是要付费的。这里不做讨论。
Visual Studio里面请安装好office开发组件
首先要添加程序集引用：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 tchivs@163.com 14 */ 15 ///16 /// Excel抽象类，封装了常用的方法，只需要实现Hanlder方法即可。 17 /// 18 public abstract class Excel 19 { 20 /// 21 /// 实例化Excel对象 22 /// 23 /// 设置Debug模式（Excel可见性，屏幕刷新，不提示警告窗体） 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 /// 38 /// 显示Excel窗口 39 /// 40 public void Show() 41 { 42 if (!ExcelApp.Visible) 43 { 44 ExcelApp.Visible = true; 45 } 46 } 47 48 /// 49 /// 获取Excel对象，如果不存在则打开 50 /// 51 /// 返回一个Excel对象 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 /// 71 /// 创建一个Excel对象 72 /// 73 /// 是否显示Excel，默认为True 74 /// 标题栏 75 /// 返回创建好的Excel对象 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 /// 87 /// 退出Excel 88 /// 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 /// 100 /// 杀死Excel进程 101 /// 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 /// 114 /// Excel实例对象 115 /// 116 public Application ExcelApp { get; } 117 118 /// 119 /// 获取workbook对象 120 /// 121 /// 工作簿全名 122 /// 123 public Workbook GetWorkbook(string name) 124 { 125 var wbk = ExcelApp.Workbooks[name]; 126 return wbk; 127 } 128 129 /// 130 /// 获取workbook对象 131 /// 132 /// 索引 133 /// 134 public Workbook GetWorkbook(int index) 135 { 136 var wbk = ExcelApp.Workbooks[index]; 137 return wbk; 138 } 139 140 /// 141 /// 获取workbook活动对象 142 /// 143 /// 144 public Workbook GetWorkbook() 145 { 146 var wbk = ExcelApp.ActiveWorkbook; 147 return wbk; 148 } 149 150 /// 151 /// 打开工作簿 152 /// 153 /// 154 /// 155 public Workbook OpenFromFile(string path) 156 { 157 var workbook = ExcelApp.Workbooks.Open(path); 158 return workbook; 159 } 160 161 /// 162 /// 添加工作簿 163 /// 164 /// 165 public Workbook AddWorkbook() 166 { 167 var workbook = ExcelApp.Workbooks.Add(); 168 return workbook; 169 } 170 171 /// 172 /// 保存工作簿 173 /// 174 /// 175 /// 176 public void SaveWorkbook(Workbook workbook, string path) 177 { 178 workbook.SaveAs(path); 179 } 180 181 /// 182 /// 关闭工作簿 183 /// 184 /// 185 public void CloseWorkbook(Workbook workbook) 186 { 187 workbook.Close(false, Type.Missing, Type.Missing); 188 } 189 190 /// 191 /// 打开或者查找表 192 /// 193 /// 194 /// 195 /// 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 /// 225 /// 打开或者查找表 226 /// 227 /// 文件名全路径 228 /// 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 /// 259 /// 复制列到另一张表 260 /// 261 /// 源表 262 /// 源列 263 /// 起始位置 264 /// 目的表 265 /// 目的列 266 /// 目的位置 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 /// 288 /// 复制列到另一张表 289 /// 290 /// 源表 291 /// 源列 292 /// 起始位置 293 /// 目的表 294 /// 目的列 295 /// 目的位置 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 /// 315 /// 复制表头到另一个sheet中 316 /// 317 /// 表头所在的sheet 318 /// 要复制到的sheet 319 /// 起始位置 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 /// 327 /// 设置特定列的数据 328 /// 329 /// 源表 330 /// 要设置的列号 331 /// 长度 332 /// 要设的值 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 /// 341 /// 取有效列的最后一列的长度 342 /// 343 /// 344 /// 345 public int GetEndRow(Worksheet worksheet) 346 { 347 int res = worksheet.UsedRange.Rows.Count; 348 return res; 349 } 350 351 /// 352 /// 插入图片 353 /// 354 /// 图片路径 355 /// 要插入的表 356 /// 要插入的range 357 public void AddPic(string path, Worksheet worksheet, Range range) 358 { 359 this.AddPic(path, worksheet, range, range.Width, range.Height); 360 } 361 362 /// 363 /// 插入图片 364 /// 365 /// 图片路径 366 /// 要插入的表 367 /// 要插入的range 368 /// 图片的宽度 369 /// 图片的高度 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 /// 378 /// 批量插入图片 379 /// 380 /// 单元格范围-图片名 381 /// 图片根目录 382 /// 要插入图片的worksheet 383 /// 返回处理好的图片日志 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 /// 404 /// 主要实现这个方法 405 /// 406 /// 要打开的文件路径 407 public abstract void Handler(string path = null); 408 /// 409 /// 开启或者关闭屏幕刷新 410 /// 411 public bool ScreenUpdating 412 { 413 get => ExcelApp.ScreenUpdating; 414 set => ExcelApp.ScreenUpdating = value; 415 } 416 /// 417 /// Excel可见性 418 /// 419 public bool Visible 420 { 421 get => ExcelApp.Visible; 422 set => ExcelApp.Visible = value; 423 } 424 /// 425 /// 是否显示警告窗体 426 /// 427 public bool DisplayAlerts 428 { 429 get => ExcelApp.DisplayAlerts; 430 set => ExcelApp.DisplayAlerts = value; 431 } 432 private bool _debugMode; 433 /// 434 /// 设置DEBUG模式 435 /// 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 /// 452 /// 关闭Excel进程 453 /// 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 }
最后放上github的地址
https://github.com/tchivs/ExcelLib