Adjusting the visual look of presentation

Aspose.Cells Product Family
1

Our customer has visual guideline for the presentation they use - and we would like to
generate those presentations automatically from our java-serverside software.

I attached the ppt template that we need to generate.

Are we able to that with Aspose (colors, text angle, font, size)?
The document has to be identical with the template (template.ppt).

The charts need to be included into powerpoint as excel-objects. Our customer has to be able to edit any part of the presentation (so we are not able to use picture charts).


2

Hi,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

Well, you can use Aspose.Cells to create the charts (with different formatting features provided by MS Excel) and then embed them in your presentation using Aspose.Slides for Java. Please see the following sample code which will give you the basic idea of how to create and add the chart as Ole Object in a presentation.

Workbook workbook = new Workbook();

Worksheets worksheets = workbook.getWorksheets();

//Obtaining the reference of the first worksheet

Worksheet worksheet= worksheets.getSheet(0);

Cells cells = worksheet.getCells();

cells.getCell("A1").setValue(50);

cells.getCell("A2").setValue(100);

cells.getCell("A3").setValue(150);

cells.getCell("A4").setValue(200);

cells.getCell("B1").setValue(60);

cells.getCell("B2").setValue(32);

cells.getCell("B3").setValue(50);

cells.getCell("B4").setValue(40);

cells.getCell("C1").setValue("Q1");

cells.getCell("C2").setValue("Q2");

cells.getCell("C3").setValue("Y1");

cells.getCell("C4").setValue("Y2");

Charts charts = worksheet.getCharts();

Chart chart= charts.addChart(ChartType.COLUMN_CLUSTERED,5,0,15,5);

chart.getNSeries().add("=A1:C4", true);

chart.getCategoryAxis().setRotation(45);

//Get Chart as image.

ImageOptions imgOpts = new ImageOptions();

imgOpts.setImageFormat(ImageFormat.PNG);

chart.toImage(new FileOutputStream("C:\\chart.png"), imgOpts);

workbook.getWorksheets().setActiveSheet(0);

//Save the workbook to stream

ByteArrayOutputStream bout = new ByteArrayOutputStream();

workbook.save(bout);

workbook.save(new FileOutputStream("C:\\output.xls"));

//Add a chart in ChartSheet with data series from DataSheet

int chartWidth = 600;

int chartHeight = 400;

int chartHeightInRows = 0;

int chartWidthInCols = 0;

int cellWidth = worksheet.getCells().getColumnWidthPixel(0);

int cellHeight = worksheet.getCells().getRowHeightPixel(0);

chartWidthInCols = (int)(chartWidth / cellWidth);

chartHeightInRows = (int)(chartHeight / cellHeight);

chartWidth = chartWidthInCols * cellWidth;

chartHeight = chartHeightInRows * cellHeight;

Presentation pres = new Presentation();

Slide sld = pres.getSlideByPosition(1);

int slideWidth = (int) pres.getSlideSize().getX();

int slideHeight = (int) pres.getSlideSize().getY();

OleObjectFrame oof = sld.getShapes().addOleObjectFrame(800, 500, chartWidth * 8, chartHeight * 8, "Excel.Sheet.8", bout.toByteArray());

com.aspose.slides.Picture pic = new com.aspose.slides.Picture(pres, new FileInputStream("C:\\chart.png"));

int picId = pres.getPictures().add(pic);

oof.setPictureId(picId);

pres.write(new FileOutputStream("C:\\output.ppt"));

Also, please find the attached Excel and PPT files generated for your reference. In case of any further query, please let us know. We will be happy to help you out.

Thank You & Best Regards,

3

Thank you for the reply.

Output.ppt is very close to what we need. There are only 3 things to fix.

1. Chart must have white background (now it’s gray)

2. Chart and all other objects must be without black borders

3. The color mark and series text has to be positioned at the bottom of the slide
(like in template.ppt)


If this is all possible, we will buy Aspose.

4

Hi,

Well, Aspose.Cells for Java does provide API to perform your all the three tasks you mentioned.

See the following updated code, and attached are the output files.

Workbook workbook = new Workbook();
Worksheets worksheets = workbook.getWorksheets();

