直接在磁盘上打开XML导出数据表到excel

电子表格文档。Create接受一个流、字符串或包作为它的第一个参数，所以我们可以使用MemoryStream在内存中创建工作簿并返回一个字节数组。

应该是这样的:

public byte[] ExportToCSVFileOpenXML(DataTable dt)
    {
        byte[] returnBytes = null;
        using (MemoryStream mem = new MemoryStream())
        {
            var workbook = SpreadsheetDocument.Create(mem, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
            // your code
            workbook.WorkbookPart.Workbook.Save();
            workbook.Close();
            returnBytes = mem.ToArray();
        }
        return returnBytes;
    }

一旦你有了一个字节数组作为文件传递它应该是很容易的。

如果你使用的是MVC那么在你的控制器中应该是这样的:

return File(ExportToCSVFileOpenXML(aTable), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "export.xlsx");

Alex的回答引导我找到正确的答案。

我做了以下的事情:

private byte[] ExportToCSVFileOpenXML(DataTable dt)
    {
        DataSet ds = new DataSet();
        DataTable dtCopy = new DataTable();
        dtCopy = dt.Copy();
        ds.Tables.Add(dtCopy);
        try 
        {
            byte[] returnBytes = null;
            MemoryStream mem = new MemoryStream();
            var workbook = SpreadsheetDocument.Create(mem, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
            {
                var workbookPart = workbook.AddWorkbookPart();
                workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
                workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
                foreach (System.Data.DataTable table in ds.Tables)
                {
                    var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                    var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                    sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
                    DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                    string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
                    uint sheetId = 1;
                    if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
                    {
                        sheetId =
                            sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                    }
                    DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
                    sheets.Append(sheet);
                    DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                    List<String> columns = new List<string>();
                    foreach (System.Data.DataColumn column in table.Columns)
                    {
                        columns.Add(column.ColumnName);
                        DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                        cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                        headerRow.AppendChild(cell);
                    }

                    sheetData.AppendChild(headerRow);
                    foreach (System.Data.DataRow dsrow in table.Rows)
                    {
                        DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                        foreach (String col in columns)
                        {
                            DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                            cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                            cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
                            newRow.AppendChild(cell);
                        }
                        sheetData.AppendChild(newRow);
                    }
                }
            }
            workbook.WorkbookPart.Workbook.Save();
            workbook.Close();
            returnBytes = mem.ToArray();
            return returnBytes;
                }
        catch (Exception)
        {
            throw;
        }
    }

调用这个函数为:

protected void hlInCorrectRecords_Click(object sender, EventArgs e)
    {
        lblmsg.Text = "";
        DataSet dsDtUploadedSummary = (DataSet)ViewState["dsDtUploadedSummary"];
        if (dsDtUploadedSummary.Tables.Count > 0)
        {
            DataTable dtFreshRecords = dsDtUploadedSummary.Tables[4];
            if (dtFreshRecords.Rows.Count > 0 && dtFreshRecords != null)
            {                
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                string filename = @"IncorrectRecordsUploaded_" + DateTime.Now.ToString();                
                Response.AddHeader("Content-Disposition", "inline;filename=" + filename.Replace("/", "").Replace(":", "")+".xlsx");
                Response.BinaryWrite(ExportToCSVFileOpenXML(dtFreshRecords));                                
                Response.Flush();
                Response.End();
            }
            else
            {
                lblmsg.Text = "No Data To Export";
            }
        }
        else
        {
            lblmsg.Text = "No Data To Export";
        }
    }

谢谢亚历克斯。我使用普通的ASP。. NET not MVC