Inserting varbinary(Max) files into an excel spreadsheet

pimentinho · November 20, 2013, 10:18am

I am using a sql database. In one of the tables we are storing a document in a varbinary(Max) format in a table . It could be a pdf, doc, or excel document.

I need to be able to insert this field into a spreadsheet . 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.

Also how to I make reference for the picture(icon) url that I would use to display it. We are using the corporate intranet so there is no direct access to the a particular drive just the url address.

thanks

amjad.sahi · November 20, 2013, 11:44am

Hi,

Thanks for your inquiry.

Well, I would only address your requirements from Aspose.Cells perspective for Excel file(s). Well, there is no such APIs or options available in Aspose.Cells for .NET APIs that could directly import the Excel file that is resided on varbinary type of data field in the SQL server database. However, Aspose.Cells does support to read Excel file from streams also, so, if you could get or fetch that Excel file into streams (if possible), then you may use Aspose.Cells APIs to load/read that file for your needs. Please note, you have to use your own .NET code or your own technique to read that file into streams, so you could use the line of code to read that file to create a workbook object (by Aspose.Cells) for it, see the sample line of code:

e.g

Sample code:

’…

‘Your code goes here to fetch/ read the Excel file from the varbinary field from database table.

’…

Dim workbook As Workbook = New Workbook(stream)

Moreover, regarding other file formats, e.g PDF, Doc etc., please use their respective forums (e.g Aspose.Words forum, Aspose.Pdf forum) to post your query there, so they could help you soon.

Let us know if I can be of any further help.

Thank you.

pimentinho · November 20, 2013, 1:47pm

Thank you for your response. What I am needing is to know how to include the excel file as an attachment within the excel file I am creating. Excel allows one to attach documents within a spreadsheet .. so the end user just clicks on the icon and it opens it up.

Does you have that type of capability? if so could you give me some sample code.

I will also post this on the other forums too. thanks.

babar.raza · November 20, 2013, 5:10pm

Hi David,

Please check the source code snippet provided here. It shows how you can embed a PDF file into an Excel file. You can use the same source code to embed an Excel file into another Excel file. All you have to do is to flip the OleFileType to XLSX or XLS while providing the ObjectData of an Excel file. The resultant spreadsheet will contain an icon representing the attachment, and once clicked the attachment will open into its respective application. Please check the output for your reference.

amjad.sahi · November 21, 2013, 1:43am

Hi,

And, please see the document/article on how to embed or extract OLE objects in Excel spreadsheets for further reference:

http://www.aspose.com/docs/display/cellsnet/Managing+OLE+Objects

Thank you.

pimentinho · November 21, 2013, 10:13am

I appreciate everyone’s responses …but none of them are addressing my issue.

In the examples… they are all getting documents stored in a specific folder location …

'Get an excel file path in a variable.

Dim path As String = “d:\test\Book1.xls”
'Get the file into the streams.

fs = File.OpenRead(path)

My issue is that the document is stored within a database table in a field (varbinary(Max)).  I am need the logic to how to read that information and then embed into a spreadsheet. Does that make sense?  How do I read the value … then put it into a stream where it can then be loaded into spreadsheet.   thanks

babar.raza · November 21, 2013, 11:42am

Hi David,

As narrated earlier if you can load your documents in stream objects then you can attach them as Ole Objects by using Aspose.Cells component. We have already shared code snippet exhibiting the usage of Aspose.Cells API to inject PDF files into spreadsheets, whereas similar approach can also be used to embed Excel and Word document files.

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.

pimentinho · December 4, 2013, 12:58pm

I am able to add attachments now from the memory stream .. but I am having problems with certain types of attachments.

When I open the spreadsheet ... there are problems with .xlsx .pptx type of files.
Also, I don't know what olefiletype I should use for .txt .zip file types
when I use .docx and .doc file types .. they don't open MS WORD .. and on the screen they appear very narrow and long.
what is "OleFileType.Unknown" and how do I use it

Here is the code I am using .. perhaps you can let me know if it is a syntax error or what I need to do to fix it. Thanks ..

If AttachmentsView.Table.Rows(_icount).Item(1).ToString = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" Then

AttachmentsSheet.OleObjects(_index).FileType = OleFileType.Xlsx

ElseIf AttachmentsView.Table.Rows(_icount).Item(1).ToString = "application/ms-excel" Then

AttachmentsSheet.OleObjects(_index).FileType = OleFileType.Xls

ElseIf AttachmentsView.Table.Rows(_icount).Item(1).ToString = "application/msexcel" Then

AttachmentsSheet.OleObjects(_index).FileType = OleFileType.Xls

ElseIf AttachmentsView.Table.Rows(_icount).Item(1).ToString = "application/vnd.ms-excel" Then

AttachmentsSheet.OleObjects(_index).FileType = OleFileType.Xls

ElseIf AttachmentsView.Table.Rows(_icount).Item(1).ToString = "application/vnd.openxmlformats-officedocument.presentationml.presentation" Then

