如何运用.NET技术高效操作Excel工作簿和工作表？
摘要：在ExcelWPS ET自动化开发中，工作簿（Workbook）和工作表（Worksheet）是核心的操作对象。熟练掌握工作簿与工作表的操作是构建高效ExcelWPS ET自动化应用的基础。本文将深入探讨MudTools.OfficeI
在Excel/WPS ET自动化开发中，工作簿（Workbook）和工作表（Worksheet）是核心的操作对象。熟练掌握工作簿与工作表的操作是构建高效Excel/WPS ET自动化应用的基础。本文将深入探讨MudTools.OfficeInterop.Excel库中关于工作簿和工作表的各项操作，包括创建、打开、保存、管理以及各种高级功能。
工作簿基础操作
工作簿的创建与打开
创建工作簿
MudTools.OfficeInterop.Excel提供了多种创建工作簿的方式：
using System;
using MudTools.OfficeInterop;
using MudTools.OfficeInterop.Excel;
namespace WorkbookCreationExample
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("=== 工作簿创建示例 ===");
try
{
// 方法1：创建空白工作簿
CreateBlankWorkbook();
// 方法2：基于模板创建工作簿
CreateWorkbookFromTemplate();
// 方法3：打开现有工作簿
OpenExistingWorkbook();
// 方法4：创建多个工作簿
CreateMultipleWorkbooks();
Console.WriteLine("\n✓ 所有工作簿创建示例完成");
}
catch (Exception ex)
{
Console.WriteLine($"✗ 工作簿创建示例时出错: {ex.Message}");
}
Console.WriteLine("按任意键退出...");
Console.ReadKey();
}
static void CreateBlankWorkbook()
{
Console.WriteLine("\n1. 创建空白工作簿");
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
// 设置工作簿属性
workbook.Title = "我的第一个工作簿";
workbook.Author = Environment.UserName;
workbook.Comments = "这是通过MudTools.OfficeInterop.Excel创建的工作簿";
// 获取活动工作表
var worksheet = workbook.ActiveSheetWrap;
// 设置工作表属性
worksheet.Name = "数据表";
worksheet.Tab.Color = System.Drawing.Color.LightBlue;
// 添加示例数据
PopulateSampleData(worksheet);
// 应用格式
ApplyFormatting(worksheet);
// 保存工作簿
string fileName = $"BlankWorkbookExample_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
workbook.SaveAs(fileName);
Console.WriteLine($" ✓ 空白工作簿创建成功: {fileName}");
DisplayWorkbookInfo(workbook);
}
static void CreateWorkbookFromTemplate()
{
Console.WriteLine("\n2. 基于模板创建工作簿");
// 先检查模板文件是否存在
string templatePath = "MonthlyReportTemplate.xltx";
if (!File.Exists(templatePath))
{
Console.WriteLine(" ⚠ 模板文件不存在，创建默认模板");
CreateTemplateFile(templatePath);
}
// 基于模板创建工作簿
using var excelApp = ExcelFactory.CreateFrom(templatePath);
var workbook = excelApp.ActiveWorkbook;
// 修改模板内容
var worksheet = workbook.ActiveSheetWrap;
worksheet.Range("B2").Value = "ABC科技有限公司";
worksheet.Range("B3").Value = DateTime.Now.ToString("yyyy年MM月");
worksheet.Range("B4").Value = "月度销售报告";
// 填充动态数据
FillTemplateData(worksheet);
// 保存工作簿
string fileName = $"TemplateBased_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
workbook.SaveAs(fileName);
Console.WriteLine($" ✓ 基于模板的工作簿创建成功: {fileName}");
}
static void OpenExistingWorkbook()
{
Console.WriteLine("\n3. 打开现有工作簿");
// 先创建一个测试文件
string testFile = "TestWorkbook.xlsx";
CreateTestWorkbook(testFile);
// 打开现有工作簿
using var excelApp = ExcelFactory.Open(testFile);
var workbook = excelApp.ActiveWorkbook;
// 显示工作簿信息
DisplayWorkbookInfo(workbook);
// 修改工作簿
var worksheet = workbook.ActiveSheetWrap;
worksheet.Range("A10").Value = $"打开时间: {DateTime.Now:HH:mm:ss}";
// 保存修改
string newFileName = $"OpenedAndModified_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
workbook.SaveAs(newFileName);
Console.WriteLine($" ✓ 现有工作簿打开并修改成功: {newFileName}");
}
static void CreateMultipleWorkbooks()
{
Console.WriteLine("\n4. 创建多个工作簿");
// 创建多个工作簿实例
for (int i = 1; i <= 3; i++)
{
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
var worksheet = workbook.ActiveSheetWrap;
// 设置工作表名称
worksheet.Name = $"工作表{i}";
// 添加数据
worksheet.Range("A1").Value = $"这是第{i}个工作簿";
worksheet.Range("A2").Value = $"创建时间: {DateTime.Now}";
// 保存
string fileName = $"MultipleWorkbook_{i}_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
workbook.SaveAs(fileName);
Console.WriteLine($" ✓ 工作簿{i}创建成功: {fileName}");
}
}
static void PopulateSampleData(IExcelWorksheet worksheet)
{
// 设置表头
string[] headers = { "产品名称", "单价", "数量", "总价", "备注" };
for (int i = 0; i < headers.Length; i++)
{
worksheet.Cells[1, i + 1].Value = headers[i];
}
// 填充示例数据
var sampleData = new[]
{
new { Name = "笔记本电脑", Price = 5999, Quantity = 10 },
new { Name = "无线鼠标", Price = 89, Quantity = 25 },
new { Name = "机械键盘", Price = 299, Quantity = 8 },
new { Name = "显示器", Price = 1299, Quantity = 5 },
new { Name = "打印机", Price = 899, Quantity = 3 }
};
for (int i = 0; i < sampleData.Length; i++)
{
var data = sampleData[i];
int row = i + 2;
worksheet.Cells[row, 1].Value = data.Name;
worksheet.Cells[row, 2].Value = data.Price;
worksheet.Cells[row, 3].Value = data.Quantity;
worksheet.Cells[row, 4].Formula = $"=B{row}*C{row}"; // 计算总价值
}
}
static void ApplyFormatting(IExcelWorksheet worksheet)
{
// 设置标题行格式
var headerRange = worksheet.Range("A1:E1");
headerRange.Font.Bold = true;
headerRange.Interior.Color = System.Drawing.Color.LightBlue;
headerRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
// 设置数字格式
worksheet.Range("B2:B6").NumberFormat = "¥#,##0.00";
worksheet.Range("D2:D6").NumberFormat = "¥#,##0.00";
// 自动调整列宽
worksheet.Columns["A:E"].AutoFit();
// 设置边框
worksheet.Range("A1:E6").Borders.LineStyle = XlLineStyle.xlContinuous;
}
static void CreateTemplateFile(string templatePath)
{
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
var worksheet = workbook.ActiveSheetWrap;
// 创建模板内容
worksheet.Range("A1").Value = "公司名称:";
worksheet.Range("A2").Value = "报告月份:";
worksheet.Range("A3").Value = "报告标题:";
worksheet.Range("A5").Value = "部门";
worksheet.Range("B5").Value = "预算";
worksheet.Range("C5").Value = "实际";
worksheet.Range("D5").Value = "差异";
worksheet.Range("E5").Value = "完成率";
// 设置模板格式
worksheet.Range("A1:A3").Font.Bold = true;
worksheet.Range("A5:E5").Font.Bold = true;
worksheet.Range("A5:E5").Interior.Color = System.Drawing.Color.LightGreen;
// 保存为模板
workbook.SaveAs(templatePath, XlFileFormat.xlOpenXMLTemplate);
}
static void FillTemplateData(IExcelWorksheet worksheet)
{
// 填充示例数据
var departments = new[]
{
new { Name = "销售部", Budget = 500000, Actual = 480000 },
new { Name = "市场部", Budget = 200000, Actual = 195000 },
new { Name = "技术部", Budget = 300000, Actual = 310000 },
new { Name = "人事部", Budget = 150000, Actual = 148000 }
};
for (int i = 0; i < departments.Length; i++)
{
var dept = departments[i];
int row = i + 6;
worksheet.Cells[row, 1].Value = dept.Name;
worksheet.Cells[row, 2].Value = dept.Budget;
worksheet.Cells[row, 3].Value = dept.Actual;
worksheet.Cells[row, 4].Formula = $"=C{row}-B{row}"; // 差异
worksheet.Cells[row, 5].Formula = $"=C{row}/B{row}"; // 完成率
}
// 设置数字格式
worksheet.Range("B6:E9").NumberFormat = "#,##0";
worksheet.Range("E6:E9").NumberFormat = "0.00%";
}
static void CreateTestWorkbook(string filePath)
{
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
var worksheet = workbook.ActiveSheetWrap;
worksheet.Range("A1").Value = "测试工作簿";
worksheet.Range("A2").Value = $"创建时间: {DateTime.Now}";
workbook.SaveAs(filePath);
}
static void DisplayWorkbookInfo(IExcelWorkbook workbook)
{
Console.WriteLine($" 工作簿信息:");
Console.WriteLine($" 名称: {workbook.Name}");
Console.WriteLine($" 路径: {workbook.FullName}");
Console.WriteLine($" 工作表数量: {workbook.Worksheets.Count}");
Console.WriteLine($" 是否只读: {workbook.ReadOnly}");
Console.WriteLine($" 是否已保存: {workbook.Saved}");
}
}
}
工作簿打开模式
MudTools.OfficeInterop.Excel支持多种工作簿打开模式：
using System;
using MudTools.OfficeInterop;
using MudTools.OfficeInterop.Excel;
namespace WorkbookOpenModesExample
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("=== 工作簿打开模式示例 ===");
try
{
// 创建测试文件
string testFile = "OpenModeTest.xlsx";
CreateTestWorkbook(testFile);
// 模式1：正常打开（可读写）
OpenWorkbookInReadWriteMode(testFile);
// 模式2：只读模式
OpenWorkbookInReadOnlyMode(testFile);
// 模式3：打开并修复
OpenWorkbookWithRepair(testFile);
// 模式4：打开副本
OpenWorkbookAsCopy(testFile);
Console.WriteLine("\n✓ 所有打开模式测试完成");
}
catch (Exception ex)
{
Console.WriteLine($"✗ 打开模式测试时出错: {ex.Message}");
}
Console.WriteLine("按任意键退出...");
Console.ReadKey();
}
static void OpenWorkbookInReadWriteMode(string filePath)
{
Console.WriteLine("\n1. 正常打开模式（可读写）");
using var excelApp = ExcelFactory.Open(filePath);
var workbook = excelApp.ActiveWorkbook;
// 验证可写性
workbook.ActiveSheetWrap.Range("A3").Value = "正常模式 - 可修改";
string newFileName = $"ReadWriteMode_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
workbook.SaveAs(newFileName);
Console.WriteLine($" ✓ 正常模式打开成功，文件已保存为: {newFileName}");
}
static void OpenWorkbookInReadOnlyMode(string filePath)
{
Console.WriteLine("\n2. 只读模式");
// 设置文件为只读属性
File.SetAttributes(filePath, FileAttributes.ReadOnly);
try
{
using var excelApp = ExcelFactory.Open(filePath);
var workbook = excelApp.ActiveWorkbook;
Console.WriteLine($" ✓ 只读模式打开成功");
Console.WriteLine($" 只读状态: {workbook.ReadOnly}");
// 尝试修改（应该会失败或创建副本）
try
{
workbook.ActiveSheetWrap.Range("A4").Value = "只读模式 - 尝试修改";
workbook.Save();
}
catch (Exception ex)
{
Console.WriteLine($" ⚠ 修改失败（预期行为）: {ex.Message}");
}
}
finally
{
// 恢复文件属性
File.SetAttributes(filePath, FileAttributes.Normal);
}
}
static void OpenWorkbookWithRepair(string filePath)
{
Console.WriteLine("\n3. 打开并修复模式");
// 注意：这个功能需要特定版本的Excel支持
try
{
// 模拟损坏文件（实际应用中应该检查文件完整性）
string corruptedFile = $"Corrupted_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
File.Copy(filePath, corruptedFile);
// 这里可以添加文件损坏的模拟逻辑
// 在实际应用中，Excel会自动检测并尝试修复
using var excelApp = ExcelFactory.Open(corruptedFile);
var workbook = excelApp.ActiveWorkbook;
Console.WriteLine($" ✓ 文件打开成功（可能已自动修复）");
// 清理临时文件
File.Delete(corruptedFile);
}
catch (Exception ex)
{
Console.WriteLine($" ⚠ 打开修复模式测试: {ex.Message}");
}
}
static void OpenWorkbookAsCopy(string filePath)
{
Console.WriteLine("\n4. 打开副本模式");
using var excelApp = ExcelFactory.Open(filePath);
var workbook = excelApp.ActiveWorkbook;
// 创建副本
string copyFileName = $"Copy_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
workbook.SaveAs(copyFileName);
Console.WriteLine($" ✓ 副本创建成功: {copyFileName}");
// 验证原文件未被修改
var originalInfo = new FileInfo(filePath);
var copyInfo = new FileInfo(copyFileName);
Console.WriteLine($" 原文件大小: {originalInfo.Length} bytes");
Console.WriteLine($" 副本文件大小: {copyInfo.Length} bytes");
Console.WriteLine($" 文件是否相同: {originalInfo.Length == copyInfo.Length}");
}
static void CreateTestWorkbook(string filePath)
{
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
var worksheet = workbook.ActiveSheetWrap;
worksheet.Range("A1").Value = "打开模式测试工作簿";
worksheet.Range("A2").Value = $"创建时间: {DateTime.Now}";
// 添加测试数据
for (int i = 1; i <= 5; i++)
{
worksheet.Cells[i + 2, 1].Value = $"测试数据 {i}";
worksheet.Cells[i + 2, 2].Value = i * 100;
}
workbook.SaveAs(filePath);
}
}
}
工作簿属性与状态管理
工作簿属性操作
工作簿包含丰富的属性信息，可以用于文档管理和元数据存储：
using System;
using MudTools.OfficeInterop;
using MudTools.OfficeInterop.Excel;
namespace WorkbookPropertiesExample
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("=== 工作簿属性操作示例 ===");
try
{
// 创建测试工作簿
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
// 设置内置属性
SetBuiltInProperties(workbook);
// 设置自定义属性
SetCustomProperties(workbook);
// 显示属性信息
DisplayProperties(workbook);
// 保存工作簿
string fileName = $"PropertiesExample_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
workbook.SaveAs(fileName);
Console.WriteLine($"\n✓ 工作簿属性设置完成: {fileName}");
// 重新打开验证属性
VerifyProperties(fileName);
}
catch (Exception ex)
{
Console.WriteLine($"✗ 属性操作时出错: {ex.Message}");
}
Console.WriteLine("按任意键退出...");
Console.ReadKey();
}
static void SetBuiltInProperties(IExcelWorkbook workbook)
{
Console.WriteLine("\n设置内置属性:");
// 核心属性
workbook.Title = "2024年度财务报告";
workbook.Subject = "财务数据分析";
workbook.Author = Environment.UserName;
workbook.Comments = "这是年度财务报告的自动化版本";
workbook.Keywords = "财务,报告,2024,数据分析";
workbook.Category = "财务文档";
// 公司信息
workbook.Company = "ABC科技有限公司";
workbook.Manager = "张经理";
// 状态信息
workbook.Status = "已完成";
Console.WriteLine(" ✓ 内置属性设置完成");
}
static void SetCustomProperties(IExcelWorkbook workbook)
{
Console.WriteLine("\n设置自定义属性:");
// 添加自定义属性
workbook.CustomProperties.Add("DocumentID", Guid.NewGuid().ToString());
workbook.CustomProperties.Add("Version", "1.0.0");
workbook.CustomProperties.Add("Department", "财务部");
workbook.CustomProperties.Add("ProjectCode", "FIN-2024-001");
workbook.CustomProperties.Add("Reviewer", "李审核员");
workbook.CustomProperties.Add("ApprovalDate", DateTime.Now.AddDays(7).ToString("yyyy-MM-dd"));
// 数值型自定义属性
workbook.CustomProperties.Add("TotalPages", 15);
workbook.CustomProperties.Add("RevisionNumber", 1);
workbook.CustomProperties.Add("ConfidentialLevel", 2);
Console.WriteLine(" ✓ 自定义属性设置完成");
}
static void DisplayProperties(IExcelWorkbook workbook)
{
Console.WriteLine("\n工作簿属性信息:");
// 显示内置属性
Console.WriteLine(" 内置属性:");
Console.WriteLine($" 标题: {workbook.Title}");
Console.WriteLine($" 主题: {workbook.Subject}");
Console.WriteLine($" 作者: {workbook.Author}");
Console.WriteLine($" 备注: {workbook.Comments}");
Console.WriteLine($" 公司: {workbook.Company}");
Console.WriteLine($" 经理: {workbook.Manager}");
Console.WriteLine($" 状态: {workbook.Status}");
// 显示自定义属性
Console.WriteLine("\n 自定义属性:");
foreach (var prop in workbook.CustomProperties)
{
Console.WriteLine($" {prop.Name}: {prop.Value}");
}
// 显示统计信息
Console.WriteLine("\n 统计信息:");
Console.WriteLine($" 名称: {workbook.Name}");
Console.WriteLine($" 完整路径: {workbook.FullName}");
Console.WriteLine($" 路径: {workbook.Path}");
Console.WriteLine($" 文件格式: {workbook.FileFormat}");
Console.WriteLine($" 是否只读: {workbook.ReadOnly}");
Console.WriteLine($" 是否已保存: {workbook.Saved}");
Console.WriteLine($" 工作表数量: {workbook.Worksheets.Count}");
// 显示保护状态
Console.WriteLine($" 是否受保护: {workbook.ProtectStructure}");
if (workbook.ProtectStructure)
{
Console.WriteLine($" 保护密码: {workbook.Password}");
}
}
static void VerifyProperties(string filePath)
{
Console.WriteLine("\n验证属性保存:");
using var excelApp = ExcelFactory.Open(filePath);
var workbook = excelApp.ActiveWorkbook;
Console.WriteLine($" 重新打开的文件属性:");
Console.WriteLine($" 标题: {workbook.Title}");
Console.WriteLine($" 作者: {workbook.Author}");
Console.WriteLine($" 公司: {workbook.Company}");
// 验证自定义属性
var docIdProp = workbook.CustomProperties["DocumentID"];
if (docIdProp != null)
{
Console.WriteLine($" 文档ID: {docIdProp.Value}");
}
Console.WriteLine(" ✓ 属性验证完成");
}
}
}
工作簿状态监控
工作簿状态监控对于确保应用程序的稳定性至关重要：
using System;
using System.Diagnostics;
using MudTools.OfficeInterop;
using MudTools.OfficeInterop.Excel;
namespace WorkbookStateMonitoring
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("=== 工作簿状态监控示例 ===");
try
{
// 创建状态监控器
var monitor = new WorkbookStateMonitor();
// 执行监控测试
monitor.StartMonitoring();
Console.WriteLine("\n✓ 状态监控示例完成");
}
catch (Exception ex)
{
Console.WriteLine($"✗ 状态监控时出错: {ex.Message}");
}
Console.WriteLine("按任意键退出...");
Console.ReadKey();
}
}
public class WorkbookStateMonitor
{
private Stopwatch _stopwatch;
private List<WorkbookStateRecord> _records;
public void StartMonitoring()
{
_stopwatch = Stopwatch.StartNew();
_records = new List<WorkbookStateRecord>();
Console.WriteLine("开始工作簿状态监控...");
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
// 记录初始状态
RecordState(workbook, "初始状态");
// 测试1：常规操作
TestNormalOperations(workbook);
// 测试2：大量数据操作
TestHeavyOperations(workbook);
// 测试3：异常情况处理
TestExceptionHandling(workbook);
// 最终状态记录
RecordState(workbook, "最终状态");
// 生成监控报告
GenerateReport();
}
private void TestNormalOperations(IExcelWorkbook workbook)
{
Console.WriteLine("\n测试1：常规操作");
var worksheet = workbook.ActiveSheetWrap;
// 操作1：添加数据
for (int i = 1; i <= 100; i++)
{
worksheet.Cells[i, 1].Value = $"数据{i}";
worksheet.Cells[i, 2].Value = i * 10;
if (i % 20 == 0)
{
RecordState(workbook, $"添加数据{i}行");
}
}
// 操作2：应用格式
worksheet.Range("A1:B100").Borders.LineStyle = XlLineStyle.xlContinuous;
RecordState(workbook, "应用格式");
// 操作3：保存
workbook.SaveAs("NormalOperations.xlsx");
RecordState(workbook, "第一次保存");
}
private void TestHeavyOperations(IExcelWorkbook workbook)
{
Console.WriteLine("\n测试2：大量数据操作");
var worksheet = workbook.ActiveSheetWrap;
// 大量数据写入
object[,] largeData = new object[1000, 10];
for (int i = 0; i < 1000; i++)
{
for (int j = 0; j < 10; j++)
{
largeData[i, j] = $"数据{i+1}-{j+1}";
}
}
worksheet.Range("A1:J1000").Value = largeData;
RecordState(workbook, "大量数据写入");
// 公式计算
worksheet.Range("K1").Formula = "=SUM(B:B)";
worksheet.Range("K2").Formula = "=AVERAGE(B:B)";
worksheet.Range("K3").Formula = "=MAX(B:B)";
worksheet.Range("K4").Formula = "=MIN(B:B)";
RecordState(workbook, "公式计算");
// 再次保存
workbook.SaveAs("HeavyOperations.xlsx");
RecordState(workbook, "大量操作后保存");
}
private void TestExceptionHandling(IExcelWorkbook workbook)
{
Console.WriteLine("\n测试3：异常情况处理");
try
{
// 模拟异常操作
workbook.ActiveSheetWrap.Range("Z10000").Value = "测试异常";
RecordState(workbook, "异常操作前");
// 强制触发异常（实际应用中应该避免）
// 这里只是演示异常处理机制
}
catch (Exception ex)
{
Console.WriteLine($" ⚠ 捕获异常: {ex.Message}");
RecordState(workbook, $"异常捕获: {ex.Message}");
}
// 验证工作簿状态是否正常
if (workbook.Saved)
{
Console.WriteLine(" ✓ 工作簿状态正常");
}
else
{
Console.WriteLine(" ⚠ 工作簿有未保存的更改");
}
}
private void RecordState(IExcelWorkbook workbook, string operation)
{
var record = new WorkbookStateRecord
{
Timestamp = _stopwatch.ElapsedMilliseconds,
Operation = operation,
IsSaved = workbook.Saved,
IsReadOnly = workbook.ReadOnly,
WorksheetCount = workbook.Worksheets.Count,
MemoryUsage = GetCurrentMemoryUsage()
};
_records.Add(record);
Console.WriteLine($" [{record.Timestamp}ms] {operation} - 已保存: {record.IsSaved}, 内存: {record.MemoryUsage} KB");
}
private long GetCurrentMemoryUsage()
{
return Process.GetCurrentProcess().WorkingSet64 / 1024;
}
private void GenerateReport()
{
Console.WriteLine("\n=== 工作簿状态监控报告 ===");
if (_records.Count == 0)
{
Console.WriteLine("没有监控记录");
return;
}
var firstRecord = _records.First();
var lastRecord = _records.Last();
Console.WriteLine($"监控时长: {lastRecord.Timestamp}ms");
Console.WriteLine($"操作数量: {_records.Count}");
Console.WriteLine($"内存变化: {firstRecord.MemoryUsage} KB → {lastRecord.MemoryUsage} KB");
Console.WriteLine($"最终状态: {(lastRecord.IsSaved ? "已保存" : "未保存")}");
// 分析性能数据
var performanceIssues = _records
.Where(r => r.MemoryUsage > 50000) // 内存超过50MB
.ToList();
if (performanceIssues.Any())
{
Console.WriteLine("\n⚠ 检测到可能的性能问题:");
foreach (var issue in performanceIssues)
{
Console.WriteLine($" [{issue.Timestamp}ms] {issue.Operation} - 内存: {issue.MemoryUsage} KB");
}
}
else
{
Console.WriteLine("\n✓ 没有检测到明显的性能问题");
}
}
}
public class WorkbookStateRecord
{
public long Timestamp { get; set; }
public string Operation { get; set; }
public bool IsSaved { get; set; }
public bool IsReadOnly { get; set; }
public int WorksheetCount { get; set; }
public long MemoryUsage { get; set; }
}
}
工作簿保存与关闭
工作簿保存策略
工作簿的保存操作需要考虑多种场景和策略：
using System;
using System.IO;
using MudTools.OfficeInterop;
using MudTools.OfficeInterop.Excel;
namespace WorkbookSaveStrategies
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("=== 工作簿保存策略示例 ===");
try
{
// 策略1：常规保存
TestNormalSaveStrategy();
// 策略2：增量保存
TestIncrementalSaveStrategy();
// 策略3：备份保存
TestBackupSaveStrategy();
// 策略4：自动保存
TestAutoSaveStrategy();
Console.WriteLine("\n✓ 所有保存策略测试完成");
}
catch (Exception ex)
{
Console.WriteLine($"✗ 保存策略测试时出错: {ex.Message}");
}
Console.WriteLine("按任意键退出...");
Console.ReadKey();
}
static void TestNormalSaveStrategy()
{
Console.WriteLine("\n策略1：常规保存");
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
var worksheet = workbook.ActiveSheetWrap;
// 添加数据
worksheet.Range("A1").Value = "常规保存测试";
for (int i = 1; i <= 10; i++)
{
worksheet.Cells[i + 1, 1].Value = $"数据{i}";
}
// 保存为不同格式
string xlsxFile = $"NormalSave_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
string xlsFile = $"NormalSave_{DateTime.Now:yyyyMMddHHmmss}.xls";
string csvFile = $"NormalSave_{DateTime.Now:yyyyMMddHHmmss}.csv";
workbook.SaveAs(xlsxFile);
Console.WriteLine($" ✓ XLSX格式保存: {xlsxFile}");
workbook.SaveAs(xlsFile, XlFileFormat.xlExcel8);
Console.WriteLine($" ✓ XLS格式保存: {xlsFile}");
workbook.SaveAs(csvFile, XlFileFormat.xlCSV);
Console.WriteLine($" ✓ CSV格式保存: {csvFile}");
// 验证文件大小
DisplayFileInfo(xlsxFile);
DisplayFileInfo(xlsFile);
DisplayFileInfo(csvFile);
}
static void TestIncrementalSaveStrategy()
{
Console.WriteLine("\n策略2：增量保存");
string baseFileName = $"IncrementalSave_{DateTime.Now:yyyyMMddHHmmss}";
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
var worksheet = workbook.ActiveSheetWrap;
// 分阶段保存
for (int stage = 1; stage <= 3; stage++)
{
// 添加阶段数据
worksheet.Range($"A{stage}").Value = $"阶段{stage}数据";
for (int i = 1; i <= 5; i++)
{
worksheet.Cells[stage * 10 + i, 1].Value = $"阶段{stage}-数据{i}";
}
// 增量保存
string fileName = $"{baseFileName}_Stage{stage}.xlsx";
workbook.SaveAs(fileName);
Console.WriteLine($" ✓ 阶段{stage}保存: {fileName}");
DisplayFileInfo(fileName);
}
}
static void TestBackupSaveStrategy()
{
Console.WriteLine("\n策略3：备份保存");
string originalFile = $"BackupSave_Original_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
string backupDir = "Backups";
// 创建备份目录
if (!Directory.Exists(backupDir))
{
Directory.CreateDirectory(backupDir);
}
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
var worksheet = workbook.ActiveSheetWrap;
// 初始保存
worksheet.Range("A1").Value = "备份保存测试";
workbook.SaveAs(originalFile);
Console.WriteLine($" ✓ 原始文件保存: {originalFile}");
// 多次修改并备份
for (int i = 1; i <= 5; i++)
{
worksheet.Range($"A{i + 1}").Value = $"修改版本{i}";
// 保存主文件
workbook.Save();
// 创建备份
string backupFile = Path.Combine(backupDir, $"Backup_{DateTime.Now:yyyyMMddHHmmss}_v{i}.xlsx");
File.Copy(originalFile, backupFile, true);
Console.WriteLine($" ✓ 版本{i}备份: {backupFile}");
}
// 显示备份文件信息
var backupFiles = Directory.GetFiles(backupDir, "*.xlsx");
Console.WriteLine($" 备份文件数量: {backupFiles.Length}");
}
static void TestAutoSaveStrategy()
{
Console.WriteLine("\n策略4：自动保存");
var autoSaveManager = new AutoSaveManager();
autoSaveManager.StartAutoSaveTest();
}
static void DisplayFileInfo(string filePath)
{
if (File.Exists(filePath))
{
var fileInfo = new FileInfo(filePath);
Console.WriteLine($" 文件大小: {fileInfo.Length} bytes");
Console.WriteLine($" 创建时间: {fileInfo.CreationTime:HH:mm:ss}");
}
}
}
public class AutoSaveManager
{
private Timer _autoSaveTimer;
private int _saveCount;
public void StartAutoSaveTest()
{
Console.WriteLine(" 开始自动保存测试...");
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
var worksheet = workbook.ActiveSheetWrap;
_saveCount = 0;
string baseFileName = $"AutoSave_{DateTime.Now:yyyyMMddHHmmss}";
// 设置自动保存定时器（每5秒保存一次）
_autoSaveTimer = new Timer(_ =>
{
try
{
_saveCount++;
string fileName = $"{baseFileName}_Auto{_saveCount}.xlsx";
// 添加时间戳
worksheet.Range($"A{_saveCount}").Value = $"自动保存 {_saveCount} - {DateTime.Now:HH:mm:ss}";
workbook.SaveAs(fileName);
Console.WriteLine($" ✓ 自动保存 #{_saveCount}: {fileName}");
// 测试10次后停止
if (_saveCount >= 10)
{
_autoSaveTimer?.Dispose();
Console.WriteLine(" ⚠ 自动保存测试完成");
}
}
catch (Exception ex)
{
Console.WriteLine($" ✗ 自动保存失败: {ex.Message}");
}
}, null, TimeSpan.Zero, TimeSpan.FromSeconds(5));
// 模拟用户操作
for (int i = 1; i <= 20; i++)
{
worksheet.Cells[i, 2].Value = $"用户操作数据{i}";
Thread.Sleep(1000); // 每秒一次操作
if (i % 5 == 0)
{
Console.WriteLine($" 用户操作完成 {i}/20");
}
}
// 等待自动保存完成
Thread.Sleep(6000);
_autoSaveTimer?.Dispose();
Console.WriteLine($" ✓ 自动保存测试完成，共保存 {_saveCount} 次");
}
}
}
工作簿关闭与资源清理
正确的工作簿关闭和资源清理是防止内存泄漏的关键：
using System;
using System.Diagnostics;
using System.Runtime.InteropServices;
using MudTools.OfficeInterop;
using MudTools.OfficeInterop.Excel;
namespace WorkbookCloseAndCleanup
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("=== 工作簿关闭与资源清理示例 ===");
try
{
// 测试正确的关闭方式
TestProperClose();
// 测试资源泄漏检测
TestResourceLeakDetection();
// 测试异常情况下的清理
TestCleanupOnException();
Console.WriteLine("\n✓ 所有关闭与清理测试完成");
}
catch (Exception ex)
{
Console.WriteLine($"✗ 测试时出错: {ex.Message}");
}
Console.WriteLine("按任意键退出...");
Console.ReadKey();
}
static void TestProperClose()
{
Console.WriteLine("\n测试1：正确的关闭方式");
long initialMemory = GetCurrentMemoryUsage();
// 方式1：使用using语句（推荐）
using (var excelApp = ExcelFactory.BlankWorkbook())
{
var workbook = excelApp.ActiveWorkbook;
workbook.ActiveSheetWrap.Range("A1").Value = "测试数据";
workbook.SaveAs("ProperCloseTest.xlsx");
// using语句会自动调用Dispose()
}
long afterUsingMemory = GetCurrentMemoryUsage();
Console.WriteLine($" ✓ Using语句方式 - 内存变化: {initialMemory} → {afterUsingMemory} KB");
// 方式2：手动管理（不推荐，仅用于演示）
initialMemory = GetCurrentMemoryUsage();
IExcelApplication excelApp2 = null;
try
{
excelApp2 = ExcelFactory.BlankWorkbook();
var workbook2 = excelApp2.ActiveWorkbook;
workbook2.ActiveSheetWrap.Range("A1").Value = "手动管理测试";
workbook2.SaveAs("ManualCloseTest.xlsx");
}
finally
{
// 手动清理
if (excelApp2 != null)
{
excelApp2.Quit();
Marshal.FinalReleaseComObject(excelApp2);
}
}
long afterManualMemory = GetCurrentMemoryUsage();
Console.WriteLine($" ✓ 手动管理方式 - 内存变化: {initialMemory} → {afterManualMemory} KB");
// 强制垃圾回收以观察内存变化
GC.Collect();
GC.WaitForPendingFinalizers();
long afterGCMemory = GetCurrentMemoryUsage();
Console.WriteLine($" ⚠ 垃圾回收后内存: {afterGCMemory} KB");
}
static void TestResourceLeakDetection()
{
Console.WriteLine("\n测试2：资源泄漏检测");
var leakDetector = new ResourceLeakDetector();
leakDetector.DetectLeaks();
}
static void TestCleanupOnException()
{
Console.WriteLine("\n测试3：异常情况下的清理");
long initialMemory = GetCurrentMemoryUsage();
try
{
// 模拟异常情况
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
// 正常操作
workbook.ActiveSheetWrap.Range("A1").Value = "异常测试";
// 模拟异常
throw new InvalidOperationException("测试异常");
// 这里的代码不会执行
workbook.SaveAs("ExceptionTest.xlsx");
}
catch (Exception ex)
{
Console.WriteLine($" ⚠ 捕获异常: {ex.Message}");
// 验证资源是否已正确清理
long afterExceptionMemory = GetCurrentMemoryUsage();
Console.WriteLine($" ✓ 异常处理后内存: {afterExceptionMemory} KB");
// 强制垃圾回收
GC.Collect();
GC.WaitForPendingFinalizers();
long afterGCMemory = GetCurrentMemoryUsage();
Console.WriteLine($" ⚠ 垃圾回收后内存: {afterGCMemory} KB");
if (afterGCMemory <= initialMemory + 1000) // 允许1MB的波动
{
Console.WriteLine(" ✓ 资源清理正常，没有明显泄漏");
}
else
{
Console.WriteLine(" ⚠ 检测到可能的资源泄漏");
}
}
}
static long GetCurrentMemoryUsage()
{
return Process.GetCurrentProcess().WorkingSet64 / 1024;
}
}
public class ResourceLeakDetector
{
public void DetectLeaks()
{
Console.WriteLine(" 开始资源泄漏检测...");
long initialMemory = GetCurrentMemoryUsage();
// 创建多个工作簿但不正确清理
for (int i = 0; i < 5; i++)
{
// 错误的方式：不释放资源
var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
workbook.ActiveSheetWrap.Range("A1").Value = $"泄漏测试{i}";
// 不调用Dispose()，模拟泄漏
if (i < 4) // 前4个不释放，最后一个正确释放
{
Console.WriteLine($" ⚠ 故意不释放工作簿{i + 1}");
}
else
{
Console.WriteLine($" ✓ 正确释放工作簿{i + 1}");
excelApp.Dispose();
}
Thread.Sleep(500);
}
long afterCreationMemory = GetCurrentMemoryUsage();
Console.WriteLine($" 创建后内存: {afterCreationMemory} KB (增加: {afterCreationMemory - initialMemory} KB)");
// 强制垃圾回收
GC.Collect();
GC.WaitForPendingFinalizers();
long afterGCMemory = GetCurrentMemoryUsage();
Console.WriteLine($" 垃圾回收后内存: {afterGCMemory} KB");
// 分析泄漏情况
long memoryIncrease = afterGCMemory - initialMemory;
if (memoryIncrease > 5000) // 超过5MB认为有泄漏
{
Console.WriteLine($" ⚠ 检测到可能的资源泄漏，内存增加: {memoryIncrease} KB");
}
else
{
Console.WriteLine(" ✓ 没有检测到明显的资源泄漏");
}
// 建议的最佳实践
Console.WriteLine("\n 资源管理最佳实践:");
Console.WriteLine(" 1. 始终使用using语句");
Console.WriteLine(" 2. 避免在循环中创建大量COM对象");
Console.WriteLine(" 3. 定期调用GC.Collect()（谨慎使用）");
Console.WriteLine(" 4. 监控应用程序内存使用情况");
}
private long GetCurrentMemoryUsage()
{
return Process.GetCurrentProcess().WorkingSet64 / 1024;
}
}
}
工作表基础操作
工作表创建与管理
工作表创建与配置
工作表是Excel工作簿的核心组成部分，MudTools.OfficeInterop.Excel提供了丰富的工作表操作功能：
using System;
using MudTools.OfficeInterop;
using MudTools.OfficeInterop.Excel;
namespace WorksheetManagementExample
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("=== 工作表管理示例 ===");
try
{
// 创建工作簿并管理工作表
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
// 工作表管理操作
ManageWorksheets(workbook);
// 保存工作簿
string fileName = $"WorksheetManagement_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
workbook.SaveAs(fileName);
Console.WriteLine($"\n✓ 工作表管理示例完成: {fileName}");
}
catch (Exception ex)
{
Console.WriteLine($"✗ 工作表管理时出错: {ex.Message}");
}
Console.WriteLine("按任意键退出...");
Console.ReadKey();
}
static void ManageWorksheets(IExcelWorkbook workbook)
{
Console.WriteLine("\n1. 工作表基本操作");
// 获取默认工作表
var defaultSheet = workbook.ActiveSheetWrap;
defaultSheet.Name = "主工作表";
Console.WriteLine($" 默认工作表: {defaultSheet.Name}");
// 创建新工作表
Console.WriteLine("\n2. 创建新工作表");
for (int i = 1; i <= 3; i++)
{
var newSheet = workbook.Worksheets.Add();
newSheet.Name = $"数据表{i}";
// 设置工作表标签颜色
newSheet.Tab.Color = GetTabColor(i);
// 在新工作表中添加数据
InitializeWorksheet(newSheet, i);
Console.WriteLine($" ✓ 创建工作表: {newSheet.Name}");
}
// 工作表排序和重命名
Console.WriteLine("\n3. 工作表排序和重命名");
// 重命名工作表
var sheetToRename = workbook.Worksheets[2];
sheetToRename.Name = "重命名的工作表";
Console.WriteLine($" ✓ 重命名工作表: {sheetToRename.Name}");
// 移动工作表位置
var sheetToMove = workbook.Worksheets["数据表3"];
sheetToMove.Move(workbook.Worksheets[1]); // 移动到第一个位置
Console.WriteLine(" ✓ 移动工作表位置");
// 显示所有工作表信息
Console.WriteLine("\n4. 工作表列表");
DisplayWorksheetList(workbook);
// 工作表保护
Console.WriteLine("\n5. 工作表保护");
ProtectWorksheets(workbook);
}
static System.Drawing.Color GetTabColor(int index)
{
return index switch
{
1 => System.Drawing.Color.LightBlue,
2 => System.Drawing.Color.LightGreen,
3 => System.Drawing.Color.LightYellow,
_ => System.Drawing.Color.LightGray
};
}
static void InitializeWorksheet(IExcelWorksheet worksheet, int sheetNumber)
{
// 设置标题
worksheet.Range("A1").Value = $"工作表{sheetNumber} - 示例数据";
worksheet.Range("A1").Font.Bold = true;
worksheet.Range("A1").Font.Size = 14;
// 添加表头
string[] headers = { "项目", "数量", "单价", "总价" };
for (int i = 0; i < headers.Length; i++)
{
worksheet.Cells[3, i + 1].Value = headers[i];
worksheet.Cells[3, i + 1].Font.Bold = true;
worksheet.Cells[3, i + 1].Interior.Color = System.Drawing.Color.LightGray;
}
// 添加示例数据
for (int i = 1; i <= 5; i++)
{
int row = i + 3;
worksheet.Cells[row, 1].Value = $"项目{sheetNumber}-{i}";
worksheet.Cells[row, 2].Value = i * 2;
worksheet.Cells[row, 3].Value = i * 100;
worksheet.Cells[row, 4].Formula = $"=B{row}*C{row}";
}
// 设置格式
worksheet.Range("A3:D8").Borders.LineStyle = XlLineStyle.xlContinuous;
worksheet.Columns["A:D"].AutoFit();
}
static void DisplayWorksheetList(IExcelWorkbook workbook)
{
Console.WriteLine(" 工作簿中的工作表:");
for (int i = 1; i <= workbook.Worksheets.Count; i++)
{
var sheet = workbook.Worksheets[i];
Console.WriteLine($" {i}. {sheet.Name} (Visible: {sheet.Visible}, Index: {sheet.Index})");
}
}
static void ProtectWorksheets(IExcelWorkbook workbook)
{
// 保护特定工作表
var sheetToProtect = workbook.Worksheets["重命名的工作表"];
sheetToProtect.Protect("password123", true, true, true, true, true, true, true, true);
Console.WriteLine(" ✓ 工作表保护已启用");
// 尝试修改受保护的工作表（应该失败）
try
{
sheetToProtect.Range("A1").Value = "尝试修改";
Console.WriteLine(" ⚠ 修改成功（不应该发生）");
}
catch (Exception ex)
{
Console.WriteLine($" ✓ 修改被阻止（预期行为）: {ex.Message}");
}
// 取消保护
sheetToProtect.Unprotect("password123");
Console.WriteLine(" ✓ 工作表保护已取消");
// 验证可以修改
sheetToProtect.Range("A1").Value = "现在可以修改";
Console.WriteLine(" ✓ 修改成功");
}
}
}
工作表高级操作
工作表的高级操作包括复制、隐藏、分组等功能：
using System;
using MudTools.OfficeInterop;
using MudTools.OfficeInterop.Excel;
namespace WorksheetAdvancedOperations
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("=== 工作表高级操作示例 ===");
try
{
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
// 执行高级操作
PerformAdvancedOperations(workbook);
// 保存工作簿
string fileName = $"AdvancedWorksheetOps_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
workbook.SaveAs(fileName);
Console.WriteLine($"\n✓ 高级操作示例完成: {fileName}");
}
catch (Exception ex)
{
Console.WriteLine($"✗ 高级操作时出错: {ex.Message}");
}
Console.WriteLine("按任意键退出...");
Console.ReadKey();
}
static void PerformAdvancedOperations(IExcelWorkbook workbook)
{
// 创建基础工作表
var mainSheet = workbook.ActiveSheetWrap;
mainSheet.Name = "主数据";
InitializeMainSheet(mainSheet);
Console.WriteLine("\n1. 工作表复制操作");
// 复制工作表
var copiedSheet = mainSheet.Copy();
copiedSheet.Name = "数据备份";
Console.WriteLine(" ✓ 工作表复制完成");
// 复制到特定位置
var specificCopy = mainSheet.Copy(workbook.Worksheets[1]);
specificCopy.Name = "前置副本";
Console.WriteLine(" ✓ 特定位置复制完成");
Console.WriteLine("\n2. 工作表隐藏与显示");
// 隐藏工作表
var sheetToHide = workbook.Worksheets.Add();
sheetToHide.Name = "隐藏的工作表";
sheetToHide.Visible = XlSheetVisibility.xlSheetHidden;
Console.WriteLine(" ✓ 工作表隐藏完成");
// 深度隐藏（用户无法通过界面取消隐藏）
var veryHiddenSheet = workbook.Worksheets.Add();
veryHiddenSheet.Name = "深度隐藏表";
veryHiddenSheet.Visible = XlSheetVisibility.xlSheetVeryHidden;
Console.WriteLine(" ✓ 深度隐藏完成");
Console.WriteLine("\n3. 工作表分组操作");
// 创建分组工作表
CreateWorksheetGroup(workbook);
Console.WriteLine("\n4. 工作表保护与权限");
// 高级保护设置
ConfigureAdvancedProtection(workbook);
Console.WriteLine("\n5. 工作表事件处理");
// 工作表事件演示
SetupWorksheetEvents(workbook);
}
static void InitializeMainSheet(IExcelWorksheet worksheet)
{
worksheet.Range("A1").Value = "主数据表";
worksheet.Range("A1").Font.Bold = true;
// 添加示例数据
for (int i = 1; i <= 10; i++)
{
worksheet.Cells[i + 1, 1].Value = $"数据项{i}";
worksheet.Cells[i + 1, 2].Value = i * 50;
worksheet.Cells[i + 1, 3].Value = DateTime.Now.AddDays(i);
}
worksheet.Columns["A:C"].AutoFit();
}
static void CreateWorksheetGroup(IExcelWorkbook workbook)
{
// 创建一组相关的工作表
for (int i = 1; i <= 3; i++)
{
var groupSheet = workbook.Worksheets.Add();
groupSheet.Name = $"季度{i}数据";
// 设置相同的格式
groupSheet.Range("A1").Value = $"第{i}季度报告";
groupSheet.Range("A1").Font.Bold = true;
groupSheet.Tab.Color = System.Drawing.Color.LightBlue;
Console.WriteLine($" ✓ 创建分组工作表: {groupSheet.Name}");
}
// 可以选择性地分组这些工作表
// 注意：实际分组操作需要通过VBA或用户界面完成
Console.WriteLine(" ⚠ 工作表分组通常通过VBA或用户界面完成");
}
static void ConfigureAdvancedProtection(IExcelWorkbook workbook)
{
var protectedSheet = workbook.Worksheets.Add();
protectedSheet.Name = "高级保护表";
// 设置允许用户编辑的区域
protectedSheet.Range("A1:A10").Value = "可编辑区域";
protectedSheet.Range("B1:B10").Value = "受保护区域";
// 保护工作表，但允许某些操作
protectedSheet.Protect("advanced123",
allowFormattingCells: true,
allowFormattingColumns: true,
allowFormattingRows: true,
allowInsertingColumns: false,
allowInsertingRows: false,
allowInsertingHyperlinks: false,
allowDeletingColumns: false,
allowDeletingRows: false,
allowSorting: true,
allowFiltering: true,
allowUsingPivotTables: true
);
Console.WriteLine(" ✓ 高级保护设置完成");
// 测试保护效果
try
{
// 尝试在受保护区域修改（应该失败）
protectedSheet.Range("B1").Value = "尝试修改";
Console.WriteLine(" ⚠ 修改成功（不应该发生）");
}
catch (Exception)
{
Console.WriteLine(" ✓ 受保护区域修改被阻止（预期行为）");
}
try
{
// 尝试在可编辑区域修改（应该成功）
protectedSheet.Range("A1").Value = "修改成功";
Console.WriteLine(" ✓ 可编辑区域修改成功");
}
catch (Exception ex)
{
Console.WriteLine($" ⚠ 可编辑区域修改失败: {ex.Message}");
}
}
static void SetupWorksheetEvents(IExcelWorkbook workbook)
{
var eventSheet = workbook.Worksheets.Add();
eventSheet.Name = "事件测试表";
// 注意：工作表事件处理需要通过事件处理程序
// 这里演示事件处理的基本概念
eventSheet.Range("A1").Value = "事件测试区域";
eventSheet.Range("A1").Interior.Color = System.Drawing.Color.LightYellow;
Console.WriteLine(" ⚠ 工作表事件处理需要额外的事件处理程序配置");
Console.WriteLine(" 常见事件: SelectionChange, Change, Activate, Deactivate");
// 在实际应用中，可以通过EventHandler类来处理事件
// var eventHandler = new EventHandler(workbook);
// eventHandler.WorksheetSelectionChange += OnWorksheetSelectionChange;
}
}
}
工作表导航与选择
工作表导航操作
工作表导航是Excel自动化中的基本操作，包括激活、选择、遍历等工作表：
using System;
using MudTools.OfficeInterop;
using MudTools.OfficeInterop.Excel;
namespace WorksheetNavigationExample
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("=== 工作表导航示例 ===");
try
{
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
// 创建多个工作表用于导航测试
CreateNavigationWorksheets(workbook);
// 执行导航操作
PerformNavigationOperations(workbook);
// 保存工作簿
string fileName = $"WorksheetNavigation_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
workbook.SaveAs(fileName);
Console.WriteLine($"\n✓ 导航操作示例完成: {fileName}");
}
catch (Exception ex)
{
Console.WriteLine($"✗ 导航操作时出错: {ex.Message}");
}
Console.WriteLine("按任意键退出...");
Console.ReadKey();
}
static void CreateNavigationWorksheets(IExcelWorkbook workbook)
{
Console.WriteLine("创建导航测试工作表...");
// 重命名默认工作表
workbook.ActiveSheetWrap.Name = "首页";
// 创建多个工作表
for (int i = 1; i <= 5; i++)
{
var sheet = workbook.Worksheets.Add();
sheet.Name = $"工作表{i}";
// 在每个工作表中添加标识
sheet.Range("A1").Value = $"这是{sheet.Name}";
sheet.Range("A1").Font.Bold = true;
sheet.Range("A1").Font.Color = GetSheetColor(i);
// 添加导航标记
for (int j = 1; j <= 3; j++)
{
sheet.Cells[j + 2, 1].Value = $"导航点{j}";
sheet.Cells[j + 2, 2].Value = j * 100;
}
Console.WriteLine($" ✓ 创建: {sheet.Name}");
}
}
static System.Drawing.Color GetSheetColor(int index)
{
return index switch
{
1 => System.Drawing.Color.Red,
2 => System.Drawing.Color.Blue,
3 => System.Drawing.Color.Green,
4 => System.Drawing.Color.Orange,
5 => System.Drawing.Color.Purple,
_ => System.Drawing.Color.Black
};
}
static void PerformNavigationOperations(IExcelWorkbook workbook)
{
Console.WriteLine("\n1. 基础导航操作");
// 激活特定工作表
var sheet3 = workbook.Worksheets["工作表3"];
sheet3.Activate();
Console.WriteLine($" ✓ 激活工作表: {sheet3.Name}");
// 获取当前活动工作表
var activeSheet = workbook.ActiveSheetWrap;
Console.WriteLine($" 当前活动工作表: {activeSheet.Name}");
Console.WriteLine("\n2. 工作表遍历");
// 遍历所有工作表
Console.WriteLine(" 工作表列表:");
foreach (IExcelWorksheet sheet in workbook.Worksheets)
{
string status = sheet == activeSheet ? "[当前]" : "";
Console.WriteLine($" {sheet.Index}. {sheet.Name} {status}");
}
Console.WriteLine("\n3. 条件导航");
// 根据条件导航到特定工作表
NavigateByCondition(workbook);
Console.WriteLine("\n4. 工作表选择操作");
// 选择多个工作表
SelectMultipleWorksheets(workbook);
Console.WriteLine("\n5. 高级导航技巧");
// 使用高级导航技术
AdvancedNavigationTechniques(workbook);
}
static void NavigateByCondition(IExcelWorkbook workbook)
{
// 导航到包含特定内容的工作表
foreach (IExcelWorksheet sheet in workbook.Worksheets)
{
// 检查工作表是否包含特定内容
var searchValue = sheet.Range("A1").Value?.ToString();
if (searchValue != null && searchValue.Contains("工作表3"))
{
sheet.Activate();
Console.WriteLine($" ✓ 条件导航到: {sheet.Name}");
break;
}
}
// 导航到第一个可见工作表
foreach (IExcelWorksheet sheet in workbook.Worksheets)
{
if (sheet.Visible == XlSheetVisibility.xlSheetVisible)
{
sheet.Activate();
Console.WriteLine($" ✓ 导航到第一个可见工作表: {sheet.Name}");
break;
}
}
}
static void SelectMultipleWorksheets(IExcelWorkbook workbook)
{
Console.WriteLine(" 选择多个工作表:");
// 选择连续的工作表
try
{
workbook.Worksheets["工作表1"].Select();
workbook.Worksheets["工作表2"].Select(false); // 添加到选择
workbook.Worksheets["工作表3"].Select(false); // 添加到选择
Console.WriteLine(" ✓ 选择连续工作表: 工作表1, 工作表2, 工作表3");
}
catch (Exception ex)
{
Console.WriteLine($" ⚠ 连续选择失败: {ex.Message}");
}
// 选择不连续的工作表（需要通过VBA或特定方法）
Console.WriteLine(" ⚠ 不连续选择通常通过VBA或特定API完成");
}
static void AdvancedNavigationTechniques(IExcelWorkbook workbook)
{
Console.WriteLine("\n5. 高级导航技巧");
// 技巧1：使用索引导航
var firstSheet = workbook.Worksheets[1];
firstSheet.Activate();
Console.WriteLine($" ✓ 索引导航: {firstSheet.Name}");
// 技巧2：使用名称导航（带错误处理）
try
{
var nonExistentSheet = workbook.Worksheets["不存在的工作表"];
nonExistentSheet.Activate();
}
catch (Exception ex)
{
Console.WriteLine($" ✓ 错误处理导航: {ex.Message}");
}
// 技巧3：循环导航
Console.WriteLine(" 循环导航演示:");
for (int i = 1; i <= Math.Min(3, workbook.Worksheets.Count); i++)
{
workbook.Worksheets[i].Activate();
Console.WriteLine($" → 导航到: {workbook.ActiveSheetWrap.Name}");
Thread.Sleep(500); // 模拟延迟
}
// 技巧4：保存和恢复导航状态
Console.WriteLine("\n6. 导航状态管理");
var navigationManager = new WorksheetNavigationManager(workbook);
navigationManager.DemonstrateStateManagement();
}
}
public class WorksheetNavigationManager
{
private IExcelWorkbook _workbook;
private Stack<WorksheetNavigationState> _navigationStack;
public WorksheetNavigationManager(IExcelWorkbook workbook)
{
_workbook = workbook;
_navigationStack = new Stack<WorksheetNavigationState>();
}
public void DemonstrateStateManagement()
{
Console.WriteLine(" 导航状态管理演示:");
// 保存当前状态
SaveCurrentState();
Console.WriteLine(" ✓ 保存当前导航状态");
// 导航到其他工作表
_workbook.Worksheets["工作表4"].Activate();
Console.WriteLine($" → 导航到: {_workbook.ActiveSheetWrap.Name}");
// 保存新状态
SaveCurrentState();
Console.WriteLine(" ✓ 保存新导航状态");
// 恢复上一个状态
RestorePreviousState();
Console.WriteLine($" ← 恢复到: {_workbook.ActiveSheetWrap.Name}");
// 显示导航历史
DisplayNavigationHistory();
}
private void SaveCurrentState()
{
var state = new WorksheetNavigationState
{
SheetName = _workbook.ActiveSheetWrap.Name,
Timestamp = DateTime.Now,
Selection = _workbook.ActiveSheetWrap.Selection?.Address
};
_navigationStack.Push(state);
}
private void RestorePreviousState()
{
if (_navigationStack.Count > 1)
{
_navigationStack.Pop(); // 移除当前状态
var previousState = _navigationStack.Peek();
// 导航到之前的工作表
var targetSheet = _workbook.Worksheets[previousState.SheetName];
targetSheet.Activate();
}
}
private void DisplayNavigationHistory()
{
Console.WriteLine(" 导航历史:");
int count = 1;
foreach (var state in _navigationStack.Reverse())
{
string currentIndicator = count == _navigationStack.Count ? "[当前]" : "";
Console.WriteLine($" {count}. {state.SheetName} - {state.Timestamp:HH:mm:ss} {currentIndicator}");
count++;
}
}
}
public class WorksheetNavigationState
{
public string SheetName { get; set; }
public DateTime Timestamp { get; set; }
public string Selection { get; set; }
}
}
实战应用案例
企业报表系统
多工作表报表生成
在实际企业应用中，经常需要生成包含多个工作表的复杂报表：
using System;
using System.Collections.Generic;
using MudTools.OfficeInterop;
using MudTools.OfficeInterop.Excel;
namespace EnterpriseReportSystem
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("=== 企业报表系统示例 ===");
try
{
// 创建报表生成器
var reportGenerator = new EnterpriseReportGenerator();
// 生成月度财务报告
reportGenerator.GenerateMonthlyFinancialReport();
// 生成销售分析报告
reportGenerator.GenerateSalesAnalysisReport();
// 生成员工绩效报告
reportGenerator.GenerateEmployeePerformanceReport();
Console.WriteLine("\n✓ 所有企业报表生成完成");
}
catch (Exception ex)
{
Console.WriteLine($"✗ 报表生成时出错: {ex.Message}");
}
Console.WriteLine("按任意键退出...");
Console.ReadKey();
}
}
public class EnterpriseReportGenerator
{
public void GenerateMonthlyFinancialReport()
{
Console.WriteLine("\n1. 生成月度财务报告");
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
// 创建封面页
CreateReportCover(workbook, "2024年12月财务报告");
// 创建资产负债表
CreateBalanceSheet(workbook);
// 创建利润表
CreateIncomeStatement(workbook);
// 创建现金流量表
CreateCashFlowStatement(workbook);
// 创建财务分析页
CreateFinancialAnalysis(workbook);
// 保存报告
string fileName = $"MonthlyFinancialReport_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
workbook.SaveAs(fileName);
Console.WriteLine($" ✓ 月度财务报告生成完成: {fileName}");
DisplayReportSummary(workbook);
}
public void GenerateSalesAnalysisReport()
{
Console.WriteLine("\n2. 生成销售分析报告");
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
// 销售数据工作表
CreateSalesDataSheet(workbook);
// 区域分析工作表
CreateRegionalAnalysis(workbook);
// 产品分析工作表
CreateProductAnalysis(workbook);
// 趋势分析工作表
CreateTrendAnalysis(workbook);
string fileName = $"SalesAnalysisReport_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
workbook.SaveAs(fileName);
Console.WriteLine($" ✓ 销售分析报告生成完成: {fileName}");
}
public void GenerateEmployeePerformanceReport()
{
Console.WriteLine("\n3. 生成员工绩效报告");
using var excelApp = ExcelFactory.BlankWorkbook();
var workbook = excelApp.ActiveWorkbook;
// 员工信息表
CreateEmployeeInfoSheet(workbook);
// 绩效评分表
CreatePerformanceRatingSheet(workbook);
// 部门汇总表
CreateDepartmentSummary(workbook);
// 个人发展计划表
CreateDevelopmentPlanSheet(workbook);
string fileName = $"EmployeePerformanceReport_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
workbook.SaveAs(fileName);
Console.WriteLine($" ✓ 员工绩效报告生成完成: {fileName}");
}
private void CreateReportCover(IExcelWorkbook workbook, string reportTitle)
{
var coverSheet = workbook.ActiveSheetWrap;
coverSheet.Name = "报告封面";
// 设置封面格式
coverSheet.Range("A1").Value = reportTitle;
coverSheet.Range("A1").Font.Bold = true;
coverSheet.Range("A1").Font.Size = 24;
coverSheet.Range("A1").Font.Color = System.Drawing.Color.DarkBlue;
coverSheet.Range("A3").Value = "公司名称: ABC科技有限公司";
coverSheet.Range("A4").Value = $"报告期间: {DateTime.Now:yyyy年MM月}";
coverSheet.Range("A5").Value = $"生成时间: {DateTime.Now:yyyy-MM-dd HH:mm:ss}";
coverSheet.Range("A6").Value = $"生成人员: {Environment.UserName}";
coverSheet.Range("A8").Value = "报告包含内容:";
coverSheet.Range("A8").Font.Bold = true;
string[] contents = {
"• 资产负债表",
"• 利润表",
"• 现金流量表",
"• 财务分析"
};
for (int i = 0; i < contents.Length; i++)
{
coverSheet.Range($"A{i + 9}").Value = contents[i];
}
// 设置列宽
coverSheet.Columns["A"].ColumnWidth = 30;
Console.WriteLine(" ✓ 报告封面创建完成");
}
private void CreateBalanceSheet(IExcelWorkbook workbook)
{
var balanceSheet = workbook.Worksheets.Add();
balanceSheet.Name = "资产负债表";
balanceSheet.Tab.Color = System.Drawing.Color.LightBlue;
// 资产负债表结构
balanceSheet.Range("A1").Value = "资产负债表";
balanceSheet.Range("A1").Font.Bold = true;
balanceSheet.Range("A1").Font.Size = 16;
// 资产部分
balanceSheet.Range("A3").Value = "资产";
balanceSheet.Range("A3").Font.Bold = true;
string[] assets = {
"货币资金", "应收账款", "存货", "固定资产", "无形资产", "资产总计"
};
decimal[] assetValues = { 5000000, 2000000, 1500000, 8000000, 1000000, 0 };
assetValues[5] = assetValues.Sum(); // 自动计算总计
for (int i = 0; i < assets.Length; i++)
{
balanceSheet.Cells[i + 4, 1].Value = assets[i];
balanceSheet.Cells[i + 4, 2].Value = assetValues[i];
}
// 负债和所有者权益部分
balanceSheet.Range("A12").Value = "负债和所有者权益";
balanceSheet.Range("A12").Font.Bold = true;
string[] liabilities = {
"短期借款", "应付账款", "长期借款", "负债合计",
"实收资本", "留存收益", "所有者权益合计", "负债和权益总计"
};
decimal[] liabilityValues = { 1000000, 800000, 2000000, 0, 5000000, 1200000, 0, 0 };
liabilityValues[3] = liabilityValues[0] + liabilityValues[1] + liabilityValues[2]; // 负债合计
liabilityValues[6] = liabilityValues[4] + liabilityValues[5]; // 所有者权益合计
liabilityValues[7] = liabilityValues[3] + liabilityValues[6]; // 总计
for (int i = 0; i < liabilities.Length; i++)
{
balanceSheet.Cells[i + 13, 1].Value = liabilities[i];
balanceSheet.Cells[i + 13, 2].Value = liabilityValues[i];
}
// 设置数字格式
balanceSheet.Range("B4:B21").NumberFormat = "¥#,##0";
// 设置边框
balanceSheet.Range("A3:B10").Borders.LineStyle = XlLineStyle.xlContinuous;
balanceSheet.Range("A12:B20").Borders.LineStyle = XlLineStyle.xlContinuous;
// 自动调整列宽
balanceSheet.Columns["A:B"].AutoFit();
Console.WriteLine(" ✓ 资产负债表创建完成");
}
private void CreateIncomeStatement(IExcelWorkbook workbook)
{
var incomeSheet = workbook.Worksheets.Add();
incomeSheet.Name = "利润表";
incomeSheet.Tab.Color = System.Drawing.Color.LightGreen;
// 利润表结构
incomeSheet.Range("A1").Value = "利润表";
incomeSheet.Range("A1").Font.Bold = true;
incomeSheet.Range("A1").Font.Size = 16;
string[] incomeItems = {
"营业收入", "营业成本", "毛利润",
"销售费用", "管理费用", "财务费用", "营业利润",
"营业外收入", "营业外支出", "利润总额",
"所得税费用", "净利润"
};
decimal[] incomeValues = {
10000000, 6000000, 0, // 营业收入，营业成本，毛利润
800000, 500000, 200000, 0, // 各项费用，营业利润
100000, 50000, 0, // 营业外收支，利润总额
875000, 0 // 所得税，净利润
};
// 计算衍生值
incomeValues[2] = incomeValues[0] - incomeValues[1]; // 毛利润
incomeValues[6] = incomeValues[2] - incomeValues[3] - incomeValues[4] - incomeValues[5]; // 营业利润
incomeValues[9] = incomeValues[6] + incomeValues[7] - incomeValues[8]; // 利润总额
incomeValues[11] = incomeValues[9] - incomeValues[10]; // 净利润
for (int i = 0; i < incomeItems.Length; i++)
{
incomeSheet.Cells[i + 3, 1].Value = incomeItems[i];
incomeSheet.Cells[i + 3, 2].Value = incomeValues[i];
// 为计算项设置特殊格式
if (new[] { 2, 6, 9, 11 }.Contains(i))
{
incomeSheet.Cells[i + 3, 1].Font.Bold = true;
incomeSheet.Cells[i + 3, 2].Font.Bold = true;
}
}
// 设置数字格式
incomeSheet.Range("B3:B15").NumberFormat = "¥#,##0";
// 设置边框
incomeSheet.Range("A3:B15").Borders.LineStyle = XlLineStyle.xlContinuous;
incomeSheet.Columns["A:B"].AutoFit();
Console.WriteLine(" ✓ 利润表创建完成");
}
private void CreateCashFlowStatement(IExcelWorkbook workbook)
{
var cashFlowSheet = workbook.Worksheets.Add();
cashFlowSheet.Name = "现金流量表";
cashFlowSheet.Tab.Color = System.Drawing.Color.LightYellow;
// 现金流量表结构
cashFlowSheet.Range("A1").Value = "现金流量表";
cashFlowSheet.Range("A1").Font.Bold = true;
cashFlowSheet.Range("A1").Font.Size = 16;
// 经营活动现金流量
cashFlowSheet.Range("A3").Value = "经营活动产生的现金流量";
cashFlowSheet.Range("A3").Font.Bold = true;
string[] operatingItems = {
"销售商品、提供劳务收到的现金", "购买商品、接受劳务支付的现金",
"支付给职工以及为职工支付的现金", "支付的各项税费",
"经营活动现金流量净额"
};
decimal[] operatingValues = { 9500000, 5500000, 1200000, 800000, 0 };
operatingValues[4] = operatingValues[0] - operatingValues[1] - operatingValues[2] - operatingValues[3];
for (int i = 0; i < operatingItems.Length; i++)
{
cashFlowSheet.Cells[i + 4, 1].Value = operatingItems[i];
cashFlowSheet.Cells[i + 4, 2].Value = operatingValues[i];
}
// 投资活动现金流量
cashFlowSheet.Range("A10").Value = "投资活动产生的现金流量";
cashFlowSheet.Range("A10").Font.Bold = true;
string[] investingItems = {
"购建固定资产支付的现金", "投资支付的现金", "投资活动现金流量净额"
};
decimal[] investingValues = { 500000, 300000, 0 };
investingValues[2] = -investingValues[0] - investingValues[1];
for (int i = 0; i < investingItems.Length; i++)
{
cashFlowSheet.Cells[i + 11, 1].Value = investingItems[i];
cashFlowSheet.Cells[i + 11, 2].Value = investingValues[i];
}
// 筹资活动现金流量
cashFlowSheet.Range("A14").Value = "筹资活动产生的现金流量";
cashFlowSheet.Range("A14").Font.Bold = true;
string[] financingItems = {
"吸收投资收到的现金", "取得借款收到的现金",
"偿还债务支付的现金", "分配股利支付的现金", "筹资活动现金流量净额"
};
decimal[] financingValues = { 1000000, 500000, 300000, 200000, 0 };
financingValues[4] = financingValues[0] + financingValues[1] - financingValues[2] - financingValues[3];
for (int i = 0; i < financingItems.Length; i++)
{
cashFlowSheet.Cells[i + 15, 1].Value = financingItems[i];
cashFlowSheet.Cells[i + 15, 2].Value = financingValues[i];
}
// 现金净增加额
cashFlowSheet.Range("A21").Value = "现金及现金等价物净增加额";
cashFlowSheet.Range("A21").Font.Bold = true;
cashFlowSheet.Range("B21").Formula = "=B8+B13+B19";
// 设置数字格式
cashFlowSheet.Range("B4:B21").NumberFormat = "¥#,##0";
// 设置边框
cashFlowSheet.Range("A3:B8").Borders.LineStyle = XlLineStyle.xlContinuous;
cashFlowSheet.Range("A10:B13").Borders.LineStyle = XlLineStyle.xlContinuous;
cashFlowSheet.Range("A14:B19").Borders.LineStyle = XlLineStyle.xlContinuous;
cashFlowSheet.Columns["A"].ColumnWidth = 40;
cashFlowSheet.Columns["B"].AutoFit();
Console.WriteLine(" ✓ 现金流量表创建完成");
}
private void CreateFinancialAnalysis(IExcelWorkbook workbook)
{
var analysisSheet = workbook.Worksheets.Add();
analysisSheet.Name = "财务分析";
analysisSheet.Tab.Color = System.Drawing.Color.LightPink;
analysisSheet.Range("A1").Value = "财务比率分析";
analysisSheet.Range("A1").Font.Bold = true;
analysisSheet.Range("A1").Font.Size = 16;
// 财务比率计算
string[] ratios = {
"流动比率", "速动比率", "资产负债率",
"毛利率", "净利率", "资产收益率"
};
for (int i = 0; i < ratios.Length; i++)
{
analysisSheet.Cells[i + 3, 1].Value = ratios[i];
analysisSheet.Cells[i + 3, 2].Formula = GetRatioFormula(ratios[i], i + 3);
analysisSheet.Cells[i + 3, 3].Value = GetRatioInterpretation(ratios[i]);
}
analysisSheet.Columns["A:C"].AutoFit();
Console.WriteLine(" ✓ 财务分析表创建完成");
}
private string GetRatioFormula(string ratio, int row)
{
return ratio switch
{
"流动比率" => "=资产负债表!B9/资产负债表!B16",
"速动比率" => "=(资产负债表!B9-资产负债表!B6)/资产负债表!B16",
"资产负债率" => "=资产负债表!B16/资产负债表!B9",
"毛利率" => "=利润表!B5/利润表!B3",
"净利率" => "=利润表!B15/利润表!B3",
"资产收益率" => "=利润表!B15/资产负债表!B9",
_ => "N/A"
};
}
private string GetRatioInterpretation(string ratio)
{
return ratio switch
{
"流动比率" => "衡量短期偿债能力，理想值>2",
"速动比率" => "更严格的短期偿债能力指标，理想值>1",
"资产负债率" => "衡量财务杠杆，理想值<60%",
"毛利率" => "衡量产品盈利能力，越高越好",
"净利率" => "衡量整体盈利能力",
"资产收益率" => "衡量资产使用效率",
_ => "N/A"
};
}
private void DisplayReportSummary(IExcelWorkbook workbook)
{
Console.WriteLine("\n 报告摘要:");
Console.WriteLine($" 工作表数量: {workbook.Worksheets.Count}");
foreach (IExcelWorksheet sheet in workbook.Worksheets)
{
Console.WriteLine($" {sheet.Index}. {sheet.Name}");
}
}
// 其他创建方法（销售数据、员工信息等）的实现类似，这里省略以保持简洁
// 实际应用中应该完整实现所有方法
}
}
MudTools.OfficeInterop.Excel库提供了强大的API支持，使得在.NET环境中进行Excel公式操作变得简单高效。无论是基础的单元格计算还是复杂的财务分析，该库都能提供完整的解决方案。
本文代码示例基于MudTools.OfficeInterop.Excel库，完整的代码已包含中库的示例代码中，实际使用时请参考最新的API文档和示例代码。