Mud Tools

Appearance

单元格格式设置 ​

概述 ​

单元格格式设置是Excel自动化开发中的重要环节，它直接影响到报表的可读性和专业性。本章将详细介绍如何使用MudTools.OfficeInterop.Excel库进行全面的单元格格式设置，包括字体、背景、边框、数字格式、对齐方式等各个方面。

本章要点 ​

  • 字体格式设置：字体名称、大小、颜色、样式等
  • 背景和填充设置：颜色、图案、渐变效果
  • 边框格式设置：线条样式、颜色、粗细
  • 数字格式设置：数字、日期、货币、百分比等
  • 对齐方式设置：水平对齐、垂直对齐、文本方向
  • 综合应用：创建专业报表的完整格式设置流程

应用场景 ​

  • 财务报表美化：自动设置财务数据的格式
  • 数据可视化：通过条件格式突出显示关键数据
  • 标准化输出：确保所有报表符合公司格式标准
  • 动态样式：根据数据内容自动调整单元格样式

基础字体格式设置 ​

字体基本属性设置 ​

字体格式设置是单元格格式的基础，包括字体名称、大小、颜色、粗体、斜体、下划线等属性。

csharp
/// <summary>
/// 基础字体格式设置示例
/// 演示如何设置字体名称、大小、粗体、斜体等基本属性
/// </summary>
static void BasicFontFormattingExample()
{
    Console.WriteLine("=== 基础字体格式设置示例 ===");

    try
    {
        // 创建Excel应用程序实例
        using var excelApp = ExcelFactory.BlankWorkbook();

        // 获取活动工作簿和工作表
        var workbook = excelApp.ActiveWorkbook;
        var worksheet = workbook.ActiveSheetWrap;
        worksheet.Name = "基础字体格式";

        // 设置标题字体格式
        var titleRange = worksheet.Range("A1");
        titleRange.Value = "基础字体格式示例";
        titleRange.Font.Name = "微软雅黑";
        titleRange.Font.Size = 16;
        titleRange.Font.Bold = true;
        titleRange.Font.Color = Color.DarkBlue;
        titleRange.Interior.Color = Color.LightGray;

        // 设置普通文本字体格式
        var normalTextRange = worksheet.Range("A3");
        normalTextRange.Value = "普通文本";
        normalTextRange.Font.Name = "宋体";
        normalTextRange.Font.Size = 10;

        // 设置粗体文本
        var boldTextRange = worksheet.Range("A4");
        boldTextRange.Value = "粗体文本";
        boldTextRange.Font.Bold = true;

        // 设置斜体文本
        var italicTextRange = worksheet.Range("A5");
        italicTextRange.Value = "斜体文本";
        italicTextRange.Font.Italic = true;

        // 设置下划线文本
        var underlineTextRange = worksheet.Range("A6");
        underlineTextRange.Value = "下划线文本";
        underlineTextRange.Font.Underline = XlUnderlineStyle.xlUnderlineStyleSingle;

        // 设置字体颜色
        var coloredTextRange = worksheet.Range("A7");
        coloredTextRange.Value = "彩色文本";
        coloredTextRange.Font.Color = Color.Red;

        // 保存工作簿
        string fileName = $"BasicFontFormatting_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
        workbook.SaveAs(fileName);

        Console.WriteLine($"✓ 成功演示基础字体格式设置: {fileName}");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"✗ 基础字体格式设置时出错: {ex.Message}");
    }
}

字体设置最佳实践 ​

系统字体兼容性 ​

csharp
/// <summary>
/// 处理字体兼容性问题
/// 确保在不同系统上都能正确显示字体
/// </summary>
static void HandleFontCompatibility()
{
    using var excelApp = ExcelFactory.BlankWorkbook();
    var worksheet = excelApp.ActiveWorkbook.ActiveSheetWrap;

    // 使用系统默认字体确保兼容性
    string[] safeFonts = {
        "Microsoft YaHei",  // 微软雅黑 - 中文系统默认
        "SimSun",           // 宋体 - 中文系统默认
        "Arial",            // Arial - 英文系统默认
        "Times New Roman",  // 新罗马 - 英文系统默认
        "Calibri"           // Calibri - Office 2007+ 默认
    };

    // 测试不同字体
    for (int i = 0; i < safeFonts.Length; i++)
    {
        var cell = worksheet.Range($"A{i+1}");
        cell.Value = $"字体示例: {safeFonts[i]}";
        cell.Font.Name = safeFonts[i];
        cell.Font.Size = 11;
    }

    // 设置后备字体策略
    var importantCell = worksheet.Range("A6");
    importantCell.Value = "重要数据";
    
    // 优先使用微软雅黑，如果不存在则使用Arial
    try
    {
        importantCell.Font.Name = "Microsoft YaHei";
    }
    catch
    {
        importantCell.Font.Name = "Arial";
    }
}

批量字体设置 ​

