Convert HTML table data into csv using scriptrunner
Vikrant Yadav
Community Leader 
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
2 answers
1 accepted
Ram Kumar Aravindakshan _Adaptavist_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Vikrant Yadav
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Ram Kumar Aravindakshan _Adaptavist_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
To answer your question, you can try something like this:-
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.security.login.LoginManager
import com.atlassian.mail.Email
import groovy.xml.MarkupBuilder
import org.jsoup.Jsoup
import javax.mail.internet.MimeBodyPart
import javax.mail.internet.MimeMultipart
import java.text.SimpleDateFormat
def loginManager = ComponentAccessor.getComponentOfType(LoginManager.class)
def groupManager = ComponentAccessor.groupManager
def userUtil = ComponentAccessor.userUtil
def adminGroup = groupManager.getUsersInGroup('jira-administrators')
def softwareGroup = groupManager.getUsersInGroup('jira-software-users')
def serviceDeskGroup = groupManager.getUsersInGroup('jira-servicedesk-users')
def users = adminGroup + softwareGroup + serviceDeskGroup
users.unique()
final def filePath = '/tmp'
def emailBody = new StringWriter()
def html = new MarkupBuilder(emailBody)
html.html {
head {
style(type:'text/css', """
table {
border-collapse: collapse;
width: 100%;
}
th, td {
text-align: left;
padding: 8px;
}
tr:nth-child(even){background-color: #f2f2f2}
th {
background-color: #04AA6D;
color: white;
}
""".toString() )
}
body {
table {
thead {
tr {
th 'User Name'
th 'Full Name'
th 'Email Address'
th 'Last Login'
th 'Status'
}
users.each {
def lastLoginTime = loginManager.getLoginInfo(it.username).lastLoginTime
def username = it.username
def displayName = it.displayName
def emailAddress = it.emailAddress
def active = it.active
if (userUtil.getGroupsForUser(it.name).size() > 0) {
tr {
if (!active) {
td ( username )
td ( displayName )
td ( emailAddress )
td ('Inactive User' )
td ( active )
} else if (!lastLoginTime) {
td ( username )
td ( displayName )
td ( emailAddress )
td ('Logon not found' )
td ( active )
} else {
def date = new Date(lastLoginTime)
def df2 = new SimpleDateFormat("dd/MM/yy hh:mm")
def dateText = df2.format(date)
td ( username )
td ( displayName )
td ( emailAddress )
td ( dateText )
td ( active )
}
}
}
}
}
}
}
}
def dest = new File("${filePath}/output.csv")
dest.createNewFile()
def subject = 'User data'
def emailAddr = 'sample@user.com'
def fileWriter = new FileWriter("${filePath}/output.csv")
fileWriter.write(generateCSV(emailBody.toString()))
fileWriter.close()
final static String generateCSV(String tableDetails) {
def stringWriter = new StringWriter()
def doc = Jsoup.parseBodyFragment(tableDetails)
def rows = doc.getElementsByTag('tr')
rows.each {
def header = it.getElementsByTag('th')
def cells = it.getElementsByTag('td')
header.each { headerCell ->
stringWriter.append(headerCell.text().concat(', '))
}
cells.each { cell ->
stringWriter.append(cell.text().concat(', '))
}
stringWriter.append('\n')
}
stringWriter.toString()
}
final static creatMessage(String to, String subject, String content, File file) {
def mailServerManager = ComponentAccessor.mailServerManager
def mailServer = mailServerManager.defaultSMTPMailServer
def multipart = new MimeMultipart()
def body = new MimeBodyPart()
def mailAttachment = new MimeBodyPart()
body.setContent(content, 'text/html; charset=utf-8')
mailAttachment.attachFile(file)
multipart.addBodyPart(body)
multipart.addBodyPart(mailAttachment)
def email = new Email(to)
email.setSubject(subject)
email.setMultipart(multipart)
email.setMimeType("text/html")
def threadClassLoader = Thread.currentThread().contextClassLoader
Thread.currentThread().contextClassLoader = mailServer.class.classLoader
mailServer.send(email)
Thread.currentThread().contextClassLoader = threadClassLoader
}
creatMessage(emailAddr, subject, emailBody.toString(), dest)
Please note that the sample working code above is not 100% exact to your environment. Hence, you will need to modify it accordingly.
If you observe in the code, I have created two different static methods, i.e. the first is to generate the CSV file, and the second is to send the email. Also, instead of getting the users by using the UserUtil class, which is deprecated as shown below:-
def userUtil = ComponentAccessor.userUtil
def user = userUtil.users
I have instead got the users from the Group using the Group Manager as shown below:-
def groupManager = ComponentAccessor.groupManager
....
def adminGroup = groupManager.getUsersInGroup('jira-administrators')
def softwareGroup = groupManager.getUsersInGroup('jira-software-users')
def serviceDeskGroup = groupManager.getUsersInGroup('jira-servicedesk-users')
def users = adminGroup + softwareGroup + serviceDeskGroup
The Collection of users from all the groups will be merged into a single Collection, and any duplicates will be removed.
Below is a print screen of the Jobs configuration:-
Below is the output that is returned in the email. If you observe, the table formatted is added to the email's body and included as an attachment in the CSV file format.
And the CSV file data are shown on the print screen below:-
I hope this helps to solve your question. :)
Thank you and Kind regards,
Ram
Vikrant Yadav
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Ram Kumar Aravindakshan _Adaptavist_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Vikrant Yadav
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Ram Kumar Aravindakshan _Adaptavist_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Vikrant Yadav
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Ram Kumar Aravindakshan _Adaptavist_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Vikrant Yadav
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Ravi Sagar _Sparxsys_
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.