We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Hidden series and deleted LegendEntries are present in the Chart.toImage generated image - implementing Waterfall Chart

Hi,

Please run the following sample, let me know how to resolve the following issues.
1. Generated image shows deleted legend entry in Legend box
2. Generated image shows invisible N series. Please match the image attached with the actual chart in excel sheet
3. Can I set Legend labels in a single statement? Please see the yello highlighted code. I am looking for a single line solution for this.

Source Code
import java.io.*;
import java.lang.reflect.Array;

import com.aspose.cells.*;
import com.aspose.slides.*;

public class WaterfallChartMaker {

public static void main(String args[]) {

try {
String[] cellsName = new String[] { "B2", "B3", "B4", "B5", "B6", "B7", "B8", "B9", "B10",
"C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9", "C10",
"D2", "D3", "D4", "D5", "D6", "D7", "D8", "D9", "D10",
"E2", "E3", "E4", "E5", "E6", "E7", "E8", "E9", "E10",
"F2", "F3", "F4", "F5", "F6", "F7", "F8", "F9", "F10"
};

//Array of cell data
int[] cellsValue = new int[] { 1000, 0, 10000, 9000, 7000, 6000, 6000, 0, 1000,
0, 0, 0, 0, 0, 0, 0, 8500, 0,
0, 0, 0, 3000, 2000, 1000, 0, 0, 0,
0, 0, 2000, 0, 0, 0, 2500, 0, 0,
0, 10000, 0, 0, 0, 0, 0, 0, 0
};

Workbook wb = new Workbook();
Worksheet dataSheet = wb.getWorksheets().addSheet();
wb.getPalette().setColor(55, Color.RED);
wb.getPalette().setColor(54, Color.GREEN);

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

dataSheet.getCells().getCell("A1").setValue("");
//dataSheet.getCells().getCell("B1").setValue("Invisible");
dataSheet.getCells().getCell("B1").setValue("");
dataSheet.getCells().getCell("C1").setValue("Final");
dataSheet.getCells().getCell("D1").setValue("Minus");
dataSheet.getCells().getCell("E1").setValue("Plus");
dataSheet.getCells().getCell("F1").setValue("Initial");

dataSheet.getCells().getCell("A2").setValue("");
dataSheet.getCells().getCell("A3").setValue("Initial");
dataSheet.getCells().getCell("A4").setValue("Alpha");
dataSheet.getCells().getCell("A5").setValue("Beta");
dataSheet.getCells().getCell("A6").setValue("Gamma");
dataSheet.getCells().getCell("A7").setValue("Delta");
dataSheet.getCells().getCell("A8").setValue("Epsilon");
dataSheet.getCells().getCell("A9").setValue("Final");
dataSheet.getCells().getCell("A10").setValue("");

int size = Array.getLength(cellsName);
for (int i = 0; i < size; i++) {
String cellName = cellsName[i];
int cellValue = cellsValue[i];
dataSheet.getCells().getCell(cellName).setValue(cellValue);
}

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

//Add a chart in ChartSheet with data series from DataSheet
int chartRows = 25, chartCols = 15;
Chart chart = chartSheet.getCharts().addChart(ChartType.COLUMN_STACKED, 0, 0, 1, 1, 700, 600);

chart.getNSeries().add(dataSheetName + "!B2:F10", true);

String categoryAreaRef = dataSheetName + "!A2:A10";
chart.getNSeries().setCategoryData(categoryAreaRef);

//Set Series Names to be used in the Legend and there respective colors
//Is there any simple way to do this...?
chart.getNSeries().get(0).setName((String)dataSheet.getCells().getCell("B1").getValue());
chart.getNSeries().get(1).setName((String)dataSheet.getCells().getCell("C1").getValue());
chart.getNSeries().get(2).setName((String)dataSheet.getCells().getCell("D1").getValue());
chart.getNSeries().get(3).setName((String)dataSheet.getCells().getCell("E1").getValue());
chart.getNSeries().get(4).setName((String)dataSheet.getCells().getCell("F1").getValue());

//chart.getNSeries().get(0).setGapWidth(0);

//Hide invisible series to make it waterfall
chart.getNSeries().get(0).getArea().setVisible(false);
chart.getNSeries().get(0).getBorder().setVisible(false);
//delete legend entry for invisible series
chart.getLegend().getLegendEntries().getLegendEntry(0).setDeleted(true);

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("successfully completed!!!");

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

}


Thanks & Regards

Muhammed

Hi,

Thanks for providing us sample code and template files.

We found both your mentioned issues (1 & 2) after an initial test, we will figure it out and get back to you soon.

Your issue has been logged into our issue tracking system with an issue id: CELLSJAVA-14166.

Thank you.

Hi,


We will provide a new fix for the issue of LegendEntry and invisible Series (1 & 2) later because the developer of this portion is on leave. But, please try this new fix (v2.1.1.26) attached instead of v2.1.1.25 because there is a known bug in the fix 2.1.1.25 (we provided).


3) For your third question, I am afraid there is no better way to set names for all serieses in one line currently. Maybe you can use a loop to set it like:

for(int i=0; i<5; i++)
{
chart.getNSeries().get(i).setName((String)dataSheet.getCells().getCell(0, i+1).getValue());
}



Thank you.

Hi,

Please try the attached version. We have fixed the issue of deleted legend entry and invisible series.

Thank you.

working fine.

Thanks

Muhammed

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


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