DataLabelPositionType on ColumnStacked Excel chart type

[DunasIT]

Hello,

We have updated a .NET solution from version 2.0 (and spire xls dll version 9.5.6.6020) to .NET 4.6.1 (and spire xls dll version 13.1.1.0)

In the new version, spire change the data label position of a columnstacked chart. The problem is that with the new version the labels are hidden behind the stacks or strikethrough.

With the followig code, with .NET 2.0 we get the attached file old_version_grafica_28022023_1133.bmp:

Private Function GraficaPruebaDatalabels() As Integer
Try
Dim rutaImagen As String = "c:\temp\grafica_" & Now.ToShortDateString.Replace("/", "") & "_" & Now.ToShortTimeString.Replace(":", "") & ".bmp"
Dim libro As New Spire.Xls.Workbook
libro.DefaultFontName = "Calibri Light"
libro.CreateEmptySheet(1)
Dim hoja As Spire.Xls.Worksheet = libro.Worksheets(0)
hoja.Name = "ConsVolXTipoAct"
'Vuelco los datos a la hoja:
hoja.Range(1, 1).Value = "Grupo"
hoja.Range(1, 2).Value = "Valor"
hoja.Range(2, 1).Value = "RV EURO"
hoja.Range(2, 2).Value = "0,157704566876736"
hoja.Range(3, 1).Value = "DP NO EURO"
hoja.Range(3, 2).Value = "0"
hoja.Range(4, 1).Value = "DIVERSIFICACION"
hoja.Range(4, 2).Value = "-0,0630980817958831"
hoja.Range(5, 1).Value = "DIVISAS"
hoja.Range(5, 2).Value = "0,0158817699531128"

'Creo el gráfico:
Dim grafico As Spire.Xls.Chart = hoja.Charts.Add()
grafico.ChartType = Spire.Xls.ExcelChartType.ColumnStacked

grafico.LeftColumn = 5
grafico.TopRow = 2
grafico.RightColumn = 31
grafico.BottomRow = 29

grafico.ChartTitle = ""
grafico.DataRange = hoja.Range("B2:B5")
grafico.SeriesDataFromRange = False
grafico.PlotArea.Fill.Visible = False

grafico.PrimaryCategoryAxis.CategoryLabels = hoja.Range("A2:A5")

grafico.PrimaryValueAxis.MajorGridLines.LineProperties.Color = colorLineasRejilla

Dim cs1 As Spire.Xls.Charts.ChartSerie = grafico.Series(0)
cs1.Values = hoja.Range("B2:B5")
cs1.DataPoints.Clear()

cs1.Format.Fill.FillType = Spire.Xls.ShapeFillType.SolidColor
cs1.DataFormat.Fill.ForeColor = Color.Black

cs1.DataPoints.DefaultDataPoint.DataLabels.HasValue = True
cs1.DataPoints.DefaultDataPoint.DataLabels.IsYMode = True
cs1.DataPoints.DefaultDataPoint.DataLabels.Size = CInt(17)
cs1.DataPoints.DefaultDataPoint.DataLabels.IsAutoMode = True

hoja.Range("B1:B5").NumberFormat = "0,00%"

hoja.Range("A1:A5").Style.Font.Size = 12

grafico.PrimaryCategoryAxis.TextRotationAngle = -45
grafico.PrimaryCategoryAxis.TickLabelPosition = Spire.Xls.TickLabelPositionType.TickLabelPositionLow
grafico.PrimaryCategoryAxis.Font.Size = CInt(17)
grafico.PrimaryValueAxis.Font.Size = CInt(17)

grafico.Legend.Delete()

'Pongo un color distinto a las barras con valor negativo:
With grafico
For i As Integer = 0 To cs1.Values.Count - 1
If cs1.Values.Cells(i).Value < 0 Then
cs1.DataPoints().Item(i).DataFormat.Fill.ForeColor = Color.Gray
End If
Next
cs1.DataPoints.DefaultDataPoint.DataLabels.Position = Spire.Xls.DataLabelPositionType.Above
End With

