Excel Generation

Question

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