自己在用的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方法即可。
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using System.IO;
using static System.IO.File; namespace ExcelHelper
{
/*
2018-08-17 13:43:53
luoc@zhiweicl.com
*/
/// <summary>
/// Excel抽象类，封装了常用的方法，只需要实现Hanlder方法即可。
/// </summary>
public abstract class Excel
{
/// <summary>
/// 实例化Excel对象
/// </summary>
/// <param name="debugMode">设置Debug模式（Excel可见性，屏幕刷新，不提示警告窗体）</param>
protected Excel(bool debugMode = true)
{
try
{
ExcelApp = GetExcelApplication();
DebugMode = debugMode;
}
catch (InvalidCastException)
{
throw new COMException("对不起,没有获取到本机安装的Excel对象，请尝试修复或者安装Office2016后使用本软件！");
}
} /// <summary>
/// 显示Excel窗口
/// </summary>
public void Show()
{
if (!ExcelApp.Visible)
{
ExcelApp.Visible = true;
}
} /// <summary>
/// 获取Excel对象，如果不存在则打开
/// </summary>
/// <returns>返回一个Excel对象</returns>
public Application GetExcelApplication()
{
Application application;
try
{
application = (Application)Marshal.GetActiveObject("Excel.Application");//尝试取得正在运行的Excel对象
Debug.WriteLine("Get Running Excel");
}
//没有打开Excel则会报错
catch (COMException)
{
application = CreateExcelApplication();//打开Excel
Debug.WriteLine("Create new Excel");
}
Debug.WriteLine(application.Version);//打印Excel版本
return application;
} /// <summary>
/// 创建一个Excel对象
/// </summary>
/// <param name="visible">是否显示Excel，默认为True</param>
/// <param name="caption">标题栏</param>
/// <returns>返回创建好的Excel对象</returns>
public Application CreateExcelApplication(bool visible = true, string caption = "New Application")
{
var application = new Application
{
Visible = visible,
Caption = caption
};
return application;
} /// <summary>
/// 退出Excel
/// </summary>
public void Exit()
{
if (ExcelApp.Workbooks.Count > )
{
ExcelApp.DisplayAlerts = false;
ExcelApp.Workbooks.Close(); //关闭所有工作簿
}
ExcelApp.Quit(); //退出Excel
ExcelApp.DisplayAlerts = true;
}
/// <summary>
/// 杀死Excel进程
/// </summary>
public void Kill()
{
if (ExcelApp.Workbooks.Count > )
{
ExcelApp.DisplayAlerts = false;
ExcelApp.Workbooks.Close(); //关闭所有工作簿
}
ExcelApp.Quit();
GC.Collect();
KeyMyExcelProcess.Kill(ExcelApp);
}
/// <summary>
/// Excel实例对象
/// </summary>
public Application ExcelApp { get; } /// <summary>
/// 获取workbook对象
/// </summary>
/// <param name="name">工作簿全名</param>
/// <returns></returns>
public Workbook GetWorkbook(string name)
{
var wbk = ExcelApp.Workbooks[name];
return wbk;
} /// <summary>
/// 获取workbook对象
/// </summary>
/// <param name="index">索引</param>
/// <returns></returns>
public Workbook GetWorkbook(int index)
{
var wbk = ExcelApp.Workbooks[index];
return wbk;
} /// <summary>
/// 获取workbook活动对象
/// </summary>
/// <returns></returns>
public Workbook GetWorkbook()
{
var wbk = ExcelApp.ActiveWorkbook;
return wbk;
} /// <summary>
/// 打开工作簿
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public Workbook OpenFromFile(string path)
{
var workbook = ExcelApp.Workbooks.Open(path);
return workbook;
} /// <summary>
/// 添加工作簿
/// </summary>
/// <returns></returns>
public Workbook AddWorkbook()
{
var workbook = ExcelApp.Workbooks.Add();
return workbook;
} /// <summary>
/// 保存工作簿
/// </summary>
/// <param name="workbook"></param>
/// <param name="path"></param>
public void SaveWorkbook(Workbook workbook, string path)
{
workbook.SaveAs(path);
} /// <summary>
/// 关闭工作簿
/// </summary>
/// <param name="workbook"></param>
public void CloseWorkbook(Workbook workbook)
{
workbook.Close(false, Type.Missing, Type.Missing);
} /// <summary>
/// 打开或者查找表
/// </summary>
/// <param name="path"></param>
/// <param name="filename"></param>
/// <returns></returns>
public Workbook OpenAndFindWorkbook(string path, string filename)
{
var pathFull = Path.Combine(path, filename);
string fileName;
if (!Exists(pathFull))
{
pathFull = Directory.GetFiles(path, filename)[];
fileName = Path.GetFileName(pathFull);
}
else
{
fileName = Path.GetFileName(filename);
} Workbook res = null;
//遍历所有已打开的工作簿
foreach (Workbook ws in ExcelApp.Workbooks)
{
if (ws.Name != fileName) continue;
res = GetWorkbook(fileName); //OpenFromFile(umts_path).Worksheets[1];
break;
} //如果没有找到就直接打开文件
return res ?? (OpenFromFile(pathFull));
} /// <summary>
/// 打开或者查找表
/// </summary>
/// <param name="filename">文件名全路径</param>
/// <returns></returns>
public Workbook OpenAndFindWorkbook(string filename)
{
var pathFull = filename;
string fileName;
var path = Path.GetDirectoryName(filename);
if (!Exists(pathFull))
{
pathFull = Directory.GetFiles(path ?? throw new InvalidOperationException(), filename)[];
fileName = Path.GetFileName(pathFull);
}
else
{
fileName = Path.GetFileName(filename);
} Workbook res = null;
//遍历所有已打开的工作簿
foreach (Workbook ws in ExcelApp.Workbooks)
{
if (ws.Name != fileName) continue;
res = GetWorkbook(fileName); //OpenFromFile(umts_path).Worksheets[1];
break;
} //如果没有找到就直接打开文件
return res ?? (OpenFromFile(pathFull));
} /// <summary>
/// 复制列到另一张表
/// </summary>
/// <param name="sourceWorksheet">源表</param>
/// <param name="sourceRows">源列</param>
/// <param name="sourceStart">起始位置</param>
/// <param name="newWorksheet">目的表</param>
/// <param name="newRows">目的列</param>
/// <param name="newStart">目的位置</param>
public void CopyRow2OtherSheet(Worksheet sourceWorksheet, string[] sourceRows, int sourceStart,
Worksheet newWorksheet, string[] newRows, int newStart)
{
int intrngEnd = GetEndRow(sourceWorksheet);
if (newRows != null && (sourceRows != null && sourceRows.Length == newRows.Length))
{
for (int i = ; i < sourceRows.Length; i++)
{
var rg = sourceRows[i] + sourceStart + ":" + sourceRows[i] + intrngEnd;
sourceWorksheet.Range[rg]
.Copy(newWorksheet.Range[newRows[i] + newStart]);
// 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;
}
}
else
{
Console.WriteLine("Error source_rows length not is new_rows length!");
}
} /// <summary>
/// 复制列到另一张表
/// </summary>
/// <param name="sourceWorksheet">源表</param>
/// <param name="sourceRows">源列</param>
/// <param name="sourceStart">起始位置</param>
/// <param name="newWorksheet">目的表</param>
/// <param name="newRows">目的列</param>
/// <param name="newStart">目的位置</param>
public void CopyRow2OtherSheet(Worksheet sourceWorksheet, int[] sourceRows, int sourceStart, Worksheet newWorksheet,
int[] newRows, int newStart)
{
int intrngEnd = GetEndRow(sourceWorksheet);
if (sourceRows.Length == newRows.Length)
{
for (int i = ; i < sourceRows.Length; i++)
{
newWorksheet.Cells[, newRows[i]].End[XlDirection.xlUp].Offset(sourceStart, ).Resize(intrngEnd, sourceStart)
.Value = sourceWorksheet.Cells[newStart, sourceRows[i]].Resize(intrngEnd, newRows[i]).Value;
}
}
else
{
Console.WriteLine("Error source_rows length not is new_rows length!");
}
} /// <summary>
/// 复制表头到另一个sheet中
/// </summary>
/// <param name="sourceWorksheet">表头所在的sheet</param>
/// <param name="newWorksheet">要复制到的sheet</param>
/// <param name="start">起始位置</param>
public void CopyHeader(Worksheet sourceWorksheet, Worksheet newWorksheet, int start = )
{
if (sourceWorksheet.Rows != null)
sourceWorksheet.Rows[start].Copy(newWorksheet.Cells[, ]); //把数据表的表头复制到新表中
} /// <summary>
/// 设置特定列的数据
/// </summary>
/// <param name="worksheet">源表</param>
/// <param name="row">要设置的列号</param>
/// <param name="len">长度</param>
/// <param name="value">要设的值</param>
/// ///
public void SetSheetRow(Worksheet worksheet, int row, int len, string value)
{
//int intrngEnd = this.GetEndRow(worksheet);//取特定列最后一列的长度
worksheet.Cells[, row].End[XlDirection.xlUp].Offset(, ).Resize(len, ).Value = value;
} /// <summary>
/// 取有效列的最后一列的长度
/// </summary>
/// <param name="worksheet"></param>
/// <returns></returns>
public int GetEndRow(Worksheet worksheet)
{
int res = worksheet.UsedRange.Rows.Count;
return res;
} /// <summary>
/// 插入图片
/// </summary>
/// <param name="path">图片路径</param>
/// <param name="worksheet">要插入的表</param>
/// <param name="range">要插入的range</param>
public void AddPic(string path, Worksheet worksheet, Range range)
{
this.AddPic(path, worksheet, range, range.Width, range.Height);
} /// <summary>
/// 插入图片
/// </summary>
/// <param name="path">图片路径</param>
/// <param name="worksheet">要插入的表</param>
/// <param name="range">要插入的range</param>
/// <param name="width">图片的宽度</param>
/// <param name="height">图片的高度</param>
public void AddPic(string path, Worksheet worksheet, Range range, int width, int height)
{
worksheet.Shapes.AddPicture(path, Microsoft.Office.Core.MsoTriState.msoCTrue,
Microsoft.Office.Core.MsoTriState.msoCTrue, range.Left, range.Top, width, height).Placement =
XlPlacement.xlMoveAndSize;
} /// <summary>
/// 批量插入图片
/// </summary>
/// <param name="pngdic">单元格范围-图片名</param>
/// <param name="imgBase">图片根目录</param>
/// <param name="worksheet">要插入图片的worksheet</param>
/// <returns>返回处理好的图片日志</returns>
public string InsertMultipleImages(Dictionary<string, string> pngdic, string imgBase, Worksheet worksheet)
{
string msg = null;
foreach (var s in pngdic)
{
string imgPath = Path.Combine(imgBase, s.Value);
if (!Exists(imgPath))
{
continue;
} Range range = worksheet.Range[s.Key];
AddPic(imgPath, worksheet, range);
msg = s.Value + "\t" + s.Key + "\t\t\t" + range.Left.ToString() + "\t" + range.Top.ToString() + "\n";
} return msg;
} /// <summary>
/// 主要实现这个方法
/// </summary>
/// <param name="path">要打开的文件路径</param>
public abstract void Handler(string path = null);
/// <summary>
/// 开启或者关闭屏幕刷新
/// </summary>
public bool ScreenUpdating
{
get => ExcelApp.ScreenUpdating;
set => ExcelApp.ScreenUpdating = value;
}
/// <summary>
/// Excel可见性
/// </summary>
public bool Visible
{
get => ExcelApp.Visible;
set => ExcelApp.Visible = value;
}
/// <summary>
/// 是否显示警告窗体
/// </summary>
public bool DisplayAlerts
{
get => ExcelApp.DisplayAlerts;
set => ExcelApp.DisplayAlerts = value;
}
private bool _debugMode;
/// <summary>
/// 设置DEBUG模式
/// </summary>
public bool DebugMode
{
get => _debugMode;
set
{
_debugMode = value;
//设置是否显示警告窗体
DisplayAlerts = value;
//设置是否显示Excel
Visible = value;
//禁止刷新屏幕
ScreenUpdating = value;
}
}
}
/// <summary>
/// 关闭Excel进程
/// </summary>
public class KeyMyExcelProcess
{
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int id);
public static void Kill(Application excel)
{
try
{
IntPtr t = new IntPtr(excel.Hwnd); //得到这个句柄，具体作用是得到这块内存入口
GetWindowThreadProcessId(t, out var k); //得到本进程唯一标志k
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用
p.Kill(); //关闭进程k
}
catch (Exception e)
{
Console.WriteLine(e.Message);
} }
}
}
最后放上github的地址
https://github.com/tchivs/ExcelLib