Different Formats In Report Generation Using JSReport Server

The JSReport Server supports different types of formats in report generation, like pdf, xlsx, html, text, html to xlsx etc. In previous articles, the report was generated in PDF format. This is a continuation of my previous article with other types of formats, like XLS for generating reports.

For more information of how to useJSReport, read the below article.

• Generate Reports Using JSReport and Browser Client

Before implementing the below sample, please make sure that JSReport Server and node.js are up and running in your computer.

Report in Excel format: (using handlebars engine)

Create a new template with “Excel Sample” and JSReport Studio.

Create an empty JSON file in ‘data +’ section and name it “Excel Sample”.

Now, create an empty spreadsheet with required title, header, and images as shown below.


Save the above file in your machine.

Now, go to JSReport Studio, under ‘xlsxTemplates +’ section, and upload the above created empty spreadsheet.

Highlight the empty template and set few properties as shown in the below figure.

In order to generate the XLS report, the above properties are mandatory.

To show the report, we need some sample JSON data in “Excel Sample.JSON” file. Use the below sample data and save the JSON file in report server. 

{
    "deptInfo": {
        "deptNumber": 101,
        "deptName": "Engineering",
        "deptBuilding": "A-Block",
        "empCount": "A"
    },
    "empDetails": {
        "titles": [{
            "title": "Employee Name"
        }, {
            "title": "Designation"
        }, {
            "title": "Experience"
        }, {
            "title": "Remarks"
        }],
        "values": [{
                "name": "Suresh",
                "designation": "Senior QA Engineer",
                "exp": "5 yrs",
                "remarks": "Selenium, Manual Automation."
            },
            {
                "name": "Raghava",
                "designation": "Senior Software Engineer",
                "exp": "6 yrs",
                "remarks": "C#.Net, Asp.Net Core WebApi"
            },
            {
                "name": "Kiran Kumar",
                "designation": "Technical Lead",
                "exp": "8 yrs",
                "remarks": "Scrum master, .Net Framework, Agile Develpment"
            },
            {
                "name": "Prameela",
                "designation": "Trainee Engineer",
                "exp": "1.2 yrs",
                "remarks": "C#.Net, Sql Server 2008"
            }
        ]
    }
}

So far, we have created sample data, and a sample spread sheet. Now, build the actual XLS template with handlebars syntax. This is used to generate the Excel report on Server.

Write the following code in template section.

