title: Saving the results of an SQL query to an Excel file on a button click
body: <p>I have an Asp.Net MVC web application in which a web page has link <code>EXPORT</code> runs a C# function which has an SQL Query and exports the data to an excel file and saves on the local machine. Once the action of saving is completed it should return to page called admin and for some reasons it is not doing so and returning to an empty page</p>
  
  <p>I have the following code in the controller</p>
  
  <pre><code>[Authorize(Roles = "Admin")]
          public ActionResult ExportWBData()
          {
  
                  sqlcon.Open();
                  string strGetAllWBData = @"select Product, Term, BidVolume, BidCP as BidCounterParty, Bid, Offer, OfferCP as OfferCounterParty, OfferVolume from CanadianCrudes";
  
                  SqlCommand cmdGetAllWBData = new SqlCommand(strGetAllWBData, sqlcon);
                  DataTable dtGetAllWBData = new DataTable();
                  SqlDataAdapter daGetAllWBData = new SqlDataAdapter();
  
                  daGetAllWBData.SelectCommand = cmdGetAllWBData;
                  daGetAllWBData.Fill(dtGetAllWBData);
                  sqlcon.Close();
  
                  Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
                  Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
                  Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
                  worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets["Sheet1"];
                  worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;
  
                  for (int i = 0; i &lt; dtGetAllWBData.Rows.Count; i++)
                  {
                      for (int j = 0; j &lt; dtGetAllWBData.Columns.Count; j++)
                      {
                          worksheet.Cells[1, j + 1] = dtGetAllWBData.Columns[j].ToString();
                          worksheet.Cells[i + 2, j + 1] = dtGetAllWBData.Rows[i][j].ToString();
                      }
                  }
  
                  string fromFormat = "dd/MM/yyyy";
                  string toFormat = "MM-dd-yyyy";
  
                  DateTime newDate = DateTime.ParseExact(DateTime.Today.ToString(fromFormat), fromFormat, null);
  
  
                  string filedate = newDate.ToString(toFormat);
  
  
                  workbook.SaveAs(@"Z:\WBDomesticDumps\WBData " + filedate + ".xls", 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);
                  workbook.Close(true, Type.Missing, Type.Missing);
                  app.Quit();
  
                  return Admin();  
          }
  </code></pre>
  
  <p>Layout Page is</p>
  
  <pre><code>&lt;li&gt;@Html.ActionLink("Export", "ExportWBData", "Home")&lt;/li&gt;
  </code></pre>
  
  <p>May I know a better way to do so?</p>