No one can help in this ???
Anyways I am able to populate the pivot table now, i am working on generating pivot chart programmatically now.
I saw many peoples facing the same problem so I am posting my code :
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());
}