Dim img As Image = libro.SaveChartAsImage(hoja, 0)
'Para borrar el borde:
Dim w As Single = 0.2
Using g As Graphics = Graphics.FromImage(img)
Using pen As New Pen(Color.White, w)
g.DrawRectangle(pen, New Rectangle(0, 0, img.Width - 1, img.Height - 1))
g.Dispose()
End Using
End Using
'Guardo la imagen:
img.Save(rutaImagen, Imaging.ImageFormat.Bmp)

Return 0
Catch ex As Exception
Return -1
End Try
End Function



And with the followig code, with .NET 4.6.1 we get the attached file new_version_grafica_28022023_1144.bmp:

Private Function GraficaPruebaDatalabels() As Integer
Try
Dim rutaImagen As String = "c:\temp\grafica_" & Now.ToShortDateString.Replace("/", "") & "_" & Now.ToShortTimeString.Replace(":", "") & ".bmp"
Dim libro As New Spire.Xls.Workbook
libro.DefaultFontName = "Calibri Light"
libro.CreateEmptySheet(1)
Dim hoja As Spire.Xls.Worksheet = libro.Worksheets(0)
hoja.Name = "ConsVolXTipoAct"
'Vuelco los datos a la hoja:
hoja.Range(1, 1).Value = "Grupo"
hoja.Range(1, 2).Value = "Valor"
hoja.Range(2, 1).Value = "RV EURO"
hoja.Range(2, 2).Value = "0,157704566876736"
hoja.Range(3, 1).Value = "DP NO EURO"
hoja.Range(3, 2).Value = "0"
hoja.Range(4, 1).Value = "DIVERSIFICACION"
hoja.Range(4, 2).Value = "-0,0630980817958831"
hoja.Range(5, 1).Value = "DIVISAS"
hoja.Range(5, 2).Value = "0,0158817699531128"

'Creo el gráfico:
Dim grafico As Spire.Xls.Chart = hoja.Charts.Add()
grafico.ChartType = Spire.Xls.ExcelChartType.ColumnStacked

grafico.LeftColumn = 5
grafico.TopRow = 2
grafico.RightColumn = 31
grafico.BottomRow = 29

grafico.ChartTitle = ""
grafico.DataRange = hoja.Range("B2:B5")
grafico.SeriesDataFromRange = False
grafico.PlotArea.Fill.Visible = False

grafico.PrimaryCategoryAxis.CategoryLabels = hoja.Range("A2:A5")

grafico.PrimaryValueAxis.MajorGridLines.LineProperties.Color = colorLineasRejilla

Dim cs1 As Spire.Xls.Charts.ChartSerie = grafico.Series(0)
cs1.Values = hoja.Range("B2:B5")
cs1.DataPoints.Clear()

cs1.Format.Fill.FillType = Spire.Xls.ShapeFillType.SolidColor
cs1.DataFormat.Fill.ForeColor = Color.Black

cs1.DataPoints.DefaultDataPoint.DataLabels.HasValue = True
cs1.DataPoints.DefaultDataPoint.DataLabels.IsYMode = True
cs1.DataPoints.DefaultDataPoint.DataLabels.Size = CInt(17)
'cs1.DataPoints.DefaultDataPoint.DataLabels.IsAutoMode = True 'NO COMPATIBLE CON NUEVA VERSION

hoja.Range("B1:B5").NumberFormat = "0,00%"

hoja.Range("A1:A5").Style.Font.Size = 12

grafico.PrimaryCategoryAxis.TextRotationAngle = -45
grafico.PrimaryCategoryAxis.TickLabelPosition = Spire.Xls.TickLabelPositionType.TickLabelPositionLow
grafico.PrimaryCategoryAxis.Font.Size = CInt(17)
grafico.PrimaryValueAxis.Font.Size = CInt(17)

