Create
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Products
See all
Community resources
Top groups
Explore all groups
Community resources
Learn
Community resources
Events
Community resources

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

February 27, 2018

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>

 

Comment
Watch
Like # people like this
5319 views

9 comments

TheReal00sohn
TheReal00sohn
Contributor
March 15, 2018

works like a charm. Great. Thank you

Like
Davin Studer
Davin Studer
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Get recognized
March 15, 2018

Awesome! Glad it worked for you.

Like
Bill Bailey
Bill Bailey
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Get recognized
September 27, 2018

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

Like
Davin Studer
Davin Studer
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Get recognized
September 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.

Like
Samer Harb
Samer Harb
Contributor
March 22, 2019

Works great, thank you!

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

Like
Davin Studer
Davin Studer
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Get recognized
March 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?

Like
Samer Harb
Samer Harb
Contributor
March 25, 2019

@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
Davin Studer
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Get recognized
March 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
Samer Harb
Samer Harb
Contributor
November 1, 2019

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

Like

Comment

Log in or Sign up to comment
Davin Studer

Davin Studer

Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Get recognized

About this author

Business Intelligence Engineer

Vancouver, WA

480 accepted answers

3,356 total posts

View profile
Confluence
Confluence
TAGS
AUG Leaders

Atlassian Community Events

    See all events