Dynamic tabular data report in ASP.Net

Question

I am working on an MVC5 web application where I need to generate a report from any database table. The user will select the table he wants to print, then choose which columns to include in the report and the system should simply display a grid (table) with the data.

Displaying the data in a HTML table is of course not a problem and I have also managed to generate Excel files with the data, that the user can download.

However I also need to provide a printable form of the report such as PDF or some other format that doesn't need special software (such as MS Office) to open and print.

At first I thought I should go for the ReportViewer control, but the problem is that my report is dynamic since the user selects the columns he needs. I have seen attempts from others to generate the report definition file, but I was hoping for something less ugly.

Is there a way to take a HTML table, which calculates the sizing of the cells nicely, and create a PDF from it? I don't mind of course splitting the report horizontally in multiple pages if the columns are too many.

Answer 1

You could try Spartacus.

It's a relatively new .NET library, totally written in C#. It can connect to many different databases and generate reports in Excel and PDF.

I uploaded 3 files in Google Drive:

• Spartacus.dll
• template.xml
• report.pdf

In order to use Spartacus, you need to reference System.Data and System.Xml packages, as well as Spartacus.dll.

In the following example, I created report.pdf from template.xml in simple steps:

Spartacus.Database.Generic v_database;
Spartacus.Reporting.Report v_report;
System.Data.DataTable v_table;

v_database = new Spartacus.Database.Postgresql("127.0.0.1", "database", "postgres", "password");

v_table = v_database.Query(
    "select 'Example of Report made with Spartacus' as title, " +
    "       product, " +
    "       description, " +
    "       unit, " +
    "       quantity, " +
    "       total_cost, " +
    "       unit_cost " +
    "from table", "REPORT");

v_report = new Spartacus.Reporting.Report(1, "template.xml", v_table);
v_report.Execute();
v_report.Save("report.pdf");

Note that you don't need to use Spartacus.Database objects. If you can get a System.Data.DataTable by other means, then you can pass it to the Report object.

However, there is a catch. As you can see in the XML template, for each column, you need to know:

• Column name (obviously)
• Title
• Align (LEFT, RIGHT or CENTER)
• Fill (percent, 100 being the total width of the page minus width of the margins)
• Type (INTEGER, REAL, BOOLEAN, CHAR, DATE or STRING)

Fill and Type are essential, and you may need to keep information about all your columns. If this is too hard to achieve, and you can only get column name and type, you can compute a aproximation based only on type, like the following:

• product is of type STRING, default fill = 30
• description is of type STRING, default fill = 30
• unit is of type STRING, default fill = 30
• quantity is of type REAL, default fill = 15
• total_cost is of type REAL, default fill = 15
• unit_cost is of type REAL, default fill = 15

The sum of all default fill are 30+30+30+15+15+15 = 135, greater than 100. So you can normalize to 100, this way:

• Column fill = Column default fill * (sum of default fills / 100)

After that, you will need to generate dynamic template.xml files with dynamic field information.

DISCLAIMER: I'm Spartacus' creator and maintainer.