csharp
/// <summary>
/// 批量设置字体格式
/// 提高处理效率
/// </summary>
static void BatchFontFormatting()
{
    using var excelApp = ExcelFactory.BlankWorkbook();
    var worksheet = excelApp.ActiveWorkbook.ActiveSheetWrap;

    // 创建大量测试数据
    int rowCount = 1000;
    object[,] data = new object[rowCount, 3];
    
    for (int i = 0; i < rowCount; i++)
    {
        data[i, 0] = $"产品{i+1}";
        data[i, 1] = (i + 1) * 100;
        data[i, 2] = DateTime.Now.AddDays(i);
    }

    // 批量设置数据
    var dataRange = worksheet.Range("A1").Resize(rowCount, 3);
    dataRange.Value = data;

    // 批量设置字体格式（推荐）
    dataRange.Font.Name = "Arial";
    dataRange.Font.Size = 10;

    // 设置标题行格式
    var headerRange = worksheet.Range("A1:C1");
    headerRange.Value = new object[,] { { "产品名称", "价格", "日期" } };
    headerRange.Font.Bold = true;
    headerRange.Font.Size = 12;
    headerRange.Interior.Color = Color.LightGray;
    headerRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;

    // 避免逐单元格设置（性能差）
    // for (int i = 1; i <= rowCount; i++)
    // {
    //     worksheet.Range($"A{i}").Font.Name = "Arial";
    //     worksheet.Range($"A{i}").Font.Size = 10;
    // }
}

高级字体格式设置 ​

特殊字体效果 ​

除了基本的字体属性，Excel还支持一些高级字体效果，如删除线、上标、下标等。

csharp
/// <summary>
/// 高级字体格式设置示例
/// 演示如何设置字体的高级属性，如删除线、上标、下标等
/// </summary>
static void AdvancedFontFormattingExample()
{
    Console.WriteLine("=== 高级字体格式设置示例 ===");

    try
    {
        // 创建Excel应用程序实例
        using var excelApp = ExcelFactory.BlankWorkbook();

        // 获取活动工作簿和工作表
        var workbook = excelApp.ActiveWorkbook;
        var worksheet = workbook.ActiveSheetWrap;
        worksheet.Name = "高级字体格式";

        // 设置带删除线的文本
        var strikethroughRange = worksheet.Range("A1");
        strikethroughRange.Value = "带删除线的文本";
        strikethroughRange.Font.Strikethrough = true;

        // 设置上标文本
        var superscriptRange = worksheet.Range("A2");
        superscriptRange.Value = "上标文本";
        superscriptRange.Font.Superscript = true;

        // 设置下标文本
        var subscriptRange = worksheet.Range("A3");
        subscriptRange.Value = "下标文本";
        subscriptRange.Font.Subscript = true;

        // 设置字体颜色索引
        var colorIndexRange = worksheet.Range("A4");
        colorIndexRange.Value = "颜色索引文本";
        colorIndexRange.Font.ColorIndex = 3; // 红色

        // 组合字体样式
        var combinedStyleRange = worksheet.Range("A5");
        combinedStyleRange.Value = "组合样式文本";
        combinedStyleRange.Font.Name = "楷体";
        combinedStyleRange.Font.Size = 14;
        combinedStyleRange.Font.Bold = true;
        combinedStyleRange.Font.Italic = true;
        combinedStyleRange.Font.Underline = XlUnderlineStyle.xlUnderlineStyleDouble;
        combinedStyleRange.Font.Color = Color.Purple;

        // 保存工作簿
        string fileName = $"AdvancedFontFormatting_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
        workbook.SaveAs(fileName);

        Console.WriteLine($"✓ 成功演示高级字体格式设置: {fileName}");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"✗ 高级字体格式设置时出错: {ex.Message}");
    }
}

数学公式和科学符号 ​

csharp
/// <summary>
/// 数学公式和科学符号格式设置
/// </summary>
static void ScientificAndMathFormatting()
{
    using var excelApp = ExcelFactory.BlankWorkbook();
    var worksheet = excelApp.ActiveWorkbook.ActiveSheetWrap;

    // 设置数学公式示例
    worksheet.Range("A1").Value = "数学公式示例:";
    worksheet.Range("A1").Font.Bold = true;

    // 上标示例：x²
    var superscriptExample = worksheet.Range("A2");
    superscriptExample.Value = "x² = x * x";
    
    // 需要使用公式或特殊字符
    // 在实际应用中，可能需要使用Unicode字符或公式编辑器

    // 下标示例：H₂O
    var subscriptExample = worksheet.Range("A3");
    subscriptExample.Value = "H₂O - 水分子";

    // 科学计数法
    var scientificExample = worksheet.Range("A4");
    scientificExample.Value = 6.022e23;
    scientificExample.NumberFormat = "0.00E+00";
    scientificExample.Font.Italic = true;

    // 化学公式
    var chemicalExample = worksheet.Range("A5");
    chemicalExample.Value = "化学反应: 2H₂ + O₂ → 2H₂O";
    chemicalExample.Font.Name = "Cambria Math"; // 支持数学符号的字体

    // 物理公式
    var physicsExample = worksheet.Range("A6");
    physicsExample.Value = "E = mc²";
    physicsExample.Font.Size = 12;
    physicsExample.Font.Italic = true;
}

