Allows developer to return an ExcelResult by passing a collection of any type to Excel method just like JsonResult and Json method, Just call the method, pass the collection you want its data to be exported to excel file and watch the magic.
Also you can mark some properties to be ignored by ExcelResult while generating the Excel file by setting ExcelIgnoreAttribute on the property you want to ignore and more..
In order to have ExcelResult to work you need to specify Excel method in your base controller if you have one, If you don't just let your controller inherit from ExcelMasterController
public class HomeController : ExcelMasterController
{
public ActionResult ExportToExcel()
{
List<Person> lst = new List<Person>();
for (int c = 0; c < 50; c++)
{
lst.Add(new Person() {BirthDate = new DateTime(1990, 12, 18), IsMale = true, Name = "Ibrahim", Summary = "I'm passionate about technology specially Microsoft's technologies in software development, Adore software development and willing to be one of the worldwide noted developers, getting the most of my technical skills along with my studies in business field to develop a real business solutions.I'm a knowledge hunger have no end point for my learning path and trying to reach for the sky." });
}
return Excel(lst, "TEST REPORT","TEST SHEET");
}
}
public class ExcelMasterController : Controller
{
protected ExcelResult<T> Excel<T>(IEnumerable<T> data) where T : class
{
return new ExcelResult<T>(data);
}
protected ExcelResult<T> Excel<T>(IEnumerable<T> data, string fileName) where T : class
{
return new ExcelResult<T>(data, fileName);
}
protected ExcelResult<T> Excel<T>(IEnumerable<T> data, string fileName, string sheetName) where T : class
{
return new ExcelResult<T>(data, fileName, sheetName);
}
}
ExcelResult Have several overloads that takes some parameters described as following
| Parameter | Type | Description |
|---|---|---|
| data | IEnumerable<T> where T : class | The data you want to be exported to Excel File, It takes a generic collection but the generic type is constrained to be a class. |
| fileName (Optional) | String | The name of the file will be downloaded by the client (Default will be the name of the type passed to the generic data collection). |
| sheetName (Optional) | String | The name of the sheet in the excel file (Default will be the name of the type passed to the generic data collection). |
MvcExcelExtensions contains some attributes that can be used to format your returned excel file, Just mark your class's Properties with the attributes and it will work.
Following is Person class which i used above to generate ExcelResult by passed a list of it to Excel<T> method.
[ExcelSheetStyle(HeaderFontSize = 14, BodyFontSize = 12, FontFamily = "Times New Roman", IsHeaderBold = true)]
public class Person
{
public string Name { get; set; }
[ExcelValueFormat("dd, MMMM yyyy"), ExcelDisplayName("Birthday")]
public DateTime BirthDate { get; set; }
[ExcelIgnore]
public bool IsMale { get; set; }
[ExcelColumnStyle(HorizontalAlignment = HorizontalAlign.Left, VerticalAlignment = VerticalAlign.Top, Width = 35, WordWrap = true)]
public string Summary { get; set; }
}
You can mark your class with this attribute to specify some global settings you want in your resulting Excel file.
| Parameter | Type | Description |
|---|---|---|
| HeaderFontSize (Optional) | float | Set the header's font size of the resulting excel file where header is the first row. (Default 14) |
| BodyFontSize (Optional) | float | Set the body's font size of the resulting excel file where body is all rows except the first. (Default 12) |
| FontFamily | String | Set the font family that will be used for the text inside the resulting excel file. (Default 'Times New Roman') |
| IsHeaderBold | bool | Specify if you want the header (i.e, First Row) to be bolded. (Default true) |
You can set a format for a specific property to be used in the resulting excel file. Note that the type of the property marked with this attribute must implement IFormattable interface otherwise the format will be ignored.
This attribute takes only string representing the format in its constructor and its mandatory
Use this attribute if you don't want the resulting excel file's header contain the property name but contains another name, In the example above i decided to use 'Birthday' as a header for 'BirthDate' values
This attribute takes only string representing the header title in its constructor and its mandatory
Use this attribute if you don't want a property to be printed in the resulting excel file.
This attribute takes no arguments and have no parameters
Use this attribute to style the column that will list the property's values.
| Parameter | Type | Description |
| HorizontalAlignment (Optional) | HorizontalAlign | Set horizontal alignment for all column's cells (Default HorizontalAlign.Center) |
| VerticalAlignment | VerticalAlign | Set vertical alignment for all column's cells (Default VerticalAlign.Middle) |
| Width (Optional) | double | Set a custom width to the column that will list the property's values. (Default To Fit Content) |
| WordWrap | bool | Set to true if you want the word to be wrapped. (Default false) |
Special Thanks To EPPlus Team, I've used their great library in CodePlex to create mine. So special thanks for them. You can find EPPlus library Here