无法以编程方式填充数据透视表

问题描述

大家好，我正在尝试以编程方式制作数据透视表，但出现此错误:

Hello guys I am trying to make a pivot table programmtically but i getting this error :

"Exception from HRESULT: 0x800A03EC"

上线:

on line :

pivotCache.Connection = connection;

我的代码如下:

My code is as follows :

Microsoft.Office.Interop.Excel.Application objApp;
       Microsoft.Office.Interop.Excel.Workbook objBook;
       Microsoft.Office.Interop.Excel.Sheets objSheets;
       Microsoft.Office.Interop.Excel.Workbooks objBooks;

       string connection = @"OLEDB;Provider=SQLOLEDB.1;Data Source=.;Initial Catalog=Employee;persist security info=False;user id=sa;pwd=server";
       string command = "SELECT * FROM employee";


       objApp = new Microsoft.Office.Interop.Excel.Application();

       objBooks = objApp.Workbooks;
       objBook = objApp.Workbooks.Add(Missing.Value);
       objSheets = objBook.Worksheets;

       Microsoft.Office.Interop.Excel.Worksheet sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)objSheets[1];
       sheet1.Name = "ACCOUNTS";
       Microsoft.Office.Interop.Excel.PivotCache pivotCache = objBook.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, sheet1.UsedRange);


       //Microsoft.Office.Interop.Excel.PivotCache pivotCache = this.Application.ActiveWorkbook.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlExternal, Missing.Value);

       pivotCache.Connection = connection;
       pivotCache.MaintainConnection = true;
       pivotCache.CommandText = command;
       pivotCache.CommandType = Microsoft.Office.Interop.Excel.XlCmdType.xlCmdSql;
       Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)objApp.ActiveSheet;
       Microsoft.Office.Interop.Excel.PivotTables pivotTables = (Microsoft.Office.Interop.Excel.PivotTables)sheet.PivotTables(Missing.Value);
       Microsoft.Office.Interop.Excel.PivotTable pivotTable = pivotTables.Add(pivotCache, objApp.ActiveCell, "PivotTable1", Missing.Value, Missing.Value);
       pivotTable.SmallGrid = false;
       pivotTable.ShowTableStyleRowStripes = true;
       pivotTable.TableStyle2 = "PivotStyleLight1";
       Microsoft.Office.Interop.Excel.PivotField pageField = (Microsoft.Office.Interop.Excel.PivotField)pivotTable.PivotFields("SalesTerritory");
       pageField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlPageField;
       Microsoft.Office.Interop.Excel.PivotField rowField = (Microsoft.Office.Interop.Excel.PivotField)pivotTable.PivotFields("FullName");
       rowField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField;
       pivotTable.AddDataField(pivotTable.PivotFields("2004"), "Sum of 2004", Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum);

推荐答案

Microsoft.Office.Interop.Excel.Application objApp;
       Microsoft.Office.Interop.Excel.Workbook objBook;
       Microsoft.Office.Interop.Excel.Sheets objSheets;
       Microsoft.Office.Interop.Excel.Workbooks objBooks;

       objApp = new Microsoft.Office.Interop.Excel.Application();
       objBooks = objApp.Workbooks;
       objBook = objApp.Workbooks.Add(Missing.Value);
       objSheets = objBook.Worksheets;

       Microsoft.Office.Interop.Excel.Worksheet sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)objSheets[1];
       sheet1.Activate();

       string connection = @"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=.;Initial Catalog=Employee";
       string command = "SELECT * FROM employee";
       Microsoft.Office.Interop.Excel.PivotCache pivotCache = objBook.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlExternal, Missing.Value);
       pivotCache.Connection = connection;
       pivotCache.MaintainConnection = true;
       pivotCache.CommandText = command;
       pivotCache.CommandType = Microsoft.Office.Interop.Excel.XlCmdType.xlCmdSql;
       Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)objApp.ActiveSheet;
       Microsoft.Office.Interop.Excel.PivotTables pivotTables = (Microsoft.Office.Interop.Excel.PivotTables)sheet.PivotTables(Missing.Value);
       Microsoft.Office.Interop.Excel.PivotTable pivotTable = pivotTables.Add(pivotCache, objApp.ActiveCell, "PivotTable1",Missing.Value, Missing.Value);
       pivotTable.SmallGrid = false;
       pivotTable.ShowTableStyleRowStripes = true;
       pivotTable.TableStyle2 = "PivotStyleLight1";
       Microsoft.Office.Interop.Excel.PivotField pageField =  (Microsoft.Office.Interop.Excel.PivotField)pivotTable.PivotFields("UserName");
       pageField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlPageField;
       Microsoft.Office.Interop.Excel.PivotField rowField =  (Microsoft.Office.Interop.Excel.PivotField)pivotTable.PivotFields("FirstName");
       rowField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField;
       pivotTable.AddDataField(pivotTable.PivotFields("LastName"), "ID", Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum);


       ExcelSaveAs(objApp, objBook, "c:NewPivote.xls");

   }

   static string ExcelSaveAs(Microsoft.Office.Interop.Excel.Application objApp, Microsoft.Office.Interop.Excel.Workbook objBook, string path)
   {
       try
       {
           objApp.DisplayAlerts = false;
           objBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet, Missing.Value, Missing.Value, false, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
           objApp.DisplayAlerts = true;
           return null;
       }
       catch (Exception e)
       {
           StackTrace st = new StackTrace(new StackFrame(true));
           StackFrame sf = st.GetFrame(0);
           return (e.Message + "\n" + "Method" + sf.GetMethod().ToString() + "\n" + "Line" + sf.GetFileLineNumber().ToString());
       }

这篇关于无法以编程方式填充数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！