Free Support Forum - aspose.com

Chart.toImage - Series as Line - Line is drwan behind bars & other issues

Hi,
Please find the sample below and verify the following issues.

Issues in the generated image:-
1) Line is drwan behind bars,
2) chart area height and width are not matching,
3) X axis tick label placement is not matching,
4) Legend box width and height are not matching,
5) Legend box entry ordering is not matching(Unit Sales first in image),
6) Line in image looks bolded

Thanks & Regards
Muhammed


import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.lang.reflect.Array;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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 SeriesAsLine {

public static void main(String args[]) {

try {
String[] colTitle = new String[] { "Rinse Conditioners",
"Main Wash",
"Deodorants",
"Tumble Drying Enhancer",
"Iron Enhancer",
"Special Wash",
"Wash Treatment"
};

//Array of cell data
double[][] data = new double[][] { {17060100, 30207780, 0, 9006.5, 877104.1, 481208, 1652079},
{20892900, 72572620, 0, 433483.7, 1010714, 1276822, 6848673},
{20892900, 72572620, 0, 433483.7, 1010714, 1276822, 6848673},
{14008380, 23011910, 0, 231134.1, 933056.1, 733034, 2866746}
};

Workbook wb = new Workbook();
Worksheet dataSheet = wb.getWorksheets().addSheet();
wb.getPalette().setColor(0, new Color(0x4B, 0x30, 0xF4));
wb.getPalette().setColor(1, new Color(0x42, 0xDF, 0xF4));
wb.getPalette().setColor(2, new Color(0x00, 0xF4, 0x65));
wb.getPalette().setColor(3, new Color(0xF9, 0x04, 0x21));

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

int rowIndex = 0;
for (int i = 0; i < colTitle.length; i++) {
dataSheet.getCells().getCell(rowIndex, i).setValue(colTitle[i]);
}

for (int i = 0; i < data.length; i++) {
for (int j = 0; j < data[i].length; j++) {
dataSheet.getCells().getCell(i + 1, j).setValue(data[i][j]);
}
}

//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_CLUSTERED, 0, 0, 1, 1, 500, 400);

Map legendLabelMap = new HashMap();
legendLabelMap.put("0", "Unit Sales");
legendLabelMap.put("1", "Value Sales");
legendLabelMap.put("2", "Volume Sales");
legendLabelMap.put("3", "Volume per $MM ACV");

for (int i = 1; i <= data.length; i++) {
String lid = "" + (i - 1);

String dataRef = dataSheet.getName() + "!" + CellsHelper.convertCellIndexToName(i, 0) + ":" + CellsHelper.convertCellIndexToName(i, colTitle.length - 1);
int seriesIndex = chart.getNSeries().add(dataRef, false);
ASeries series = chart.getNSeries().get(seriesIndex);
series.setGapWidth(25);
if (legendLabelMap.get(lid) != null) {
series.setName((String)legendLabelMap.get(lid));
}

PatternFill seriesFillObj = new PatternFill();
seriesFillObj.setForegroundColor(wb.getPalette().getColor(i - 1));
seriesFillObj.setBackgroundColor(wb.getPalette().getColor(i - 1));
series.getArea().setFill(seriesFillObj);
}

chart.getNSeries().get(0).setType(ChartType.LINE);

//Set X Axis Data
String categoryAreaRef = dataSheet.getName() + "!" + CellsHelper.convertCellIndexToName(0, 0) + ":" + CellsHelper.convertCellIndexToName(0, colTitle.length - 1);
chart.getNSeries().setCategoryData(categoryAreaRef);

chart.getCategoryAxis().getTitle().setText("X Axis Title");

chart.getValueAxis().getTitle().setText("Y Axis Title");
chart.getSecondValueAxis().getTitle().setText("Y1 Axis Title");

chart.getNSeries().setSecondCategoryData(categoryAreaRef);

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

wb.getWorksheets().setActiveSheet(chartSheetIdx);
wb.setOleSize(0, 29, 0, 12);

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

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("FirstSeriesAsLine successfully completed!!!" + CellsHelper.getReleaseVersion());

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

}

Hi,

Thanks for providing us the sample code and template files.

We have found all your mentioned issues after an initial test, we will figure it out soon.

We have logged it into our issue tracking system with an issue id: CELLSJAVA-15105.

Thank you.

Hi,

Please try the attached version.
We have fixed following issues:
1) line that be drawn behind bars
2) line in image looks bold
3) ordering of legend entries

For other issues, since these are more complex and we need more time to fix them.


Thank you.