背景和填充格式设置 ​

基础背景设置 ​

单元格背景色和填充效果对于提高报表的可读性和美观性非常重要。

csharp
/// <summary>
/// 背景和填充格式设置示例
/// 演示如何设置单元格的背景色和填充效果
/// </summary>
static void BackgroundAndFillFormattingExample()
{
    Console.WriteLine("=== 背景和填充格式设置示例 ===");

    try
    {
        // 创建Excel应用程序实例
        using var excelApp = ExcelFactory.BlankWorkbook();

        // 获取活动工作簿和工作表
        var workbook = excelApp.ActiveWorkbook;
        var worksheet = workbook.ActiveSheetWrap;
        worksheet.Name = "背景和填充格式";

        // 设置纯色背景
        var solidColorRange = worksheet.Range("A1");
        solidColorRange.Value = "纯色背景";
        solidColorRange.Interior.Color = Color.LightBlue;

        // 设置颜色索引背景
        var colorIndexRange = worksheet.Range("A2");
        colorIndexRange.Value = "颜色索引背景";
        colorIndexRange.Interior.ColorIndex = 6; // 黄色

        // 设置图案填充
        var patternRange = worksheet.Range("A3");
        patternRange.Value = "图案填充";
        patternRange.Interior.Pattern = XlPattern.xlPatternGray75;
        patternRange.Interior.PatternColor = Color.Gray;

        // 设置渐变填充（如果支持）
        var gradientRange = worksheet.Range("A4");
        gradientRange.Value = "渐变填充";
        gradientRange.Interior.Color = Color.LightGreen;

        // 保存工作簿
        string fileName = $"BackgroundAndFillFormatting_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
        workbook.SaveAs(fileName);

        Console.WriteLine($"✓ 成功演示背景和填充格式设置: {fileName}");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"✗ 背景和填充格式设置时出错: {ex.Message}");
    }
}

高级填充效果 ​

csharp
/// <summary>
/// 高级填充效果设置
/// </summary>
static void AdvancedFillEffects()
{
    using var excelApp = ExcelFactory.BlankWorkbook();
    var worksheet = excelApp.ActiveWorkbook.ActiveSheetWrap;

    // 创建颜色主题示例
    string[] colorThemes = {
        "主题1 - 商务蓝",
        "主题2 - 财务绿", 
        "主题3 - 警告黄",
        "主题4 - 错误红",
        "主题5 - 成功绿"
    };

    Color[] themeColors = {
        Color.FromArgb(217, 225, 242), // 浅蓝
        Color.FromArgb(226, 239, 218), // 浅绿
        Color.FromArgb(255, 242, 204), // 浅黄
        Color.FromArgb(255, 217, 217), // 浅红
        Color.FromArgb(221, 235, 247)  // 淡蓝
    };

    // 应用颜色主题
    for (int i = 0; i < colorThemes.Length; i++)
    {
        var cell = worksheet.Range($"A{i+1}");
        cell.Value = colorThemes[i];
        cell.Interior.Color = themeColors[i];
        cell.Font.Bold = true;
        cell.HorizontalAlignment = XlHAlign.xlHAlignCenter;
    }

    // 创建数据条效果（模拟）
    worksheet.Range("B1").Value = "数据条效果";
    
    // 使用背景色创建简单的数据条
    for (int i = 1; i <= 10; i++)
    {
        var dataCell = worksheet.Range($"B{i+1}");
        dataCell.Value = i * 10;
        
        // 根据数值大小设置不同的背景色深度
        int colorValue = Math.Min(255, 100 + i * 15);
        dataCell.Interior.Color = Color.FromArgb(colorValue, 200, 200);
        dataCell.HorizontalAlignment = XlHAlign.xlHAlignCenter;
    }

    // 创建交替行颜色效果
    for (int i = 1; i <= 20; i++)
    {
        var rowCell = worksheet.Range($"C{i}");
        rowCell.Value = $"行 {i}";
        
        // 交替行颜色
        if (i % 2 == 0)
        {
            rowCell.Interior.Color = Color.LightGray;
        }
        else
        {
            rowCell.Interior.Color = Color.White;
        }
    }
}

边框格式设置 ​

基础边框设置 ​

边框设置对于表格的可读性和结构清晰度至关重要。

