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

如何在 Excel 中使用 C# 创建数据透视表？

  • 772

我需要的正是我所要求的，使用 c# 在 EXCEL 中创建一个动态表，其格式尽可能相似。

这个程序是一个更大的开发的一部分，我只取了引用这个的部分代码，我把我生成的数据表放在了底部，它们是能够重新创建我的例子所必需的有。

在尝试了几天之后，我感到非常迷茫，这就是为什么我在这里问的原因。

除了创建表之外，您还必须将数据从 SQL 传递到 Excel，但这部分不是一个很大的复杂性，所以我认为没有必要放置它，因为它是实现我在下面反映的内容的前一步.

我的一些进步在下面的代码中，但仍然没有任何效果。

（（（根据此处发布的数据，应该有一个完整的最小且可验证的示例，可以从中完成操作。））

这就是我现在所拥有的：

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();

        }




    }
}

这是通常完成的过程：

1- 在 Excel 中选择数据

2-命中数据透视表

3-将列名放置在每个位置

excel菜单

然后我们这样做以便为我们创建数据透视表。

数据透视表创建

我一直在互联网上寻找类似的东西，但我没有找到任何适合我的例子，或者我可以使用的例子，或者任何东西。

有什么方法可以使数据适应格式？

最后我要留下的是这样的

格式示例

我在这里添加了一些数据，如果有人想尝试这个程序，这些数据可以传递给 Excel。

+-----+--------+-----+-----+------+-------+
|     |   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

    我将在这里留下一个可能的解决方案，尽管它不是最正确或最理想的，但它在很长一段时间后对我有用。

    简而言之，这就是我所做的：

    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.)

    在 excel 中生成宏：

    Vista>Macros>Grabar Macro

    从 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");

    通过这种方式，您可以在 excel 中执行 VBA 模块，理论上您已经编程或已经编程了数据透视表的生成。

    需要记住的是，在创建数据透视表时，您不能在 Sheet1 单元格 A1 中创建它们，无论是从 excel 本身执行此操作还是将其作为模块执行时，都会出现非常一般的错误。允许 excel 执行此操作的方法是始终在新工作表中创建它，这样它就不会出错。

    • 3

  2. 你可以尝试这样的事情，我已经在 Visual C# 中测试了这段代码，它工作正常。在第 2 步中，我为 8 个对象的列表添加了前缀，但您可以将其替换为从数据库中读取的列表：

                //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