Importing Excel data into Jira with a Python script

Question

I'm currently stuck with a simple script that reads data from an Excel file and then turns that data into tasks on Jira.

from openpyxl import load_workbook
from jira import JIRA

print("Imports Work")

jira = JIRA('http://jira-mycompany:8080/', basic_auth=('ZippertjeZappertje', 
'Passwordhiddenforthepurpose of this'))
wb = load_workbook(filename = 'ImportTasksExcel.xlsx', read_only = True)
ws = wb['Sheet1']

def promp():
   print("Press Any Key To Continue...")
   input()

def getCellValue(worksheet,  r, c):
   if(worksheet.cell(row=r, column=c).value == None):
      print("Error: Empty Cell. Row " + str(r) + " Col " + str(c))
      promp()
   return worksheet.cell(row=r, column=c).value

print("Worksheet Loaded")

key = ''
parent = ''
child = ''
prename = ''
time = ''
crew = ''
team = ''
importance = ''
person = ''
desc = ''
i = 0

print("Variables Initialised")

for i in range (2, ws.max_row+1):
    pro = getCellValue(ws, i, 1)
    key = ws.cell(row=i, column=2).value
    parent_issue = jira.issue(key)
    parent = str(parent_issue.fields.summary)
    type = getCellValue(ws, i, 3)
    type = ws.cell(row=i, column=3).value
    prename = ws.cell(row=i, column=4).value 
    taskname = ws.cell(row=i, column=5).value 
    child = prename + taskname
    prio = ws.cell(row=i, column=6).value 
    imp = ws.cell(row=i, column=7).value
    confidence = getCellValue(ws, i, 8)
    team = getCellValue(ws, i, 9)
    person = ws.cell(row=i, column=10).value
    time = ws.cell(row=i, column=11).value
    desc = ws.cell(row=i, column=12).value
    sum = prename + " - " + taskname
    report = ws.cell(row=i, column=13).value
    print (sum)

    issue = jira.create_issue(project=pro, summary=sum, description=desc, 
    issuetype=type,  assignee={'name': person}, reporter={'name': report}, 
    timetracking =  {"originalEstimate": time,"remainingEstimate": time}, 
    customfield_11200 = { "value": team }, customfield_12306 = { "value": 
    confidence }, customfield_11100 = { "value": imp }, priority = {'name': 
    prio}    

    print("Successfuly Created: "+ str(issue))

    print('Linking Child: ' + str(issue) + '\t to Parent: ' + key)

    jira.create_issue_link('Broken into', key, str(issue))
    print("SUCCESS\n")



print("\n\nDONE\t Total New Issues Created and Linked: " + str(i-1))
input("prompt: ")
raw_input("Press Enter to Continue: ")

The code returns the following TypeError:

line 39, in pro = getCellValue(ws, i, 1)
line 16, in getCellValue if(worksheet.cell(row=r, column=c).value == None): TypeError: unbound method cell() must be called with Worksheet instance as first argument (got nothing instead)

Which I don't really understand, since "worksheet" is a parameter I declared for the getCellValue function? The cell() method accesses a cell at row r and column c and then the value() method should return that cell's value?

Let's clarify: the method `cell` in question is [Worksheet.cell](https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/worksheet/worksheet.html#Worksheet.cell) ? b) if you add `print(type(worksheet))` in `getCellValue` what do you see? — Alex Yu, Feb 19 '19 at 15:49
Hm. `for i in range (2, ws.max_row+1):` do you get error on first iteration or last? — Alex Yu, Feb 19 '19 at 16:24
I mean on the very first cell. Let's say: https://rextester.com/XUQHW68010 - are all lines are executed fine, not one of them, some fine and others not? (I hope you can see this wall) — Alex Yu, Feb 19 '19 at 17:38
I've run the code you sent above and even when calling: `print(ws.cell(0, 0).value) print(ws.cell(2, 0).value) print(ws.cell(3, ms.max_row).value) print(ws.cell(3, ms.max_row + 1).value)` It produces a traceback error: `line 10, in print(ws.cell(0, 0).value) TypeError: unbound method cell() must be called with Worksheet instance as first argument (got int instance instead)` Given that information, I'd imagine it definitely has to do with the `.cell().value()` calls? — christianvuye, Feb 19 '19 at 17:56
I mean which line (11-14) with `ws.cell(X,Y).value` reised an error: every one of them, first, last? What I can't understand is: why the cell method became **unbound**? Can you try `ws.cell(ws, 0,0)` - it will be strange but understandable (somewhat) — Alex Yu, Feb 19 '19 at 19:52
Glad that you found the solution! It would be better if you instead of edit OP: a) answer your own question, b) accept your own answer - it is encouraged practice on SO — Alex Yu, Feb 20 '19 at 12:39

Importing Excel data into Jira with a Python script

0 Answers0