工作簿与工作表操作基础

前言

在Excel自动化开发中，工作簿（Workbook）和工作表（Worksheet）是核心的操作对象。熟练掌握工作簿与工作表的操作是构建高效Excel自动化应用的基础。本章将深入探讨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;
        }
    }
}

相关章节

• 工作表操作详解
• 企业报表系统与最佳实践