This post will show a very easy way to export a generic IEnumerable into an Excel file within an Azure Function V2.
This is very useful, for example, to give the user the capability to export data from a LoB database.
Related Articles
First, I create an empty Azure Function, in my case triggered by an HTTP call and without authentication, only for test purpose and I changed the Framework version to 3.0.
Then I add the reference to a very simply to user Nuget Package: ClosedXML. Differently from OpenXmlSDK, which is a bit complex, this library allows you to easily manipulate Excel 2007 (or above) files (XLSX) from .net code.
Install-Package ClosedXML -Version 0.94.2
Now I want to create my example class named Student: a collection of students will be written inside the Excel file. The static method GetFakeStudents will return a list of some fake student.
internal class Student
{
public string Name { get; set; }
public string Surname { get; set; }
public DateTime DateofBird { get; set; }
public string Address1 { get; set; }
public string Address2 { get; set; }
internal static List GetFakeStudents()
{
var students = new List()
{
new Student()
{
Name = "Mario",
Surname = "Rossi",
Address1 = "Via Napoli 12",
Address2 = "Genoa",
DateofBird = new DateTime(2008,5,22)
},
new Student()
{
Name = "Filippo",
Surname="Gialli",
Address1 = "Via Sapri 1",
Address2 = "Genoa",
DateofBird = new DateTime(2008, 2, 11)
},
new Student()
{
Name = "Luca",
Surname="Verdi",
Address1 = "Via Boine 11",
Address2 = "Genoa",
DateofBird = new DateTime(2008, 11, 7)
},
new Student()
{
Name = "Federica",
Surname="Blue",
Address1 = "Via Montemoro 4",
Address2 = "Genoa",
DateofBird = new DateTime(2008, 3, 17)
}
};
return students;
}
}
Thanks to Stack Overflow, I create a simple function which creates a (vintage) System.Data.DataTable from a generic IEnumerable.
This datatable will be added inside the Excel Workbook.
private static DataTable CreateDataTableT>(IEnumerable list)
{
Type type = typeof(T);
var properties = type.GetProperties();
DataTable dataTable = newDataTable();
foreach (PropertyInfo info in properties)
{
if (info.PropertyType != typeof(Guid))
{
dataTable.Columns.Add(new DataColumn(info.Name, Nullable.GetUnderlyingType(info.PropertyType) ?? info.PropertyType));
}
else
{
dataTable.Columns.Add(new DataColumn(info.Name, typeof(string)));
}
}
foreach (T entity in list)
{
object[] values = new object[properties.Length];
for (int i = 0; i
{
values[i] = properties[i].GetValue(entity);
}
dataTable.Rows.Add(values);
}
return dataTable;
}
Now, by using the InsertTable method offered by ClosedXML library, I can add my datatable to an Excel WorkSheet inside an Excel WorkBook stored inside a System.Io.MemoryStream.
var ms = new System.IO.MemoryStream();
XLWorkbook wb = newXLWorkbook();
var sheet = wb.AddWorksheet("Sheet1");
sheet.Cell(1, 1).InsertTable(studentsDT);
sheet.Columns().Width = 20;
//I can't use this method to adjust column with because it use System.Drawing and GDI+ functionality
