Export To Excel (using Template)

Export to Excel (Using Template)

Export to Excel (Using Template) is a comprehensive and efficient feature used to generate reports with complex formatting. Sometimes it is difficult or tedious to generate reports with complex formats using Export to Excel (Normal) or Export to Sheets functions. In such cases, Template feature is used.

Example

Template feature can be explained better with an example. Consider that the requirement is to generate the below report.

Report Requirements: This sample report contains two sheets: Equity Scheme, Debt Scheme. Both sheets have similar formats. In these sheets, the first table (A4:N23) contains Month-wise Security details whereas the second table (A25:C29) contains Plan-wise NAV details for the current month.

  • This is a monthly report. The number of months in 6th row is not fixed. If there is a new month data, two additional columns (Units, Mkt Value) along with the Month header should get added to the first table.

  • Similarly, the number of Securities is also not fixed (A8:A22). There can be security addition or deletion. So, the first table should be able to vary dynamically (in horizontal and vertical direction) based on the data.

  • In the second table, the number of plans (A27:A29) can increase or decrease. So, the table 2 should be able to vary dynamically (in vertical direction)

  • In the NAV column of the second table, the number of digits shown after the decimal point should be 3 decimals for Equity Scheme and 4 decimals for Debt Scheme.

  • There should be one line gap between the first table and the second table. All other number formatting, color formatting should be replicated in SheetKraft output report.

Implementation:

Generating this example report using Export to Excel (Normal) and Export to Sheets would be a tedious process. In this case, Export to Excel (Using Template) feature can be used.

  • The first step is to create a Template sheet in your implementation workbook. Copy a sheet from the sample report and paste it in the implementation workbook with exact formatting, row & column sizes etc.

  • Next step is to prepare the data required for generating the report. To generate this sample report, two datasets are required as shown below. The first data range contains Security-wise values which is required for generating the first table in the sample report (A4:N23). The second data range contains Plan-wise NAV values which is required for generating the second table (A25:C29).

Note: The sorting of the data in the data range is very important when using the Template feature. Reports are generated based on this sorting order. For example, in the first data range mentioned above, for a Security, the Jan month values should always come before Feb and succeeding months values. Similarly, Feb month values should come before March and succeeding months values and so on.

Select the first required data range now (here the entire first data range is selected) and click on Export button in SheetKraft Ribbon of Excel. Click on “Export To Excel” from the list of options. Click “OK” in the Export To Excel dialog box that pops up after that.

  • Export To Excel UI: The UI of “Export To Excel” feature would be visible. Under “Export Specifications” section, select the “Using a Template” option. After that, click on the ellipsis icon in the File path section. A dialog box pops up asking for output file path and file name. Choose a path, file name and click on Save button.

  • Since the sample report we need to generate has multiple sheets i.e. Equity Scheme and Debt Scheme Sheets, select the “Multiple Sheets” option under “Export Specifications” section. After that, from the “Partition the data by” drop-down list, select the column of the first data range which contains the desired sheet names i.e. the Scheme column. For this to work, the first data range should contain the data of both Equity and Debt Schemes.
    Also, if the generated excel file needs to be password protected, type the password as a double quoted string in the “Password” text box or give reference to an Excel cell which contains the password.

Visualization of Template Sections/Blocks:

  • The next step is to setup the template and map data with different sections of the template. To do that, the template must be visualized into different blocks/sections based on the report format.

  • The A2:N2 range contains the Scheme Name. It is a merged cell. The Scheme Name here changes when the sheet changes.

  • The A4 and A25 cells are similar kind of the cells. These cells contain date for which the report is generated. The date in these cells change based on the input date provided by the user.

  • In the C6:N6 range, month merged cell is being repeated horizontally. Based on the Scheme-wise month list in the data, the months should get added or deleted. In this C6:N6 range, C6:D6 can be considered as a base range/unit. The E6:N6 cells have similar format and similar kind of data in it.

  • Similarly, in the C7:N7 range, C7:D7 can be considered as base range/unit. The E7:N7 range is just repeating (rightwards) the same headers and format seen in C7:D7 range.

  • In the range A8:N22, the A8:N8 can be considered as the base range/unit. The A9:N22 range is just repeating (downwards) the same kind of data and formatting based on the Security Name.

  • Within this A8:N8 range, consider C8:N8 range. In this range, C8:D8 can be considered as base range/unit and the E8:N8 range is just repeating the same data (Units, Market Value) and same formatting (rightwards) based on the Month.

  • In the C23:N23 range of the Grand Total row, C23:D23 can be considered as base range/unit and the E23:N23 range is just repeating the same data (Sum of Units, Sum of Market Value) and same formatting (rightwards) based on the Month.

  • In the A27:C29 range of the second table, A27:C27 can be considered as base range/unit and the A28:C29 range is just repeating the same data (Date, NAV) and same formatting (downwards) based on the Scheme Plan.


  • Visualizing the entire template into different sections/blocks like this helps in setting up the template through the Export to Excel’s UI.


