In a recent project we had the need to create a pivot table in Excel from an existing spreadsheet.
Should be relatively easy… as always never as straight forward as it should be!
I’m not going to show the whole code for creating the pivot, but in essence to create a pivot table you need to
- Open the spreadsheet
- Select the data range
- Create a pivot cache
- Create the actual pivot table
- Add the Row/Column/Data fields
- Save the file.
Easy right, well yes so far. This is the basics of the code:
Application excelApp = new Application();
Workbook excelWorkBook = excelApp.Workbooks.Open(file);
Worksheet excelworksheet = excelWorkBook.ActiveSheet;
Worksheet sheet2 = excelWorkBook.Sheets.Add();
try
{
sheet2.Name = sheetname;
excelApp.ActiveWindow.DisplayGridlines = showGridLines;
Range oRange = excelworksheet.UsedRange;
PivotCache oPivotCache = excelWorkBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange);
PivotCaches pch = excelWorkBook.PivotCaches();
pch.Add(Excel.XlPivotTableSourceType.xlDatabase, oRange).CreatePivotTable(sheet2.Cells[1, 1], "PivotTable", Type.Missing, Type.Missing);// Create Pivot table
PivotTable pvt = sheet2.PivotTables("PivotTable");
pvt.ShowDrillIndicators = true;
pvt.InGridDropZones = false;
// add rows
// add columns
// add data fields
#region Misc
sheet2.UsedRange.Columns.AutoFit();
pvt.ColumnGrand = true;
pvt.RowGrand = true;
excelApp.DisplayAlerts = false;
if (removeSourceSheet)
excelworksheet.Delete();
sheet2.Activate();
sheet2.get_Range("B1", "B1").Select();
#endregion
excelWorkBook.SaveAs(file, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excelApp.DisplayAlerts = false;
excelWorkBook.Close(0);
excelApp.Quit();
return "OK";
}
catch (Exception ex)
{
excelWorkBook.Close(0);
excelApp.Quit();
return ex.ToString();
}
So that’s it really, running it works a treat… unfortunately this is where my problems start.
When running in unattended mode you get something like:
A call to ‘x’ failed with this message: Microsoft Excel cannot access the file ‘<filename>’. There are several possible reasons:
The file name or path does not exist.
The file is being use by another program.
The workbook you are trying to save has the same name as a currently open workbook
Now personally I think this is misleading.. as it turns out to be none of those reasons!
Turns out excel interop doesn’t like running in unattended mode, aka non interactive or whatever you would like to call it – but I need it too.
Step in some black voodoo magic 🙂
Turns out you need to make sure 2 folders exist for it to work, and there are:
C:\Windows\System32\config\systemprofile\Desktop
C:\Windows\SysWOW64\config\systemprofile\Desktop
Now, when it runs it works perfectly. It should be noted this isn’t support by Microsoft, so use at your own risk – and it could stop working at any point in time!
Roland Thompson 