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();
}
}
}