Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,360,317
Community Members
 
Community Events
168
Community Groups

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

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.

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?

@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

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

Watch 4 Confluence apps compete for Best App Demo in September's Appy Hours

Calling all collaborators! Appy Hours on the Atlassian Community is a monthly event where 4 Partner and app vendor presenters go head-to-head with 5-minute demos for the title of Best App Demo. I...

535 views 0 12
Read article

Atlassian Community Events