Updating Embedded Excel File with Multi Cell References in C# Requires Opening the File and Opening the Object to Update

Aspose.Cells Product Family
, ,
1

In a C# standalone environment with these nuget packages installed

<PackageReference Include="Aspose.Cells" Version="24.10.0" />
<PackageReference Include="Aspose.Slides.NET" Version="24.10.0" />
<PackageReference Include="DocumentFormat.OpenXml" Version="3.1.0" />
<PackageReference Include="Newtonsoft.Json" Version="13.0.3" />

With the following file

bugs.2027 - Copy.zip (476.0 KB)

I use OpenXML to open/update the underlying data for this object

#region Aspose Ignore This Part

then I use Aspose Libraries in order to save and run all “update” procedures.

//  Refresh things using Aspose

Since I run these “RefreshData” methods from Aspose, I would assume these are the ones I need to call. However, I notice when I open the file again, the data does not appear to be updated, although I have to not only open the file, but open the excel object to finally see the updated data.

Observe this as at first, the left hand table has 4 rows;

1st Phorm
Living Essentials LLC
Ghost Energy
0.0

But after the code is run, another row, “Dr Pepper” should be added.

I notice one part of this code that may be a culprit

CalculationOptions opts = new()
{
    Recursive = true,
    IgnoreError = true
};

Set IgnoreError to false and an exception is being thrown, but I can’t see why this would be thrown in this area.

code.zip (4.2 KB)

2

@BK.Broiler.730,
Thank you for contacting free support.

Please note that after loading the PowerPoint presentation using Aspose.Slides and changing the chart data, you do not save the presentation (the Presentation.Save method is not called). I hope this will help you. If the issue persists, please simplify the code as much as possible to reproduce the issues you found.

3

I added this line on Line 132

ap.Save(@"[Directory Omitted]\abc.pptx", Aspose.Slides.Export.SaveFormat.Pptx);

The problem persist even after I saved this new file, opened it, and the same problem occurs, I have to open the object to see the data updated.

The Aspose code is from line 78 - 135, the #region Aspose Ignore this is the underlying sheet update, it can be ignored.

4

@BK.Broiler.730,
I need some time to check the problem. I will get back to you as soon as possible.

5

@BK.Broiler.730,
Thank you for your patience. I’ve reproduced the issue with an OLE object preview not updating after changing the object data.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): SLIDESNET-44743

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

6

@BK.Broiler.730,
Our developers have investigated the case. By default, an OLE object’s image does not update automatically when a presentation is opened in PowerPoint if the object is embedded. This may be due to the absence of a required add-in in PowerPoint. This article explains how to configure PowerPoint for automatic updating of OLE objects. So if you are working with an embedded object, you should ensure that the necessary add-ins are installed in PowerPoint to enable automatic updating of OLE objects, as described in the documentation.

7

Hello,

We have been using Aspose primarily on our web platform and we have often have examples where Ole Objects have updated automatically most of the time. Especially in much more straightforward examples. Why is it not updating in this specific example?

8

@BK.Broiler.730,
Could you please share a sample presentation and the simplest code example to reproduce the automatic update of the OLE object preview?

9

Hey, update, so the article linked and the request to provide a more simplified example lead me to realize a key detail was missing, we were using SheetRender class in order to provide a snapshot of the update chart/object, which seems to update the actual object for us. In our environment, we are seeing this sheet not updating, but when I tried to create a more standalone example, the sheetrender class was showing the new record as we expect it to. I am going to have to take a deeper dive into how I can replicate our production environment to replicate this example of SheetRender not showing the updated information.

Thanks for the link.

10

Alright alright alright!

I have an update now with a better grasp on the issue at hand, I can tell my supervisor to add a different ticket number to our paid support if a new ticket item needs to be created.

bugs.2027.updated.zip (622.3 KB)

I have both an updated code base that has been super simplified, I even took out the OpenXML code and made this strictly Aspose related. I have an updated powerpoint to this as well. In a much more simplified example, the png generated has the updated data, even if I have to open and click the embeeded object to see it in the file itself. But in this case, the image itself does not update, on the right hand side I should see entries like “KEURIG DR PEPPER” or “BD3 INC” but they do not show up on the generated PNG. If I open the file and open the embedded object, the data is there.

11

@BK.Broiler.730,
Thank you for the details. I’ve reviewed your updated code example. Please note that the Presentation object (ap variable) is again not saved after changes. I added the code line

ap.Save(@"[Directory Omitted]\output.pptx", Aspose.Slides.Export.SaveFormat.Pptx);

after

ole.SubstitutePictureFormat.Picture.Image.ReplaceImage(imageStream.ToArray());

