Excel Pivot Tables and Filtering

So I get a post this morning from my client saying everything looks great and they can't find any issues with the latest release. Happy moment, cigar time.

Well, at this point I am over on the project and am working on my own time. That's fine considering that there might be future business there. Ok, so they mention 2 minor items that are annoyances that would be perfect. No obligation to do them but I have a few minutes so I dive in.

5 hours later I have it working. What was the issue? Pivot Tables in Excel and setting their filter values. For those poor saps who have to deal with it in the future, here is some C# code to hopefully save you some time:


private static void HandlePivotTableChanges(string p)
{
FindProcessInstances();
Console.WriteLine("Starting Excel. Please be patient while it loads.");
string format = "'Product_Container_Release_Metrics_Workbook.xls'!Format_Workbook";
Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelApp.DisplayAlerts = false;
Console.WriteLine("Opening workbook.");
Workbook wb = ExcelApp.Workbooks.Open(p, m, m, m, m, m, m, m, m, m, m, m, m, m, m);
Console.WriteLine("Running macro.");
ExcelApp.RunMacro(format);
Worksheet ws = (Worksheet)wb.Worksheets["Pivot Tables"];
string pivotTable = "Forecast to Go";
PivotTable pt = (PivotTable)ws.PivotTables(pivotTable);
pt.ManualUpdate = true;
PivotField pf = (PivotField)pt.PivotFields("Release (A)");
PivotItems items = (PivotItems)pf.PivotItems(m);

foreach (PivotItem item in items)
{
if (item.Value != string.Empty) // <--- CRITICAL LINE. Filters must have at least 1 value selected. Exception otherwise.
((PivotItem) items.Item(item.Value)).Visible = false;
}

pt.ManualUpdate = false;
ExcelApp.Calculation = XlCalculation.xlCalculationAutomatic;
Console.WriteLine("Saving workbook.");
wb.Save();
wb.Close(false, m, m);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wb);
Console.WriteLine("Closing Excel.");
ExcelApp.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ExcelApp);
}

private static void FindProcessInstances()
{
Console.WriteLine("Finding open processes.");
KillProcesses("Excel");
KillProcesses("PowerPnt");
}

private static void KillProcesses(string processName)
{
Process[] processes = Process.GetProcessesByName(processName);
if (processes.Length > 0)
{
foreach (var p in processes)
{
p.Kill();
}
}
}

public static void RunMacro(this Application app, string macro)
{
app.Run(macro,
m, m, m, m, m, m, m, m, m, m,
m, m, m, m, m, m, m, m, m, m,
m, m, m, m, m, m, m, m, m, m
);
}

Comments

Alejandro said…
Hi Alan, my name is Alex, im working right now with SAS Metadata as part of one proyect and also SAS tables.

Until now i have implemented something like Linq To SAS using the SAS OMI, but its still in the beggining, maybe you could help me to develop a nice Linq To SAS provider this is my email

alexcuban@gmail.com

Best Regards Alex.

Popular posts from this blog

DNN Error

SAS Deployment Manager and Meadow Muffins

SAS XPT Files, IBM Floats, and C#