Exporting Excel data using MVC 3

Go To StackoverFlow.com

1

I have been searching the internet for days now and I was just wondering if anyone has done any work with exporting data to excel using MVC 3. I really need a good tutorial that has step-by-step instructions on how to export excel data using MVC 3 with C#. I have found plenty of articles but none of them seem to work with my existing database. I am just looking for some instruction on where to put methods etc. This tutorial http://stephenwalther.com/blog/archive/2008/06/16/asp-net-mvc-tip-2-create-a-custom-action-result-that-returns-microsoft-excel-documents.aspx seems to be highly recommended on stack overflow. I have followed the article's instructions to the letter but I can't run the program because Visual studio is throwing an error on the return for method GenerateExcel1

This is where VS is saying I have an error

return this.Excel(db, db.iamp_mapping, "data.xls")

right now I am getting 2 error messages that say

Error 1 'DBFirstMVC.Controllers.PaController' does not contain a definition for 'Excel' and the best extension method overload 'DBFirstMVC.CustomActionResults.ExcelControllerExtensions.Excel(System.Web.Mvc.Controller, System.Data.Linq.DataContext, System.Linq.IQueryable, string)' has some invalid arguments C:\Documents and Settings\lk230343\My Documents\Visual Studio 2010\WebSites\DBFirstMVC Saves\DBFirstMVC_CRUD_and_PAGING_done\DBFirstMVC\Controllers\PaController.cs 193 24 DBFirstMVC

Error 2 Argument 2: cannot convert from 'DBFirstMVC.Models.PaEntities' to 'System.Data.Linq.DataContext' C:\Documents and Settings\lk230343\My Documents\Visual Studio 2010\WebSites\DBFirstMVC Saves\DBFirstMVC_CRUD_and_PAGING_done\DBFirstMVC\Controllers\PaController.cs 193 35 DBFirstMVC

Has anyone ever seen these errors before or have any idea on how I can fix them? I mean the excel method exists in ExcelControllerExtensions.cs and I thought I had made that assembly available to the PaController. Honestly, I have no idea why it is throwing an error on the return so any advice/discussion/help would be welcomed. I have included the code for the 3 files that I have been messing with but if you think I forgot to post one that is needed to diagnose this error let me know and I will post it. Thanks for your help!

PaController

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Web.UI.WebControls;
using System.Web;
using System.Web.Mvc;
using DBFirstMVC.Models;
using System.Data;
using PagedList;
using PagedList.Mvc;
using DBFirstMVC.Controllers;
using System.IO;
using DBFirstMVC;
using DBFirstMVC.CustomActionResults;


namespace DBFirstMVC.Controllers

{
    public class PaController : Controller
    {
       private PaEntities db = new PaEntities();
        //
        // GET: /Pa/

        public ViewResult Index(string sortOrder, string currentFilter, string searchString, int? page)
        {
            ViewBag.CurrentSort = sortOrder;
            ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "PA desc" : "";
            ViewBag.MPSortParm = sortOrder == "MP" ? "MP desc" : "MP asc";
            ViewBag.IASortParm = sortOrder == "IA" ? "IA desc" : "IA asc";


            if (Request.HttpMethod == "GET")
            {
                searchString = currentFilter;
            }
            else
            {
                page = 1;
            }
            ViewBag.CurrentFilter = searchString;


            var IAMP = from p in db.iamp_mapping select p;

            if (!String.IsNullOrEmpty(searchString))
            {
                IAMP = IAMP.Where(p => p.PA.ToUpper().Contains(searchString.ToUpper()));
            }

            switch (sortOrder)
            {
                case "Pa desc":
                    IAMP = IAMP.OrderByDescending(p => p.PA);
                    break;
                case "MP desc":
                    IAMP = IAMP.OrderByDescending(p =>p.MAJOR_PROGRAM);
                    break;
                case "MP asc":
                    IAMP = IAMP.OrderBy(p =>p.MAJOR_PROGRAM);
                    break;
                case "IA desc":
                    IAMP = IAMP.OrderByDescending(p => p.INVESTMENT_AREA);
                    break;
                case "IA asc":
                    IAMP = IAMP.OrderBy(p => p.INVESTMENT_AREA);
                    break;
                default:
                    IAMP = IAMP.OrderBy(p => p.PA);
                    break;
            }
            int pageSize = 25;
            int pageNumber = (page ?? 1);

            return View(IAMP.ToPagedList(pageNumber, pageSize));
        }


        //
        // GET: /Pa/Details/5

        public ActionResult Details(int id)
        {
            return View();
        }

