本文詳細(xì)解答了如何使用Aspose.Cells導(dǎo)出DataGrid數(shù)據(jù)到Excel的方法。

	 '導(dǎo)出數(shù)據(jù)到EXCEL文件

	    '---------------------------------------------------------------------------------------

	    '說明：只能導(dǎo)出一個(gè)DATAGRID，并將DATAGRID中顯示的數(shù)據(jù)保存到EXCEL中

	    '    DATAGRID只能而且必須存在一個(gè)TableStyle，且必須包含可以顯示的列

	    '參數(shù)：     dg：需要導(dǎo)出的DATAGRID實(shí)例

	    '           RowCount：需要導(dǎo)出的行數(shù)

	    '           SheetName：EXCEL中Sheet的名稱

	    '           FilePath：文件保存的全路徑名

	    '           IsChar：是否設(shè)置成字符格式

	    '返回：     導(dǎo)出結(jié)果

 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("打開文件失敗！", "錯(cuò)誤", 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 '設(shè)置垂直對(duì)齊方式
        'oSheet.Range("A1", GetColumnChar(colcount) + (RowCount + 1).ToString).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter '設(shè)置水平對(duì)齊方式
        'oSheet.Range("A1", GetColumnChar(colcount) + (RowCount + 1).ToString).Font.Size = 9 '設(shè)置字體
        'oSheet.Range("A1", GetColumnChar(colcount) + "1").Font.Bold = True '設(shè)置臺(tái)頭粗體
        'oSheet.Range("A1", GetColumnChar(colcount) + "1").Cells.Interior.Color = 16751001 '設(shè)置臺(tái)頭填充色
 

    End Function
 
 
'將datagrid導(dǎo)出到Aspose 的WorkSheet中
    '參數(shù)：
    'ws:aspose的worksheet
    'dg:DataGrid
    'name:worksheet 名稱
    'rowcount:DataGrid中行數(shù)
    'Ischart:是否設(shè)置為字符串格式
    '格式說明：
    '表格線為：XlBorderWeight.xlThin
    '垂直對(duì)齊方式為：xlVAlignCenter
    '水平對(duì)齊方式為：xlHAlignCenter
    '字體：9
    '臺(tái)頭：粗體
    '臺(tái)頭填充色：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
        '需要導(dǎo)出的列數(shù)
        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

        '設(shè)置格式
        '暫存
        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