Home / Questions / 146116
Accepted
Aritzbn
Asked: 2020-03-15 02:10:57 +0800 CST 2020-03-15 02:10:57 +0800 CST

How to create pivot tables using C# from Excel?

  • 772

I need exactly what I ask for, using c# to create a dynamic table in EXCEL with the format that this would do, being as similar as possible.

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

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

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

The few advances I have are in the code below, but still nothing works.

((With the data posted here there should be a complete minimum and verifiable example from which things 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 the data in Excel

2- hit pivot table

3- Position the column names in each place

excel menu

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

Pivot table creation

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 have to stay is something like this

format example

I ADD HERE DATA THAT CAN BE PASSED TO EXCEL IN CASE SOMEONE WANTS TO TRY 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     |
+-----+--------+-----+-----+------+-------+
c#

2 Answers

  1. Best Answer

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

    Briefly, 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");

    In this way you can execute a VBA module in excel, where in theory you have programmed or the generation of pivot tables has been programmed.

    Something to keep in mind is that, when creating the pivot tables, you cannot create them in Sheet1 cell A1, it gives a very generic error, both when doing it from excel itself and when executing it as a module. The way that allows excel to do it is to always create it in a new sheet, in this way it does not give an error.

    • 3

  2. You can try something like this, I have tested 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;
    • 0