grafico.Legend.Delete()

'Pongo un color distinto a las barras con valor negativo:
With grafico
For i As Integer = 0 To cs1.Values.Count - 1
If cs1.Values.Cells(i).Value < 0 Then
cs1.DataPoints().Item(i).DataFormat.Fill.ForeColor = Color.Gray
End If
Next
cs1.DataPoints.DefaultDataPoint.DataLabels.Position = Spire.Xls.DataLabelPositionType.Above
End With

Dim img As Image = libro.SaveChartAsImage(hoja, 0)
'Para borrar el borde:
Dim w As Single = 0.2
Using g As Graphics = Graphics.FromImage(img)
Using pen As New Pen(Color.White, w)
g.DrawRectangle(pen, New Rectangle(0, 0, img.Width - 1, img.Height - 1))
g.Dispose()
End Using
End Using
'Guardo la imagen:
img.Save(rutaImagen, Imaging.ImageFormat.Bmp)

Return 0
Catch ex As Exception
Return -1
End Try
End Function



Please, note that with the new version, the line cs1.DataPoints.DefaultDataPoint.DataLabels.IsAutoMode = True is commented to prevent an error when trying to set a readonly property.

Is possible to get the same result with the new version?

Thanks.

[Abel.He]

Hello,

Thanks for your inquiry.
I created a VB project to test your issue, but I encountered a exception, as shown in the screenshot below, does that happen on your side of this code? However, But I did not reproduce the problem by simulating your scenario using the following code. Please offer the following message to help us reproduce your issue and work out a solution for you, thanks for your assistance in advance.

1) The code that can reproduce your issue.
2) Application type, such as Console App, .NET Framework 4.8.
3)Your test environment, such as OS info (E.g. Windows 7, 64-bit) and region setting (E.g. China, Chinese).

Code: Select all

