A common requirement for us is exporting data to excel or CSV files. While it is not too daunting of a task, the frequency of it prompted me to look at a more concise way of writing it.
Based on the established practices we first get all the data we need to export into some form of IEnumerable and then have a function to export it to the desired target.
My previous approach was to use one of our writer classes (ie BufferedExcelWriter or CSVWriter to write each field one by one. This is quick to write and looks like this:
So, this is not too difficult to implement but you end up with many lines and the code for the headers is separate from that of the data in the table. This can lead to a bit more complication in the event we need to rearrange, add/remove columns since it becomes quite easy to accidentally misalign columns.
I thought it would be nice if instead, we could just have a single call that includes defining the columns and what data they should have rather than directly iterating through them. This may not be the best way to go in all cases – you are omitting looping in favour of just using a single function to get each value, but for our exports, it usually satisfies our requirements.
Say we have the following model class:
class TestData
{
public string Name { get; set; }
public string Description { get; set; }
public decimal Amount { get; set; }
public TestData(string name, string description, decimal amount)
{
Name = name;
Description = description;
Amount = amount;
}
} We want to export all 3 properties per line.
The old approach would look like this (for Excel):
BufferedExcelWriter writer = new BufferedExcelWriter();
writer.AddSheet("Sheet1");
writer.Write("Name");
writer.Write("Description");
writer.WriteLine("Amount");
writer.WriteLine();
foreach (TestData data in testData)
{
writer.Write(data.Name);
writer.Write(data.Description);
writer.Write(data.Amount);
}
writer.Save("New File.xlsx"); This works and gives us what we want but can get tedious.
The new approach makes use of some new things in HuLib, namely ExportTableBuilder:
BufferedExcelWriter writer = new BufferedExcelWriter();
writer.AddSheet("Sheet1");
writer.WriteTable(testData, new ExportTableBuilder()
.AddColumn("Name", d => d.Name)
.AddColumn("Description", d => d.Description)
.AddColumn("Amount", d => d.Amount));
writer.Save("New File.xlsx"); Now we have all of the writing content to the file down to a single call! You can see that it is very clear which header corresponds to which value, and it is impossible to misalign the header to the value.
Seeing that this would reduce the number of calls for generating content to 1 in most cases I took it one step further and added extensions to CSV and Excel to export to a file directly:
testData.ToExcel("New File.xlsx", new ExportTableBuilder()
.AddColumn("Name", d => d.Name)
.AddColumn("Description", d => d.Description)
.AddColumn("Amount", d => d.Amount)); Now you can get a list of objects into a CSV / Excel file without worrying about which class to use.
Note: there is currently no support for formatting (in Excel)
Functions of note:
// Some list of whatever model you want
List testData = new List()
{
new TestData("Apple", "A fruit", 30),
new TestData("Banana", "Also a fruit", 22),
new TestData("Flamingo", "Not a fruit", 4),
};
// Table definition
ExportTableBuilder builder = new ExportTableBuilder()
.AddColumn("Name", d => d.Name)
.AddColumn("Description", d => d.Description)
.AddColumn("Amount", d => d.Amount);
// Export to Excel
Excel.Export(testData, "New File.xlsx", builder);
testData.ToExcel("New File.xlsx", builder); // This and the line above are equivalent
// Export to CSV
CSV.Export(testData, "New File.csv", builder);
testData.ToCSV("New File.csv", builder); // This and the line above are equivalent These functions are made available via an extension to the IWriter interface which both BufferedExcelWriter and CSVWriter now implement. If you want to enable the table export to another type of writer just implement IWriter and you will have access to the WriteTable functionality.
Just added in the latest preview 1.0.7 build is the ability to infer the header name based on the property or field. If no header is specified and there is only an expression, the header will be set to the provided property or field’s display name attribute (if it exists) or its own name.
class TestData
{
[DisplayName("Name2")]
public string Name { get; set; }
public string Description { get; set; }
public decimal Amount { get; set; }
}
ExportTableBuilder<TestData> builder = new ExportTableBuilder<TestData>()
.AddColumn(d => d.Name) // Header will be Name2
.AddColumn(d => d.Description) // Header will be Description
.AddColumn("Amount2", d => d.Amount); // Header will be Amount2 This is just a convenience addition that does not add more functionality but can be more convenient in some cases.