Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

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
Highlighted

Create a collapsible list from a CSV

High level description of the steps

  • Part 1 - Copy list to the excel template
  • Part 2 - Run the macro
  • Part 3 - Copy the output, and paste it on confluence page

 

This approach will turn this.....
image.png

...into this:
image.png

 

Part 1 - Copy list to the excel template
Download the excel template from this link. We copy the info required to it.

 


Part 2 - Run the macro
We click View Macros

image.png

We select the Macro and click Run

image.png

 

We should notice a file (wikiTree_Date) created in the same directory of our excel file
image.png

 

 

Part 3 - Copy the output, and paste it on confluence page

We edit the output file (i.e. in Notepad) and copy the whole content

image.png

 

Then we go to the wiki page and we add an HTML macro and we click insert without doing any changes

image.png

 

We paste the HTML content generated and we click Save

image.png

 

And the tree will be generated.

image.png

 

 

Thanks for @Davin Studer for writing this article: https://community.atlassian.com/t5/Confluence-articles/A-User-Macro-to-Create-a-Table-From-an-Attached-CSV-File-Part/ba-p/738757?utm_source=atlcomm&utm_medium=email&utm_campaign=kudos_comment&utm_content=topic

 

 

NB: In case you are unable to download the macro file for security reasons, this is the code that you can copy/paste to your Excel macro file

Sub wikiTree()


Dim t

t = Timer
ClearDups Range("D2:D300000") 'remove the dups
Debug.Print Timer - t & " sec" ' < 1sec

t = Timer
ClearDups Range("B2:B300000") 'remove the dups
Debug.Print Timer - t & " sec" ' < 1sec

t = Timer
ClearDups Range("A2:A300000") 'remove the dups
Debug.Print Timer - t & " sec" ' < 1sec




'Define your variables.
Dim iRow As Long
Dim iStage As Integer
Dim iCounter As Integer
Dim iPage As Integer

'Create an .htm file in the same directory as your active workbook.
Dim sFile As String
sFile = Application.ActiveWorkbook.path & "\wikiTree_" & DateTime.Date & ".htm"
'sFile = "D:\test.htm"
Close

'Open up the temp HTML file and format the header.
Open sFile For Output As #1
Print #1, "<style>"
Print #1, "ul, #myUL {"
Print #1, " list-style-type: none;"
Print #1, "}"
Print #1, "#myUL {"
Print #1, " margin: 0;"
Print #1, " padding: 0;"
Print #1, "}"
Print #1, ".box {"
Print #1, " cursor: pointer;"
Print #1, " -webkit-user-select: none; /* Safari 3.1+ */"
Print #1, " -moz-user-select: none; /* Firefox 2+ */"
Print #1, " -ms-user-select: none; /* IE 10+ */"
Print #1, " user-select: none;"
Print #1, "}"
Print #1, ".box::before {"
Print #1, " content: ""\2610"";"
Print #1, " color: black;"
Print #1, " display: inline-block;"
Print #1, " margin-right: 6px;"
Print #1, "}"
Print #1, ".check-box::before {"
Print #1, " content: ""\2611""; "
Print #1, " color: dodgerblue;"
Print #1, "}"
Print #1, ".nested {"
Print #1, " display: none;"
Print #1, "}"
Print #1, ".active {"
Print #1, " display: block;"
Print #1, "}"
Print #1, " body { font-size:12px;font-family:tahoma } "
Print #1, "</style>"
Print #1, "<body>"
Print #1, "<ul id=""myUL"">"

'Start on the 2nd row to avoid the header.
iRow = 2

'Translate the first column of the table into the first level of the hierarchy.
Do While WorksheetFunction.CountA(Rows(iRow)) > 0
If Not IsEmpty(Cells(iRow, 1)) Then
For iCounter = 1 To iStage
Print #1, "</ul>"
iStage = iStage - 1
Next iCounter
Print #1, "<li><a class=""box"">" & Cells(iRow, 1).Value & "</a>"
iPage = iPage + 1
If iStage < 1 Then
Print #1, "<ul class=""nested"">"
iStage = iStage + 1
End If
End If

'Translate the second column of the table into the second level of the hierarchy.
If Not IsEmpty(Cells(iRow, 2)) Then
For iCounter = 2 To iStage
Print #1, "</ul>"
iStage = iStage - 1
Next iCounter
Print #1, "<li><a class=""box"">" & Cells(iRow, 2).Value & "</a>"
iPage = iPage + 1
If iStage < 2 Then
Print #1, "<ul class=""nested"">"
iStage = iStage + 1
End If
End If

'Translate the third column of the table into the third level of the hierarchy.
If Not IsEmpty(Cells(iRow, 3)) Then
If iStage < 3 Then
Print #1, "<ul>"
End If
Print #1, "<li><a>" & Cells(iRow, 3).Value & "</a>"
iPage = iPage + 1
If iStage < 3 Then

iStage = iStage + 1
End If
End If
iRow = iRow + 1
Loop

'Add ending HTML tags
For iCounter = 2 To iStage
Print #1, " </ul>"
iStage = iStage - 1
Next iCounter

Print #1, "<script>"
Print #1, "var toggler = document.getElementsByClassName(""box"");"
Print #1, "var i;"
Print #1, "for (i = 0; i < toggler.length; i++) {"
Print #1, " toggler[i].addEventListener(""click"", function() {"
Print #1, " this.parentElement.querySelector("".nested"").classList.toggle(""active"");"
Print #1, " this.classList.toggle(""check-box"");"
Print #1, " });"
Print #1, "}"
Print #1, "</script>"
Print #1, "</body>"
Close
'Shell "hh " & vbLf & sFile, vbMaximizedFocus
End Sub




Sub ClearDups(rng As Range)
Dim data, dict As Object, r As Long, nR As Long, tmp
Set dict = CreateObject("scripting.dictionary")

Set rng = rng.Columns(1) 'make sure only one column...
data = rng.Value 'grab data in an array
nR = UBound(data, 1)
'loop over the array
For r = 1 To nR
tmp = data(r, 1)
If Len(tmp) > 0 Then
If dict.exists(tmp) Then
data(r, 1) = "" 'seen this value before - clear it
Else
dict.Add tmp, 1 'first time for this value
End If
End If
Next r
rng.Value = data 'dump the array back to the range
End Sub

 

0 comments

Comment

Log in or Sign up to comment
TAGS
Community showcase
Posted in Confluence

What do you think is the most *delightful* Confluence feature? Comment for a prize!

- Create your own custom emoji 🔥 - "Shake for Feedback" on mobile 📱 - An endless supply of GIFs via GIPHY 🤩 Is there anything quite as nice as a pleasant surprise? Comment below with what...

462 views 24 9
Join discussion

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