It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

A User Macro to Create a Table From an Attached CSV File Part One

User macros are pretty great. There are so many awesome things that you can accomplish with them. This is a user macro I wrote a few years ago to take a CSV file attached to a page and render it out as a table.

If you would like to know more about how this user macro works check out part 2 of this article. I had to split the article into two parts because there is a 20,000 character limit. Specifically, in part two I go into a bit of detail how I got a file attachment drop down in a user macro. "Say what! That's not possible!"

Oh, but it is. :)

Update:
01/09/2020:Updated to make the table sortable.

User Macro Code

Macro Name: csv_table

Macro Title: CSV Table

Description: This will take an attached CSV file and render it as a table.

Categories: Confluence Content, Formatting

Macro Body Processing: No macro body

Template:

## Developed by: Davin Studer
## Date created: 05/07/2014
## @param CSVName:title=CSV File|type=string|required=false|desc=The CSV file
## @param TopHeader:title=Top Header Row|type=boolean|required=false|default=false|desc=Check if the top row should be a header row?
## @param Page:title=Page Title|type=confluence-content|required=false|desc=If not specified, the current page is used.

###########################################################################
## These are used for getting around velocity issues when writing jQuery ##
###########################################################################
#set( $d = '$' )
#set( $p = '#' )

##############################
## Create a unique id value ##
##############################
#set( $id=$action.dateFormatter.calendar.timeInMillis )

#if( $paramPage && $paramPage != "" )
    ##########################
    ## Get the page manager ##
    ##########################
    #set( $containerManagerClass=$content.class.forName('com.atlassian.spring.container.ContainerManager') )
    #set( $getInstanceMethod=$containerManagerClass.getDeclaredMethod('getInstance',null) )
    #set( $containerManager=$getInstanceMethod.invoke(null,null) )
    #set( $containerContext=$containerManager.containerContext )
    #set( $pageManager=$containerContext.getComponent('pageManager') )

    ##########################
    ## Find the page        ##
    ##########################
    #set( $parts = $paramPage.split(":") )
    #set( $i = 0 )
    #set( $len = 0 )
    #set( $key = "" )
    #set( $name = "" )

    ##Having trouble finding out the length/size of $parts ... brute force it
    #foreach( $part in $parts )
        #set( $len = $len + 1 )
    #end
    
    #if( $len == 1 )
        #set( $key = $content.spaceKey )
        #set( $name = $paramPage )
    #else
        #foreach( $part in $parts )
            #if( $i == 0 )
              #set( $key = $part )
              #set( $i = $i + 1 )
           #else
              #set( $name = $part )
           #end
        #end
    #end

    #set( $tempcontent = "" )

    #if( $pageManager )
        #set( $tempcontent = $pageManager.getPage($key, $name) )
    #end

    #if( $tempcontent && $tempcontent != "" )
        #set( $content = $tempcontent )
    #else
        The page "$paramPage" was not found in this space.
        #set( $content = "" )
    #end
#end

#set( $attachments = $attachmentManager.getLatestVersionsOfAttachments($content) )

############################################################################
## Could not find a method to sort the $attachments in Alphabetical order ##
## Must sort them myself ... bubble sort                                  ##
############################################################################
#set( $size = $attachments.size() )
#foreach( $junk in $attachments )
    #set( $count = -1 )
    #foreach( $line in $attachments )
        #set( $count = $count + 1 )
        #if( $velocityCount < $size )
            #if( $line.getTitle().compareToIgnoreCase($attachments.get($velocityCount).getTitle()) > 0 )
                #set( $tmp = $attachments.get($velocityCount) )
                #set( $junk = $attachments.set($velocityCount, $line) )
                #set( $junk = $attachments.set($count, $tmp) )
            #end
        #end
    #end
#end

#set( $baseUrl = $action.getGlobalSettings().getBaseUrl() )

#foreach( $attachment in $attachments )
    #if( $attachment.getTitle().toLowerCase() == $paramCSVName.toLowerCase() )
        #set( $url = $attachment.getDownloadPathWithoutVersion() )
    #end
#end

