DataGridView或 DataTable导出到excel

一．首先，项目中引入 excel.dll
二．代码：
１.　datagridview to excel

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.ComponentModel;
namespace DocumentSearch
{
    class ExcelHelper
    {
    //实现思路：锁定 DataGridView（以防止导出过程中用户重新对datagridview排序等等）->逐行获取DataGridView的cells的值（cell用 tab隔开，行用 NewLine -->将cells值串装入 dictionary(多个DataGridView对应) ）,中途可以用进度条显示进度或取消-->   复制到剪贴板Clipboard.SetDataObject(dictionary)-->粘贴到excel   worksheet(复制＼粘贴方法要比逐行循环插入速度要快很多)．
        //<summary>
        //get the datagridview data to text content. use "tab" split the field value ,"newline" to split the datarow
        //</summary>
        //<param name="dgvArr"></param>
        //<param name="bgWorker"></param>
        //<returns></returns>
        public Dictionary<string, string> DataGridToExcelContent(DataGridView[] dgvArr, BackgroundWorker  bgWorker)
        {
            Dictionary<string, string> rtnDic = new Dictionary<string, string>();
            int rowTol=0,rowRead=0,percent=0;
            foreach (DataGridView  dgv in dgvArr)
            {
                dgv.Invoke(new Action(() => {
                    dgv.Enabled = false;
                    rowTol = rowTol + dgv.Rows.Count;
                }));
            }

            foreach (DataGridView dgv in dgvArr)
            {
                if (dgv.Rows.Count < 1)
                    return rtnDic;
                string dgvContent = "";
                //Title of column name
                //Add  column title
                for (int i = 0; i <= dgv.Columns.Count - 1; i++)
                {
                    dgvContent = dgvContent + (i == 0 ? "" : "\t") + dgv.Columns[i].HeaderText;
                }
                dgvContent = dgvContent + Environment.NewLine;
                //Content
                for (int r = 0; r <= dgv.Rows.Count - 1; r++)
                {
                    if (bgWorker != null && (bgWorker as BackgroundWorker).CancellationPending)
                    {
                        //如果有取消則退出導出   
                        dgv.Invoke(new Action(() =>
                                            {
                                                foreach (DataGridView dgvInvoke in dgvArr)
                                                {
                                                    dgvInvoke.Enabled = true;
                                                }
                                            }));
                        return rtnDic;
                    }
                    for (int c = 0; c <= dgv.Columns.Count - 1; c++)
                    {
                        dgvContent = dgvContent + (c == 0 ? "" : "\t") + Convert.ToString(dgv.Rows[r].Cells[c].Value);
                     }
                    rowRead = rowRead + 1;
                    percent = (100 * rowRead) / rowTol;
                    if (bgWorker != null && percent <= 100)
                    {
                        (bgWorker as BackgroundWorker).ReportProgress(percent);
                    }
                    dgvContent = dgvContent + Environment .NewLine ;
                }
                rtnDic.Add(dgv.Name, dgvContent);
                dgv.Invoke(new Action(() =>
                {
                    dgv.Enabled = true;
                }));
           }
            return rtnDic;
        }
        /// <summary>
        /// export the Dictionary text to excel
        public void ExportExcel(Dictionary<string, string> dic)
        {
            if (dic == null && dic.Count == 0)
                return;

            try
            {
                var xlApp = new Microsoft.Office.Interop.Excel.Application();
                if (xlApp == null)
                       return;
                //Dim CurrentCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture()
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                var workbooks = xlApp.Workbooks; //  Microsoft.Office.Interop.Excel.Workbooks
                var workbook = workbooks.Add();//    Microsoft.Office.Interop.Excel.Workbook 
                foreach (var d in dic)
                {
                    var worksheet = workbook.Worksheets.Add();// Microsoft.Office.Interop.Excel.Worksheet
                    worksheet.Name = d.Key;
                    if (worksheet == null)
                        continue;
                    Clipboard.SetDataObject(d.Value);
                    worksheet.Paste();
                    worksheet.Cells.Font.Size = 10;
                    worksheet.Cells.Borders.LineStyle = 1;
                    worksheet.Columns.AutoFit();  //列寬自適應
                }
                xlApp.Visible = true;
              }
            catch (Exception ex)
            {
                throw ex;
            }
       }
    }
}

