我正在尝试使用Microsoft Office EXCEL INTEROP将C#DataTable动态导出到EXCEL文件(不创建物理文件),并通过asp.net网页通过Response对象下载.我能够使用库生成内存流但不知何故该文件没有通过内存流保存.参考代码如下.您将需要参考DocumentFormat.OpenXml.dll&
WindowsBase.DLL(可以从microsoft站点下载).
任何想法如何解决问题? ? ?
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,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); }