To set Different Colors For each column in Column Chart Using NPOI excel nuget package in .Net6 Core

Question

I have code to Generate Excel File using Npoi package where my data on excel sheet creates Column Chart. In that I have 4 columns in my column chart where all columns color is blue by default. I want to set custom colors for each Column in Column chart that is I want 4 different colors for 4 columns. How to achieve this using NPOI Package?

using NPOI.SS.UserModel;
using NPOI.SS.UserModel.Charts;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.IO;

namespace ColumnChart
{
class Program
{
    const int NUM_OF_ROWS = 4;
    const int NUM_OF_COLUMNS = 2;
    private static void CreateChart(ISheet sheet, IDrawing drawing, IClientAnchor anchor, string serieTitle, int startDataRow, int endDataRow, int columnIndex)
    {
        XSSFChart chart = (XSSFChart)drawing.CreateChart(anchor);

        IColumnChartData<string, double> columnChartData = chart.ChartDataFactory.CreateColumnChartData<string, double>();
        IChartLegend legend = chart.GetOrCreateLegend();
        legend.Position = LegendPosition.Bottom;

        IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
        bottomAxis.MajorTickMark = AxisTickMark.None;
        IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
        leftAxis.Crosses = AxisCrosses.AutoZero;
        leftAxis.SetCrossBetween(AxisCrossBetween.Between);


        IChartDataSource<string> categoryAxis = DataSources.FromStringCellRange(sheet, new CellRangeAddress(startDataRow, endDataRow, 0, 0));
        IChartDataSource<double> valueAxis = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(startDataRow, endDataRow, columnIndex, columnIndex));
        var serie = columnChartData.AddSeries(categoryAxis, valueAxis);
        serie.SetTitle("Categories of Risk Associated");

        chart.Plot(columnChartData, bottomAxis, leftAxis);
    }
    static void Main(string[] args)
    {
        using (IWorkbook wb = new XSSFWorkbook())
        {
            ISheet sheet = wb.CreateSheet();

            IRow row;
            ICell cell;
            for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++)
            {
                row = sheet.CreateRow((short)rowIndex);
                for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++)
                {
                    cell = row.CreateCell((short)colIndex);
                    if (colIndex == 0)
                        cell.SetCellValue("X" + rowIndex);
                    else
                    {
                        var x = colIndex * (rowIndex + 1);
                        cell.SetCellValue(x * x + 2 * x + 1);
                    }
                }
            }
            XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
            XSSFClientAnchor anchor = (XSSFClientAnchor)drawing.CreateAnchor(0, 0, 0, 0, 3, 3, 10, 12);

            CreateChart(sheet, drawing, anchor, "s1", 0, 3, 1);
            using (FileStream fs = File.Create("C:\\Users\\lenovo\\Downloads\\columnChart.xlsx"))
            {
                wb.Write(fs, false);
            }
            Console.WriteLine("Done!");
            Console.ReadLine();
        }
    }
  }
}

Sample of ColumnChart Generated

Sample of ColumnChart Wanted

Answer 1

Try to add the following code after chart.Plot(columnChartData, bottomAxis, leftAxis);

chart.Plot(columnChartData, bottomAxis, leftAxis);

var plotaArea = chart.GetCTChart().plotArea;
plotaArea.barChart[0].ser[0].dPt = new List<NPOI.OpenXmlFormats.Dml.Chart.CT_DPt>();

for(int index = 0; index < endDataRow - startDataRow; index++)
{
    var dpt = new NPOI.OpenXmlFormats.Dml.Chart.CT_DPt();
    dpt.idx = new NPOI.OpenXmlFormats.Dml.Chart.CT_UnsignedInt();
    dpt.idx.val = (uint)index;
    dpt.spPr = new NPOI.OpenXmlFormats.Dml.Chart.CT_ShapeProperties();
    var solidFill = dpt.spPr.AddNewSolidFill();
    var color = solidFill.AddNewSrgbClr();
    // random color
    color.val = new byte[] { (byte)(125 + index*5), (byte)(50 + index * 8), (byte)(100 + index * 11) };

    plotaArea.barChart[0].ser[0].dPt.Add(dpt);
}

With random color the result is

You will need to add your logic to for loop and set the color.val based on the current dataRow.

System.Drawing.Color can help you. You can set the value like this:

color.val = new byte[] { Color.Red.R, Color.Red.G, Color.Red.B }

Answer 2

Try to add the following code after chart.Plot(columnChartData, bottomAxis, leftAxis);

 chart.Plot(columnChartData, bottomAxis, leftAxis);

 var plotaArea = chart.GetCTChart().plotArea;
 plotaArea.barChart[0].ser[0].dPt = new 
 List<NPOI.OpenXmlFormats.Dml.Chart.CT_DPt>();

 for (int index = 0; index < endDataRow - startDataRow; index++)
 {
  var dpt = new NPOI.OpenXmlFormats.Dml.Chart.CT_DPt();
  dpt.idx = new NPOI.OpenXmlFormats.Dml.Chart.CT_UnsignedInt();
  dpt.idx.val = (uint)index;
  dpt.spPr = new NPOI.OpenXmlFormats.Dml.Chart.CT_ShapeProperties();
  var solidFill = dpt.spPr.AddNewSolidFill();
  var color = solidFill.AddNewSrgbClr();

  // Define colors based on index
  switch (index % 4)
  {
    case 0: // Orange
        color.val = new byte[] { Color.Orange.R, Color.Orange.G, Color.Orange.B };
        break;
    case 1: // Green
        color.val = new byte[] { Color.Green.R, Color.Green.G, Color.Green.B };
        break;
    case 2: // Red
        color.val = new byte[] { Color.Red.R, Color.Red.G, Color.Red.B };
        break;
    case 3: // Black
        color.val = new byte[] { Color.Black.R, Color.Black.G, Color.Black.B };
        break;
  }

   plotaArea.barChart[0].ser[0].dPt.Add(dpt);
 }