VB.NET_从DataGridview中导出EXCEL(1.准备)

陆续做了几个小的erp，其中对excel的操作很频繁，在此总结vb.net操作EXCEL的心得。

一.配置环境，首先要添加引用

1.选择你要使用excel的project,点击 工程->添加引用

2.添加ms的interop.excel,因为我装的是2007,所以版本是12.0

3.在类中导入引用：

Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

二.从datagridview中导出EXCEL

以下是一个粗糙的类，传入一个datagridview和保存路径及文件名，即可导出

Imports System.Windows.Forms
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel


''' <summary>
''' 操作EXCEL的类
''' </summary>
''' <remarks></remarks>
Public Class ExcelManager


#Region "attributes define"
  Enum Oprate As Integer
    Export = 0
    Print = 1
  End Enum
  Private _OprateType As Oprate
  Private _strFilePath As String
  Private _strFileName As String
  Private _DGV As DataGridView = Nothing
  Private _processTime As Long = 0 '导出所需时间


  Property strFilePath() As String
    Get
      Return _strFilePath
    End Get
    Set(ByVal value As String)
      _strFilePath = value
    End Set
  End Property
  Property strFileName() As String
    Get
      Return _strFileName
    End Get
    Set(ByVal value As String)
      _strFileName = value
    End Set
  End Property
  Property DGV() As DataGridView
    Get
      Return _DGV
    End Get
    Set(ByVal value As DataGridView)
      _DGV = value
    End Set
  End Property
  Property OprateType() As Oprate
    Get
      Return _OprateType
    End Get
    Set(ByVal value As Oprate)
      _OprateType = value
    End Set
  End Property
  ''' <summary>
  ''' 导出EXCEL耗费的时间，单位为秒
  ''' </summary>
  ''' <value></value>
  ''' <returns></returns>
  ''' <remarks></remarks>
  Property processTime() As Long
    Get
      Return _processTime
    End Get
    Set(ByVal value As Long)


    End Set
  End Property
