For some time I have used Aspose.Total to generate Excel, Word and PDF documents. These were saved to a file system separate from the .net web site. The folder and file name were saved back into the application so that the documents could be accessed from the .net application.
We no longer store the documents in a file system, but store them in SQL server as a VarBinary(Max) column. This is causing a problem.
The most interesting example is one form where the code first checks if there is an existing “Utilisation” spreadsheet for that particular “customer”. If there is, it displays the data in an Aspose GridWeb. If not, the spreadsheet is generated, stored in SQL and then displayed in the GridWeb from the SQL data row. The data is passed from the SQL database to the GridWeb as a stream.
This works fine and one can see the data correctly displayed in the GridWeb. The the SQL database, looking at the data in the relevant row shows that there is data that has been stored in the VarBinary(max) column.
However, there are other forms that merely download the document from SQL.
In the case of this Excel document I get something like the attached .png. Looking at the bottom of the document one can see that it is an Excel document with phrases like “worksheets/Sheet1.xml”.
So, my logic said "I am saving this thing as an XML file, not as an .xlsx.
However, this is my code to save the document in an SQL table.
Dim myStream As New System.IO.MemoryStream
wb.Save(myStream, Aspose.Cells.SaveFormat.Xlsx)
Dim intDocumentID As Integer = bllDocs.AddWithParent(CustomerID, clsEnum.DocumentType.Utilisation, _
myStream, CustomerID, _
dtTargetDate, UserName, Now)
The routine “AddWithParent” is a very convention business logic layer routine which writes the data away.
The only bit that concerns me in this routine is that the Aspose save “wb.save” outputs either a file system file or a System.IO.Stream.
In AddWithParent there is a routine which converts that stream to a bytearray, with this code:
Private Function ReturnByteArrayFromFileStream(ByVal stream As System.IO.MemoryStream) As Byte()
Dim streamLength As Integer = Convert.ToInt32(stream.Length)
Dim fileData As Byte() = New Byte(streamLength) {}
’ Read the file into a byte array
stream.Read(fileData, 0, streamLength)
stream.Close()
Return fileData
End Function
The code to download the document is:
Protected Sub atvCustomerFlexible1_MenuClicked(MenuItemSelected As String, Node As Telerik.Web.UI.RadTreeNode) Handles atvCustomerFlexible1.MenuClicked
Dim drDoc As IHADALDoc.dsDocuments.tblDo_DocumentsRow = bllDoc.GetRowByIHADocumentID(CInt(Node.Value))
Dim drDocType As IHADALDoc.dsDocuments.tblDo_DocumentTypesRow = bllDocType.GetRowByDocumentTypeID(drDoc.DocumentTypeID)
DownloadFile(drDocType.FileFormat, drDoc.DocumentName, drDoc.FileImage)
End Sub
Protected Sub DownloadFile(FileFormat As String, Name As String, ByVal bytes As Byte())
Dim strContentType As String = bllDocType.ConvertFileFormatToContentType(FileFormat)
Response.Buffer = True
Response.Charset = “”
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.ContentType = strContentType
Response.AddHeader(“content-disposition”, “attachment;filename=” & Name)
Response.BinaryWrite(bytes)
Response.Flush()
Response.End()
End Sub
I have obviously tried a number of varations of Aspose.Excel.SaveFormats and a number of variations on content type. None of them works.
I have similar problems with generated word and pdf documents, where, when I download a word document in Word it comes up with a message that it can’t open a zip file.
Documents generated in genuine word, excel or adobe display fine when they are downloaded using the same code.
Can anyone please suggest where I am going wrong?
Hi,
Aspose.Cells for .NET allows you to save your workbook in System.IO stream which you can then convert to bytes and save those bytes in your SQL server.
Later on, you can retrieve your bytes from SQL server and create a System.IO stream from bytes which you can then use to create a workbook object again.
This whole process is called serialization and deserialization of workbook.
Below is a sample code how to serialize/deserialize workbook object. The code is in C# but you can easily convert it to VB.NET.
C#
public static void TestSerialization()
{
string filePath = @“f:\downloads\test.xls”;
Workbook book = new Workbook(filePath);
byte[] bytesBook = SerializeWorkbook(book);
Workbook newBook = DeSerializeWorkbook(bytesBook);
newBook.Save(filePath + “.out.xls”);
}
public static byte[] SerializeWorkbook(Workbook workbook)
{
//Create a memory stream
MemoryStream ms = new MemoryStream();
//Save the workbook which contains all excel objects
//into memory stream
workbook.Save(ms, SaveFormat.Excel97To2003);
//Read bytes from memory stream
ms.Position = 0;
byte[] bytesWorkbook = new byte[ms.Length];
ms.Read(bytesWorkbook, 0, bytesWorkbook.Length);
return bytesWorkbook;
}
public static Workbook DeSerializeWorkbook(byte[] bytesWorkbook)
{
MemoryStream ms = new MemoryStream();
ms.Write(bytesWorkbook, 0, bytesWorkbook.Length);
Workbook workbook = new Workbook(ms);
return workbook;
}
{
string filePath = @“f:\downloads\test.xls”;
Workbook book = new Workbook(filePath);
byte[] bytesBook = SerializeWorkbook(book);
Workbook newBook = DeSerializeWorkbook(bytesBook);
newBook.Save(filePath + “.out.xls”);
}
public static byte[] SerializeWorkbook(Workbook workbook)
{
//Create a memory stream
MemoryStream ms = new MemoryStream();
//Save the workbook which contains all excel objects
//into memory stream
workbook.Save(ms, SaveFormat.Excel97To2003);
//Read bytes from memory stream
ms.Position = 0;
byte[] bytesWorkbook = new byte[ms.Length];
ms.Read(bytesWorkbook, 0, bytesWorkbook.Length);
return bytesWorkbook;
}
public static Workbook DeSerializeWorkbook(byte[] bytesWorkbook)
{
MemoryStream ms = new MemoryStream();
ms.Write(bytesWorkbook, 0, bytesWorkbook.Length);
Workbook workbook = new Workbook(ms);
return workbook;
}