#########################################################################
## This will only run on the macro properties dialog. It will add a    ##
## dropdown to select a CSV file.                                      ##
#########################################################################
<script type="text/javascript">
AJS.toInit(function(){
    /////////////////////////////////////////////////
    // Are we on the macro properties edit screen? //
    /////////////////////////////////////////////////
    if(self != top && window.parent.AJS.${d}('${p}macro-browser-preview').length > 0) {
        var options = '<option value="">Select a file</option>';
        var csvInput = window.parent.AJS.${d}('${p}macro-param-CSVName');
        var pageInput = window.parent.AJS.${d}('${p}macro-param-Page');
        var refresh = window.parent.AJS.${d}('${p}macro-browser-preview-link');
        var secondBlur = false;
        
        //Hide cvsInput ... this will be updated programatically by our new dropdown
        csvInput.css('display','none');

        //Remove copy input if it exists ... macro preview refresh
        window.parent.AJS.${d}('${p}macro-param-CSVName-copy').remove();

        //Add copy field
        csvInput.before('<select class="select" id="macro-param-CSVName-copy" name="macro-param-CSVName-copy"></select>');

        //Reference to new field
        var csvInputCopy = window.parent.AJS.${d}('${p}macro-param-CSVName-copy');

        //Change original csvInput field when dropdown copy is changed
        csvInputCopy.change(function(){
            csvInput.val(csvInputCopy.val());
        });

        //Register change event for page input to populate the attachments for that page
        //This focus/blur workaround is because if you select a page from the page hint
        //popup the blur and change events won't have the full value right away. They will
        //only see what the user typed which may not be the complete page title. So, after 500
        //milliseconds we will focus it and then blur it to get the real value. The secondBlur boolean
        //is to make sure we only run the blur event on the second time around.
        pageInput.change(function(){
            secondBlur = false;
            
            setTimeout(function(){
                secondBlur = true;
                window.parent.AJS.${d}('${p}macro-param-Page').focus();
                window.parent.AJS.${d}('${p}macro-param-Page').blur();
            }, 500);
        });
        pageInput.blur(function(){
            if(secondBlur) {
                secondBlur = false;
                
                //Clear csvInput since we are doing a preview refresh to get new dropdown values
                csvInput.val('');

                //Refresh the preview window which will trigger a fetch of the attachments from the specified page.
                refresh.click();
            }
        });
        
        csvInputCopy.html('');
        #foreach( $attachment in $attachments )
            #if($attachment.getFileExtension() == "csv")
                #if($attachment.getTitle().toLowerCase() == $paramCSVName.toLowerCase())
                    #set( $selected = ' selected="selected"' )
                #else
                    #set( $selected = '' )
                #end
            options += '<option value="$attachment.getTitle()"$selected>$attachment.getTitle()</option>';
            #end
        #end
        if(options == '<option value="">Select a file</option>') {
            csvInput.val('');
        }
        csvInputCopy.html(options);
    }
});
</script>
#########################################################################
## End                                                                 ##
#########################################################################

