C# 如何在单击按钮时立即将 dataGridView 数据导出到 Excel？

Question

提问by Jake

I have 10k rows and 15 column in my data grid view. I want to export this data to an excel sheet o button click. I have already tried with the below code.

我的数据网格视图中有 10k 行和 15 列。我想将此数据导出到 Excel 表 o 按钮单击。我已经尝试过下面的代码。

private void btExport_Click(object sender, EventArgs e)
    {
        Microsoft.Office.Interop.Excel._Application app  = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel._Workbook workbook =  app.Workbooks.Add(Type.Missing);        
        Microsoft.Office.Interop.Excel._Worksheet worksheet = null;                   
        app.Visible = true;
        worksheet = workbook.Sheets["Sheet1"];
        worksheet = workbook.ActiveSheet;                  
        for(int i=1;i<dataGridView1.Columns.Count+1;i++)
        {
             worksheet.Cells[1, i] = dataGridView1.Columns[i-1].HeaderText;
        }    
        for (int i=0; i < dataGridView1.Rows.Count-1 ; i++)
        {
            for(int j=0;j<dataGridView1.Columns.Count;j++)
            {
                if (dataGridView1.Rows[i].Cells[j].Value != null)
                {
                    worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                }
                else
                {
                    worksheet.Cells[i + 2, j + 1] = "";
                }
            }
        }
    }

This is working for me but it is taking lots of time tocomplete exporting process.

这对我有用，但完成导出过程需要花费大量时间。

Is it possible to export from dataGridView (with 10k rows)to excel instantly on a button click?

是否可以从 dataGridView（具有 10k 行）导出以在单击按钮时立即表现出色？

Other than this, when I tried copy all dataGridview contents to clip board and then paste it to excel sheet manually, it happen almost instantly.

除此之外，当我尝试将所有 dataGridview 内容复制到剪贴板，然后手动将其粘贴到 Excel 表时，它几乎立即发生。

So is there a way to copy all dataGridView cells to clip board and paste it to excel sheet(with cell formatting) on a button click?

那么有没有办法将所有 dataGridView 单元格复制到剪贴板并通过单击按钮将其粘贴到 Excel 工作表（带有单元格格式）？

I have code for copy to clipboard as below, but I don't know how to paste it in to a new excel sheet by opening it.

我有复制到剪贴板的代码，如下所示，但我不知道如何通过打开将其粘贴到新的 Excel 工作表中。

        private void copyAllToolStripMenuItem_Click(object sender, EventArgs e)
    {
        dataGridView1.SelectAll();
        DataObject dataObj = dataGridView1.GetClipboardContent();
        if (dataObj != null)
            Clipboard.SetDataObject(dataObj);
    }

Please help with an example. I am new to C#.

请帮忙举个例子。我是 C# 的新手。

Answer 1

采纳答案by Jake

I solved this by simple copy and paste method. I don't know it is the best way to do this but,for me it works good and almost instantaneously. Here is my code.

我通过简单的复制和粘贴方法解决了这个问题。我不知道这是最好的方法，但是，对我来说，它效果很好，而且几乎是即时的。这是我的代码。

    private void copyAlltoClipboard()
    {
        dataGridView1.SelectAll();
        DataObject dataObj = dataGridView1.GetClipboardContent();
        if (dataObj != null)
            Clipboard.SetDataObject(dataObj);
    }
    private void button3_Click_1(object sender, EventArgs e)
    {
        copyAlltoClipboard();
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;
        xlexcel = new Excel.Application();
        xlexcel.Visible = true;
        xlWorkBook = xlexcel.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
        CR.Select();
        xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);          
    }

Thanks.

谢谢。

Answer 2

回答by Vadim

Interop is slow and has other issues, using the the clipboard seems non extensible. Here are two other ways to do this

互操作很慢并且有其他问题，使用剪贴板似乎不可扩展。这里有另外两种方法可以做到这一点

