none
Tabla Dinamica con Etiquetas de elementos en de formatos esquematicos que muestre el campo siguiente en la misma columna(forma compacta) RRS feed

  • Pregunta

  • Buen dia:

    Tengo mi codigo desarrollado en vbNet2008, que me crea una tabla dinamica de acuerdo al resultado de un store procedure, logicamente son 5 columnas expandibles(Canal de venta,Vendedor,Linea de Producto,Grupo de Producto,Tipo de Producto)  y 12 columnas de Valores de venta acumuladas en cada mes del periodo mas la final la columna total. bajo el siguiente codigo:

     Sub Exportar_a_Excel_TDinamics()
            Dim xlapp As New Excel.Application
            Dim xlBook As Excel.Workbook
            Dim xlSheet As Excel.Worksheet
            Dim xlSheet1 As Excel.Worksheet
            Dim PRange As Excel.Range
            Dim FinalRow As Long
            Dim FinalCol As Long
            Dim ruta As String
            Dim col As String
            Dim sourcedata As String
            Try
                ruta = Application.StartupPath & "\pivoting\Estadisticas.xls"
                xlapp.Workbooks.Open(ruta)
                xlBook = xlapp.ActiveWorkbook()
                xlSheet1 = xlapp.ActiveSheet()
                FinalRow = xlSheet1.Cells(xlSheet1.Rows.Count, 1).End(Excel.XlDirection.xlUp).Row
                FinalCol = xlSheet1.Cells(2, xlSheet1.Columns.Count).End(Excel.XlDirection.xlToLeft).Column
                PRange = xlSheet1.Cells(1, 1).Resize(FinalRow, FinalCol)
                Select Case FinalCol
                    Case 1 : col = "A"
                    Case 2 : col = "B"
                    Case 3 : col = "C"
                    Case 4 : col = "D"
                    Case 5 : col = "E"
                    Case 6 : col = "F"
                    Case 7 : col = "G"
                    Case 8 : col = "H"
                    Case 9 : col = "I"
                    Case 10 : col = "J"
                    Case 11 : col = "K"
                    Case 12 : col = "L"
                    Case 13 : col = "M"
                    Case 14 : col = "N"
                    Case 15 : col = "O"
                    Case 16 : col = "P"
                    Case 17 : col = "Q"
                    Case 18 : col = "R"
                    Case 19 : col = "S"
                    Case 20 : col = "T"
                    Case 21 : col = "U"
                    Case 22 : col = "V"
                    Case 23 : col = "W"
                    Case 24 : col = "X"
                    Case 25 : col = "Y"
                    Case 26 : col = "Z"
                    Case 27 : col = "AA"
                    Case 28 : col = "AB"
                    Case 29 : col = "AC"
                    Case 30 : col = "AD"
                    Case 31 : col = "AE"
                    Case 32 : col = "AF"
                    Case 33 : col = "AG"
                    Case 34 : col = "AH"
                    Case 35 : col = "AI"
                    Case 36 : col = "AJ"
                    Case 37 : col = "AK"
                    Case 38 : col = "AL"
                    Case 39 : col = "AM"
                    Case 40 : col = "AN"
                    Case 41 : col = "AO"
                    Case 42 : col = "AP"
                    Case 43 : col = "AQ"
                    Case 44 : col = "AR"
                    Case 45 : col = "AS"
                    Case 46 : col = "AT"
                    Case 47 : col = "AU"
                    Case 48 : col = "AV"
                    Case 49 : col = "AW"
                    Case 50 : col = "AX"
                    Case 51 : col = "AY"
                    Case 52 : col = "AZ"
                    Case 53 : col = "BA"
                    Case 54 : col = "BB"
                    Case 55 : col = "BC"
                    Case 56 : col = "BD"
                    Case 57 : col = "BE"
                    Case 58 : col = "BF"
                    Case 59 : col = "BG"
                    Case 60 : col = "BH"
                    Case 61 : col = "BI"
                    Case 62 : col = "BJ"
                    Case 63 : col = "BK"
                    Case 64 : col = "BL"
                    Case 65 : col = "BM"
                    Case 66 : col = "BN"
                    Case 67 : col = "BO"
                    Case 68 : col = "BP"
                    Case 69 : col = "BQ"
                    Case 70 : col = "BR"
                    Case 71 : col = "BS"
                    Case 72 : col = "BT"
                    Case 73 : col = "BU"
                    Case 74 : col = "BV"
                    Case 75 : col = "BW"
                    Case 76 : col = "BX"
                    Case 77 : col = "BY"
                    Case 78 : col = "BZ"
                End Select
                sourcedata = "Hoja1!$A$2:$" + col.ToString + "$" + CStr(FinalRow - 1)
                MsgBox(sourcedata)
                xlSheet = xlapp.Worksheets.Add(Before:=xlapp.Worksheets("Hoja1"))
                xlSheet.Activate()
                xlSheet.Name = "Hoja4"
                xlBook.ActiveSheet.Cells(3, 1).Select()
                xlBook.PivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, sourcedata:=sourcedata, _
                             Version:=Excel.XlPivotTableVersionList.xlPivotTableVersion10).CreatePivotTable _
                             (TableDestination:=xlSheet.Range("A1:Z16"), TableName:="PivotTable1", _
                              DefaultVersion:=Excel.XlPivotTableVersionList.xlPivotTableVersion10)
                With xlSheet.PivotTables("PivotTable1")
                    .ColumnGrand = False
                    .RowGrand = False
                    .InGridDropZones = False
                    '.RowAxisLayout(xlOutlineRow)
                End With
                'LOS DATOS PTIVOT EN FILAS
                With xlSheet.PivotTables("PivotTable1").PivotFields("CANALVTA")
                    .Orientation = Excel.XlPivotFieldOrientation.xlRowField
                    .Position = 1
                End With
                With xlSheet.PivotTables("PivotTable1").PivotFields("VENDEDOR")
                    .Orientation = Excel.XlPivotFieldOrientation.xlRowField
                    .Position = 2
                End With
                With xlSheet.PivotTables("PivotTable1").PivotFields("LINEA")
                    .Orientation = Excel.XlPivotFieldOrientation.xlRowField
                    .Position = 3
                End With
                With xlSheet.PivotTables("PivotTable1").PivotFields("GRUPO")
                    .Orientation = Excel.XlPivotFieldOrientation.xlRowField
                    .Position = 4
                End With
                With xlSheet.PivotTables("PivotTable1").PivotFields("TIPO")
                    .Orientation = Excel.XlPivotFieldOrientation.xlRowField
                    .Position = 5
                End With
                xlSheet.PageSetup.Zoom = 190
                'TABULANDO LA DATA DEL CAMPO CALCULADO
                xlSheet.PivotTables("PivotTable1").AddDataField(xlSheet.PivotTables _
                                                  ("PivotTable1").PivotFields("ENERO"), _
                                                  "Suma_de_Enero", Excel.XlConsolidationFunction.xlSum)
                xlSheet.PivotTables("PivotTable1").AddDataField(xlSheet.PivotTables _
                                                             ("PivotTable1").PivotFields("FEBRERO"), _
                                                  "Suma_de_Febrero", Excel.XlConsolidationFunction.xlSum)
                xlSheet.PivotTables("PivotTable1").AddDataField(xlSheet.PivotTables _
                                      ("PivotTable1").PivotFields("MARZO"), _
                                      "Suma_de_Marzo", Excel.XlConsolidationFunction.xlSum)
                xlSheet.PivotTables("PivotTable1").AddDataField(xlSheet.PivotTables _
                                                             ("PivotTable1").PivotFields("Abril"), _
                                                             "Suma_de_Abril", Excel.XlConsolidationFunction.xlSum)
                xlSheet.PivotTables("PivotTable1").AddDataField(xlSheet.PivotTables _
                                      ("PivotTable1").PivotFields("Mayo"), _
                                      "Suma_de_Mayo", Excel.XlConsolidationFunction.xlSum)
                xlSheet.PivotTables("PivotTable1").AddDataField(xlSheet.PivotTables _
                                                             ("PivotTable1").PivotFields("Junio"), _
                                                             "Suma_de_JUnio", Excel.XlConsolidationFunction.xlSum)
                xlSheet.PivotTables("PivotTable1").AddDataField(xlSheet.PivotTables _
                                                 ("PivotTable1").PivotFields("Julio"), _
                                                 "Suma_de_Julio", Excel.XlConsolidationFunction.xlSum)
                xlSheet.PivotTables("PivotTable1").AddDataField(xlSheet.PivotTables _
                                                 ("PivotTable1").PivotFields("Agosto"), _
                                                 "Suma_de_Agosto", Excel.XlConsolidationFunction.xlSum)
                xlSheet.PivotTables("PivotTable1").AddDataField(xlSheet.PivotTables _
                                                 ("PivotTable1").PivotFields("Septiembre"), _
                                                 "Suma_de_Septiembre", Excel.XlConsolidationFunction.xlSum)
                xlSheet.PivotTables("PivotTable1").AddDataField(xlSheet.PivotTables _
                                                 ("PivotTable1").PivotFields("Octubre"), _
                                                 "Suma_de_Octubre", Excel.XlConsolidationFunction.xlSum)
                xlSheet.PivotTables("PivotTable1").AddDataField(xlSheet.PivotTables _
                                                 ("PivotTable1").PivotFields("Noviembre"), _
                                                 "Suma_de_Noviembre", Excel.XlConsolidationFunction.xlSum)
                xlSheet.PivotTables("PivotTable1").AddDataField(xlSheet.PivotTables _
                                                 ("PivotTable1").PivotFields("Diciembre"), _
                                                 "Suma_de_Diciembre", Excel.XlConsolidationFunction.xlSum)
                With xlSheet.PivotTables("PivotTable1").DataPivotField
                    .Orientation = Excel.XlPivotFieldOrientation.xlColumnField
                    .Position = 1
                End With
                xlBook.ShowPivotTableFieldList = False
                xlSheet.SaveAs(Application.StartupPath & "\pivoting\Estadisticas", _
                                        Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7)
                xlapp.Workbooks.Close()
                xlSheet = Nothing
                xlBook = Nothing
                If Not xlapp Is Nothing Then
                    xlapp.Quit()
                    xlapp = Nothing
                End If
                'Mato todos los procesos de excel.
                Try
                    MsgBox("Cartera de cuentas x cobrar se ha procesado con exito...", MsgBoxStyle.Information, "Proceso concluido!!...")
                    xcelKill()
                Catch ex As Exception
                    xcelKill()
                End Try
                MessageBox.Show("Proceso a finalizado", "Ctas x Cobrar", MessageBoxButtons.OK)
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
                xcelKill()
            End Try
        End Sub

    Todo este codigo me produce mi tabla dinamica en excel sin error alguno, pero la presentacion que deseo realizar es que en una sola columna

    se muestren los datos de las demas columnas como simulando un treeview en la tabla dinamica, en el excel 2007 manualmente se puede hacer en la configuracion del campo o de cada columna (click derecho del campo) en la pestaña diseño e impresion activamos "MOSTRAR ETIQUETAS DE ELEMENTO EN FORMATO ESQUEMATICO y marcamos los 2 checks que indican MOSTRAR ELEMENTO DEL CAMPO SIGUIENTE EN LA MISMA COLUMNA (FORMA COMPACTA) , luego marcamos MOSTRAR SUBTOTALES EN LA PARTE SUPERIOR DE CADA GRUPO Y ACEPTAMOS y asi sucesivamente con cada columa del rotulo de la fila"

    No es que me hagan mi trabajo pero he estado leyendo y buscando en la web ejemplos de estos tipos y no existen....

    martes, 22 de mayo de 2012 21:01

