Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Table of Contents

General information

The Table Transformer macro supports Confluence Wiki Markup (the FORMATWIKI function) and Markdown syntax (the FORMATMARKDOWN function) for the purposes of cell formatting.

Info

These functions don't support formatting of complex macros that dynamically change the content, get data from someplace ( e.g User List Macro, Expand Macro etc.)

Below you can find how Table Transformer helps to enhance the formatting abilities of tables in Confluence.



Use cases


Changing alignment

Use case: 

You need to align text in some columns of the table.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.
  5. Enter the following SQL query:

    Code Block
    languagesql
    SELECT 
    FORMATWIKI("{cell:vertical-align=bottom}" + T1.'Bill to' + "{cell}") AS 'Bill to',
    FORMATWIKI("{cell:align=right}" + T1.'Invoice'+ "{cell}") AS 'Invoices',
    T1.'Date',
    T1.'Product',
    T1.'Price'
    FROM T1


  6. Click Next

  7. Define the table settings and view options if needed. 
  8. Save the macro and the page.


Handy Carousel



Changing column width

Use case: 

You need to change columns width in the merged table.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.
  5. Enter the following SQL query:

    Code Block
    languagesql
    SELECT *,
    FORMATWIKI("{cell:width=100px}" + 'Ticket'+ "{cell}") AS 'Ticket',
    FORMATWIKI("{cell:width=150px}" + 'Description'+ "{cell}") AS 'Description'
    FROM T*


  6. Click Next

  7. Define the table settings and view options if needed. 
  8. Save the macro and the page.


Handy Carousel



Adding line breaks

Use case: 

You need to add a line break.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.
  5. Enter the following SQL query:

    Code Block
    languagesql
    SELECT 
    FORMATWIKI(T1.'Name'+ " \n" + T1.'Surname') AS 'Representative 1',
    FORMATMARKDOWN(T1.'Name'+ " \n" + T1.'Surname') AS 'Representative 2',
    FORMATWIKI(T1.'Name'+ " \\ " + T1.'Surname') AS 'Representative 3'
    FROM T1


    Info

    Three alternative ways of adding line breaks are shown in the SQL query.


  6. Click Next

  7. Define the table settings and view options if needed. 
  8. Save the macro and the page.




Handy Carousel



Adding text color

Use case: 

You need to merge tables, create a new column with the sum of two others, and then highlight and align text.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.
  5. Enter the following SQL query:

    Code Block
    languagesql
    SELECT T1.'Name', T1.'Income 2019', T2.'Income 2020', 
    FORMATWIKI("{cell:textColor=red|align=right}" 
    + ( T1.'Income 2019' + T2.'Income 2020') + "{cell}") 
    AS 'Total Income' 
    FROM T1 OUTER JOIN T2 ON T1.'Name' = T2.'Name'


  6. Click Next
  7. Define the table settings and view options if needed. 
  8. Save the macro and the page.


Handy Carousel



Text formatting

Use case: 

