Label on Column 3D stacked

Hi everyone,


I’ve created a Column 3D stacked where i force labels. When i delete 1 stacked column by deleting the data that enable me to draw the chart, my whole chart change his look.

I attached 2 screenshots one before deleting (th column called SRE C), one after.

Is there a way to fix it ?
Looking forward
Benoit

Hi Benoit,


Thank you for contacting Aspose support.

Please try calling the Chart.calculate method after removing the desired data and before saving the spreadsheet back. Please also try the case against the latest version of Aspose.Cells for Java 8.3.1.2. In case the problem persists, please provide us the input spreadsheet along with the code snippet to remove the data (as per your requirement). We will thoroughly check this scenario on our end to assist you further in this regard.

Hi,


thank you very much and sorry to answer so long after your post.

The thing is, i delete the column on my own (not the automated way), after my excel file is generated, so java is no longer in use at that moment. I was thinking about an option to put upon label or stuff like that to keep them at the right place. Do you have any idea ?

looking forward
Benoit

Hi Benoit,


Thank you for contacting back.

I believe what you wish to achieve is to delete a column (that is being used in the chart as data source) but keep the chart as it is. I am afraid, this will not be possible at all because the chart will be refreshed and the series based on the removed column will be removed too. If I have misunderstood your scenario then please explain your scenario further, and/or share a sample spreadsheet that could show your requirement.

Hi,


That is not really what i meant. Let me explain it again. To understand me better, please look at the 2 pictures i’ve posted on the first message of this topic.

1/ On the 1st picture you have 5 columns on the chart, corresponding to 5 lines of data :
  • SRE B : 2 values 100% & 100%
  • SRE B : 0 value
  • SRE C : 1 value 100%
  • SRE D : 0 value
  • SRE E : 3 values 33% & 100% & 50%

2/ I delete the line "SRE C"

3/ The chart contains now 4 columns (that’s what i want) BUT now the column SRE E do not have any label on its column


Is it clearer now ?

Hi Benoit,


Thank you for the elaboration. I now have much better understanding of your presented scenario. What I understand is that chart does not show the data labels on the existing bar;series after removing another series from the chart. I would request you to please post your sample spreadsheet and source code lines that could replicate the problem on our end. Based on the above requested information, we will guide you further how to tackle this situation.

The source for the chart is on the 2nd sheet named “Données”, on the line 29 to 34.


The code that allows me to do so is :

chartIndex = charts.add(ChartType.COLUMN_3_D_STACKED, 2, 1, 21, 6);
chart = charts.get(chartIndex);

//Change TickLabels position from horizontal position to vertical one
chart.getCategoryAxis().getTickLabels().setRotationAngle(45);

//Setting TickLabels font size to 7
chart.getCategoryAxis().getTickLabels().getFont().setSize(7);

serieses = chart.getNSeries();

int ligneFinTabPourc = 34;
int ligneDebTabPourcData = 30;
String rangeValue = “Données!H”+ligneDebTabPourcData+":H" + ligneFinTabPourc;
serieIndex = serieses.add(rangeValue, true);
serie = serieses.get(serieIndex);
serie.getBorder().setVisible(false);
serie.setName(“Sous recommandation importance Majeur”);
serie.getDataLabels().setShowValue(false);
serie.getDataLabels().setShowPercentage(true);
serie.getArea().setForegroundColor(Color.getRed());

rangeValue = “Données!I”+ligneDebTabPourcData+":I" + ligneFinTabPourc;
serieIndex = serieses.add(rangeValue, true);
serie = serieses.get(serieIndex);
serie.getBorder().setVisible(false);
serie.setName(“Sous recommandation importance Haute”);
serie.getDataLabels().setShowValue(false);
serie.getDataLabels().setShowPercentage(true);
serie.getArea().setForegroundColor(Color.getOrange());

rangeValue = “Données!J”+ligneDebTabPourcData+":J" + ligneFinTabPourc;
serieIndex = serieses.add(rangeValue, true);
serie = serieses.get(serieIndex);
serie.getBorder().setVisible(false);
serie.setName(“Sous recommandation importance Moyenne”);
serie.getDataLabels().setShowValue(false);
serie.getDataLabels().setShowPercentage(true);
serie.getArea().setForegroundColor(Color.fromArgb(0, 176, 240));

rangeValue = “Données!K”+ligneDebTabPourcData+":K" + ligneFinTabPourc;
serieIndex = serieses.add(rangeValue, true);
serie = serieses.get(serieIndex);
serie.getBorder().setVisible(false);
serie.setName(“Sous recommandation importance Faible”);
serie.getDataLabels().setShowValue(false);
serie.getDataLabels().setShowPercentage(true);
serie.getArea().setForegroundColor(Color.fromArgb(146, 208, 80));