#End Region




  ''' <summary>
  ''' 导出DGV中的数据到EXCEL中
  ''' </summary>
  ''' <returns></returns>
  ''' <remarks></remarks>
  Public Function ExportEXLFromDGV() As Boolean


    '合法检查
    '1.路径是否合法
    If My.Computer.FileSystem.DirectoryExists(_strFilePath) = False Then
      MsgBox("文件保存路径不存在",MsgBoxStyle.Exclamation,"")
      Return False
    End If
    '2._DGV中是否有数据
    If _DGV.RowCount < 1 Then
      MsgBox("表格中没有数据，不可导出","")
      Return False
    End If


    '文件保存的默认路径为预设值
    Dim path As String = ""


    '如果是导出成EXCEL，则弹出saveFileDialog
    If _OprateType = Oprate.Export Then
      Dim SaveFileDialog1 As New SaveFileDialog
      SaveFileDialog1.FileName = _strFileName
      SaveFileDialog1.InitialDirectory = _strFilePath
      SaveFileDialog1.Filter = "Excel (*.xlsx)|*.xlsx|All Files (*.*)|*.*"


      If SaveFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.Cancel Then
        Return False
      End If


      '没有合法的文件保存路径
      If SaveFileDialog1.FileName = "" Or SaveFileDialog1.FileName Is Nothing Then
        Exit Function
      End If
      path = SaveFileDialog1.FileName 'FileName即为绝对路径


      '是否存在重名文件
      If My.Computer.FileSystem.FileExists(path) = True Then
        If MsgBox(path + " 已存在！" + Chr(13) + "是否覆盖？",MsgBoxStyle.YesNo + MsgBoxStyle.Information,"") = MsgBoxResult.No Then
          Exit Function
        End If
        Try
          My.Computer.FileSystem.DeleteFile(path)
        Catch ex As Exception
          MsgBox(ex.ToString,"")
        End Try
      End If


    Else
      '如果是要打印表格，则判断其行数，超过200行禁止打印
      If DGV.RowCount > 200 Then
        MsgBox("数据量过大，禁止直接打印","")
        Return False
      End If
    End If




    Dim dat_begin As Date = Now


    Dim exlApp As Excel.Application
    Dim exlBook As Excel.Workbook
    Dim exlSheet As Excel.Worksheet


    exlApp = CreateObject("Excel.Application") '创建Excel的应用程序实例
    exlBook = exlApp.Workbooks.Add '创建Excel的工作簿
    '删除多余的sheet,只留一张
    Dim co As Integer = exlBook.Sheets.Count
    For i As Integer = 1 To co - 1
      exlBook.Worksheets(1).delete()
    Next
    exlSheet = exlBook.Worksheets(1) '指定一张表
    exlSheet.Name = _strFileName


    exlApp.Visible = False


    '=================================================宏
    ' 宏由 龙觉寺 录制，时间: 2013-7-1
    'exlSheet.Columns(1).ColumnWidth = 10.75
    'exlSheet.Columns(2).ColumnWidth = 13.88


    '设置表头
    Dim oRange As Excel.Range
    oRange = exlSheet.Range(exlSheet.Cells(1,1),exlSheet.Cells(2,_DGV.ColumnCount))
    With oRange
      .HorizontalAlignment = XlHAlign.xlHAlignCenter
      .VerticalAlignment = XlVAlign.xlVAlignBottom
      .WrapText = False
      .Orientation = 0
      .AddIndent = False
      .IndentLevel = 0
      .ShrinkToFit = False
      .MergeCells = False
    End With
    oRange.Merge()


    With oRange
      .HorizontalAlignment = XlHAlign.xlHAlignCenter
      .VerticalAlignment = XlVAlign.xlVAlignCenter
      .WrapText = False
      .Orientation = 0
      .AddIndent = False
      .IndentLevel = 0
      .ShrinkToFit = False
      .MergeCells = True
    End With
    With oRange.Font
      .Name = "宋体"
      .FontStyle = "常规"
      .Size = 22
      .Strikethrough = False
      .Superscript = False
      .Subscript = False
      .OutlineFont = False
      .Shadow = False
      .Underline = XlUnderlineStyle.xlUnderlineStyleNone
      .ColorIndex = XlColorIndex.xlColorIndexAutomatic
    End With
    oRange.Borders(XlBordersIndex.xlDiagonalDown).LineStyle = BorderStyle.None
    oRange.Borders(XlBordersIndex.xlDiagonalUp).LineStyle = BorderStyle.None
    With oRange.Borders(XlBordersIndex.xlEdgeLeft)
      .LineStyle = XlLineStyle.xlContinuous
      .Weight = XlBorderWeight.xlThin
      .ColorIndex = XlColorIndex.xlColorIndexAutomatic
    End With
    With oRange.Borders(XlBordersIndex.xlEdgeTop)
      .LineStyle = XlLineStyle.xlContinuous
      .Weight = XlBorderWeight.xlThin
      .ColorIndex = XlColorIndex.xlColorIndexAutomatic
    End With
    With oRange.Borders(XlBordersIndex.xlEdgeBottom)
      .LineStyle = XlLineStyle.xlContinuous
      .Weight = XlBorderWeight.xlThin
      .ColorIndex = XlColorIndex.xlColorIndexAutomatic
    End With
    With oRange.Borders(XlBordersIndex.xlEdgeRight)
      .LineStyle = XlLineStyle.xlContinuous
      .Weight = XlBorderWeight.xlThin
      .ColorIndex = XlColorIndex.xlColorIndexAutomatic
    End With
    oRange.Borders(XlBordersIndex.xlInsideVertical).LineStyle = BorderStyle.None
    oRange.Borders(XlBordersIndex.xlInsideHorizontal).LineStyle = BorderStyle.None
    oRange.FormulaR1C1 = _strFileName




    '设置主体部分，大标题占了两行，从第三行开始，共总行数+1（1是给_DGV的表头设置的）
    oRange = exlSheet.Range(exlSheet.Cells(3,exlSheet.Cells(_DGV.RowCount + 3,_DGV.ColumnCount))
    With oRange
      '.NumberFormatLocal = "@"'文本格式
      .HorizontalAlignment = XlHAlign.xlHAlignLeft
      .VerticalAlignment = XlVAlign.xlVAlignCenter
      .WrapText = False
      .Orientation = 0
      .AddIndent = False
      .IndentLevel = 0
      .ShrinkToFit = True
    End With
    oRange.Borders(XlBordersIndex.xlDiagonalDown).LineStyle = BorderStyle.None
    oRange.Borders(XlBordersIndex.xlDiagonalUp).LineStyle = BorderStyle.None


    With oRange.Borders(XlBordersIndex.xlEdgeLeft)
      .LineStyle = XlLineStyle.xlContinuous
      .Weight = XlBorderWeight.xlThin
      .ColorIndex = XlColorIndex.xlColorIndexAutomatic
    End With
    With oRange.Borders(XlBordersIndex.xlEdgeTop)
      .LineStyle = XlLineStyle.xlContinuous
      .Weight = XlBorderWeight.xlThin
      .ColorIndex = XlColorIndex.xlColorIndexAutomatic
    End With
    With oRange.Borders(XlBordersIndex.xlEdgeBottom)
      .LineStyle = XlLineStyle.xlContinuous
      .Weight = XlBorderWeight.xlThin
      .ColorIndex = XlColorIndex.xlColorIndexAutomatic
    End With
    With oRange.Borders(XlBordersIndex.xlEdgeRight)
      .LineStyle = XlLineStyle.xlContinuous
      .Weight = XlBorderWeight.xlThin
      .ColorIndex = XlColorIndex.xlColorIndexAutomatic
    End With
    With oRange.Borders(XlBordersIndex.xlInsideVertical)
      .LineStyle = XlLineStyle.xlContinuous
      .Weight = XlBorderWeight.xlThin
      .ColorIndex = XlColorIndex.xlColorIndexAutomatic
    End With
    With oRange.Borders(XlBordersIndex.xlInsideHorizontal)
      .LineStyle = XlLineStyle.xlContinuous
      .Weight = XlBorderWeight.xlThin
      .ColorIndex = XlColorIndex.xlColorIndexAutomatic
    End With


    '=================================================宏


    Try
      '将dataGrid表格中的数据插入到EXCEL
      'For int_row As Integer = 0 To _DGV.RowCount - 1
      '  For int_col As Integer = 0 To _DGV.ColumnCount - 1
      '    exlSheet.Cells(int_row + 4,int_col + 1).FormulaR1C1 = _DGV.Rows(int_row).Cells(int_col).Value.ToString
      '  Next
      'Next


      '优化算法，用arr对块进行批量赋值
      Dim arr As Array = Array.CreateInstance(GetType(String),DGV.RowCount,DGV.ColumnCount)
      For i As Integer = 0 To DGV.RowCount - 1
        For j As Integer = 0 To DGV.ColumnCount - 1
          arr.SetValue(DGV.Rows(i).Cells(j).Value.ToString,i,j)
        Next
      Next
      oRange = exlSheet.Range(exlSheet.Cells(4,_DGV.ColumnCount))
      oRange.Value2 = arr


      '将dataGridView列名写入exlSheet第三行
      For i As Integer = 0 To _DGV.ColumnCount - 1
        exlSheet.Cells(3,i + 1).FormulaR1C1 = _DGV.Columns(i).HeaderText
      Next


      '如果是导出
      If _OprateType = Oprate.Export Then
        exlBook.SaveAs(path)
        _processTime = DateDiff(DateInterval.Second,dat_begin,Now)
        exlApp.Quit()
        exlSheet = Nothing
        exlBook = Nothing
        exlApp = Nothing
        MsgBox("文件成功保存到:" & Chr(13) & path,vbOKOnly + vbInformation,"Time: " + _processTime.ToString + " s")
        Exit Function
      End If
      '如果是打印
      If _OprateType = Oprate.Print Then
        exlApp.Visible = True
        exlBook.PrintPreview()
        exlBook.Close(False,Type.Missing,Type.Missing)
        exlApp.Quit()
        exlSheet = Nothing
        exlBook = Nothing
        exlApp = Nothing
        Exit Function
      End If


    Catch ex As Exception
      MsgBox(ex.ToString)
    Finally
      If Not (exlApp Is Nothing) Then
        exlApp.Quit()
        exlSheet = Nothing
        exlBook = Nothing
        exlApp = Nothing
      End If
    End Try


    Return True
  End Function
  ''' <summary>
  ''' 从dataTable中导出数据到excel,疾速方式，没有格式，且只能为2003以下的版本
  ''' </summary>
  ''' <param name="sub_ds">dataset</param>
  ''' <param name="sub_FileName"></param>
  ''' <returns></returns>
  ''' <remarks></remarks>
  Public Shared Function DateSetToExcel(ByVal sub_ds As System.Data.DataSet,ByVal sub_FileName As String) As Boolean
    Dim excelApp As New ApplicationClass()
    Dim excelWorkbook As Workbook = excelApp.Workbooks.Add(Type.Missing)


    Dim sheetIndex As Integer = 0
    Dim col,row As Integer
    Dim excelSheet As Worksheet


    ' Copy each DataTable as a new Sheet
    For Each dt As System.Data.DataTable In sub_ds.Tables
      sheetIndex += 1
      ' Copy the DataTable to an object array
      Dim rawData(dt.Rows.Count,dt.Columns.Count - 1) As Object
      ' Copy the column names to the first row of the object array
      For col = 0 To dt.Columns.Count - 1
        rawData(0,col) = dt.Columns(col).ColumnName
      Next
      ' Copy the values to the object array
      For col = 0 To dt.Columns.Count - 1
        For row = 0 To dt.Rows.Count - 1
          rawData(row + 1,col) = dt.Rows(row).ItemArray(col)
        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 dt.Columns.Count > colCharsetLen Then
        finalColLetter = colCharset.Substring((dt.Columns.Count - 1)  colCharsetLen - 1,1)
      End If


      finalColLetter += colCharset.Substring((dt.Columns.Count - 1) Mod colCharsetLen,1)


      ' Create a new Sheet
      excelSheet = CType(excelWorkbook.Sheets.Add(excelWorkbook.Sheets(sheetIndex),1,XlSheetType.xlWorksheet),Worksheet)
      excelSheet.Name = dt.TableName


      ' Fast data export to Excel
      Dim excelRange As String = String.Format("A1:{0}{1}",finalColLetter,dt.Rows.Count + 1)
      excelSheet.Range(excelRange,Type.Missing).Value2 = rawData


      ' Mark the first row as BOLD
      CType(excelSheet.Rows(1,Type.Missing),Range).Font.Bold = True


      excelSheet = Nothing
    Next


    ' Save and Close the Workbook
    excelWorkbook.SaveAs(sub_FileName,XlFileFormat.xlWorkbookNormal,_
    Type.Missing,XlSaveAsAccessMode.xlExclusive,Type.Missing)


    excelWorkbook.Close(True,Type.Missing)


    excelWorkbook = Nothing


    ' Release the Application object
    excelApp.Quit()
    excelApp = Nothing


    ' Collect the unreferenced objects
    GC.Collect()
    GC.WaitForPendingFinalizers()
  End Function


End Class

龙觉寺CSDN_BLOG...原创...不总结永远一知半解，一知半解比无知更可怕

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!