Hoping someone more knowledgeable with C# can point out where i'm going wrong please.

希望有更了解C#的人能指出我错在哪里。

Being a Powershell scripter and relatively new to C#, I've added a button to my web page that attempts to export the GridView to a .xlsx file and have the browser behave like the file is being downloaded. I had this working for .xls files, but now need to update it for .xlsx export.

作为一名PowerShell脚本编写者和C#的新手，我在我的网页上添加了一个按钮，它试图将GridView导出为.xlsx文件，并使浏览器的行为像下载文件一样。我将其用于.xls文件，但现在需要更新它以用于.xlsx导出。

The GridView is a web view of a SQL table.

GridView是一个SQL表格的Web视图。

I've largely modified code that others have posted online to export a GridView to .xlsx, but have never had it working.

我在很大程度上修改了其他人在网上发布的代码，以将GridView导出为.xlsx，但从未运行过。

Please find the code below:

请找到下面的代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using System.Data;
using System.Web.UI.WebControls;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Bibliography;
using DocumentFormat.OpenXml.Wordprocessing;
using TableCell = System.Web.UI.WebControls.TableCell;
using System.Text;
using System.Runtime.InteropServices.ComTypes;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button1_Click(object sender, EventArgs e)
    {
       ExportGridToExcel();
    }
        private void ExportGridToExcel()
        {
        
            DataTable dt = new DataTable("GridView_Data");
            foreach (TableCell cell in GridView1.HeaderRow.Cells)
            {
                dt.Columns.Add(cell.Text);
            }

            foreach (GridViewRow row in GridView1.Rows)
            {
                dt.Rows.Add();
                for (int i = 0; i < row.Cells.Count; i++)
                {
                    dt.Rows[dt.Rows.Count - 1][i] = row.Cells[i].Text;
                }
            }
            
            string FileName = "DataExport" + DateTime.Now + ".xlsx";
            string FilePath = Path.GetTempPath();
            string FileNameAndPath = "FilePath\\FileName";
            
            using (MemoryStream MyMemoryStream = new MemoryStream())
            //using (var workbook = SpreadsheetDocument.Create(MyMemoryStream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
            using (var workbook = SpreadsheetDocument.Create(MyMemoryStream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))

            {
                var workbookPart = workbook.AddWorkbookPart();

                workbook.WorkbookPart.Workbook = new Workbook
                {
                    Sheets = new Sheets()
                };

                var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                var sheetData = new SheetData();
                sheetPart.Worksheet = new Worksheet(sheetData);

                Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<Sheets>();
                string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                uint sheetId = 1;
                if (sheets.Elements<Sheet>().Count() > 0)
                {
                    sheetId =
                        sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }

                Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = "SoTExport" };
                sheets.Append(sheet);

                Row headerRow = new Row();

                List<String> columns = new List<string>();
                foreach (DataColumn column in dt.Columns)
                {
                    columns.Add(column.ColumnName);

                    Cell cell = new Cell
                    {
                        DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String,
                        CellValue = new CellValue(column.ColumnName)
                    };
                    headerRow.AppendChild(cell);
                }


                sheetData.AppendChild(headerRow);

                foreach (DataRow dsrow in dt.Rows)
                {
                    Row newRow = new Row();
                    foreach (String col in columns)
                    {
                        Cell cell = new Cell
                        {
                            DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String,
                            CellValue = new CellValue(dsrow[col].ToString()) //
                        };
                        newRow.AppendChild(cell);
                    }

                    sheetData.AppendChild(newRow);
                }

                Response.Clear();
                Response.Buffer = true;
                Response.Charset = "";
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;filename=" + FileName);
                workbookPart.Workbook.Save(MyMemoryStream);
                MyMemoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            
            }

            
    }
    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {

    }


    protected void GridView1_SelectedIndexChanged1(object sender, EventArgs e)
    {

    }
}

When the "Export to Excel" button is clicked, it throws an error stating:

Cannot perform a read/write operation in write-only or read-only modes.
Debugging it, the error is thrown when the line below is executed:

当单击“导出到Excel”按钮时，它会抛出一个错误：无法在只写或只读模式下执行读/写操作。调试它时，执行以下行时抛出错误：

MyMemoryStream.WriteTo(Response.OutputStream);

MyMemoryStream.WriteTo(Response.OutputStream)；

I have tried replacing the line above with:

我已尝试将上面的行替换为：

Response.BinaryWrite(MyMemoryStream.ToArray());

Response.BinaryWrite(MyMemoryStream.ToArray())；

but that gives me a 1KB Excel file with no data in and also doesn't give me the "Save As" dialogue box in Edge.

但这给了我一个1KB的Excel文件，里面没有数据，也没有给我Edge中的“另存为”对话框。

I just can't seem to wrap my head around how the data is supposed to work. I can see how the spreadsheet object is getting populated by iterating through the GridView/data table, but can't actually see how the memory stream comes into play or why.

我似乎就是无法理解这些数据应该是如何工作的。我可以通过迭代GridView/Data表来看到电子表格对象是如何填充的，但实际上看不到内存流是如何发挥作用的，或者为什么会发挥作用。

Any help or guidance that can be offered would be greatly appreciated as I'm at my wits end now.

任何可以提供的帮助或指导都将不胜感激，因为我现在已经无计可施了。

Many thanks indeed!

真的非常感谢！

I have tried various other suggestions I have seen online, but as they didn't work, I ended up deleting those lines.
What I expect or hope to happen is that the data table is exported to a .xlsx file, with the data being identical to the data table on the .aspx web page (which is a SQL table) and that the browser gives the Save As dialogue box so that the user can save it where they need to, rather than an automatic download.

我尝试了网上看到的其他各种建议，但由于它们都不起作用，我最终删除了这些行。我期望或希望发生的是，数据表导出为.xlsx文件，其中的数据与.aspx网页上的数据表(这是一个SQL表)相同，并且浏览器提供了另存为对话框，以便用户可以将其保存在他们需要的位置，而不是自动下载。

If I have understood your question correctly, your grid is already loaded on the .aspx page and you are trying to download the data from GridView1 at the click of a button, which calls the ExportGridToExcel() method.
Can you please try the code below, you will have to set the the path and let me know if this solves your problem:

如果我没有理解错您的问题，那么您的网格已经加载到.aspx页面上，并且您正试图通过单击一个按钮从GridView1下载数据，该按钮调用ExportGridToExcel()方法。你能试一下下面的代码吗，你必须设置路径，然后让我知道这是否解决了你的问题：

private void ExportGridToExcel()  
{  
   Response.Clear();  
   Response.Buffer = true;  
   Response.ClearContent();  
   Response.ClearHeaders();  
   Response.Charset = "";  
   string FileName ="SoTDataExport"+DateTime.Now+".xlsx";  
   StringWriter stringWriter = new StringWriter();  
   HtmlTextWriter htmltextwrtter = new HtmlTextWriter(stringWriter);        
   Response.Cache.SetCacheability(HttpCacheability.NoCache);  
   Response.ContentType ="application/vnd.ms-excel";    
   Response.AddHeader("Content-Disposition","attachment;filename=" + FileName);  
   GridView1.GridLines = GridLines.Both;  
   GridView1.HeaderStyle.Font.Bold = true;  
   GridView1.RenderControl(htmltextwrtter);  
   Response.Write(stringWriter.ToString());  
   Response.End();
}