Hiding Data Label in Charts

Aspose.Cells Product Family
1

I have a template with a chart containing multiple series defined.


I’ve attempted to hide data labels when the value is zero by setting a custom Number format as shown in the attached .png.

The Excel document renders with data labels that are zero still, but if I click into the format Number and simply click the ‘Add’ button again, then the data label is hidden.

Does Aspose run the same module that Excel is running when I click the ‘Add’ button after its rendered?


2

Hi,

Thanks for your posting and using Aspose.Cells.

Please provide us your sample source excel file and the sample expected output excel file. You can create both of them manually using Microsoft Excel. If you could also provide some code, then it will also be helpful. We will look into your issue and help you asap.

3
The problem is that we do not want data labels for Positive and Negative series categories to show on the Chart when the values is not greater than zero (""). The template being used (sample.xlsx) with test data in it formats the way we would like it to.

We are getting the results of sample_10_11_2015 .xlsx when running the template through Aspose.Cells.

See the "Pam Email" series for issue we are trying to solve. We do not want 0 to show like it does in the sample_10_11_2015 xlsx.




4

Hi,

Thanks for your posting and using Aspose.Cells.

I have checked your both files and I found there is one 0 data label which you want to hide, but I could not find any way to hide manually using Microsoft Excel. Could you please explain how to hide it manually using Microsoft Excel 2013. It will help us to find the similar code for Aspose.Cells. You could provide us screenshots showing how to achieve your desired excel file from your actual excel file. Thanks for your cooperation.

5

The original post provides the screen shot your requesting.


Right click, format data labels. Online posts state that you can add a custom Number format to hide values of empty string or 0.



6

Hi,

Thanks for your clarification, detail elaboration and using Aspose.Cells.

You need to set chart.NSeries[2].DataLabels.NumberFormat = “#” to achieve your requirement.

Please check the source excel file as well as output excel file generated by the following code for your reference.

C#
Workbook workbook = new Workbook(“source.xlsx”);

Worksheet worksheet = workbook.Worksheets[0];

Chart chart = worksheet.Charts[0];

for(int i=0; i<chart.NSeries.Count; i++)
{
Console.WriteLine(chart.NSeries[i].Name);
}

//Set the number format of 3rd series whose name is negative
chart.NSeries[2].DataLabels.NumberFormat = “#”;

workbook.Save(“output.xlsx”);

7

That did it. Thank you!

8

Hi Travas,


It is good to know that you are up & running again. Please feel free to contact us back in case you need our further assistance with Aspose APIs.
9

Hello,

I generated a chart in Aspose Cells. I saved it to an svg to later include it in a pdf. However the datalabels text are truncated. Please advise.

10

@devyoni.mohun,

Thanks for providing us some details.

Please provide us your sample code (runnable) or preferably create a console application (you may exclude Aspose.Cells assembly), you may zip it prior attaching here to reproduce the issue on our end, we will check it soon. Also provide input file (if any) and output svg file for better evaluation of your issue.

PS. Please use our latest version/fix: Aspose.Cells v17.12 if you are not already using it.

11

Hello,

The code generates both the excel and the image in svg format. You will notice the labels text are truncated.

Here is the solution zip file:

Do you know why i can’t upload the zip file on this post?

Regards

12

@devyoni.mohun,

Please zip your attachment(s) in an archive and attach it here (if the size of the final zip file is <=3MB). Alternatively, you may upload your attachments to some file sharing service (e.g Dropbox, Google drive, etc.) and share the Download link here, we will check it soon.

13

Hello,

Here you are:

14

@devyoni.mohun

Thanks for using Aspose APIs.

We were able to observe this issue and logged it in our database for investigation and for a fix. Once, the issue is resolved or we have some other news for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-45828 - Data labels in Chart Image are truncated and do not match with Output Excel Chart

Output Excel File and SVG Image
Output Excel File and SVG Image.zip (11.3 KB)

Screenshot of Erroneous Output Image

15

Hello Shakeel,

I have done a fix right now whereby i had to increase the width and height of the datalabels for them to be visible.

