Click here to Skip to main content
12,512,515 members (43,788 online)
Click here to Skip to main content
Add your own
alternative version

Stats

41.8K views
2 bookmarked
Posted

Working with Microsoft.Office.Interop.Excel.Workbook

, 17 Sep 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
While working with Microsoft.Office.Interop.Excel.Application after publish

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...

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Yachana_
Software Developer L&T IES
India India
No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionCom exceptions Pin
salehe24-Apr-15 23:58
membersalehe24-Apr-15 23:58 
AnswerRe: Com exceptions Pin
Yachana_25-Apr-15 7:49
professionalYachana_25-Apr-15 7:49 
GeneralRe: Com exceptions Pin
salehe25-Apr-15 20:07
membersalehe25-Apr-15 20:07 
GeneralRe: Com exceptions Pin
Yachana_27-Apr-15 20:19
professionalYachana_27-Apr-15 20:19 
GeneralRe: Com exceptions Pin
salehe18-May-15 2:57
membersalehe18-May-15 2:57 
GeneralCool Tricks Pin
samishra218-Apr-14 23:32
membersamishra218-Apr-14 23:32 
BugInterop on server side. Pin
chethan.sathyamurthy8-Apr-14 19:10
memberchethan.sathyamurthy8-Apr-14 19:10 
GeneralRe: Interop on server side. Pin
Yachana_8-Apr-14 22:30
professionalYachana_8-Apr-14 22:30 
GeneralRe: Interop on server side. Pin
Yachana_8-Apr-14 23:06
professionalYachana_8-Apr-14 23:06 
GeneralRe: Interop on server side. Pin
chethan.sathyamurthy9-Apr-14 20:52
memberchethan.sathyamurthy9-Apr-14 20:52 
GeneralRe: Interop on server side. Pin
Yachana_14-Apr-14 18:05
professionalYachana_14-Apr-14 18:05 
GeneralRe: Interop on server side. Pin
chethan.sathyamurthy15-Apr-14 20:43
memberchethan.sathyamurthy15-Apr-14 20:43 
GeneralRe: Interop on server side. Pin
Yachana_20-Apr-14 18:46
professionalYachana_20-Apr-14 18:46 
GeneralRe: Interop on server side. Pin
Mario Z13-Mar-15 5:24
professionalMario Z13-Mar-15 5:24 
GeneralThank You Pin
rafaelrend11-Mar-14 5:56
memberrafaelrend11-Mar-14 5:56 
QuestionInterop on server side. Pin
Defilippo8-Dec-13 21:08
memberDefilippo8-Dec-13 21:08 
AnswerRe: Interop on server side. Pin
Yachana_25-Dec-13 22:56
professionalYachana_25-Dec-13 22:56 
QuestionRelease COM objects Pin
Mike Meinz22-Sep-13 1:33
memberMike Meinz22-Sep-13 1:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160929.1 | Last Updated 17 Sep 2013
Article Copyright 2013 by Yachana_
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid