I am generating a Excel in my application and it is working fine but some times I am getting this message when I am trying to open the Excel. Below is the code and the error which I am getting.
CODE:using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using System.Data.OleDb;
using System.Data.OracleClient;
using System.Text;
using System.Xml;
using System.Xml.Xsl;
using System.Drawing;
using System.Threading;
using Microsoft.Office.Interop.Excel;
using System.Collections.Generic;
using System.Reflection;
namespace NIA
{
public partial class WebForm3 : System.Web.UI.Page
{
#region Page Load
protected void Page_Load(object sender, EventArgs e)
{
((CommonHeaderMasterPage)Page.Master).onSessionExpired();
Page.Title = "NIA :: Claim Status Report";
ClientScript.RegisterHiddenField("hdnReportType", ddlReportType.ClientID);
ClientScript.RegisterHiddenField("hdnStatus", ddlStatus.ClientID);
btnSubmit.Attributes.Add("OnClick", "Javascript:return Validate()");
if (!IsPostBack)
{
}
lblMessage.Text = "";
}
#endregion
#region Button Cancel click
protected void btnCancel_Click(object sender, EventArgs e)
{
Response.Redirect("ClaimStatusReport.aspx");
}
#endregion
#region Back Button Click
protected void btnBack_Click(object sender, EventArgs e)
{
Response.Redirect("Home.aspx");
}
#endregion
#region Connection Error
private void ConnErrorMsg()
{
lblMessage.Text = "Connection Error";
lblMessage.Visible = true;
}
#endregion
#region Button Submit Click
protected void btnSubmit_Click(object sender, EventArgs e)
{
try
{
string strOrcConnString = ConfigurationSettings.AppSettings["ConnStr"];
string reporttype = ddlReportType.SelectedItem.Text.ToString();
string status = ddlStatus.SelectedItem.Text.ToString();
OracleConnection vOrcConn;
vOrcConn = new OracleConnection(strOrcConnString);
try
{
vOrcConn.Open();
}
catch (Exception ex)
{
string Error = ex.Message;
File.WriteAllText(@"E:\\NIA\\NIA\\Logs\\logs.txt", Error);
ConnErrorMsg();
return;
}
OracleCommand orcCmd = new OracleCommand("APP.nia_claims_application_1.NIA_ALL_STATUS_REPORT", vOrcConn);
orcCmd.CommandType = CommandType.StoredProcedure;
orcCmd.Parameters.Add(new OracleParameter("V_STATUS", OracleType.VarChar, 50));
orcCmd.Parameters["V_STATUS"].Value = status;
OracleParameter outparam1 = new OracleParameter("NOT_OK", OracleType.Cursor, 50000);
outparam1.Direction = ParameterDirection.Output;
OracleParameter outparam2 = new OracleParameter("WAITING", OracleType.Cursor, 50000);
outparam2.Direction = ParameterDirection.Output;
OracleParameter outparam3 = new OracleParameter("NCR_UPLOADED", OracleType.Cursor, 50000);
outparam3.Direction = ParameterDirection.Output;
OracleParameter outparam4 = new OracleParameter("NCR_NOT_OK", OracleType.Cursor, 50000);
outparam4.Direction = ParameterDirection.Output;
OracleParameter outparam5 = new OracleParameter("NCR_WAITING", OracleType.Cursor, 50000);
outparam5.Direction = ParameterDirection.Output;
OracleParameter outparam6 = new OracleParameter("INTIMATED_NOT_OK", OracleType.Cursor, 50000);
outparam6.Direction = ParameterDirection.Output;
OracleParameter outparam7 = new OracleParameter("INTIMATED_WAITING", OracleType.Cursor, 50000);
outparam7.Direction = ParameterDirection.Output;
OracleParameter outparam8 = new OracleParameter("INTIMATED_UPLOADED", OracleType.Cursor, 50000);
outparam8.Direction = ParameterDirection.Output;
orcCmd.Parameters.Add(outparam1);
orcCmd.Parameters.Add(outparam2);
orcCmd.Parameters.Add(outparam3);
orcCmd.Parameters.Add(outparam4);
orcCmd.Parameters.Add(outparam5);
orcCmd.Parameters.Add(outparam6);
orcCmd.Parameters.Add(outparam7);
orcCmd.Parameters.Add(outparam8);
orcCmd.ExecuteNonQuery();
DataSet ds = new DataSet();
OracleDataAdapter da = new OracleDataAdapter(orcCmd);
da.Fill(ds);
orcCmd.Dispose();
//OracleCommand orcCmd1 = new OracleCommand("APP.nia_claims_application_1.NIA_APLN_CLAIM_STATUS_REPORT", vOrcConn);
//orcCmd1.CommandType = CommandType.StoredProcedure;
//orcCmd1.Parameters.Add(new OracleParameter("V_STATUS", OracleType.VarChar, 50));
//orcCmd1.Parameters["V_STATUS"].Value = "INTIMATED_WAITING";
//OracleParameter outparam2 = new OracleParameter("RESULT_SET", OracleType.Cursor, 500);
//outparam2.Direction = ParameterDirection.Output;
//orcCmd1.Parameters.Add(outparam2);
//orcCmd1.ExecuteNonQuery();
//DataSet ds2 = new DataSet();
//OracleDataAdapter da2 = new OracleDataAdapter(orcCmd1);
//da2.Fill(ds2);
//OracleCommand orcCmd2 = new OracleCommand("APP.nia_claims_application_1.NIA_APLN_CLAIM_STATUS_REPORT", vOrcConn);
//orcCmd2.CommandType = CommandType.StoredProcedure;
//orcCmd2.Parameters.Add(new OracleParameter("V_STATUS", OracleType.VarChar, 50));
//orcCmd2.Parameters["V_STATUS"].Value = "NOT_OK";
//OracleParameter outparam3 = new OracleParameter("RESULT_SET", OracleType.Cursor, 500);
//outparam3.Direction = ParameterDirection.Output;
//orcCmd2.Parameters.Add(outparam3);
//orcCmd2.ExecuteNonQuery();
//DataSet ds3 = new DataSet();
//OracleDataAdapter da3 = new OracleDataAdapter(orcCmd2);
//da3.Fill(ds3);
if (ds.Tables[0].Rows.Count > 0)
{
if (reporttype == "CSV")
{
foreach (System.Data.DataTable dt in ds.Tables)
{
StringBuilder sb = new StringBuilder();
foreach (DataColumn col in dt.Columns)
{
sb.Append(col.ColumnName + ',');
}
sb.Remove(sb.Length - 1, 1);
sb.Append(Environment.NewLine);
foreach (DataRow row in dt.Rows)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
sb.Append(row[i].ToString() + ",");
}
sb.Append(Environment.NewLine);
}
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = "application/CSV";
Response.AddHeader("Content-Disposition", "attachment;filename=testNIA.csv");
Response.Write(sb.ToString());
Response.End();
}
}
else
{
//System.IO.StringWriter stringWrite = new System.IO.StringWriter();
//System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
//DataGrid dg = new DataGrid();
//dg.DataSource = ds1;
//dg.DataBind();
//dg.HeaderStyle.Font.Bold = true;
//dg.HeaderStyle.BackColor = Color.LightGreen;
//dg.HorizontalAlign = HorizontalAlign.Right;
//dg.RenderControl(htmlWrite);
////response.object is cleaned
//Response.Clear();
//Response.Charset = "";
//Response.Write(stringWrite.ToString());
//stringWrite.Dispose();
//htmlWrite.Dispose();
//DataGrid dg1 = new DataGrid();
//dg1.DataSource = ds2;
//dg1.DataBind();
//dg1.HeaderStyle.Font.Bold = true;
//dg1.HeaderStyle.BackColor = Color.LightGreen;
//dg1.HorizontalAlign = HorizontalAlign.Right;
//dg1.RenderControl(htmlWrite);
////response.object is cleaned
//Response.Clear();
//Response.Charset = "";
//Response.Write(stringWrite.ToString());
//stringWrite.Dispose();
//htmlWrite.Dispose();
//DataGrid dg2 = new DataGrid();
//dg2.DataSource = ds3;
//dg2.DataBind();
//dg2.HeaderStyle.Font.Bold = true;
//dg2.HeaderStyle.BackColor = Color.LightGreen;
//dg2.HorizontalAlign = HorizontalAlign.Right;
//dg2.RenderControl(htmlWrite);
////response.object is cleaned
//Response.Clear();
//Response.Charset = "";
//Response.Write(stringWrite.ToString());
//stringWrite.Dispose();
//htmlWrite.Dispose();
// Workbook workBook = _excelApp.Workbooks.Open(thisFileName,
//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);
//Application app;
//Workbook wb=app.Workbooks.Open("Waiting",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);
string st1 = "NOT_OK";
string st2 = "WAITING";
string st3 = "NCR_UPLOADED";
string st4 = "NCR_NOT_OK";
string st5 = "NCR_WAITING";
string st6 = "INTIMATED_NOT_OK";
string st7 = "INTIMATED_WAITING";
string st8 = "INTIMATED_UPLOADED";
List<string> name = new List<string>();
name.Add(st1);
name.Add(st2);
name.Add(st3);
name.Add(st4);
name.Add(st5);
name.Add(st6);
name.Add(st7);
name.Add(st8);
DataSetsToExcel(ds, name);
//response mime type for excel is set
//Response.ContentType = "application/vnd.xls";
//Response.AddHeader("content-disposition", "attachment;filename=ClaimStatusReport.xls");
//HttpContext.Current.ApplicationInstance.CompleteRequest();
Response.End();
}
}
else
{
lblMessage.Text = "No Data";
lblMessage.Visible = true;
return;
}
// Page.ClientScript.RegisterStartupScript(this.GetType(), @"CloseProgressbar", @"HideProgress();", true);
}
catch (Exception ex)
{
string Error = ex.Message;
if (Error != "Thread was being aborted.")
{
File.WriteAllText(@"E:\\NIA\\NIA\\Logs\\logs.txt", Error);
lblMessage.Text = "Error occured during data retrieval";
lblMessage.Visible = true;
}
return;
}
}
#endregion
public void DataSetsToExcel(DataSet dataSets, List<string> fileName)
{
Application app = new Application();
Microsoft.Office.Interop.Excel.Application xlApp =
new Microsoft.Office.Interop.Excel.Application();
Workbook xlWorkbook = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets xlSheets = null;
Worksheet xlWorksheet = null;
int i = 0;
int clr1 = System.Drawing.Color.Brown.ToArgb();
int clr2 = System.Drawing.Color.BurlyWood.ToArgb();
int clr3 = System.Drawing.Color.CornflowerBlue.ToArgb();
int clr4 = System.Drawing.Color.Cyan.ToArgb();
int clr5 = System.Drawing.Color.DarkOliveGreen.ToArgb();
int clr6 = System.Drawing.Color.DarkRed.ToArgb();
int clr7 = System.Drawing.Color.DarkTurquoise.ToArgb();
int clr8 = System.Drawing.Color.GreenYellow.ToArgb();
List<int> colors = new List<int>();
colors.Add(clr1);
colors.Add(clr2);
colors.Add(clr3);
colors.Add(clr4);
colors.Add(clr5);
colors.Add(clr6);
colors.Add(clr7);
colors.Add(clr8);
foreach (System.Data.DataTable dt in dataSets.Tables)
{
int rowNo = dt.Rows.Count;
int columnNo = dt.Columns.Count;
int colIndex = 0;
//Create Excel Sheets
xlSheets = xlWorkbook.Sheets;
xlWorksheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
xlWorksheet.Name = fileName[i];
xlWorksheet.Tab.Color = colors[i];
i++;
//Generate Field Names
foreach (DataColumn dataColumn in dt.Columns)
{
colIndex++;
xlApp.Cells[1, colIndex] = dataColumn.ColumnName;
}
object[,] objData = new object[rowNo, columnNo];
//Convert DataSet to Cell Data
for (int row = 0; row < rowNo; row++)
{
for (int col = 0; col < columnNo; col++)
{
objData[row, col] = dt.Rows[row][col];
}
}
Range range = xlWorksheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[rowNo + 1, columnNo]);
range.Value2 = objData;
//Format Data Type of Columns
colIndex = 0;
foreach (DataColumn dataColumn in dt.Columns)
{
colIndex++;
string format = "'";
switch (dataColumn.DataType.Name)
{
case "Boolean":
break;
case "Byte":
break;
case "Char":
break;
case "DateTime":
format = "dd/mm/yyyy";
break;
case "Decimal":
format = "* #,##0.00;[Red]-$* #,##0.00";
break;
case "Double":
break;
case "Int16":
format = "0";
break;
case "Int32":
format = "0";
break;
case "Int64":
format = "0";
break;
case "SByte":
break;
case "Single":
break;
case "TimeSpan":
break;
case "UInt16":
break;
case "UInt32":
break;
case "UInt64":
break;
default: //String
break;
}
xlWorksheet.get_Range(xlApp.Cells[2, colIndex],
xlApp.Cells[rowNo + 1, colIndex]).NumberFormat = format;
xlWorksheet.get_Range(xlApp.Cells[2, colIndex],
xlApp.Cells[rowNo + 1, colIndex]).ColumnWidth = 30;
}
}
string name = Guid.NewGuid().ToString();
string FileName = name + ".xls";
string FilePath = "d:\\";
xlWorkbook.SaveAs(FilePath+FileName, System.Reflection.Missing.Value,System.Reflection.Missing.Value,
System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,
XlSaveAsAccessMode.xlExclusive,System.Reflection.Missing.Value,System.Reflection.Missing.Value,
System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value);
xlApp.Quit();
GC.Collect();
System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
response.ClearContent();
response.Clear();
response.ContentType = "application/vnd.xls";
response.AddHeader("Content-Disposition", "attachment; filename=" + FileName +";");
response.TransmitFile(FilePath + FileName);
HttpContext.Current.ApplicationInstance.CompleteRequest();
response.End();
response.Flush();
}
}
}
Error I am getting:Missing file: C:\Users\rakesh.rcs\Desktop\css\IMSStyleSheet.css is the Error
