12 votes

How to create pivot tables using C# from Excel?

I need exactly what I ask for, using c#create a pivot table in EXCEL with the formatting that this would do, being most similar possible.

This program is part of a larger development. only the part of the code that refers to this and I have put at the bottom a data table generated by me are necessary to be able to recreate the example I have.

I feel very lost and that's why I ask here, after trying several things for days.

Apart from creating the table you have to pass the data from SQL to Excel, but that part is not very complicated so I do not think it is necessary to put it, since it is a previous step to achieve what I reflect below.

The little progress I have is in the code below, but still nothing works.

((With the data provided here there should be a complete minimum verifiable example of what can be done)).

This is what I have for now:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Office.Interop.Excel;
using System.Threading;
using System.IO;

namespace emcvTablasDinamicas
{
    class Program
    {
        static void Main(string[] args)
        {
            generarTabla();
        }

        public static void generarTabla()
        {

            int cError = 0;

            Boolean error;
            Application xlApp = null;
            do
            {
                error = false;
                try
                {
                    xlApp = new Microsoft.Office.Interop.Excel.Application();
                }
                catch (Exception)
                {
                    Thread.Sleep(1000);
                    error = true;
                    cError++;
                }
            } while (error && cError < 10);
            Workbook xlWorkbook;
            String excelpath = System.AppDomain.CurrentDomain.BaseDirectory + "EMCV.xlsx";

            xlWorkbook = xlApp.Workbooks.Open(excelpath);
            Worksheet xlWorksheet = xlWorkbook.Sheets[1];

//De aquí en adelante empiezan los fallos, no sé como solucionarlos o que alternativas podría usar
            //ESTA REGION NO FUNCIONA. No vale ni pa cagar, pero en los ejemplos de codeproject pone que si//
            #region "Aquí creamos la tabla dinámica"
            //Cantidad de datos en este caso son 100, normalmente esto se recibe de una Lista y hago Lista.Count
            int cantidadDeDatos = 100;
            Range pivotData = xlWorksheet.Range["N35:R" + Convert.ToString(500 + cantidadDeDatos + 1)];//Cantidad de datos +1 ya que también quiero incluir el rango de los alias. 

            //Ya a partir de aquí no sé exactamente lo que pasa, entendía que pivotDestination era donde iría la tabla.
            Range pivotDestination = xlWorksheet.Range["A1:A1", Type.Missing]; 

            PivotCache oPivotCache = (PivotCache)xlWorkbook.PivotCaches().Add(XlPivotTableSourceType.xlDatabase, pivotData);
            PivotCaches pch = xlWorkbook.PivotCaches();
            pch.Add(XlPivotTableSourceType.xlDatabase, pivotData).CreatePivotTable(xlWorksheet.Cells[1, 1], "Franjas horarias", Type.Missing, Type.Missing);
            PivotTable pvt = xlWorksheet.PivotTables("Franjas horarias") as PivotTable;

            //DEFINIR 'FILTRO DE INFORME'
            PivotField fld = ((PivotField)pvt.PivotFields("Platform"));
            fld.Orientation = XlPivotFieldOrientation.xlPageField;
            fld.set_Subtotals(1, false);

            //DEFINIR FILTROS DE FILA
            fld = ((PivotField)pvt.PivotFields("Mes"));
            fld.Orientation = XlPivotFieldOrientation.xlRowField;
            fld.set_Subtotals(1, false);

            //DEFINIR FILTROS DE FILA
            fld = ((PivotField)pvt.PivotFields("Dia"));
            fld.Orientation = XlPivotFieldOrientation.xlRowField;
            fld.set_Subtotals(1, false);

            fld = ((PivotField)pvt.PivotFields("Count"));
            fld.Orientation = XlPivotFieldOrientation.xlDataField;
            //Se supone que este método le funciona a todo dios, todo el mundo lo usa en internet pero a mi me dice
            //'_Worksheet.PivotTables(object)' es un método, que no es válida en el contexto indicado.

            #endregion

            xlWorkbook.Save();
            xlWorkbook.Close();
            xlApp.Quit();

        }

    }
}

This is the process that is normally done:

1- Select data in Excel

2- click on pivot table

3- Position column names in each location

Menú de excel

Then we do this so that the pivot table is created.

Creación de tabla dinámica

I've been looking for similar things on the internet but I haven't found any examples that work for me, or that I can use, or anything.

Any way to adapt the data to the format?

In the end, what I will end up with is something along these lines

Ejemplo de formato

I ADD HERE DATA THAT CAN BE TRANSFERRED TO EXCEL IN CASE SOMEONE WANTS TO TEST THE PROGRAM.

