C# 将 DataGridView 导出到 Excel 的简单方法

Simple way to Export DataGridView to Excel

提问by user1632718

I am trying to copy DataGridView data to Excel and I am using this code:

我正在尝试将 DataGridView 数据复制到 Excel，我正在使用以下代码：

public static void ExportToExcel(DataGridView dgView)
{
    Microsoft.Office.Interop.Excel.Application excelApp = null;

    try
    {
        // instantiating the excel application class
        object misValue = System.Reflection.Missing.Value;
        excelApp = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook currentWorkbook = excelApp.Workbooks.Add(Type.Missing);
        Microsoft.Office.Interop.Excel.Worksheet currentWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)currentWorkbook.ActiveSheet;

        currentWorksheet.Columns.ColumnWidth = 18;

        if (dgView.Rows.Count > 0)
        {
            currentWorksheet.Cells[1, 1] = DateTime.Now.ToString("s");
            int i = 1;

            foreach (DataGridViewColumn dgviewColumn in dgView.Columns)
            {
                // Excel work sheet indexing starts with 1
                currentWorksheet.Cells[2, i] = dgviewColumn.Name;
                ++i;
            }

            Microsoft.Office.Interop.Excel.Range headerColumnRange = currentWorksheet.get_Range("A2", "G2");
            headerColumnRange.Font.Bold = true;
            headerColumnRange.Font.Color = 0xFF0000;

            //headerColumnRange.EntireColumn.AutoFit();
            int rowIndex = 0;

            for (rowIndex = 0; rowIndex < dgView.Rows.Count; rowIndex++)
            {
                DataGridViewRow dgRow = dgView.Rows[rowIndex];

                for (int cellIndex = 0; cellIndex < dgRow.Cells.Count; cellIndex++)
                {
                    currentWorksheet.Cells[rowIndex + 3, cellIndex + 1] = dgRow.Cells[cellIndex].Value;
                }
            }

            Microsoft.Office.Interop.Excel.Range fullTextRange = currentWorksheet.get_Range("A1", "G" + (rowIndex + 1).ToString());
            fullTextRange.WrapText = true;
            fullTextRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
        }
        else
        {
            string timeStamp = DateTime.Now.ToString("s");
            timeStamp = timeStamp.Replace(':', '-');
            timeStamp = timeStamp.Replace("T", "__");
            currentWorksheet.Cells[1, 1] = timeStamp;
            currentWorksheet.Cells[1, 2] = "No error occured";
        }

        using (SaveFileDialog exportSaveFileDialog = new SaveFileDialog())
        {
            exportSaveFileDialog.Title = "Select Excel File";
            exportSaveFileDialog.Filter = "Microsoft Office Excel Workbook(*.xlsx)|*.xlsx";

            if (DialogResult.OK == exportSaveFileDialog.ShowDialog())
            {
                string fullFileName = exportSaveFileDialog.FileName;
                // currentWorkbook.SaveCopyAs(fullFileName);
                // indicating that we already saved the workbook, otherwise call to Quit() will pop up
                // the save file dialogue box

                currentWorkbook.SaveAs(fullFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, System.Reflection.Missing.Value, misValue, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, misValue, misValue, misValue);
                currentWorkbook.Saved = true;
                MessageBox.Show("Exported successfully", "Exported to Excel", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally
    {
        if (excelApp != null)
        {
            excelApp.Quit();
        }
    }
}

But as there is over 200 000 records, its taking a long time to export. Is there a faster way to do this?

但由于有超过 200 000 条记录，导出需要很长时间。有没有更快的方法来做到这一点？

采纳答案by sajanyamaha

Try this code. It's faster than the normal interop methods, also it converts into CSV which can be read easily by excel.

试试这个代码。它比普通的互操作方法更快，而且它可以转换为 CSV 格式，可以通过 excel 轻松读取。

int cols;
//open file 
StreamWriter wr = new StreamWriter("GB STOCK.csv");

//determine the number of columns and write columns to file 
cols = dgvStock.Columns.Count;
for (int i = 0; i < cols - 1; i++)
{ 
    wr.Write(dgvStock.Columns[i].Name.ToString().ToUpper() + ",");
} 
wr.WriteLine();

//write rows to excel file
for (int i = 0; i < (dgvStock.Rows.Count - 1); i++)
{ 
    for (int j = 0; j < cols; j++)
    { 
        if (dgvStock.Rows[i].Cells[j].Value != null)
        {
            wr.Write(dgvStock.Rows[i].Cells[j].Value + ",");
        }
        else 
        {
            wr.Write(",");
        }
    }

    wr.WriteLine();
}

//close file
wr.Close();

回答by Jquery

How to export GridView data to excel in asp.net in C#. more details click here

如何在 C# 中将 GridView 数据导出到 asp.net 中的 Excel。更多详情请点击这里