关于winfrom中如何快速导出DataGridView数据到excel中的问题

这两天一直在写一个售后服务的项目，其中有一个功能是导出DataGridView的数据到Excel中，期间尝试了很多种方法导出，结果都不是很理想，不是速度非常慢就是很卡，导出一个一千多行的数据结果需要5 6分钟，这当然是用户不可能接受的，所以经过我反复测试，发现了一个快速导出的好方法，在此分享出来。以下是常规的方法，最后一个是最快的方法。

常规的VB方法，虽然代码量少，但是不太理想，比如导出时间很慢。

  Function LeadToExcel(ByVal dgv As DataGridView) As Boolean    '导出为Excel函数
        '建立Excel连接
        ' Dim myExcel As Excel.Application = New Microsoft.Office.Interop.Excel.Application()
        Dim myExcel = CreateObject("Excel.Application")
        myExcel.Application.Workbooks.Add(True)
        myExcel.Visible = True
        '定义循环变量，行，列变量
        Dim intRow As Integer
        Dim intColumn As Integer
        Dim intTitle As Integer
        '添加表头
        For intTitle = 0 To dgv.ColumnCount - 1
            myExcel.Cells(1, intTitle + 1) = dgv.Columns(intTitle).HeaderText
        Next intTitle
        For intRow = 0 To dgv.RowCount - 1
            For intColumn = 0 To dgv.ColumnCount - 1
                '由于第一行是表头，所以添加数据时：
                'Excel中从第二行，第一列（2，1）开始赋值，
                'DataGridView从第一行，第一列（0，0）开始赋值（index 从0，0开始）
                myExcel.Cells(intRow + 2, intColumn + 1) = dgv(intColumn, intRow).Value.ToString
            Next intColumn
        Next intRow
        Return True     '添加完毕，返回true
    End Function

用流的方式导出，虽然也比较快，但是导出的文件总是提示，格式不正确，但依然能打开，只是编辑后只能另存为。

 Private Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click
        Try
            Dim path As String = ""
            Dim saveDialog As SaveFileDialog = New SaveFileDialog()
            saveDialog.DefaultExt = ".xls"
            saveDialog.Filter = "Excel File(*.xls)|*.xlsx|All Files(*.*)|*.*"
            Dim DR As DialogResult = saveDialog.ShowDialog()
            path = saveDialog.FileName
            If DR = DialogResult.OK Then
                Dim sw As StreamWriter = New StreamWriter(path, False, Encoding.UTF8)
                Dim sb As StringBuilder = New StringBuilder()
                For k = 0 To DataGridView_Report.ColumnCount - 1 Step 1
                    If DataGridView_Report.Columns(k).Visible = True Then
                        sb.Append(DataGridView_Report.Columns(k).HeaderText.ToString.Trim() & "," & vbTab)
                    End If
                Next
                sb.Append(Environment.NewLine)
                For i = 0 To DataGridView_Report.Rows.Count - 2 Step 1
                    System.Windows.Forms.Application.DoEvents()
                    For j = 0 To DataGridView_Report.Columns.Count - 1 Step 1
                        If DataGridView_Report.Columns(j).Visible = True Then
                            sb.Append(DataGridView_Report.Rows(i).Cells(j).Value.ToString.Trim() & "," & vbTab)
                            If j = DataGridView_Report.Columns.Count - 1 Then
                                sb.Append(Environment.NewLine)
                            End If
                        End If
                    Next
                Next
                sb.Append(Environment.NewLine)
                sw.Write(sb.ToString())
                sw.Flush()
                sw.Close()
                MessageBox.Show(path + "，导出成功", "系统提示", MessageBoxButtons.OK)
            Else
                Exit Sub
            End If
        Catch ex As Exception
            MsgBox("错误信息：" + ex.Message, MsgBoxStyle.OkOnly + MsgBoxStyle.Exclamation, "提示")
        End Try
        End Su

用VB代码实现快速导出DataGridView数据到Excel
需要引用这两个库Microsoft.Office.Interop.Excel 、Microsoft Office 15.0 Object Library

