本文詳細解答了如何使用Aspose.Cells導出DataGrid數據到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