Aspose.Cells for .NET 教程（二）：工作簿与工作表操作全攻略

Aspose.Cells for .NET下载地址 https://soft51.cc/software/175799451582733643

2.1 创建与加载工作簿

创建新工作簿

// 创建空工作簿
Workbook workbook = new Workbook();

// 基于特定格式创建
Workbook xlsxWorkbook = new Workbook(FileFormatType.Xlsx);

// 从模板创建
Workbook templateWorkbook = new Workbook("template.xlsx");

加载现有工作簿

// 基本加载
Workbook workbook = new Workbook("data.xlsx");

// 使用加载选项
LoadOptions loadOptions = new LoadOptions();
loadOptions.ParsingFormulaOnOpen = true;
loadOptions.Password = "your_password";
Workbook workbook = new Workbook("encrypted.xlsx", loadOptions);

// 从流加载
using (FileStream stream = new FileStream("data.xlsx", FileMode.Open))
{
    Workbook workbook = new Workbook(stream);
}

设置工作簿属性

// 设置文档属性
workbook.BuiltInDocumentProperties.Title = "销售报告";
workbook.BuiltInDocumentProperties.Author = "张三";
workbook.BuiltInDocumentProperties.Company = "某公司";

// 设置自定义属性
workbook.CustomDocumentProperties.Add("部门", "销售部");
workbook.CustomDocumentProperties.Add("版本", "1.0");

2.2 保存工作簿到不同格式

基本保存操作

// 自动识别格式保存
workbook.Save("output.xlsx");
workbook.Save("output.pdf");

// 明确指定格式
workbook.Save("data.xls", SaveFormat.Xls);
workbook.Save("report.pdf", SaveFormat.Pdf);

使用保存选项

// Excel保存选项
XlsxSaveOptions xlsxOptions = new XlsxSaveOptions();
xlsxOptions.ExportCellName = true;
xlsxOptions.DefaultFont = "Arial";
workbook.Save("custom.xlsx", xlsxOptions);

// PDF保存选项
PdfSaveOptions pdfOptions = new PdfSaveOptions();
pdfOptions.AllColumnsInOnePagePerSheet = true;
pdfOptions.SecurityOptions = new PdfSecurityOptions();
pdfOptions.SecurityOptions.UserPassword = "123456";
workbook.Save("secure.pdf", pdfOptions);

// HTML保存选项
HtmlSaveOptions htmlOptions = new HtmlSaveOptions();
htmlOptions.ExportGridLines = true;
htmlOptions.ExportImagesAsBase64 = true;
workbook.Save("report.html", htmlOptions);

保存到流

// 保存到内存流
using (MemoryStream stream = new MemoryStream())
{
    workbook.Save(stream, SaveFormat.Xlsx);
    byte[] data = stream.ToArray();
    // 处理字节数据
}

2.3 添加、删除、重命名工作表

添加工作表

// 添加新工作表
int index = workbook.Worksheets.Add();
Worksheet newSheet = workbook.Worksheets[index];

// 添加指定名称的工作表
Worksheet namedSheet = workbook.Worksheets.Add("销售数据");

// 在指定位置插入
workbook.Worksheets.Insert(1, "插入的工作表");

// 复制现有工作表
int copiedIndex = workbook.Worksheets.AddCopy("Sheet1");
workbook.Worksheets[copiedIndex].Name = "复制表";

删除工作表

// 根据索引删除
workbook.Worksheets.RemoveAt(0);

// 根据名称删除
workbook.Worksheets.RemoveByName("不需要的表");

// 批量删除空工作表
for (int i = workbook.Worksheets.Count - 1; i >= 0; i--)
{
    Worksheet sheet = workbook.Worksheets[i];
    if (sheet.Cells.MaxDataRow == -1)
    {
        workbook.Worksheets.RemoveAt(i);
    }
}

重命名工作表

// 直接重命名
workbook.Worksheets[0].Name = "新名称";

// 批量重命名
for (int i = 0; i < workbook.Worksheets.Count; i++)
{
    workbook.Worksheets[i].Name = $"数据表{i + 1}";
}