Work with Excel 2007+ files directly instead of working with Excel, it'll be much (much) faster. You can use OpenXML (http://openxmldeveloper.org/) which is Microsoft's SDK. The best way to learn OpenXML is to download the Productivity tool (http://www.microsoft.com/en-us/download/details.aspx?id=5124), ittakes an existing file and generates the code required to create it. Another, perhaps simpler, option is to use ClosedXML (http://closedxml.codeplex.com/). It seems a lot easier to use (look at the example http://closedxml.codeplex.com/wikipage?title=Showcase&referringTitle=Home), but I have no experience with it. I'm sure there are other libraries that wrap work with Excel.
Work with excel via OLEDB. This allows you to work with Excel as if it's a dababase. See http://www.codeproject.com/Articles/8500/Reading-and-Writing-Excel-using-OLEDBor Performance of OLEDB to read Excelfor examples and more details.

直接使用 Excel 2007+ 文件而不是使用 Excel，它会更快（更快）。您可以使用Microsoft 的 SDK 的OpenXML ( http://openxmldeveloper.org/)。学习 OpenXML 的最佳方法是下载生产力工具 ( http://www.microsoft.com/en-us/download/details.aspx?id=5124)，它需要一个现有文件并生成创建它所需的代码. 另一种可能更简单的选择是使用 ClosedXML ( http://closedxml.codeplex.com/)。它似乎更容易使用（查看示例http://closedxml.codeplex.com/wikipage?title=Showcase&referringTitle=Home），但我没有使用它的经验。我确定还有其他库可以与 Excel 一起工作。
通过 OLEDB 使用 excel。这使您可以像使用数据库一样使用 Excel。有关示例和更多详细信息，请参阅http://www.codeproject.com/Articles/8500/Reading-and-Writing-Excel-using-OLEDB或OLEDB 的性能以读取 Excel。

I'd start with ClosedXML.

我会从 ClosedXML 开始。

Answer 3

回答by Loubna H

that's what i use for my gridview, try to use it for yr data , it works perfectly :

这就是我用于 gridview 的内容，尝试将它用于 yr 数据，它完美运行：

        GridView1.AllowPaging = false;
        GridView1.DataBind();

        StringBuilder sb = new StringBuilder();

        for (int k = 0; k < GridView1.Columns.Count; k++)
        {
            //add separator
            sb.Append(GridView1.Columns[k].HeaderText+";");

        }


        //append new line
        sb.Append("\r\n");
        for (int i = 0; i < GridView1.Rows.Count; i++)
        {
            for (int k = 0; k < GridView1.Columns.Count; k++)
            {
                sb.Append(GridView1.Rows[i].Cells[k].Text+";");
            }
            sb.AppendLine();
        }

Answer 4

回答by jlh3302

I like Jake's solution. The problem with no header is resolved by doing the following

我喜欢Hyman的解决方案。通过执行以下操作解决没有标题的问题

xlWorkSheet.Cells[1, 1] = "Header 1";
xlWorkSheet.Cells[1, 2] = "Header 2";
xlWorkSheet.Cells[1, 3] = "Header 3";

of course this only works is you know what the headers should be ahead of time.

当然，这只适用于您提前知道标题应该是什么。

Answer 5

回答by alex.pulver

This answer is for the first question, why it takes so much time and it offers an alternative solution for exporting the DataGridView to Excel.

这个答案是针对第一个问题，为什么需要这么多时间，它提供了将 DataGridView 导出到 Excel 的替代解决方案。

MS Office Interop is slow and even Microsoft does not recommend Interop usage on server side and cannot be use to export large Excel files. For more details see why not to use OLE Automationfrom Microsoft point of view.

MS Office Interop 很慢，甚至 Microsoft 也不建议在服务器端使用 Interop，并且不能用于导出大型 Excel 文件。有关更多详细信息，请参阅从 Microsoft 的角度来看为什么不使用 OLE 自动化。

Interop saves Excel files in XLS file format (old Excel 97-2003 file format) and the support for Office 2003 has ended. Microsoft Excel released XLSX file format with Office 2007 and recommends the usage of OpenXML SDK instead of Interop. But XLSX files are not really so fast and doesn't handle very well large Excel files because they are based on XML file format. This is why Microsoft also released XLSB file format with Office 2007, file format that is recommended for large Excel files. It is a binary format. So the best and fastest solution is to save XLSB files.

Interop 将 Excel 文件保存为 XLS 文件格式（旧的 Excel 97-2003 文件格式），并且已结束对 Office 2003 的支持。Microsoft Excel 在 Office 2007 中发布了 XLSX 文件格式，并推荐使用 OpenXML SDK 而不是 Interop。但是 XLSX 文件并不是那么快，并且不能很好地处理大型 Excel 文件，因为它们基于 XML 文件格式。这就是 Microsoft 还在 Office 2007 中发布 XLSB 文件格式的原因，该文件格式推荐用于大型 Excel 文件。它是一种二进制格式。所以最好和最快的解决方案是保存 XLSB 文件。

You can use this C# Excel libraryto save XLSB files, but it also supports XLS and XLSX file formats.

您可以使用这个C# Excel 库来保存 XLSB 文件，但它也支持 XLS 和 XLSX 文件格式。

See the following code sample as alternative of exporting DataGridView to Excel:

请参阅以下代码示例作为将 DataGridView 导出到 Excel 的替代方法：

// Create a DataSet and add the DataTable of DataGridView 
DataSet dataSet = new DataSet();
dataSet.Tables.Add((DataTable)dataGridView);
//or ((DataTable)dataGridView.DataSource).Copy() to create a copy

// Export Excel file 
ExcelDocument workbook = new ExcelDocument();
workbook.easy_WriteXLSBFile_FromDataSet(filePath, dataSet, 
     new EasyXLS.ExcelAutoFormat(EasyXLS.Constants.Styles.AUTOFORMAT_EASYXLS1), 
     "Sheet1");

If you also need to export the formatting of the DataGridView check this code sample on how to export datagridview to Excel in C#.

如果您还需要导出 DataGridView 的格式，请查看此代码示例，了解如何在 C# 中将 datagridview 导出到 Excel。

Answer 6

回答by Cornelius

This is a great question and I was surprised at how difficult it was to find a clear and complete answer, most of the answers I found were either sudo-code or not 100% complete.

这是一个很好的问题，我很惊讶找到一个清晰完整的答案是多么困难，我找到的大多数答案要么是 sudo 代码，要么不是 100% 完整。

I was able to create a complete solution to copy and save the data from my DataGridView to an excel file based on Jake's answer so I'm posting my complete solution in the hopes that it can help other new comers to c# like myself :)

我能够创建一个完整的解决方案，以根据 Jake 的回答将数据从我的 DataGridView 复制并保存到 excel 文件中，因此我发布了我的完整解决方案，希望它可以帮助其他像我一样使用 c# 的新人:)

First off, you will need the Microsoft.Office.Interop.Excelreference in your project. See MSDNon how to add it.

首先，您将需要Microsoft.Office.Interop.Excel项目中的参考。有关如何添加它，请参阅MSDN。

My Code:

我的代码：

using Excel = Microsoft.Office.Interop.Excel;

private void btnExportToExcel_Click(object sender, EventArgs e)
{
    SaveFileDialog sfd = new SaveFileDialog();
    sfd.Filter = "Excel Documents (*.xls)|*.xls";
    sfd.FileName = "Inventory_Adjustment_Export.xls";
    if (sfd.ShowDialog() == DialogResult.OK)
    {
        // Copy DataGridView results to clipboard
        copyAlltoClipboard();

        object misValue = System.Reflection.Missing.Value;
        Excel.Application xlexcel = new Excel.Application();

        xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts
        Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
        Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        // Format column D as text before pasting results, this was required for my data
        Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells;
        rng.NumberFormat = "@";

        // Paste clipboard results to worksheet range
        Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
        CR.Select();
        xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);

        // For some reason column A is always blank in the worksheet. ˉ\_(ツ)_/ˉ
        // Delete blank column A and select cell A1
        Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells;
        delRng.Delete(Type.Missing);
        xlWorkSheet.get_Range("A1").Select();

        // Save the excel file under the captured location from the SaveFileDialog
        xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlexcel.DisplayAlerts = true;
        xlWorkBook.Close(true, misValue, misValue);
        xlexcel.Quit();

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlexcel);

        // Clear Clipboard and DataGridView selection
        Clipboard.Clear();
        dgvItems.ClearSelection();

        // Open the newly saved excel file
        if (File.Exists(sfd.FileName))
            System.Diagnostics.Process.Start(sfd.FileName);
    }
}