AttachmentsSheet.OleObjects(_index).FileType = OleFileType.Pptx

ElseIf AttachmentsView.Table.Rows(_icount).Item(1).ToString = "application/ppt" Then

AttachmentsSheet.OleObjects(_index).FileType = OleFileType.Ppt

ElseIf AttachmentsView.Table.Rows(_icount).Item(1).ToString = "application/msppt" Then

AttachmentsSheet.OleObjects(_index).FileType = OleFileType.Ppt

ElseIf AttachmentsView.Table.Rows(_icount).Item(1).ToString = "application/ms-ppt" Then

AttachmentsSheet.OleObjects(_index).FileType = OleFileType.Ppt

ElseIf AttachmentsView.Table.Rows(_icount).Item(1).ToString = "application/pdf" Then

AttachmentsSheet.OleObjects(_index).FileType = OleFileType.Pdf

ElseIf AttachmentsView.Table.Rows(_icount).Item(1).ToString = "text/plain" Then

AttachmentsSheet.OleObjects(_index).FileType = OleFileType.Unknown

ElseIf AttachmentsView.Table.Rows(_icount).Item(1).ToString = "application/text/javascript" Then

AttachmentsSheet.OleObjects(_index).FileType = OleFileType.Unknown

ElseIf AttachmentsView.Table.Rows(_icount).Item(1).ToString = "application/text/richtext" Then

AttachmentsSheet.OleObjects(_index).FileType = OleFileType.Unknown

ElseIf AttachmentsView.Table.Rows(_icount).Item(1).ToString = "application/vnd.openxmlformats-officedocument.wordprocessingml.document" Then

AttachmentsSheet.OleObjects(_index).FileType = OleFileType.Docx

ElseIf AttachmentsView.Table.Rows(_icount).Item(1).ToString = "application/msword" Then

AttachmentsSheet.OleObjects(_index).FileType = OleFileType.Doc

ElseIf AttachmentsView.Table.Rows(_icount).Item(1).ToString = "application/ms-word" Then

AttachmentsSheet.OleObjects(_index).FileType = OleFileType.Doc

ElseIf AttachmentsView.Table.Rows(_icount).Item(1).ToString = "application/vnd.openxmlformats-officedocument.presentationml.presentation" Then

AttachmentsSheet.OleObjects(_index).FileType = OleFileType.Ppt

ElseIf AttachmentsView.Table.Rows(_icount).Item(1).ToString = "application/zip" Then

AttachmentsSheet.OleObjects(_index).FileType = OleFileType.Unknown

ElseIf AttachmentsView.Table.Rows(_icount).Item(1).ToString = "vnd.visio" Then

AttachmentsSheet.OleObjects(_index).FileType = OleFileType.Unknown

Else

AttachmentsSheet.OleObjects(_index).FileType = OleFileType.Unknown

End If

babar.raza · December 5, 2013, 5:49am

Hi David,

Good to know that you have found a solution for pulling the data from SQL database.

Regarding your recent inquiry, I have tested your presented scenario on my end by converting several document types to memory stream and embedding them in spreadsheets using the Aspose.Cells for .NET. I am afraid, I am unable to replicate the issues mentioned in point 1) and 3).

As we are loading the files from disk and results are fine on our end, so we suspect that the problem could be due to the custom routines that are pulling the data from SQL database and converting it to memory stream. You can further troubleshoot this on your end by fetching the data and saving it to disk by just using the .NET core functionality. If you can open the saved files in their corresponding applications that means the fetched data is in correct format. In this case please share your resultant spreadsheet files generated through Aspose.Cells for .NET API after embedding the DOC/DOCX, PPTX and XLSX files. We will review them in order to assist you further in this matter.

Please note, OleFileType.Unknown can be used with any file type that is not mentioned OleFileType list/enumeration, therefore you can use the same for TXT and ZIP file types.

pimentinho · December 11, 2013, 12:34pm

Thanks for your response … I am unable to write to the disk as all permissions to create a file on the network drive are blocked. I have attached a copy of the spreadsheet … you will see that I have several types of attachments. The ones with the “X” suffix don’t work as Excel generates an error message when the spreadsheet is opened and removes the associated information. Note too that the .doc doesn’t open in Word … the .xls and .pdf open in their respective programs.

I think the code for handling the memory stream is standard (see below) … I think the error is associated with the addind the file type … “OleFileType.Xlsx”

Here is the code that I am creating the spreadsheet … perhaps that syntax is incorrect.

Dim designer As WorkbookDesigner = New WorkbookDesigner()

designer.Workbook.Save(stream, Aspose.Cells.SaveFormat.Xlsx)

HttpContext.Current.Response.ClearContent()

HttpContext.Current.Response.ClearHeaders()

HttpContext.Current.Response.Clear()

HttpContext.Current.Response.Expires = 0

HttpContext.Current.Response.ContentType = “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”

HttpContext.Current.Response.AddHeader(“Content-Disposition”, “attachment; filename=” + _customer.ToString + “" + _description.ToString + "” + _entered + “.xlsx”)