VB:

 ''' <summary>
    ''' get the datagridview data to text content. use "tab" split the field value ,"newline" to split the datarow
    ''' </summary>
    ''' <param name="dgvArr"></param>
    ''' <param name="bgWorker"></param>
    ''' <returns></returns>
    Public Function DataGridToExcelContent(ByVal dgvArr As DataGridView(), ByVal bgWorker As BackgroundWorker) As Dictionary(Of String, String)
        Dim rtnDic = New Dictionary(Of String, String)
        Dim rowTol As Integer  'get the data's rows count
        Dim rowRead As Integer
        Dim percent As Integer
        For Each dgv In dgvArr
            dgv.Invoke(New Action(Sub()
                                      dgv.Enabled = False
                                  End Sub))
            rowTol = rowTol + dgv.Rows.Count
        Next
        For Each dgv In dgvArr
            If dgv.Rows.Count < 1 Then
                Return rtnDic
            End If

            Dim dgvContent = ""
            'Title of column name
            'Add  column title
            For i = 0 To dgv.Columns.OfType(Of DataGridViewColumn).Count - 1
                dgvContent = dgvContent + IIf(i = 0, "", vbTab) + dgv.Columns(i).HeaderText
            Next
            dgvContent = dgvContent + vbNewLine
            'Content
            For r = 0 To dgv.Rows.OfType(Of DataGridViewRow).Count - 1
                If bgWorker IsNot Nothing And CType(bgWorker, BackgroundWorker).CancellationPending Then
                    '//如果有取消則退出導出   
                    dgv.Invoke(New Action(Sub()
                                              For Each dgvInvoke In dgvArr
                                                  dgvInvoke.Enabled = True
                                              Next
                                          End Sub))
                    Return rtnDic
                End If
                For c = 0 To dgv.Columns.OfType(Of DataGridViewColumn).Count - 1
                    dgvContent = dgvContent + IIf(c = 0, "", vbTab) + dgv.Rows(r).Cells(c).Value.ToString()
                Next
                rowRead = rowRead + 1
                percent = (100 * rowRead) / rowTol
                If bgWorker IsNot Nothing And percent <= 100 Then
                    CType(bgWorker, BackgroundWorker).ReportProgress(percent)
                End If
                dgvContent = dgvContent + vbNewLine
            Next
            rtnDic.Add(dgv.Name, dgvContent)
            dgv.Invoke(New Action(Sub()
                                      dgv.Enabled = True
                                  End Sub))
        Next
        Return rtnDic
    End Function
    '/// <summary>
    '/// export the datagridview to excel
    '/// </summary>
    '/// <param name="dgv">the datagridviews will be exported to excel</param>
    '/// <param name="oddEvenColor">set the odd And event rows have different color. </param>
    '/// <param name="bgWorker">the name of BackgroundWorker.  yes</param>
    '/// 
    Public Sub ExportExcel(ByVal dic As Dictionary(Of String, String))
        If dic Is Nothing Or dic.Count = 0 Then
            Return
        End If

        Try
            Dim xlApp = New Microsoft.Office.Interop.Excel.Application()
            If (xlApp Is Nothing) Then
                Return
            End If

            Dim CurrentCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture()
            System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
            Dim workbooks As Microsoft.Office.Interop.Excel.Workbooks = xlApp.Workbooks
            Dim workbook As Microsoft.Office.Interop.Excel.Workbook = workbooks.Add()
            For Each d In dic
                Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = workbook.Worksheets.Add()
                worksheet.Name = d.Key
                If worksheet Is Nothing Then
                    Continue For
                End If
                Clipboard.SetDataObject(d.Value)
                worksheet.Paste()
                worksheet.Cells.Font.Size = 10
                worksheet.Cells.Borders.LineStyle = 1
                worksheet.Columns.AutoFit()  ' //列寬自適應
            Next
            xlApp.Visible = True
        Catch e As Exception
            Throw e
        End Try
    End Sub

2. datatable to excel

C#:

//-------------------------------------------------
    class ExportToExcel
{
      
        /// <summary>
        /// export the dataset to excel
        /// </summary>
        /// <param name="ds">the dataset will be exported to excel</param>
        /// <param name="oddEvenColor">set the odd and event rows have different color. </param>
        /// <param name="bgWorker">the name of BackgroundWorker.  yes</param>
        /// <param name="tableName">sheets 's name </param>
        /// 
        public void ExportExcel(DataSet ds, bool oddEvenColor, object bgWorker,string[] tableName)
    {
       
        if (ds == null || ds.Tables .Count == 0) return;

        try
        {
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

            if (xlApp == null)

            {

                return;

            }

            System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;

            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;        
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add();

                int rowRead = 0;
                int rowCount = 0;
                //get the table's rows count
                foreach (DataTable dt in ds.Tables)
                {
                    rowCount += dt.Rows.Count;
                }
               
                for (int tbi = 0; tbi < ds.Tables.Count; tbi++)
                {
                
                    Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet) workbook.Worksheets.Add();
                    worksheet.Name = tableName[tbi].ToString();
                    if (worksheet == null)//工作薄中没有工作表
                    {
                        return;
                    }

                    Microsoft.Office.Interop.Excel.Range range;      

                    int percent = 0;

                    //title column name

                    for (int i = 0; i < ds.Tables[tbi].Columns.Count; i++)

                    {
                        worksheet.Cells[1, i + 1] = ds.Tables[tbi].Columns[i].ColumnName;
                        range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                        range.Interior.ColorIndex = 31;
                        range.Font.Size = 12;
                        range.Font.Bold = true;
                        range.Borders.LineStyle = 1;
                        range.Font.ColorIndex = 2;//white 

                    }
                    //content                
                    for (int r = 0; r < ds.Tables[tbi].Rows.Count; r++)
                    {
                        if (bgWorker != null && (bgWorker as BackgroundWorker).CancellationPending)
                            //if (frmMain.form1.bgWorker.CancellationPending)//如果有取消則退出導出                        
                            break;
                        for (int i = 0; i < ds.Tables[tbi].Columns.Count; i++)
                        {
                            worksheet.Cells[r + 2, i + 1] = ds.Tables[tbi].Rows[r][i].ToString();
                            
                        }
                        rowRead++;
                        percent = ((int)(100 * rowRead)) / rowCount;

                        if (bgWorker != null && percent <= 100)
                        {
                            (bgWorker as BackgroundWorker).ReportProgress(percent);
                            //frmMain.form1.bgWorker.ReportProgress(percent);                        
                        }
                    }
　  range = worksheet.Range(worksheet.Cells［2, 1］, worksheet.Cells［ds.Tables［tbi］.Rows.Count + 1, ds.Tables［tbi］.Columns.Count］)
                            range.Borders.LineStyle = 1;
                            if (oddEvenColor)
                            {
                                if (r % 2 == 1)
                                    range.Interior.ColorIndex = 34;//隔行換底色
                            }
                            range.Font.Size = 9;
                    worksheet.Columns.AutoFit(); //列寬自適應
                }
            xlApp.Visible = true;
        }
        catch (Exception e)
        {
            throw (e);
        }
    }

