Generic Way To Export Data To Any Textformat In C#

Introduction

Let us consider this very common scenario where you have an application used by a multitude of customers. This application generates invoices and each customer wants to export these invoices and import them into their accounting.

They all need a different format and none is the same. Some need CSV, others XML and others yet another format, and they all expect a different structure.

In this article, I will show you a way to export the data (invoices in my example) using only very little source code and without the need to do any extra coding whenever a new export is required.

I repeat NO CODING!

The secret is called Razor and a small library called RazorEngine from Antaris. RazorEngine brings the razor technology -as we know it- from asp.net MVC to your C# application. It is designed to generate HTML but you will be amazed how well it performs to generate any text-based output.

Base mechanism

Take a look at a very basic usage of RazorEngine (Copied from the RazorEngine project on Github)

using RazorEngine;
using RazorEngine.Templating; // For extension methods.
string template = "Hello @Model.Name, welcome to RazorEngine!";
var result =
Engine.Razor.RunCompile(template, "templateKey", null, new { Name = "World" });

The variable 'result' will contain "Hello World, welcome to RazorEngine!". You basically provide a template and a model (as we do all the time in MVC) and the engine gives a string as a result. Feel it coming?

The export program

The actual export program is only a small extension to the code found here above (under Base mechanism)

public void ExportSalesInvoices(List<SalesInvoice> SalesInvoices)
{
string SalesInvoiceTemplateList=@"c:\templates\template1.txt,c:\templates\template2.txt"; //Just as an example ...
int counter = 1;
foreach (string templateFileName in SalesInvoiceTemplateList.Split(new[]{','}))
{
if (string.IsNullOrEmpty(templateFileName))
continue;
string template = File.ReadAllText(templateFileName);
string templateKey = Guid.NewGuid().ToString();
var result = Engine.Razor.RunCompile(template, templateKey, null, SalesInvoices);
string fileName = GetFileName(@"c:\exported invoices\", $"-{counter++}");
File.WriteAllText(fileName, result);
}
}

This is all the code your application requires.

Some explanation

The templates are stored in a textfile on disk (line 11) but they can also come from a database of a web service, etc ...

The output is also stored on disk (line 15) but again, you can store it wherever you want.

The template key is a GUID that is generated each time the export is executed (line 12). This way the template is compiled each time, which is not very efficient so there is definitely room for improvement. If you want to run the export with the same template on different instances of the model, you could do a RunCompile() the first time and a Run() subsequently.

The invoice example

Here is the model for the invoice export example.

public class SalesInvoice
{
public int SalesInvoiceId { get; set; }
public int InvoiceNo { get; set; }
[ForeignKey("Customer")]
public int CustomerId { get; set; }
public Customer Customer { get; set; }
public DateTime InvoiceDate { get; set; }
public DateTime? InvoiceDueDate { get; set; }
public List<SalesInvoiceDetail> SalesInvoiceDetails { get; set; }
}

As you can see, very straight forward, nothing special. The model for Customer and SalesInvoiceDetail is left out to keep the listed code limited.

A CSV export template example:

@model List<ShopPro.MasterData.SalesInvoice>
type,id,name,contact,language,currid,category,paydelay,paymode,vattype,vatnumber,vatcountry,vatid,vatpc,centaccid,partaccid,agent,adrcountry,adrzip,adrcity,adrstreet1,adrstreet2,phone1,phone2,fax,email,bank1,bank2,blocked,creditmax
@foreach(var customer in Model.Select(x=>x.Customer).Distinct())
{
<text>"C","@customer.CompanyId","@customer.Name","@customer.ContactName","@(customer.Language!=null ? customer.Language.LanguageCode : "")","@(customer.Currency!=null ? customer.Currency.CurrencyCode : "")","CL","",""," ","@customer.VatNumber","@(customer.Country!=null ? customer.Country.CountryCode : "")","",0.00,"","","","@(customer.Country!=null ? customer.Country.CountryCode : "")","@customer.Zip","@customer.City","@customer.Address","","@customer.Phone","","@customer.Fax","@customer.Email","","",F,0.00</text>
}
@foreach(var invoice in Model)
{
<text>@invoice.InvoiceNo;@invoice.InvoiceDate; ... etc ...</text>
}

If you are familiar with MVC and razor, you will immediatly recognize what's happening here.

Line1: defines the model for the template: a LIst<SalesInvoice>

Line2: Exports the first line of the CSV with the fieldnamelist

Line3: A list of all distinct customers is retrieved and used as a source for the foreach

Line5: export of the customer info

Lines7- export the invoices themselves, 1 line for each invoice. Probably this part will be put in a separate template and output but for the example customers and invoices are exported in the same output.

An XML export template example

The XML sample template is somewhat more complicated:

@model List<ShopPro.MasterData.SalesInvoice>
@{
var culture=new System.Globalization.CultureInfo("en-us");
string mask="0.00";
}
<text>
<?xml version="1.0" encoding="UTF-8" ?>
<Invoices></text>
@foreach(var invoice in Model)
{
<text>
<Invoice>
<No>@invoice.InvoiceNo</No>
<InvoiceDate>@invoice.InvoiceDate</InvoiceDate>
<CustomerId>@invoice.CustomerId</CustomerId>
<AccountSummaries>
</text>
foreach (var accountSummary in invoice.SalesInvoiceDetails.GroupBy(x => x.AccountNumber).Select(x => new { AccountNumber = x.Key, TotalBase = x.Sum(y => y.TotalBase), TotalVatInc = x.Sum(y => y.TotalVatInc) }))
{
<text>
<AccountSummary>
<AccountNumber>@accountSummary.AccountNumber</AccountNumber>
<TotalBase>@accountSummary.TotalBase.ToString(mask, culture)</TotalBase>
<TotalVatInc>@accountSummary.TotalVatInc.ToString(mask, culture)</TotalVatInc>
</AccountSummary>
</text>
}
<text>
</AccountSummaries>
</Invoice>
</text>
}
<text>
</Invoices>
</text>

Line1: defines he model for the template: a List<SalesInvoice>

Line2-5 : defines the culture and mask to convert the amounts to strings

Line7: the xml starts

Line9: loop through the invoices in the model

Line12: xml for the invoice header

Line18: calculate totals per Account and vatrate (found in the SalesInvoiceDetails) and loop through those totals

Line21: xml for the account summary

Line29: rest of the xml

Isn't that superpowerful, flexible and elegant?

Conclusion

This razor based export has a lot of advantages:

powerfull - it can handle virtually any scenario,whether your data need to be grouped, sorted, filtered or aggregated, the razor export can take care of it
flexibel - smaller and big changes can be made without recompiling your application. The export has a small bug, just change the template and you're done!
elegant - you don't have to learn a new language (if you know razor)
Easy - a template can be modified, simple ones even be created by someone that isn't a developer.

Some Background

In the first version of my application to export its invoices, I rapidly noticed, this would be a never-ending story, hardcoding the export every time a new customer needed his very own export.

In my search of finding a generic way to export the invoices my application generates, I considered XSLT which is a transformation language that transforms XML into any format. The drawback of this solution was that it was based on XML and more importantly, I needed to learn yet another language (xslt).

So I continued searching and at a certain moment, I realized that razor did exactly what I was looking for with a language I already knew and with very powerful possibilities.