随笔08 简易的将gdv数据导出excel

方法1

为了将 DataGridView 中的数据导出到 Excel，并将所有数据以字符串格式显示，您可以通过在导出数据时在每个值的前面加上一个单引号 ' 来强制 Excel 将其作为文本显示。这个修改可以确保即使是数字也会作为文本类型显示。

修改后的代码：

vb
复制代码
Imports Microsoft.Office.Interop.Excel
Imports System.IO

Public Sub ExportDataGridViewToExcel(gdv As DataGridView, sheetName As String)
    ' 创建保存文件对话框，供用户选择保存地址
    Dim saveFileDialog As New SaveFileDialog()
    saveFileDialog.Filter = "Excel Files|*.xlsx"
    saveFileDialog.Title = "保存Excel文件"
    saveFileDialog.FileName = sheetName

    ' 检查用户是否选择了保存地址
    If saveFileDialog.ShowDialog() = DialogResult.OK Then
        ' 获取用户选择的保存路径
        Dim savePath As String = saveFileDialog.FileName

        ' 创建 Excel 应用程序对象
        Dim excelApp As New Application()
        Dim workbook As Workbook = excelApp.Workbooks.Add()
        Dim worksheet As Worksheet = workbook.Sheets(1)
        worksheet.Name = sheetName

        ' 填充表头
        For i As Integer = 0 To gdv.Columns.Count - 1
            worksheet.Cells(1, i + 1) = gdv.Columns(i).HeaderText
        Next

        ' 填充数据，添加单引号使其显示为字符串
        For rowIndex As Integer = 0 To gdv.Rows.Count - 1
            For colIndex As Integer = 0 To gdv.Columns.Count - 1
                If gdv.Rows(rowIndex).Cells(colIndex).Value IsNot Nothing Then
                    ' 在数据前面加上单引号
                    worksheet.Cells(rowIndex + 2, colIndex + 1) = "'" & gdv.Rows(rowIndex).Cells(colIndex).Value.ToString()
                End If
            Next
        Next

        ' 保存 Excel 文件
        workbook.SaveAs(savePath)
        workbook.Close()
        excelApp.Quit()

        ' 释放 COM 对象，防止内存泄漏
        ReleaseObject(worksheet)
        ReleaseObject(workbook)
        ReleaseObject(excelApp)

        MessageBox.Show("Excel文件导出成功！", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End If
End Sub

' 释放COM对象的辅助方法
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

修改的重点：

• 在写入 Excel 单元格时，将每个值都添加一个单引号 '，例如：

  vb
  复制代码
  worksheet.Cells(rowIndex + 2, colIndex + 1) = "'" & gdv.Rows(rowIndex).Cells(colIndex).Value.ToString()
  

  这样，Excel 会将所有值都强制转换为文本格式，即使是数字也会以文本方式显示。

代码执行效果：

• 当用户打开导出的 Excel 文件时，所有单元格中的数据前面都会有一个单引号 '，以确保 Excel 将它们视为字符串类型而不是数字或其他格式。

通过这种方式，您可以确保 DataGridView 中的所有数据在 Excel 中都以字符串形式呈现。

放法2

    Public Sub ExportDataGridViewToExcel(gdv As DataGridView, sheetName As String)
        ' 创建保存文件对话框，供用户选择保存地址
        Dim saveFileDialog As New SaveFileDialog()
        saveFileDialog.Filter = "Excel Files|*.xlsx"
        saveFileDialog.Title = "保存Excel文件"
        saveFileDialog.FileName = sheetName

        ' 检查用户是否选择了保存地址
        If saveFileDialog.ShowDialog() = DialogResult.OK Then
            ' 获取用户选择的保存路径
            Dim savePath As String = saveFileDialog.FileName

            ' 创建 Excel 应用程序对象
            Dim excelApp As New Application()
            Dim workbook As Workbook = excelApp.Workbooks.Add()
            Dim worksheet As Worksheet = workbook.Sheets(1)
            worksheet.Name = sheetName

            ' 设置Excel为文本格式
            worksheet.Cells.NumberFormat = "@"

            ' 填充表头
            For i As Integer = 0 To gdv.Columns.Count - 1
                worksheet.Cells(1, i + 1) = gdv.Columns(i).HeaderText
            Next

            ' 填充数据，格式化为字符串
            For rowIndex As Integer = 0 To gdv.Rows.Count - 1
                For colIndex As Integer = 0 To gdv.Columns.Count - 1
                    If gdv.Rows(rowIndex).Cells(colIndex).Value IsNot Nothing Then
                        worksheet.Cells(rowIndex + 2, colIndex + 1).NumberFormat = "@"
                        worksheet.Cells(rowIndex + 2, colIndex + 1).Value = gdv.Rows(rowIndex).Cells(colIndex).Value.ToString()
                    End If
                Next
            Next

            ' 保存 Excel 文件
            workbook.SaveAs(savePath)
            workbook.Close()
            excelApp.Quit()

            ' 释放 COM 对象，防止内存泄漏
            ReleaseObject(worksheet)
            ReleaseObject(workbook)
            ReleaseObject(excelApp)

            MessageBox.Show("Excel文件导出成功！", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub

    ' 释放COM对象的辅助方法
    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

Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
    Try
        ExportDataGridViewToExcel(grdList, "MyExportedData_" + Now.Date.ToString("yyMMdd"))
    Catch ex As Exception
        MessageBox.Show("出现问题！" + ex.ToString(), "错误")
    End Try
End Sub