Atlassian Confluence

Manage Tables in Confluence with Ease

Are you an accountant, statistician or analyst? So you should know MS Excel from A to Z. But what if your company migrates to Confluence, how are you going to deal with? Have the same question, no worry – we will give you the answers.

Using Standard Tools

Confluence is bundled with a visual editor that allows you to insert tables the way similar to MS Word.

confluence_visual_editor_tables

Just select the appropriate number of rows and columns and here’s your table. By holding the Shift key, you can skip addition of the table header. Now you can start entering your data, but it will make much time of yours for this.

confluence_visual_editor_insert_table

Confluence allows you to perform the following actions:

  • Inserting a row before or after and removing the row
  • Inserting a column before or after and removing the column
  • Cutting, copying or pasting a table row
  • Merging or splitting table cells
  • Setting heading rows or columns
  • Coloring table cells

Manual entry of the table is quite a time-consuming procedure and you will quickly understand it once you start migration of one-thousand-row table.

Importing from Excel

If you already have a table in MS Excel, you can just copy-paste it into Confluence.

conlfuence_insert_excel_table

In this case the formatting of the table will be reset, and you will get a plain table with data.

conlfuence_insert_excel_rendered

But a large table with data is useless if you cannot filter it or find the necessary value in a blink of an eye. From the box, Confluence does not provide functionality to filter tables with data, but you can always install the Table Filter add-on and apply filtration to your large table.

Inserting Tables from Internet

Confluence processes correctly tables inserted from the Internet. You can just select the appropriate table on the web-page, copy it and then insert it onto the necessary page.

This is an easy way, but what if you have HTML code of your table. It is not a problem, just add the HTML macro and insert the code of your table into it. and that’s all.

confluence_html_table_code_insert

But it is not very convenient to edit such a table, as anyway you will have to deal with with the code.

Importing CSV Tables

Another solution for working with tables is Advanced Tables for Confluence developed by Bob Swift. It provides you with a complex solution to manage your tables and get data for its population.

It provides a set of macros to work with tables and generate them from eternal sources.

  • CSV macro allows you to import, format and display comma-separated values (CSV) data from external sources.
  • Table Plus macro allows you to use advanced functions and apply special formatting to Confluence tables.
  • Attachment Table macro allows you to generate a table with attachments based on the selected criteria.
  • JSON Table macro allows you to import, format and display tables fetched from JSON string from external sources.

The most easy and convenient way is to have a table in MS Excel, which you can further save to a CSV file and attach to the appropriate Confluence page. You can work with this table in Excel and export to CSV when you need it to show in Confluence. Each newly uploaded CSV file will be stored as a new revision, so all the data will be automatically updated after each update.

Just enter {CSV} macro on your Confluence page and set the appropriate macro parameters. You can select the table format (HTML or wiki), show a heading row, enter the table columns to show in the table, modify the table column titles, select the appropriate delimiter. Then you can define whether you want to use the attached file or file stored on some external resource, in this case, you have to enter a URL and credentials if needed. Optionally, you can set the appropriate text format, enable column sorting or row highlighting when pointing to it, add automatic row numeration, configure the sorting parameters, use column calculation, and configure the size of the table.

confluence_csv_table_insert

This macro has a lot of parameters that allow you to thoroughly adjust display of the table. Once you save the page, you can proceed to work with this table.

confluence_csv_table_rendered

Working with Databases

Confluence can be integrated with external servers running the database. This can be achieved by means of the following add-ons:

They provide the similar level of functionality and allow you to establish connection to external databases, such as MSSQL, Oracle, DB2, PostgreSQL, and MySQL. Connection is configured in the dedicated section of the add-on and requires entry of the database type, name, credentials and path to the JDBC driver.

confluence_sql_database_configuration

You can create multiple data source profiles that you can use for fetching data from multiple sources. While adding a {SQL} macro to Confluence pages, you need to write SQL queries for sampling and outputting data matching your request. You can write complex SQL queries that can calculate, combine, filter and do a lot of other things. This gives you unlimited freedom in showing table data in your Confluence.

conlfuence_sql_macro_with_request

When you save the page, you will get your table with data from the database. This data will automatically update when you reload the page as the production databases get new data all the time.

conlfuence_sql_macro_table_rendered

Easy Filtration of Data in Your Tables

If you get a table, a really large table, with thousands of rows – you get another problem how to limit all the shown information and get a piece of data, which you are actually interested at the moment. If you use data from some external database, you can always write a query to sample the necessary data. But it is quite inconvenient when you need to vary the data all the time, and constant rewriting of your SQL queries will drive your crazy. This solution will be also unacceptable for standard data tables or HTML tables.

Is there any solution? Yes, there is – Table Filter for Confluence developed by StiltSoft. This add-on allows you to insert your tables and tables generated by other add-ons or macros into its macro body and apply filters on the fly.

confluence_table_filter_data_filtration

You can select the columns that will get drop-down filters and text filters, enable a global filter for search across the entire table. Additionally, you can set the width for filters, apply default values to filters, regulate the number of rows to shows and hide labels or filters. Once you save the page, get a pane with filters for filtering your table data.

confluence_sql_table_rendered_data_filtration

The add-on supports both Hosted and Cloud versions of Confluece.

Visualizing Table Data with Charts and Graphs

When your tables abound with numbers, you may find it useful to visualize your charts and see the spread of values on the chart. Here comes the chart macro which allows you to generate charts of different types. You can choose among pie, bar, line, area, scatter, time series, gantt and other charts. Additionally, you can show 3D view of charts if applicable, define width and height of your charts, enable output of the legend and configure a lot of other options.

confluence_chart_macro_configuration

Once you save the page, Confluence will render your chart and show it to you on the page.

confluence_chart_macro_rendered

You can also insert macros that output the table with numbers into the chart macro. This allows you to get charts automatically updated if any changes occur in the data source.

Conclusions

Out-of-the-box Confuence cannot provide the table management capabilities exceeding those ones available in MS Excel. Anyway you can find a lot of useful add-ons in the Atlassian Marketplace. They allow you to get many more features that you can use while working with tables, for example filtering large tables, loading data from external sources or performing automatic manipulations with your table data.

 

Recommended Articles