Free Support Forum - aspose.com

Chart.toImage - DataLabels on invisible series are present in the image

Hi,

Please run the below sample code and see that Datalabels on invisible series are present in the image generated, where as actual chart doesn't show them.

Thanks
Muhammed


import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.lang.reflect.Array;

import com.aspose.cells.ASeries;
import com.aspose.cells.CellsHelper;
import com.aspose.cells.Chart;
import com.aspose.cells.ChartType;
import com.aspose.cells.Color;
import com.aspose.cells.FileFormatType;
import com.aspose.cells.ImageFormat;
import com.aspose.cells.ImageOptions;
import com.aspose.cells.PatternFill;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
import com.aspose.slides.OleObjectFrame;
import com.aspose.slides.Presentation;
import com.aspose.slides.Slide;

public class WaterfallToImage {

public static void main(String args[]) {

try {
System.out.println(CellsHelper.getReleaseVersion());

Workbook wb = new Workbook();
Worksheet dataSheet = wb.getWorksheets().addSheet();

String dataSheetName = "DataSheet";
dataSheet.setName(dataSheetName);

//Add a chart sheet
Worksheet chartSheet = wb.getWorksheets().addSheet();
chartSheet.setName("ChartSheet");
int chartSheetIdx = chartSheet.getIndex();

int colIndex = 0;
String rowTitle[] = {"", "Invisible", "Minus", "Plus", "Initial"};
for (int i = 0; i < rowTitle.length; i++) {
dataSheet.getCells().getCell(i, colIndex).setValue(rowTitle[i]);
}

//Second Column
colIndex++;
dataSheet.getCells().getCell(0, colIndex).setValue(" ");
for (int j = 1; j < 5; j++) {
dataSheet.getCells().getCell(j, colIndex).setValue(0.0);
}
colIndex++;

double[] dataPointList = {12278620, 15241270, 19965860, 19965860};
String label[] = {"Unit Sales", "Volume Sales", "Value Sales", "Volume per $MM ACV"};

for (int j = 0; j < dataPointList.length; j++) {
double value = dataPointList[j];
if (Double.isNaN(value)) {
value = 0;
}

dataSheet.getCells().getCell(0, colIndex).setValue(label[j]);


if (j == 0 || j == 3) {
dataSheet.getCells().getCell(4, colIndex).setValue(value);
}
else {
if (value >= 0) {
dataSheet.getCells().getCell(3, colIndex).setValue(value);
}
else {
dataSheet.getCells().getCell(2, colIndex).setValue(value);
}
}
colIndex++;
}

for (int j = 1; j < 5; j++) {
dataSheet.getCells().getCell(j, colIndex).setValue(0.0);
}

//Add the formula
for (int i = 2; i < (colIndex - 1); i++) {
Double initialVal = dataSheet.getCells().getCell(4, i).getDoubleValue();
if (initialVal == null || initialVal.doubleValue() == 0.0) {
//String formula = "=SUM(B" + i + ",E" + i + ":F" + i + ")-D" + (i + 1);
String prevColumn = CellsHelper.convertColumnIndexToName(i - 1);
String formula = "=SUM(" + prevColumn + "2," + prevColumn + "4:" + prevColumn + "5) - " + prevColumn + "3";
dataSheet.getCells().getCell(1, i).setFormula(formula);
}
}

String lastDataPointColumn = CellsHelper.convertColumnIndexToName(colIndex - 1);
String invisibleValFormula = "=MAX(C2:"+ lastDataPointColumn + "2)";
dataSheet.getCells().getCell(1, colIndex).setFormula(invisibleValFormula);
dataSheet.getCells().getCell(1, 1).setFormula(invisibleValFormula);

String dataRef = dataSheet.getName() + "!" + CellsHelper.convertCellIndexToName(1, 1) + ":" + CellsHelper.convertCellIndexToName(rowTitle.length - 1, colIndex);

Chart chart = chartSheet.getCharts().addChart(ChartType.COLUMN_STACKED, 0, 0, 1, 1, 600, 400);
chart.getNSeries().add(dataRef, false);

String lastColName = CellsHelper.convertColumnIndexToName(colIndex);
String categoryAreaRef = dataSheet.getName() + "!B1:" + lastColName + "1";
chart.getNSeries().setCategoryData(categoryAreaRef);

//Hide invisible series to make it waterfall
chart.getNSeries().get(0).getArea().setVisible(false);
chart.getNSeries().get(0).getBorder().setVisible(false);

chart.getNSeries().get(2).setName("2010");
//delete legend entry for invisible series
chart.getLegend().getLegendEntries().getLegendEntry(0).setDeleted(true);
chart.getLegend().getLegendEntries().getLegendEntry(1).setDeleted(true);
chart.getLegend().getLegendEntries().getLegendEntry(3).setDeleted(true);


for (int i = 0; i < chart.getNSeries().size(); i++) {
ASeries series = chart.getNSeries().get(i);
series.getDataLabels().setValueShown(true);
}

chart.getCategoryAxis().getMajorGridLines().setVisible(true);
chart.getValueAxis().getMajorGridLines().setVisible(true);

//Add a chart in ChartSheet with data series from DataSheet
int chartRows = 25, chartCols = 15;

wb.getWorksheets().setActiveSheet(chartSheetIdx);
wb.setOleSize(0, chartRows, 0, chartCols);

//Save the workbook to stream
ByteArrayOutputStream bout = new ByteArrayOutputStream();
wb.save(bout);
wb.save(new FileOutputStream("D:\\Temp\\output.xls"));

//Get Chart as image.
ImageOptions imgOpts = new ImageOptions();
imgOpts.setImageFormat(ImageFormat.PNG);
imgOpts.setFashion(FileFormatType.EXCEL2003);

chart.toImage(new FileOutputStream("D:\\Temp\\chart.png"), imgOpts);

Presentation pres = new Presentation();
Slide sld = pres.addEmptySlide();

int slideWidth = (int) pres.getSlideSize().getX() - 1500;

int slideHeight = (int) pres.getSlideSize().getY();
int x = 1500 / 2;
OleObjectFrame oof = sld.getShapes().addOleObjectFrame(x, 0, slideWidth, slideHeight, "Excel.Sheet.8", bout.toByteArray());

com.aspose.slides.Picture pic = new com.aspose.slides.Picture(pres, new FileInputStream("D:\\Temp\\chart.png"));
int picId = pres.getPictures().add(pic);
oof.setPictureId(picId);

//Write the presentation on disk
pres.write(new FileOutputStream("D:\\Temp\\output.ppt"));
System.out.println("WaterfallChartMaker successfully completed!!!");

}
catch (Exception e) {
e.printStackTrace();
}
}

}

Hi,

After an initial test, I found the issue, we will figure it out soon.
Your issue has been logged into our issue tracking system with an issue id: CELLSJAVA-15198.

Thank you.

Hi,

Please try the attached latest version of Aspose.Cells. We have fixed your mentioned issue.

Thank You & Best Regards,

working fine.
thanks
muhammed

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


This message was posted using Notification2Forum from Downloads module by aspose.notifier.