//Obtaining the reference of the first worksheet
Worksheet worksheet= worksheets.getSheet(0);
Cells cells = worksheet.getCells();



cells.getCell(“A1”).setValue(50);
cells.getCell(“A2”).setValue(100);
cells.getCell(“A3”).setValue(150);
cells.getCell(“A4”).setValue(200);
cells.getCell(“B1”).setValue(60);
cells.getCell(“B2”).setValue(32);
cells.getCell(“B3”).setValue(50);
cells.getCell(“B4”).setValue(40);
cells.getCell(“C1”).setValue(“Q1”);
cells.getCell(“C2”).setValue(“Q2”);
cells.getCell(“C3”).setValue(“Y1”);
cells.getCell(“C4”).setValue(“Y2”);
Charts charts = worksheet.getCharts();
Chart chart= charts.addChart(ChartType.COLUMN_CLUSTERED,5,0,15,5);
chart.getNSeries().add("=A1:C4", true);
chart.getCategoryAxis().setRotation(45);

//Set the plot area’s background color to white
chart.getPlotArea().getArea().setForegroundColor(Color.WHITE);
//Hide the plot area’s borders
chart.getPlotArea().getBorder().setVisible(false);

//Hide the chart area’s borders
chart.getChartArea().getBorder().setVisible(false);


//Hide the borders of the data series.
for(int i=0;i<chart.getNSeries().size();i++)
{
chart.getNSeries().get(i).getBorder().setVisible(false);
}

//Hide the valueaxis and category axis line.
chart.getCategoryAxis().getAxisLine().setVisible(false);
chart.getValueAxis().getAxisLine().setVisible(false);

//Position the legend to bottom and make the border invisible
chart.getLegend().setPosition(LegendPositionType.BOTTOM);
chart.getLegend().getBorder().setVisible(false);

//Get Chart as image.
ImageOptions imgOpts = new ImageOptions();
imgOpts.setImageFormat(com.aspose.cells.ImageFormat.PNG);
chart.toImage(new FileOutputStream(“d:\files\techart.png”), imgOpts);
workbook.getWorksheets().setActiveSheet(0);
//Save the workbook to stream
ByteArrayOutputStream bout = new ByteArrayOutputStream();
workbook.save(bout);
workbook.save(new FileOutputStream(“d:\files\teoutput.xls”));

//Add a chart in ChartSheet with data series from DataSheet
int chartWidth = 600;
int chartHeight = 400;
int chartHeightInRows = 0;
int chartWidthInCols = 0;
int cellWidth = worksheet.getCells().getColumnWidthPixel(0);
int cellHeight = worksheet.getCells().getRowHeightPixel(0);
chartWidthInCols = (int)(chartWidth / cellWidth);
chartHeightInRows = (int)(chartHeight / cellHeight);
chartWidth = chartWidthInCols * cellWidth;
chartHeight = chartHeightInRows * cellHeight;

Presentation pres = new Presentation();
Slide sld = pres.getSlideByPosition(1);
int slideWidth = (int) pres.getSlideSize().getX();
int slideHeight = (int) pres.getSlideSize().getY();


OleObjectFrame oof = sld.getShapes().addOleObjectFrame(800, 500, chartWidth * 8, chartHeight * 8, “Excel.Sheet.8”, bout.toByteArray());
com.aspose.slides.Picture pic = new com.aspose.slides.Picture(pres, new FileInputStream(“d:\files\techart.png”));
int picId = pres.getPictures().add(pic);
oof.setPictureId(picId);


pres.write(new FileOutputStream(“d:\files\teoutput.ppt”));


Thank you.

5

Hi,


and thanks for the answer in previous posts ( from my colleague ).


We implemented the solution in our code and now it seems to work quite fine, however the styles are lost after the following process:

1) create a powerpoint file with generated chart-image

2) double-click the chart to edit excel sheet

3) quit editing the excel

>> the chart after edit is not styled anymore ( plain OLE-object instead of generated chart image )

Is there any other way to do this than generating an image in Java? Like formatting the excel sheet and the OLE frame?

I also noticed excel sheet as an OLE-object does not work when reading the powerpoint file with OpenOffice. ( After saving to openoffice format the excel -data is missing )…