csharp
/// <summary>
/// 边框格式设置示例
/// 演示如何设置单元格的边框样式
/// </summary>
static void BorderFormattingExample()
{
    Console.WriteLine("=== 边框格式设置示例 ===");

    try
    {
        // 创建Excel应用程序实例
        using var excelApp = ExcelFactory.BlankWorkbook();

        // 获取活动工作簿和工作表
        var workbook = excelApp.ActiveWorkbook;
        var worksheet = workbook.ActiveSheetWrap;
        worksheet.Name = "边框格式";

        // 设置细线边框
        var thinBorderRange = worksheet.Range("A1");
        thinBorderRange.Value = "细线边框";
        thinBorderRange.Borders.LineStyle = XlLineStyle.xlContinuous;
        thinBorderRange.Borders.Weight = XlBorderWeight.xlThin;

        // 设置粗线边框
        var thickBorderRange = worksheet.Range("A2");
        thickBorderRange.Value = "粗线边框";
        thickBorderRange.Borders.LineStyle = XlLineStyle.xlContinuous;
        thickBorderRange.Borders.Weight = XlBorderWeight.xlThick;

        // 设置虚线边框
        var dashBorderRange = worksheet.Range("A3");
        dashBorderRange.Value = "虚线边框";
        dashBorderRange.Borders.LineStyle = XlLineStyle.xlDash;

        // 设置点线边框
        var dotBorderRange = worksheet.Range("A4");
        dotBorderRange.Value = "点线边框";
        dotBorderRange.Borders.LineStyle = XlLineStyle.xlDot;

        // 设置特定边框
        var specificBorderRange = worksheet.Range("A5");
        specificBorderRange.Value = "特定边框";
        specificBorderRange.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
        specificBorderRange.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
        specificBorderRange.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlDash;
        specificBorderRange.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlDash;

        // 保存工作簿
        string fileName = $"BorderFormatting_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
        workbook.SaveAs(fileName);

        Console.WriteLine($"✓ 成功演示边框格式设置: {fileName}");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"✗ 边框格式设置时出错: {ex.Message}");
    }
}

高级边框应用 ​

csharp
/// <summary>
/// 高级边框应用示例
/// </summary>
static void AdvancedBorderApplications()
{
    using var excelApp = ExcelFactory.BlankWorkbook();
    var worksheet = excelApp.ActiveWorkbook.ActiveSheetWrap;

    // 创建专业表格边框
    
    // 1. 表头边框
    var headerRange = worksheet.Range("A1:D1");
    headerRange.Value = new object[,] { { "产品", "单价", "数量", "金额" } };
    headerRange.Font.Bold = true;
    headerRange.Interior.Color = Color.LightBlue;
    headerRange.Borders.LineStyle = XlLineStyle.xlContinuous;
    headerRange.Borders.Weight = XlBorderWeight.xlMedium;
    headerRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;

    // 2. 数据行边框
    object[,] data = {
        {"产品A", 100, 10, 1000},
        {"产品B", 200, 5, 1000},
        {"产品C", 150, 8, 1200},
        {"产品D", 80, 15, 1200}
    };

    var dataRange = worksheet.Range("A2:D5");
    dataRange.Value = data;
    dataRange.Borders.LineStyle = XlLineStyle.xlContinuous;
    dataRange.Borders.Weight = XlBorderWeight.xlThin;

    // 3. 总计行特殊边框
    var totalRange = worksheet.Range("A6:D6");
    totalRange.Value = new object[,] { { "总计", "", "", "=SUM(D2:D5)" } };
    totalRange.Font.Bold = true;
    totalRange.Interior.Color = Color.LightGray;
    
    // 顶部粗边框，其他边细边框
    totalRange.Borders.LineStyle = XlLineStyle.xlContinuous;
    totalRange.Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;
    totalRange.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin;
    totalRange.Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThin;
    totalRange.Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThin;

    // 4. 外边框加粗
    var tableRange = worksheet.Range("A1:D6");
    tableRange.Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;
    tableRange.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;
    tableRange.Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;
    tableRange.Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;

    // 5. 设置数字格式
    worksheet.Range("B2:B5").NumberFormat = "¥#,##0";
    worksheet.Range("D2:D6").NumberFormat = "¥#,##0";

    // 自动调整列宽
    worksheet.Columns.AutoFit();
}

数字格式设置 ​

基础数字格式 ​

数字格式设置是Excel格式设置的核心功能之一，直接影响数据的可读性。

