Hi Team,
I have the following excel file.
chart-forum.zip (15.8 KB)
In it there is a simple chart, with one graph representing one data series.
Data labels are added to the chart. The default setting is 11px fontsize, however some labels are individually resized to some other font size.
I need to access each and every label, detect the font size and the text of that particular label, and resize the label font if necessary.
I tried the online documentation but did not succeed in finding a solution for this.
Can you please help me out with some example C# .Net code using Aspose.Cells? Thank you!
@zpopswat
You want to read each data‑label’s font size (and its text) and change it if needed.
How to do it with Aspose.Cells
- Load the workbook and obtain the chart.
- Make sure data labels are shown for the series (
series.IsShowDataLabels = true).
- Iterate through the series (
chart.NSeries) and then through each point (series.Points).
- For each point use
point.DataLabel – its Font.Size returns the current font size and Text returns the label text.
- Set
point.DataLabel.Font.Size (or other font properties) to the desired value and, if required, modify point.DataLabel.Text.
C# example
using Aspose.Cells;
using Aspose.Cells.Charts;
Workbook wb = new Workbook(@"C:\Input.xlsx");
// first worksheet and first chart (adjust indexes as needed)
Chart chart = wb.Worksheets[0].Charts[0];
foreach (NSeries series in chart.NSeries)
{
// ensure data labels are visible for the series
series.IsShowDataLabels = true;
foreach (NSeriesPoint pt in series.Points)
{
DataLabel lbl = pt.DataLabel; // label for this point
double curSize = lbl.Font.Size; // current font size
string curText = lbl.Text; // current label text
// example: increase size to 14 if it is smaller than 12
if (curSize < 12)
{
lbl.Font.Size = 14; // set new size
}
// you can also change the displayed text, e.g.:
// lbl.Text = "New text";
}
}
wb.Save(@"C:\Output.xlsx");
series.IsShowDataLabels enables the labels for the whole series.pt.DataLabel.Font.Size gets/sets the font size of an individual label, and pt.DataLabel.Text accesses its text [2].
This lets you inspect and modify each data label separately for chart types that support data labels.
@zpopswat,
Thanks for the template Excel file and details.
See the sample code to accomplish your task for your reference.
e.g.,
Sample code:
// Load the existing workbook that contains the XY scatter chart
Workbook workbook = new Workbook("e:\\test2\\chart-forum.xlsm");
Worksheet worksheet = workbook.Worksheets[0];
Chart chart = worksheet.Charts[0];
// Access the first (and only) series of the chart
Series series = chart.NSeries[0];
// Ensure data labels are enabled
series.DataLabels.IsDeleted = false;
chart.Calculate();
foreach (ChartPoint pt in series.Points)
{
DataLabels lbl = pt.DataLabels; // label for this point
double curSize = lbl.Font.Size; // current font size
string curText = lbl.Text; // current label text
// If the font size differs from the desired 11 points, set it to 11
if (Math.Abs(curSize - 11) > 0.01)
{
Console.WriteLine($"Label : Text=\"{curText}\", FontSize={curSize}");
lbl.Font.Size = 11;
}
}
// Save the workbook with the updated font sizes
workbook.Save("e:\\test2\\out1.xlsx");
Hope, this helps a bit.
Thank you for the quick response.
It partly resolves my issue.
However what I don’t understand is this:
- printing series.Points.Count shows 14, which means there are 14 points, which is the expected as the data series consists of 14 data points
- when looping through with the foreach, only one ChartPoint is processed, with the text “1840” and font size 31
Console.WriteLine($"Series : Name=\"{series.Name}\", Points count ={series.Points.Count}");
foreach (ChartPoint pt in series.Points)
{
DataLabels lbl = pt.DataLabels; // label for this point
double curSize = lbl.Font.Size; // current font size
string curText = lbl.Text; // current label text
//log each processed point for debugging purposes
Console.WriteLine($"Processing data label : Text=\"{curText}\", FontSize={curSize}");
// If the font size differs from the desired 11 points, set it to 11
if (Math.Abs(curSize - 11) > 0.01)
{
Console.WriteLine($"Label : Text=\"{curText}\", FontSize={curSize}");
lbl.Font.Size = 11;
}
}
Please, can you explain why I can not loop through the rest of the chart points?
@zpopswat
In order to save memory, we only initialized a few points which are need and different settings from series.
So if looping through with the foreach, only the existed points are returned.
@zpopswat,
Moreover, if you need to retreive points values, you may try the following code segment.
//Get all the data point (label) values for the series
foreach (ChartDataValue val in series.PointValues)
{
Console.WriteLine(val.IntValue);
}
Thank you for you response. So if I want to iterate through all datapoints in order to discover their label font settings I need to loop through both collections, series.PointValues and series.Points, with
series.Points containing only those items that have custom settings differing from the default label settings for the data series.
My question is then, how can I tell which datapoints are listed in series.Points so that I do not process them again in series.PointValues
Thank you!
@zpopswat,
As mentioned earlier, to optimize memory usage, we only initialize specific points that are necessary and have unique settings within a particular series. Therefore, it would be advisable to use both the series.PointValues and series.Points APIs to achieve this task. Kindly refer to the following example for your reference.
e.g.,
Sample code:
for (int i = 0; i < series.Points.Count; i++)
{
ChartPoint pt = series.Points[i];
ChartDataValue val = series.PointValues[i];
Console.WriteLine($"Point {i +1}: Value={val.IntValue}, Size=\"{pt.DataLabels.Font.Size}\"");
}
Hope, this helps a bit.