Using .Net to create Excel Pivot table

  • Thread starter Thread starter Gary Dunne
  • Start date Start date
G

Gary Dunne

Hi All,
I'm struggling to find the correct syntax for creating a Pivot table in
an excel file via VB . Net 2005

The Excel help file is not particularly helpful where parameters are
concerned (It rarely mentiones the required data types etc)

The following is as far as I can get;

Dim excelApp As Excel.Application
Dim wkBook As Excel.Workbook, wkSheet As Excel.Worksheet

excelApp = New Excel.Application
excelApp.Visible = False

wkBook = excelApp.Workbooks.Add()
wkSheet = wkBook.Worksheets("Sheet1")

Dim connectionStr as String = ...
Dim tableName as String = "myDb.dbo.TableName"

wkBook.Connections.Add("MyConnection", "Description", connectionStr,
tableName, 3)
wkBook.Connections("MyConnection").Refresh()

Dim pvtCache As Excel.PivotCache =
wkBook.PivotCaches.Create(Excel.XlPivotTableSourceType.xlExternal,
wkBook.Connections("MyConnection"), _
Excel.XlPivotTableVersionList.xlPivotTableVersion11)

' The above part works fine

' but creating the Pivot Table throws up a ComException .. if I play with
the parameters I get a very unhelpful ArgumentException telling me "The
argument is incorrect" .. but which one ?

Dim pvtTable As Excel.PivotTable

' This version throws an ArgumentException
pvtTable = wkSheet.PivotTables.Add(PivotCache:=pvtCache,
TableDestination:=Excel.XlParameterType.xlRange, TableName:="PivotTable1")
' This version (and multiple others) throw a ComException
pvtTable = wkSheet.PivotTables.Add(PivotCache:=pvtCache,
TableDestination:=wkSheet.Range("A3"), TableName:="PivotTable1") ' Throws a
ComException

Can anyone help with the correct syntax please ?

Thanks

Gary
 
Hi,

Best way is to record a macro in Excel of you manually creating the pivot
table, then copy paste the VBA code to VB and modify as needed.
 
Thanks Rod,

I found some VB6 code online that I was able to alter to suit my needs.
(The record macro idea is also very helpful for fillig in the blanks)

Gary
 
thanks a lot for hint on how to rip the code from the macro - this was very helpful!!

I have created a quick and dirty pilot of pivot table creation funcitonlity in C#:

call the funciton: ExportToPivotTable("C:\\new.xlsx");

function itself:

public void ExportToPivotTable(String sFilePath)

{

Microsoft.Office.Interop.Excel.PivotTable PT;

// Creates a new Excel application.

Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();

Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

// Create a worksheet for the pivot table.

Microsoft.Office.Interop.Excel.Worksheet pivotSheet = (Worksheet)excelBook.Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);

pivotSheet.Name = "pTable";

//create excel connection

excelBook.Connections.Add("AnalysisServices", "eek", "OLEDB;Provider=MSOLAP.4, Integrated Security=SSPI; Persist Security Info = True; Data Source=SERVERNAME;Initial Catalog=DBNAME", "CUBENAME", 1);

// excelBook.Connections

// Create the Excel pivot table.

excelBook.PivotCaches().Create(XlPivotTableSourceType.xlExternal, excelBook.Connections["AnalysisServices"], XlPivotTableVersionList.xlPivotTableVersion12).CreatePivotTable(pivotSheet.get_Range("A9", Type.Missing), "PivotTable1", Type.Missing, Type.Missing);

PT = (PivotTable)pivotSheet.PivotTables("PivotTable1");

// select dimensions and measures

PT.AddDataField(PT.CubeFields["[Measures].[Measure1]"], Type.Missing, Type.Missing);

PT.CubeFields["[1 Common Dimensions and Security].[Facilities]"].Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField;

PT.CubeFields["[DimensionA].[LevelB]"].Position = 1;

// Save the newly created Excel file.

excelBook.SaveAs(sFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing);

excelBook.Close(Type.Missing, sFilePath, Type.Missing);

//excelApp.Visible = true;

Response.Clear();

Response.Buffer = true;

Response.ContentType = "application/vnd.ms-excel";

Response.AppendHeader("content-disposition", "attachment;filename=new.xlsx");



Response.Charset = "";

this.EnableViewState = false;

Response.TransmitFile(sFilePath);



Response.End();



}

 
Back
Top