Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 - Subtotals Below the Data in Pivot Tables | Excel Discussion (Misc queries) | |||
Building pivot tables in Excel 2007 based on existing pivot tables? | Excel Discussion (Misc queries) | |||
Grouping data with Pivot Tables Excel 2003 | Excel Discussion (Misc queries) | |||
Pivot table retrieving data from several worksheets | Excel Worksheet Functions | |||
Retrieving Source Data From Pivot Table | Excel Programming |