// 智能重命名（根据内容）
Worksheet sheet = workbook.Worksheets[0];
Cell firstCell = sheet.Cells[0, 0];
if (firstCell.Value != null)
{
    sheet.Name = firstCell.StringValue.Substring(0, Math.Min(20, firstCell.StringValue.Length));
}

2.4 复制与移动工作表

复制工作表

// 在同一工作簿内复制
workbook.Worksheets.AddCopy(0);
workbook.Worksheets.AddCopy("源工作表");

// 跨工作簿复制
Workbook sourceBook = new Workbook("source.xlsx");
Workbook targetBook = new Workbook("target.xlsx");
targetBook.Worksheets.AddCopy(sourceBook.Worksheets[0]);
targetBook.Save("target.xlsx");

移动工作表

// 交换工作表位置
workbook.Worksheets.SwapSheet(0, 2);

// 移动到指定位置
public void MoveWorksheet(Workbook workbook, int fromIndex, int toIndex)
{
    if (fromIndex == toIndex) return;
    
    if (fromIndex < toIndex)
    {
        for (int i = fromIndex; i < toIndex; i++)
        {
            workbook.Worksheets.SwapSheet(i, i + 1);
        }
    }
    else
    {
        for (int i = fromIndex; i > toIndex; i--)
        {
            workbook.Worksheets.SwapSheet(i, i - 1);
        }
    }
}

// 按名称排序工作表
var sortedSheets = workbook.Worksheets.Cast<Worksheet>()
    .OrderBy(s => s.Name)
    .Select((sheet, index) => new { Sheet = sheet, NewIndex = index })
    .ToList();

foreach (var item in sortedSheets)
{
    MoveWorksheet(workbook, item.Sheet.Index, item.NewIndex);
}

2.5 设置工作表可见性与保护

工作表可见性

// 隐藏工作表
workbook.Worksheets["敏感数据"].IsVisible = false;

// 设置为超级隐藏（在Excel中不显示）
workbook.Worksheets[0].VisibilityType = VisibilityType.VeryHidden;

// 显示工作表
workbook.Worksheets[0].VisibilityType = VisibilityType.Visible;

// 批量设置可见性
string[] hiddenSheets = { "临时数据", "计算表", "备份" };
foreach (string sheetName in hiddenSheets)
{
    if (workbook.Worksheets[sheetName] != null)
    {
        workbook.Worksheets[sheetName].IsVisible = false;
    }
}

工作表保护

// 基本保护
Worksheet sheet = workbook.Worksheets[0];
sheet.Protect(ProtectionType.All);

// 使用密码保护
sheet.Protect(ProtectionType.All, "password123", "");

// 自定义保护选项
Protection protection = sheet.Protection;
protection.AllowDeletingColumn = false;
protection.AllowDeletingRow = false;
protection.AllowEditingContent = false;
protection.AllowEditingObject = false;
protection.AllowEditingScenario = false;
protection.AllowFiltering = true;
protection.AllowFormattingCell = true;
protection.AllowFormattingColumn = true;
protection.AllowFormattingRow = true;
protection.AllowInsertingColumn = false;
protection.AllowInsertingRow = false;
protection.AllowInsertingHyperlink = false;
protection.AllowSorting = true;
protection.AllowUsingPivotTable = true;
protection.AllowSelectingLockedCell = true;
protection.AllowSelectingUnlockedCell = true;

sheet.Protect(ProtectionType.All, "password123", "");

工作簿保护

// 保护工作簿结构
workbook.Protect(ProtectionType.Structure, "workbook_password");

// 保护窗口
workbook.Protect(ProtectionType.Windows, "window_password");

// 同时保护结构和窗口
workbook.Protect(ProtectionType.All, "full_password");

// 取消保护
workbook.Unprotect("full_password");

单元格级别保护

// 锁定特定单元格
Style style = workbook.CreateStyle();
style.IsLocked = true;
sheet.Cells["A1:C10"].SetStyle(style);

