How do I programmatically generate Jira chart/table/pie in confluence page

[yifat_test]

Hello,

How do I programmatically generate Jira chart/table/pie in confluence page?
Using Rest API or any other option?

I develop in Python.

Thank you

[Mike Rathwell]

Hi @yifat_test ,

No coding needed to do this. If you don't have it, put in the Table Filter and Charts macro in Confluence. Insert that on a page and then insert a Jira macro with your issues selected in there. Presto... charts of many kinds.

[yifat_test]

Hi,

My flow is that I programmatically create new confluence parent page and child pages and post data in them.
Now I need that based on specific method results to generate chart with this data.

I don't think i can do it with macro, correct?

Thank you,

Yifat

[Andrey Khaneev _StiltSoft_]

Hi @yifat_test,

You can create a sample page with Jira Issues and Charts set as you need. Then copy the page storage format and use it as a template for your script. I guess you'll need to update the JQL in the Jira Issues macro only. Then you can create new pages with the automatically updated storage format using Confluence REST API.

[yifat_test]

Assuming that I want to do it from my code because it is one time effort instead of maintenance. is it possible? I couldn't find any rest-api to generate Jira chart

[Andrey Khaneev _StiltSoft_]

I meant that you create a sample page manually - it is the easiest way. Set anything you need as a Confluence user, then use this page as a template.

For example, I created this page:

Then I can get the page storage format:

I can find there, for example, JQL that is used to retrieve Jira issues: 

<ac:structured-macro ac:name="jira" ac:schema-version="1" ac:macro-id="6c23ec1e-0994-4628-897f-25a5bfc8d7c9">
<ac:parameter ac:name="server">Create and track feature requests for Atlassian products.</ac:parameter>
<ac:parameter ac:name="columns">key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution</ac:parameter>
<ac:parameter ac:name="maximumIssues">20</ac:parameter>
<ac:parameter ac:name="jqlQuery">project = CONFSERVER </ac:parameter><ac:parameter ac:name="serverId">144880e9-a353-312f-9412-ed028e8166fa</ac:parameter>
</ac:structured-macro>

This XML can be modified by your script. 

Then use Confluence REST API to create page.

[yifat_test]

I will check now
Thank you

[yifat_test]

It seems that I don't have the option "View Storage format", only "View source"

[Andrey Khaneev _StiltSoft_]

I suppose this option is restricted by your Confluence administrator, you can ask the administrator to give you permissions (see docs for details). Another workaround is to use the free Source Editor app.

[kaysaff.tal]

@Andrey Khaneev _StiltSoft_

