Free Support Forum - aspose.com

Handling error "Cannot Find Central Directory" by proper usage of MemoryStream with Aspose.Cells for .NET in C#

I have a web application that creates an Excel workbook from application data, then inserts that workbook into an Image field in Sql Server. The files are saved in Sql Server for historical purposes and users are given a menu where they can click a link to open a specified file; however, on .Open(), I get the "Cannot Find Central Directory" error. I get this on both my local host and on my test machine. I am using Aspose.Cells 4.8.2.0. Below is the code I am using:

//Create file
Aspose.Cells.License cl = new License();
cl.SetLicense(HttpContext.Current.Server.MapPath(".") + \\Aspose.Custom.lic.xml);
Aspose.Cells.Workbook file = new Workbook();
Aspose.Cells.Cells workSheet = file.Worksheets[0].Cells;
for (int row = 0; row <= 5; row++)
{
for (int col = 0; col <= 10; col++)
{
workSheet[row, col].PutValue("Row: " + row.ToString() + " Column: " + col.ToString());
}
}
file.Worksheets[0].Name = "My Test SpreadSheet";
file.Worksheets[0].AutoFitColumns();

//(if I open the file here it works, which tells me the file is being created properly)
//file.Save("Test.xlsx", FileFormatType.Excel2007Xlsx, SaveType.OpenInExcel, HttpContext.Current.Response);

//save file to database
MemoryStream ms = new MemoryStream();
file.Save(ms, FileFormatType.Excel2007Xlsx);
byte[] fileBytes = new Byte[ms.Length];
ms.Read(fileBytes, 0, fileBytes.Length);
ms.Close();

string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
string insertQuery = "INSERT INTO MYTESTTABLE \n" +
" (SAVE_DATE, FILE_BYTES, FILE_CONTENTTYPE, FILE_SIZE) \n" +
" VALUES \n" +
" ('2/16/2010', @fileBytes, @contentType, @contentLength)";

SqlConnection conn = this.sqlHR();
SqlCommand cInsert = new SqlCommand(insertQuery, conn);
cInsert.Parameters.AddWithValue("@fileBytes", fileBytes);
cInsert.Parameters.AddWithValue("@contentType", contentType);
cInsert.Parameters.AddWithValue("@contentLength", fileBytes.Length);

conn.Open();
cInsert.ExecuteNonQuery();
cInsert.Dispose();

//retrieve from database
byte[] dbFileBytes = new byte[0];
string retrieveQuery = "SELECT FILE_BYTES, FILE_SIZE, FILE_CONTENTTYPE FROM MYTESTTABLE WHERE SAVE_DATE = '2/16/2010' \n";

SqlCommand cFile = new SqlCommand(retrieveQuery, conn);
SqlDataReader rFile = null;
rFile = cFile.ExecuteReader();
while (rFile.Read() == true)
{
dbFileBytes = ((byte[])rFile["FILE_BYTES"]);
}
rFile.Close();
cFile.Dispose();
conn.Dispose();
conn = null;

//send to client
MemoryStream strDoc = new MemoryStream(dbFileBytes);
Aspose.Cells.Workbook returnWorkbook = new Aspose.Cells.Workbook();
//(the next line is where the error occurs)
returnWorkbook.Open(strDoc, Aspose.Cells.FileFormatType.Excel2007Xlsx, null);
returnWorkbook.Save("Test.xlsx", Aspose.Cells.FileFormatType.Excel2007Xlsx, Aspose.Cells.SaveType.OpenInExcel, HttpContext.Current.Response);
HttpContext.Current.Response.End();

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for sharing the sample code.

We have found your mentioned issue after an initial test. We will look into it and get back to you soon. Your issue has been registered in our internal issue tracking system with issue id CELLSNET-14432.

Thank You & Best Regards,

Thanks for the reply. It looks like I have figured out the issue. Before placing the MemoryStream into a Byte Array, the MemoryStream's pointer must be reset. The code in red should have been added to reset the pointer:

//save file to database
MemoryStream ms = new MemoryStream();
file.Save(ms, FileFormatType.Excel2007Xlsx);
byte[] fileBytes = new Byte[ms.Length];
ms.Seek(0, SeekOrigin.Begin);
ms.Read(fileBytes, 0, fileBytes.Length);
ms.Close();


Or I could have used the MemoryStream's ToArray() method rather than the Read() method. This would not have required the MemoryStream pointer to be reset:

MemoryStream ms = new MemoryStream();
file.Save(ms, FileFormatType.Excel2007Xlsx);
byte[] fileBytes = new Byte[ms.Length];
fileBytes = ms.ToArray();
ms.Close()

Thanks for checking into this.