.NET 通过Open XML 在创建的Excel文件中给表格数据添加数据条

通过NuGet包添加引用

DocumentFormat.OpenXml

ClosedXML

 

创建文件类

public class DataBarSimulation
{
    public void CreateExcelWithDataBars()
    {
        using (var workbook = new XLWorkbook())
        {
            var worksheet = workbook.Worksheets.Add("销售数据可视化");

            // 添加数据
            AddSampleData(worksheet);

            // 添加数据条（模拟图表）
            AddDataBars(worksheet);

            // 添加迷你图效果
            AddSparklines(worksheet);

            workbook.SaveAs("SalesReport_DataBars.xlsx");
            Console.WriteLine("Excel文件已创建: SalesReport_DataBars.xlsx");
            Console.WriteLine("使用数据条和条件格式模拟图表效果");
        }
    }

    private void AddSampleData(IXLWorksheet worksheet)
    {
        // 标题行
        worksheet.Cell("A1").Value = "月份";
        worksheet.Cell("B1").Value = "销售额";
        worksheet.Cell("C1").Value = "利润";
        worksheet.Cell("D1").Value = "销售趋势";
        worksheet.Cell("E1").Value = "利润趋势";

        var data = new[]
        {
            new { Month = "一月", Sales = 120000, Profit = 30000 },
            new { Month = "二月", Sales = 150000, Profit = 40000 },
            new { Month = "三月", Sales = 180000, Profit = 50000 },
            new { Month = "四月", Sales = 160000, Profit = 45000 },
            new { Month = "五月", Sales = 200000, Profit = 55000 },
            new { Month = "六月", Sales = 220000, Profit = 60000 }
        };

        for (int i = 0; i < data.Length; i++)
        {
            worksheet.Cell(i + 2, 1).Value = data[i].Month;
            worksheet.Cell(i + 2, 2).Value = data[i].Sales;
            worksheet.Cell(i + 2, 3).Value = data[i].Profit;
        }

        // 设置样式
        var headerRange = worksheet.Range("A1:E1");
        headerRange.Style.Font.Bold = true;
        headerRange.Style.Fill.BackgroundColor = XLColor.LightGray;

        worksheet.Columns().AdjustToContents();
    }

    private void AddDataBars(IXLWorksheet worksheet)
    {
        // 为销售额添加数据条
        var salesRange = worksheet.Range("B2:B7");
        salesRange.AddConditionalFormat().DataBar(XLColor.Blue, XLColor.LightBlue);

        // 为利润添加数据条
        var profitRange = worksheet.Range("C2:C7");
        profitRange.AddConditionalFormat().DataBar(XLColor.Green, XLColor.LightGreen);

        // 添加百分比显示
        for (int i = 2; i <= 7; i++)
        {
            worksheet.Cell(i, 4).Value = GetTrendSymbol(worksheet.Cell(i, 2).GetValue<int>(), 150000);
            worksheet.Cell(i, 5).Value = GetTrendSymbol(worksheet.Cell(i, 3).GetValue<int>(), 45000);
        }
    }

    private string GetTrendSymbol(int value, int average)
    {
        if (value > average * 1.2) return "↑↑"; // 显著高于平均
        if (value > average) return "↑";        // 高于平均
        if (value < average * 0.8) return "↓↓"; // 显著低于平均
        if (value < average) return "↓";        // 低于平均
        return "→";                             // 持平
    }

    private void AddSparklines(IXLWorksheet worksheet)
    {
        // 在备注列添加文本趋势
        worksheet.Cell("F1").Value = "业绩评价";

        var evaluations = new[] { "良好", "优秀", "杰出", "良好", "优秀", "杰出" };
        for (int i = 0; i < evaluations.Length; i++)
        {
            worksheet.Cell(i + 2, 6).Value = evaluations[i];
        }
    }
}

　　方法调用

DataBarSimulation excelChart = new DataBarSimulation();
excelChart.CreateExcelWithDataBars();

　　创建结果

 

该方法只用于个人使用, 或可使用

EPPlus 创建,但是后续版本需要许可证