客服电话
APP下载
迪恩网络APP
随时随地掌握行业动态
官方微信
扫描二维码
关注迪恩网络微信公众号
|
自己在用的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 [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 /// 全部评论
专题导读
热门推荐
热门话题
 阅读排行榜
|
请发表评论