CODEWITHSUNDEEP
pythonseleniumfor-loopxlsxwriter
meto
meto

Reputation: 65

How can I write my table of data row after row to Excel with xlsxwriter

I am having issue iterating rows in xlsxwriter once again. I tried many combination of it but failed. Your help will be much appreciated! My code;

file = open("namelist.txt","r")
data = []

for d in file:
    url = "file:///C:/Users/k/Desktop/HTML/"
    t = (url) + d
    data.append(t.strip())
file.close()

# data variable becomes --> ['file:///C:/Users/k/Desktop/HTML/new01.html', 'file:///C:/Users/k/Desktop/HTML/new02.html', 
# 'file:///C:/Users/k/Desktop/HTML/new03.html', 'file:///C:/Users/k/Desktop/HTML/new04.html']

workbook = xlsxwriter.Workbook("namelist.xlsx")
worksheet = workbook.add_worksheet("Name list")

# this peace of code writes header
for headers in data:
    b = data[0]
    browser.get(b)
    header = browser.find_elements_by_xpath("/html/body/table/tbody/tr[1]/th")
    head = []
    for h in header:
        head.append(h.text)
        worksheet.write_row("A1", head)

print ("Headers written to namelist.xlsx succesfully!")
print ("Given Name list being written to namelist.xlsx...")

# this part of the core overrites the tables to excel file
for i in data:
    browser.get(i)
    trs = browser.find_elements_by_xpath("/html/body/table/tbody/tr")
    for n, tr in enumerate(trs):
        row=[td.text for td in tr.find_elements_by_tag_name("td")]
        print (row)
        worksheet.write_row("A{}".format(1+n), row)
print ("namelist.xlsx is ready!")
workbook.close()

Each link in data[ ] contain table of different data with the same format. Like this below

enter image description here

enter image description here enter image description here enter image description here

For loop in my code overwrites the rows so I can only see one of the table above in Excel enter image description here

however I need them to be written by iterating to all link and write row after row like this; enter image description here

Many thanks in advance!

Upvotes: 1

Views: 942

Answers (2)

meto
meto

Reputation: 65

Thanks very much for the help, It worked out actually however adds space for each loop so there are empty rows in Excel now. I have tried to change; worksheet.write_row("A{}".format(line), row)

but didn't work.

enter image description here

Any idea on how to avoid empty rows for the each loop?

Upvotes: 0

nerko937
nerko937

Reputation: 11

Apperently you have 1,2,3... values in n in last loop, make some variable with int and increment it in loop like:

line = 1
for tr in trs:
    row=[td.text for td in tr.find_elements_by_tag_name("td")]
    print (row)
    worksheet.write_row("A{}".format(line + 1), row)
    line += 1

Upvotes: 1

Related Questions