##########################
## Render the CSV table ##
##########################
#if(${url} && ${url} != "")
<script type="text/javascript">
//CSV parser from https://github.com/evanplaice/jquery-csv
RegExp.escape=function(e){return e.replace(/[-\/\\^${d}*+?.()|[\]{}]/g,"\\${d}&")};AJS.${d}.csv={defaults:{separator:",",delimiter:'"',headers:true},hooks:{castToScalar:function(e,t){var n=/\./;if(isNaN(e)){return e}else{if(n.test(e)){return parseFloat(e)}else{var r=parseInt(e);if(isNaN(r)){return null}else{return r}}}}},parsers:{parse:function(e,t){function f(){o=0;u="";if(t.start&&t.state.rowNum<t.start){s=[];t.state.rowNum++;t.state.colNum=1;return}if(t.onParseEntry===undefined){i.push(s)}else{var e=t.onParseEntry(s,t.state);if(e!==false){i.push(e)}}s=[];if(t.end&&t.state.rowNum>=t.end){a=true}t.state.rowNum++;t.state.colNum=1}function l(){if(t.onParseValue===undefined){s.push(u)}else{var e=t.onParseValue(u,t.state);if(e!==false){s.push(e)}}u="";o=0;t.state.colNum++}var n=t.separator;var r=t.delimiter;if(!t.state.rowNum){t.state.rowNum=1}if(!t.state.colNum){t.state.colNum=1}var i=[];var s=[];var o=0;var u="";var a=false;var c=RegExp.escape(n);var h=RegExp.escape(r);var p=/(D|S|\r\n|\n|\r|[^DS\r\n]+)/;var d=p.source;d=d.replace(/S/g,c);d=d.replace(/D/g,h);p=RegExp(d,"gm");e.replace(p,function(e){if(a){return}switch(o){case 0:if(e===n){u+="";l();break}if(e===r){o=1;break}if(/^(\r\n|\n|\r)${d}/.test(e)){l();f();break}u+=e;o=3;break;case 1:if(e===r){o=2;break}u+=e;o=1;break;case 2:if(e===r){u+=e;o=1;break}if(e===n){l();break}if(/^(\r\n|\n|\r)${d}/.test(e)){l();f();break}throw new Error("CSVDataError: Illegal State [Row:"+t.state.rowNum+"][Col:"+t.state.colNum+"]");case 3:if(e===n){l();break}if(/^(\r\n|\n|\r)${d}/.test(e)){l();f();break}if(e===r){throw new Error("CSVDataError: Illegal Quote [Row:"+t.state.rowNum+"][Col:"+t.state.colNum+"]")}throw new Error("CSVDataError: Illegal Data [Row:"+t.state.rowNum+"][Col:"+t.state.colNum+"]");default:throw new Error("CSVDataError: Unknown State [Row:"+t.state.rowNum+"][Col:"+t.state.colNum+"]")}});if(s.length!==0){l();f()}return i},splitLines:function(e,t){function a(){s=0;if(t.start&&t.state.rowNum<t.start){o="";t.state.rowNum++;return}if(t.onParseEntry===undefined){i.push(o)}else{var e=t.onParseEntry(o,t.state);if(e!==false){i.push(e)}}o="";if(t.end&&t.state.rowNum>=t.end){u=true}t.state.rowNum++}var n=t.separator;var r=t.delimiter;if(!t.state.rowNum){t.state.rowNum=1}var i=[];var s=0;var o="";var u=false;var f=RegExp.escape(n);var l=RegExp.escape(r);var c=/(D|S|\n|\r|[^DS\r\n]+)/;var h=c.source;h=h.replace(/S/g,f);h=h.replace(/D/g,l);c=RegExp(h,"gm");e.replace(c,function(e){if(u){return}switch(s){case 0:if(e===n){o+=e;s=0;break}if(e===r){o+=e;s=1;break}if(e==="\n"){a();break}if(/^\r${d}/.test(e)){break}o+=e;s=3;break;case 1:if(e===r){o+=e;s=2;break}o+=e;s=1;break;case 2:var i=o.substr(o.length-1);if(e===r&&i===r){o+=e;s=1;break}if(e===n){o+=e;s=0;break}if(e==="\n"){a();break}if(e==="\r"){break}throw new Error("CSVDataError: Illegal state [Row:"+t.state.rowNum+"]");case 3:if(e===n){o+=e;s=0;break}if(e==="\n"){a();break}if(e==="\r"){break}if(e===r){throw new Error("CSVDataError: Illegal quote [Row:"+t.state.rowNum+"]")}throw new Error("CSVDataError: Illegal state [Row:"+t.state.rowNum+"]");default:throw new Error("CSVDataError: Unknown state [Row:"+t.state.rowNum+"]")}});if(o!==""){a()}return i},parseEntry:function(e,t){function u(){if(t.onParseValue===undefined){i.push(o)}else{var e=t.onParseValue(o,t.state);if(e!==false){i.push(e)}}o="";s=0;t.state.colNum++}var n=t.separator;var r=t.delimiter;if(!t.state.rowNum){t.state.rowNum=1}if(!t.state.colNum){t.state.colNum=1}var i=[];var s=0;var o="";if(!t.match){var a=RegExp.escape(n);var f=RegExp.escape(r);var l=/(D|S|\n|\r|[^DS\r\n]+)/;var c=l.source;c=c.replace(/S/g,a);c=c.replace(/D/g,f);t.match=RegExp(c,"gm")}e.replace(t.match,function(e){switch(s){case 0:if(e===n){o+="";u();break}if(e===r){s=1;break}if(e==="\n"||e==="\r"){break}o+=e;s=3;break;case 1:if(e===r){s=2;break}o+=e;s=1;break;case 2:if(e===r){o+=e;s=1;break}if(e===n){u();break}if(e==="\n"||e==="\r"){break}throw new Error("CSVDataError: Illegal State [Row:"+t.state.rowNum+"][Col:"+t.state.colNum+"]");case 3:if(e===n){u();break}if(e==="\n"||e==="\r"){break}if(e===r){throw new Error("CSVDataError: Illegal Quote [Row:"+t.state.rowNum+"][Col:"+t.state.colNum+"]")}throw new Error("CSVDataError: Illegal Data [Row:"+t.state.rowNum+"][Col:"+t.state.colNum+"]");default:throw new Error("CSVDataError: Unknown State [Row:"+t.state.rowNum+"][Col:"+t.state.colNum+"]")}});u();return i}},helpers:{collectPropertyNames:function(e){var t,n,r=[];for(t in e){for(n in e[t]){if(e[t].hasOwnProperty(n)&&r.indexOf(n)<0&&typeof e[t][n]!=="function"){r.push(n)}}}return r}},toArray:function(e,t,n){var t=t!==undefined?t:{};var r={};r.callback=n!==undefined&&typeof n==="function"?n:false;r.separator="separator"in t?t.separator:AJS.${d}.csv.defaults.separator;r.delimiter="delimiter"in t?t.delimiter:AJS.${d}.csv.defaults.delimiter;var i=t.state!==undefined?t.state:{};var t={delimiter:r.delimiter,separator:r.separator,onParseEntry:t.onParseEntry,onParseValue:t.onParseValue,state:i};var s=AJS.${d}.csv.parsers.parseEntry(e,t);if(!r.callback){return s}else{r.callback("",s)}},toArrays:function(e,t,n){var t=t!==undefined?t:{};var r={};r.callback=n!==undefined&&typeof n==="function"?n:false;r.separator="separator"in t?t.separator:AJS.${d}.csv.defaults.separator;r.delimiter="delimiter"in t?t.delimiter:AJS.${d}.csv.defaults.delimiter;var i=[];var t={delimiter:r.delimiter,separator:r.separator,onPreParse:t.onPreParse,onParseEntry:t.onParseEntry,onParseValue:t.onParseValue,onPostParse:t.onPostParse,start:t.start,end:t.end,state:{rowNum:1,colNum:1}};if(t.onPreParse!==undefined){t.onPreParse(e,t.state)}i=AJS.${d}.csv.parsers.parse(e,t);if(t.onPostParse!==undefined){t.onPostParse(i,t.state)}if(!r.callback){return i}else{r.callback("",i)}},toObjects:function(e,t,n){var t=t!==undefined?t:{};var r={};r.callback=n!==undefined&&typeof n==="function"?n:false;r.separator="separator"in t?t.separator:AJS.${d}.csv.defaults.separator;r.delimiter="delimiter"in t?t.delimiter:AJS.${d}.csv.defaults.delimiter;r.headers="headers"in t?t.headers:AJS.${d}.csv.defaults.headers;t.start="start"in t?t.start:1;if(r.headers){t.start++}if(t.end&&r.headers){t.end++}var i=[];var s=[];var t={delimiter:r.delimiter,separator:r.separator,onPreParse:t.onPreParse,onParseEntry:t.onParseEntry,onParseValue:t.onParseValue,onPostParse:t.onPostParse,start:t.start,end:t.end,state:{rowNum:1,colNum:1},match:false,transform:t.transform};var o={delimiter:r.delimiter,separator:r.separator,start:1,end:1,state:{rowNum:1,colNum:1}};if(t.onPreParse!==undefined){t.onPreParse(e,t.state)}var u=AJS.${d}.csv.parsers.splitLines(e,o);var a=AJS.${d}.csv.toArray(u[0],t);var i=AJS.${d}.csv.parsers.splitLines(e,t);t.state.colNum=1;if(a){t.state.rowNum=2}else{t.state.rowNum=1}for(var f=0,l=i.length;f<l;f++){var c=AJS.${d}.csv.toArray(i[f],t);var h={};for(var p in a){h[a[p]]=c[p]}if(t.transform!==undefined){s.push(t.transform.call(undefined,h))}else{s.push(h)}t.state.rowNum++}if(t.onPostParse!==undefined){t.onPostParse(s,t.state)}if(!r.callback){return s}else{r.callback("",s)}},fromArrays:function(e,t,n){var t=t!==undefined?t:{};var r={};r.callback=n!==undefined&&typeof n==="function"?n:false;r.separator="separator"in t?t.separator:AJS.${d}.csv.defaults.separator;r.delimiter="delimiter"in t?t.delimiter:AJS.${d}.csv.defaults.delimiter;var i="",s,o,u,a;for(u=0;u<e.length;u++){s=e[u];o=[];for(a=0;a<s.length;a++){var f=s[a]===undefined||s[a]===null?"":s[a].toString();if(f.indexOf(r.delimiter)>-1){f=f.replace(r.delimiter,r.delimiter+r.delimiter)}var l="\n|\r|S|D";l=l.replace("S",r.separator);l=l.replace("D",r.delimiter);if(f.search(l)>-1){f=r.delimiter+f+r.delimiter}o.push(f)}i+=o.join(r.separator)+"\r\n"}if(!r.callback){return i}else{r.callback("",i)}},fromObjects:function(e,t,n){var t=t!==undefined?t:{};var r={};r.callback=n!==undefined&&typeof n==="function"?n:false;r.separator="separator"in t?t.separator:AJS.${d}.csv.defaults.separator;r.delimiter="delimiter"in t?t.delimiter:AJS.${d}.csv.defaults.delimiter;r.headers="headers"in t?t.headers:AJS.${d}.csv.defaults.headers;r.sortOrder="sortOrder"in t?t.sortOrder:"declare";r.manualOrder="manualOrder"in t?t.manualOrder:[];r.transform=t.transform;if(typeof r.manualOrder==="string"){r.manualOrder=AJS.${d}.csv.toArray(r.manualOrder,r)}if(r.transform!==undefined){var i=e;e=[];var s;for(s=0;s<i.length;s++){e.push(r.transform.call(undefined,i[s]))}}var o=AJS.${d}.csv.helpers.collectPropertyNames(e);if(r.sortOrder==="alpha"){o.sort()}if(r.manualOrder.length>0){var u=[].concat(r.manualOrder);var a;for(a=0;a<o.length;a++){if(u.indexOf(o[a])<0){u.push(o[a])}}o=u}var f,a,l,c=[],h;if(r.headers){c.push(o)}for(f=0;f<e.length;f++){l=[];for(a=0;a<o.length;a++){h=o[a];if(h in e[f]&&typeof e[f][h]!=="function"){l.push(e[f][h])}else{l.push("")}}c.push(l)}return AJS.${d}.csv.fromArrays(c,t,r.callback)}}

