Awesome ASP.net Core and MVC Controls

Export grid to excel, pdf, txt (csv) demo

This demo is showing how to build the grid model and use it to export the data in various formats (xls, pdf, txt) using external libraries and custom code.






Export the content of a grid to excel, pdf and txt file (csv). To create an excel file we're using the NPOI library.
For exporting to Pdf the iText7 library is used.
To get the entire source code you can download this demo.
GridExportToExcelDemo/Index.cshtml
@{
var grid = "LunchGrid";
}
@(Html.Awe().Grid(grid)
.Url(Url.Action("GetItems"))
.Persistence(Persistence.Session)
.Resizable()
.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">Export grid to excel</button>
<button id="exportGridPdf" type="button" class="awe-btn">Export grid to Pdf</button>
<button id="exportGridTxt" type="button" class="awe-btn">Export grid to txt (csv)</button>
<div style="padding: 0 1em; display: inline-block;">
<label>@Html.Awe().CheckBox("allPages") All pages</label>
</div>

<br/>
<br/>
<button id="exportAll" type="button" class="awe-btn">Export all records to excel</button>
</div>
<script>
$(function () {
$('#exportAll').click(function () {
getFile('@Url.Action("ExportAllToExcel", "GridExportToExcelDemo")');
});

$('#exportGrid').click(function () {
getFile('@Url.Action("ExportGridToExcel", "GridExportToExcelDemo")');
});

$('#exportGridPdf').click(function () {
getFile('@Url.Action("ExportGridToPdf", "GridExportToExcelDemo")');
});

$('#exportGridTxt').click(function () {
getFile('@Url.Action("ExportGridToTxt", "GridExportToExcelDemo")');
});

function getFile(url) {
var $form = $('<form method="post"/>').attr('action', url).appendTo('body');

// add request paramters (sorting, grouping, any parent parameters)
var req = $('#@(grid)').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();
$form.remove();
}
});
</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 ExportGridToTxt(GridParams g, bool? allPages)
{
if (allPages.HasValue && allPages.Value)
{
g.Paging = false;
}

var gridModel = BuildGridModel(g);

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

var res = GridTxtBuilder.Build(gridModel, properties, headers);

using (var memoryStream = new MemoryStream())
{
using (var writer = new StreamWriter(memoryStream))
{
writer.WriteLine(res);
}

return File(memoryStream.GetBuffer(), "text/plain", "lunches.txt");
}
}

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

var gridModel = BuildGridModel(g);

var properties = 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, properties, headers);

using (var stream = new MemoryStream())
{
workbook.Write(stream);
stream.Close();
return File(stream.ToArray(), "application/vnd.ms-excel", "lunches.xls");
}
}

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

var gridModel = BuildGridModel(g);

var properties = new[] { "Id", "Person", "Food", "Date", "Price", "CountryName", "ChefName", "Location" };
var headers = new[] { "Id", "Person", "Food", "Date", "Price", "Country", "Chef", "Location" };
var relativeWidths = new float[] {1.5f, 2.5f, 3, 2.2f, 2, 3, 3.2f, 2.7f};

var builder = new GridPdfBuilder(properties, headers)
{
RelativeWidths = relativeWidths
};

var workStream = new MemoryStream();
var writer = new PdfWriter(workStream);
var pdf = new PdfDocument(writer);
var document = new Document(pdf);

document.Add(new Paragraph("Hello World!"));
document.Add(new Paragraph("Export to pdf example (using iText7)"));
document.Add(new Paragraph(new Text("\n")));

var table = builder.Build(gridModel);
document.Add(table);
writer.SetCloseStream(false);
document.Close();

var byteInfo = workStream.ToArray();
workStream.Write(byteInfo, 0, byteInfo.Length);
workStream.Position = 0;

return File(workStream, "application/pdf", "lunches.pdf");
}

/// <summary>
/// Demonstrates the simplest way of creating an excel workbook
/// it exports all the lunches records, without taking into account 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 properties = 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 < properties.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 < properties.Length; j++)
{
var cell = row.CreateCell(j);
var o = items[i];
cell.SetCellValue(o.GetType().GetProperty(properties[j]).GetValue(o, null).ToString());
}
}

using (var stream = new MemoryStream())
{
workbook.Write(stream);
stream.Close();
return File(stream.ToArray(), "application/vnd.ms-excel", "lunches.xls");
}
}
}



Comments