+-----+--------+-----+-----+------+-------+
|     |   A    |  B  |  C  |  D   |   E   |
+-----+--------+-----+-----+------+-------+
|   1 | Alias  | Mes | Dia | Hora | Count |
|   2 | Alias1 | 1   | 2   | 8    | 6     |
|   3 | Alias2 | 3   | 2   | 9    | 28    |
|   4 | Alias1 | 1   | 2   | 10   | 36    |
|   5 | Alias2 | 1   | 2   | 11   | 41    |
|   6 | Alias2 | 1   | 2   | 12   | 31    |
|   7 | Alias1 | 2   | 2   | 13   | 23    |
|   8 | Alias1 | 2   | 2   | 14   | 10    |
|   9 | Alias2 | 2   | 2   | 15   | 12    |
|  10 | Alias2 | 2   | 2   | 16   | 24    |
|  11 | Alias1 | 1   | 2   | 17   | 11    |
|  12 | Alias2 | 1   | 2   | 18   | 17    |
|  13 | Alias2 | 1   | 2   | 19   | 4     |
|  14 | Alias1 | 1   | 2   | 20   | 3     |
|  15 | Alias2 | 1   | 2   | 21   | 1     |
|  16 | Alias1 | 2   | 3   | 8    | 7     |
|  17 | Alias1 | 2   | 3   | 9    | 23    |
|  18 | Alias2 | 2   | 3   | 10   | 44    |
|  19 | Alias1 | 2   | 3   | 11   | 42    |
|  20 | Alias2 | 1   | 3   | 12   | 41    |
|  21 | Alias1 | 1   | 3   | 13   | 26    |
|  22 | Alias2 | 1   | 3   | 14   | 6     |
|  23 | Alias1 | 1   | 3   | 15   | 4     |
|  24 | Alias1 | 1   | 3   | 16   | 10    |
|  25 | Alias1 | 3   | 3   | 17   | 22    |
|  26 | Alias1 | 1   | 3   | 18   | 8     |
|  27 | Alias2 | 3   | 3   | 19   | 5     |
|  28 | Alias1 | 1   | 3   | 20   | 7     |
|  29 | Alias2 | 1   | 3   | 21   | 2     |
|  30 | Alias1 | 1   | 4   | 8    | 6     |
|  31 | Alias2 | 1   | 4   | 9    | 24    |
|  32 | Alias1 | 1   | 4   | 10   | 29    |
|  33 | Alias2 | 2   | 4   | 11   | 26    |
|  34 | Alias2 | 2   | 4   | 12   | 18    |
|  35 | Alias1 | 2   | 4   | 13   | 16    |
|  36 | Alias1 | 2   | 4   | 14   | 10    |
|  37 | Alias1 | 2   | 4   | 15   | 12    |
|  38 | Alias1 | 2   | 4   | 16   | 13    |
|  39 | Alias2 | 1   | 4   | 17   | 20    |
|  40 | Alias1 | 1   | 4   | 18   | 13    |
|  41 | Alias2 | 1   | 4   | 19   | 6     |
|  42 | Alias2 | 1   | 4   | 20   | 4     |
|  43 | Alias1 | 1   | 4   | 21   | 2     |
|  44 | Alias2 | 1   | 5   | 8    | 7     |
|  45 | Alias1 | 1   | 5   | 9    | 21    |
|  46 | Alias1 | 3   | 5   | 10   | 34    |
|  47 | Alias1 | 3   | 5   | 11   | 37    |
|  48 | Alias2 | 3   | 5   | 12   | 15    |
|  49 | Alias1 | 3   | 5   | 13   | 17    |
|  50 | Alias2 | 3   | 5   | 14   | 15    |
|  51 | Alias2 | 1   | 5   | 15   | 9     |
|  52 | Alias2 | 1   | 5   | 16   | 9     |
|  53 | Alias1 | 1   | 5   | 17   | 9     |
|  54 | Alias2 | 1   | 5   | 18   | 3     |
|  55 | Alias1 | 1   | 5   | 19   | 2     |
|  56 | Alias1 | 1   | 5   | 20   | 3     |
|  57 | Alias1 | 1   | 5   | 21   | 2     |
|  58 | Alias1 | 2   | 6   | 14   | 1     |
|  59 | Alias2 | 3   | 8   | 8    | 29    |
|  60 | Alias2 | 3   | 8   | 9    | 42    |
|  61 | Alias2 | 3   | 8   | 10   | 51    |
|  62 | Alias2 | 2   | 8   | 11   | 48    |
|  63 | Alias2 | 2   | 8   | 12   | 47    |
|  64 | Alias1 | 2   | 8   | 13   | 47    |
|  65 | Alias1 | 2   | 8   | 14   | 16    |
|  66 | Alias2 | 1   | 8   | 15   | 8     |
|  67 | Alias1 | 1   | 8   | 16   | 23    |
|  68 | Alias1 | 1   | 8   | 17   | 26    |
|  69 | Alias1 | 1   | 8   | 18   | 23    |
|  70 | Alias1 | 2   | 8   | 19   | 11    |
|  71 | Alias2 | 2   | 8   | 20   | 4     |
|  72 | Alias1 | 2   | 8   | 21   | 1     |
|  73 | Alias1 | 2   | 9   | 8    | 15    |
|  74 | Alias2 | 3   | 9   | 9    | 33    |
|  75 | Alias1 | 3   | 9   | 10   | 48    |
|  76 | Alias1 | 3   | 9   | 11   | 43    |
|  77 | Alias2 | 2   | 9   | 12   | 48    |
|  78 | Alias1 | 3   | 9   | 13   | 29    |
|  79 | Alias1 | 1   | 9   | 14   | 14    |
|  80 | Alias1 | 1   | 9   | 15   | 8     |
|  81 | Alias1 | 1   | 9   | 16   | 24    |
|  82 | Alias2 | 1   | 9   | 17   | 21    |
|  83 | Alias1 | 3   | 9   | 18   | 17    |
|  84 | Alias1 | 2   | 9   | 19   | 9     |
|  85 | Alias1 | 1   | 9   | 20   | 6     |
|  86 | Alias1 | 2   | 9   | 21   | 2     |
|  87 | Alias2 | 2   | 10  | 8    | 10    |
|  88 | Alias2 | 2   | 10  | 9    | 40    |
|  89 | Alias2 | 1   | 10  | 10   | 61    |
|  90 | Alias1 | 2   | 10  | 11   | 35    |
|  91 | Alias1 | 3   | 10  | 12   | 27    |
|  92 | Alias2 | 1   | 10  | 13   | 25    |
|  93 | Alias2 | 2   | 10  | 14   | 14    |
|  94 | Alias1 | 1   | 10  | 15   | 28    |
|  95 | Alias1 | 1   | 10  | 16   | 19    |
|  96 | Alias2 | 3   | 10  | 17   | 26    |
|  97 | Alias1 | 3   | 10  | 18   | 12    |
|  98 | Alias2 | 3   | 10  | 19   | 6     |
|  99 | Alias1 | 3   | 10  | 20   | 1     |
| 100 | Alias1 | 3   | 10  | 21   | 5     |
+-----+--------+-----+-----+------+-------+

