Diagonal gradient fill for a cell

[jtampier]

In Excel I can create a diagonally half colored cell by using the following code:

Code: Select all

Sub Macro1()
'
' Macro1 Macro
'
    With Selection.Interior
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 45
        .Gradient.ColorStops.Clear
    End With
    With Selection.Interior.Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1 'First Color
    End With
    With Selection.Interior.Gradient.ColorStops.Add(0.49)
        .ThemeColor = xlThemeColorDark1 'First Color
    End With
    With Selection.Interior.Gradient.ColorStops.Add(0.51)
        .ThemeColor = xlThemeColorAccent1 'Second Color
    End With
    With Selection.Interior.Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorAccent1 'Second Color
    End With
End Sub

With Spire.XLS I currently seem unable to access the ColorStops and Degree properties. Is there an implementaion goal or a workaround?

Thanks

[Jane.Bai]

Hello jtampier,

Thank you for your post.
Our Spire.XLS supports the feature you need.
Please download the latest(Spire.XLS Pack(Hotfix) Version:8.5.1) and use the code below to accomplish your task.

Code: Select all

Dim workbook As var = New Workbook
workbook.Version = ExcelVersion.Version2010
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim b1 As CellRange = sheet.Range("B1")
b1.Style.Interior.FillPattern = ExcelPatternType.Gradient
'set the color stops
b1.Style.Interior.Gradient.BackColor = Color.LightSkyBlue
b1.Style.Interior.Gradient.ForeColor = Color.LightPink
'set the gradient type
b1.Style.Interior.Gradient.GradientStyle = GradientStyleType.Diagonl_Down
'set the variant
b1.Style.Interior.Gradient.GradientVariant = GradientVariantsType.ShadingVariants1
workbook.SaveToFile("13844.xlsx")


Sincerely,
Jane
E-iceblue support team

[jtampier]

Thank you for the quick response. I could have mentioned, that I tried it, but as you will see I was talking about customization of the gradient stops for a different effect.

excel_gradient.png (5.95 KiB) Viewed 2505 times

The left box is made with your method, while the right one is made with the macro from above.

[Jane.Bai]

Hello,

Thanks for your response.
Sorry that the there's no direct way to reach your goal by formatting a cell.
As a workaround, you could add a triangle shape. Please refer to the code below.

Code: Select all

var workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];
sheet.SetRowHeightInPixels(1, 1, 100);
sheet.SetColumnWidthInPixels(1, 100);
sheet.DefaultColumnWidth = sheet.GetColumnWidth(1);
IPrstGeomShape shape = sheet.PrstGeomShapes.AddPrstGeomShape(1, 1, 100, 100, PrstGeomShapeType.RtTriangle);

shape.Rotation = 270;
shape.Fill.FillType = ShapeFillType.SolidColor;
shape.Fill.BackColor = Color.LightBlue;

string result = "13844.xlsx";
workbook.SaveToFile(result, ExcelVersion.Version2013);
System.Diagnostics.Process.Start(result);

Sincerely,
Jane
E-iceblue support team

[jtampier]

Thank you for the alternative approach, since the generated table will be resortable it is sadly not an option. I will go with a full fill instead (bad luck for the color impaired).
I tried copying the Style with Spire.XLS, too, but that yields an OutOfRange-Exception.
If you ever get to implement those gradient options, I'll get back to it.

[Jane.Bai]

Hi,

Sorry for the inconvenience caused.
Once the feature is available in the feature, I will inform you.

Sincerely,
Jane
E-iceblue support team