如何在ClosedXML中使用渐变填充(GradientFill) [英] How to use a gradient fill (GradientFill) with ClosedXML

问题描述

我正在使用C#/ASP.NET Web东西，并且想要执行Excel导出.首先，我找到了OpenXML，迈出了第一步，但确实很难使用.现在，我正在使用ClosedXML，到目前为止，这很轻松.

I'm working on a C#/ASP.NET web thing and want to do an Excel export. First I found OpenXML, made some first steps, but it's really hard to use. Now, I'm using ClosedXML and it's quite a relief - so far.

我想出了一个问题，要在表格单元格上进行渐变填充...普通填充等问题都没有问题

I came up with the problem to have a gradient fill on a table cell ... no problem with normal fills like

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

或带有类似模式

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;

但是如何进行GradientFill?据我了解，ClosedXML是基于OpenXML SDK构建的，因此应该可以进行GradientFill-之类的(我无法测试)

But how to do a GradientFill? As far as I understand ClosedXML is built on the OpenXML SDK, so it should be possible to do a GradientFill - something like (I couldn't test that 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);

但是如何进行-如何将此gFillEx设置为单元格的填充样式?如果我只知道放在哪里，我也不会介意直接从styles.xml文件中的xml使用它.

But how to proceed - how to set this gFillEx as fill style of a cell? I also wouldn't mind to use the xml from the styles.xml file directly somewhere here - if I only knew where to put.

有任何提示或有用的想法吗?谢谢.

Any hints or helpful ideas? Thanks.

推荐答案

在ClosedXML不了解GradientFill之后，由于我没有找到在ClosedXML中操作OpenXML的WorkbookStylesPart的方法，我得到了以下解决方法

After ClosedXML doesn't know about GradientFill and as I didn't found a way to manipulate the WorkbookStylesPart of OpenXML within ClosedXML, I ended up with the following workaround

首先在内存流中生成您的.xlsx

First generate your .xlsx in a memory stream

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(使用内存流)打开内存流我们不必关心临时文件)并导航到样式表

The idea is to use a unique fill color - we're going to replace that fill in the WorkbookStylesPart with the gradient fill that we intended to have ... so we open the memory stream with OpenXML again (with the memory stream we don't have to care about a temporary file) and navigate to the stylesheet

  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

由于总是有两种默认的填充样式，因此样式表永远不会为空.现在，我们可以在样式表的填充"中搜索我们独特的颜色，然后将其替换为渐变填充

As there are always two default fill styles, the stylesheet's never empty. Now we can search for our unique color in the Fills of the Stylesheet and replace that fill with our gradient fill

  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();

最后，我们可以关闭内存流，并将其显示出来以供下载...

Finally we can close the memory stream and present it for download ...

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

要进行测试，只需将该操作放入您的控制器即可.别忘了您需要一些用法:

For testing, simply put that action into your controller. Don't forget you need some usings:

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

其中有趣的部分:您还可以使用0、90、180、270(而不是对角渐变)以外的其他度数，甚至可以使用多级渐变，因此也可以使用这种方式

The fun part of this: you can also use other degrees than 0, 90, 180, 270 (will result in some kind of diagonal gradient) and you can even use multi-stop gradients, so something like this is also possible

    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中编辑度值或多级渐变，但是很适合生成工作表.

While Excel (and Excel Viewer) displays this correctly (the degree value corresponds to a square cell, so that one might be stretched), you can't edit the degree values or multi-stop gradients in Excel, nevertheless, it's nice for generated sheets.

这篇关于如何在ClosedXML中使用渐变填充(GradientFill)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！