3voto

Aritzbn Points 1423

I am going to leave a possible solution here, although it is not the most correct or ideal, it is the one that has worked for me after a long time.

In a very summarized form this is what I have done:

1- Crear hoja auxiliar
2- Insertar datos que se utilizarán en la tabla, extraídos de SQL, en hoja auxiliar
3- Grabar macro con excel, teniendo en cuenta la hoja auxiliar
4- Pasar el código de la macro generada por excel a Documento de texto
5- Ejecutarla usando las librerías de interop
6- Reestructurar el excel como quiero (nombres de hojas etc.)

To generate the macro in excel:

Vista>Macros>Grabar Macro

Run it from c#

        Boolean error;
        int cError = 0;
        Excel.Application xlApp = new Excel.Application();
        Excel.Workbook xlWorkbook = null;

        do
        {
            error = false;
            try
            {
                xlWorkbook = xlApp.Workbooks.Open("Ruta del archivo+archivo.xls");
                xlApp.DisplayAlerts = false;
            }
            catch (Exception)
            {
                Thread.Sleep(1000);
                error = true;
                cError++;
            }
        } while (error && cError < 10);

        Excel.Worksheet xlWorksheet = xlWorkbook.Sheets["Hoja1"];
        Microsoft.Vbe.Interop.VBComponent oModule;

        oModule = xlWorkbook.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);

        oModule.CodeModule.AddFromString(File.ReadAllText(System.AppDomain.CurrentDomain.BaseDirectory + "\\Macro.txt"));

        xlWorkbook.Application.Run("Nombre de la funcion");

This way you can run a VBA module in excel, where in theory you have programmed or have programmed the generation of pivot tables.

Something to keep in mind is that, when creating pivot tables, you can not do that they are created in Sheet1 cell A1, it gives a very generic error, both doing it from excel itself as when running it as a module. The way excel allows you to do it is to create it in a new sheet always, this way it does not give error.

0voto

Erick Garcia Points 1

