我正在编写一个在现有的Excel工作簿中添加数据透视表的应用程序。这是代码:
Console.WriteLine("File: " + e.FullPath + " " + e.ChangeType);
if (e.Name.StartsWith("~$"))
return;
Excel.Application oApp;
Excel.Worksheet oSheet;
Excel.Workbook oBook;
oApp = new Excel.Application();
try
{
oBook = oApp.Workbooks.Open(e.FullPath);
oSheet = (Excel.Worksheet)oBook.Worksheets.get_Item(1);
//Excel.Range finalCell = oSheet.Cells[2, oSheet.Rows.Count];
Excel.Range oRange = oSheet.get_Range("A1", "M9");
if (oApp.Application.Sheets.Count < 2)
{
oSheet = (Excel.Worksheet)oBook.Worksheets.Add();
}
else
{
oSheet = oApp.Worksheets[2];
}
oSheet.Name = "Pivot Table";
// specify first cell for pivot table
Excel.Range oRange2 = oSheet.Cells[3,1];
// create Pivot Cache and Pivot Table
Excel.PivotCache oPivotCache = (Excel.PivotCache)oBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange);
Excel.PivotTable oPivotTable = (Excel.PivotTable)oSheet.PivotTables().Add(PivotCache: oPivotCache, TableDestination: oRange2, TableName: "Summary");
Excel.PivotField oPivotField1 = (Excel.PivotField)oPivotTable.PivotFields("Field1");
oPivotField1.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
oPivotField1.Position = 1;
oPivotField1.Name = "Field1";
Excel.PivotField oPivotField2 = (Excel.PivotField)oPivotTable.PivotFields("Field2");
oPivotField2.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
oPivotField2.Position = 2;
oPivotField2.Name = "Field2";
oBook.Save();
Console.WriteLine("Operation Complete");
}
它位于FileSystemWatcher的事件处理程序中,用于文件创建。该程序运行并为数据透视表创建工作表,但未列出数据值,并且我得到两个单独的列。这是屏幕
请让我知道我在做什么错。使用Visual Studio 2015且项目类型为Windows窗体应用程序。
替换数据透视表缓存/表创建行:
Excel.PivotCache oPivotCache = (Excel.PivotCache)oBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange);
Excel.PivotTable oPivotTable = (Excel.PivotTable)oSheet.PivotTables().Add(PivotCache: oPivotCache, TableDestination: oRange2, TableName: "Summary");
和
PivotCache oPivotCache = oBook.PivotCaches().Create(XlPivotTableSourceType.xlDatabase, oRange, XlPivotTableVersionList.xlPivotTableVersion14);
PivotTable oPivotTable = oPivotCache.CreatePivotTable(TableDestination: oRange2, TableName: "Summary");
请注意,您正在做很多多余的转换。通常,您不必强制转换所有那些ivotCache和pivotfield变量。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句