Hi,
i created chart using aspose.cell and exported ole object frame to ppt .All good.
But when i clicked on ole object frame to view data the worksheet opens and the chart disappears from ppt. Rather than displaying chart is displaying excel data table in PPT.
Is there any way to lock the OLE chart in PPT.
Thanks
I have observed the information shared by you and request you to please share the working sample code, source presentation, source Excel file, generated presentation and desired output presentation. I also suggest you to please first try using latest Aspose.Slides for .NET 17.11 on your end as well. Please provide requested information so that we may help you further.
The input and out files attached here
Files.zip (55.7 KB)
Sample code is as following
Test.Java
public void mytest() throws Exception {
Workbook workbook = new Workbook("C:\\Users\\kedar\\Desktop\\a.xlsx");
Worksheet fws = workbook.getWorksheets().get(0);
String firstCell = fws.getCells().getFirstCell().getName();
String lastCell = fws.getCells().getLastCell().getName();
String sheetName = fws.getName();
String range = sheetName+"!"+"\""+firstCell+":"+lastCell+"\"";
System.out.println(range);
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().add(“Pivot”);
int pivotIndex = worksheet.getPivotTables().add(range,"F3","PivotTables");
PivotTable pt = worksheet.getPivotTables().get(pivotIndex);
pt.setRowGrand(false);
pt.setColumnGrand(false);
pt.setAutoFormat(true);
pt.getRefreshDataOnOpeningFile();
pt.refreshData();
pt.addFieldToArea(PivotFieldType.ROW, 2);
pt.addFieldToArea(PivotFieldType.COLUMN,0);
pt.addFieldToArea(PivotFieldType.DATA, 1);
int sheetIndex = workbook.getWorksheets().add(SheetType.CHART);
Worksheet sheet3 = workbook.getWorksheets().get(sheetIndex);
sheet3.setName("PivotChart");
int chartIndex = sheet3.getCharts().add(ChartType.LINE, 5, 5, 20, 10);
Chart chart = sheet3.getCharts().get(chartIndex);
chart.setPivotSource("Pivot!PivotTables");
chart.setHidePivotFieldButtons(false);
chart.getAutoScaling();
// chart.getShowDataTable();
//chart.setShowDataTable(true);
workbook.save("C:\\Users\\kedar\\Desktop\\b.xlsx");
AddToPPT.Run();
}
AddToPPT.java
public class AddToPPT {
public static void Run() throws Exception {
com.aspose.slides.License l = new com.aspose.slides.License();
l.setLicense("C:\\Users\\kedar\\Documents\\JARS\\Aspose Licence\\Aspose.Slides.lic");
Workbook wb = new Workbook("C:\\Users\\kedar\\Desktop\\b.xlsx");
int chartRows = 55;
int chartCols = 25;
Worksheet sheet3 = wb.getWorksheets().get(2);
sheet3.setName("PivotChart");
com.aspose.cells.ImageOrPrintOptions opts= new com.aspose.cells.ImageOrPrintOptions();
opts.setImageFormat(com.aspose.cells.ImageFormat.getPng());
ByteArrayOutputStream imageStream=new ByteArrayOutputStream();
wb.getWorksheets().get(2).getCharts().get(0).toImage(imageStream, opts);
//wb.getWorksheets().get(2).getCharts().get("PivotChart").getChartObject().getChart();
ByteArrayOutputStream bout=new ByteArrayOutputStream();
wb.save(bout,com.aspose.cells.SaveFormat.EXCEL_97_TO_2003);
//Create a presentation
Presentation pres = new Presentation();
ISlide sld = pres.getSlides().get_Item(0);
//Add the workbook on slide
AddExcelChartInPresentation(pres, sld, bout.toByteArray(), imageStream.toByteArray());
//Write the presentation to disk
}
static void AddExcelChartInPresentation(Presentation pres, ISlide sld, byte[] wbArray, byte[] imgChart) throws Exception
{
double oleHeight = pres.getSlideSize().getSize().getHeight();
double oleWidth = pres.getSlideSize().getSize().getWidth();
Workbook wb=new Workbook();
//Createing and EXCEL_97_TO_2003 LoadOptions object
com.aspose.cells.LoadOptions loadOptions = new com.aspose.cells.LoadOptions(com.aspose.cells.FileFormatType.EXCEL_97_TO_2003);
Workbook wbs=new Workbook(new ByteArrayInputStream(wbArray),loadOptions);
IOleObjectFrame oof = sld.getShapes().addOleObjectFrame(0f, 0f, (float)oleWidth, (float)oleHeight, "Excel.Sheet.8", wbArray);
oof.getSubstitutePictureFormat().getPicture().setImage(pres.getImages().addImage(new ByteArrayInputStream(imgChart)));
//oof.setUpdateAutomatic(true);
IOleObjectFrame Pic = oof;
oof.setUpdateAutomatic(false);
IBaseShapeLock PicLock = Pic.getShapeLock();
//Applying shapes locks
pres.save("C:\\Users\\kedar\\Desktop\\outputT.pptx", com.aspose.slides.SaveFormat.Pptx);
}
}
I have observed the information shared by you and like to share that it is not an issue with Aspose.Slides. Actually, when you are saving b.xlsx, you are not setting any active worksheet index which make sheet 0 as default active sheet for Excel file. When you add the same file as OLE frame in Aspose.Slides, the MS OLE engine, also preview the default active sheet upon double clicking. Please visit documentation article, Activating Sheets for your kind reference. I hope this will be helpful.
Hi mudassir,
Thanks for replying . I want show OLE object from only sheet3 .It should not change when i change sheet
As shared earlier, you need to use Aspose.Cells to set the active sheet index for Excel sheet which shall set the default sheet for Ole frame. You need to first set the Active Sheet index then save the workbook using Aspose.Cells and then add that as OLE frame for presentation.
// Set the first sheet as an active sheet
workbook.getWorksheets().setActiveSheetIndex(0);