You can try something like this, I have tried this code in Visual C# and it works fine. In step 2 I have prefixed a list of 8 objects, but you can replace it with your list read from the database:

            //1.
            var AppExcel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook LibroExcel;
            Microsoft.Office.Interop.Excel.Worksheet HojaExcel;

            /*
             TITULOS DE LA LISTA DE MEDICIONES
             */
            LibroExcel = AppExcel.Workbooks.Add();
            HojaExcel = LibroExcel.Worksheets.Add();

            //2.
            int nroColumnas = 3;
            int nroFilas = 8;
            var data = new object[nroFilas, nroColumnas];
            int fila = 100;

            HojaExcel.Rows.Item[1].Font.Bold = 1;
            HojaExcel.Rows.Item[1].Font.size = 12;

            HojaExcel.Cells[fila, 31] = "PAIS";
            HojaExcel.Cells[fila, 32] = "CIUDAD";
            HojaExcel.Cells[fila, 33] = "VENTAS";
            fila++;

            HojaExcel.Cells[fila, 31] = "PERU";
            HojaExcel.Cells[fila, 32] = "LIMA";
            HojaExcel.Cells[fila, 33] = 2015;
            fila++;
            HojaExcel.Cells[fila, 31] = "PERU";
            HojaExcel.Cells[fila, 32] = "TRUJILLO";
            HojaExcel.Cells[fila, 33] = 4524;
            fila++;
            HojaExcel.Cells[fila, 31] = "COLOMBIA";
            HojaExcel.Cells[fila, 32] = "CALI";
            HojaExcel.Cells[fila, 33] = 452782;
            fila++;
            HojaExcel.Cells[fila, 31] = "COLOMBIA";
            HojaExcel.Cells[fila, 32] = "BOGOTA";
            HojaExcel.Cells[fila, 33] = 42577;
            fila++;
            HojaExcel.Cells[fila, 31] = "BOLIVIA";
            HojaExcel.Cells[fila, 32] = "LA PAZ";
            HojaExcel.Cells[fila, 33] = 4747;
            fila++;
            HojaExcel.Cells[fila, 31] = "BRAZIL";
            HojaExcel.Cells[fila, 32] = "SAO APULO";
            HojaExcel.Cells[fila, 33] = 4524;
            fila++;
            HojaExcel.Cells[fila, 31] = "ECUADOR";
            HojaExcel.Cells[fila, 32] = "QUITO";
            HojaExcel.Cells[fila, 33] = 27578;
            fila++;
            HojaExcel.Cells[fila, 31] = "ECUADOR";
            HojaExcel.Cells[fila, 32] = "CUENCA";
            HojaExcel.Cells[fila, 33] = 57789;
            fila++;

            Excel.Range c1 = (Excel.Range)HojaExcel.Cells[100, 31]; //1, 111
            Excel.Range c2 = (Excel.Range)HojaExcel.Cells[nroFilas + 100, 31 + nroColumnas - 1];
            Excel.Range range = HojaExcel.get_Range(c1, c2);
            //range.Value = data;

            ////3.
            Excel.Range oRange = range; // HojaExcel.UsedRange;
            Excel.PivotCache oPivotCache = (Excel.PivotCache)LibroExcel.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange);  // Set the Source data range from First sheet
            //Excel.Range oRange2 = HojaExcel.Cells[1, 1];
            Excel.PivotCaches pch = LibroExcel.PivotCaches();
            pch.Add(Excel.XlPivotTableSourceType.xlDatabase, oRange).CreatePivotTable(HojaExcel.Cells[1, 1], "PivTbl_1", Type.Missing, Type.Missing);// Create Pivot table
            Excel.PivotTable pvt = HojaExcel.PivotTables("PivTbl_1") as Excel.PivotTable;

            pvt.ShowDrillIndicators = false;  // Used to remove the Expand/ Collapse Button from each cell

            Excel.PivotField fld = ((Excel.PivotField)pvt.PivotFields("PAIS")); // Create a Pivot Field in Pivot table
            fld.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            fld.set_Subtotals(1, false);

            fld = ((Excel.PivotField)pvt.PivotFields("CIUDAD"));
            fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            fld.set_Subtotals(1, false);

            fld = ((Excel.PivotField)pvt.PivotFields("VENTAS"));
            fld.Orientation = Excel.XlPivotFieldOrientation.xlDataField;

            //HojaExcel.UsedRange.Columns.AutoFit();  // Used to Autoset the column width according to data 
            pvt.ColumnGrand = false;  // Used to hide Grand total for columns
            pvt.RowGrand = false; // Used to hide Grand total for Rows

            HojaExcel.Columns.AutoFit();
            //HojaExcel.Rows.AutoFit();
            AppExcel.Application.Visible = true;
            HojaExcel = null;
            LibroExcel = null;
            AppExcel = null;

HolaDevs.com

HolaDevs is an online community of programmers and software lovers.
You can check other people responses or create a new question if you don't find a solution

Powered by:

X