Writing IEnumerable data to CSV/Excel as a table (HuLib 1.0.7 feature)

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 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 (e.g., BufferedExcelWriter or CSVWriter) to write each field one by one. This is quick to write and looks like this:

• Write each header one by one with .Write("header name")
• Loop through rows of the IEnumerable
• For each of the columns, write the value
• Write a newline

This is not too difficult to implement, but you end up with many lines, and the code for the headers is separate from the data. This can lead to complications if you need to rearrange, add, or remove columns, as it becomes easy to accidentally misalign them.

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 favor of just using a single function to get each value—but for our exports, it usually satisfies our requirements.

Example Model Class

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 three properties per line.

The Old Approach

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 with ExportTableBuilder

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. It is very clear which header corresponds to which value, and it is impossible to misalign the header to the value.

Seeing that this reduces the number of calls for generating content to one 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 or 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> testData = new List<TestData>()
{
	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 table export to another type of writer, just implement IWriter and you will have access to the WriteTable functionality.

Extra – Naming Based on Property

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.