c# - 如何将datagridview数据导出到excel？

Question

我的导出代码

此代码在 excel 中写入数据，但内容适合单元格。它们是分散的。

   public static void export_to_excel(DataGridView dgv, string excel_file) 
     { 
         int cols; 
         //open file
         StreamWriter wr = new StreamWriter(excel_file); 
         cols = dgv.Columns.Count; 
         for (int i = 0; i < cols; i++)
         {
             wr.Write(dgv.Columns[i].HeaderText.ToString().ToUpper() + "\t");
         }
         wr.WriteLine(); //write rows to excel file 

         for (int i = 0; i < (dgv.Rows.Count - 1); i++)
         {
             for (int j = 0; j < cols; j++) 
             { 
                 if (dgv.Rows[i].Cells[j].Value != null) 
                     wr.Write(dgv.Rows[i].Cells[j].Value + "\t");
                 else 
                 { 
                     wr.Write("\t");
                 }
             }
             wr.WriteLine(); 
         } 
         //close file 
         wr.Close(); }

Answer 1

我已经在 VB.net 中做到了，我用 http://www.developerfusion.com/tools/convert/vb-to-csharp/ 将它转换为 c# , 所以你应该测试一下。

VB.net 代码:

    Imports System.Runtime.CompilerServices
Imports Excel = Microsoft.Office.Interop.Excel
Public Module ExcelMod

    <Extension()> _
    Public Function ToExcel(ByVal grd As DataGridView, ByVal path As String, Optional ByRef exp As Exception = Nothing) As Boolean
        Dim res As Boolean = False
        exp = Nothing
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Try

            Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture

            Dim misValue As Object = System.Reflection.Missing.Value
            Dim i As Integer
            Dim j As Integer

            xlApp = New Excel.ApplicationClass
            System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")

            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = xlWorkBook.Sheets("sheet1")

            Dim lastCol As Integer = 0
            Dim lastRow As Integer = 0
            For j = 0 To grd.ColumnCount - 1
                If grd.Columns(j).Visible Then
                    xlWorkSheet.Columns(lastCol + 1).ColumnWidth = CInt(grd.Columns(j).Width / 10)
                    xlWorkSheet.Cells(1, lastCol + 1) = grd.Columns(j).HeaderText
                    lastCol += 1
                End If
            Next

            lastRow = 0
            For i = 0 To grd.RowCount - 1
                lastCol = 0
                For j = 0 To grd.ColumnCount - 1
                    If grd.Columns(j).Visible AndAlso grd.Rows(i).Visible Then

                        If grd(j, i).FormattedValue <> Nothing Then _
                            xlWorkSheet.Cells(lastRow + 2, lastCol + 1) = grd(j, i).FormattedValue.ToString()

                        lastCol += 1

                    End If
                Next
                If grd.Rows(i).Visible Then lastRow += 1
            Next


            xlWorkSheet.SaveAs(path)
            xlWorkBook.Close()
            xlApp.Quit()

            System.Threading.Thread.CurrentThread.CurrentCulture = oldCI
            res = True

        Catch ex As Exception
            exp = ex
        Finally
            If xlApp IsNot Nothing Then releaseObject(xlApp)
            If xlWorkBook IsNot Nothing Then releaseObject(xlWorkBook)
            If xlWorkSheet IsNot Nothing Then releaseObject(xlWorkSheet)
        End Try

        Return res
    End Function

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

End Module

C# 代码:

using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Runtime.CompilerServices;
using Excel = Microsoft.Office.Interop.Excel;
public static class ExcelMod
{


    public static bool ToExcel(this DataGridView grd, string path, ref Exception exp = null)
    {
        bool res = false;
        exp = null;
        Excel.Application xlApp = null;
        Excel.Workbook xlWorkBook = null;
        Excel.Worksheet xlWorkSheet = null;

        try {
            System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;

            object misValue = System.Reflection.Missing.Value;
            int i = 0;
            int j = 0;

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

            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = xlWorkBook.Sheets("sheet1");

            int lastCol = 0;
            int lastRow = 0;
            for (j = 0; j <= grd.ColumnCount - 1; j++) {
                if (grd.Columns(j).Visible) {
                    xlWorkSheet.Columns(lastCol + 1).ColumnWidth = Convert.ToInt32(grd.Columns(j).Width / 10);
                    xlWorkSheet.Cells(1, lastCol + 1) = grd.Columns(j).HeaderText;
                    lastCol += 1;
                }
            }

            lastRow = 0;
            for (i = 0; i <= grd.RowCount - 1; i++) {
                lastCol = 0;
                for (j = 0; j <= grd.ColumnCount - 1; j++) {

                    if (grd.Columns(j).Visible && grd.Rows(i).Visible) {
                        if (grd(j, i).FormattedValue != null)
                            xlWorkSheet.Cells(lastRow + 2, lastCol + 1) = grd(j, i).FormattedValue.ToString();

                        lastCol += 1;

                    }
                }
                if (grd.Rows(i).Visible)
                    lastRow += 1;
            }


            xlWorkSheet.SaveAs(path);
            xlWorkBook.Close();
            xlApp.Quit();

            System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
            res = true;

        } catch (Exception ex) {
            exp = ex;
        } finally {
            if (xlApp != null)
                releaseObject(xlApp);
            if (xlWorkBook != null)
                releaseObject(xlWorkBook);
            if (xlWorkSheet != null)
                releaseObject(xlWorkSheet);
        }

        return res;
    }

    private static void releaseObject(object obj)
    {
        try {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        } catch (Exception ex) {
            obj = null;
        } finally {
            GC.Collect();
        }
    }

}