        //
        // GET: /Pa/Create

        public ActionResult Create()
        {
            return View();
        }

        //
        // POST: /Pa/Create

        [HttpPost]
        public ActionResult Create(iamp_mapping IAMP)
        {
            try
            {
                using (var db = new PaEntities())
                {
                    db.iamp_mapping.Add(IAMP);
                    db.SaveChanges();
                }

                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        //
        // GET: /Pa/Edit/5

        public ActionResult Edit(string id)
        {

            using (var db = new PaEntities())
            {

                return View(db.iamp_mapping.Find(id));
            }
        }

        //
        // POST: /Pa/Edit/5

        [HttpPost]
        public ActionResult Edit(string id, iamp_mapping IAMP)
        {
            try
            {
                using (var db = new PaEntities())
                {
                    db.Entry(IAMP).State = EntityState.Modified;
                    db.SaveChanges();
                    return RedirectToAction("");
                }
            }
            catch
            {
                return View();
            }
        }

        //
        // GET: /Pa/Delete/5

        public ActionResult Delete(string id)
        {
            using (var db = new PaEntities())
            {

                return View(db.iamp_mapping.Find(id));
            }
        }

        //
        // POST: /Pa/Delete/5

        [HttpPost]
        public ActionResult Delete(string id, iamp_mapping IAMP)
        {
            try
            {
                using (var db = new PaEntities())
                {
                    var vIAMP = db.iamp_mapping.Find(id);
                    db.Entry(vIAMP).State = EntityState.Deleted;
                    db.SaveChanges();
                    return RedirectToAction("Index");
                }

            }
            catch (Exception e)
            {
                throw (e);
                //return View();
            }
        }
        public ActionResult GenerateExcel1()
        {
            using (var db = new PaEntities())
            {
                return this.Excel(db, db.iamp_mapping, "data.xls");
            }
        }









    }
}

ExcelResult.cs

using System;
using System.Web.Mvc;
using System.Data.Linq;
using System.Collections;
using System.IO;
using System.Web.UI.WebControls;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Drawing;

namespace DBFirstMVC
{
    public class ExcelResult : ActionResult
    {
        private DataContext _dataContext;
        private string _fileName;
        private IQueryable _rows;
        private string[] _headers = null;

        private TableStyle _tableStyle;
        private TableItemStyle _headerStyle;
        private TableItemStyle _itemStyle;

        public string FileName
        {
            get { return _fileName; }
        }

        public IQueryable Rows
        {
            get { return _rows; }
        }


        public ExcelResult(DataContext dataContext, IQueryable rows, string fileName)
            : this(dataContext, rows, fileName, null, null, null, null)
        {
        }

        public ExcelResult(DataContext dataContext, string fileName, IQueryable rows, string[] headers)
            : this(dataContext, rows, fileName, headers, null, null, null)
        {
        }

        public ExcelResult(DataContext dataContext, IQueryable rows, string fileName, string[] headers, TableStyle tableStyle, TableItemStyle headerStyle, TableItemStyle itemStyle)
        {
            _dataContext = dataContext;
            _rows = rows;
            _fileName = fileName;
            _headers = headers;
            _tableStyle = tableStyle;
            _headerStyle = headerStyle;
            _itemStyle = itemStyle;

            // provide defaults
            if (_tableStyle == null)
            {
                _tableStyle = new TableStyle();
                _tableStyle.BorderStyle = BorderStyle.Solid;
                _tableStyle.BorderColor = Color.Black;
                _tableStyle.BorderWidth = Unit.Parse("2px");
            }
            if (_headerStyle == null)
            {
                _headerStyle = new TableItemStyle();
                _headerStyle.BackColor = Color.LightGray;
            }
        }

