Improvement on "Exporting the Razor Webgrid to Excel" - send Object to GenerateExcel RSS

• • Reply
  wavemaster

  Contributor

  2613 Points

  2717 Posts

  Improvement on "Exporting the Razor Webgrid to Excel" - send Object to GenerateExcel

  May 06, 2018 03:14 PM|wavemaster|LINK

  I have used this handy piece of code throughout many applications, however it has become time consuming to maintain because of all the filtering I do that change the query. In fact there are two queries with filtering the one that shows the grid and the one that creates the data for Excel.

  Is there a simpler way where I can re-use the Object from the first query?

  Not sure what the best approach is that handles the Layout = null

  https://www.mikesdotnetting.com/article/207/exporting-the-razor-webgrid-to-excel-using-oledb

  @{
      Layout = null;
  
      var appData = Server.MapPath("~/App_Data");
      var originalFileName = "Customers.xls";
      var newFileName = string.Format("{0}.xls", Guid.NewGuid().ToString());
      var originalFile = Path.Combine(appData, originalFileName);
      var newFile = Path.Combine(appData, newFileName);
      File.Copy(originalFile, newFile);
      
      var northwind = Database.Open("Northwind");
      var sql = "SELECT CustomerID, CompanyName, ContactName, Address, City, Country, Phone FROM Customers";
      var customers = northwind.Query(sql);
      
      var connString = string.Format(@"Provider=Microsoft.Jet.OleDb.4.0;
                                      Data Source={0}/{1};Extended Properties='Excel 8.0;HDR=Yes;'", 
                                      appData, newFileName);
      var provider = "System.Data.OleDb";
  
      using (var excel = Database.OpenConnectionString(connString, provider)){
          
          sql = @"INSERT INTO [Sheet1$] (CustomerID, CompanyName, ContactName, Address, City, Country, Phone) 
              VALUES (@0,@1,@2,@3,@4,@5,@6)";
          foreach(var customer in customers){
              excel.Execute(sql,  
                  customer.CustomerID, 
                  customer.CompanyName, 
                  customer.ContactName, 
                  customer.Address, 
                  customer.City, 
                  customer.Country, 
                  customer.Phone);
          }
      }
  
      Response.AddHeader("Content-disposition", "attachment; filename=report.xls");
      Response.ContentType = "application/octet-stream";
      Response.TransmitFile(newFile);
      Response.Flush();
      File.Delete(newFile);
      Response.End();
  }

  Click on "Mark as Answer" if my answers help you along.
• • Reply
  Angie xu - M...

  All-Star

  32817 Points

  3815 Posts

  Re: Improvement on "Exporting the Razor Webgrid to Excel" - send Object to GenerateExcel

  May 07, 2018 05:13 AM|Angie xu - MSFT|LINK

  According to your description, you want to re-use the retrieved records which are used to present in your WebGrid, and use the previous retrieved records for exporting.  Per my understanding, both your first and second queries are executed on the server side, if you want to re-use the records, you must cache the records into memory or file(s) on your web server or other storages (e.g. Redis, MongoDB, etc).

  You handle the downloading process within a View Page, I assume that you do not need to do it under a specific View, you could just create a action to handle this process. Moreover, for the downloading process, you could leverage WebGrid.GetHtml to get the html and directly respond to the client for downloading. Details you could follow this similar tutorial.

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
• • Reply
  wavemaster

  Contributor

  2613 Points

  2717 Posts

  Re: Improvement on "Exporting the Razor Webgrid to Excel" - send Object to GenerateExcel

  May 07, 2018 02:09 PM|wavemaster|LINK

  Web Pages is based on the page model.

  MVC is based on the View model.

  Two different things.

  Click on "Mark as Answer" if my answers help you along.