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