How to write a set of lists to Excel using xlsxwriter (without pandas)?

user_14144126 0
Viewed 747

I created 3 separated lists and appended them into a empty.

I would like to write each list in Excel under the column Company, Sector, URL.

I tried to iterate them without success.

I'd like to do it without going through Pandas.


    import xlsxwriter

    workbook = xlsxwriter.Workbook('example.xlsx')
    worksheet = workbook.add_worksheet('To add')

    companies = ['House1', 'House2']
    sector = ['Kitchen', 'Living room']
    url = ['www.house1.com', 'www.house2.com']

    list_companies = []
    list_companies.append(companies)
    list_companies.append(sector)
    list_companies.append(url)

    head = ['Company', 'Sector', 'URL']
    bold = workbook.add_format({'bold':True})

    worksheet.write_row(0,0,head, bold)
    workbook.close()
xlsxwriter list excel python
2 Answers

The cleanest way is probably to use the write_column() method. Like this:


import xlsxwriter

workbook = xlsxwriter.Workbook('example.xlsx')
worksheet = workbook.add_worksheet('To add')

companies = ['House1', 'House2']
sector = ['Kitchen', 'Living room']
url = ['http://www.house1.com', 'http://www.house2.com']

head = ['Company', 'Sector', 'URL']
bold = workbook.add_format({'bold':True})

# Make some of the columns wider for clarity.
worksheet.set_column(0, 1, 12)
worksheet.set_column(2, 2, 20)

# Write the data.
worksheet.write_row(0,0,head, bold)
worksheet.write_column(1, 0, companies)
worksheet.write_column(1, 1, sector)
worksheet.write_column(1, 2, url)

workbook.close()

Output:

enter image description here

Note, I added http:// to the urls so that they are converted by XlsxWriter to Excel links.

For writing data with XlsxWriter see the Working with and Writing Data section of the XlsxWriter docs.

user_102380

Converted lists aggregation to a dictionary for convenience.

To write to excel use the worksheet.write(row, col, *args) as follows:

import xlsxwriter

workbook = xlsxwriter.Workbook('example.xlsx')
worksheet = workbook.add_worksheet('To add')

companies = ['House1', 'House2']
sectors = ['Kitchen', 'Living room']
urls = ['www.house1.com', 'www.house2.com']

companies_dictionary = {}

companies_dictionary['companies']   = companies
companies_dictionary['sector']      = sectors
companies_dictionary['url']         = urls

head = ['Company', 'Sector', 'URL']
bold = workbook.add_format({'bold': True})

worksheet.write_row(0, 0, head, bold)


head_offset = 1
for company_index in range(len(companies_dictionary['companies'])):
    current_excel_row_number = company_index + head_offset + 1
    worksheet.write(f'A{current_excel_row_number}', companies_dictionary['companies'][company_index])
    worksheet.write(f'B{current_excel_row_number}', companies_dictionary['sector'][company_index])
    worksheet.write(f'C{current_excel_row_number}', companies_dictionary['url'][company_index])

workbook.close()

Result:

Company Sector  URL
House1  Kitchen www.house1.com
House2  Living room www.house2.com
user_141488640
Related