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!
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");
}
}
}
}
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();
}
}
}
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);
}
}
}
//------------------------------------------------------------------------------
// <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; }
}
}
//------------------------------------------------------------------------------
// <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; }
}
}
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?