使用ASPOSE导出DataGrid数据到Excel（VB）

'介绍：采用Aspose导出EXCEL
    '导出数据到EXCEL文件
    '---------------------------------------------------------------------------------------
    '说明：只能导出一个DATAGRID，并将DATAGRID中显示的数据保存到EXCEL中
    '    DATAGRID只能而且必须存在一个TableStyle，且必须包含可以显示的列
    '参数：     dg：需要导出的DATAGRID实例
    '           RowCount：需要导出的行数
    '           SheetName：EXCEL中Sheet的名称
    '           FilePath：文件保存的全路径名
    '           IsChar：是否设置成字符格式
    '返回：     导出结果
    '---------------------------------------------------------------------------------------
    Public Function ExportExcel(ByVal dg As DataGrid, ByVal RowCount As Int32, ByVal SheetName As String, _
                                ByVal FilePath As String, Optional ByVal IsChar As Boolean = True, _
                                Optional ByVal FileType As ExportFileType = ExportFileType.EXCEL2003) As String

        Dim fs As FileStream

        Try

            'AsposeWorkBook
            Dim awbWorkBook As Aspose.Cells.Workbook = New Aspose.Cells.Workbook
            'AsposeWorkSheet
            Dim awsWorkSheet As Aspose.Cells.Worksheet = awbWorkBook.Worksheets(0)
            SetDataGridToAsposeWS(awsWorkSheet, dg, SheetName, RowCount, IsChar)

            Try
                fs = File.Open(FilePath, FileMode.OpenOrCreate)
            Catch ex As Exception
                MessageBox.Show("打开文件失败！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try

            awbWorkBook.Save(fs, FileFormatType.Excel2003)
        Catch ex As Exception
            Debug.Assert(False, ex.Message)
            Return "失败！"
        Finally
            If (Not fs Is Nothing) Then
                fs.Close()
            End If

        End Try
        Return "成功！"

        ''格式化
        'If IsChar Then
        '    oSheet.Range("A1", GetColumnChar(colcount) + (RowCount + 1).ToString).NumberFormat = "@" '定义格式为字符
        'End If
        'oSheet.Range("A1", GetColumnChar(colcount) + (RowCount + 1).ToString).Borders.Weight = Excel.XlBorderWeight.xlThin '添加表格线
        'oSheet.Range("A1", GetColumnChar(colcount) + (RowCount + 1).ToString).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter '设置垂直对齐方式
        'oSheet.Range("A1", GetColumnChar(colcount) + (RowCount + 1).ToString).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter '设置水平对齐方式
        'oSheet.Range("A1", GetColumnChar(colcount) + (RowCount + 1).ToString).Font.Size = 9 '设置字体
        'oSheet.Range("A1", GetColumnChar(colcount) + "1").Font.Bold = True '设置台头粗体
        'oSheet.Range("A1", GetColumnChar(colcount) + "1").Cells.Interior.Color = 16751001 '设置台头填充色

 

    End Function

 

 

'将datagrid导出到Aspose 的WorkSheet中
    '参数：
    'ws:aspose的worksheet
    'dg:DataGrid
    'name:worksheet 名称
    'rowcount:DataGrid中行数
    'Ischart:是否设置为字符串格式
    '格式说明：
    '表格线为：XlBorderWeight.xlThin
    '垂直对齐方式为：xlVAlignCenter
    '水平对齐方式为：xlHAlignCenter
    '字体：9
    '台头：粗体
    '台头填充色：16751001
    Private Function SetDataGridToAsposeWS(ByVal ws As Aspose.Cells.Worksheet, _
                                           ByVal dg As DataGrid, _
                                           ByVal name As String, _
                                           ByVal rowcount As Int32, _
                                           Optional ByVal Ischar As Boolean = True _
                                           )
        ws.Name = name

        '需要导出的列数
        Dim colcount As Int32 = 0
        For i As Int32 = 0 To dg.TableStyles(0).GridColumnStyles.Count - 1
            If dg.TableStyles(0).GridColumnStyles(i).Width > 0 Then
                ws.Cells(0, colcount).PutValue(dg.TableStyles(0).GridColumnStyles(i).HeaderText)

            End If

            For j As Int32 = 1 To rowcount
                ws.Cells(j, colcount).PutValue(dg.Item(j - 1, i).ToString)
            Next
            colcount += 1
        Next

        '设置格式
        '暂存
        Dim r As Aspose.Cells.Range
        Dim s As Aspose.Cells.Style
        Dim theStyleFlag As StyleFlag = New StyleFlag
        Dim sindex As Int32

        r = ws.Cells.CreateRange(0, 0, 1, colcount)
        sindex = ws.Workbook.Styles.Add()

        s = ws.Workbook.Styles(sindex)
        s.BackgroundColor = New System.Drawing.Color().FromArgb(16751001)
        s.Font.Size = 9

        s.Font.IsBold = True

        theStyleFlag.All = True
        r.Style = s

        sindex = ws.Workbook.Styles.Add()
        s = ws.Workbook.Styles(sindex)
        If Ischar Then
            s.Number = 49
        End If
        r = ws.Cells.CreateRange(0, 0, rowcount + 1, colcount)
        s.HorizontalAlignment = TextAlignmentType.Center
        s.VerticalAlignment = TextAlignmentType.Center
        s.Borders.SetStyle(CellBorderType.Thin)
        s.Borders.DiagonalStyle = CellBorderType.None
        r.ApplyStyle(s, theStyleFlag)

        ws.AutoFitColumns()

    End Function