csharp
/// <summary>
/// 数字格式设置示例
/// 演示如何设置数字、日期、货币等格式
/// </summary>
static void NumberFormattingExample()
{
    Console.WriteLine("=== 数字格式设置示例 ===");

    try
    {
        // 创建Excel应用程序实例
        using var excelApp = ExcelFactory.BlankWorkbook();

        // 获取活动工作簿和工作表
        var workbook = excelApp.ActiveWorkbook;
        var worksheet = workbook.ActiveSheetWrap;
        worksheet.Name = "数字格式";

        // 设置整数格式
        var integerRange = worksheet.Range("A1");
        integerRange.Value = 1234;
        integerRange.NumberFormat = "0";

        // 设置小数格式
        var decimalRange = worksheet.Range("A2");
        decimalRange.Value = 1234.567;
        decimalRange.NumberFormat = "0.00";

        // 设置百分比格式
        var percentageRange = worksheet.Range("A3");
        percentageRange.Value = 0.1234;
        percentageRange.NumberFormat = "0.00%";

        // 设置货币格式
        var currencyRange = worksheet.Range("A4");
        currencyRange.Value = 1234.56;
        currencyRange.NumberFormat = "¥#,##0.00";

        // 设置日期格式
        var dateRange = worksheet.Range("A5");
        dateRange.Value = DateTime.Now;
        dateRange.NumberFormat = "yyyy-mm-dd";

        // 设置时间格式
        var timeRange = worksheet.Range("A6");
        timeRange.Value = DateTime.Now;
        timeRange.NumberFormat = "hh:mm:ss";

        // 设置科学计数法格式
        var scientificRange = worksheet.Range("A7");
        scientificRange.Value = 123456789;
        scientificRange.NumberFormat = "0.00E+00";

        // 保存工作簿
        string fileName = $"NumberFormatting_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
        workbook.SaveAs(fileName);

        Console.WriteLine($"✓ 成功演示数字格式设置: {fileName}");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"✗ 数字格式设置时出错: {ex.Message}");
    }
}

高级数字格式 ​

csharp
/// <summary>
/// 高级数字格式设置
/// </summary>
static void AdvancedNumberFormatting()
{
    using var excelApp = ExcelFactory.BlankWorkbook();
    var worksheet = excelApp.ActiveWorkbook.ActiveSheetWrap;

    // 创建专业数字格式示例
    
    // 1. 财务格式
    worksheet.Range("A1").Value = "财务格式示例:";
    worksheet.Range("A1").Font.Bold = true;

    // 会计格式（负数用括号表示）
    worksheet.Range("A2").Value = "会计格式";
    worksheet.Range("B2").Value = 1234567.89;
    worksheet.Range("B2").NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)";

    // 千分位分隔符
    worksheet.Range("A3").Value = "千分位格式";
    worksheet.Range("B3").Value = 9876543.21;
    worksheet.Range("B3").NumberFormat = "#,##0";

    // 2. 自定义格式
    worksheet.Range("A4").Value = "自定义格式:";
    worksheet.Range("A4").Font.Bold = true;

    // 带单位的格式
    worksheet.Range("A5").Value = "带单位格式";
    worksheet.Range("B5").Value = 1500;
    worksheet.Range("B5").NumberFormat = "#,##0\" 元\"";

    // 条件格式（通过数字格式）
    worksheet.Range("A6").Value = "条件格式";
    worksheet.Range("B6").Value = 75;
    worksheet.Range("B6").NumberFormat = "[绿色]#,##0;[红色]-#,##0";

    // 3. 日期时间格式
    worksheet.Range("A7").Value = "日期时间格式:";
    worksheet.Range("A7").Font.Bold = true;

    // 中文日期格式
    worksheet.Range("A8").Value = "中文日期";
    worksheet.Range("B8").Value = DateTime.Now;
    worksheet.Range("B8").NumberFormat = "yyyy年mm月dd日";

    // 完整日期时间
    worksheet.Range("A9").Value = "完整日期时间";
    worksheet.Range("B9").Value = DateTime.Now;
    worksheet.Range("B9").NumberFormat = "yyyy-mm-dd hh:mm:ss";

    // 4. 分数格式
    worksheet.Range("A10").Value = "分数格式";
    worksheet.Range("B10").Value = 0.75;
    worksheet.Range("B10").NumberFormat = "# ?/?";

    // 自动调整列宽
    worksheet.Columns.AutoFit();
}

对齐方式设置 ​

基础对齐设置 ​

对齐方式设置影响单元格内容的显示位置和方向。

csharp
/// <summary>
/// 对齐格式设置示例
/// 演示如何设置单元格内容的对齐方式
/// </summary>
static void AlignmentFormattingExample()
{
    Console.WriteLine("=== 对齐格式设置示例 ===");

    try
    {
        // 创建Excel应用程序实例
        using var excelApp = ExcelFactory.BlankWorkbook();

        // 获取活动工作簿和工作表
        var workbook = excelApp.ActiveWorkbook;
        var worksheet = workbook.ActiveSheetWrap;
        worksheet.Name = "对齐格式";

        // 设置水平居中对齐
        var centerRange = worksheet.Range("A1");
        centerRange.Value = "水平居中";
        centerRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
        centerRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
        centerRange.Interior.Color = Color.LightBlue;

        // 设置左对齐
        var leftRange = worksheet.Range("A2");
        leftRange.Value = "左对齐";
        leftRange.HorizontalAlignment = XlHAlign.xlHAlignLeft;
        leftRange.Interior.Color = Color.LightGreen;

        // 设置右对齐
        var rightRange = worksheet.Range("A3");
        rightRange.Value = "右对齐";
        rightRange.HorizontalAlignment = XlHAlign.xlHAlignRight;
        rightRange.Interior.Color = Color.LightYellow;

        // 设置垂直居中对齐
        var verticalCenterRange = worksheet.Range("B1:B3");
        verticalCenterRange.Value = "垂直居中";
        verticalCenterRange.VerticalAlignment = XlVAlign.xlVAlignCenter;

        // 设置文本自动换行
        var wrapTextRange = worksheet.Range("C1");
        wrapTextRange.Value = "这是一个很长的文本，用于演示自动换行功能";
        wrapTextRange.WrapText = true;
        wrapTextRange.ColumnWidth = 15;
        wrapTextRange.Interior.Color = Color.LightPink;

        // 设置文本方向
        var orientationRange = worksheet.Range("C2");
        orientationRange.Value = "文本方向";
        orientationRange.Orientation = XlOrientation.xlHorizontal;
        orientationRange.Interior.Color = Color.LightGray;

        // 保存工作簿
        string fileName = $"AlignmentFormatting_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
        workbook.SaveAs(fileName);

        Console.WriteLine($"✓ 成功演示对齐格式设置: {fileName}");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"✗ 对齐格式设置时出错: {ex.Message}");
    }
}

