Free Support Forum - aspose.com

Insert object

I tried to create an Excel document which contains another Excel (Emulating the function using Excel menu insert > object > Create from File > Display as icon).

I found the sample code from

http://www.aspose.com/demos/.net-components/aspose.cells/csharp/quick-start/drawing-objects/inserting-ole-object.aspx

The following changed code generate an Excel with an image icon embeded in sheet 1 cell (0,0). But when I double click the icom, error happens "Cannot start the source application for this object".

Could you please send me the sample code to do the work? Thanks.

//Instantiate a new Workbook.

Workbook workbook = new Workbook();

//Get the first worksheet.

Worksheet sheet = workbook.Worksheets[0];

//Define a string variable to store the image path.

string ImageUrl = @"C:\Temp\Excel.jpg";

//Get the picture into the streams.

FileStream fs = File.OpenRead(ImageUrl);

//Define a byte array.

byte[] imageData = new Byte[fs.Length];

//Obtain the picture into the array of bytes from streams.

fs.Read(imageData, 0, imageData.Length);

//Close the stream.

fs.Close();

//Get an excel file path in a variable.

string path = @"C:\Temp\LookupTable.xlsx";

//Get the file into the streams.

fs = File.OpenRead(path);

//Define an array of bytes.

byte[] objectData = new Byte[fs.Length];

//Store the file from streams.

fs.Read(objectData, 0, objectData.Length);

//Close the stream.

fs.Close();

//Add an Ole object into the worksheet with the image

//shown in MS Excel.

sheet.OleObjects.Add(0, 0, 100, 100, imageData);

//Set embedded ole object data.

sheet.OleObjects[0].ObjectData = objectData;

workbook.Save(@"C:\Temp\MyExcel.xlsx", new OoxmlSaveOptions(SaveFormat.Xlsx));

Hi,

Thanks for your posting and using Aspose.Cells.

You will have to play with the ProgId to make it work. Please see the following reference thread for your more help. You can modify the code as per your needs.

http://www.aspose.com/community/forums/permalink/372079/372079/showthread.aspx#372079

Hi,


Please use OleObject.SourceFullName property for your issue, it will work fine. Please see the following updated code, I just added a line (in bold) to your code:

Sample code:

//Instantiate a new Workbook.

Workbook workbook = new Workbook();

//Get the first worksheet.

Worksheet sheet = workbook.Worksheets[0];

//Define a string variable to store the image path.

string ImageUrl = @“C:\Temp\Excel.jpg”;

//Get the picture into the streams.

FileStream fs = File.OpenRead(ImageUrl);

//Define a byte array.

byte[] imageData = new Byte[fs.Length];

//Obtain the picture into the array of bytes from streams.

fs.Read(imageData, 0, imageData.Length);

//Close the stream.

fs.Close();

//Get an excel file path in a variable.

string path = @“C:\Temp\LookupTable.xlsx”;

//Get the file into the streams.

fs = File.OpenRead(path);

//Define an array of bytes.

byte[] objectData = new Byte[fs.Length];

//Store the file from streams.

fs.Read(objectData, 0, objectData.Length);

//Close the stream.

fs.Close();

//Add an Ole object into the worksheet with the image

//shown in MS Excel.

sheet.OleObjects.Add(0, 0, 100, 100, imageData);

//Set embedded ole object data.

sheet.OleObjects[0].ObjectData = objectData;


sheet.OleObjects[0].SourceFullName = path;

workbook.Save(@“C:\Temp\MyExcel.xlsx”, new OoxmlSaveOptions(SaveFormat.Xlsx));



Let us know if it works or not.


Thank you.

Thank you for your help, Amjad. The code works with the additional line of code.

One more question. I would like the file name to be shown together with the icon. If this can be done, I will talk to my manager about buying your product.

James

Through test, I found another issue.

When I double click the icon for the embeded Excel document, it is open in a second Excel window. But the content of the embeded Excel document also shows in the first Excel window. The behavior is different from manually embdeded document.

James

Hi James,

Good to know that it is working for you now.

James.Wang-1:

One more question. I would like the file name to be shown together with the icon. If this can be done, I will talk to my manager about buying your product.


Well, I am afraid, it is not supported at the moment. I think you may do it by yourself. For example, you may create/get such an image or icon (as per MS Excel) and set it as an image data while inserting OLE Object by Aspose.Cells. Also, to paste file name as well, you may try to use the following APIs of .NET:

Graphics.DrawImage, Graphics.DrawString etc.


Thank you.



Thank you for your reply again, Amjad. But could you please answer my question below? This is more important than the image one. The Excel document generated will contain several embed documents, so I don't want the icon to be replaced by the content when it's double clicked.

When I double click the icon for the embeded Excel document, it is open in a second Excel window. But the content of the embeded Excel document also shows in the first Excel window (Icon disapears). The behavior is different from manually embdeded document.

The icon created through Aspose.Cell is EMBED("Package","") while the icon created by Excel insert is EMBED("Worksheet","")

Please refer to the attached xlsx file.

I found the solution already. Set DisplayAsIcon = true will work.

James

Hi,


Good to know that your have sorted out your issue.

Thank you.

As shown in the code, the image for the OLE object is 100x100 in size. In order to shown the whole image, the height of the row should be greater than 100. There's no problem to set the height using SetRowHeight( ) method. But AutoFitRows( ) does not work. Is this a bug?

James

Hi James,


I think when you insert an OLE Object in MS Excel manually, and then perform Auto-fit rows/columns operation, it does not work either in accordance with the embedded ole object. Aspose.Cells does follow the same standards and works the same way as MS Excel does. If you find any difference or have different opinion, give us your sample files and provide screen shots to show it, we will check it soon.

Thank you.