Setting up the Template:

  • Now that the entire template sheet is visualized in the form of blocks/sections, we can setup the template in UI now.

  • Without the setting up anything the “Template” section in the UI would look like the pic shown below. Now click on the range icon in the “Template range” section. Select the range containing the data and formatting from the Template sheet and click on OK.

  • Template Range: A template is basically a sample - a cell range which acts as a blueprint for the final report. This cell range should be specified in the Template range. Think of the sections/blocks visualized in the Template sheet earlier. Each of these sections/blocks can be considered as different templates. These different sections/blocks have different template ranges. The goal is to create such sections/blocks and map the data to each section/block in the UI.

  • Here in the pic above, only the range (A1:N29) is selected as Template range because the cells outside this range are all blank cells with no formatting at all. This A1:N29 range would serve as the parent template/block/section in the UI.

  • Now go to the Repeats section and click on the plus icon. A “Container” element would get added to the Repeats section. Now, click on the Ellipsis button of this newly added container to go into the container section of the UI.

  • Container: The container helps us to separate out data area in the template sheet. With this, we can create a particular section/block for particular data. It is used to various inner areas of template based on the data and format similarity.

  • After clicking on the ellipsis button next to the Container, Container section will be visible. Here select the template range that should be grouped under one container. This will be “Full Range” of a container. In the example template, A4:N23 and A25:N29 contain different kind of data. So, these ranges should be grouped under different containers.

  • For this section, data range section, the Security-wise data in the setup workbook should be given as the data source.

  • In Template section of the container we created, give the same range (A4:N23) as the Template range.

  • Now go back to the upper most Template by clicking on it in the Navigation pane. Create another container for A25:A29 range of the Template sheet. Repeat the similar steps. For this container, the Full/Template range would be A25:N29 and the data range would be the Plan-wise data in the setup workbook. Once this done click on “Next” and paste the formulas in a blank range. This would save the two containers we just created.

  • Click on ‘Formula Explorer’ button in SheetKraft add-in ribbon, to re-open the Template UI again.

  • Now the range A1:N2 in the template sheet does not belong to the two containers created earlier. It only belongs to the uppermost/outermost template with the template range A1:N29. This A1:N2 contains Scheme Name which should change based on the sheet we are in. Now we should map the Scheme Name column in the data range with the A1:N2 range.

  • First, click on the plus button in “Template Cells”. From the drop-down list, choose Scheme column. Click on the range icon after that and select the A1:N2 range in Template sheet since it’s a merged cell.

  • Whenever merged cell included in “Template Cells” section, it should also be included in “Anchors” section. Click on the plus sign in Anchors section and select the same A1:N2 range there. Since the Scheme Name merged cell should expand/contract in horizontal direction based on the number months in a Scheme, check the Left and Right boxes.

  • Template Cells: These are used to map a column in the data range to a cell/cell range in the template. In the “Report Column”, custom values are also supported. This feature is helpful when we need to use SUM(), MIN() and other functions on a column in the data range. It is also useful to refer a cell outside the data range of the template.

  • Anchors: Anchors are used to fix template cell’s position relative to the template. Checking the direction indicates that the number of cells between that edge of the template and the selected anchor range will not change. In this example, it was used to create to horizontally expanding Scheme Name merge cell.

  • Now, in the first container created, we should map some data with template cells. Here we should map the data to A4 Cell (“Data as on…”) and the C5:N5 (“Month-wise Data”) merged cell. Since the data range does not contain the data required for these cells, custom “Report Column” are used to map data with the cell range. Since the C5:N5 (“Month-wise Data”) should also expand/contract horizontally based on the number of months, Anchor is used for that range with Left and Right directions checked.

  • Now with in the first container, create a RepeatRigtht element for the Month Merged cell range C6:N6, here the same kind of data and format in C6:D6 merged cell is being repeated in E6:N6 range as well. This repetition is in horizontal direction and the repeated data is the month here. In such cases, RepeatRight should be used. A Repeat element can be created by creating a new container and choosing the direction either Down or Right.

  • Repeats:

  • This allows for containers for repeating elements to be made if a template contains recurring patterns. The normal containers have the Direction option selected as ‘None’. Once the direction of container is changed to Down or Right, then the container would be changed to a RepeatDown or RepeatRight element.

  • The “Full Range” (C6:N6 here) includes all the repetitions of same pattern. The “Template Range” (C6:D6 here) of the Repeat element is the first occurring pattern in the Full Range. The Full Range excluding the “Template Range” is the “Repeat Range”. The “Partition By” denotes the column which is the basis for these repeating patterns/elements.

