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

Free Support Forum - aspose.com

java.lang.IllegalArgumentException: Cannot set font as null: while creating image from WaterFall chart implementation using StackedBar

Hi,
When I run the following code using Aspose.Cells 2.4.0.0, I am getting the following exception. Its running without exception with Aspose.Cells 2.2.1.9 version.

Aspose version : 2.4.0.0
java.lang.IllegalArgumentException: Cannot set font as null.
at com.aspose.a.j.a(Unknown Source)
at com.aspose.a.b.f.a(Unknown Source)
at com.aspose.a.b.f.a(Unknown Source)
at com.aspose.a.A.a(Unknown Source)
at com.aspose.a.a.b(Unknown Source)
at com.aspose.a.a.a(Unknown Source)
at com.aspose.a.a.j(Unknown Source)
at com.aspose.a.a.a(Unknown Source)
at com.aspose.a.c.a.a(Unknown Source)
at com.aspose.cells.Chart.toImage(Unknown Source)
at test.aspose.DataLabelToImage.main(DataLabelToImage.java:321)

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

}

Hi,

Thanks for the sample code.

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

Thank you.

<!–[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. We have fixed the issue of IllegalArgumentException.

Thank you.

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.