使用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

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值