rangeValue = “Données!G”+ligneDebTabPourcData+":G" + ligneFinTabPourc;
serieIndex = serieses.add(rangeValue, true);
serie = serieses.get(serieIndex);
serie.getBorder().setVisible(false);
serie.setName(“Mission”);
serie.getDataLabels().setShowValue(true);
serie.getDataLabels().setShowPercentage(true);
serie.getDataLabels().getFont().setBold(true);
serie.getDataLabels().getFont().setSize(8);
serie.getDataLabels().getBorder().setVisible(false);
serie.getArea().setForegroundColor(Color.getTransparent());
serie.getArea().setBackgroundColor(Color.getTransparent());
serie.getArea().getFillFormat().setType(FillType.NONE);
serie.getBorder().setVisible(false);

rangeValue = “Données!A”+ligneDebTabPourcData+":A" + ligneFinTabPourc;
serieses.setCategoryData(rangeValue);

//permet d’évaluer les formules de la feuille excel pour pouvoir les afficher en label du graphe
workbook.calculateFormula();
//affichage des labels sauf la dernière “pile/stack”
DataLabels datalabels;
for (int i = 0; i < chart.getNSeries().getCount()-1; i++){
for (int j = 0 ; j < chart.getNSeries().get(i).getPoints().getCount() ; j++){
String temp = cells2.get((ligneDebTabPourcData+j-1),(i+2)).getStringValue();
if (!temp.equals(“0%”)){ //hidden labels at 0%
//Get the data labels in the data series of the first data point.
datalabels = chart.getNSeries().get(i).getPoints().get(j).getDataLabels();

//Change the text of the label. (ATTENTION cell A1 = 0,0)
datalabels.setText(temp);
datalabels.getFont().setColor(Color.getWhite());
datalabels.getFont().setBold(true);
datalabels.getFont().setSize(8);

//allow the display of the value
datalabels.setShowValue(true);
}
}
}


//Format color & background & …
chart.getPlotArea().getArea().setForegroundColor(Color.getWhite());
chart.getPlotArea().getBorder().setVisible(false);
chart.getChartArea().getArea().setForegroundColor(Color.getWhite());
chart.getWalls().getBorder().setVisible(false);
chart.getWalls().setFormatting(FormattingType.NONE);
chart.getFloor().setFormatting(FormattingType.NONE);
chart.getFloor().getBorder().setVisible(false);
chart.getValueAxis().getMajorGridLines().setVisible(false);
//max de l’axe à 100%
// chart.getPlotArea().getBorder().setAutoScaling(true);
chart.getValueAxis().setMaxValue(new Integer(1));
chart.setShowLegend(false);
chart.setShowDataTable(false);


Thank you for the help !

Hi Benoit,


Thank you for providing the sample code and spreadsheet.

I have evaluated your presented scenario and we are able to replicate the problem on our end. We have used the latest version of Aspose.Cells for Java 8.3.2.3 to create a new chart based on your provided code snippet, and then manually deleted the row 32 from worksheet Données, it deletes the series SRE C from the chart, that is expected behavior. However, this action also removes the data labels from the series SRE E, that is not expected. We have logged this incident in our bug tracking system under the ticket CELLSJAVA-41199 for further investigation. Please spare us little time to properly analyze the problem cause, and to provide the fix (if applicable). In the meanwhile, we will keep you posted with updates in this regard.

Hi,

Thanks for using Aspose.Cells.

Please refer to the attachment in this post.

We are afraid Aspose.Cells has no option or property to prevent the data labels disappear. That’s Excel behavior. We think this is Excel’s bug. Data is deleted. But Excel can’t refresh the data labels.

In your file, as the labels(disappear) are customized text, the excel file has an index to point to label. When delete the data, index changes, but the index of label does not change too.

Hi again,


Thanks for your answer.

I made some tests that help me understand your answer.

Do you have any idea of how i can go through this problem ?
An MS EXCEL 2013, there is an option on label called “Value from cells” where you can select a range of value. Moreover you have an option called “Show leader lines” maybe there is a clue around that ?

Thanks
Benoit

Hi Benoit,

Thanks for your posting and using Aspose.Cells.

We have logged your comment in our database against this issue. We will look into it and advise you if there is any possible solution for your problem. Please spare us some time. Once, there is some news for you, we will let you know asap.