Microsoft.Office.Interop.Excel & Object reference not set to an instance of an object error after deployee on iis server ?

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.