Re-set data labels texts

centile · November 19, 2015, 1:37pm

I am changing a pre-existing chart, modifying the labels via Cells.

The initial chart labels had been changed via Excel by two ways.
A list of texts inputed in the data editor for the series.
Some labels had been changed later on directly by double clicking on individual labels.

The modifications via Cells are :

Series series = seriesIterator.next();
String textBefore = series.getXValues();
String text = translate(textBefore);
if (!textBefore.equals(text)) {
series.setXValues(text);
}

When opened in Excel, the new chart labels are not good.
- only one label is updated for the text (the only one not updated manually with Excell ? - not verified- )
- none of the other items displayed with the text have changed (value and percentage) despite the source values had been modified.

When editing the chart labels via the right-click menu, there is a button that re-applies the modifications in order to repaint the labels. (button shown in attached picture).

When clicked, all the texts are updated and the values changed properly.
(it can be reproduced with attached example.xlsx chart)

What am I doing wrong in the modification ? A step missing ?
Is there a command similar to the Excel button or a way to re-validate the labels ?

babar.raza · November 20, 2015, 12:32am

Hi Sébastien,

Thank you for contacting Aspose support.

Please try the following piece of code for modifying existing data labels of a chart and let us know of your feedback.

Java

Workbook workbook = new Workbook(“C:/temp/example.xlsx”);
Chart chart = workbook.getWorksheets().get(“Feuil1”).getCharts().get(“Graphique 1”);
for (int s = 0; s < chart.getNSeries().getCount(); s++)
{
Series series = chart.getNSeries().get(s);
for (int i = 0; i < series.getPoints().getCount(); i++)
{
series.getPoints().get(i).getDataLabels().setAutoText(false);
String oldText = series.getPoints().get(i).getDataLabels().getText();
series.getPoints().get(i).getDataLabels().setText(oldText + " modified");
}
}
chart.calculate();
workbook.save(“c:/temp/output.xlsx”);

babar.raza · November 20, 2015, 12:41am

centile:

What am I doing wrong in the modification ? A step missing ?

The code provided above should fulfill your requirement. In case you still face any difficulty, please provide us an executable sample application for further investigation.

centile:

Is there a command similar to the Excel button or a way to re-validate the labels ?

You need to disable the DataLabel's AutoText property first otherwise the modifications will not take effect. Regarding the question about the Excel button, I am afraid, I am not able to understand which button/option are you talking about because the snapshot shows text in some language other than English. Could you be kind enough to share the steps to open the particular dialog or share a snapshot from Excel in English language?

centile · November 20, 2015, 2:29am

To reach the button:
- right-click on any chart label
- select last option (something like ‘edit data labels’)
- the button should be there (using Excel 2007)

centile · November 20, 2015, 4:42am

Here is a short example:
book.xlsx is the origin chart, with a series composed of two values that are range references (1 cell only ranges) and whose labels is a list : ={"${key1}","${key2}"}
Then, the second label has been updated manually, adding “manual " in front.

The following code will generate 2 outputs, one with the method you provided, one with the method I was using.

import java.util.Iterator;

import com.aspose.cells.Cells;
import com.aspose.cells.Chart;
import com.aspose.cells.ChartPoint;
import com.aspose.cells.ChartPointCollection;
import com.aspose.cells.DataLabels;
import com.aspose.cells.Series;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;

