Imports Microsoft.VisualBasic
Imports System.Data

Public Class Reportes

#Region "Constantes para Excel"
    Private Const xlDiagonalDown As Byte = 5
    Private Const xlDiagonalUp As Byte = 6
    Private Const xlEdgeLeft As Byte = 7
    Private Const xlEdgeTop As Byte = 8
    Private Const xlEdgeBottom As Byte = 9
    Private Const xlEdgeRight As Byte = 10
    Private Const xlInsideVertical As Byte = 11
    Private Const xlInsideHorizontal As Byte = 12

    Private Const xlNone = &HFFFFEFD2
    Private Const xlAutomatic = &HFFFFEFF7
    Private Const xlContinuous As Byte = 1
    Private Const xlThin As Byte = 2

    Private Const xlDataLabelsShowLabelAndPercent As Byte = 5
    Private Const xlLocationAsNewSheet As Byte = 1
    Private Const xlLocationAsObject As Byte = 2

#End Region

    Public Function GenerarReporte(ByVal pstrTitulo As String, ByVal pdsDatos As DataSet, ByVal pdsResumen As DataSet, ByVal pstrDirectory As String) As String
        'funcion que envia a excel un tabla y una hoja
        'descomponiendo el dataset en las tablas que contenga y hoja por tabla
        Dim excelApp As Object 'Excel.Application
        Dim myworkBook As Object 'Excel.Workbook
        Dim ws As Object ' Excel.Worksheet
        Dim ConWs As Integer = 1
        Dim firstRow As Integer
        Dim row As Integer = 1
        Dim cell As Integer = 1
        Dim strNameSheet As String

        Dim strFileName As String
        strFileName = pstrDirectory & "\" & Date.Now.Day.ToString & Date.Now.Month.ToString & Date.Now.Year.ToString & Date.Now.Hour.ToString & Date.Now.Minute.ToString & Date.Now.Second.ToString & ".xls"

        Try
            'instanciamos a excel.application
            excelApp = CreateObject("Excel.Application") 'New Excel.Application()

            'Creamos una hoja en el libro predeterminado
            myworkBook = excelApp.Workbooks.Add()

            'Creamos una WorkSheet
            ws = myworkBook.Worksheets(ConWs)
            'Obtenemos el nombre de la WorkSheet
            strNameSheet = ws.Name

            'Insertamos el titulo
            ws.Cells(row, 1) = pstrTitulo
            'hacemos un rango para ponerlo en  negrita y darle formato
            'El Chr(65+el numero de columnas) es para sacar la letra hasta la que abarcara la tabla, para poder hacer un merge
            excelApp.Range("A" & row.ToString & ":" & Convert.ToString(Chr(65 + pdsDatos.Tables(0).Columns.Count - 1)) & row.ToString).Select()
            With excelApp.Selection
                .HorizontalAlignment = &HFFFFEFF4 'xlCenter
                .VerticalAlignment = &HFFFFEFF5 'xlBottom
                .WrapText = False
                .Orientation = 0
                .Font.Size = 12
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = True
                .Font.Bold = True
                .Merge()
            End With
            row += 2

            'para los encabezados se debe recuperar del dataset
            Dim H As Integer
            For H = 0 To pdsDatos.Tables(0).Columns.Count - 1
                ws.Cells(row, cell) = pdsDatos.Tables(0).Columns(H).Caption()
                ws.Columns(cell).autoFit()
                cell += 1
            Next
            excelApp.Range("A" & row.ToString & ":" & Convert.ToString(Chr(64 + cell - 1)) & row.ToString).Select()
            With excelApp.Selection
                .HorizontalAlignment = &HFFFFEFF4 'xlCenter
                .VerticalAlignment = &HFFFFEFF5 'xlBottom
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
                .Font.Bold = True
                .Interior.ColorIndex = 15
            End With

            row += 1
            cell = 1

            'creando las filas
            Dim Dr As DataRow

            For Each Dr In pdsDatos.Tables(0).Rows
                For H = 0 To pdsDatos.Tables(0).Columns.Count - 1
                    ws.Cells(row, cell) = Dr.Item(H)
                    ws.Columns(cell).autoFit()
                    'Aqui checamos si se trata de una columna con contenido de pesos
                    If LCase(pdsDatos.Tables(0).Columns(H).ColumnName).IndexOf("monto") <> -1 Then
                        excelApp.Range(Convert.ToString(Chr(65 + H)) & row.ToString).Select()
                        excelApp.Selection.NumberFormat = "$#,##0.00"
                    End If
                    cell += 1
                Next
                cell = 1
                row += 1
            Next

            'TODO: Checar formato de cantidades
            'txtCantidad.Text = Format(ds.Tables(0).Rows(0)("cantidad"), "$#,##0.00")

            'Seleccionamos toda la tabla, para ponerle borde
            excelApp.Range("A3" & ":" & Convert.ToString(Chr(65 + pdsDatos.Tables(0).Columns.Count - 1)) & (row - 1).ToString).Select()
            excelApp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
            excelApp.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
            With excelApp.Selection.Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With excelApp.Selection.Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With excelApp.Selection.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With excelApp.Selection.Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With excelApp.Selection.Borders(xlInsideVertical)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With excelApp.Selection.Borders(xlInsideHorizontal)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With

            row += 2

            'Guardamos la posicion donde inicia la tabla de resumen
            firstRow = row

            cell = 1
            'para los encabezados se debe recuperar del dataset
            For H = 0 To pdsResumen.Tables(0).Columns.Count - 1
                ws.Cells(row, cell) = pdsResumen.Tables(0).Columns(H).Caption()
                ws.Columns(cell).autoFit()
                cell += 1
            Next
            'Agregamos una columna mas para el porcentaje
            ws.Cells(row, cell) = "Porcentaje"
            ws.Columns(cell).autoFit()
            cell += 1

            excelApp.Range("A" & row.ToString & ":" & Convert.ToString(Chr(64 + cell - 1)) & row.ToString).Select()
            With excelApp.Selection
                .HorizontalAlignment = &HFFFFEFF4 'xlCenter
                .VerticalAlignment = &HFFFFEFF5 'xlBottom
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
                .Font.Bold = True
                .Interior.ColorIndex = 15
            End With

            row += 1
            cell = 1

            For Each Dr In pdsResumen.Tables(0).Rows
                For H = 0 To pdsResumen.Tables(0).Columns.Count - 1
                    ws.Cells(row, cell) = Dr.Item(H)
                    ws.Columns(cell).autoFit()
                    cell += 1
                Next
                cell = 1
                row += 1
            Next

            'Agregamos la fila con el total
            ws.Cells(row, cell) = "Total"
            ws.Columns(cell).autoFit()

            cell += 1
            ws.Cells(row, cell) = "=SUM(B" & (firstRow + 1).ToString & ":B" & (row - 1).ToString & ")"
            ws.Columns(cell).autoFit()

            cell += 1
            'Agregamos una columna con los porcentajes
            For H = firstRow + 1 To row - 1
                excelApp.Range(Convert.ToString(Chr(65 + cell - 1)) & H.ToString).Select()
                excelApp.Selection.NumberFormat = "0.00%"
                ws.Cells(H, cell) = "=B" & H.ToString & "/B" & row.ToString
                ws.Columns(cell).autoFit()
            Next
            excelApp.Range(Convert.ToString(Chr(65 + cell - 1)) & H.ToString).Select()
            excelApp.Selection.NumberFormat = "0.00%"
            ws.Cells(row, cell) = "=SUM(C" & (firstRow + 1).ToString & ":C" & (row - 1).ToString & ")"
            ws.Columns(cell).autoFit()

            excelApp.Range("A" & row.ToString & ":C" & row.ToString).Select()
            excelApp.Selection.Font.Bold = True
            excelApp.Selection.Interior.ColorIndex = 37

            'Seleccionamos toda la tabla, para ponerle borde
            excelApp.Range("A" & firstRow.ToString & ":" & Convert.ToString(Chr(65 + pdsResumen.Tables(0).Columns.Count)) & row.ToString).Select()
            excelApp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
            excelApp.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
            With excelApp.Selection.Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With excelApp.Selection.Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With excelApp.Selection.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With excelApp.Selection.Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With excelApp.Selection.Borders(xlInsideVertical)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With excelApp.Selection.Borders(xlInsideHorizontal)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With

            row += 1

            excelApp.Range("A" & row.ToString).Select()

            excelApp.Charts.Add()
            excelApp.ActiveChart.ChartType = 5 'xlPie
            excelApp.ActiveChart.SetSourceData(Source:=excelApp.Sheets(ws.Name).Range("A" & (firstRow + 1).ToString & ":C" & (row - 2).ToString), PlotBy:=2) 'xlColumns)
            'excelApp.ActiveChart.Location(Where:=xlLocationAsNewSheet, Name:="Grafica")
            excelApp.ActiveChart.Location(Where:=xlLocationAsObject, Name:=strNameSheet)
            excelApp.ActiveChart.HasTitle = True
            excelApp.ActiveChart.ChartTitle.Characters.Text = pstrTitulo
            excelApp.ActiveChart.HasLegend = True
            excelApp.ActiveChart.ApplyDataLabels(Type:=xlDataLabelsShowLabelAndPercent, LegendKey _
                    :=False, HasLeaderLines:=True)
            'excelApp.ActiveChart.ChartArea.Select()

            'guardamos el excel
            ws.SaveAs(strFileName)
            'excelApp.Sheets(1).Select()
            'excelApp.Visible = True
            myworkBook.Close()
            excelApp.Quit()
        Catch e As Exception
            'se utiliza la version 10 de excel o XP
            Throw New Exception(e.Message + "     " + strFileName)
        Finally
            ws = Nothing
            myworkBook = Nothing
            excelApp = Nothing
        End Try

        Return strFileName
    End Function

End Class