Sub Main()
        'Create a Workbook
        Dim workbook As New Workbook()

        'Get the first sheet and set its name
        Dim sheet As Worksheet = workbook.Worksheets(0)
        sheet.Name = "FormatAxis"

        'Set chart data
        CreateChartData(sheet)

        'Add a chart
        Dim chart As Chart = sheet.Charts.Add(ExcelChartType.ColumnClustered)
        chart.DataRange = sheet.Range("B1:B9")
        chart.SeriesDataFromRange = False
        chart.PlotArea.Visible = False
        chart.TopRow = 2
        chart.BottomRow = 29
        chart.LeftColumn = 5
        chart.RightColumn = 31
        chart.ChartTitle = "Chart with Customized Axis"
        chart.ChartTitleArea.IsBold = True
        chart.ChartTitleArea.Size = 12
        Dim cs1 As Spire.Xls.Charts.ChartSerie = chart.Series(0)
        cs1.CategoryLabels = sheet.Range("A2:A9")
        cs1.DataPoints.DefaultDataPoint.DataLabels.HasValue = True
        cs1.DataPoints.DefaultDataPoint.DataLabels.IsYMode = True

        'Format axis
        chart.PrimaryValueAxis.MajorUnit = 8
        chart.PrimaryValueAxis.MinorUnit = 2
        chart.PrimaryValueAxis.MaxValue = 50
        chart.PrimaryValueAxis.MinValue = 0
        chart.PrimaryValueAxis.IsReverseOrder = False
        chart.PrimaryValueAxis.MajorTickMark = TickMarkType.TickMarkOutside
        chart.PrimaryValueAxis.MinorTickMark = TickMarkType.TickMarkInside
        chart.PrimaryValueAxis.TickLabelPosition = TickLabelPositionType.TickLabelPositionNextToAxis
        chart.PrimaryValueAxis.CrossesAt = 0

        chart.PrimaryCategoryAxis.TextRotationAngle = -45
        chart.PrimaryCategoryAxis.TickLabelPosition = Spire.Xls.TickLabelPositionType.TickLabelPositionLow
        chart.PrimaryCategoryAxis.Font.Size = CInt(17)
        chart.PrimaryValueAxis.Font.Size = CInt(17)


        'Set NumberFormat
        chart.PrimaryValueAxis.NumberFormat = "0.00%"
        chart.PrimaryValueAxis.IsSourceLinked = False

        Dim serie As ChartSerie = chart.Series(0)

        For Each dataPoint As ChartDataPoint In serie.DataPoints
            'Format Series
            dataPoint.DataFormat.Fill.FillType = ShapeFillType.SolidColor
            dataPoint.DataFormat.Fill.ForeColor = Color.LightGreen

            'Set transparency
            dataPoint.DataFormat.Fill.Transparency = 0.3
        Next dataPoint


        'Pongo un color distinto a las barras con valor negativo:
        With chart
            For i As Integer = 0 To cs1.Values.Count - 1
                If cs1.Values.Cells(i).Value < 0 Then
                    cs1.DataPoints().Item(i).DataFormat.Fill.ForeColor = Color.Gray


                End If
            Next
            cs1.DataPoints.DefaultDataPoint.DataLabels.Position = Spire.Xls.DataLabelPositionType.Above
        End With

        'Save and Launch
        workbook.SaveToFile("../../output/custom_Output.xlsx", ExcelVersion.Version2010)
    End Sub
    Private Sub CreateChartData(ByVal sheet As Worksheet)
        'Set value of specified cell
        sheet.Range("A1").Value = "Month"
        sheet.Range("A2").Value = "Jan"
        sheet.Range("A3").Value = "Feb"
        sheet.Range("A4").Value = "Mar"
        sheet.Range("A5").Value = "Apr"
        sheet.Range("A6").Value = "May"
        sheet.Range("A7").Value = "Jun"
        sheet.Range("A8").Value = "Jul"
        sheet.Range("A9").Value = "Aug"

        sheet.Range("B1").Value = "Planned"
        sheet.Range("B2").NumberValue = 0.38
        sheet.Range("B3").NumberValue = 0.47
        sheet.Range("B4").NumberValue = -0.39
        sheet.Range("B5").NumberValue = 0.36
        sheet.Range("B6").NumberValue = -0.27
        sheet.Range("B7").NumberValue = 0.25
        sheet.Range("B8").NumberValue = -0.36
        sheet.Range("B9").NumberValue = 0.48

        sheet.Range("B2:B9").NumberFormat = "0.00%"


    End Sub

Sincerely
Abel
E-iceblue support team

[DunasIT]

Hello,

I don't have that error on my side. I attach you the screenshot with the DataRange value set, so you can check it. Anyway, I think the data range is not the problem, you can modify it as needed.

The application is a library, version .NET Framework 4.6.1, and when debugging I run it on a Windows 10 Pro (version 10.0.19045). And the regional settings are Spanish (Spain international).

Please, note that the chart type is ColumnStacked, not ColumnClustered.

The code is still:

Private Function GraficaPruebaDatalabels() As Integer
Try
Dim rutaImagen As String = "c:\temp\grafica_" & Now.ToShortDateString.Replace("/", "") & "_" & Now.ToShortTimeString.Replace(":", "") & ".bmp"


Dim libro As New Spire.Xls.Workbook
libro.DefaultFontName = "Calibri Light"
libro.CreateEmptySheet(1)
Dim hoja As Spire.Xls.Worksheet = libro.Worksheets(0)
hoja.Name = "ConsVolXTipoAct"
'Vuelco los datos a la hoja:
hoja.Range(1, 1).Value = "Grupo"
hoja.Range(1, 2).Value = "Valor"
hoja.Range(2, 1).Value = "RV EURO"
hoja.Range(2, 2).Value = "0,157704566876736"
hoja.Range(3, 1).Value = "DP NO EURO"
hoja.Range(3, 2).Value = "0"
hoja.Range(4, 1).Value = "DIVERSIFICACION"
hoja.Range(4, 2).Value = "-0,0630980817958831"
hoja.Range(5, 1).Value = "DIVISAS"
hoja.Range(5, 2).Value = "0,0158817699531128"