// 解锁特定单元格（允许编辑）
Style unlockStyle = workbook.CreateStyle();
unlockStyle.IsLocked = false;
sheet.Cells["B2:B10"].SetStyle(unlockStyle);

// 应用工作表保护
sheet.Protect(ProtectionType.All, "password", "");

高级保护功能

// 设置编辑区域（只有特定区域可编辑）
AllowEditRangeCollection allowRanges = sheet.AllowEditRanges;
int idx = allowRanges.Add("EditableRange", 1, 1, 10, 5);
allowRanges[idx].Password = "edit123";

// 保护后仍允许特定操作
sheet.Protection.AllowFormattingCell = true;
sheet.Protection.AllowInsertingRow = true;
sheet.Protection.AllowDeletingRow = true;
sheet.Protect(ProtectionType.Contents, "protect123", "");

// 数字签名保护
DigitalSignatureCollection signatures = new DigitalSignatureCollection();
// 添加数字签名逻辑...
workbook.SetDigitalSignature(signatures);

综合应用示例

using System;
using System.IO;
using Aspose.Cells;
using System.Collections.Generic;
using System.Linq;

namespace WorkbookWorksheetTutorial
{
    public class WorkbookManager
    {
        private Workbook _workbook;

        public WorkbookManager()
        {
            _workbook = new Workbook();
        }

        public WorkbookManager(string filePath)
        {
            _workbook = new Workbook(filePath);
        }

        /// <summary>
        /// 创建标准化的报告工作簿
        /// </summary>
        public void CreateStandardReport(string[] sheetNames, string title, string author)
        {
            // 设置文档属性
            _workbook.BuiltInDocumentProperties.Title = title;
            _workbook.BuiltInDocumentProperties.Author = author;
            _workbook.BuiltInDocumentProperties.CreatedTime = DateTime.Now;

            // 清除默认工作表
            _workbook.Worksheets.Clear();

            // 添加指定的工作表
            foreach (string sheetName in sheetNames)
            {
                Worksheet sheet = _workbook.Worksheets.Add(sheetName);
                
                // 设置基本格式
                sheet.Cells["A1"].Value = sheetName;
                Style titleStyle = _workbook.CreateStyle();
                titleStyle.Font.IsBold = true;
                titleStyle.Font.Size = 14;
                sheet.Cells["A1"].SetStyle(titleStyle);
                
                // 设置列宽
                sheet.Cells.SetColumnWidth(0, 20);
            }

            // 添加汇总表
            Worksheet summarySheet = _workbook.Worksheets.Add("汇总");
            summarySheet.Cells["A1"].Value = "报告汇总";
            summarySheet.Cells["A2"].Value = "创建日期:";
            summarySheet.Cells["B2"].Value = DateTime.Now;
            summarySheet.Cells["A3"].Value = "工作表数量:";
            summarySheet.Cells["B3"].Value = _workbook.Worksheets.Count;

            // 移动汇总表到第一位
            MoveWorksheetToPosition("汇总", 0);
        }

        /// <summary>
        /// 移动工作表到指定位置
        /// </summary>
        public void MoveWorksheetToPosition(string sheetName, int position)
        {
            Worksheet sheet = _workbook.Worksheets[sheetName];
            if (sheet != null)
            {
                int currentIndex = sheet.Index;
                int steps = Math.Abs(position - currentIndex);
                
                if (currentIndex < position)
                {
                    for (int i = 0; i < steps; i++)
                    {
                        _workbook.Worksheets.SwapSheet(currentIndex + i, currentIndex + i + 1);
                    }
                }
                else if (currentIndex > position)
                {
                    for (int i = 0; i < steps; i++)
                    {
                        _workbook.Worksheets.SwapSheet(currentIndex - i, currentIndex - i - 1);
                    }
                }
            }
        }

        /// <summary>
        /// 批量处理工作表
        /// </summary>
        public void ProcessWorksheets(Action<Worksheet, int> processor)
        {
            for (int i = 0; i < _workbook.Worksheets.Count; i++)
            {
                processor(_workbook.Worksheets[i], i);
            }
        }

