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"))
.Mod(o => o.ColumnsSelector())
.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');

var grid = $('#@(grid)');

// request paramters (sorting, grouping, any parent parameters)
var req = grid.data('api').getRequest();

// send visible columns info, binds to GridExpParams
var viscols = utils.getVisCols(grid);
awef.loop(viscols, function(v) {
$form.append("<input type='hidden' name='visNames' value='" + v + "'/>");
});

awef.loop(req, function(val) {
$form.append("<input type='hidden' name='" + val.name + "' value='" + val.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)
{
KeyProp = o => o.Id,
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();
}

private ExpColumn[] getExpColumns()
{
var expColumns = new[]
{
new ExpColumn { Name = "Id", Width = 1.5f },
new ExpColumn { Name = "Person", Width = 2.5f },
new ExpColumn { Name = "Food", Width = 3 },
new ExpColumn { Name = "Date", Width = 2.2f },
new ExpColumn { Name = "Price", Width = 2 },
new ExpColumn { Name = "CountryName", Width = 3, Header = "Country" },
new ExpColumn { Name = "ChefName", Width = 3.2f, Header = "Chef" },
new ExpColumn { Name = "Location", Width = 2.7f }
};

return expColumns;
}

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

var gridModel = BuildGridModel(g);

var workbook = new GridExcelBuilder(getExpColumns())
{
// adding ExpParams so the hidden columns won't get exported
ExpParams = expParams
}.Build(gridModel);

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

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

var gridModel = BuildGridModel(g);

var res = new GridTxtBuilder(getExpColumns())
{
// adding ExpParams so the hidden columns won't get exported
ExpParams = expParams
}.Build(gridModel);

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 ExportGridToPdf(GridParams g, GridExpParams expParams, bool? allPages)
{
if (allPages.HasValue && allPages.Value)
{
g.Paging = false;
}

var gridModel = BuildGridModel(g);

var builder = new GridPdfBuilder(getExpColumns())
{
// adding ExpParams so the hidden columns won't get exported
ExpParams = expParams
};

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