excelexport.vb

一个管理系统

Option Strict Off 'Latebinding

Imports Excel

Public Class ExcelExport
    ' Export to Excel if it is installed on their machine

    Public Sub ExportTasks(ByVal TasksData As DataSet, ByVal TasksDataView As DataView, ByVal TasksGrid As DataGrid)

        Dim excelApp As Excel.Application
        Dim workBook As Excel.Workbook
        Dim ws As Excel.Worksheet
        Dim r As Excel.Range
        Dim c As Excel.Chart
        Dim row As Integer = 1
        Dim cell As Integer = 1
        Dim priorityHigh, priorityNormal, priorityLow As Integer

        Try
            'which ever version of Excel was referenced to build the interop file
            'should be installed on client machines
            excelApp = New Excel.Application()

            'create the task list on the first worksheet in the workbook
            workBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
            ws = CType(workBook.Worksheets(1), Worksheet)
        Catch
            'remove the excel references: Excel, Microsoft.Office.Core, and VBIDE and re-add
            'the COM reference Microsoft Excel 10.0 Type Library.
            MessageBox.Show("An incompatible version of Microsoft Excel is intalled on your system.")
            Return
        End Try

        ' Title
        ws.Name = "Task Management"
        ws.Cells(row, 1) = "Tasks as of " + Now.ToShortDateString()
        r = CType(ws.Cells(row, 1), Range)
        r.Font.Bold = True
        row += 2

        'create Headers
        Dim dc As DataColumn
        For Each dc In TasksData.Tables(0).Columns
            Dim cs As DataGridColumnStyle
            For Each cs In TasksGrid.TableStyles(0).GridColumnStyles
                If cs.Width <> 0 Then
                    If cs.MappingName = dc.ColumnName Then
                        ws.Cells(row, cell) = cs.HeaderText()
                        ws.Columns(cell).autoFit()
                        Exit For
                    End If
                End If
                cell += 1
            Next
            cell = 1
        Next
        row += 1

        'create rows
        priorityHigh = 0
        priorityNormal = 0
        priorityLow = 0

        Dim myRow As DataRow
        Dim i As Integer

        For i = 0 To TasksDataView.Count - 1
            For Each dc In TasksData.Tables(0).Columns
                Dim cs As DataGridColumnStyle
                For Each cs In TasksGrid.TableStyles(0).GridColumnStyles
                    If cs.Width <> 0 Then
                        If cs.MappingName = dc.ColumnName Then
                            If TasksDataView.Item(i).Item(dc.ColumnName.ToString()).GetType() Is GetType(DateTime) Then
                                ws.Cells(row, cell) = String.Format("{0:d}", TasksDataView.Item(i).Item(dc.ColumnName.ToString()))
                            Else
                                ws.Cells(row, cell) = TasksDataView.Item(i).Item(dc.ColumnName.ToString())
                            End If
                            If cs.MappingName = "PriorityText" Then
                                Select Case TasksDataView.Item(i).Item(dc.ColumnName.ToString())
                                    Case "Major"
                                        priorityHigh += 1
                                    Case "Medium"
                                        priorityNormal += 1
                                    Case "Minor"
                                        priorityLow += 1
                                End Select
                            End If
                            ws.Columns(cell).autoFit()
                            Exit For
                        End If
                    End If
                    cell += 1
                Next
                cell = 1
            Next
            row += 1
        Next

        ' Priority Summary and Chart on Second Worksheet
        ws = CType(workBook.Worksheets.Add(, ws), Worksheet)
        ws.Name = "Priority Summary"

        row = 1
        ws.Cells(row, 1) = "Priority Summary"
        r = CType(ws.Cells(row, 1), Range)
        r.Font.Bold = True
        row += 2
        ws.Cells(row, 1) = "Major"
        ws.Cells(row, 2) = priorityHigh.ToString
        row += 1
        ws.Cells(row, 1) = "Medium"
        ws.Cells(row, 2) = priorityNormal.ToString
        row += 1
        ws.Cells(row, 1) = "Minor"
        ws.Cells(row, 2) = priorityLow.ToString

        ws.Columns(1).autoFit()
        ws.Columns(2).autoFit()

        ' Create the chart
        r = ws.Range("A3", "B5")
        r.Select()
        c = ws.Parent.Charts.Add
        With c
            .ChartType = XlChartType.xl3DPieExploded
            .SetSourceData(r, XlRowCol.xlColumns)
            .Name = "Priority Summary Chart"
            .HasTitle = True
            .ChartTitle.Characters.Text = "Tasks by Priority"
            .Location(Excel.XlChartLocation.xlLocationAsObject, ws.Name)
        End With

        ' Show(Excel)
        excelApp.Sheets(1).Select()
        excelApp.Visible = True
    End Sub

End Class