如何导出数据的datagridview到Excel？ [英] How to export datagridview data to excel?

问题描述

我出口的代码

这个代码在Excel中的数据写入，但内容是对细胞接头。他们散了。

 公共静态无效export_to_excel（DataGridView的DGV，串excel_file）
 {
 INT COLS ; 
 //打开文件
的StreamWriter WR =新的StreamWriter（excel_file）; 
 COLS = dgv.Columns.Count; 
的for（int i = 0; I< COLS;我++）
 {
 wr.Write（dgv.Columns [I] .HeaderText.ToString（）ToUpper的（）+\\。 \\t）; 
} 
 wr.WriteLine（）; //写行exce​​l文件
 
的for（int i = 0;我≤（dgv.Rows.Count  -  1）;我++）
 {
为（诠释J = 0; J< COLS; J ++）
 {
如果（dgv.Rows [I] .Cells [J]。价值= NULL）
 wr.Write（dgv.Rows [！我] .Cells [J]。价值+\t）; 
，否则
 {
 wr.Write（\t）; 
} 
} 
 wr.WriteLine（）; 
} 
 //关闭文件
 wr.Close（）; } 
  

解决方案

我已经做到了在VB.net，和我将其转换为C＃与 http://www.developerfusion.com/tool​​s/ 。转换/ VB到CSHARP / 的，所以你应该测试它

VB.net代码：

 进口System.Runtime.CompilerServices 
来导入Excel =的Microsoft.Office.Interop.Excel 
公共模块ExcelMod 
 
<扩展（）> _ 
公共功能ToExcel（BYVAL GRD作为DataGridView的，BYVAL路径字符串，可选的ByRef EXP作为例外= Nothing）作为布尔值
尺寸水库由于布尔=假
 EXP =什么
昏暗xlApp作为Excel.Application =什么
昏暗xlWorkBook作为Excel.Workbook =什么
昏暗xlWorkSheet作为Excel.Worksheet =什么
尝试
 
昏暗oldCI作为系统。 Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture 
 
昏暗misValue作为对象= System.Reflection.Missing.Value 
暗淡我作为整数
昏暗Ĵ作为整数
 
 xlApp =新Excel.ApplicationClass 
 System.Threading.Thread.CurrentThread.CurrentCulture =新System.Globalization.CultureInfo（EN-US）
 
 xlWorkBook = xlApp.Workbooks.Add（misValue）
 xlWorkSheet = xlWorkBook.Sheets（工作表Sheet1）
 
昏暗lastCol为整数= 0 
昏暗LASTROW为整数= 0 
对于j = 0到grd.ColumnCount  -  1 
如果grd.Columns（J）。可见然后
 xlWorkSheet.Columns（lastCol + 1）= .ColumnWidth CINT（grd.Columns（J）.WIDTH / 10）
 xlWorkSheet.Cells（1，lastCol + 1）= grd.Columns（j）条.HeaderText 
 lastCol + = 1 
结束如果
下一
 
 LASTROW = 0 
。对于i = 0到grd.RowCount  -  1 
 lastCol = 0 
。对于j = 0到grd.ColumnCount  -  1 
如果grd.Columns（J ）。可见AndAlso运算grd.Rows（I）。可见然后
 
。如果GRD（J，I）.FormattedValue<>没什么然后_ 
 xlWorkSheet.Cells（LASTROW + 2，lastCol + 1）= GRD（J，I）.FormattedValue.ToString（）
 
 lastCol + = 1 
 
端如果
下一步
如果grd.Rows（I）。可见然后LASTROW + = 1 
下一步
 
 
 xlWorkSheet.SaveAs（路径） 
 xlWorkBook.Close（）
 xlApp.Quit（）
 
 System.Threading.Thread.CurrentThread.CurrentCulture = oldCI 
解析度=真
 
抓住EX作为例外
 EXP =前