HttpContext.Current.Response.AddHeader(“Content-Length”, stream.Length.ToString)

HttpContext.Current.Response.BinaryWrite(stream.ToArray)

In doing further research, it is only the types that have an X on the end that do not work. When it opens the following error comes up:

“Excel found unreadable content in ‘Test Five - Psu 11.13.13__Testing in Dev__11_13_2013.xlsx’. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click yes.” … Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.

Removed Records: Object from /xl/printerSettings/printerSettings9.bin part (Print options)

I only get this error when adding a mime type with the “X” … ie xlsX … if I remove those types of attachments I don’t receive the error.

Here is some of the additional code I am using.

Dim _data As Byte() = CType(AttachmentsView.Table.Rows(_icount).Item(5), Byte())

Dim _ms As New System.IO.MemoryStream(_data)

'

’ read the memory stream

'

Dim _objectdata(CInt(_ms.Length - 1)) As Byte

_ms.Position = 0

_ms.Read(_objectdata, 0, _objectdata.Length)

_ms.Close()

Dim _index As Integer = AttachmentsSheet.OleObjects.Add(_irow - 1, 3, 25, 25, _imageData)

AttachmentsSheet.OleObjects(_index).ObjectData = _objectdata

'*********************************************

’ set the appropriate file type

If AttachmentsView.Table.Rows(_icount).Item(1).ToString = “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet” Then

AttachmentsSheet.OleObjects(_index).FileType = OleFileType.Xlsx

THE LINE ABOVE IS WHERE I THINK THE ISSUE IS …

If there were problems with the memory stream then the other attachment types wouldn’t be working.

I found another article and tried it but I got an error with “ContentDisposition.Attachment” .

This example built the worksheet as a New Workbook … while I am using " Dim designer As WorkbookDesigner = New WorkbookDesigner()" … I don’t understand the difference between the two methods .

Here is the link to the site:

Different Ways to Save Files|Documentation

[VB.NET]

'Creating an Workbook object
Dim workbook As Workbook = New Workbook(FileFormatType.Xlsx)
'... Your code goes here
'Save in xlsx format and send the file to user so that he may open the file in

'some application or save it to some location

workbook.Save(Me.Response, “Report.xlsx”, ContentDisposition.Attachment, New OoxmlSaveOptions())

Response.End()

I really appreciate your help on this. My boss is getting after me to getting an answer ASAP.

FYI … we are using .net 4.5 and Aspose.cells.dll version 7.5.3.0

thanks again

dave

babar.raza · December 12, 2013, 8:31am

Hi Dave,

First of all, please accept our apologies for a bit delayed response.

I have thoroughly evaluated your presented scenario on my end while using the latest build of Aspose.Cells for .NET 7.6.1.4 (attached). I am able to replicate the problem with DOC file type, as after embedding it in XLSX, DOC file type does not load the word processor to show the contents of the embedded object. A ticket (CELLSNET-42228) has been logged to further investigate the problem cause and to provide a fix (if applicable). Please note, all other types including XLSX and DOCX present expected behavior when embedded in an spreadsheet. I would request you to give the latest version of Aspose.Cells for .NET a try on your end, and see if it makes any difference.

You do not need to use WorkbookDesigner class unless you are using SmartMarkers in your spreadsheet. If not then you should use Workbook class for all other scenarios. Moreover, please set the OleObject.ObjectSourceFullName to any string containing the object’s actual extension. This will also help MS Excel to determine what program should it trigger to load the contents of the embedded object.

amjad.sahi · December 27, 2013, 3:55am

Hi,

Please try our latest version/fix: Aspose.Cells for .NET v7.7.0.3

We have fixed the issue.

Please try the following sample code:
e.g.
Sample code:

string ImageUrl = @"c:\dest.wmf";

//Get the picture into the stream

FileStream fs = File.OpenRead(ImageUrl);

//Define a byte array

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

//Obtain the picture into the array of bytes from stream

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

//Close the stream

fs.Close();

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

string[] files = Directory.GetFiles(@"c:\oles\");

for (int i = 0; i < files.Length; i++)

{

//Get the file into the stream

fs = File.OpenRead(files[i]);

//Define an array of bytes

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

//Store the file from stream

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

//Close the stream

fs.Close();

//Instantiate a new Workbook.

int index = sheet.OleObjects.Add(0 + i * 5, 3, 200, 220, imageData);

sheet.Cells[0 + i * 5, 0].PutValue(files[i]);

workbook.Worksheets[0].OleObjects[index].ObjectData = objectData;

//if (workbook.Worksheets[0].OleObjects[index].FileFormatType == FileFormatType.Unknown)

{

workbook.Worksheets[0].OleObjects[index].ObjectSourceFullName = Path.GetFileName(files[i]);

}

}

Thank you.

aspose.notifier · January 13, 2014, 8:26pm

The issues you have found earlier (filed as CELLSNET-42228) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.