Note: The “Template Range” should always be a subset of “Full Range” in Containers/RepeatDowns/RepeatRights. Also, in the Repeats the “Repeated Range” always inherit cell formatting, borders, colors, width/height from the “Template Range”.

  • In the template of the RepeatRight element, map the columns in the data range with cell ranges using Template Cells. Since C6:D6 is merged cell, Anchors are also used here. Here Top and Down anchors are used because we know that it will not expand horizontally because there are only two kinds of values in a month i.e. Units and Market Value.

  • Like the RepeatRight created now, create another two RepeatRight elements for Column Names range (C7:N7) and Grad Total values (C23:N23) range in the first Container (C4:N23).

  • Now consider the A8:N22 range in the Template sheet. In this range, the A9:N22 range is just repeating (downwards) the same kind of data and formatting present in A8:N8 range. In this downward repeating range values depend upon the Security Name.

  • Within the A8:N8 range, the C8:N8 range is a horizontally repeating range with E8:N8 repeating the same kind of data and format present in C8:D8 range based on the Month.

  • This means the entire A8:N22 range can be represented as a RepeatRight block nested within a RepeatDown block.

  • Create a RepeatDown range in the first container with Full Range (A8:N22), Repeating Range (A9:N22), Partition By (Security Name) and Template Range (A8:N8).

  • Nested within this Template (A8:N8), create a RepeatRight with Full Range (C8:N8), Repeating Range (E8:N8), Partition By (Month) and Template Range (C8:D8). Click on OK to save these newly created Repeats.

  • Now first container’s data mapping is done. Now open the Second Container, here tick the Conditions checkbox. In this sample report, since there are multiple sheets generated based on the Scheme Name. To make sure that both the Container 1 and Container 2 show the data of the same scheme, the data ranges both these containers should be linked by a common column (Scheme Name column here). Fill the non-repeating template cells (A25 here) in the container.

  • In the second Container, the A27:C29 is a downward repeating range based on the Scheme Plan. The same kind of data and formatting present in the A27:C27 range is being repeated in the A28:C29 range.

  • A RepeatDown with Full Range (A27:C29), Repeating Range (A28:C29), Partition By (Scheme Plan) and Template Range (A27:C27) should be created.

  • NAV Precision based conditional formatting should be applied for the C27 cell in the Template Range. After that click on OK to save the changes made to the template and run the workbook to generate the output report.

  • Conditional Cell Formats:

  • Conditional Cell Formats feature is used to override the default format of a “Template cell” with the format present in the “Format Source” range based on a “Condition”. In the pic above, if NAV Precision (Data2 F column) for a Scheme Plan is 3, the format in the Data2 sheet J6 cell (3 decimals number format) will be applied to the NAV column values (C27:C29 range in the sample template).

  • The Conditional Cell Formats is especially useful in Repeats. In Repeats, the format of the “Repeated Range” is always overridden by the format of “Template Range”. If a formatting which is different from the “Template Range” format is required in “Repeated Range”, then Conditional Cell Formats can be used.

  • Note: We have not used any template cells to fill cells/cell ranges like A5:A7 (“Security Name” Header), B5:B7 (“ISIN Code” Header), A23:AB23 (“Grand Total” Footer), A26 (“Scheme Plan” Header), B26 (“Date” Header), C26 (“NAV” Header). All these cells/cell ranges have static values which are same across all the sheets.

Template feature extracts the data and formatting of these static cell/ranges without explicitly specifying. So, these ranges are not included/mapped in the template setup.

Conclusion

  • Export To Excel (Using Template) feature is useful in generating heavily formatted/dynamic report which are very tedious to create using Export To Excel (Normal) and Export To Sheet features.

  • Before setting up any template using the UI, it is imperative to visualize the sample report into different sections/blocks like Static Values, Non-Repeating Template Cells, Horizontally Repeating Ranges, Vertically Repeating Ranges etc.

  • Once the report is visualized into different sections, a sample template with all the formatting should be included in one of the sheets of implementation workbook. This sheet should then be used as the base for setting up the template in UI.

Additional Notes and Tips:

1. Whenever a new Container/RepeatDown/RepeatRight element gets created, it always gets generated along with a Template element. The “Template Range” of this Template element should not exceed the “Full Range” of the Container/RepeatDown/RepeatRight.

Similarly, all the inner “Template Ranges” and “Full Ranges” of a Template should not exceed the “Template Range” of the topmost/outermost template.

If this condition is not met, the below errors occur when creating the template.

2. In the template sheet, there should not be any merged cells in a “Repeating Range”.

If this condition is not followed, an error like this might occur. So unmerge this cells in the Template sheet.

3. Whenever a Template Range is left empty, the below error occurs.

4. For concise and quick reference, go to the link: https://www.sheetkraft.com/blog/export-to-excel-using-template/