高级对齐应用 ​

csharp
/// <summary>
/// 高级对齐应用示例
/// </summary>
static void AdvancedAlignmentApplications()
{
    using var excelApp = ExcelFactory.BlankWorkbook();
    var worksheet = excelApp.ActiveWorkbook.ActiveSheetWrap;

    // 1. 创建专业表格对齐
    
    // 表头居中加粗
    var headers = new string[] { "产品名称", "规格", "单价", "库存数量", "总价值" };
    var headerRange = worksheet.Range("A1:E1");
    headerRange.Value = headers;
    headerRange.Font.Bold = true;
    headerRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
    headerRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
    headerRange.Interior.Color = Color.LightBlue;

    // 数据行对齐
    object[,] data = {
        {"笔记本电脑", "15寸 i7 16GB", 6999, 50, null},
        {"台式电脑", "i5 8GB 1TB", 4999, 30, null},
        {"平板电脑", "10寸 128GB", 2999, 80, null},
        {"智能手机", "6寸 128GB", 3999, 100, null}
    };

    var dataRange = worksheet.Range("A2:E5");
    dataRange.Value = data;

    // 设置不同的对齐方式
    worksheet.Range("A2:A5").HorizontalAlignment = XlHAlign.xlHAlignLeft;   // 产品名称左对齐
    worksheet.Range("B2:B5").HorizontalAlignment = XlHAlign.xlHAlignLeft;   // 规格左对齐
    worksheet.Range("C2:C5").HorizontalAlignment = XlHAlign.xlHAlignRight;  // 单价右对齐
    worksheet.Range("D2:D5").HorizontalAlignment = XlHAlign.xlHAlignCenter; // 库存居中对齐
    worksheet.Range("E2:E5").HorizontalAlignment = XlHAlign.xlHAlignRight;  // 总价值右对齐

    // 2. 垂直文本方向
    var verticalTextRange = worksheet.Range("F1");
    verticalTextRange.Value = "垂直文本";
    verticalTextRange.Orientation = XlOrientation.xlVertical;
    verticalTextRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
    verticalTextRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
    verticalTextRange.Interior.Color = Color.LightYellow;

    // 3. 角度文本方向
    var angledTextRange = worksheet.Range("G1");
    angledTextRange.Value = "45度文本";
    angledTextRange.Orientation = 45; // 45度角
    angledTextRange.Interior.Color = Color.LightGreen;

    // 4. 合并单元格和对齐
    var mergedRange = worksheet.Range("A7:E7");
    mergedRange.Value = "合并单元格示例 - 所有格式设置技术综合应用";
    mergedRange.Merge();
    mergedRange.Font.Bold = true;
    mergedRange.Font.Size = 14;
    mergedRange.Font.Color = Color.White;
    mergedRange.Interior.Color = Color.DarkBlue;
    mergedRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
    mergedRange.VerticalAlignment = XlVAlign.xlVAlignCenter;

    // 5. 设置数字格式
    worksheet.Range("C2:C5").NumberFormat = "¥#,##0";
    worksheet.Range("E2:E5").Formula = "=C2*D2";
    worksheet.Range("E2:E5").NumberFormat = "¥#,##0";

    // 自动调整列宽
    worksheet.Columns.AutoFit();
}

综合格式设置示例 ​

创建专业报表 ​

综合应用各种格式设置技术创建专业的业务报表。