AJS.toInit(function(){
    AJS.${d}.get("${baseUrl}${url}", function( data ) {
        var result = AJS.${d}.csv.toArrays(data);
        var html = '';
        var count = 0;
        var topHeader = $paramTopHeader;

        for(var row in result) {
            
            if(count == 0) {
                if(topHeader) {
                    html += '<thead>\r\n';
                    html += '<tr>\r\n';
                    for(var item in result[row]) {
                        html += '<th class="confluenceTh">' + result[row][item] + '</th>\r\n';
                    }
                    html += '</tr>\r\n';
                    html += '</thead>\r\n';
                    html += '<tbody>\r\n';
                } else {
                    html += '<tbody>\r\n';
                    html += '<tr>\r\n';
                    for(var item in result[row]) {
                        html += '<td class="confluenceTd">' + result[row][item] + '</td>\r\n';
                    }
                    html += '</tr>\r\n';
                }
            } else {
                html += '<tr>\r\n';
                for(var item in result[row]) {
                    html += '<td class="confluenceTd">' + result[row][item] + '</td>\r\n';
                }
                html += '</tr>\r\n';
            }
            count++
        }
        html += '</tbody>\r\n';
        AJS.$('#csv-table-$id').html(html);

if(topHeader) {
AJS.tablessortable.setTableSortable(AJS.$('#csv-table-$id'));
} }); }); </script> #end <table id="csv-table-$id" class="confluenceTable tablesorter"> </table>

 

