I have a list consisting of tuples that I want to export to excel using python. The list is structured in the following way:
[('ABC-12345', 'hello', 'world', 'https://mycompany.com/browse/ABC-12345'), ..., ('ABC-12360', 'good', 'night', 'https://mycompany.com/browse/ABC-12360')]
On top of this, should the first element in each list row, i.e. ''ABC-12345'', be converted to a hyperlink. The URL for the link is the fourth element, i.e. ''https://mycompany.com/browse/ABC-12345''. To solve this I tried to use Workbook from openpyxl and a for-loop, where i overwrite the first column with the fourth element as a hyperlink. This raises a tuple-error, since the for-loop need n to be an integer or boolean.
I started of with the code:
from openpyxl import Workbook
search = [('ABC-12345', 'hello', 'world', 'https://mycompany.com/browse/ABC-12345'),
...,
('ABC-12360', 'good', 'night', 'https://mycompany.com/browse/ABC-12360')]
wb = Workbook()
ws = wb.active
search_row = 1
search_column = 1
for n in search:
row = search[n]
ws.cell(row=n, column=n).value = row[0]
ws.cell(row=n, column=n).hyperlink = row[3] # Overwriting cell n to create hyperlink
n = n + 1
wb.save("testReport.xlsx")
To get around the tuple-error I tried to write ''for n in enumerate(search):'' instead, but without luck. Hence, I changed my approach and tried using:
import numpy as np
import csv
import pyexcel
from pyexcel._compact import OrderedDict
search = [('ABC-12345', 'hello', 'world', 'https://mycompany.com/browse/ABC-12345'),
...,
('ABC-12360', 'good', 'night', 'https://mycompany.com/browse/ABC-12360')]
# Converting list of tuple to array
arr = np.asarray(search)
# Adding array to a dictionary
dictionary = {'Sheet 1': arr}
pyexcel.save_book_as(
bookdict = dictionary,
dest_file_name = "testReport.xlsx"
)
data = OrderedDict()
data.update({"Sheet 1": dictionary['Sheet 1']})
This does give me an excel-file, but .xls seems to be an outdated file-type and every time I run the program I need to delete the existing file since the filename is already in use. There also seems to be little freedom with editing the xls-file - I didn't find a way to create the hyperlink. Moving on, since I didn't find a solution in these libraries, I thought that maybe I could combine this method with my first method. That required me to save the file as .xlsx, and by doing so I was brought back to the tuple-problem.
Any ideas on how I can solve this? Thankful for suggestions.
I suppose you want something like:
for n, row in enumerate(search, start=1):
ws.cell(row=n, column=1).value = row[0]
ws.cell(row=n, column=1).hyperlink = row[3] # Overwriting cell n to create hyperlink
wb.save("testReport.xlsx")