regards
Timo
6

Hi,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Well, we are not very clear about your issue / requirement. Please share your code and generated files to show the issue. We will check it soon.

Thank You & Best Regards,

7

Hi,


the code doesn’t really differ from the code earlier in this thread.

The results of the powerpoint-generation are attached as screenshots:
- a picture from fresh-generated powerpoint ( before.JPG )
- a picture when editing the chart ( edit.JPG )
- a picture when finished the editing ( before.JPG )

Hopefully those screenshots clarify the problem…


regards
Timo

8

Hi Timo,

To generate your desired ppt file, I think you can change the sample code a bit like following:
Workbook workbook = new Workbook();
Worksheets worksheets = workbook.getWorksheets();

//Obtaining the reference of the first worksheet
Worksheet worksheet= worksheets.getSheet(0);
Cells cells = worksheet.getCells();
cells.getCell("A1").setValue(50);
cells.getCell("A2").setValue(100);
cells.getCell("A3").setValue(150);
cells.getCell("A4").setValue(200);
cells.getCell("B1").setValue(60);
cells.getCell("B2").setValue(32);
cells.getCell("B3").setValue(50);
cells.getCell("B4").setValue(40);
cells.getCell("C1").setValue("Q1");
cells.getCell("C2").setValue("Q2");
cells.getCell("C3").setValue("Y1");
cells.getCell("C4").setValue("Y2");
Worksheet chartsheet = worksheets.addSheet(SheetType.CHART, "ChartSheet");
worksheets.setActiveSheet(chartsheet);
 Charts charts = chartsheet.getCharts();
Chart chart= charts.addChart(ChartType.COLUMN_CLUSTERED,5,0,15,5);
chart.getNSeries().add("=Sheet1!A1:C4", true);
chart.getCategoryAxis().setRotation(45);

//Set the plot area's background color to white
chart.getPlotArea().getArea().setForegroundColor(Color.WHITE);
//Hide the plot area's borders
chart.getPlotArea().getBorder().setVisible(false);

//Hide the chart area's borders
chart.getChartArea().getBorder().setVisible(false);


//Hide the borders of the data series.
for(int i=0;i<chart.getNSeries().size();i++)
{
chart.getNSeries().get(i).getBorder().setVisible(false);
}

//Hide the valueaxis and category axis line.
chart.getCategoryAxis().getAxisLine().setVisible(false);
chart.getValueAxis().getAxisLine().setVisible(false);

//Position the legend to bottom and make the border invisible
chart.getLegend().setPosition(LegendPositionType.BOTTOM);
chart.getLegend().getBorder().setVisible(false);

//Get Chart as image.
ImageOptions imgOpts = new ImageOptions();
imgOpts.setImageFormat(com.aspose.cells.ImageFormat.PNG);
chart.toImage(new FileOutputStream("techart.png"), imgOpts);
// workbook.getWorksheets().setActiveSheet(0);
//Save the workbook to stream
ByteArrayOutputStream bout = new ByteArrayOutputStream();
workbook.save(bout);
workbook.save(new FileOutputStream("teoutput.xls"));

//Add a chart in ChartSheet with data series from DataSheet
int chartWidth = 600;
int chartHeight = 400;
int chartHeightInRows = 0;
int chartWidthInCols = 0;
int cellWidth = worksheet.getCells().getColumnWidthPixel(0);
int cellHeight = worksheet.getCells().getRowHeightPixel(0);
chartWidthInCols = (int)(chartWidth / cellWidth);
chartHeightInRows = (int)(chartHeight / cellHeight);
chartWidth = chartWidthInCols * cellWidth;
chartHeight = chartHeightInRows * cellHeight;

Presentation pres = new Presentation();
Slide sld = pres.getSlideByPosition(1);
int slideWidth = (int) pres.getSlideSize().getX();
int slideHeight = (int) pres.getSlideSize().getY();


OleObjectFrame oof = sld.getShapes().addOleObjectFrame(800, 500, chartWidth * 8, chartHeight * 8, "Excel.Sheet.8", bout.toByteArray());
com.aspose.slides.Picture pic = new com.aspose.slides.Picture(pres, new FileInputStream("techart.png"));
int picId = pres.getPictures().add(pic);
oof.setPictureId(picId);