        public override void ExecuteResult(ControllerContext context)
        {
            // Create HtmlTextWriter
            StringWriter sw = new StringWriter();
            HtmlTextWriter tw = new HtmlTextWriter(sw);

            // Build HTML Table from Items
            if (_tableStyle != null)
                _tableStyle.AddAttributesToRender(tw);
            tw.RenderBeginTag(HtmlTextWriterTag.Table);

            // Generate headers from table
            if (_headers == null)
            {
                _headers = _dataContext.Mapping.GetMetaType(_rows.ElementType).PersistentDataMembers.Select(m => m.Name).ToArray();
            }


            // Create Header Row
            tw.RenderBeginTag(HtmlTextWriterTag.Thead);
            foreach (String header in _headers)
            {
                if (_headerStyle != null)
                    _headerStyle.AddAttributesToRender(tw);
                tw.RenderBeginTag(HtmlTextWriterTag.Th);
                tw.Write(header);
                tw.RenderEndTag();
            }
            tw.RenderEndTag();



            // Create Data Rows
            tw.RenderBeginTag(HtmlTextWriterTag.Tbody);
            foreach (Object row in _rows)
            {
                tw.RenderBeginTag(HtmlTextWriterTag.Tr);
                foreach (string header in _headers)
                {
                    string strValue = row.GetType().GetProperty(header).GetValue(row, null).ToString();
                    strValue = ReplaceSpecialCharacters(strValue);
                    if (_itemStyle != null)
                        _itemStyle.AddAttributesToRender(tw);
                    tw.RenderBeginTag(HtmlTextWriterTag.Td);
                    tw.Write(HttpUtility.HtmlEncode(strValue));
                    tw.RenderEndTag();
                }
                tw.RenderEndTag();
            }
            tw.RenderEndTag(); // tbody

            tw.RenderEndTag(); // table
            WriteFile(_fileName, "application/ms-excel", sw.ToString());
        }


        private static string ReplaceSpecialCharacters(string value)
        {
            value = value.Replace("’", "'");
            value = value.Replace("“", "\"");
            value = value.Replace("”", "\"");
            value = value.Replace("–", "-");
            value = value.Replace("…", "...");
            return value;
        }

        private static void WriteFile(string fileName, string contentType, string content)
        {
            HttpContext context = HttpContext.Current;
            context.Response.Clear();
            context.Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
            context.Response.Charset = "";
            context.Response.Cache.SetCacheability(HttpCacheability.NoCache);
            context.Response.ContentType = contentType;
            context.Response.Write(content);
            context.Response.End();
        }
    }
}

ExcelControllerExtensions

using System;
using System.Web.Mvc;
using System.Data.Linq;
using System.Collections;
using System.Web.UI.WebControls;
using System.Linq;

namespace DBFirstMVC.CustomActionResults
{
   public static class ExcelControllerExtensions
    {

        public static ActionResult Excel
        (
            this Controller controller,
            DataContext dataContext,
            IQueryable rows,
            string fileName
        )
        {
            return new ExcelResult(dataContext, rows, fileName, null, null, null, null);
        }

        public static ActionResult Excel
        (
            this Controller controller,
            DataContext dataContext,
            IQueryable rows,
            string fileName,
            string[] headers
        )
        {
            return new ExcelResult(dataContext, rows, fileName, headers, null, null, null);
        }

        public static ActionResult Excel
        (
            this Controller controller,
            DataContext dataContext,
            IQueryable rows,
            string fileName,
            string[] headers,
            TableStyle tableStyle,
            TableItemStyle headerStyle,
            TableItemStyle itemStyle
        )
        {
            return new ExcelResult(dataContext, rows, fileName, headers, tableStyle, headerStyle, itemStyle);
        }

    }
}

Model1.context.cs

//------------------------------------------------------------------------------
// <auto-generated>
//    This code was generated from a template.
//
//    Manual changes to this file may cause unexpected behavior in your application.
//    Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;

namespace DBFirstMVC.Models
{
    public partial class PaEntities : DbContext
    {
        public PaEntities()
            : base("name=PaEntities")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }

        public DbSet<iamp_mapping> iamp_mapping { get; set; }
        public DbSet<pa_mapping> pa_mapping { get; set; }
    }
}

iamp_mapping.cs

//------------------------------------------------------------------------------
// <auto-generated>
//    This code was generated from a template.
//
//    Manual changes to this file may cause unexpected behavior in your application.
//    Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

using System;
using System.Collections.Generic;

namespace DBFirstMVC.Models
{
    public partial class iamp_mapping
    {
        public string PA { get; set; }
        public string MAJOR_PROGRAM { get; set; }
        public string INVESTMENT_AREA { get; set; }
    }

}
2012-04-03 21:28
by Goldentp


1

The reason for the error is right there in the message: cannot convert from 'DBFirstMVC.Models.PaEntities' to 'System.Data.Linq.DataContext. It means that you PaEntities type cannot be converted to System.Data.Linq.DataContext, therefore none of your Excel extension methods' signature matches the arguments you are passing.

What is your PaEntities type? Does it inherit from System.Data.Linq.DataContext?

2012-04-03 22:08
by Pedro
Pedro, I think PAEntities is my DBContext but I am new to MVC, all it contains is three getters and setters for my three DB objects PA, MAJORPROGRAM and INVESTMENTAREA. I will post the files that contain the models abov - Goldentp 2012-04-04 13:34