使用C＃在Excel中创建数据透视表我创建数据透视表时出错 [英] Create Pivot In Excel Using C# I Have Error When I Create The Pivot

问题描述

我是否会创建枢轴我得到错误如何解决这是我的代码

  string  fileTest =  @  C：\ Temp \ ExcelTest \ test.xlsx; 
 
 Excel.Application oApp; 
 Excel.Worksheet oSheet; 
 Excel.Workbook oBook; 
 
 oApp =  new  Excel.Application（）; 
 oBook = oApp.Workbooks.Add（Type.Missing）; 
 oSheet =（Excel.Worksheet）oBook.Worksheets.get_Item（ 1 ）; 
 
 oSheet.Cells [ 1 ， 1 ] =  名称; 
 oSheet.Cells [ 1 ， 2 ] =  薪水; 
 
 oSheet.Cells [ 2 ， 1 ] =   Frank; 
 oSheet.Cells [ 2 ， 2 ] =  150000 ; 
 
 oSheet.Cells [ 3 ， 1 ] =   Ann; 
 oSheet.Cells [ 3 ， 2 ] =  300000 ; 
 
  //  现在捕获第一张工作表的范围=我将需要这个来创建数据透视表 
 Excel.Range oRange = oSheet.get_Range（  A1，< span class =code-string>  B3）; 
 
  //  创建第二张 
 oSheet.Name = < span class =code-string> 数据透视表; 
 
  //  指定数据透视表的第一个单元格 
 Excel。范围oRange2 = oSheet.get_Range（  A1，   b3）; 
 
  //  创建数据透视表缓存和数据透视表 
 Excel。 PivotCache oPivotCache =（Excel.PivotCache）oBook.PivotCaches（）。Add（Excel.XlPivotTableSourceType.xlDatabase，oRange）; 
 
  //  我这行有错误 
 Excel。数据透视表oPivotTable =（Excel.PivotTable）oSheet.PivotTables（）。添加（oPivotCache，oRange2， 摘要）; 
 
  //  创建数据透视字段，请注意该名称将与工作表上的列名相同一个 
 Excel.PivotField oPivotField =（Excel.PivotField）oPivotTable.PivotFields（  Salary ）; 
 oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField; 
 oPivotField.Function = Excel.XlConsolidationFunction.xlSum; 
 oPivotField.Name =  薪水; 

解决方案

问题是您将枢轴放在原始数据上。 PivotTable.Add方法的 Range 参数定义了数据透视表的左上角。



尝试例如以下内容：

 Excel.PivotTable oPivotTable =（Excel.PivotTable）oSheet.PivotTables（）。Add（oPivotCache，oSheet.get_Range（  E1）， 摘要）; 

已添加：



完整的测试代码：

  string  fileTest =  @  C：\ Temp \ ExcelTest \ test.xlsx; 
 
 Microsoft.Office.Interop.Excel.Application oApp; 
 Microsoft.Office.Interop.Excel.Worksheet oSheet; 
 Microsoft.Office.Interop.Excel.Workbook oBook; 
 
 oApp =  new  Microsoft.Office.Interop.Excel.Application（）; 
 oApp.Visible =  true ; 
 oBook = oApp.Workbooks.Add（Type.Missing）; 
 oSheet =（Microsoft.Office.Interop.Excel.Worksheet）oBook.Worksheets.get_Item（ 1 ）; 
 
 oSheet.Cells [ 1 ， 1 ] =  名称; 
 oSheet.Cells [ 1 ， 2 ] =  薪水; 
 
 oSheet.Cells [ 2 ， 1 ] =   Frank; 
 oSheet.Cells [ 2 ， 2 ] =  150000 ; 
 
 oSheet.Cells [ 3 ， 1 ] =   Ann; 
 oSheet.Cells [ 3 ， 2 ] =  300000 ; 
 
  //  现在捕获第一张工作表的范围=我将需要这个来创建数据透视表 
 Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range（  A1 ，  B3）; 
 
  //  创建第二张 
 oSheet.Name = < span class =code-string> 数据透视表; 
 
  //  指定数据透视表的第一个单元格 
 Microsoft。 Office.Interop.Excel.Range oRange2 = oSheet.get_Range（  A1，  b3）; 
 
  //  创建数据透视表缓存和数据透视表 
 Microsoft。 Office.Interop.Excel.PivotCache oPivotCache =（Microsoft.Office.Interop.Excel.PivotCache）oBook.PivotCaches（）。Add（Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase，oRange）; 
 
  //  我在此行上有错误 
 Microsoft。 Office.Interop.Excel.PivotTable oPivotTable =（Microsoft.Office.Interop.Excel.PivotTable）oSheet.PivotTables（）。Add（oPivotCache，oSheet.get_Range（ < span class =code-string> E1，  E1），< span class =code-string> 摘要）; 
 
  //  创建数据透视字段，请注意该名称将与工作表上的列名相同一个 
 Microsoft.Office.Interop.Excel.PivotField oPivotField =（Microsoft.Office.Interop.Excel.PivotField）oPivotTable.PivotFields（ 薪水）; 
 oPivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField; 
 oPivotField.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum; 
 oPivotField.Name =  薪水; 