'Creo el gráfico:
Dim grafico As Spire.Xls.Chart = hoja.Charts.Add()
grafico.ChartType = Spire.Xls.ExcelChartType.ColumnStacked

grafico.LeftColumn = 5
grafico.TopRow = 2
grafico.RightColumn = 31
grafico.BottomRow = 29


grafico.ChartTitle = ""
grafico.DataRange = hoja.Range("B2:B5")
grafico.SeriesDataFromRange = False
grafico.PlotArea.Fill.Visible = False

grafico.PrimaryCategoryAxis.CategoryLabels = hoja.Range("A2:A5")

grafico.PrimaryValueAxis.MajorGridLines.LineProperties.Color = colorLineasRejilla

Dim cs1 As Spire.Xls.Charts.ChartSerie = grafico.Series(0)
cs1.Values = hoja.Range("B2:B5")
cs1.DataPoints.Clear()

cs1.Format.Fill.FillType = Spire.Xls.ShapeFillType.SolidColor
cs1.DataFormat.Fill.ForeColor = Color.Black

cs1.DataPoints.DefaultDataPoint.DataLabels.HasValue = True
cs1.DataPoints.DefaultDataPoint.DataLabels.IsYMode = True
cs1.DataPoints.DefaultDataPoint.DataLabels.Size = CInt(17)
'cs1.DataPoints.DefaultDataPoint.DataLabels.IsAutoMode = True 'NO COMPATIBLE CON NUEVA VERSION

hoja.Range("B1:B5").NumberFormat = "0,00%"

hoja.Range("A1:A5").Style.Font.Size = 12

grafico.PrimaryCategoryAxis.TextRotationAngle = -45
grafico.PrimaryCategoryAxis.TickLabelPosition = Spire.Xls.TickLabelPositionType.TickLabelPositionLow
grafico.PrimaryCategoryAxis.Font.Size = CInt(17)
grafico.PrimaryValueAxis.Font.Size = CInt(17)

grafico.Legend.Delete()

'Pongo un color distinto a las barras con valor negativo:
With grafico
For i As Integer = 0 To cs1.Values.Count - 1
If cs1.Values.Cells(i).Value < 0 Then
cs1.DataPoints().Item(i).DataFormat.Fill.ForeColor = Color.Gray
End If
Next
cs1.DataPoints.DefaultDataPoint.DataLabels.Position = Spire.Xls.DataLabelPositionType.Above
End With

Dim img As Image = libro.SaveChartAsImage(hoja, 0)
'Para borrar el borde:
Dim w As Single = 0.2
Using g As Graphics = Graphics.FromImage(img)
Using pen As New Pen(Color.White, w)
g.DrawRectangle(pen, New Rectangle(0, 0, img.Width - 1, img.Height - 1))
g.Dispose()
End Using
End Using
'Guardo la imagen:
img.Save(rutaImagen, Imaging.ImageFormat.Bmp)

Return 0
Catch ex As Exception
Return -1
End Try
End Function


Thanks.

[Abel.He]

Hello,

Thanks for your feedback.
I was surprised that my side threw an exception every time I ran grafico. DataRange = hoja.Range("B2:B5"), therefore, I modify the code to grafico.DataRange = hoja.Range("A1:B5"). This monment, I can get the result bmp file, and I found the issue of the labels are hidden behind the stacks or strikethrough, as shown in the following screenshot below. However, you can try set the data label position to achieve your requirement.

Code: Select all

cs1.DataPoints.DefaultDataPoint.DataLabels.Position =  XXXX;

If the code doesn’t help you, please write back.

Sincerely
Abel
E-iceblue support team