Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#3.0 IIS7 ASP.NET Windows C#4.0 , +
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 21:03pm
Comments
Saurabh_Damle at 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 at 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  

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

  Print Answers RSS
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,192
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 21 Feb 2013
Copyright © CodeProject, 1999-2014
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