使用Aspose.Cells导出DataGrid数据到Excel

原创|其它|编辑:郝浩|2012-10-17 09:40:42.000|阅读 529 次

概述: 本文详细解答了如何使用Aspose.Cells导出DataGrid数据到Excel的方法。

相关链接:

 本文详细解答了如何使用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


标签:Java.NET文档管理Excel

本站文章除注明转载外,均为本站原创或翻译。欢迎任何形式的转载,但请务必注明出处,尊重他人劳动成果

文章转载自:gsfw2010的专栏-CSDN

登录 慧都网发表评论


暂无评论...

为你推荐

  • 推荐视频
  • 推荐活动
  • 推荐产品
  • 推荐文章
  • 慧都慧问
相关厂商
相关产品
Aspose.Cells

专业的电子表格控件,无需MS Excel也可满足一切Excel表格功能。

在线客服 在线QQ 电话咨询
400-700-1020
反馈
在线客服系统
live chat