Create Excels with C# and EPPlus: Import
posted in dotnet on • by Wouter Van SchandevijlIEnumerable objects
Exporting data to an Excel doesn’t get easier than this
using (var package = new ExcelPackage())
{
var sheet = package.Workbook.Worksheets.Add("IEnumerable");
var data = new[]
{
new {Name = "A", Value = 1},
new {Name = "B", Value = 2},
new {Name = "C", Value = 3},
};
bool generateHeaders = true;
sheet.Cells["A1"].LoadFromCollection(data, generateHeaders);
// Overwrite headers with something fancier
sheet.Cells["A1"].SetHeaders("Name", "Value");
package.SaveAs(new FileInfo(@""));
}
Which would result in the following xlsx.
| A | B | |
|---|---|---|
| 1 | Name | Value |
| 2 | A | 1 |
| 3 | B | 2 |
| 4 | C | 3 |
There are overloads for
- Writing headers or not
TableStyles: Style the table in one of the predefined formatsBindingFlags+MemberInfo: Allow you to export specific (private) fields
The pretty blue background for the header is done with an extension method. The LoadFromXXX methods do not set any header styling themselves.
public static void SetHeaders(this ExcelRangeBase cell, params string[] headers)
{
foreach (string text in headers)
{
cell.Value = text;
cell.Style.Font.Bold = true;
cell.Style.Fill.PatternType = ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
cell.Style.Font.Color.SetColor(Color.White);
cell = cell.Offset(0, 1);
}
}
LoadFromArrays:
using (var package = new ExcelPackage())
{
var sheet = package.Workbook.Worksheets.Add("Arrays");
var data = new[]
{
new[] {"A1", "B1", "C1"},
new[] {"A2", "B2", "C3"},
};
sheet.Cells["A1"].LoadFromArrays(data);
package.SaveAs(new FileInfo(@""));
}
CSV file
using (var package = new ExcelPackage())
{
var sheet = package.Workbook.Worksheets.Add("CSV");
var file = new FileInfo("LoadFromText.csv");
var format = new ExcelTextFormat()
{
Delimiter = ',',
Culture = CultureInfo.InvariantCulture,
// Escape character for values containing the Delimiter
// ex: "A,Name",1 --> two cells, not three
TextQualifier = '"'
// Other properties
// EOL, DataTypes, Encoding, SkipLinesBeginning/End
};
sheet.Cells["A1"].LoadFromText(file, format);
package.SaveAs(new FileInfo(@""));
}
DataReader & DataTable
In case you ever need it, it’s there :)
sheet.Cells["A1"].LoadFromDataTable(new DataTable(), true);
Stuff that came into being during the making of this post
