ITSM/Reporting/Forms/frmAuswertung.vb

Imports C1.Win.C1TrueDBGrid
Imports System
Imports System.Data
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.Windows.Forms

Public Class frmAuswertung

#Region "Deklarationen"
    Dim Auswertungsdaten As DataTable
    Dim Auswertungsname As String


#End Region



    ''' <summary>
    ''' Initialisierung des Formulars
    ''' </summary>
    ''' <param name="Auswertungnr"></param>
    ''' <remarks></remarks>
    Sub New(ByVal Auswertungname As String, ByRef Auswerungsdaten As DataTable)
        Me.InitializeComponent()
        Me.Text = Me.Text + ": " + Auswertungname
        Me.Auswertungsdaten = Auswerungsdaten
        Me.Auswertungsname = Auswertungname
    End Sub

    Private Sub frmAuswertung_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.C1Daten.DataSource = Me.Auswertungsdaten
        Me.C1Daten.DataMember = Me.Auswertungsdaten.TableName

        Dim i As Integer
        For i = 0 To Me.Auswertungsdaten.Columns.Count - 1
            Me.C1Daten.Splits(0).DisplayColumns(i).AutoSize()
            If C1Daten.Columns(i).DataType.Name = "DateTime" Then
                C1Daten.Columns(i).NumberFormat = "dd.MM.yyyy HH:mm:ss"
            End If
            If C1Daten.Columns(i).Caption = "Journaleintrag" Then
                C1Daten.Splits(0).DisplayColumns(i).Width = 250
            End If
            C1Daten.Splits(0).DisplayColumns(i).Locked = True
        Next
    End Sub


    Private Sub TSBtnExcelExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TSBtnExcelExport.Click
        Dim xls As New XLSLib.clsXLSLib
        xls.Write_Excel(Me.Auswertungsdaten, True)
        Exit Sub
        Me.SaveFileDialog1.AddExtension = True
        Me.SaveFileDialog1.Filter = "Excel-Dateien|*.xls|CSV-Dateien|*.CSV"
        Me.SaveFileDialog1.DefaultExt.Insert(0, "*.xls")

        Me.SaveFileDialog1.ShowDialog()
        If Me.SaveFileDialog1.FileName <> "" Then
            If Me.SaveFileDialog1.FileName.EndsWith("xls") Then
                Me.DatatableToExcel(Me.Auswertungsdaten, Me.SaveFileDialog1.FileName)
            Else
                Me.C1Daten.ExportToDelimitedFile(Me.SaveFileDialog1.FileName, C1.Win.C1TrueDBGrid.RowSelectorEnum.AllRows, ";", "", "", True, System.Text.Encoding.Default.BodyName)
            End If
        End If

        Exit Sub

        If Me.C1Daten.Splits(0).Rows.Count > 0 Then
            Me.SaveFileDialog1.AddExtension = True
            Me.SaveFileDialog1.DefaultExt.Insert(0, "*.csv")
            Me.SaveFileDialog1.Filter = "CSV-Dateien|*.CSV"
            Me.SaveFileDialog1.ShowDialog()
            If Me.SaveFileDialog1.FileName <> "" Then
                '                Me.C1Daten.ExportToDelimitedFile(Me.SaveFileDialog1.FileName, C1.Win.C1TrueDBGrid.RowSelectorEnum.AllRows, ";", "", "", "", True, System.Text.Encoding.Default.BodyName)
                Me.C1Daten.ExportToDelimitedFile(Me.SaveFileDialog1.FileName, C1.Win.C1TrueDBGrid.RowSelectorEnum.AllRows, ";", "", "", True, System.Text.Encoding.Default.BodyName)
            End If

        End If
    End Sub

    Public Shared Function DatatableToExcel(ByVal aDataTable As DataTable, ByVal aOutputFilename As String) As Boolean
        Application.DoEvents()
        Dim f As New FrmFortschritt
        Dim app As New Object
        Dim wb As New Object
        Dim ws As New Object
        Try
            f.Show()
            Application.DoEvents()
            f.ProgressBar1.Minimum = 0
            f.ProgressBar1.Maximum = aDataTable.Rows.Count
            app = CreateObject("Excel.application")
            'Dim wb As Object
            'Dim ws As Object
            '        Dim app As New Excel.ApplicationClass
            '       Dim wb As Excel.Workbook
            '      Dim ws As Excel.Worksheet
            wb = app.Workbooks.add()
            'wb = app.Workbooks.Add()
            ws = wb.ActiveSheet()
            Dim dc As DataColumn
            Dim dr As DataRow
            Dim colIndex As Integer
            Dim rowIndex As Integer
            ' Columns erstellen
            For Each dc In aDataTable.Columns
                colIndex += 1
                app.Cells(1, colIndex) = dc.ColumnName
            Next
            ' Rows hinzuf<75>gen
            For Each dr In aDataTable.Rows
                f.ProgressBar1.Value = rowIndex
                rowIndex += 1
                colIndex = 0
                For Each dc In aDataTable.Columns
                    colIndex += 1
                    Try
                        app.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
                    Catch ex As Exception
                        Dim s As String = dr(dc.ColumnName)
                        s = "'" + s
                        Try
                            app.Cells(rowIndex + 1, colIndex) = s
                        Catch ex1 As Exception
                            app.Cells(rowIndex + 1, colIndex) = "'* Text beim Export entfernt"
                        End Try
                    End Try
                Next
            Next
            ws.Columns.AutoFit()
            wb.SaveAs(aOutputFilename)
            app.Workbooks.Open(aOutputFilename)
            ' Excel anzeigen wenn fertig exportiert
            app.Visible = True
            ws = Nothing
            wb = Nothing
            app = Nothing

            f.Close()
            Return True
        Catch ex As Exception
            f.Close()
            MsgBox(ex.Message)
        End Try


    End Function


    Private Sub TSBtnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TSBtnPrint.Click
        Dim fontbold As New Font("Arial", 12, FontStyle.Bold)
        Dim fontregular As New Font("Arial", 12, FontStyle.Regular)

        With Me.C1Daten.PrintInfo
            .PageHeaderStyle.Font = fontbold
            .PageSettings.Margins.Bottom = 20
            .PageSettings.Margins.Top = 80
            .PageSettings.Landscape = True
            .PageFooter = "Seite \p von \P" & vbLf & "Druckdatum " & Now()
            .PageHeader = Me.Auswertungsname
            .ShowProgressForm = True
            .UseGridColors = True
            .RepeatColumnHeaders = True
            .VarRowHeight = .RowHeightEnum.StretchToFit
            .WrapText = C1.Win.C1TrueDBGrid.PrintInfo.WrapTextEnum.NoWrap
            .PageBreak = .PageBreaksEnum.OnColumn
            .FillAreaWidth = .FillEmptyEnum.None
            .PrintPreview()
        End With

    End Sub


    Private Sub TSBtnQuit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TSBtnQuit.Click
        Me.Close()
    End Sub

    Private Sub BeendenToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BeendenToolStripMenuItem.Click
        Me.Close()
    End Sub



    Private Sub C1Daten_FetchScrollTips(ByVal sender As Object, ByVal e As C1.Win.C1TrueDBGrid.FetchScrollTipsEventArgs) Handles C1Daten.FetchScrollTips
        Try
            Select Case e.ScrollBar
                Case C1.Win.C1TrueDBGrid.ScrollBarEnum.Horizontal
                    e.ScrollTip = Me.C1Daten.Columns(e.ColIndex).Caption
                Case C1.Win.C1TrueDBGrid.ScrollBarEnum.Vertical
                    e.ScrollTip = CStr(e.Row + 1) & " / " & CStr(Me.Auswertungsdaten.Rows.Count)
            End Select
            e.TipStyle.ForeColor = Color.Blue
        Catch
        End Try

    End Sub

    Private Sub ContextMenuStrip1_Opening(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles ContextMenuStrip1.Opening
        If Me.C1Daten.SelectedCols.Count > 0 Then SpalteL<EFBFBD>schenToolStripMenuItem.Enabled = True Else Me.SpalteL<EFBFBD>schenToolStripMenuItem.Enabled = False
    End Sub

    Private Sub SpalteL<EFBFBD>schenToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SpalteL<EFBFBD>schenToolStripMenuItem.Click
        Dim i As Integer
        For i = 0 To Me.C1Daten.SelectedCols.Count - 1
            Me.Auswertungsdaten.Columns.Remove(Me.C1Daten.SelectedCols(i).Caption)
            Me.Auswertungsdaten.AcceptChanges()
        Next
        Me.C1Daten.DataSource = Nothing
        Me.C1Daten.DataSource = Me.Auswertungsdaten
        Me.C1Daten.DataMember = Me.Auswertungsdaten.TableName

        For i = 0 To Me.Auswertungsdaten.Columns.Count - 1
            Me.C1Daten.Splits(0).DisplayColumns(i).AutoSize()
            If C1Daten.Columns(i).DataType.Name = "DateTime" Then
                C1Daten.Columns(i).NumberFormat = "dd.MM.yyyy HH:mm:ss"
            End If
            If C1Daten.Columns(i).Caption = "Journaleintrag" Then
                C1Daten.Splits(0).DisplayColumns(i).Width = 250
            End If
            C1Daten.Splits(0).DisplayColumns(i).Locked = True
        Next

    End Sub
End Class