Will i create the pivot i got the error how to solve this is my code

string fileTest = @"C:\Temp\ExcelTest\test.xlsx";

Excel.Application oApp;
Excel.Worksheet oSheet;
Excel.Workbook oBook;

oApp = new Excel.Application();
oBook = oApp.Workbooks.Add(Type.Missing);
oSheet = (Excel.Worksheet)oBook.Worksheets.get_Item(1);

oSheet.Cells[1, 1] = "Name";
oSheet.Cells[1, 2] = "Salary";

oSheet.Cells[2, 1] = "Frank";
oSheet.Cells[2, 2] = 150000;

oSheet.Cells[3, 1] = "Ann";
oSheet.Cells[3, 2] = 300000;

// now capture range of the first sheet = I will need this to create pivot table
Excel.Range oRange = oSheet.get_Range("A1", "B3");

// create second sheet
oSheet.Name = "Pivot Table";

// specify first cell for pivot table
Excel.Range oRange2 = oSheet.get_Range("A1", "b3");

// create Pivot Cache and Pivot Table
Excel.PivotCache oPivotCache = (Excel.PivotCache)oBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange);

// I have error on this line
Excel.PivotTable oPivotTable = (Excel.PivotTable)oSheet.PivotTables().Add(oPivotCache, oRange2, "Summary");

// create Pivot Field, note that name will be the same as column name on sheet one
Excel.PivotField oPivotField = (Excel.PivotField)oPivotTable.PivotFields("Salary");
oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
oPivotField.Function = Excel.XlConsolidationFunction.xlSum;
oPivotField.Name = " Salary";

解决方案

The problem is that you're placing the pivot on the original data. The Range parameter of the PivotTable.Add method defines the upper-left corner of the pivot table.

Try for example the following:

Excel.PivotTable oPivotTable = (Excel.PivotTable)oSheet.PivotTables().Add(oPivotCache, oSheet.get_Range("E1"), "Summary");

Added:

The full tested code:

string fileTest = @"C:\Temp\ExcelTest\test.xlsx";

Microsoft.Office.Interop.Excel.Application oApp;
Microsoft.Office.Interop.Excel.Worksheet oSheet;
Microsoft.Office.Interop.Excel.Workbook oBook;

oApp = new Microsoft.Office.Interop.Excel.Application();
oApp.Visible = true;
oBook = oApp.Workbooks.Add(Type.Missing);
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oBook.Worksheets.get_Item(1);

oSheet.Cells[1, 1] = "Name";
oSheet.Cells[1, 2] = "Salary";

oSheet.Cells[2, 1] = "Frank";
oSheet.Cells[2, 2] = 150000;

oSheet.Cells[3, 1] = "Ann";
oSheet.Cells[3, 2] = 300000;

// now capture range of the first sheet = I will need this to create pivot table
Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A1", "B3");

// create second sheet
oSheet.Name = "Pivot Table";

// specify first cell for pivot table
Microsoft.Office.Interop.Excel.Range oRange2 = oSheet.get_Range("A1", "b3");

// create Pivot Cache and Pivot Table
Microsoft.Office.Interop.Excel.PivotCache oPivotCache = (Microsoft.Office.Interop.Excel.PivotCache)oBook.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, oRange);

// I have error on this line
Microsoft.Office.Interop.Excel.PivotTable oPivotTable = (Microsoft.Office.Interop.Excel.PivotTable)oSheet.PivotTables().Add(oPivotCache, oSheet.get_Range("E1", "E1"), "Summary");

// create Pivot Field, note that name will be the same as column name on sheet one
Microsoft.Office.Interop.Excel.PivotField oPivotField = (Microsoft.Office.Interop.Excel.PivotField)oPivotTable.PivotFields("Salary");
oPivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField;
oPivotField.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum;
oPivotField.Name = " Salary";

这篇关于使用C＃在Excel中创建数据透视表我创建数据透视表时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！