9 comments

works like a charm. Great. Thank you

Davin Studer Community Leader Mar 15, 2018

Awesome! Glad it worked for you.

Bill_Bailey Community Leader Sep 27, 2018

I love these articles as they help me be a better user macro writer.

Davin Studer Community Leader Sep 28, 2018

Truly. I love that the Community, as opposed the previous Answers site, allows for articles to be written. I've learned some pretty cool stuff from others as well.

Works great, thank you!

Any suggestion how to create a collapsible list from an attached CSV?

Davin Studer Community Leader Mar 22, 2019

@Samer Harb I'm not quite sure what you mean by that. How would you envision a collapsible list working from a CSV?

@Davin Studer maybe something like this?

2019-03-25 10_00_41-Book1.csv - Excel.png

 

The objective is to have an HTML collapsible list from this table

Like Vincent Blain likes this
Davin Studer Community Leader Mar 25, 2019

It would have to be coded specifically for something like that. My user macro was written to be pretty generic. It creates a table from a CSV ... that's pretty much it. To do something like you are asking would require it to be coded for that specific circumstance. It's certainly not impossible, but it is beyond the scope of this user macro.

If I were to do it I would probably use the css display property on the tr elements. I would for instance scan through the table and mark all the rows that were for Asia with a "data-???" property. Then when you collapse the Asia rows you would just query the rows in the table that were for Asia and set the css for those rows to display:none; and vice versa for expand to "display:table-row;".

Like # people like this

Hello again,

For those interested, I was able to find a way to create a collapsible list from CSV as described on this thread: https://community.atlassian.com/t5/Confluence-discussions/Create-a-collapsible-list-from-a-CSV/m-p/1217582#M6358

Comment

Log in or Sign up to comment
TAGS
Community showcase
Published in Confluence Cloud

What's New in Confluence Cloud – June 2020 Edition

Ready for the monthly rollup of what happened in May for Confluence Cloud? Improved mobile login experience (& SSO) For those of you with the Confluence mobile app, we know the login experien...

25,953 views 40 82
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you