Excel automation: Having trouble with PivotTableWizard from C#

P

pepe

Member
Joined
Mar 23, 2004
Messages
7
Location
Cairns
Im using C# and excel automation to try to create a pivot table report in excel.

I have code to extract data from an oracle data source and can populate an ado recordset.

Ive seen snippets of VBA code on the net to pivot a range of cells or pivot a recordset, but am having a hell of a time getting the correct syntax for c#.
If anybody can help it would be much appreciated.!! :D



Here is my WIP:


Excel.Application m_objExcel;
Excel.Workbooks m_objBooks;
Excel._Workbook m_objBook;
Excel.Worksheet m_objSheet;
Excel.Range m_objRange;

m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
m_objSheet = (Excel.Worksheet)m_objBook.ActiveSheet;

m_objExcel.Visible = true;


string strSQL;
strSQL = "select * from ...";


// Create a Recordset
ADODB.Connection objConn;
ADODB.Recordset objRS;
object objRecAff;

objConn = new ADODB.Connection();
objConn.Open("Provider=MSDAORA.1;User ID=...", "", "", 0);
objConn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;

objRS = (ADODB.Recordset)objConn.Execute(strSQL, out objRecAff, (int)ADODB.CommandTypeEnum.adCmdText);


// Get the Fields collection from the recordset and determine
// the number of fields (or columns).
System.Collections.IEnumerator objFields = objRS.Fields.GetEnumerator();
int nFields = objRS.Fields.Count;

// Create an array for the headers and add it to the
// worksheet starting at cell A1.
object[] objHeaders = new object[nFields];
ADODB.Field objField = null;
for(int n=0;n<nFields;n++)
{
objFields.MoveNext();
objField = (ADODB.Field)objFields.Current;
objHeaders[n] = objField.Name;
}
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objRange = m_objRange.get_Resize(1, nFields);
m_objRange.Value = objHeaders;
m_objRange.Font.Bold=true;


// Transfer the recordset to the worksheet starting at cell A2.
m_objRange = m_objSheet.get_Range("A2", m_objOpt);
m_objRange.CopyFromRecordset(objRS, m_objOpt, m_objOpt);


//Create pivot table from excel range
// Return used data range on Order Details Extended worksheet.
Excel.Range rngData;
rngData = m_objSheet.UsedRange;


//Here is where Im having problems getting the correct syntax. :o
// Create PivotTable report. Start at cell B5 so there is enough
// room for page fields.
Excel.Worksheet m_objDestSheet = (Excel.Worksheet)m_objBook.Worksheets["Sheet2"];
Excel.Range rngDest = m_objDestSheet.get_Range("B5", m_objOpt);
m_objSheet.PivotTableWizard("xlDatabase", rngData, rngDest, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);


Thankx guys!
Pepe
 
Back
Top