Inserting a PDF document into a spreadsheet cell

Aspose.Cells Product Family
1

I am using a sql database. In one of the tables we are storing a pdf document in a varbinary(Max) format in a table .

I need to be able to insert this field into a spreadsheet as an attachment. I am writing in VB.net code (not C#). Could someone give me an example of how to do this. I couldn't find an example on your site nor on the internet.

thanks

2

Hi David,

Thank you for writing to us.

If you can convert your PDF file into an array of bytes or a stream then you may consider embedding the PDF file as an Ole Object in the spreadsheet. Please check the below provided source code to achieve this. Also have a look at the attached archive for the resultant spreadsheet.

VB.Net


'Define a string variable to store the image path
'this image will serve as icon for embedded object
Dim ImageUrl As String = myDir & “images.jpg”
'Get the picture into the stream
Dim fs As FileStream = File.OpenRead(ImageUrl)
'Define a byte array
Dim imageData(fs.Length - 1) As Byte
'Obtain the picture into the array of bytes from stream
fs.Read(imageData, 0, imageData.Length)
'Close the stream
fs.Close()
'Get an PDF file path in a variable
Dim path As String = myDir & “sample.pdf”
'Get the file into the stream
fs = File.OpenRead(path)
'Define an array of bytes
Dim objectData(fs.Length - 1) As Byte
'Store the file from stream
fs.Read(objectData, 0, objectData.Length)
'Close the stream
fs.Close()

Dim intIndex As Integer = 0
'Instantiate a new Workbook.
Dim workbook As New Workbook()
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim index As Integer = sheet.OleObjects.Add(14, 3, 200, 220, imageData)
workbook.Worksheets(index).OleObjects(intIndex).FileType = OleFileType.Pdf
workbook.Worksheets(index).OleObjects(intIndex).ObjectData = objectData
workbook.Worksheets(index).OleObjects(intIndex).SourceFullName = path
workbook.Save(myDir & “output.xlsx”, SaveFormat.Xlsx)

Hope this helps. Please feel free to write back in case you face any difficulty.
3

thank you for your response … everything is great except that the PDF file is stored in a table as a varbinary(max) … I need the logic of how to read the value and then plug it into the spreadsheet . Your example is referenceing a PDF file in a folder.


thanks

4

Hi David,

Yes, the code snippet provided earlier loads a PDF file from disk using FileStream that is in turn converted into an array of bytes and set as Ole Object data. As you may see from the output that PDF file was injected perfectly in the resultant spreadsheet and can be accessed according to your requirement. Aspose.Cells provides this feature for most commonly used Microsoft Office formats so you can opt the similar approach to embed Word, PowerPoint, Excel and PDF files in spreadsheets.

As narrated earlier if you can convert your documents into stream objects then you can attach them as Ole Objects. In order to read the file data stored as varbinary(max) and to convert it to stream object, you have to write your own custom logic because Aspose.Cells component does not cover this part of your requirement.

Thank you for your understanding.