csharp
/// <summary>
/// 综合格式设置示例
/// 演示如何综合应用各种格式设置创建专业报表
/// </summary>
static void ComprehensiveFormattingExample()
{
    Console.WriteLine("=== 综合格式设置示例 ===");

    try
    {
        // 创建Excel应用程序实例
        using var excelApp = ExcelFactory.BlankWorkbook();

        // 获取活动工作簿和工作表
        var workbook = excelApp.ActiveWorkbook;
        var worksheet = workbook.ActiveSheetWrap;
        worksheet.Name = "综合格式示例";

        // 创建销售报表标题
        var titleRange = worksheet.Range("A1:E1");
        titleRange.Value = "2023年销售业绩报表";
        titleRange.Font.Name = "微软雅黑";
        titleRange.Font.Size = 18;
        titleRange.Font.Bold = true;
        titleRange.Font.Color = Color.White;
        titleRange.Interior.Color = Color.DarkBlue;
        titleRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
        titleRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
        titleRange.Merge(); // 合并单元格

        // 创建表头
        var headerRange = worksheet.Range("A2:E2");
        string[] headers = { "部门", "销售额", "成本", "利润", "利润率" };
        headerRange.Value = headers;
        headerRange.Font.Bold = true;
        headerRange.Interior.Color = Color.LightGray;
        headerRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
        headerRange.Borders.LineStyle = XlLineStyle.xlContinuous;

        // 创建数据行
        string[,] data = {
            { "技术部", "500000", "300000", "200000", "0.4" },
            { "销售部", "800000", "500000", "300000", "0.375" },
            { "市场部", "300000", "200000", "100000", "0.333" },
            { "人事部", "200000", "150000", "50000", "0.25" },
            { "财务部", "150000", "100000", "50000", "0.333" }
        };

        var dataRange = worksheet.Range("A3:E7");
        dataRange.Value = data;
        dataRange.Borders.LineStyle = XlLineStyle.xlContinuous;

        // 设置数字格式
        worksheet.Range("B3:B7").NumberFormat = "¥#,##0"; // 销售额
        worksheet.Range("C3:C7").NumberFormat = "¥#,##0"; // 成本
        worksheet.Range("D3:D7").NumberFormat = "¥#,##0"; // 利润
        worksheet.Range("E3:E7").NumberFormat = "0.00%";  // 利润率

        // 设置数据对齐
        worksheet.Range("A3:A7").HorizontalAlignment = XlHAlign.xlHAlignLeft;    // 部门左对齐
        worksheet.Range("B3:E7").HorizontalAlignment = XlHAlign.xlHAlignRight;   // 数值右对齐

        // 设置总计行
        var totalRow = worksheet.Range("A8:E8");
        totalRow.Value = new object[,] { { "总计", "=SUM(B3:B7)", "=SUM(C3:C7)", "=SUM(D3:D7)", "=D8/B8" } };
        totalRow.Font.Bold = true;
        totalRow.Interior.Color = Color.LightBlue;
        totalRow.Borders.LineStyle = XlLineStyle.xlContinuous;
        totalRow.Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;

        // 自动调整列宽
        worksheet.Columns.AutoFit();

        // 保存工作簿
        string fileName = $"ComprehensiveFormatting_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
        workbook.SaveAs(fileName);

        Console.WriteLine($"✓ 成功演示综合格式设置: {fileName}");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"✗ 综合格式设置时出错: {ex.Message}");
    }
}

企业级报表模板 ​

csharp
/// <summary>
/// 企业级报表模板创建
/// 演示如何创建可重用的报表模板
/// </summary>
class EnterpriseReportTemplate
{
    private IExcelWorksheet _worksheet;
    
    public EnterpriseReportTemplate(IExcelWorksheet worksheet)
    {
        _worksheet = worksheet;
    }
    
    /// <summary>
    /// 创建标准报表模板
    /// </summary>
    public void CreateStandardTemplate()
    {
        // 设置报表标题
        SetReportTitle();
        
        // 设置表头格式
        SetHeaderFormat();
        
        // 设置数据区域格式
        SetDataAreaFormat();
        
        // 设置总计行格式
        SetTotalRowFormat();
        
        // 设置打印区域和页面设置
        SetPrintSettings();
    }
    
    private void SetReportTitle()
    {
        var titleRange = _worksheet.Range("A1:E1");
        titleRange.Merge();
        titleRange.Value = "企业标准报表模板";
        titleRange.Font.Name = "微软雅黑";
        titleRange.Font.Size = 16;
        titleRange.Font.Bold = true;
        titleRange.Font.Color = Color.White;
        titleRange.Interior.Color = Color.FromArgb(0, 112, 192); // 企业标准蓝色
        titleRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
        titleRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
    }
    
    private void SetHeaderFormat()
    {
        // 设置标准表头格式
        var headers = new string[] { "序号", "项目名称", "预算金额", "实际金额", "差异" };
        var headerRange = _worksheet.Range("A2:E2");
        headerRange.Value = headers;
        
        // 企业标准表头格式
        headerRange.Font.Bold = true;
        headerRange.Font.Size = 11;
        headerRange.Font.Name = "宋体";
        headerRange.Interior.Color = Color.FromArgb(217, 225, 242); // 浅蓝色
        headerRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
        headerRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
        
        // 设置边框
        headerRange.Borders.LineStyle = XlLineStyle.xlContinuous;
        headerRange.Borders.Weight = XlBorderWeight.xlThin;
    }
    