private void copyAlltoClipboard()
{
    dgvItems.SelectAll();
    DataObject dataObj = dgvItems.GetClipboardContent();
    if (dataObj != null)
        Clipboard.SetDataObject(dataObj);
}

private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        MessageBox.Show("Exception Occurred while releasing object " + ex.ToString());
    }
    finally
    {
        GC.Collect();
    }
}

Answer 7

回答by Taran

The Best is use use closedxml.codeplex.com Library.Refer it @https://closedxml.codeplex.com/wikipage?title=Adding%20DataTable%20as%20Worksheet&referringTitle=Documentation

最好是使用 closedxml.codeplex.com 库。参考它@ https://closedxml.codeplex.com/wikipage?title=Adding%20DataTable%20as%20Worksheet&referringTitle=Documentation

var wb = new ClosedXML.Excel.XLWorkbook();
DataTable dt = GetTheDataTable();//Refer documentation


wb.Worksheets.Add(dt);

Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=\"FileName.xlsx\"");

using (var ms = new System.IO.MemoryStream()) {
    wb.SaveAs(ms);
    ms.WriteTo(Response.OutputStream);
    ms.Close();
}

Response.End();

Answer 8

回答by Koray

using Excel = Microsoft.Office.Interop.Excel;


private void btnExportExcel_Click(object sender, EventArgs e)
{
    try
    {
        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        excel.Visible = true;
        Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(System.Reflection.Missing.Value);
        Microsoft.Office.Interop.Excel.Worksheet sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
        int StartCol = 1;
        int StartRow = 1;
        int j = 0, i = 0;

        //Write Headers
        for (j = 0; j < dgvSource.Columns.Count; j++)
        {
            Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[StartRow, StartCol + j];
            myRange.Value2 = dgvSource.Columns[j].HeaderText;
        }

        StartRow++;

        //Write datagridview content
        for (i = 0; i < dgvSource.Rows.Count; i++)
        {
            for (j = 0; j < dgvSource.Columns.Count; j++)
            {
                try
                {
                    Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[StartRow + i, StartCol + j];
                    myRange.Value2 = dgvSource[j, i].Value == null ? "" : dgvSource[j, i].Value;
                }
                catch
                {
                    ;
                }
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}

Answer 9

回答by Рахул Маквана

I did not intend to steal @Jake and @Cornelius's answer, so i tried editing it. but it was rejected. Anyways, the only improvement I have to point out is about avoiding extra blank column in excel after paste. Adding one line dataGridView1.RowHeadersVisible = false;hides so called "Row Header" which appears on the left most part of DataGridView, and so it is not selected and copied to clipboard when you do dataGridView1.SelectAll();

我无意窃取@Jake 和@Cornelius 的答案，所以我尝试对其进行编辑。但它被拒绝了。无论如何，我必须指出的唯一改进是在粘贴后避免在 excel 中出现额外的空白列。添加一行dataGridView1.RowHeadersVisible = false;隐藏了所谓的“行标题”，它出现在 DataGridView 的最左侧，因此在您执行此操作时不会将其选中并复制到剪贴板dataGridView1.SelectAll();

private void copyAlltoClipboard()
    {
        //to remove the first blank column from datagridview
        dataGridView1.RowHeadersVisible = false;
        dataGridView1.SelectAll();
        DataObject dataObj = dataGridView1.GetClipboardContent();
        if (dataObj != null)
            Clipboard.SetDataObject(dataObj);
    }
    private void button3_Click_1(object sender, EventArgs e)
    {
        copyAlltoClipboard();
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;
        xlexcel = new Excel.Application();
        xlexcel.Visible = true;
        xlWorkBook = xlexcel.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
        CR.Select();
        xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);          
    }

Answer 10

回答by George

This line works only for the DataGridView Control on Windows Forms:

此行仅适用于 Windows 窗体上的 DataGridView 控件：

DataObject dataObj = dataGridView1.GetClipboardContent();

This one addresses the same issue, but for the DataGrid control for the WPF Framework:

这个解决了同样的问题，但对于 WPF 框架的 DataGrid 控件：

    private void copyDataGridContentToClipboard()
    {
        datagridGrupeProductie.SelectAll();
        datagridGrupeProductie.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;

        ApplicationCommands.Copy.Execute(null, datagridGrupeProductie);
        datagridGrupeProductie.UnselectAll();
    }


    private void rightClickGrupeProductie_Click(object sender, RoutedEventArgs e)
    {
        copyDataGridContentToClipboard();
        Microsoft.Office.Interop.Excel.Application excelApp;
        Microsoft.Office.Interop.Excel.Workbook excelWkbk;
        Microsoft.Office.Interop.Excel.Worksheet excelWksht;
        object misValue = System.Reflection.Missing.Value;
        excelApp = new Microsoft.Office.Interop.Excel.Application();
        excelApp.Visible = true;
        excelWkbk = excelApp.Workbooks.Add(misValue);
        excelWksht = (Microsoft.Office.Interop.Excel.Worksheet)excelWkbk.Worksheets.get_Item(1);
        Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)excelWksht.Cells[1, 1];
        CR.Select();
        excelWksht.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
    }

C# 如何在单击按钮时立即将 dataGridView 数据导出到 Excel？

提问by Jake

采纳答案by Jake

回答by Vadim

回答by Loubna H

回答by jlh3302

回答by alex.pulver

回答by Cornelius

回答by Taran

回答by Koray

回答by Рахул Маквана

回答by George

相关推荐

最近更新

标签

C# 如何在单击按钮时立即将 dataGridView 数据导出到 Excel？

提问by Jake

采纳答案by Jake

回答by Vadim

回答by Loubna H

回答by jlh3302

回答by alex.pulver

回答by Cornelius

回答by Taran

回答by Koray

回答by Рахул Маквана

回答by George

相关推荐

使用 C# 对象类型的动态转换

C# 如何在不知道字符串中包含哪些标签的情况下从字符串中删除所有 HTML 标签？

C# 仅基于表的一个字段在 Linq 中不同

如何在 C# windows 窗体的文本框中接受日期格式的值？

相关推荐

最近更新

标签