Export grid to excel demo

export the content of a grid to excel, to create an excel file we're using the NPOI library




GridExportToExcelDemo/Index.cshtml
@(Html.Awe().Grid("LunchGrid")
.Url(Url.Action("GetItems"))
.Persistence(Persistence.Session)
.Resizable(true)
.Height(350)
.Columns(
new Column { Bind = "Id", Width = 80 },
new Column { Bind = "Person" },
new Column { Bind = "Food" },
new Column { Bind = "Date", Width = 150},
new Column { Bind = "Price", Width = 110 },
new Column { Bind = "Country.Name", ClientFormat = ".CountryName", Header = "Country" },
new Column { Bind = "Chef.FirstName,Chef.LastName", ClientFormat = ".ChefName", Header = "Chef" },
new Column { Bind = "Location" }))

<br />
<div class="bar">
<button id="exportGrid" type="button" class="awe-btn ib">Export grid to excel</button>
<label>@Html.Awe().CheckBox("allPages") All pages</label>
<form id="exportGridForm" method="post" action="@Url.Action("ExportGridToExcel")" class="ib">
</form>
<br/>
<br/>
<form method="post" action="@Url.Action("ExportAllToExcel")" class="ib">
<input id="export" type="submit" class="awe-btn" value="Export all records to excel" />
</form>
</div>
<script>
$(function () {
$('#exportGrid').click(function () {
var $form = $('#exportGridForm').empty();

var req = $('#LunchGrid').data('api').getRequest();

for (var i = 0; i < req.length; i++) {
$form.append("<input type='hidden' name='" + req[i].name + "' value='" + req[i].value + "'/>");
}

$form.append($('#allPages').clone());

$form.submit();
});
});
</script>
Demos/Grid/GridExportToExcelDemoController.cs
public class GridExportToExcelDemoController : Controller
{
public ActionResult Index()
{
return View();
}

public ActionResult GetItems(GridParams g)
{
return Json(BuildGridModel(g).ToDto());
}

private GridModel<Lunch> BuildGridModel(GridParams g)
{
return new GridModelBuilder<Lunch>(Db.Lunches.AsQueryable(), g)
{
Key = "Id", // needed for Entity Framework | nesting | tree | api
Map = o => new
{
o.Id,
o.Person,
o.Food,
o.Location,
o.Price,
Date = o.Date.ToShortDateString(),
CountryName = o.Country.Name,
ChefName = o.Chef.FirstName + " " + o.Chef.LastName
},
MakeFooter = gi =>
{
if (gi.Level == 0)
{
return new
{
Id = "Total",
Price = "Min: " + gi.Items.Min(o => o.Price),
Date = "Max: " + gi.Items.Max(o => o.Date).ToShortDateString()
};
}

return new
{
Price = "Min: " + gi.Items.Min(o => o.Price),
Date = "Max: " + gi.Items.Max(o => o.Date).ToShortDateString()
};
}
}.BuildModel();
}

[HttpPost]
public ActionResult ExportGridToExcel(GridParams g, bool? allPages)
{
if (allPages.HasValue && allPages.Value)
{
g.Paging = false;
}

var gridModel = BuildGridModel(g);

var columns = new[] { "Id", "Person", "Food", "Date", "Price", "CountryName", "ChefName", "Location" };
var headers = new[] { "Id", "Person", "Food", "Date", "Price", "Country", "Chef", "Location" };

var workbook = GridExcelBuilder.Build(gridModel, columns, headers);

var stream = new MemoryStream();
workbook.Write(stream);
stream.Close();

return File(stream.ToArray(), "application/vnd.ms-excel", "lunches.xls");
}

/// <summary>
/// Demonstrates the simplest way of creating an excel workbook
/// it exports all the lunches records, without taking into count any sorting/paging that is done on the client side
/// </summary>
/// <returns></returns>
[HttpPost]
public ActionResult ExportAllToExcel()
{
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet("sheet1");

var items = Db.Lunches.Select(
o => new
{
o.Id,
o.Person,
o.Food,
o.Location,
CountryName = o.Country.Name,
ChefName = o.Chef.FirstName + " " + o.Chef.LastName
}).ToList();

var columns = new[] { "Id", "Person", "Food", "CountryName", "ChefName", "Location" };
var headers = new[] { "Id", "Person", "Food", "Country", "Chef", "Location" };

var headerRow = sheet.CreateRow(0);

//create header
for (int i = 0; i < columns.Length; i++)
{
var cell = headerRow.CreateCell(i);
cell.SetCellValue(headers[i]);
}

//fill content
for (int i = 0; i < items.Count; i++)
{
var rowIndex = i + 1;
var row = sheet.CreateRow(rowIndex);

for (int j = 0; j < columns.Length; j++)
{
var cell = row.CreateCell(j);
var o = items[i];
cell.SetCellValue(o.GetType().GetProperty(columns[j]).GetValue(o, null).ToString());
}
}

var stream = new MemoryStream();
workbook.Write(stream);
stream.Close();

return File(stream.ToArray(), "application/vnd.ms-excel", "lunches.xls");
}
}