Overview

The Source Table:

The Result:

Create the Office Excel macro   to display the content of an Excel spreadsheet on a page  and configure its parameters.

Table Filter & Office Excel

1. Add the Table Filter macro to the Office Excel macro. 

Table Filter helps you to filter data, set default sorting, freeze rows and columns, calculate totals and more.

2.  Add filters to the table columns and set filter values.

Default sorting in the Table Filter macro can't be applied as Sorting in the header row doesn't work with tables outputted by the Office Excel macro in Confluence by default.

Pivot Table & Office Excel

3.   Add the Pivot Table  to the Office Excel macro with filtered data.

Pivot Table allows you to summarize and aggregate your table data.
You can select the following operations: Sum, Count, Min, Max, Average, Literal.

4.  Configure the pivot table.

Table Transformer & Office Excel

5. Add the Table Transformer macro to the filtered and aggregated Excel table. 

You can place  one or multiple tables within the macro body to merge, look up them or to perform custom calculations.

6. Select one of the presets or switch to the SQL query tab.


7. Enter the following SQL query to rename columns in the pivot table:

SELECT T1.'Product',                                     
T1.'Sum of Discounts' AS 'Discounts', 
T1.'Sum of Profit' AS 'Profit'
FROM T1
CODE

Chart from Table & Office Excel

8. Add the Chart from Table macro to the already filtered, aggregated and transformed Excel table to visualize data with the help of charts and graphs.

9. Set the parameters of the chart .


You can change the filtration criteria, the pivot table and chart parameters on the fly.
 The changes will be automatically applied to the data from the Office Excel macro.


The Office Excel macro can be used with each of the macros separately or combined with them according to your needs.