Click here to Skip to main content
12,293,588 members (60,273 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C#3.0 IIS7 C# ASP.NET Windows IIS , +
Hi,
My application is migrated on new server. Its dot net application with framework 3.5 and
using Microsoft.Office.Interop.Excel v14.00 for generated excel. its previously worked fine.
Window Server 2008 R2 Enterprise installed office excel 2007.


My code is running locally well. after deployee on iis server it throw following error :-

Object reference not set to an instance of an object.


my code is given below-:

void DownloadExcel(string downloadtype, string cycledetailuid, string useruid)
{
Microsoft.Office.Interop.Excel.Application oXL = null;
Workbook oWB = null;
Workbooks oWBS = null;
Worksheet oTemplateSheet = null;
Sheets oSheets = null;
QueryTables oTables = null;
QueryTable oTable = null;
Range oRng = null;


string DirectoryPath = Server.MapPath("~/OffinvoiceDownloadExcel/" + Session["LoginUserUId"].ToString());

if (!Directory.Exists(DirectoryPath))
{
Directory.CreateDirectory(DirectoryPath);
}

if (Directory.Exists(DirectoryPath))
{
string[] Files = System.IO.Directory.GetFiles(DirectoryPath, "*.xls");

for (int i = 0; i < Files.Count(); i++)
{
File.Delete(Files[i].ToString());
}

// Start a new workbook in Excel.
string filename = "";

try
{
// Start Excel and get the Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = false;
oXL.ScreenUpdating = false;

// get the workbooks collection and add a new Workbook to it.
oWBS = oXL.Workbooks;
oWB = oWBS.Add();

// Create a QueryTable that starts at cell A1.
oSheets = oWB.Sheets;
// by default when you create a new WB you get 3 sheets, get the first one
oTemplateSheet = (Worksheet)oSheets[1];
oRng = oTemplateSheet.get_Range("A1");
oTemplateSheet.Name = downloadtype;

// get the QueryTables collection
oTables = oTemplateSheet.QueryTables;

string SQLStr = "";

if (downloadtype == "Tot")
{
SQLStr = "exec [dbo].[abrlsmdm_proc_downloadexceldata] " + "'" + "CreateTotExcel" + "'," + "'" + cycledetailuid + "'," + "'" + useruid + "'";
filename = "Tot" + cycledetailuid + ".xls";
}
else if (downloadtype == "Offinvoice")
{
SQLStr = "exec [dbo].[abrlsmdm_proc_downloadexceldata] " + "'" + "OFFINVOICE-EXCEL" + "'," + "'" + cycledetailuid + "'," + "'" + useruid + "'";
filename = "Offinvoice" + cycledetailuid + ".xls";
}

object aStrSQL = SQLStr;

object connection = "OLEDB;Provider=SQLOLEDB.1;Initial Catalog=SMDM_REPORTAPPS_BNM;Data Source=10.200.202.875;User Id=admin_admin; Password=en#123";

// create a query table with the connection and SQL command
oTable = oTables.Add(connection, oRng, SQLStr);
oTable.RefreshStyle = XlCellInsertionMode.xlInsertEntireRows;
oTable.Refresh(false);

//Remove the Connection I made because I don't want users refreshing the data (optional)
oWB.Connections[1].Delete();
//Make sure Excel is visible and give the user control of Microsoft Excel's lifetime.
oXL.Visible = false;
oXL.ScreenUpdating = true;
oXL.UserControl = true;

oWB.SaveAs(DirectoryPath + "/" + filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

oWB.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Type.Missing, Type.Missing);

oXL.Quit();

string filePath = DirectoryPath + "/" + filename;

FileInfo fileinfo = new FileInfo(filePath);

// Clear the content of the response
Response.ClearContent();

// Add the file name and attachment, which will force the open/cancel/save dialog box to show, to the header
Response.AddHeader("content-disposition", "attachment;filename=" + filename);

// Set the ContentType
Response.ContentType = "application/vnd.ms-excel";

// Write the file into the response (TransmitFile is for ASP.NET 2.0. In ASP.NET 1.1 you have to use WriteFile instead)
Response.TransmitFile(filePath);

// End the response
Response.End();

}

catch (Exception exception)
{
oXL.Quit();
}
}

}

this code is work fine.

my system64 >> config >> sytstemprofile >> Desktop folder have.

Please help. what is issue ?



thanks.
Posted 20-Feb-13 20:03pm
Comments
Saurabh_Damle 21-Feb-13 2:44am
   
Check for the path of Microsoft.Office.Interop.Excel in references and make sure that path contains dll.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

It works fine locally because you installed Excel locally. It fails on the server because the server cannot access the Excel installation on your development computer. You have to install Excel on the server, too.
  Permalink  
Comments
Saurabh_Damle 21-Feb-13 3:10am
   
If possible I would suggest adding dll of Microsoft.Office.Interop.Excel to application and refer it from there.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

I solved by giving iis user permission to application.
  Permalink  
Comments
Member 12302481 2-Feb-16 7:26am
   
Our task is , we need to run the excel macro through programatically.

In local environment it was working fine, when we follow the below Steps:-

1) Microsoft.Office.Interop.Excel.Application appExcel = new Microsoft.Office.Interop.Excel.Application();

2) Microsoft.Office.Interop.Excel.Workbook workBook = appExcel.Workbooks.Open("FilePath",
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);

3) appExcel.Run("Macro name", "$W$5", 1, 0, "$Q$11:$Q$112", Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Note:-

On localhost everything is working fine and the macro is getting executed.

But the functionality is not working as expected when hosted in IIS(version 7.5).

For this to work the changes which we have done are:-

a) Control Panel -> Administrative Tools -> Component Services
Computers -> My Computer -> DCOM Config -> Microsoft Excel Application -> Properties

i) Here in the security tab we have Customized all the permissions by adding user(IUSR) and giving him all
access permissions.

After doing these changes we are able to create instance of Interop.Excel.
But we could not open the workbook.

ii) In Identity tab of Microsoft Excel Application peroperties, by default "The launching user" is selected.

After changing the value to "The interactive user" we are able to open the workbook.
But we could not run the macro.

iii) C:\Windows\System32\config\systemprofile

In this location we have created Desktop folder. For this folder properties, in the security tab we have created user(IUSR) and given full control.
Now we are able to run the macro and getting the desired output even when running the application on IIS hosted site.

Issue:-

After making all the changes when we have moved the changes to production server, there we could not find Microsoft Excel Application in DCOM Config.
Microsoft Office is not completely installed in production server. Only Excel software was installed on the server.

We are getting null reference exception at below line of code.

Microsoft.Office.Interop.Excel.Workbook workBook = appExcel.Workbooks.Open("FilePath",
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);

Our production server is running on windows 2008 R2.

Could any one please suggest the right way to resolve this issue.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160525.2 | Last Updated 21 Feb 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100