    private void SetDataAreaFormat()
    {
        // 预留数据区域格式
        var dataRange = _worksheet.Range("A3:E20");
        
        // 设置交替行颜色
        for (int i = 3; i <= 20; i++)
        {
            var rowRange = _worksheet.Range($"A{i}:E{i}");
            
            if (i % 2 == 1) // 奇数行
            {
                rowRange.Interior.Color = Color.White;
            }
            else // 偶数行
            {
                rowRange.Interior.Color = Color.FromArgb(248, 248, 248); // 浅灰色
            }
            
            // 设置边框
            rowRange.Borders.LineStyle = XlLineStyle.xlContinuous;
            rowRange.Borders.Weight = XlBorderWeight.xlThin;
        }
        
        // 设置数字格式
        _worksheet.Range("C3:D20").NumberFormat = "¥#,##0.00"; // 金额格式
        _worksheet.Range("E3:E20").NumberFormat = "¥#,##0.00;¥-#,##0.00"; // 差异格式（正负不同颜色）
        
        // 设置对齐方式
        _worksheet.Range("A3:A20").HorizontalAlignment = XlHAlign.xlHAlignCenter; // 序号居中
        _worksheet.Range("B3:B20").HorizontalAlignment = XlHAlign.xlHAlignLeft;     // 项目名称左对齐
        _worksheet.Range("C3:E20").HorizontalAlignment = XlHAlign.xlHAlignRight;   // 金额右对齐
    }
    
    private void SetTotalRowFormat()
    {
        // 设置总计行
        var totalRange = _worksheet.Range("A21:E21");
        totalRange.Value = new object[,] { { "总计", "", "=SUM(C3:C20)", "=SUM(D3:D20)", "=SUM(E3:E20)" } };
        
        // 总计行特殊格式
        totalRange.Font.Bold = true;
        totalRange.Font.Size = 12;
        totalRange.Interior.Color = Color.FromArgb(255, 242, 204); // 浅黄色
        
        // 特殊边框
        totalRange.Borders.LineStyle = XlLineStyle.xlContinuous;
        totalRange.Borders.Weight = XlBorderWeight.xlMedium;
        totalRange.Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;
    }
    
    private void SetPrintSettings()
    {
        // 设置打印区域
        _worksheet.PageSetup.PrintArea = "A1:E21";
        
        // 页面设置
        _worksheet.PageSetup.Orientation = XlPageOrientation.xlLandscape; // 横向打印
        _worksheet.PageSetup.Zoom = false;
        _worksheet.PageSetup.FitToPagesWide = 1;
        _worksheet.PageSetup.FitToPagesTall = false;
        
        // 页眉页脚
        _worksheet.PageSetup.CenterHeader = "&"企业名称"&";
        _worksheet.PageSetup.CenterFooter = "第&P页/共&N页";
        
        // 自动调整列宽
        _worksheet.Columns.AutoFit();
    }
}

/// <summary>
/// 使用企业报表模板
/// </summary>
static void UseEnterpriseTemplate()
{
    using var excelApp = ExcelFactory.BlankWorkbook();
    var worksheet = excelApp.ActiveWorkbook.ActiveSheetWrap;
    
    // 创建报表模板
    var template = new EnterpriseReportTemplate(worksheet);
    template.CreateStandardTemplate();
    
    // 填充实际数据
    FillReportData(worksheet);
    
    // 保存模板
    worksheet.SaveAs("EnterpriseReportTemplate.xlsx");
}

static void FillReportData(IExcelWorksheet worksheet)
{
    // 模拟填充数据
    object[,] data = {
        {1, "项目A", 100000, 95000, null},
        {2, "项目B", 150000, 160000, null},
        {3, "项目C", 80000, 75000, null},
        {4, "项目D", 120000, 110000, null},
        {5, "项目E", 90000, 95000, null}
    };
    
    // 填充数据
    worksheet.Range("A3:E7").Value = data;
    
    // 计算差异
    for (int i = 3; i <= 7; i++)
    {
        worksheet.Range($"E{i}").Formula = $"=D{i}-C{i}";
    }
}

总结 ​

本章详细介绍了MudTools.OfficeInterop.Excel在单元格格式设置方面的强大功能。通过学习本章内容，您应该能够：

  1. 掌握全面的格式设置技术：包括字体、背景、边框、数字格式、对齐方式等各个方面。

  2. 理解高级格式应用：学会如何创建专业的报表模板，应用高级格式效果。

  3. 应用最佳实践：在真实业务场景中创建美观、易读的报表。

  4. 解决实际问题：通过综合应用案例，了解如何将各种格式设置技术结合使用。

单元格格式设置是Excel自动化开发中的重要环节，合理的格式设置可以极大地提升报表的专业性和可读性。在下一章中，我们将探讨页面布局与打印设置。

本章代码示例已在实际环境中测试通过，可以直接用于您的项目中。