You need to format some text.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.
  5. Enter the following SQL query:

    Code Block
    languagesql
    SELECT *, 
    FORMATWIKI("* plain text \n
    				* _italics_ \n 
    				* *bold* \n 
    				* **italics bold** \n 
    				* text~with subscript~ \n 
    				* -strikethrough- \n
    				* text with^superscript^ \n
    				* {{monospaced}} \n 
    				* +inserted text+ \n
    				* {noformat}no format{noformat}
    				\nbq. Here's how you make a paragraph appear as a block quotation")
     AS 'Wiki Markup' FROM T*


  6. Click Next
  7. Define the table settings and view options if needed. 
  8. Save the macro and the page.


Handy Carousel



Escaping special characters for wiki markup

Use case: 

You need to escape special characters for wiki markup.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.
  5. Enter the following SQL query:

    Code Block
    languagesql
    SELECT *,
    FORMATWIKI(T1.'Description') AS 'FORMATWIKI',
    FORMATWIKI("*"+ESCAPEMARKUP(T1.'Description')+"*") AS 'FORMATWIKI+ESCAPEMARKUP'
    FROM T1


  6. Click Next
  7. Define the table settings and view options if needed. 
  8. Save the macro and the page.


Handy Carousel



Adding bullet, numbered list

Use case: 

You need to merge tables and apply bullet or numberd list to merged data.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.
  5. Enter the following SQL query:

    Code Block
    languagesql
    SELECT 'Date', 
    FORMATWIKI(SUM("* " + 'Notes' + "\n")) AS 'All Notes' 
    FROM (SELECT * FROM T*)
    GROUP BY 'Date' ORDER BY 'Date'

    or

    Code Block
    languagesql
    SELECT 'Date',
    FORMATWIKI(SUM("# " + 'Notes' +"\n")) AS 'All Notes'
    FROM (SELECT * FROM T*)
    GROUP BY 'Date' 
    ORDER BY 'Date'


  6. Click Next
  7. Define the table settings and view options if needed. 
  8. Save the macro and the page.


Handy Carousel



Adding background, text color

Use case: 

You need to add background and text color.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.
  5. Enter the following SQL query:

    Code Block
    languagesql
    SELECT 
    FORMATWIKI("{cell:bgColor=yellow}" + T1.'Rep' + "{cell}") AS 'Rep',
    FORMATWIKI("{cell:bgColor=#FFE5B4}" + T1.'Department'+ "{cell}") AS 'Department',
    FORMATWIKI("{cell:bgColor=orange|textColor=yellow|align=center|vertical-align=bottom|width=500px}" 
    + T1.'City'+ "{cell}") AS 'City',
    FORMATWIKI("{panel:bgColor=orange|textColor=white}" + T1.'City'+ "{panel}") AS 'City 1',
    FORMATWIKI("{color:orange}" + T1.'City' + "{color}") AS 'City 2'
    FROM T1


    Info

    You can write both a color name and a color hex name from this list and your own HEX code in the SQL query.


    Info

    Keep the following order of attribues: bgColor, textColor, align, width.


  6. Click Next
  7. Define the table settings and view options if needed. 
  8. Save the macro and the page.


Handy Carousel

Hex codes:


Conditional inserting of values and coloring cells background

Use case: 

You need to add values in a newly created column and color its cells through conditions.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.
  5. Enter the following SQL query:

    Code Block
    languagesql
    SELECT T1.'Employees',
            CASE 
               WHEN T1.'Job Title'="Developer"
               THEN FORMATWIKI("{cell:bgColor=#0099FF}Dev{cell}")
               WHEN T1.'Job Title'="Product Analyst" 
               THEN FORMATWIKI("{cell:bgColor=#00CC00}BI{cell}")
               ELSE FORMATWIKI("{cell:bgColor=grey}Others{cell}")
            END AS 'Team'
     FROM  T1
     ORDER BY 'Team'


  6. Click Next
  7. Define the table settings and view options if needed. 
  8. Save the macro and the page.


Handy Carousel



Conditional formatting

Use case: 

You need to add conditional formatting of table data.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.
  5. Enter the following SQL query:

    Code Block
    languagesql
    SELECT
    T1.'Month',
    FORMATWIKI("{cell:bgColor=" + 
    	CASE WHEN T1.'Bread' <= "100" THEN "#FA7E70"
    		 WHEN T1.'Bread' > "100" AND T1.'Bread' <= "200" THEN "#FFE784"
    		 ELSE "#8FCA7D"
    	END
    	+ "}" + "$" + T1.'Bread' + "{cell}")
    AS 'Bread',
    FORMATWIKI("{cell:bgColor=" + 
    	CASE WHEN T1.'Rolls' <= "100" THEN "#FA7E70"
    		 WHEN T1.'Rolls' > "100" AND T1.'Rolls' <= "200" THEN "#FFE784"
    		 ELSE "#8FCA7D"
    	END
    	+ "}" + "$" + T1.'Rolls' + "{cell}")
    AS 'Rolls',
    FORMATWIKI("{cell:bgColor=" + 
    	CASE WHEN T1.'Cookies' <= "100" THEN "#FA7E70"
    		 WHEN T1.'Cookies' > "100" AND T1.'Cookies' <= "200" THEN "#FFE784"
    		 ELSE "#8FCA7D"
    	END
    	+ "}" + "$" + T1.'Cookies' + "{cell}")
    AS 'Cookies',
    FORMATWIKI("{cell:bgColor=" + 
    	CASE WHEN T1.'Muffins' <= "100" THEN "#FA7E70"
    		 WHEN T1.'Muffins' > "100" AND T1.'Muffins' <= "200" THEN "#FFE784"
    		 ELSE "#8FCA7D"
    	END
    	+ "}" + "$" + T1.'Muffins' + "{cell}")
    AS 'Muffins'
    FROM T*


  6. Click Next
  7. Define the table settings and view options if needed. 
  8. Save the macro and the page.


Handy Carousel

Hex codes:



Conditional inserting of statuses

Use case: 

You need to insert statuses depending on some condition.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.
  5. Enter the following SQL query:

    Code Block
    languagesql
    SELECT *,
    CASE 
    WHEN 'Employees' < 60 
    THEN FORMATWIKI("{status:colour=Green|title=Low}")
    WHEN 'Employees' >= 60 AND 'Employees' < 150 
    THEN FORMATWIKI("{status:colour=Yellow|title=Medium}")
    ELSE FORMATWIKI("{status:colour=Red|title=High}")
    END AS 'Status'
    FROM T1


  6. Click Next
  7. Define the table settings and view options if needed. 
  8. Save the macro and the page.

Info

You can also use an alternative way of inserting statuses in a table.



Handy Carousel


Adding lighter status color

Use case: 

You need to choose a lighter status color.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Status macro and paste the table or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.
  5. Enter the following SQL query:

    Code Block
    languagesql
    SELECT *,
    CASE
    WHEN 'Employees' < 60
    THEN FORMATWIKI("{status:colour=Green|subtle=true|title=Low}")
    WHEN 'Employees' >= 60 AND 'Employees' < 150
    THEN FORMATWIKI("{status:colour=Yellow|subtle=true|title=Medium}")
    ELSE FORMATWIKI("{status:colour=Red|subtle=true|title=High}")
    END AS 'Status'
    FROM T1


  6. Click Next
  7. Define the table settings and view options if needed. 
  8. Save the macro and the page.


Handy Carousel



Use case: 

You need to create clickable links from multiple text pieces put together.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.
  5. Enter the following SQL query:

    Code Block
    languagesql
    SELECT T1.'Name', T1.'Phone Number', T1.'City',
    FORMATWIKI("[" + CONCAT("https://", T1.'City', ".mycompany.com/", T2.'Folder') + "]") AS 'Workspace',
    FORMATWIKI("[The link|" + CONCAT("https://", T1.'City', ".mycompany.com/", T2.'Folder') + "]") 
    AS 'Workspace 2'
    FROM T1 OUTER JOIN T2 ON T1.'Name' = T2.'Name'


  6. Click Next
  7. Define the table settings and view options if needed. 
  8. Save the macro and the page.

Info

Here's the example on how to create them to the JIRA issues.



Handy Carousel



Preserving initial cell formatting 

Use case: 

You need to preserve the initial formatting and apply a new one.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.
  5. Enter the following SQL query:

    Code Block
    languagesql
    SELECT 'Week',
    FORMATWIKI("{cell:textColor=green|align=left|width=200px}",
    'Plan for Weekly Progress', "{cell}") AS 'Weekly plan'
    FROM T*


  6. Click Next
  7. Define the table settings and view options if needed. 
  8. Save the macro and the page.


Info

Preserving initial cell formatting doesn't work in case of rows concatenation.



Handy Carousel