How to add Excel cell background color file by X++ in Dynamics 365?

Question

I'm creating an Excel file by x++, in Dynamics 365 - D365, I would like to add the background color by X++.

I'm using this code

System.IO.Stream workbookStream     = new System.IO.MemoryStream();
System.IO.MemoryStream memoryStream = new System.IO.MemoryStream();

var package = new OfficeOpenXml.ExcelPackage(memoryStream)
var package = new OfficeOpenXml.ExcelPackage(memoryStream)
var worksheets = package.get_Workbook().get_Worksheets();
var worksheet = worksheets.Add("SHEET");
var cells = worksheet.get_Cells();
var currentRow=1 ;

var cell = cells.get_Item(currentRow,1);
cell.set_Value("__MY__Value");

// NOT Work - compile error
//cell.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color::FromArgb(190, 0, 0));
//cell.Style.Fill.BackgroundColor.Rgb(Winapi::rgb2int(255,255,255));

Both commented lines give a Invalid token '('. compiler error.

How can I use the Background color property about Excel cell, by X++ in right way?

Thanks in advance.

Answer 1

As is often the case when doing .Net Interop from x++, the compiler can be a bit difficult if you mix a chain of properties and method calls. It usually helps to put the last property of the chain into its own variable and then call the method on that variable.

So instead of writing

cell.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color::FromArgb(190, 0, 0));

write this instead:

ExcelColor backgroundColor = cell.Style.Fill.BackgroundColor;
backgroundColor.SetColor(System.Drawing.Color::FromArgb(190, 0, 0));

This will get rid of the Invalid token '('. error.

However, now a runtime exception System.ArgumentException: Can't set color when patterntype is not set. is thrown. This is because OpenOfficeXml expects a fill style along with a background color so that Excel knows how the background color should be applied to the background. See the GettingStartedSample.cs#L57-L64.

I wrote the following code as a runnable class example that produces an Excel file with two values with background colors.

using OfficeOpenXml;
using OfficeOpenXml.Style; // note the additional namespace

class SOCreateExcelWithBackgroundColor
{
    public static void main(Args _args)
    {
        using (System.IO.MemoryStream stream = new System.IO.MemoryStream())
        {
            using (var package = new ExcelPackage(stream))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add('CellBackground');
                
                // add some values and colors
                ExcelRange cell = worksheet.Cells.get_Item('A1');
                cell.Value = 'Value1';
                ExcelFill fill = cell.Style.Fill;
                fill.PatternType = ExcelFillStyle::Solid;
                ExcelColor backgroundColor = fill.BackgroundColor;
                backgroundColor.SetColor(System.Drawing.Color::FromArgb(0, 87, 183));

                cell = worksheet.Cells.get_Item('A2');
                cell.Value = 'Value2';
                fill = cell.Style.Fill;
                fill.PatternType = ExcelFillStyle::Solid;
                backgroundColor = fill.BackgroundColor;
                backgroundColor.SetColor(255, 255, 215, 0); // note that the first 255 defines the transparency (alpha value) of the color

                package.Save();
            }
            File::SendFileToUser(stream, 'CellBackground.xlsx');
        }
    }

}

This creates the following Excel: