c# – Roland Thompson

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!

/// <summary> /// Ctor +1 /// </summary> /// <param name="jsonText"></param> public FulfillmentNotice (string jsonText) { dynamic t = JsonConvert.DeserializeObject<dynamic>(jsonText); id = t.id; order_number = t.order_number; billing_address = t.billing_address.ToObject<Address>(); shipping_address = t.shipping_address.ToObject<Address>(); payment_gateway_names = new List<string>(); foreach (string s in t.payment_gateway_names) payment_gateway_names.Add(s); // init list shipping_lines = new List<Shipping_Lines>(); foreach (dynamic shipline in t.shipping_lines) shipping_lines.Add(shipline.ToObject<Shipping_Lines>()); // init list fulfillments = new List<Fulfillment>(); foreach (dynamic fulLine in t.fulfillments) fulfillments.Add(fulLine.ToObject<Fulfillment>()); // init list discount_codes = new List<Discount_Codes>(); foreach (dynamic disLine in t.discount_codes) discount_codes.Add(disLine.ToObject<Discount_Codes>()); }

Tag: c#

c# excel interop – cannot access the file

Dynamics N.A.V and Homogenous AppDomain Error

Background

The Solution