Hello. I would like to show you how to export data in C#.
Check out GitHub repository to find code discussed in this article: https://github.com/viktor-kostiuk/ExportDataToExcel
Exporting data is common task in enterprise world. We need to
export data to different formats: Report, Word, Excel, Json etc. Some parts of
exporting process are the same for many export formats.
Here is export workflow:
Data it is regular object. Let us create simple object names
Person
1. public class Person
2. {
3.
4. public string LastName { get; set; }
5. public string FirstName { get; set; }
6. public DateTime? DateOfBirth { get; set; }
7.
8. }
Prepare data for exporting
Usually data should be displayed in different format then it is written in
class. For this purpose, we need to have some extra information about Person:
which properties to display, format of data, some extra column needed that is not
presented in the object.
If we look at Entity Framework, we will see that it uses entity
model that describes entities. In this model we can define names of columns, constrains,
ignore some properties for mapping. So, I will create similar model descriptor:
ModelMetadata
1. public class ModelMetadata
2. {
3.
4. public IList<PropertyMetadata> Properties { get; } = new List<PropertyMetadata>();
5.
6. public IEnumerable<PropertyMetadata> EnumeratePropertiesToExport()
7. {
8. return Properties
9. .Where(x => !x.IsIgnored)
10. .OrderBy(x => x.Order);
11. }
12.
13. }
14.
15. public class PropertyMetadata
16. {
17.
18. public PropertyInfo Property { get; set; }
19. public string PropertyName { get; set; }
20. public string DisplayName { get; set; }
21. public Func<object,
object> ValueProvider { get; set; }
22. public bool IsIgnored { get; set; }
23. public int? Order { get; set; }
24.
25. public object GetValue(object obj) => ValueProvider != null
26. ? ValueProvider.Invoke(obj)
27. : ValueProvider.GetValue(obj);
28.
29. }
ModelMetadata just keep information about all properties
of some model.
PropertyMetada define information about object
properties that need to be exported. Important thing here, PropertyMetadata can be
created without real property in object. Let us investigate its properties.
PropertyMetadata
Property. It keeps PropertyInfo for object property. Generally,
we need to export just all properties. This is can be null, then ValueProvider
provider value for this property.
PropertName. Name of property in model. For example, LastName.
Need it because Property can be null but still want to have unique name for
property.
DisplayName. This will be used to generate header of column
when export to data.
ValueProvider. If not null, then used to provider
value for this property. It is required if Property is null.
IsIgnored. Used to ignore this property in export
process. If we want to display all properties except one, it is easily to fill properties
metadata from object properties using reflection, you see it later.
Order. Used to sort properties before exporting.
It has method GetValue that provide value from object
for this property.
Following code snipped
shows how to use PropertyMetadata:
1. [TestClass]
2. public class PropertyMetadataTests
3. {
4.
5. [TestMethod]
6. public void TestGettingLastName()
7. {
8. PropertyInfo lastNameProperty = typeof(Person).GetProperty("LastName");
9.
10. PropertyMetadata lastNamePropertyMetadata = new PropertyMetadata
11. {
12. Property = lastNameProperty,
13. PropertyName = lastNameProperty.Name,
14. DisplayName = "Last Name"
15. };
16.
17. Person person = new Person
18. {
19. DateOfBirth = new DateTime(1983, 12, 26),
20. FirstName = "Jon",
21. LastName = "Snow"
22. };
23.
24. string lastName = (string)lastNamePropertyMetadata.GetValue(person);
25. Assert.AreEqual("Snow", lastName);
26. }
27.
28. [TestMethod]
29. public void TestGettingLastNameWithValueProvider()
30. {
31. PropertyMetadata lastNamePropertyMetadata = new PropertyMetadata
32. {
33. PropertyName = "LastName",
34. DisplayName = "Last Name",
35. ValueProvider = (object obj) =>
36. {
37. Person p = (Person)obj;
38. return p.LastName;
39. }
40. };
41.
42. Person person = new Person
43. {
44. DateOfBirth = new DateTime(1983, 12, 26),
45. FirstName = "Jon",
46. LastName = "Snow"
47. };
48.
49. string lastName = (string)lastNamePropertyMetadata.GetValue(person);
50. Assert.AreEqual("Snow", lastName);
51. }
52.
53. }
The way of defining metadata is not simple. So, to help
build us metadata, we will create ModelMetadataBuilder. Its goal to simplify
creation of metadata.
Let us think what we can simplify. First, our ModelMetadata
is not generic, it is OK from usage point of view, but when create metadata it
is require writing extra cast operations. So, we create ModelMetadataBuilder as generic
class.
1. public class ModelMetadataBuilder<T>
2. {
3.
4. public ModelMetadata Metadata { get; } = new ModelMetadata();
5.
6. public void InitFromProperties()
7. {
8. Type type = typeof(T);
9.
10. foreach (var p in type.GetProperties())
11. {
12. Metadata.Properties.Add(CreateForPropertyInfo(p));
13. }
14. }
15.
16. private PropertyMetadata CreateForPropertyInfo(PropertyInfo propertyInfo) => new PropertyMetadata
17. {
18. Property = propertyInfo,
19. PropertyName = propertyInfo.Name,
20. DisplayName = propertyInfo.Name
21. };
22.
23. }
First implementation introduces method InitFromProperties.
This method creates PropertyMetadata for all properties. Let us look
at code snippet.
1. [TestClass]
2. public class ModelMetadataBuilderTests
3. {
4. [TestMethod]
5. public void InitFromPropertiesTest()
6. {
7. ModelMetadataBuilder<Person> modelMetadataBuilder = new ModelMetadataBuilder<Person>();
8. modelMetadataBuilder.InitFromProperties();
9.
10. Person person = new Person
11. {
12. DateOfBirth = new DateTime(1983, 12, 26),
13. FirstName = "Jon",
14. LastName = "Snow"
15. };
16.
17. ModelMetadata metadata = modelMetadataBuilder.Metadata;
18. var lastNamePropertyMetadata = metadata.Properties
19. .First(x => x.PropertyName == "LastName");
20.
21. string lastName = (string)lastNamePropertyMetadata.GetValue(person);
22. Assert.AreEqual("Snow", lastName);
23. }
24. }
All proerties where read and putted to PropertyMetadata objects.
It is much simpler to create metadata now.
PropertyMetadataBuilder
Go to next issue. We want to make some property to be ignored.
We can use the following code:
1. var lastNamePropertyMetadata = metadata.Properties
2. .First(x => x.PropertyName == "LastName");
3. lastNamePropertyMetadata.IsIgnored = true;
But we can simplify it. We will create another builder class
for properties, that will help to modify PropertyMetadata.
1. public class PropertyMetadataBuilder<TParent>
2. {
3.
4. #region Properties
5.
6. public PropertyMetadata PropertyMetadata { get; }
7.
8. #endregion
9.
10. #region Constructors
11.
12. public PropertyMetadataBuilder(PropertyMetadata propertyMetadata)
13. {
14. PropertyMetadata = propertyMetadata ?? throw new ArgumentNullException(nameof(propertyMetadata));
15. }
16.
17. #endregion
18.
19. #region Methods
20.
21. public PropertyMetadataBuilder<TParent> HasColumnName(string name)
22. {
23. PropertyMetadata.DisplayName = name ?? throw new ArgumentNullException(nameof(name));
24.
25. return this;
26. }
27.
28. public PropertyMetadataBuilder<TParent> HasProvider(Func<TParent, object> converter)
29. {
30. PropertyMetadata.ValueProvider = (arg) => converter((TParent)arg);
31.
32. return this;
33. }
34.
35. public PropertyMetadataBuilder<TParent> HasOrder(int order)
36. {
37. PropertyMetadata.Order = order;
38.
39. return this;
40. }
41.
42. public PropertyMetadataBuilder<TParent> Ignore()
43. {
44. PropertyMetadata.IsIgnored = true;
45.
46. return this;
47. }
48.
49. #endregion
50.
51. }
First, it is generic class. Generic argument is the same as ModelMetadataBuilder generic argument.
It holds PropertyMetadata object and allow to do the
following actions:
HasColumnName. Set display name for property
HasProvider. Set value provider for property. Because
Builder is generic class, it does cast operation inside method, and we do not
need to perform this casting.
HasOrder. Set order for property.
Ignore. Set IsIgnored to true.
Each method returns this, it is called Fluent
Interface and simplify call for chain of methods.
ModelMetadataBuilder modification
Now, add extra methods to ModelMetadataBuilder to simplify getting
PropertyMetatdataBuilders.
1. public PropertyMetadataBuilder<T> Property<TProperty>(Expression<Func<T, TProperty>> propertyExpression)
2. {
3. if (propertyExpression.Body is MemberExpression me)
4. {
5. return ResolvePropertyBuilder(me.Member.Name);
6. }
7. throw new ArgumentException("Invalid property expression", nameof(propertyExpression));
8. }
9.
10. public PropertyMetadataBuilder<T> Property(string propertyName) =>
11. ResolvePropertyBuilder(propertyName);
12.
13. private PropertyMetadataBuilder<T> ResolvePropertyBuilder(string propertyName)
14. {
15. if (!_columnBuildersCache.TryGetValue(propertyName, out PropertyMetadataBuilder<T> columnMetadataBuilder))
16. {
17. var propertyMetadata = Metadata.Properties.FirstOrDefault(x => x.PropertyName == propertyName);
18. if (propertyMetadata == null)
19. {
20. propertyMetadata = new PropertyMetadata
21. {
22. PropertyName = propertyName,
23. DisplayName = propertyName
24. };
25. Metadata.Properties.Add(propertyMetadata);
26. }
27.
28. columnMetadataBuilder = new PropertyMetadataBuilder<T>(propertyMetadata);
29. _columnBuildersCache.Add(propertyName,
columnMetadataBuilder);
30. }
31.
32. return columnMetadataBuilder;
33. }
Property(string) method will return
PropertyMetadataBuilder for property with provided name.
Property(Expression)
method will get property name from LambdaExpression and return
PropretyMetadataBuilder for this name. This way more convenient to use when we
know type of model, because we will have IntelliSense in this case.
1. [TestMethod]
2. public void PropertyMetadataBuilderResolvingTest()
3. {
4. ModelMetadataBuilder<Person> modelMetadataBuilder = new ModelMetadataBuilder<Person>();
5. modelMetadataBuilder.InitFromProperties();
6.
7. Person person = new Person
8. {
9. DateOfBirth = new DateTime(1983, 12, 26),
10. FirstName = "Jon",
11. LastName = "Snow"
12. };
13.
14. ModelMetadata metadata = modelMetadataBuilder.Metadata;
15. var lastNamePropertyMetadata = modelMetadataBuilder
16. .Property(x => x.LastName)
17. .PropertyMetadata;
18.
19. string lastName = (string)lastNamePropertyMetadata.GetValue(person);
20. Assert.AreEqual("Snow", lastName);
21. }
I think that is all about preparing data to export. Now let us
move to last step – Export to Excel
Export to Excel
We built metadata for model. Now we want to use it in export
process.
Excel can have multiple sheets with different data. So, we
create information about sheet that needs to be generated.
1. public class ExportSheetInfo
2. {
3.
4. public string Header { get; set; }
5. public IEnumerable Items { get; set; }
6. public ModelMetadata Metadata { get; set; }
7.
8. }
Header. Will be used as name of sheet.
Items. Items to export. Not generic, because metadata
is not generic.
Metadata. Metadata for items.
Information for exporting is ready, let us create actual
exporter. I will use NPOI library to generate excel. You can use library you
like, just follow this approach of reading data.
NpoiExcelExporter
1. public class NpoiExcelDataExporter : IExcelExporter
2. {
3.
4. public void ExportToStream(IEnumerable<ExportSheetInfo> exportSheets, Stream stream)
5. {
6. XSSFWorkbook workbook = new XSSFWorkbook();
7.
8. foreach (ExportSheetInfo exportSheetInfo in exportSheets)
9. {
10. ExportSheet(workbook, exportSheetInfo);
11. }
12.
13. workbook.Write(stream, true);
14. }
15.
16. private static void ExportSheet(IWorkbook workbook, ExportSheetInfo sheetInfo)
17. {
18. ISheet sheet = workbook.CreateSheet(sheetInfo.Header);
19.
20. List<PropertyMetadata> propertiesToExport = sheetInfo.Metadata
21. .EnumeratePropertiesToExport()
22. .ToList();
23.
24. FillHeaderRow(sheet, propertiesToExport);
25.
26. int rowIndex = 1;
27. foreach (object item in sheetInfo.Items)
28. {
29. FillItem(sheet, propertiesToExport, item, rowIndex++);
30. }
31. }
32.
33. private static void FillHeaderRow(ISheet sheet, IList<PropertyMetadata> propertiesToExport)
34. {
35. IRow headerRow = sheet.CreateRow(0);
36.
37. for (int i = 0; i < propertiesToExport.Count; i++)
38. {
39. string propertyName = propertiesToExport[i].DisplayName;
40.
41. ICell cell = headerRow.CreateCell(i);
42. cell.SetCellValue(propertyName);
43. }
44. }
45.
46. private static void FillItem(
47. ISheet sheet,
48. IList<PropertyMetadata> propertiesToExport,
49. object item,
50. int rowIndex)
51. {
52. if (item is null)
53. {
54. throw new ArgumentNullException(nameof(item));
55. }
56.
57. IRow row = sheet.CreateRow(rowIndex);
58.
59. for (int i = 0; i < propertiesToExport.Count; i++)
60. {
61. PropertyMetadata property = propertiesToExport[i];
62.
63. ICell cell = row.CreateCell(i);
64.
65. object value = property.GetValue(item);
66. if (value != null)
67. {
68. cell.SetCellValue(value.ToString());
69. }
70. }
71. }
72.
73. }
To export we will need to provide ExportSheetInfo items and
stream where to save result of exporting.
Please, look at FillHeader and FillItem methods. We can see
how to use ModelMetada.
Export sample
Now, let us export a list of persons. We would like to
export Name (First + Last) and Birthday in format May 29.
We export only 2 properties, and all of them needs to have
own Value Provider, Name property does not exist in Person class. Here is code
snippet:
1. public void ExportTest()
2. {
3. //Prepare data
4. Person[] people = new[]
5. {
6. new Person
7. {
8. DateOfBirth = new DateTime(1983, 12, 26),
9. FirstName = "Jon",
10. LastName = "Snow"
11. },
12. new Person
13. {
14. DateOfBirth = new DateTime(1986, 10, 23),
15. FirstName = "Daenerys",
16. LastName = "Targaryen"
17. }
18. };
19.
20. //Prepare metadata
21. ModelMetadataBuilder<Person> personMetadataBuilder = new ModelMetadataBuilder<Person>();
22.
23. personMetadataBuilder.Property("Name")
24. .HasProvider(x => x.FirstName + " " + x.LastName);
25.
26. personMetadataBuilder.Property(x => x.DateOfBirth)
27. .HasColumnName("Birthday")
28. .HasProvider(x => x.DateOfBirth?.ToString("MMMM dd"));
29.
30. //Export
31. ExportSheetInfo peopleSheet = new ExportSheetInfo
32. {
33. Header = "People",
34. Items = people,
35. Metadata = personMetadataBuilder.Metadata
36. };
37. ExportSheetInfo[] sheets = new[] { peopleSheet };
38.
39. NpoiExcelDataExporter exporter = new NpoiExcelDataExporter();
40. using (FileStream fs = File.OpenWrite(@"D:\Persons.xlsx"))
41. {
42. exporter.ExportToStream(sheets, fs);
43. }
44. }
And here is result:
Thank you for reading and hope it was useful for you :-).
Please check out code at GitHub:
First of all, great job. Thanks for your work.
ReplyDeleteI suggest a small change in the ExportSheetInfo class to write a line of text before data (for example, the current date and time, or a title)
public class ExportSheetInfo
{
public string Header { get; set; }
public string Title { get; set; } = string.Empty;
public IEnumerable Items { get; set; }
public ModelMetadata Metadata { get; set; }
}
The FillHeaderRow class would look like this:
private static void FillHeaderRow(ISheet sheet, IDictionary styles, string title, IList propertiesToExport)
{
int rowIndex = 0;
if (!string.IsNullOrEmpty(title))
{
IRow tittleRow = sheet.CreateRow(0);
ICell titleCell = tittleRow.CreateCell(0);
titleCell.CellStyle = styles[HeaderCellStyleKey];
titleCell.SetCellValue(title);
rowIndex = 2;
}
IRow headerRow = sheet.CreateRow(rowIndex);
for (int i = 0; i < propertiesToExport.Count; i++)
{
string propertyName = propertiesToExport[i].DisplayName;
ICell cell = headerRow.CreateCell(i);
cell.CellStyle = styles[HeaderCellStyleKey];
cell.SetCellValue(propertyName);
}
}
And ExportSheet class:
private static void ExportSheet(IWorkbook workbook, IDictionary styles, ExportSheetInfo sheetInfo)
{
ISheet sheet = workbook.CreateSheet(sheetInfo.Header);
List propertiesToExport = sheetInfo.Metadata
.EnumeratePropertiesToExport()
.ToList();
FillHeaderRow(sheet, styles, sheetInfo.Title, propertiesToExport);
int rowIndex = 1;
if (!string.IsNullOrEmpty(sheetInfo.Title))
{
rowIndex = 3;
}
foreach (object item in sheetInfo.Items)
{
FillItem(sheet, styles, propertiesToExport, item, rowIndex++);
}
for (int i = 0; i < propertiesToExport.Count; i++)
{
sheet.AutoSizeColumn(i);
}
}