and the OLE preview updated.

I hope this will help you.

12

Hello, I just tried this, first trying to save the

ReportStream

then trying a physical file in your example, the image is still not updating with the latest data. I changed the code to look like this

sr.ToImage(0, imageStream);
ole.SubstitutePictureFormat.Picture.Image.ReplaceImage(imageStream.ToArray());
ap.Save(@"[Directory Omitted]\output.pptx", Aspose.Slides.Export.SaveFormat.Pptx);
File.WriteAllBytes($"[Directory Omitted]\\{slideShape.CustomData.Tags["ObjectName"]}.png", imageStream.ToArray());
13

@BK.Broiler.730,
It looks like the issue is related to Aspose.Cells. I’ve moved this thread to the Aspose.Total forum. My colleagues from Aspose.Cells team will assist you shortly.
@amjad.sahi FYI

14

@BK.Broiler.730,

I have evaluated the details and resource files you provided. I am not entirely sure if the issue is with the Aspose.Cells APIs. I need to test the Aspose.Cells part of the issue only. Could you please provide a sample (runnable) code or a sample (console) application that uses only Aspose.Cells and can reproduce the Aspose.Cells’ rendering (image) issue? This will help us evaluate your issue precisely and figure it out soon.

15

Alright, lets take it from the top:

In a C# Console environment with these nuget packages installed

Aspose.Cells - 24.10.0
Aspose.Slides.NET - 24.11.0
DocumentFormat.OpenXml - 3.1.0
Newtonsoft.Json - 13.0.3

Lets start with this simple example

simple-example.zip (38.7 KB)

It contains 1 powerpoint file with an embedded excel object with a few rows, if you open the file and open the embedded object, you will see it updates with 1 additional row at row 6 starting with “ENERGY DRINKS CMBND.” You cannot see this row when you first open the file. Yet despite this, run the C# code in the txt file and the SheetRender PNG file that takes a snapshot of this will save with the newer row in there.

Now, lets go to a more complex example

bugs.2027.updated.20241108.zip (617.6 KB)

Same thing as simple but this is a multi sheet excel embed with lookups and cells that look at other cells that are their own lookups. If the powerpoint is opened and the embedded object is opened, you should see the right hand chart have extra entries in it like “KEURIG DR PEPPER” or “BD3 INC." Going off the simple example, the screenshot should also reflect this updated data but it seems “stuck” on the old data.

16

@BK.Broiler.730
We tried simple ppt file with Aspose.cells 24.10 and Aspose.slides 24.11.
We got image and ppt :
24.10.zip (50.5 KB)
“ENERGY DRINKS” is updated and the ole object becomes a simple picture.

17

@simon.zhao , correct, it works as expected in the simple file, not the other one which is more complex. So the question is what am i doing wrong where the png file has the updated visual in one file but not the other

18

@BK.Broiler.730,

I tested your complex scenario/case using your exact code and PowerPoint presentation file. Since the underlying Excel file (for embedded OLE Object) has dynamic array formulas, so kindly add a line (in bold) to your code segment and it will work as expected. Those missing entries/data will also be rendered in the output image.

…
foreach (Aspose.Cells.Worksheet worksheet1 in wb.Worksheets)
{
worksheet1.RefreshPivotTables();
foreach (PivotTable pTable in worksheet1.PivotTables)
{
pTable.RefreshDataFlag = true;
pTable.RefreshData();
pTable.RefreshDataFlag = false;
pTable.CalculateData();
pTable.RefreshDataOnOpeningFile = true;
pTable.CalculateRange();

}
worksheet1.CalculateFormula(opts, true);
worksheet1.Shapes.UpdateSelectedValue();
foreach (Aspose.Cells.Charts.Chart chart1 in worksheet1.Charts)
{
    chart1.RefreshPivotData();
    chart1.Shapes.UpdateSelectedValue();
    chart1.Calculate();
}

}

wb.RefreshDynamicArrayFormulas(false);
wb.CalculateFormula(opts);

Aspose.Cells.Worksheet sheet = GetUpdatedWorksheetForOleObjectFrame(wb.Worksheets[activeIndex], “A10:O34”);

ImageOrPrintOptions options = GetImageOrPrintOptionsForOleObjectFrame(width, height, horizontalRes, verticalRes);

SheetRender sr = new SheetRender(sheet, options);

sr.ToImage(0, imageStream);
…

Let us know if you still find the issue.

19

@amjad.sahi

That appears to have fixed the issue, I am adding this line to our production environment and closing this item.

20

@BK.Broiler.730,

We’re glad to hear that the suggested line of code resolved your issue. Please don’t hesitate to reach out to us if you have any additional questions or comments.