Updating the embedded OLE Excel sheet data does not change OLE Image in VISIO (C# .NET)

Aspose.Diagram Product Family
1

The question is slightly same as thread below, but I am trying to achieve this in VISIO diagram. Please assist!

2

@Gsunbinary

Please check the below sample code snippet which extracts embedded Excel file from the Visio Diagram. The below code contains optional code lines as well which you can use or remove as per your requirements.

Aspose.Diagram.Diagram diagram = new Aspose.Diagram.Diagram(dataDir + "Test002.vsdx", Aspose.Diagram.LoadFileFormat.VSDX);
int nFile = 1;
foreach (Aspose.Diagram.Page diagramPage in diagram.Pages)
{
 foreach (Aspose.Diagram.Shape OLE_Shape in diagramPage.Shapes)
 {
  if (OLE_Shape.Type == Aspose.Diagram.TypeValue.Foreign)
  {
   if (OLE_Shape.ForeignData.ForeignType == Aspose.Diagram.ForeignType.Object)
   {
    Stream Ole_stream = new MemoryStream(OLE_Shape.ForeignData.ObjectData);
    // Get format of the OLE file object
    Aspose.Cells.FileFormatInfo info = Aspose.Cells.FileFormatUtil.DetectFileFormat(Ole_stream);
    if (info.LoadFormat == Aspose.Cells.LoadFormat.Xlsx)
    {
     // Modify an OLE object
     Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(new MemoryStream(OLE_Shape.ForeignData.ObjectData));

      MemoryStream outStream = new MemoryStream();
      //workbook.Save(dataDir + "sample"+DateTime.Now.Millisecond+".xlsx", Aspose.Cells.SaveFormat.Xlsx);
     workbook.Save(outStream, Aspose.Cells.SaveFormat.Xlsx);
     // if you want to update the excel file after modification
     OLE_Shape.ForeignData.ObjectData = outStream.ToArray();
     OLE_Shape.RefreshData();
     nFile++;
    }
   }
  }
 }
}
3

@asad.ali

I appreciate for sharing this code snippet. I have now added below line which was left from my original code, however it is still not updating preview. Please assist!

OLE_Shape.RefreshData();

George

4

@Gsunbinary

Please share your sample Diagram along with the complete code snippet that you have tried so that we can also test it at our end and address the issue accordingly.

5

@asad.ali

Please find the attached sample project that I used to work with the OLE object.
DrawingPackageLibrary.zip (1.4 MB)

George

6

@Gsunbinary

We have noticed the similar issue at our end while using your project. Therefore, an issue as DIAGRAMNET-52029 has been logged in our issue tracking system for the sake of investigation. We will further look into its details and keep you posted with the status of its correction. Please be patient and spare us some time.

We are sorry for the inconvenience.

7

@asad.ali

Thank you

8

@Gsunbinary

We have investigated the earlier logged ticket. Please use the below lines of code to get desired output:

MemoryStream outM = new MemoryStream();                        
workbook.Save(outM, SaveFormat.Xlsx);

//add codes to set ImageData                        
MemoryStream emfM = new MemoryStream();                       
ImageOrPrintOptions imgOpt = new ImageOrPrintOptions();                        
imgOpt.Transparent = true;                        
imgOpt.ImageFormat = ImageFormat.Emf;                        
SheetRender sr = new SheetRender(workbook.Worksheets[0], imgOpt);                        
sr.ToImage(0, emfM);                        
OLE_Shape.ForeignData.ImageData = emfM.ToArray();

// Save back an OLE object                        
OLE_Shape.ForeignData.ObjectData = outM.ToArray();
9

@asad.ali

Thanks, it worked!

The only thing I need to update on above code is to replace the ImageFormat with ImageType, because it is obselete.

//imgOpt.ImageFormat = ImageFormat.Emf; 
imgOpt.ImageType = Aspose.Cells.Drawing.ImageType.Emf;

Thanks again,
George

1 Like