Todas las respuestas

  • cambia el parámetro xlPivotTableVersion10 por xlPivotTableVersion12

    ¿Es eso a lo que te refieres?

    Si me permites una observación, deberías de dedicarle un poquito de tiempo (que seguro no tienes) a optimizar la programación. Ese código no es más que la grabación de una macro con cuatro retoques. Si optimizas, aprenderás a programar, y tendrás menos dolores de cabeza. Con un par de bucles escribirías todo ese código en 4 líneas.

    Un saludo

    martes, 29 de mayo de 2012 14:55
  • Recien veo la solucion, es logico y clasico lo que indicas, pero primero lo que realizo es poner toda la codificacion posible en la probabilidad de los multisusos y multiquerys que puedo usar de diferentes ventanas(forms) de consulta de datos, cuando ha funcionando al 100%(voy quitando lo superdetallado para resumirlo,simplificarlo,reducirlo,abreviarlo) y con solo enviar parametros a mis clases ha de devolverme los reportes dinamicos que deseo, porque esto ha de ser reusable por diferentes reglas de negocio, en una sola codificacion.

    Hay peores codigos en la web, y ese codigo es cogido de la web hecho por un experto de esta zona, este es codigo que estoy adapatando a mis necesidades para terminar los objetos y las clases dentro de mi proyecto.

    Si eres un trome(EL BILL GATES de la programacion, o Estas en el TOP TEN de los desarrolladores), te recomiendo humildad,y sobre todo a compartir conocimiento con los demas,acuerdate de tus inicios y de tus metodologias, mucha gente coloca soluciones grandes o extensas pero son sus aportes depende de uno como lo transforme y los aplique en sus proyectos.

    "cambia el parámetro xlPivotTableVersion10 por xlPivotTableVersion12" a lo que me refiero sabio, no es esto.

    Saludos

    miércoles, 30 de mayo de 2012 21:17