Change excel cell background color using Open xml

gianmibuso17

Hi
I Use Open XML to Extend the Excel Buffer Functionality but I don't know how to set cell background color.

For example, I use this code to set the font :

---

//Create Font color
font1 := XlWrkBkWriter.FirstWorksheet.DefaultCellDecorator.Font.CloneNode(TRUE);
fontcolor := fontcolor.Color;
fontcolor.Rgb := FontHexValue.HexBinaryValue(ColorFont);
font1.Color := fontcolor;

//Create font size
FontSize := FontSize.FontSize;
FontSize.Val := FontSizeDoubleValue.DoubleValue(DimensionFont);
font1.FontSize := FontSize;

// Create a new Font Text
FontName := FontName.FontName;
FontName.Val := FontNameStringValue.FromString(NameFont);
font1.FontName := FontName;

Decorator.Font:=font1;
XlWrkShtWriter.SetCellValueText(RigaCorrente,column,FORMAT(ValoreVariant),Decorator)

---

Help me !! ](*,) ](*,)

Thanks in advance

geordie

Hi, in my custom table made for using ClosedXML as Open Xml "wrapper", I added a new field option to the table (OptionString=Black,Blue,Gray,Green,Magenta,Orange,Purple,Red,White,Yellow) and added this code in CreateSheet function:

XlWrkShtDotNet@150002025 : DotNet "'ClosedXML, Version=0.59.0.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.IXLWorksheet";
XLStyleDotNet@150002029 : DotNet "'ClosedXML, Version=0.59.0.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.IXLStyle";
XLColorDotNet@1101321021 : DotNet "'ClosedXML, Version=0.59.0.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.XLColor";

XLStyleDotNet := XlWrkShtDotNet.Cell("Row No.","Column No.").Style;

IF "Background Color" <> "Background Color"::White THEN
	CASE "Background Color" OF
	  "Background Color"::Black : XLStyleDotNet.Fill.BackgroundColor := XLColorDotNet.Black;
	  "Background Color"::Blue : XLStyleDotNet.Fill.BackgroundColor := XLColorDotNet.Blue;
	  "Background Color"::Gray : XLStyleDotNet.Fill.BackgroundColor := XLColorDotNet.Gray;
	  "Background Color"::Green : XLStyleDotNet.Fill.BackgroundColor := XLColorDotNet.Green;
	  "Background Color"::Magenta : XLStyleDotNet.Fill.BackgroundColor := XLColorDotNet.Magenta;
	  "Background Color"::Orange : XLStyleDotNet.Fill.BackgroundColor := XLColorDotNet.Orange;
	  "Background Color"::Purple : XLStyleDotNet.Fill.BackgroundColor := XLColorDotNet.Purple;
	  "Background Color"::Red : XLStyleDotNet.Fill.BackgroundColor := XLColorDotNet.Red;
	  "Background Color"::Yellow : XLStyleDotNet.Fill.BackgroundColor := XLColorDotNet.Yellow;
	END;

Hope it helps.

Regards

gianmibuso17

Thanks

But I use this variable

XlWrkShtWriter :
Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorksheetWriter.'Microsoft.Dynamics.Nav.OpenXml, Version=7.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'

to write into cells.
how can I replicate your suggestion?

davmac1

Receommend you look into closedxml.
There are several blogs showing you how to do this, and it is a free download.