After updating the xml format (only changing the "key= my_issue" using lxml, and pushing the data back to confluence.

It is recognized as text and doesn't create the macro. 

[Andrey Khaneev _StiltSoft_]

@kaysaff.tal , how do you update the page?

[kaysaff.tal]

from atlassian import Confluence

from lxml import etree as et

from bs4 import BeautifulSoup as bs

#connect to confluence    

confObj = Confluence(url='comapny-server',username=user,password=password)    

print(f'confluence Connection created:{confObj}')

#get page data

page_id = confObj.get_page_id(space_name,page_title) 

page= confObj.get_page_by_id(page_id, expand='body.storage', status=None, version=None)

page_content = str(page['body']['storage']['value']) 

plan_tables = pd.read_html(page_content)

#use beutiful soup

soup = bs(page_content, "html")

conf_tables_bodys = soup.findAll('tbody')    

conf_table_body = conf_tables_bodys[5]

conf_table_rows = conf_table_body.findAll('tr')    

conf_table_row = conf_table_rows[1]    

conf_row_cells = conf_table_row.findAll('td')    

tag = conf_row_cells[-1]    

new_cell_contents = create_macro_text('qms-127')    

#Update the confluence page    

confObj.update_page(page_id,title=page_title,body=str(soup))

+++++++++++++++++++++++++++Used function+++++++++++

def create_macro_text(issue_key):    

text = create_jira_link_macro_text(issue_key)    

pargraph_text = create_paragraph (text)   

 div_text = create_div_class_conent_wrapper(pargraph_text)    

content_str = div_text    

return content_str

#this function creates a jira link with in confluence using a confluece macrode

create_jira_link_macro_text(issue_key):    

xHTML = '<ac:structured-macro ac:macro-id="5455a932-bfa9-44b6-969f-4ce35e337b26" ac:name="jira" ac:schema-version="1">'    

xHTML += '<ac:parameter ac:name="server">"company" Jira</ac:parameter>' 

xHTML += '<ac:parameter ac:name="columns">type,key,summary,status,due,timeoriginalestimate,timespent,epic link</ac:parameter>'    

xHTML += '<ac:parameter ac:name="maximumIssues">1000</ac:parameter>'   

 xHTML += '<ac:parameter ac:name="jqlQuery">key = ' + issue_key + ' </ac:parameter>'    

xHTML += '<ac:parameter ac:name="serverId">3664a4b1-0979-3c11</ac:parameter>'   

 xHTML += '</ac:structured-macro>'    return xHTML

def create_div_class_conent_wrapper(text_to_wrap):   

 html_div = '<div class="content-wrapper">'   

 html_div += text_to_wrap   

 html_div +='</div>'    

return html_div

#this function wraps text with Paragraph Tags

def create_paragraph(text_to_wrap):    

html_paragraph = '<p>'    html_paragraph += text_to_wrap    

html_paragraph +='</p>'    

return html_paragraph

[kaysaff.tal]

from lxml import etree as et

from bs4 import BeautifulSoup as bs

from atlassian import Confluence

#connect to confluence    

confObj = Confluence(url='company-confluence',username=user,password=password)   print(f'confluence Connection created:{confObj}')

page_id = confObj.get_page_id(space_name,page_title)
page= confObj.get_page_by_id(page_id, expand='body.storage', status=None, version=None)
page_content = str(page['body']['storage']['value'])
plan_tables = pd.read_html(page_content)

#get a refrence to the HTM
soup = bs(page_content, "html")

conf_tables_bodys = soup.findAll('tbody')
conf_table_body = conf_tables_bodys[TableIndexes.qual_act_table_index.value]
conf_table_rows = conf_table_body.findAll('tr')
conf_table_row = conf_table_rows[1]
conf_row_cells = conf_table_row.findAll('td')
tag = conf_row_cells[-1]
new_cell_contents = create_macro_text('qms-127')
new_tag = soup.new_tag('td')
new_tag.string = new_cell_contents
tag.replace_with(new_tag)

#Update the confluence page
confObj.update_page(page_id,title=page_title,body=str(soup))

def create_macro_text(issue_key):
text = create_jira_link_macro_text(issue_key)
pargraph_text = create_paragraph (text)
div_text = create_div_class_conent_wrapper(pargraph_text)
content_str = div_text
return content_str

#this function creates a jira link with in confluence using a confluece macro
def create_jira_link_macro_text(issue_key):
xHTML = '<ac:structured-macro ac:macro-id="5455a932-bfa9-44b6-969f-4ce35e337b26" ac:name="jira" ac:schema-version="1">'
xHTML += '<ac:parameter ac:name="server">Argus Jira</ac:parameter>'
xHTML += '<ac:parameter ac:name="columns">type,key,summary,status,due,timeoriginalestimate,timespent,epic link</ac:parameter>'
xHTML += '<ac:parameter ac:name="maximumIssues">1000</ac:parameter>'
xHTML += '<ac:parameter ac:name="jqlQuery">key = ' + issue_key + ' </ac:parameter>'
xHTML += '<ac:parameter ac:name="serverId">3664a4b1-0979-3c11-ad7f-869054e3fd8d</ac:parameter>'
xHTML += '</ac:structured-macro>'
return xHTML

def create_div_class_conent_wrapper(text_to_wrap):
html_div = '<div class="content-wrapper">'
html_div += text_to_wrap
html_div +='</div>'
return html_div

#this function wraps text with Paragraph Tags
def create_paragraph(text_to_wrap):
html_paragraph = '<p>'
html_paragraph += text_to_wrap
html_paragraph +='</p>'
return html_paragraph

[kaysaff.tal]

@Andrey Khaneev _StiltSoft_ 

all the text is there, i just can get is to be recognized as xml

it looks like this 

td>&lt;div class="content-wrapper"&gt;&lt;p&gt;&lt;ac:structured-macro ac:macro-id="5455a932-bfa9-44b6-969f-4ce35e337b26" ac:name="jira" ac:schema-version="1"&gt;&lt;ac:parameter ac:name="server"&gt;CompanyJira&lt;/ac:parameter&gt;&lt;ac:parameter ac:name="columns"&gt;type,key,summary,status,due,timeoriginalestimate,timespent,epic link&lt;/ac:parameter&gt;&lt;ac:parameter ac:name="maximumIssues"&gt;1000&lt;/ac:parameter&gt;&lt;ac:parameter ac:name="jqlQuery"&gt;key = qms-127 &lt;/ac:parameter&gt;&lt;ac:parameter ac:name="serverId"&gt;3664a4b1-0979-3c11&lt;/ac:parameter&gt;&lt;/ac:structured-macro&gt;&lt;/p&gt;&lt;/div&gt;</td>

[Andrey Khaneev _StiltSoft_]

As I see, the contents are HTML escaped (for example, &lt; instead of <), that's why you see it as plain text. Try to debug your code to see when characters < get escaped to &lt;.

[kaysaff.tal]

@Andrey Khaneev _StiltSoft_ THANKS.

 Will try