VB:

 '/// <summary>
    '/// export the dataset to excel
    '/// </summary>
    '/// <param name="ds">the dataset will be exported to excel</param>
    '/// <param name="oddEvenColor">set the odd And event rows have different color. </param>
    '/// <param name="bgWorker">the name of BackgroundWorker.  yes</param>
    '/// 
    Public Sub ExportExcel(ByVal ds As DataSet, ByVal oddEvenColor As Boolean, ByVal bgWorker As Object)
        If ds Is Nothing Or ds.Tables.Count = 0 Then
            Return
        End If

        Try
            Dim xlApp = New Microsoft.Office.Interop.Excel.Application()
            If (xlApp Is Nothing) Then
                Return
            End If

            Dim CurrentCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture()
            System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
            Dim workbooks As Microsoft.Office.Interop.Excel.Workbooks = xlApp.Workbooks
            Dim workbook As Microsoft.Office.Interop.Excel.Workbook = workbooks.Add()

            Dim rowRead As Integer = 0
            Dim cbRowRead As ConcurrentBag(Of Integer) = New ConcurrentBag(Of Integer)
            cbRowRead.Add(0)
            Dim rowCount As Integer = 0
            'get the table's rows count
            rowCount = ds.Tables.OfType(Of DataTable).AsEnumerable().Sum(Function(o) o.Rows.Count)
            Dim listT As List(Of Task) = New List(Of Task)()
            For Each tb In ds.Tables.OfType(Of DataTable)
                listT.Add(Task.Run(Sub()
                                       Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = workbook.Worksheets.Add()
                                       worksheet.Name = tb.ToString()
                                       If worksheet Is Nothing Then
                                           Return 'Continue For
                                       End If
                                       Dim Range As Microsoft.Office.Interop.Excel.Range
                                       Dim percent As Integer
                                       'Title of column name
                                       For i = 0 To tb.Columns.Count - 1
                                           worksheet.Cells(1, i + 1) = tb.Columns(i).ColumnName
                                           Range = worksheet.Cells(1, i + 1)
                                           Range.Interior.ColorIndex = 31
                                           Range.Font.Size = 12
                                           Range.Font.Bold = True
                                           Range.Borders.LineStyle = 1
                                           Range.Font.ColorIndex = 2
                                       Next
                                       'Content
                                       For r As Integer = 0 To tb.Rows.Count - 1
                                           If bgWorker IsNot Nothing And CType(bgWorker, BackgroundWorker).CancellationPending Then
                                               '//如果有取消則退出導出    
                                               Return
                                           End If
                                           For i = 0 To tb.Columns.Count - 1
                                               worksheet.Cells(r + 2, i + 1) = tb.Rows(r)(i).ToString()

                                           Next
                                           Dim lastItemInCb As Integer
                                           cbRowRead.TryTake(lastItemInCb)
                                           lastItemInCb = lastItemInCb + 1
                                           cbRowRead.Add(lastItemInCb)

                                           ' rowRead = rowRead + 1
                                           percent = (100 * lastItemInCb) / rowCount
                                           'percent = (100 * rowRead) / rowCount
                                           If bgWorker IsNot Nothing And percent <= 100 Then
                                               CType(bgWorker, BackgroundWorker).ReportProgress(percent)
                                           End If
                                       Next
                                       Range = worksheet.Range(worksheet.Cells(2, 1), worksheet.Cells(tb.Rows.Count + 1, tb.Columns.Count))
                                       Range.Borders.LineStyle = 1
                                       Range.Font.Size = 9
                                       worksheet.Columns.AutoFit()  ' //列寬自適應
                                   End Sub))
            Next
            Task.WaitAll(listT.ToArray())
            xlApp.Visible = True
        Catch e As Exception
            Throw e
        End Try
    End Sub