none
Trying to export a gridview or webgrid data to an excel worksheet RRS feed

  • Question

  • Trying to export a gridview to an excel worksheet I'm using MVC here is my controller:

    public ActionResult Index()
            {
                ViewBag.Message = "Export HTML to Excel.";
                IEnumerable<RecorderLog> GetRecorderLog = fuelRecorderLogRepository.GetRecorderLog();
                GridView gv = new GridView();
                gv.DataSource = GetRecorderLog;
                gv.DataBind();
                Session["FuelLogSession"] = gv;
                
                return View(GetRecorderLog);
            }
    
            public ActionResult Download()
            {
                if (Session["FuelLogSession"] != null)
                {
                    return new DownloadFileActionResult((GridView)Session["FuelLogSession"], "report.xls");
                }
                else
                {
                    return new JavaScriptResult();
                }
            }

    My class to run:

    sing System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using System.IO;
    using System.Text;
    using System.Web.UI.WebControls;
    using System.Web.UI;
    
    using FuelRecorderLog.Domain.Entities;
    
    using Excel = Microsoft.Office.Interop.Excel;
    using Office = Microsoft.Office.Core;
    using Microsoft.Office.Tools.Excel;
    using System.Web.Helpers;
    
    namespace FuelRecorderLog.Web
    {
        public class DownloadFileActionResult : ActionResult
        {
            public GridView ExcelGridView { get; set; }
            public string fileName { get; set; }
    
            public DownloadFileActionResult(GridView gv, string pFileName)
            {
                ExcelGridView = gv;
                fileName = pFileName;
            }
    
            void DisplayInExcel(IEnumerable<RecorderLog> accounts, Action<RecorderLog, Excel.Range> DisplayFunc)
            {
                var excelApp = new Excel.Workbook();
                // Add a new Excel workbook.
                var workSheet = excelApp.ActiveSheet();
                workSheet.Visible = true;
                workSheet.Range["A1"].Value = "STR Number";
                workSheet.Range["B1"].Value = "EquipmentRentalDetailedInfoID";
                workSheet.Range["C1"].Value = "Vehicle Tag";
                workSheet.Range["D1"].Value = "Reservation Number";
                workSheet.Range["A4"].Value = "Fuel Date";
                workSheet.Range["B4"].Value = "Gallons Consumed";
                workSheet.Range["C4"].Value = "Fuel Cost";
    
                workSheet.Range["A1"].Select();
                workSheet.Range["B1"].Select();
                workSheet.Range["C1"].Select();
                workSheet.Range["D1"].Select();
                workSheet.Range["A4"].Select();
                workSheet.Range["B4"].Select();
                workSheet.Range["C1"].Select();
    
                workSheet.Range["A1"].Locked = true;
    
                foreach (var ac in accounts)
                {
                    //DisplayFunc(ac, excelApp.ActiveCell);
                    workSheet.ActiveCell.Offset[1, A].Select();
                    workSheet.ActiveCell.Offset[1, B].Select();
                    workSheet.ActiveCell.Offset[1, C].Select();
                    workSheet.ActiveCell.Offset[1, D].Select();
                }
    
                workSheet.Range["A1:D4"].Copy();
    
                workSheet.Columns[1].Autofit();
                workSheet.Columns[2].Autofit();
                workSheet.Columns[3].Autofit();
                workSheet.Columns[4].Autofit();
            }
        }
    I do not understand why my code is not working, what am I missing?

    Sunday, March 26, 2017 10:48 PM

All replies

  • Hi bthumber,

    I am not much familiar with MVC, but I can understand your Excel interop code.

    all I understand that you are trying to create a object of Excel Application with Excel.workbook.

    which is wrong.

    then you need to create a workbook or open any existing workbook , that you did not done in your code.

    then you directly try to activate the ActiveSheet. but if you did not create or open any workbook then how can you reference or create an object of Activesheet.

    then you try to assign some static values to cells and then try to select some cells. then try to copy the range and try to autofit the columns.

    I think that you should receive an error when you run the code , because code is not written properly.

    you can try to refer example below for MVC to export grid view to Excel.

    public class DownloadFileActionResult : ActionResult
        {
    
            public GridView ExcelGridView { get; set; }
            public string fileName { get; set; }
    
    
            public DownloadFileActionResult(GridView gv, string pFileName)
            {
                ExcelGridView = gv;
                fileName = pFileName;
            }
    
    
            public override void ExecuteResult(ControllerContext context)
            {
    
               //Create a response stream to create and write the Excel file
                HttpContext curContext = HttpContext.Current;
                curContext.Response.Clear();
                curContext.Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
                curContext.Response.Charset = "";
                curContext.Response.Cache.SetCacheability(HttpCacheability.NoCache);
                curContext.Response.ContentType = "application/vnd.ms-excel";
    
                //Convert the rendering of the gridview to a string representation 
                StringWriter sw = new StringWriter();
                HtmlTextWriter htw = new HtmlTextWriter(sw);
                ExcelGridView.RenderControl(htw);
                
                //Open a memory stream that you can use to write back to the response
                byte[] byteArray = Encoding.ASCII.GetBytes(sw.ToString());
                MemoryStream s = new MemoryStream(byteArray);
                StreamReader sr = new StreamReader(s, Encoding.ASCII);
    
                //Write the stream back to the response
                curContext.Response.Write(sr.ReadToEnd());
                curContext.Response.End();
    
            }
    
        } 


    public ActionResult Index()
           {
               ViewBag.Message = "Welcome to ASP.NET MVC!";
               CarModels cm = new CarModels();
               List<Cars> model = cm.getAllCars();
    
               GridView gv = new GridView();
               gv.DataSource = model;
               gv.DataBind();
               Session["Cars"] = gv;
    
               return View(model);
           }
    


    public ActionResult Download()
            {
                if (Session["Cars"] != null)
                {
                    return new DownloadFileActionResult((GridView)Session["Cars"], "Cars.xls");
                }
                else 
                {
                   //Some kind of a result that will indicate that the view has 
                   //not been created yet. I would use a Javascript message to do so. 
                }
            }
    


    @Html.ActionLink("Download File", "Download")
    

    for details description regarding above code sample, please refer link below.

    MVC Grid to Excel file download

    below are some other code examples and alternative approaches for MVC that you can refer to solve your issue.

    Asp.Net MVC Export Data to Excel File Of WebGrid Using C#.Net

    Export Data in Excel File with Asp.Net MVC

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 27, 2017 3:06 AM
    Moderator