Download the EXCEL file from the ASP.NET page without creating a physical file on the server (On The Fly)

Question

I am trying to export a C # DataTable to an EXCEL file on the fly (without creating a physical file) using Microsoft Office EXCEL INTEROP and load it through the asp.net webpage through the Response object. I can generate memystream using the library But somehow the file is not saved in the memory stream. The link code is given below. You will need to take a link to DocumentFormat.OpenXml.dll and WindowsBase.DLL (which you can download from the Microsoft website).

Any idea how to solve the problem ??

Private void DownloadFile() { DataSet objTable = ReadTableFromViewstate(); if (objTable != null && objTable.Rows.Count > 0) { string strDownloadableFilename = "TestExcelFileName.xls"; MemoryStream fs1 = new MemoryStream(); if (CreateExcelFile.CreateExcelDocument(objTable, fs1)) { Response.Clear(); byte[] data1 = new byte[fs1.Length]; fs1.Read(data1, 0, data1.Length); fs1.Close(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", strDownloadableFilename)); Response.BinaryWrite(data1); ; Response.End(); } else { LblErrorMessage.Text = "Error Exporting File."; } } } 

..

 public static bool CreateExcelDocument(DataSet ds, System.IO.Stream excelFileStream) { try { using (SpreadsheetDocument document = SpreadsheetDocument.Create(excelFileStream, SpreadsheetDocumentType.Workbook)) { CreateParts(ds, document); } Trace.WriteLine("Successfully created: " + excelFileStream); return true; } catch (Exception ex) { Trace.WriteLine("Failed, exception thrown: " + ex.Message); return false; } } .. private static void CreateParts(DataSet ds, SpreadsheetDocument document) { WorkbookPart workbookPart = document.AddWorkbookPart(); Workbook workbook = new Workbook(); workbookPart.Workbook = workbook; // If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file ! WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles"); Stylesheet stylesheet = new Stylesheet(); workbookStylesPart.Stylesheet = stylesheet; Sheets sheets = new Sheets(); // Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each. uint worksheetNumber = 1; foreach (DataTable dt in ds.Tables) { // For each worksheet you want to create string workSheetID = "rId" + worksheetNumber.ToString(); string worksheetName = dt.TableName; WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>(workSheetID); WriteDataTableToExcelWorksheet(dt, worksheetPart); Sheet sheet = new Sheet() { Name = worksheetName, SheetId = (UInt32Value)worksheetNumber, Id = workSheetID }; sheets.Append(sheet); worksheetNumber++; } workbook.Append(sheets); } 

Answer 1

Thanks to everyone. Finally I got a solution to my problem. I just replaced the following line of code:

  byte[] data1 = new byte[fs1.Length]; fs1.Read(data1, 0, data1.Length); fs1.Close(); 

with this line

  byte[] data1 = fs1.ToArray(); 

and my problem is resolved.

Answer 2

After writing to the memory stream is complete, you need to return the pointer to its beginning before reading it.

 fs1.Seek(0, SeekOrigin.Begin);