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

Question

User379720387 posted

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();
}

Answer 1

User-166373564 posted

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.

Answer 2

User379720387 posted

Web Pages is based on the page model.

MVC is based on the View model.

Two different things.