        /// <summary>
        /// 应用安全策略
        /// </summary>
        public void ApplySecurityPolicy(string password, bool protectStructure = true, 
            bool hideFormulas = false)
        {
            // 保护工作簿结构
            if (protectStructure)
            {
                _workbook.Protect(ProtectionType.Structure, password);
            }

            // 处理每个工作表
            foreach (Worksheet sheet in _workbook.Worksheets)
            {
                // 隐藏公式
                if (hideFormulas)
                {
                    Style formulaStyle = _workbook.CreateStyle();
                    formulaStyle.IsFormulaHidden = true;
                    
                    for (int row = 0; row <= sheet.Cells.MaxDataRow; row++)
                    {
                        for (int col = 0; col <= sheet.Cells.MaxDataColumn; col++)
                        {
                            Cell cell = sheet.Cells[row, col];
                            if (cell.IsFormula)
                            {
                                cell.SetStyle(formulaStyle);
                            }
                        }
                    }
                }

                // 保护工作表
                sheet.Protect(ProtectionType.All, password, "");
            }
        }

        /// <summary>
        /// 导出多种格式
        /// </summary>
        public void ExportToMultipleFormats(string basePath, string fileName)
        {
            string baseFile = Path.Combine(basePath, fileName);
            
            var formats = new Dictionary<SaveFormat, string>
            {
                { SaveFormat.Xlsx, ".xlsx" },
                { SaveFormat.Pdf, ".pdf" },
                { SaveFormat.Html, ".html" },
                { SaveFormat.CSV, ".csv" }
            };

            foreach (var format in formats)
            {
                try
                {
                    string fullPath = baseFile + format.Value;
                    _workbook.Save(fullPath, format.Key);
                    Console.WriteLine($"已保存: {fullPath}");
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"保存 {format.Value} 格式失败: {ex.Message}");
                }
            }
        }

        /// <summary>
        /// 清理和优化工作簿
        /// </summary>
        public void OptimizeWorkbook()
        {
            // 删除空工作表
            for (int i = _workbook.Worksheets.Count - 1; i >= 0; i--)
            {
                Worksheet sheet = _workbook.Worksheets[i];
                if (IsWorksheetEmpty(sheet))
                {
                    _workbook.Worksheets.RemoveAt(i);
                }
            }

            // 压缩图片
            foreach (Worksheet sheet in _workbook.Worksheets)
            {
                foreach (Picture picture in sheet.Pictures)
                {
                    picture.CompressLevel = 9; // 最高压缩
                }
            }

            // 删除未使用的样式
            _workbook.RemoveUnusedStyles();
        }

        private bool IsWorksheetEmpty(Worksheet sheet)
        {
            return sheet.Cells.MaxDataRow == -1 && sheet.Cells.MaxDataColumn == -1;
        }

        public void Save(string filePath)
        {
            _workbook.Save(filePath);
        }

        public void Dispose()
        {
            _workbook?.Dispose();
        }

        // 使用示例
        public static void RunExample()
        {
            using (var manager = new WorkbookManager())
            {
                // 创建标准报告
                string[] sheets = { "销售数据", "分析结果", "图表展示" };
                manager.CreateStandardReport(sheets, "月度销售报告", "数据分析师");

                // 添加示例数据
                manager.ProcessWorksheets((sheet, index) =>
                {
                    if (sheet.Name != "汇总")
                    {
                        sheet.Cells["A2"].Value = "示例数据";
                        sheet.Cells["B2"].Value = 100 * (index + 1);
                    }
                });

                // 应用安全策略
                manager.ApplySecurityPolicy("secure123", true, true);

                // 优化工作簿
                manager.OptimizeWorkbook();

                // 导出多种格式
                manager.ExportToMultipleFormats(@"C:\Reports", "MonthlyReport");

                Console.WriteLine("工作簿处理完成！");
            }
        }
    }
}

这个综合示例展示了工作簿和工作表操作的完整流程，包括创建、管理、保护和导出等关键功能。通过这些技术，您可以构建强大的Excel文件处理应用程序。

Aspose.Cells for .NET下载地址 https://soft51.cc/software/175799451582733643