Imports Microsoft.Office.Core
Imports Microsoft.Office.Interop.Excel
Imports System.IO
Imports System.Collections
Imports System.Reflection
  Public Sub DataToExcelFast(dgv As DataGridView, fName As String)
        Try
            Dim excel As New Microsoft.Office.Interop.Excel.Application()
            Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Add(True)
            Dim ws As Microsoft.Office.Interop.Excel.Worksheet = DirectCast(wb.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)
            ws.Name = fName
            Dim sFile As String = String.Empty
            Dim dialog As New SaveFileDialog()
            dialog.Title = "Save export file"
            dialog.Filter = "EXECL File(*.xlsx) |.xlsx"
            dialog.FileName = fName
            dialog.FilterIndex = 1
            If dialog.ShowDialog() = DialogResult.OK Then
                Dim FileName As String = dialog.FileName
                If File.Exists(FileName) Then
                    File.Delete(FileName)
                End If
                sFile = FileName
                excel.Visible = False
                excel.DisplayAlerts = False
                excel.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual
                Dim cols As New System.Collections.ArrayList()
                '#Region "列标题，并根据列的数据类型设置Excel列的格式"
                Dim visibleColumnCount As Integer = 0
                For Each col As DataGridViewColumn In dgv.Columns
                    If col.Visible Then
                        visibleColumnCount += 1
                        ws.Cells(1, visibleColumnCount).Value2 = col.HeaderText.ToString()
                        ws.Cells(1, visibleColumnCount).Font.Bold = True
                        cols.Add(col.Name)
                        If col.ValueType IsNot Nothing Then
                            If col.ValueType.Name.Equals("DateTime") Then

                                ws.Columns(visibleColumnCount).NumberFormatLocal = "yyyy-mm-dd HH:mm"
                            ElseIf col.ValueType.Name.Equals("Decimal") OrElse col.ValueType.Name.Equals("Double") Then
                                ws.Columns(visibleColumnCount).NumberFormat = "0.00"
                            ElseIf col.ValueType.Name.Equals("Int32") Then
                                ws.Columns(visibleColumnCount).NumberFormat = "0"
                            Else
                                ws.Columns(visibleColumnCount).NumberFormatLocal = "@"
                            End If
                        Else
                            ws.Columns(visibleColumnCount).NumberFormatLocal = "@"
                        End If
                    End If
                Next
                '#End Region
                '#Region "写入行"
                ' Copy each DataTable  
                ' Copy the DataTable to an object array  
                Dim rawData As Object(,) = New Object(dgv.Rows.Count - 1, cols.Count - 1) {}

                ' Copy the column names to the first row of the object array  
                For col As Integer = 0 To cols.Count - 1
                    rawData(0, col) = cols(col)
                Next
                ' Copy the values to the object array  
                For col As Integer = 0 To cols.Count - 1
                    For row As Integer = 0 To dgv.Rows.Count - 1
                        rawData(row, col) = dgv.Rows(row).Cells(cols(col).ToString()).Value
                    Next
                Next
                ' Calculate the final column letter  
                Dim finalColLetter As String = String.Empty
                Dim colCharset As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
                Dim colCharsetLen As Integer = colCharset.Length
                If cols.Count > colCharsetLen Then
                    finalColLetter = colCharset.Substring((cols.Count - 1) / colCharsetLen - 1, 1)
                End If
                finalColLetter += colCharset.Substring((cols.Count - 1) Mod colCharsetLen, 1)
                ' Fast data export to Excel  
                Dim excelRange As String = String.Format("A2:{0}{1}", finalColLetter, dgv.Rows.Count + 1)
                ws.Range(excelRange, Type.Missing).Value2 = rawData
                '#End Region
                excel.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationAutomatic
                ' 51表示2007-2010格式的xlsx  
                ws.SaveAs(FileName, 51, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _
                    Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing)
                wb.Close(True, Type.Missing, Type.Missing)
                excel.Quit()
                ' 安全回收进程  
                System.GC.GetGeneration(excel)
                If MessageBox.Show("数据已导出成功，是否需要打开文件？", "提示", MessageBoxButtons.YesNo) = DialogResult.Yes Then
                    System.Diagnostics.Process.Start(FileName)
                End If
            End If
        Catch ex As Exception
            MessageBox.Show("导出数据异常" & vbCr & vbLf + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
        End Try
    End Sub

以下是C#实现快速导出DataGridView数据到Excel的代码
需要引用这两个库Microsoft.Office.Interop.Excel 、Microsoft Office 15.0 Object Library

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Windows.Forms;
namespace TextExportCshap
{
    class ExportDataGridView
    {
        public void DataToExcelFast(DataGridView dgv, string fName)
        {
            try
            {
                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Add(true);
                Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
                ws.Name = fName;
                string sFile = string.Empty;
                SaveFileDialog dialog = new SaveFileDialog();
                dialog.Title = "Save export file";
                dialog.Filter = "EXECL File(*.xlsx) |.xlsx";
                dialog.FileName = fName;
                dialog.FilterIndex = 1;
                if (dialog.ShowDialog() == DialogResult.OK)
                {
                    string FileName = dialog.FileName;
                    if (File.Exists(FileName))
                        File.Delete(FileName);
                    sFile = FileName;
                    excel.Visible = false;
                    excel.DisplayAlerts = false;
                    excel.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual;
                    System.Collections.ArrayList cols = new System.Collections.ArrayList();
                    #region 列标题，并根据列的数据类型设置Excel列的格式
                    int visibleColumnCount = 0;
                    foreach (DataGridViewColumn col in dgv.Columns)
                    {
                        if (col.Visible)
                        {
                            visibleColumnCount++;
                            ws.Cells[1, visibleColumnCount].Value2 = col.HeaderText.ToString();
                            ws.Cells[1, visibleColumnCount].Font.Bold = true;
                            cols.Add(col.Name);
                            if (col.ValueType != null)
                            {
                                if (col.ValueType.Name.Equals("DateTime"))
                                {
                                    ws.Columns[visibleColumnCount].NumberFormatLocal = @"yyyy-mm-dd HH:mm";
                                   
                                }
                                else if (col.ValueType.Name.Equals("Decimal")
                                    || col.ValueType.Name.Equals("Double"))
                                {
                                    ws.Columns[visibleColumnCount].NumberFormat = "0.00";
                                }
                                else if (col.ValueType.Name.Equals("Int32"))
                                {
                                    ws.Columns[visibleColumnCount].NumberFormat = "0";
                                }
                                else
                                {
                                    ws.Columns[visibleColumnCount].NumberFormatLocal = @"@";
                                }
                            }
                            else
                                ws.Columns[visibleColumnCount].NumberFormatLocal = @"@";
                        }
                    }
                    #endregion
                    #region 写入行
                    // Copy each DataTable  
                    // Copy the DataTable to an object array  
                    object[,] rawData = new object[dgv.Rows.Count, cols.Count];

                    // Copy the column names to the first row of the object array  
                    for (int col = 0; col < cols.Count; col++)
                    {
                        rawData[0, col] = cols[col];
                    }
                    // Copy the values to the object array  
                    for (int col = 0; col < cols.Count; col++)
                    {
                        for (int row = 0; row < dgv.Rows.Count; row++)
                        {
                            rawData[row, col] = dgv.Rows[row].Cells[cols[col].ToString()].Value;
                        }
                    }
                    // Calculate the final column letter  
                    string finalColLetter = string.Empty;
                    string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
                    int colCharsetLen = colCharset.Length;
                    if (cols.Count > colCharsetLen)
                    {
                        finalColLetter = colCharset.Substring(
                            (cols.Count - 1) / colCharsetLen - 1, 1);
                    }
                    finalColLetter += colCharset.Substring(
                            (cols.Count - 1) % colCharsetLen, 1);
                    // Fast data export to Excel  
                    string excelRange = string.Format("A2:{0}{1}",
                        finalColLetter, dgv.Rows.Count + 1);
                    ws.get_Range(excelRange, Type.Missing).Value2 = rawData;
                    #endregion
                    excel.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationAutomatic;
                    // 51表示2007-2010格式的xlsx  
                    ws.SaveAs(FileName, 51, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing,
                        Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                        Type.Missing, Type.Missing, Type.Missing);
                    wb.Close(true, Type.Missing, Type.Missing);
                    excel.Quit();
                    // 安全回收进程  
                    System.GC.GetGeneration(excel);
                    if (MessageBox.Show("数据已导出成功，是否需要打开文件？", "提示",
                         MessageBoxButtons.YesNo) == DialogResult.Yes)
                        System.Diagnostics.Process.Start(FileName);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("导出数据异常\r\n" + ex.Message,
                "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

    }
}