Hi,
Please run the following sample and see that
1) When chart size is small, chart in the image is not rendered properly.
2) Increase chart size to bigger values say - Chart chart = chartSheet.getCharts().addChart(ChartType.COLUMN_CLUSTERED, 0, 0, 1, 1, 624, 383);
Now see that category axis tick labels are not present in the image. instead, 1,2,3 etc... are shown
Thanks
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.Iterator;
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.Fill;
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 AxisTitleOverlap {
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, 416, 255);
StringBuffer emptySeries = new StringBuffer("{");
for (int i = 0; i < colTitle.length; i++) {
emptySeries.append("0.0");
//emptySeries.append("5000000.0");
if (i != (colTitle.length - 1)) {
emptySeries.append(",");
}
}
emptySeries.append("}");
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");
List secondAxisSeriesList = new ArrayList();
secondAxisSeriesList.add("3");
secondAxisSeriesList.add("0");
List plotAsLineSeriesList = new ArrayList();
plotAsLineSeriesList.add("0");
//plotAsLineSeriesList.add("0");
Map plotAsLineSeries = new HashMap();
for (int i = 1; i <= data.length; i++) {
String lid = "" + (i - 1);
if (!secondAxisSeriesList.contains(lid)) {
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);
if (plotAsLineSeriesList.contains(lid)) {
//series.setType(ChartType.LINE);
//series.getBorder().setFill(seriesFillObj);
plotAsLineSeries.put(series, seriesFillObj);
}
}
else {
int emptyIndex = chart.getNSeries().add(emptySeries.toString(), false);
chart.getLegend().getLegendEntries().getLegendEntry(emptyIndex).setDeleted(true);
if (plotAsLineSeriesList.contains(lid)) {
//chart.getNSeries().get(emptyIndex).setType(ChartType.LINE);
plotAsLineSeries.put(chart.getNSeries().get(emptyIndex), null);
}
}
}
if (secondAxisSeriesList.size() > 0) {
for (int i = 1; i <= data.length; i++) {
String lid = "" + (i - 1);
if (secondAxisSeriesList.contains(lid)) {
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.setPlotOnSecondAxis(true);
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);
if (plotAsLineSeriesList.contains(lid)) {
//series.setType(ChartType.LINE);
//series.getBorder().setFill(seriesFillObj);
plotAsLineSeries.put(series, seriesFillObj);
}
}
else {
int emptyIndex = chart.getNSeries().add(emptySeries.toString(), false);
chart.getNSeries().get(emptyIndex).setPlotOnSecondAxis(true);
chart.getLegend().getLegendEntries().getLegendEntry(emptyIndex).setDeleted(true);
if (plotAsLineSeriesList.contains(lid)) {
//chart.getNSeries().get(emptyIndex).setType(ChartType.LINE);
plotAsLineSeries.put(chart.getNSeries().get(emptyIndex), null);
}
}
}
}
Iterator seriesIt = plotAsLineSeries.keySet().iterator();
while (seriesIt.hasNext()) {
ASeries series = (ASeries)seriesIt.next();
series.setType(ChartType.LINE);
if (plotAsLineSeries.get(series) != null) {
series.getBorder().setFill((Fill)plotAsLineSeries.get(series));
}
}
//Set X Axis Data
String categoryAreaRef = dataSheet.getName() + "!" + CellsHelper.convertCellIndexToName(0, 0) + ":" + CellsHelper.convertCellIndexToName(0, colTitle.length - 1);
chart.getNSeries().setCategoryData(categoryAreaRef);
if (secondAxisSeriesList.size() > 0) {
//Set second category axis.
chart.getNSeries().setSecondCategoryData(categoryAreaRef);
chart.getSecondValueAxis().setVisible(true);
chart.getSecondValueAxis().setCrossAtMax(false);
}
chart.getCategoryAxis().getTitle().setText("X Axis Title");
chart.getValueAxis().getTitle().setText("Y Axis Title");
chart.getSecondValueAxis().getTitle().setText("Y1 Axis Title");
//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();
}
}
}