Retrieving Data from Excel Pivot tables into Datatable
 

Hi,

Has any body so far retrieved Excel pivot data in to ADO.net?

If so please let me know your approach. For some reason i get more
exceptions to my pivottable/cache objects than values.

I am using the below namespaces. Pl. let me kowif i missing any?
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Office = Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using Microsoft.VisualStudio.Tools.Applications.Runtime;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.Common;


And here is all the code: i have a ui from which i get the location of the
excel spreadsheet.

namespace _
{
public partial class Form1 : Form
{
public Excel.Application xlApp;
public Excel._Workbook xlWkBk;
public Excel.Workbooks xlBooks;
public Excel.Sheets xlSheets;
public Excel.Worksheet xlWkSheet;
public Excel.PivotTable xlPvt;
public Excel.PivotCache xlCche;
public Excel.PivotFields xlFlds;
public Excel.PivotItems xlPvtItms;
//public OleDbConnection cnExcel;
//public OleDbDataAdapter daExcel;
public DataTable dt;
public DataAdapter da;
public DataSet ds;
int i,j;

public Form1()
{
InitializeComponent();
xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("ERROR: EXCEL couldn't be started!");
System.Windows.Forms.Application.Exit();
}
xlApp.Visible = false;

}

private void button1_Click(object sender, EventArgs e)
{
//xlBooks = new Excel.Workbooks();
xlWkBk = xlApp.Workbooks.Open(textBox1.Text, 0, true, 5,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, true,
Excel.XlPlatform.xlWindows, System.Reflection.Missing.Value, false, false,
System.Reflection.Missing.Value, false, true,
Excel.XlCorruptLoad.xlNormalLoad);
if (xlWkBk == null)
{
MessageBox.Show("ERROR: EXCEL file couldn't be started!");
System.Windows.Forms.Application.Exit();
}
xlWkSheet = (Excel.Worksheet) xlWkBk.ActiveSheet;
xlPvt = (Excel.PivotTable)xlWkSheet.PivotTables(1);
xlCche = (Excel.PivotCache)xlPvt.PivotCache();
MessageBox.Show(xlCche.BackgroundQuery.ToString()) ;
label1.Text = xlCche.Connection.ToString();
textBox2.Text = xlPvt.Name.ToString();
}

private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
xlWkBk.Close(false, System.Reflection.Missing.Value, false);
xlApp.Workbooks.Close();
xlApp.Quit();
}
}
}

Thank you.