{{#xlsxAdd "xl/worksheets/sheet1.xml" "worksheet.sheetData[0].row"}}
<row r="3">
    <c t="inlineStr" s="{{@root.$removedItem.c.[0].$.s}}"><is><t>{{deptInfo.deptNumber}}</t></is></c>
    <c t="inlineStr" s="{{@root.$removedItem.c.[1].$.s}}"><is><t>{{deptInfo.deptName}}</t></is></c>
    <c t="inlineStr" s="{{@root.$removedItem.c.[2].$.s}}"><is><t>{{deptInfo.deptBuilding}}</t></is></c>
    <c t="inlineStr" s="{{@root.$removedItem.c.[3].$.s}}"><is><t>{{deptInfo.empCount}}</t></is></c>
</row>
{{/xlsxAdd}}

{{#xlsxAdd "xl/worksheets/sheet1.xml" "worksheet.sheetData[0].row"}}
<row r="5">
    {{#each empDetails.titles}}
        <c t="inlineStr" s="4"><is><t>{{title}}</t></is></c>
    {{/each}}
</row>
{{/xlsxAdd}}

{{#each empDetails.values}}
{{#xlsxAdd "xl/worksheets/sheet1.xml" "worksheet.sheetData[0].row"}}
<row r="{{#getIndex 6 @index}}{{/getIndex}}">
    <c t="inlineStr"><is><t>{{name}}</t></is></c>
    <c t="inlineStr"><is><t>{{designation}}</t></is></c>
    <c t="inlineStr"><is><t>{{exp}}</t></is></c>
    <c t="inlineStr"><is><t>{{remarks}}</t></is></c>
</row>
{{/xlsxAdd}}
{{/each}}

{{{xlsxPrint}}}

In order to generate the number of rows, we need to use the following custom script. This “getIndex()” is used in the above <row> tag.

function getIndex(val1, val2){
    return val1 + val2;
}

After completing the above steps, SAVE or SAVE ALL the template and click “Run” button in JSReport Studio to display the xlsx report on the screen. The output should look like the below screen.

Attributes in Excel template design:

In the above template,

• ‘r’ indicates row and the row number started with 3 because the uploaded design template occupies the first 2 rows for header section with image and title.
• ‘c’ indicates column in a row.
• ‘t’ indicates type, which means the type of value contained in the column cell.
• ‘s’ indicates style, initially the uploaded template ( Which is appear in xlsxTemplates section) has some set of predefined styles. Which can be applied to the template with index value starting from ‘0’ or can be specified with removed item in a row.
• {{xlsxPrint}} is a mandatory tag for excel and should be added at end of the template in order to print the excel report from server.

Customizing the report with Drilldown/Collapsible Section:

• There could be some scenarios where we need to display the report in collapsible/drilldown. For this XLS provides a special attribute “outlineLevel”. If this is set to “1” in each row like below the data can be viewable as collapsible/expanded section. After adding this attribute the section should look like below screen.
  

  {{#each empDetails.values}}
  {{#xlsxAdd "xl/worksheets/sheet1.xml" "worksheet.sheetData[0].row"}}
  <row r="{{#getIndex 6 @index}}{{/getIndex}}" outlineLevel="1">
      <c t="inlineStr"><is><t>{{name}}</t></is></c>
      <c t="inlineStr"><is><t>{{designation}}</t></is></c>
      <c t="inlineStr"><is><t>{{exp}}</t></is></c>
      <c t="inlineStr"><is><t>{{remarks}}</t></is></c>
  </row>
  {{/xlsxAdd}}
  {{/each}}

• Now, re-run the jsreport studio and the output will display with grouping icons ( + collapsible mode, - expanded mode) as below.
  
  
  Fig: Expanded Mode
  
  
  Fig: Collapsible Mode

Applying own style formats to the report

So far we have worked with predefined styles in the XLS template. But there are some instance where it requires our formatted styles. This can be achieved by modifying ‘s’ attribute values.

In order to apply the custom styles, go to the server installed path and look for content.txt file.

Write down some own custom styles and combine them into the existing styles in the jsreport server folder.

For example, assume that your jsreport server is running in D:\jsreportserver path then excel template styles are located in D:\jsreportserver\data\xlsxTemplates\<templateName> path with 3 files like below,


This content.TXT file is in JSON format. So, open content.txt file in any text editor and copy the contents to any JSON formatter like jsonlint. But, internally this txt file uses “open XML” patterns. For more information on how to use open XML, please use the link http://officeopenxml.com/SSstyles.php . After applying the custom styles in the page, save the page and restart the jsreport server.

Now, run the above report in the jsreport studio.

The sample custom style should look like below,

"cellXfs": [{
    "$": {
        "count": "20"
    },
    "xf": [{
        "$": {
            "numFmtId": "0",
            "fontId": "0",
            "fillId": "0",
            "borderId": "1",
            "xfId": "0"
        }
    },{
        "$": {
            "numFmtId": "164",
            "fontId": "4",
            "fillId": "2",
            "borderId": "1",
            "xfId": "0",
            "applyNumberFormat": "1",
            "applyFont": "1",
            "applyFill": "1",
            "applyBorder": "1",
            "applyAlignment": "1"
        },
        "alignment": [{
            "$": {
                "horizontal": "center",
                "vertical": "top",
                "wrapText": "1",
                "readingOrder": "1"
            }
        }]
    }
    ....
    ....
    ....
]