Advanced table cells formatting

   

Skip to end of metadata

• Created by Natalie Paramonova, last modified by Katerina Rudkovskaya on Oct 18, 2021

Go to start of metadata

• General information
• Use cases
  • Changing alignment
  • Changing column width
  • Adding line breaks
  • Adding text color
  • Text formatting
  • Escaping special characters for wiki markup
  • Adding bullet, numbered list
  • Adding background, text color
  • Conditional inserting of values and coloring cells background
  • Conditional formatting
  • Conditional inserting of statuses
    • Adding lighter status color
  • Creating clickable links 
  • Preserving initial cell formatting 

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.

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:

   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.

---

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:

   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.

---

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:

   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

   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.

 

---

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:

   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.

---

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:

   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.

---

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:

   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.

---

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:

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

   or

   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.

---

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:

   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

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

   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.

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:

   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.

---

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:

   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.

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:

   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.
   
   

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

 

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:

   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.

---

Creating clickable links 

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:

   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.

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

---

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:

   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.

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