How to use GradientFill using ClosedXML

Question

I am working on a C # / ASP.NET website and want to export Excel. At first I found OpenXML, took the first few steps, but it is very difficult to use. Now I'm using ClosedXML, and it's pretty bumpy for now.

I ran into the problem of having a gradient fill in a table cell ... no problems with normal fillings like

worksheet.Cell(1,1).Style.Fill.SetBackgroundColor(XLColor.Red);

or with patterns like

worksheet.Cell(1,1).Style.Fill.PatternType = XLFillPatternValues.LightHorizontal;
worksheet.Cell(1,1).Style.Fill.PatternColor = XLColor.Green;
worksheet.Cell(1,1).Style.Fill.PatternBackgroundColor = XLColor.White;

But how to make a GradientFill? As far as I understand, ClosedXML is built on the OpenXML SDK, so it should be possible to make a GradientFill - something like (I could not check it yet)

GradientFill gFillEx = new GradientFill();
GradientStop gStop1st = new GradientStop();
gStop1st.Position = 0;
gStop1st.Color = new Color { Rgb = HexBinaryValue.FromString("00FF00") };
GradientStop gStop2nd = new GradientStop();
gStop2nd.Position = 1;
gStop2nd.Color = new Color { Rgb = HexBinaryValue.FromString("FFFFFF") };
gFillEx.InsertAt<GradientStop>(gStop1st, 0);
gFillEx.InsertAt<GradientStop>(gStop2nd, 1);

But how to continue - how to set this gFillEx as a cell fill style? I also would not want to use the xml from the styles.xml file right here somewhere - if I knew where to put it.

? .

Answer 1

, ClosedXML GradientFill, WorkbookStylesPart OpenXML ClosedXML,

.xlsx

public ActionResult XLSX()
{
  System.IO.Stream spreadsheetStream = new System.IO.MemoryStream();
  XLWorkbook workbook = new XLWorkbook();
  IXLWorksheet worksheet = workbook.Worksheets.Add("GradientFillExample");
  worksheet.Cell(1, 1).SetValue("example").Style.Fill.SetBackgroundColor(XLColor.FromHtml("#08F47B")); // use some unique color
  workbook.SaveAs(spreadsheetStream);

- , WorkbookStylesPart , ... OpenXML ( )

  SpreadsheetDocument package = SpreadsheetDocument.Open(spreadsheetStream, true);
  WorkbookPart wbPart = package.GetPartsOfType<WorkbookPart>().FirstOrDefault();
  WorkbookStylesPart wbStylePart = wbPart.GetPartsOfType<WorkbookStylesPart>().FirstOrDefault();
  Stylesheet stylesheet = wbStylePart.Stylesheet; // all three are not null - check if you want

, .

  OpenXmlElement oldFill = stylesheet.Fills.FirstOrDefault(f => f.OuterXml.Contains("08F47B")); // find the fill that uses your unique color
  if (oldFill != null) // maybe you generate the .xlsx and the "gradient fill" is not always present
  {
    GradientFill gradientFill = new GradientFill() { Degree = 0 };
    gradientFill.Append(new GradientStop() { Position = 0D, Color = new Color() { Rgb = "FF00FF00" } });
    gradientFill.Append(new GradientStop() { Position = 1D, Color = new Color() { Rgb = "FFFFFFFF" } });
    oldFill.ReplaceChild(gradientFill, oldFill.FirstChild); // inside the fill replace the patternFill with your gradientFill
  }
  package.Close();

, ...

  spreadsheetStream.Position = 0;
  return new FileStreamResult(spreadsheetStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") { FileDownloadName = "gradfillexample.xlsx" };
}

. , :

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using ClosedXML.Excel;

: , 0, 90, 180, 270 ( ), ,

    GradientFill gradientFill = new GradientFill() { Degree = 354 };
    gradientFill.Append(new GradientStop() { Position = 0D, Color = new Color() { Rgb = "FF00FF00" } });
    gradientFill.Append(new GradientStop() { Position = 0.49D, Color = new Color() { Rgb = "FF00FF00" } });
    gradientFill.Append(new GradientStop() { Position = 0.51D, Color = new Color() { Rgb = "FFFFFFFF" } });
    gradientFill.Append(new GradientStop() { Position = 1D, Color = new Color() { Rgb = "FFFFFFFF" } });

Excel ( Excel Viewer) ( , ), Excel, , .