pres.write(new FileOutputStream("teoutput.ppt"));


Kindly let us know if you still have any confusion/issue.

Thank you.

9

Hi and thanks for the reply.



We made some progress and the powerpoint starts to look better.

Is there any way to preserve the style of the chart? Now the bar color is different and the titles overlap the bars. For example, chart.toImage() will change the bar color to RGB 153/153/255, when we have set it to 54/0/148 in Excel.

The point is to have chart.toImage() to render 1:1 copy of the chart.


best regards
Timo
10

Hi Timo,

If you have created the excel file (XLS) in Excel 2007 with some custom colors, the colors won’t match to the chart’s image, you may verify it opening the same xls file in Excel 2003 and 2007 versions separately to get the difference. So, if the custom colors are used, Aspose.Cells for Java will try to replace the colors with the nearest possible matching colors.

Anyways, kindly post your template excel file from which you have taken the chart’s image, we will check the issues of bars color and overlapping.

Thank you.

11

Hi,

The excel file is generated programmatically by the Aspose.Cells plugin.

Here’s the code:

// constructing the sheet
new ChartSheet(excel, “Diagrammit”, SheetType.CHART);

…

// excel code
excel.getWorksheets().setActiveSheet(2);
excel.getWorksheets().getActiveSheet().setFirstVisibleRow(1);
excel.getWorksheets().getActiveSheet().setFirstVisibleColumn(1);
Chart chart = excel.getWorksheets().getActiveSheet().getCharts().getChart(0);
chart.getTitle().setText("");
chart.setType(ChartType.COLUMN_CLUSTERED);
chart.setLegendShown(false);
chart.getPlotArea().setHeight(2000);
chart.getPlotArea().setWidth(3000);

chart.getCategoryAxis().setRotation(45);

//Set the plot area’s background color to white
chart.getPlotArea().getArea().setForegroundColor(com.aspose.cells.Color.WHITE);
chart.getPlotArea().getArea().setForegroundColor(com.aspose.cells.Color.WHITE);

//Hide the plot area’s borders
chart.getPlotArea().getBorder().setVisible(false);

//Hide the chart area’s borders
chart.getChartArea().getBorder().setVisible(false);

//Hide the borders of the data series.
for(int i=0;i<chart.getNSeries().size();i++)
{
chart.getNSeries().get(i).getBorder().setVisible(false);
chart.getNSeries().get(0).getArea().setForegroundColor(new com.aspose.cells.Color(54, 0, 148));
chart.getNSeries().get(0).getArea().getFillFormat().setColor1(new com.aspose.cells.Color(54, 0, 148));
chart.getNSeries().get(0).getArea().getFillFormat().setColor2(new com.aspose.cells.Color(54, 0, 148));

}

//Hide the valueaxis and category axis line.
chart.getCategoryAxis().getAxisLine().setVisible(false);

chart.getValueAxis().getAxisLine().setVisible(false);


ImageOptions imgOpts = new ImageOptions();
imgOpts.setImageFormat(com.aspose.cells.ImageFormat.PNG);
imgOpts.setFashion(1);

ByteArrayOutputStream pic = new ByteArrayOutputStream();

chart.toImage(pic, imgOpts);

picture = new ByteArrayInputStream(pic.toByteArray());

ByteArrayOutputStream bout = new ByteArrayOutputStream();
excel.save(bout);
workbook = bout.toByteArray();

bout.close();

–

Do you see anything in this code which could cause the distortion between the real chart in the Excel file and the chart printed by chart.toImage() function? To me it would seem that the chart.toImage() function is broken somehow.

I will post the related .xls and .ppt files later.

–

Tuomas Hynninen
Lead Programmer
Sonecta

12

Hi,

Kindly post your excel and ppt files, we will check your issue soon.

Thank you.

13

Hi,

Thanks for providing us the files.

Please try our latest fix v2.1.2.11, I have tested the conversion(chart-to-image) and it works fine.

Kindly let us know if it works fine with the attached version.

Thank you.

14

Hi!

I took the newest version from Aspose/Downloads and it works perfectly.

Thanks!