最后
如果xlApp IsNot运算没有那么releaseObject（xlApp）
如果xlWorkBook IsNot运算没有那么releaseObject（xlWorkBook）
。如果xlWorkSheet IsNot运算没有那么releaseObject（xlWorkSheet）
端尝试
 
返回水库
端功能
 
私人小组releaseObject（BYVAL OBJ作为对象）
尝试
 System.Runtime.InteropServices.Marshal.ReleaseComObject（OBJ）
 OBJ =什么
抓住EX作为例外
 OBJ =什么
最后
 GC。收集（）
端尝试
端子
 
端模块
  

C＃代码：

 使用Microsoft.VisualBasic程序; 
使用系统; System.Collections中使用
; 
使用System.Collections.Generic; 
使用System.Data这;使用System.Diagnostics程序
;使用System.Runtime.CompilerServices 
;使用Excel =的Microsoft.Office.Interop.Excel 
; 
公共静态类ExcelMod 
 {
 
 
公共静态布尔ToExcel（这DataGridView的GRD，路径字符串，裁判异常EXP = NULL）
 {
布尔解析度= FALSE; 
 EXP = NULL; 
 Excel.Application xlApp = NULL; 
 Excel.Workbook xlWorkBook = NULL; 
 Excel.Worksheet xlWorkSheet = NULL; 
 
尝试{
 System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; 
 
对象misValue = System.Reflection.Missing.Value; 
 INT I = 0; 
 INT J = 0; 
 
 xlApp =新Excel.ApplicationClass（）; 
 System.Threading.Thread.CurrentThread.CurrentCulture =新System.Globalization.CultureInfo（EN-US）; 
 
 xlWorkBook = xlApp.Workbooks.Add（misValue）; 
 xlWorkSheet = xlWorkBook.Sheets（工作表Sheet1）; 
 
 INT lastCol = 0; 
 INT LASTROW = 0; 
为（J = 0; J< = grd.ColumnCount  -  1; J ++）{$​​ B $ B如果（grd.Columns（J）。可见）{
 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; 
为（i = 0; I< = grd.RowCount  -  1;我++）{$​​ B $ B lastCol = 0; 
为（J = 0; J< = grd.ColumnCount  -  1; J ++）{$​​ B 
 $ B如果（grd.Columns（J）。可见和放大器;&安培; grd.Rows（我）。可见）{
如果（GRD（J，I）.FormattedValue！= NULL）
 xlWorkSheet.Cells（LASTROW + 2，lastCol + 1）= GRD（J，I）.FormattedValue.ToString （）; 
 
 lastCol + = 1; 
 
} 
} 
如果（grd.Rows（我）。可见）
 LASTROW + = 1; 
} 
 
 
 xlWorkSheet.SaveAs（路径）; 
 xlWorkBook.Close（）; 
 xlApp.Quit（）; 
 
 System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; 
 RES = TRUE; 
 
}赶上（例外前）{
 EXP =前; 
} {最后
如果（xlApp！= NULL）
 releaseObject（xlApp）; 
如果（xlWorkBook！= NULL）
 releaseObject（xlWorkBook）; 
如果（xlWorkSheet！= NULL）
 releaseObject（xlWorkSheet）; 
} 
 
返回水库; 
} 
 
私有静态无效releaseObject（obj对象）
 {
尝试{
 System.Runtime.InteropServices.Marshal.ReleaseComObject（OBJ）; 
的obj = NULL; 
}赶上（例外前）{
的obj = NULL; 
} {最后
 GC.Collect的（）; 
} 
} 
 
} 
  

my export code

This code writes data in excel but the contents are fitting in to the cells. they are scattered.

   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(); }

解决方案

I have did that in VB.net, and i convert it to c# with http://www.developerfusion.com/tools/convert/vb-to-csharp/, so you should test it.

VB.net code :

    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# code :

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();
        }
    }

}

这篇关于如何导出数据的datagridview到Excel？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！