public class Test {
private static final String source = “D:/book.xlsx”;
private static final String dest1 = “D:/book1.xlsx”;
private static final String dest2 = “D:/book2.xlsx”;

public static void main(String… args) {
try {
System.out.println(“Test 1”);
Workbook workbook = new Workbook(source);
Worksheet worksheet = workbook.getWorksheets().get(“sheet”);
insertLine(worksheet);
Chart chart = worksheet.getCharts().get(“chart”);
Series series = chart.getNSeries().get(0);
ChartPointCollection points = series.getPoints();
@SuppressWarnings(“unchecked”)
Iterator pointIterator = points.iterator();
while (pointIterator.hasNext()) {
ChartPoint point = pointIterator.next();
DataLabels dataLabels = point.getDataLabels();
dataLabels.setAutoText(false);
String text = dataLabels.getText();
System.out.println(“old label : " + text);
text = translate(text);
System.out.println(“new label : " + text);
dataLabels.setText(text);
}
chart.calculate();
workbook.save(dest1);
}
catch (Exception e) {
e.printStackTrace();
}
try {
System.out.println(”\nTest 2”);
Workbook workbook = new Workbook(source);
Worksheet worksheet = workbook.getWorksheets().get(“sheet”);
insertLine(worksheet);
Chart chart = worksheet.getCharts().get(“chart”);
Series series = chart.getNSeries().get(0);
String text = series.getXValues();
System.out.println(“old xvalues : " + text);
text = translate(text);
System.out.println(“new xvalues : " + text);
series.setXValues(text);
chart.calculate();
workbook.save(dest2);
}
catch (Exception e) {
e.printStackTrace();
}
}

private static void insertLine(Worksheet worksheet) {
Cells cells = worksheet.getCells();
cells.insertRow(3);
cells.get(3, 0).setValue(10);
cells.get(3, 1).setValue(20);
}

private static String translate(String text) {
if (text != null) {
text = text.replace(”${key1}”, “key1”);
text = text.replace(”${key2}", “key2”);
}
return text;
}
}

1)
The first method only detects the manually changed label and modifications have no impact on the output chart.
By clicking on the label editor button mentioned in previous posts, the ${key2} is re-applied instead of the manual ${key2}

The second method has changed properly the text of the first label but not the second.
By clicking on the label editor button mentioned in previous posts, the modified text is applied on the second label too.

2)
In both cases, the values and percentage of the labels do not reflect the increase due the new added row, as if the range reference had been replaced by the value before having added the new data row.

babar.raza · November 20, 2015, 7:01am

Hi Sébastien,

Thank you for the code snippet.

Please be kind enough to share your desired results in spreadsheet format (that you can manually create using Excel application) so we could focus on achieving the same with Aspose.Cells APIs. Thank you for your cooperation.

centile · November 20, 2015, 7:43am

babar.raza:

Please be kind enough to share your desired results in spreadsheet format (that you can manually create using Excel application) so we could focus on achieving the same with Aspose.Cells APIs.

Desired result attached.

babar.raza · November 20, 2015, 10:18am

Hi again,

Thank you for the desired results.

Unfortunately, I was not able to get the desired results because for some reason only one of the Data Label is getting processed. I have logged this incident in our bug tracking system as CELLSJAVA-41582 for further investigation. Please spare us little time to properly analyze the scenario and get back with update in this regard.

We are sorry for the inconvenience caused.

shakeel.faiz · December 6, 2015, 9:11pm

Hi,

Thanks for using Aspose.Cells.

This is to inform you
that we have fixed your issue CELLSJAVA-41582 now. We will soon provide the fix after
performing QA and including other enhancements and fixes.

shakeel.faiz · December 8, 2015, 2:27am

Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for Java v8.6.2.4 and let us know your feedback.

Please try the new fix using the following code. For making the label change accordingly by xValues, please must set the label automatic.

String source = “D:\Aspose\User\1105\book.xlsx”;

String test1 = “D:\Aspose\User\1105\test1.xlsx”;

System.out.println(“Test 1”);

Workbook workbook = new Workbook(source);

Worksheet worksheet = workbook.getWorksheets().get(“sheet”);

Chart chart = worksheet.getCharts().get(“chart”);

Series series = chart.getNSeries().get(0);

String text = series.getXValues();

System.out.println("old xvalues : " + text);

text = translate(text);

System.out.println("new xvalues : " + text);

series.setXValues(text);

series.getDataLabels().setAutoText(true);

insertLine(worksheet);

workbook.save(test1);

aspose.notifier · December 24, 2015, 12:01am

The issues you have found earlier (filed as CELLSJAVA-41582) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.