e.g
chart.NSeries[0].Points[x].DataLabels.Width = 1000;
chart.NSeries[0].Points[x].DataLabels.Height = 500;

16

@devyoni.mohun,

Good to know that you have sorted out your issue by extending the width/height of the data labels. We will also try to figure it out so you don’t have to use the workaround.

Once we have any new information, we will share it with you.

17

Thank you very much for helping me out.

18

@devyoni.mohun,

Please change the part of GeneratePDF() using following code segment. You must call Calculate() method after changing the text of datalabels:
e.g
Sample code:

    //Move down 
    //If call here, the size of datalabels will be small as the text of datalabel is changed again.
    //chart.Calculate();

    List<DataLabelPosition> dataLabelPositions = new List<DataLabelPosition>();
    int countY = 100;
    int chartWidth = chart.ActualChartSize.Width;
    int countNumberOfLabels;
    if (distributions.Count % 2 == 0)
        countNumberOfLabels = distributions.Count / 2;
    else
        countNumberOfLabels = (distributions.Count / 2) + 1;

    //Set text of datalabels
    if (distributions!= null)
    {
        for (int x = 0; x < distributions.Count; x++)
        {
            Distribution assetClassDistribution = distributions[x];
            string color = assetClassDistribution.Colour; //This would be a parameter
            if (color.StartsWith("#"))
                color = color.Remove(0, 1);
            byte r, g, b;
            if (color.Length == 3)
            {
                r = Convert.ToByte(color[0] + "" + color[0], 16);
                g = Convert.ToByte(color[1] + "" + color[1], 16);
                b = Convert.ToByte(color[2] + "" + color[2], 16);
            }
            else if (color.Length == 6)
            {
                r = Convert.ToByte(color[0] + "" + color[1], 16);
                g = Convert.ToByte(color[2] + "" + color[3], 16);
                b = Convert.ToByte(color[4] + "" + color[5], 16);
            }
            else
            {
                throw new ArgumentException("Hex color " + color + " is invalid.");
            }
            chart.NSeries[0].Points[x].Area.ForegroundColor = System.Drawing.Color.FromArgb(0, r, g, b);

            chart.NSeries[0].Points[x].DataLabels.Text = string.Format("{0}\n{1}€ - {2}%", assetClassDistribution.Header, assetClassDistribution.Invest, assetClassDistribution.Percentage);
            chart.NSeries[0].Points[x].DataLabels.Font.Color = System.Drawing.Color.FromArgb(0, r, g, b);
        }
    }

    //Call after seting datalabels
    chart.Calculate();

    //Set position of datalabels
    if (distributions != null)
    {
        for (int x = 0; x < distributions.Count; x++)
        {
            Distribution assetClassDistribution = distributions[x];
            
            int positionX = chart.NSeries[0].Points[x].DataLabels.X;
            if ((x + 1) <= countNumberOfLabels)
            {
                chart.NSeries[0].Points[x].DataLabels.X = chartWidth + 1300;
                chart.NSeries[0].Points[x].DataLabels.Y = countY;
                countY += 600;
            }
            else
            {
                chart.NSeries[0].Points[x].DataLabels.X = chartWidth - 500;
                chart.NSeries[0].Points[x].DataLabels.Y = countY;
                countY -= 600;
            }

            dataLabelPositions.Add(new DataLabelPosition { PositionX = chart.NSeries[0].Points[x].DataLabels.X, PositionY = chart.NSeries[0].Points[x].DataLabels.Y });
        }
    }
1 Like
19

Awesome. It works beautifully.

Thank you so much for your help.

I have a question though. Currently i am generating a chart in Aspose.Cells, saving it as an svg file on disk then importing it as an image in pdf using Aspose.Pdf. Do we have an easier way to draw a chart in PDF?

Regards

20

@devyoni.mohun,

Good to know that your issue is sorted out by the suggested code.

Regarding your new query, well, Aspose.Cells APIs provides the Chart.ToPdf method with ability to directly render the chart in PDF. See the document for your reference:

Hope, this helps a bit.