Wrong image generated while implementing WaterFall Chart using StackedBar chart

Aspose.Cells Product Family
1

Hi,

Please run the following sample code to gnerate WaterFall Chart using StackedBar chart type. The chart is getting properly generated in excel, But the toImage generated image is not correct.

1. It seems only a portion of the chart is shown in the image.
2. Axis Label orientation is not correct.
3. DataLabels are not present in the image

This is using version 2.2.1.9. Latest version gives exception, which is already posted.

Thanks
Muhammed

package test.aspose;

import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;

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.DataLabels;
import com.aspose.cells.FileFormatType;
import com.aspose.cells.ImageFormat;
import com.aspose.cells.ImageOptions;
import com.aspose.cells.LabelPositionType;
import com.aspose.cells.PatternFill;
import com.aspose.cells.TickLabelPositionType;
import com.aspose.cells.TickMarkerType;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;

public class DataLabelToImage {

public static void main(String args[]) {

try {

String categoryNames[] = {
"Avg Unit Sales per Store Selling",
"Avg Volume Sales per Store Selling",
"Avg Value Sales per Store Selling",
"ACV_CWD Wtd Unit Rate of Sale",
"Avg Unit Price",
"ACV_CWD Wtd Volume Rate of Sale",
"ACV_CWD Wtd Value Rate of Sale",
"Avg Volume Price"
};

double values[] = {
213.4464945,
-264.9483395,
-347.0787208,
210.9120541,
-1,
-261.801968,
-342.9569496,
0.805658057
};

int chartDataStartRow = 0;
int chartDataStartColumn = 0;
int rowIndex = chartDataStartRow;

System.out.println("Aspose version : " + CellsHelper.getReleaseVersion());

Workbook wb = new Workbook();
Worksheet dataSheet = wb.getWorksheets().addSheet();
String dataSheetName = "DataSheet";
dataSheet.setName(dataSheetName);

Worksheet chartSheet = wb.getWorksheets().addSheet();
chartSheet.setName("ChartSheet");
int chartSheetIdx = chartSheet.getIndex();

Chart chart = chartSheet.getCharts().addChart(ChartType.COLUMN_STACKED, 0, 0, 0, 0, 832, 476);

String colTitle[] = {"", "Value", "*** Value", "endpoints", "blank neg", "red neg", "grn neg", "blank pos", "red pos", "grn pos", "Y label"};
for (int i = 0; i < colTitle.length; i++) {
dataSheet.getCells().getCell(rowIndex, chartDataStartColumn + i).setValue(colTitle[i]);
}

//Data Part
int binCount = categoryNames.length;
for (int i = 0; i < categoryNames.length; i++) {
rowIndex++;

String label = categoryNames[i];
dataSheet.getCells().getCell(rowIndex, chartDataStartColumn).setValue(label);

double value = values[i];
if (Double.isNaN(value)) {
value = 0;
}

dataSheet.getCells().getCell(rowIndex, chartDataStartColumn + 1).setValue(value);

if (i == 0 || i == 3 || i == 7) {//Make it BasePoints
dataSheet.getCells().getCell(rowIndex, chartDataStartColumn + 3).setValue(value);
}
}

double currentRowCumVal = 0.0;
double cumulativeVals[] = new double[rowIndex];

String colA = CellsHelper.convertColumnIndexToName(chartDataStartColumn + 0);
String colB = CellsHelper.convertColumnIndexToName(chartDataStartColumn + 1);
String colC = CellsHelper.convertColumnIndexToName(chartDataStartColumn + 2);
String colD = CellsHelper.convertColumnIndexToName(chartDataStartColumn + 3);
String colE = CellsHelper.convertColumnIndexToName(chartDataStartColumn + 4);
String colF = CellsHelper.convertColumnIndexToName(chartDataStartColumn + 5);
String colG = CellsHelper.convertColumnIndexToName(chartDataStartColumn + 6);
String colH = CellsHelper.convertColumnIndexToName(chartDataStartColumn + 7);
String colI = CellsHelper.convertColumnIndexToName(chartDataStartColumn + 8);
String colJ = CellsHelper.convertColumnIndexToName(chartDataStartColumn + 9);

//Add Formula
int startRowIndex = chartDataStartRow + 1;
for (int i = chartDataStartRow + 1; i <= rowIndex; i++) {
//Value is column D, will be empty for adjustments
Double initialVal = dataSheet.getCells().getCell(i, chartDataStartColumn + 3).getDoubleValue();

if (initialVal != null) {
startRowIndex = i;
currentRowCumVal = 0.0;
}

currentRowCumVal = currentRowCumVal + dataSheet.getCells().getCell(i, chartDataStartColumn + 1).getDoubleValue().doubleValue();
cumulativeVals[i - 1] = currentRowCumVal;

//C2: =SUM(B2:B$2)
//String formulaC = "=SUM(B2:B" + (i + 1) +")";
String formulaC = "=SUM(" + colB + (startRowIndex + 1) + ":" + colB + (i + 1) +")";
dataSheet.getCells().getCell(i, chartDataStartColumn + 2).setFormula(formulaC);

if (initialVal == null || initialVal.doubleValue() == 0.0) {//adjustment point
if (i > 1) {
//E3: =IF(AND(C3<0,C2<0),MAX(C2:C3),0)
String formulaE = "=IF(AND(" + colC + (i + 1) + "<0," + colC + (i) +"<0),MAX(" + colC + (i) + ":" + colC + (i + 1) + "),0)";
dataSheet.getCells().getCell(i, chartDataStartColumn + 4).setFormula(formulaE);
}
//F3: =IF(AND(C3<0,B3<0),MAX(B3,C3),0)
String formulaF = "=IF(AND(" + colC + (i + 1) + "<0," + colB + (i + 1) +"<0),MAX(" + colB + (i + 1) + "," + colC + (i + 1) + "),0)";
dataSheet.getCells().getCell(i, chartDataStartColumn + 5).setFormula(formulaF);

if (i > 1) {
//G3: =IF(AND(C2<0,B3>0),MAX(-B3,C2),0)
String formulaG = "=IF(AND(" + colC + (i) + "<0," + colB + (i + 1) +">0),MAX(-" + colB + (i + 1) + "," + colC + (i) + "),0)";
dataSheet.getCells().getCell(i, chartDataStartColumn + 6).setFormula(formulaG);

//H3: =IF(AND(C3>0,C2>0),MIN(C2:C3),0)
String formulaH = "=IF(AND(" + colC + (i + 1) + ">0," + colC + (i) +">0),MIN(" + colC + (i) + ":" + colC + (i + 1) + "),0)";
dataSheet.getCells().getCell(i, chartDataStartColumn + 7).setFormula(formulaH);

//I3: =IF(AND(C2>0,B3<0),MIN(-B3,C2),0)
String formulaI = "=IF(AND(" + colC + (i) + ">0," + colB + (i + 1) +"<0),MIN(-" + colB + (i + 1) + "," + colC + (i) + "),0)";
dataSheet.getCells().getCell(i, chartDataStartColumn + 8).setFormula(formulaI);
}
//J3: =IF(AND(C3>0,B3>0),MIN(B3,C3),0)
String formulaJ = "=IF(AND(" + colC + (i + 1) + ">0," + colB + (i + 1) +">0),MIN(" + colB + (i + 1) + "," + colC + (i + 1) + "),0)";
dataSheet.getCells().getCell(i, chartDataStartColumn + 9).setFormula(formulaJ);

}
//K2: =MAX(D2,SUM(H2:J2))
String formulaK = "=MAX(" + colD + (i) + ",SUM(" + colH + (i) +":" + colJ + (i) + "))";
dataSheet.getCells().getCell(i, chartDataStartColumn + 10).setFormula(formulaC);
}

String catRef = "\'" + dataSheet.getName() + "\'!" + CellsHelper.convertCellIndexToName(chartDataStartRow + 1, chartDataStartColumn) + ":" + CellsHelper.convertCellIndexToName(rowIndex, chartDataStartColumn);
String dataRef = "\'" + dataSheet.getName() + "'!" + CellsHelper.convertCellIndexToName(chartDataStartRow + 1, chartDataStartColumn + 3) + ":" + CellsHelper.convertCellIndexToName(rowIndex, chartDataStartColumn + 9);

chart.getNSeries().add(catRef + "," + dataRef, true);
chart.getNSeries().setCategoryData(catRef);

for (int i = 0; i < chart.getNSeries().size(); i++) {
ASeries series = chart.getNSeries().get(i);
series.setName(dataSheet.getCells().getCell(chartDataStartRow, chartDataStartColumn + i + 3).getStringValue());
}



//Configure series.

PatternFill fill = new PatternFill();
fill.setBackgroundColor(Color.GREEN);
fill.setForegroundColor(Color.GREEN);
PatternFill positiveFill = fill;

fill = new PatternFill();
fill.setBackgroundColor(Color.RED);
fill.setForegroundColor(Color.RED);
PatternFill negativeFill = fill;

fill = new PatternFill();
fill.setBackgroundColor(Color.BLUE);
fill.setForegroundColor(Color.BLUE);
PatternFill basePointFill = fill;

chart.getNSeries().get(1).getArea().setVisible(false);
chart.getNSeries().get(1).getBorder().setVisible(false);

//Hide blank pos
chart.getNSeries().get(4).getArea().setVisible(false);
chart.getNSeries().get(4).getBorder().setVisible(false);

//Green fill - grn neg and grn pos
chart.getNSeries().get(3).getArea().setFill(positiveFill);
chart.getNSeries().get(6).getArea().setFill(positiveFill);

//Red fill - red neg and red pos
chart.getNSeries().get(2).getArea().setFill(negativeFill);
chart.getNSeries().get(5).getArea().setFill(negativeFill);

//Blue fill - endpoints
chart.getNSeries().get(0).getArea().setFill(basePointFill);

//delete all legend entries except positive adjustments
chart.getLegend().getLegendEntries().getLegendEntry(0).setDeleted(true);
chart.getLegend().getLegendEntries().getLegendEntry(1).setDeleted(true);
chart.getLegend().getLegendEntries().getLegendEntry(2).setDeleted(true);
chart.getLegend().getLegendEntries().getLegendEntry(3).setDeleted(true);
chart.getLegend().getLegendEntries().getLegendEntry(4).setDeleted(true);
chart.getLegend().getLegendEntries().getLegendEntry(5).setDeleted(true);

chart.getLegend().getLegendEntries().getLegendEntry(6).setDeleted(true);
chart.setLegendShown(false);

boolean displaySeriesBorder = true;
boolean displayMarkers = true;
boolean displayPercentage = true;
int rotation = 90;

chart.getCategoryAxis().setMinorTickMark(TickMarkerType.NONE);
chart.getCategoryAxis().setTickLabelPosition(TickLabelPositionType.LOW);
if (displayPercentage) {
chart.getValueAxis().setNumberFormat("0%");
}

//Data Label
for (int rIndex = 1; rIndex <= binCount; rIndex++) {
Double initialVal = dataSheet.getCells().getCell(chartDataStartRow + rIndex, chartDataStartColumn + 3).getDoubleValue();
if (initialVal != null) {//Base Point
Double dataLabelVal = dataSheet.getCells().getCell(chartDataStartRow + rIndex, chartDataStartColumn + 1).getDoubleValue();
ASeries series = chart.getNSeries().get(0);
DataLabels dataLabels = series.getChartPoints().getChartPoint((rIndex - 1)).getDataLabels();
dataLabels.setValueShown(true);
dataLabels.setLabelPosition(LabelPositionType.INSIDE_END);
dataLabels.setRotation(rotation);
if (displayPercentage) {
dataLabels.setNumberFormat("#,##0%");
}
else {
dataLabels.setNumberFormat("#,##0.00");
//dataLabels.setText(String.valueOf(dataLabelVal));
}
if (displaySeriesBorder) {
//if (series.getType() != ChartType.LINE && chart.getType() != ChartType.LINE && series.getType() != 40) {
series.getBorder().setVisible(true);
//}
}
}
else {
Double dataVal = dataSheet.getCells().getCell(chartDataStartRow + rIndex, chartDataStartColumn + 1).getDoubleValue();
if (cumulativeVals[rIndex - 1] >= 0) {
ASeries series = chart.getNSeries().get(6);
DataLabels dataLabels = series.getChartPoints().getChartPoint((rIndex - 1)).getDataLabels();
dataLabels.setValueShown(true);
//dataLabels.setText(String.valueOf(dataVal));
dataLabels.setLabelPosition(LabelPositionType.INSIDE_BASE);
dataLabels.setRotation(rotation);
if (displayPercentage) {
//String labelVal = percentFormat.format(dataVal.doubleValue());
//dataLabels.setText(String.valueOf(labelVal));
//dataLabels.setValueShown(false);
//dataLabels.setPercentageShown(true);
dataLabels.setNumberFormat("#,##0%");
}
else {
dataLabels.setNumberFormat("#,##0.00");
//dataLabels.setText(String.valueOf(dataVal));
}
if (displaySeriesBorder) {
//if (series.getType() != ChartType.LINE && chart.getType() != ChartType.LINE && series.getType() != 40) {
series.getBorder().setVisible(true);
//}
}
}
else {
ASeries series = chart.getNSeries().get(2);
DataLabels dataLabels = series.getChartPoints().getChartPoint((rIndex - 1)).getDataLabels();
/*Double cumVal = dataSheet.getCells().getCell(i, 2).getDoubleValue();
if (cumVal.doubleValue() > 0) {
series = chart.getNSeries().get(5);
}*/
dataLabels.setValueShown(true);
//dataLabels.setText(String.valueOf(dataVal));
dataLabels.setLabelPosition(LabelPositionType.INSIDE_END);
dataLabels.setRotation(rotation);
if (displayPercentage) {
//String labelVal = percentFormat.format(dataVal.doubleValue());
//dataLabels.setText(String.valueOf(labelVal));
//dataLabels.setValueShown(false);
//dataLabels.setPercentageShown(true);
dataLabels.setNumberFormat("#,##0%");
}
else {
dataLabels.setNumberFormat("#,##0.00");
//dataLabels.setText(String.valueOf(dataVal));
}
if (displaySeriesBorder) {
//if (series.getType() != ChartType.LINE && chart.getType() != ChartType.LINE && series.getType() != 40) {
series.getBorder().setVisible(true);
//}
}
}
}
}

wb.getWorksheets().setActiveSheet(chartSheetIdx);
wb.setOleSize(0, 27, 0, 13);

// 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);


System.out.println("Waterfall successfully completed!!!");

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

}

2

Hi,

Thanks for the sample code and screen shot.

I have found the issue
after an initial test using your sample code. I have already logged your issue
into our issue tracking system with an id: CELLSJAVA-19832. We will figure your issue out soon.

Thank you.

3
<!–[if gte mso 10]> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman","serif";}

<![endif]–>

Hi,

Please try the attached version. For the lost series issue in the genertaed image, please call Workbook.calculateFormula() before creating images. Also we have made some improvements for axis labels, datalabels and so on for chart2image feature in the new fix(attached).

Thank you.

4

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

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