So I’m evaluating Aspose.Cells to see if it can take care of a problem introduced as our customers upgrade their computers and move to Adobe 9+ and Office 2010+. The problem being the old methodology of using the COM based Excel libraries to add OLE objects -ex excelSheet.OLEObjects.Add(filename:={name.pdf}, Link:=False, DisplayAsIcon:=False)- no longer works if the newer versions of Adobe are installed.
Using Aspose.Cells easily bypasses the issue of getting a pdf into Excel. However the displays in the sheets are not very helpful to us. In particular we dump a lot of supporting pdf documents onto any given worksheet. So following Aspose’s sample code to insert multiple pdf documents into a sheet:
index = wrkSheet.OleObjects.Add(1, 1, 48, 48, My.Resources.adobe_pdf_document)
wrkSheet.OleObjects.Item(index).ObjectData = fdata
wrkSheet.OleObjects.Item(index).FileFormatType = Aspose.Cells.FileFormatType.Pdf
results in a worksheet filled with 10 or so adobe icons with no differentiation between them or indicator of what maybe in the files. I’ve tried:
wrkSheet.OleObjects.Item(index).DisplayAsIcon = False
thinking it may have been analogous to the Excel library calls with no luck.
If the DisplayAsIcon cannot be made functional, at the very least is there some way to add a name to each of the icons put into a worksheet?
Hi,
Thanks for providing us some details.
Well, I think you may add different icons/images for different PDF documents for differentiations. If you want to persist with same icons/images, then you may add hyperlinks for different Ole Objects, so the users could see different links/ tool tips for different embedded icons accordingly while hovering over the icons. See the sample code below and find attached the output file for your reference:
e.g
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 = @“e:\test\school.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 = @“e:\test2\Test-21.PDF”;
//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(14, 3, 20, 20, imageData);
//Set embedded ole object data.
sheet.OleObjects[0].ObjectData = objectData;
sheet.OleObjects[0].DisplayAsIcon = true;
sheet.OleObjects[0].FileFormatType = FileFormatType.Pdf;
sheet.OleObjects[0].AddHyperlink(“Test-21.PDF”);
string ImageUrl1 = @“e:\test2\image-koala.jpg”;
//Get the picture into the streams.
FileStream fs1 = File.OpenRead(ImageUrl1);
//Define a byte array.
byte[] imageData1 = new Byte[fs1.Length];
//Obtain the picture into the array of bytes from streams.
fs1.Read(imageData1, 0, imageData1.Length);
//Close the stream.
fs1.Close();
//Get an excel file path in a variable.
string path1 = @“e:\test2\output.PDF”;
//Get the file into the streams.
fs1 = File.OpenRead(path1);
//Define an array of bytes.
byte[] objectData1 = new Byte[fs1.Length];
//Store the file from streams.
fs1.Read(objectData1, 0, objectData1.Length);
//Close the stream.
fs1.Close();
//Add an Ole object into the worksheet with the image
//shown in MS Excel.
sheet.OleObjects.Add(20, 3, 20, 20, imageData1);
//Set embedded ole object data.
sheet.OleObjects[1].ObjectData = objectData1;
sheet.OleObjects[1].DisplayAsIcon = true;
sheet.OleObjects[1].AddHyperlink(“output.PDF”);
//Save the excel file
workbook.Save(@“e:\test2\oleobjects194.xlsx”);
Hope, this helps a bit.
Thank you.
Thanks for the suggestion. The problem with the .AddHyperlink({link}) approach I just tried per your suggestion is that the icon is a true hyperlink to the specified {link} location. It would have been an eloquent and acceptable solution to my problem of differentiating the various files’ data by means of the tooltip text, however the pdf files must be embedded.
Differing jpeg images for each file… Not going to happen unless Aspose.Cells also comes with a utility that can dynamically convert a string into a jpeg - aka adding text to the image data byte array for the OLEObjects.Add() method.
I also noticed that in your sample code you included the OleObjects.DisplayAsIcon property. Having tried variations on setting that property (True/False & before/after setting the object data) I find discernible affect on what that property does.
Your reply is appreciated though.
Hi,
Could you provide us your expected file with your desired Ole objects embedded into the sheet, you may manually create it in Ms Excel, we will check it on how to do it via Aspose.Cells APIs.
Thank you.
My program is a ‘Work-In-Progress’, so the copies of the same embedded pdf are actually all supposed to be different files and first two worksheets should be copied several more times - but attached is something like CiSCO would like see. The pdf data opened in the worksheets when the workbook is opened. As is, I have to manually “open” each of the pdf objects and then save the spreadsheet.
Hi,
Thanks for the template file.
Well, we have evaluated your scenario/ case in details. I think you can get it done by creating the image by himself at runtime in the memory, so your embedded objects (pasted as icons) should be differentiated accordingly. For example, you may update an image in the memory and draw some string (e.g add filename etc.) on the image accordingly, it should be simple for you to handle it, and then you can set the updated image data for the Ole Object embedded on the sheet. I have written an example code to accomplish the task, see the following sample code and find attached the output file for your complete reference. Please refer to it and write your own codes accordingly for your requirements.
e.g
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 = @“e:\test2\pdficon.jpg”;
//1st Ole Object for PDF
//Get an excel file path in a variable.
string path = @“e:\test2\Test-21.PDF”;
//Get the file into the streams.
FileStream 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();
//Get the file name only.
string fname = Path.GetFileName(path);
//Get the PDF icon image into Bitmap.
//Edit the image to add the string to the image
Bitmap image1 = new Bitmap(ImageUrl, true);
Graphics g = Graphics.FromImage(image1);
g.DrawString(fname, new System.Drawing.Font(“Tahoma”, 20), Brushes.White, new Point(10, 10));
MemoryStream ms = new MemoryStream();
image1.Save(ms, ImageFormat.Jpeg);
byte [] imageData = ms.ToArray();
//Add an Ole object into the worksheet with the updated image
//shown in MS Excel.
sheet.OleObjects.Add(14, 3, 80, 80, imageData);
//Set embedded ole object data.
sheet.OleObjects[0].ObjectData = objectData;
sheet.OleObjects[0].DisplayAsIcon = true;
sheet.OleObjects[0].FileFormatType = FileFormatType.Pdf;
//2nd Ole Object for PDF
//Get an excel file path in a variable.
string path1 = @“e:\test2\output.PDF”;
//Get the file into the streams.
fs = File.OpenRead(path1);
//Define an array of bytes.
byte[] objectData1 = new Byte[fs.Length];
//Store the file from streams.
fs.Read(objectData1, 0, objectData1.Length);
//Close the stream.
fs.Close();
//Get the file name only.
string fname1 = Path.GetFileName(path1);
//Get the PDF icon image into Bitmap.
//Edit the image to add the string to the image
Bitmap image2 = new Bitmap(ImageUrl, true);
Graphics g2 = Graphics.FromImage(image2);
g2.DrawString(fname1, new System.Drawing.Font(“Tahoma”, 20), Brushes.White, new Point(10, 10));
MemoryStream ms1 = new MemoryStream();
image2.Save(ms1, ImageFormat.Jpeg);
byte[] imageData1 = ms1.ToArray();
//Add an Ole object into the worksheet with the updated image
//shown in MS Excel.
sheet.OleObjects.Add(25, 3, 80, 80, imageData1);
//Set embedded ole object data.
sheet.OleObjects[1].ObjectData = objectData1;
sheet.OleObjects[1].DisplayAsIcon = true;
sheet.OleObjects[1].FileFormatType = FileFormatType.Pdf;
//Save the excel file
workbook.Save(@“e:\test2\oleobjects1.xlsx”);
Hope, it helps you a bit.
Note: I am afraid, there is no better way (than above) to accomplish your task via Aspose.Cells APIs for your situation.
Thank you.
Not as hands free as the actual embedding of the pdf files, but an acceptable work around.
Thanks for the support. I have put in the recommendation that licensing for Aspose.Cells be the solution to our current predicament.
Hi,
Thanks for your feedback.
Good to know that it works for your needs. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.
Thank you.