Working with Microsoft.Office.Interop.Excel.Workbook

Introduction

While working with Microsoft.Office.Interop.Excel.Application it usually happens that the code works while debugging but after publishing it stops working or throws exception or the page setup properties wont work...

Background

Microsoft.Office.Interop.Excel.Application depends upon the local machine properties on which it is published so weather it is Com exception or null reference exception it depends on the publishing machine (server) and not on the machine it is used.

Using the code

After Publishing:   

After publishing some basic permissions are required by the server to remove the exceptions...

//
// Using Microsoft.Office.Interop.Excel.Application
//
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(pathFile, System.Reflection.Missing.Value, 

System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                        System.Reflection.Missing.Value, System.Reflection.Missing.Value, 
                        System.Reflection.Missing.Value, System.Reflection.Missing.Value, 

System.Reflection.Missing.Value, System.Reflection.Missing.Value, 
                        System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                        System.Reflection.Missing.Value, System.Reflection.Missing.Value);
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Worksheet)xlWorkBook.Worksheets[1];
var _with1 = xlWorkSheet.PageSetup;
_with1.Zoom = false;
_with1.FitToPagesTall = 1;
_with1.FitToPagesWide = 1;
_with1.RightFooter = "Page &P of &N";
_with1.LeftFooter = APFToolSuite.Resources.en_US.Reports_en_US.footerReports;
xlWorkBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, 
   Type.Missing, Type.Missing, true, false, XlSaveAsAccessMode.xlNoChange, 
   XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
xlWorkBook.Close();
System.IO.File.Delete(pathFile);
xlApp.Quit();
return File(fileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", strFileName);
...

Workaround to resolve the issue:

1. System, Interactive user, Network, and Everyone are the four users required to have full permission by going to Run-> dcomcnfg->computer->My Computer->dcom config
2. Microsoft excel application right click properties->security tab->launch and activation permissions->customize->add network, administrator, interactive, system and everyone and give full access
3. Test if the excel is working properly. "Run..." type "excel.exe /safe" - Enter, and "excel.exe /automation" - Enter ... if both open the excel, the application should work
4. Create directory C:\Windows\SysWOW64\config\systemprofile\Desktop (for 64 bit Windows) or C:\Windows\System32\config\systemprofile\Desktop (for 32 bit Windows)
5. Set Full control permissions for directory Desktop (for example, in Win7 and IIS 7 and DefaultAppPool set permissions for user "IIS AppPool\DefaultAppPool")
6. Run->inetmgr->application pools->right click ->advance settings->identity ->local system
   

Points of Interest

Without these permissions exceptions like null reference or Com exceptions can come.

To define the file paths Server.MapPath can be used...