Generate Reports from CSV Data Source

Another monthly release of GroupDocs.Assembly for .NET is onboard as v19.10. This release contains a bug fix as well as three new features related to CSV, JSON, and XML data sources. So in this article, I will give you an overview of how to use CSV data sources for generating your reports. Also, I’ll demonstrate the simplified ways of using JSON and XML data sources.

Working with CSV Data Source

The Comma Separated Values (CSV) is a popular file format for keeping the data in the form of plain text. As the name suggests, all the values in a CSV file are separated by commas and it is widely used for exchanging data among different applications. We have added the support of CSV to be used as a data source and now you can use it for populating the document templates.

To access CSV data while building reports, you can create and pass a CsvDataSource instance to the DocumentAssembler as a data source. Although CSV as a format does not define a way to store values of types other than strings, CsvDataSource is capable of recognizing values of the following types by their string representations:

  • Int32?
  • Int64?
  • Double?
  • Boolean?
  • DateTime?

Let’s have a look at how a CSV data source will be used in the source code. An important point to be noted here is that in template documents a CsvDataSource instance should be treated in the same way as if it was a DataTable instance. The following are the sample CSV, a template and the code to populate the template.

CSV

John Doe,30,1989-04-01 4:00:00 pm
Jane Doe,27,1992-01-31 07:00:00 am
John Smith,51,1968-03-08 1:00:00 pm

Template

<<foreach [in persons]>>Name: <<[Column1]>>, Age: <<[Column2]>>, Date
of Birth: <<[Column3]:"dd.MM.yyyy">>
<</foreach>>
Average age: <<[persons.Average(p => p.Column2)]>>

Code

The following is the report that will be generated as a result.

Name: John Doe, Age: 30, Date of Birth: 01.04.1989
Name: Jane Doe, Age: 27, Date of Birth: 31.01.1992
Name: John Smith, Age: 51, Date of Birth: 08.03.1968
Average age: 36

In this example, we have used Column1, Column2 and so on for the column names in the template. However, if the first row in the CSV file contains the column names, you can configure CsvDataSource to treat the first row as column names. The CSV, template, and code, in this case, would be the following:

CSV

Name,Age,Birth
John Doe,30,1989-04-01 4:00:00 pm
Jane Doe,27,1992-01-31 07:00:00 am
John Smith,51,1968-03-08 1:00:00 pm

Template

<<foreach [in persons]>>Name: <<[Name]>>, Age: <<[Age]>>, Date of
Birth: <<[Birth]:"dd.MM.yyyy">>
<</foreach>>
Average age: <<[persons.Average(p => p.Age)]>>

Code

Working with JSON Data Sources

In the latest release, we have simplified the ways of working with JSON data sources. To access JSON data while building a report, you can pass a JsonDataSource instance to the assembler as a data source to work with typed values of JSON elements in the template. Have a look at the following scenarios:

1. If a top-level JSON element is an array or an object having only one property of an array type.

In such cases, a JsonDataSource instance should be treated in the same way as if it was a DataTable instance. This is how you would populate templates in such scenarios.

JSON

[ 
   { 
      Name:"John Doe",
      Age:30,
      Birth:"1989-04-01 4:00:00 pm"
   },
   { 
      Name:"Jane Doe",
      Age:27,
      Birth:"1992-01-31 07:00:00 am"
   },
   { 
      Name:"John Smith",
      Age:51,
      Birth:"1968-03-08 1:00:00 pm"
   }
]

Template

<<foreach [in persons]>>Name: <<[Name]>>, Age: <<[Age]>>, Date of Birth: <<[Birth]:"dd.MM.yyyy">>
<</foreach>>
Average age: <<[persons.Average(p => p.Age)]>>

Code

2. If a top-level JSON element represents an object.

In this case, a JsonDataSource instance should be treated in template documents in the same way as if it was a DataRow instance.

JSON

{ 
   Name:"John Doe",
   Age:30,
   Birth:"1989-04-01 4:00:00 pm",
   Child:[ 
      "Ann Doe",
      "Charles Doe"
   ]
}

Template

Name: <<[Name]>>, Age: <<[Age]>>, Date of Birth:
<<[Birth]:"dd.MM.yyyy">>
Children:
<<foreach [in Child]>><<[Child_Text]>>
<</foreach>>

Code

Working with XML Data Sources

We have also simplified the way of working with XML data sources. XmlDataSource has been introduced that works even when XML schema is not provided. The XmlDataSource is capable of recognizing values of the following types by their string representations:

  • Int32?
  • Int64?
  • Double?
  • Boolean?
  • DateTime?

Similar to the JSON data sources, the following scenarios deal with the XML data sources:

1. If a top-level XML element contains only a sequence of elements of the same type.

In this case, an XmlDataSource instance should be treated in the same way as if it was a DataTable instance.

XML

<Persons>
   <Person>
      <Name>John Doe</Name>
      <Age>30</Age>
      <Birth>1989-04-01 4:00:00 pm</Birth>
   </Person>
   <Person>
      <Name>Jane Doe</Name>
      <Age>27</Age>
      <Birth>1992-01-31 07:00:00 am</Birth>
   </Person>
   <Person>
      <Name>John Smith</Name>
      <Age>51</Age>
      <Birth>1968-03-08 1:00:00 pm</Birth>
   </Person>
</Persons>

Template

<<foreach [in persons]>>Name: <<[Name]>>, Age: <<[Age]>>, Date ofBirth: 
<<[Birth]:"dd.MM.yyyy">>
<</foreach>>
Average age: <<[persons.Average(p => p.Age)]>>

Code

2. If a top-level XML element contains attributes or nested elements of different types.

In this scenario, an XmlDataSource instance should be treated in template documents in the same way as if it was a DataRow instance.

XML

<Person>
   <Name>John Doe</Name>
   <Age>30</Age>
   <Birth>1989-04-01 4:00:00 pm</Birth>
   <Child>Ann Doe</Child>
   <Child>Charles Doe</Child>
</Person>

Template

Name: <<[Name]>>, Age: <<[Age]>>, Date of Birth:
<<[Birth]:"dd.MM.yyyy">>
Children:
<<foreach [in Child]>><<[Child_Text]>>
<</foreach>>

Code

For more details on how to use CSV, JSON, and XML data sources, please visit this documentation article. Try out all these features at your end by downloading or cloning the examples project from GitHub. If you find anything confusing for you, do let us know via our forum.