I have to read an exist excel sheet values and generate text format report using Microsoft.Office.Interop.Excel namespace, when I deploy an ASP.NET web application on IIS 7.5 at windows server 8 r2 I can't access that event after IIS hosting but when I run local machine it'll working fine... kindly give me the solution on this problem? Following my aspx.cs code.
using System.Data;
using System.Reflection;
using System.Text;
using System.IO;
using Microsoft.Office.Interop.Excel;
public void GetTextValue()
{
string sUploadFilePath = string.Empty;
sUploadFilePath = Server.MapPath(".") + @"\Report\GAD_Model_Template_keivsan1_D2038_00.xls";
DataSet ds = new DataSet();
Microsoft.Office.Interop.Excel.Application oXL = null;
Microsoft.Office.Interop.Excel.Workbook oWB = null;
Microsoft.Office.Interop.Excel.Worksheet oSheet = null;
Microsoft.Office.Interop.Excel.Range oRng = null;
try
{
oXL = new Microsoft.Office.Interop.Excel.Application();
oWB = oXL.Workbooks.Open(sUploadFilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets["OUTPUT"];
System.Data.DataTable dt = new System.Data.DataTable("dtExcel");
ds.Tables.Add(dt);
DataRow dr;
StringBuilder sb = new StringBuilder();
int jValue = oSheet.UsedRange.Cells.Columns.Count;
int iValue = oSheet.UsedRange.Cells.Rows.Count;
for (int j = 1; j <= jValue; j++)
{
dt.Columns.Add("column" + j, System.Type.GetType("System.String"));
}
for (int i = 1; i <= iValue; i++)
{
dr = ds.Tables["dtExcel"].NewRow();
for (int j = 1; j <= jValue; j++)
{
oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];
string strValue = oRng.Text.ToString();
dr["column" + j] = strValue;
}
ds.Tables["dtExcel"].Rows.Add(dr);
}
oWB.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
oXL.Workbooks.Close();
oXL.Quit();
oXL = null;
oWB = null;
oSheet = null;
oRng = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
StringBuilder commaDelimitedText = new StringBuilder();
commaDelimitedText.AppendLine("GAD Inputs");
foreach (DataRow row in ds.Tables["dtExcel"].Rows)
{
string value = string.Format("{0}", row[0]);
commaDelimitedText.AppendLine(value);
}
File.WriteAllText("" + Server.MapPath(".") + @"\Report\" + "GADoutput" + ".txt" + "", commaDelimitedText.ToString());
string txtfilepath = Server.MapPath(".") + @"\Report\" + "GADoutput" + ".txt";
Session["filepath"] = txtfilepath;
ScriptManager.RegisterStartupScript(this, typeof(string), "SITE", "alert('Text File write successfully!');", true);
}
catch (Exception ex)
{ }
}
Thanks and regards,
Parthiban K.