在之前的文章中介绍了思想，其中Excel采用的二维数组模拟，今天花了点时间，学习了一下C#操作Excel，实现了类似的效果！
准备
需要导入Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;
实现
关键函数：
/// <summary>
///
/// </summary>
/// <param name="sheet"></param>
private static void ExecutMerge(Worksheet sheet)
{
int begainIndex = 2; // 开始行, Excel下标从1开始,第一行是标题行
int endIndex = GetEndIndex(sheet, begainIndex, 1, sheet.UsedRange.Rows.Count); // 查找最开始第一列从1（下标0）行开始值相同的最后一行下标
// 外围控制，第一列的合并
while (begainIndex <= sheet.UsedRange.Rows.Count)
{
MergeContorl(sheet, begainIndex, endIndex, 1); // 递归执行
begainIndex = endIndex + 1; // 第一列下一次执行行开始下标
endIndex = GetEndIndex(sheet, endIndex + 1, 1, sheet.UsedRange.Rows.Count);
}
}
/// <summary>
/// 递归控制器
/// </summary>
private static void MergeContorl(Worksheet sheet, int rowbeg, int rowend, int col)
{
// 1. 执行当前的合并操作
Merge(sheet, rowbeg, rowend, col);
// 2.执行后面的操作
if (col > 6)
{ // 只合并前面6列，递归结束条件
return;
}
// 3.执行后面列的操作
for (int i = rowbeg; i <= rowend; i++)
{
int begin = i;
int end = GetEndIndex(sheet,begin, col + 1, rowend);
while (begin <= rowend)
{ // 这里保证后面所有的行都能遍历到
MergeContorl(sheet, begin, end, col + 1); // 开启递归
begin = end + 1;
end = GetEndIndex(sheet, begin, col + 1, rowend);
}
}
}
/// <summary>
/// 合并
/// </summary>
private static void Merge(Worksheet sheet, int rowbeg, int rowend, int col)
{
//sheet.get_Range(sheet.Cells[rowbeg, col], sheet.Cells[rowend, col]).MergeCells = true;
sheet.Range[sheet.Cells[rowbeg, col], sheet.Cells[rowend, col]].MergeCells = true;
for (int i = rowbeg + 1; i <= rowend; i++)
{
sheet.Cells[i, col] = " ";
}
}
/// <summary>
/// 获取同一列相同值得最后（行）下标
/// </summary>
/// <returns></returns>
private static int GetEndIndex(Worksheet sheet, int begrow, int col, int endscope)
{
if (begrow >= endscope || begrow >= sheet.UsedRange.Rows.Count) // sheet的行下标从0开始
{
return begrow;
}
if(((Range)sheet.Cells[begrow, col]).Text ==( (Range)sheet.Cells[begrow + 1, col]).Text)
{
return GetEndIndex(sheet, begrow + 1, col, endscope);
}
else
{
return begrow;
}
}
main函数
static void Main(string[] args)
{
// 1. 打开excel文件
Application app = new Application();
app.AlertBeforeOverwriting = false;
app.DisplayAlerts = false; // 避免合并单元格时频繁的提示
Workbooks wbks = app.Workbooks;
Workbook wbk = wbks.Add(@"C:\XXXXXX\Desktop\test.xlsx"); // 打开Excel文件
Worksheet sheet = (Worksheet)wbk.Sheets.get_Item(1); // 获取sheet
// 执行合并
ExecutMerge(sheet);
// 合并完成另存为一个文件
wbk.SaveAs(@"C:\XXXXXX\Desktop\result.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value);
// 资源的关闭释放
wbk.Save();
wbk.Close();
app.Quit();
Console.WriteLine("执行结束");
Console.ReadLine();
}
效果
- 原表
- 结果