Python xlsxwriter : use a for loop to write from several lists

Question

I'm trying to learn Python and face a problem with writing to an excel file with the xlsxwriter module using several lists.

I have 4 lists and all of the lists have the same length (5 items in each lists).

I there create without a problem my excel file, sheet and the headers of the excel file:

listing = xlsxwriter.Workbook(path_report + "/Listing.xlsx")
worksheet = listing.add_worksheet("Listing")

worksheet.write(0, 0, "files_list_database")
worksheet.write(0, 1, "totals_list_database")
worksheet.write(0, 2, "files_list_csv")
worksheet.write(0, 3, "prices_list_csv")
worksheet.write(0, 4, "match")

And here is the part where I struggle populating the 5 first rows (one row per item of the lists; I would like the 1st element of each list to be in the first row and each list has its header with the same name so that there is no confusion):

row = 0

for files_list_database, totals_list_database, files_list_csv, prices_list_csv in files_list_database:

    worksheet.write(row + 1, 0, files_list_database[row]) 
    worksheet.write(row + 1, 1, totals_list_database[row])
    worksheet.write(row + 1, 2, files_list_csv[row])
    worksheet.write(row + 1, 3, prices_list_csv[row])

    row = row + 1

files_list_database, totals_list_database, files_list_csv and prices_list_csv are my 4 lists which all contains 5 elements

Here is the error message:

--> for files_list_database, totals_list_database, files_list_csv, prices_list_csv in files_list_database:
          worksheet.write(row + 1, 0, files_list_database[row])
          worksheet.write(row + 1, 1, totals_list_database[row])

ValueError: too many values to unpack (expected 4)

I do not understand what "ValueError: too many values to unpack (expected 4)" means, the computer should expect 5 values as there are 5 values in each of my lists? I assume this comes from the "in files_list_database" which is written twice in the same line but I cannot figure how to specify how many times I want the computer to loop through the lists ...

The aim would be to be able to repeat this procedure even if the lists contains 1000 values each :) ! (All the lists would always contain the same number of values)

Basically, from those 4 lists:

files_list_database = ['2020_04_20_1.csv', '2020_04_20_2.csv', '2020_04_20_3.csv', '2020_04_20_4.csv', '2020_04_20_5.csv']
totals_list_database = [1000, 2000, 3000, 4000, 5000]
files_list_csv = ['2020_04_20_1.csv', '2020_04_20_2.csv', '2020_04_20_3.csv', '2020_04_20_4.csv', '2020_04_20_5.csv']
prices_list_csv = ['1000', '2000', '3000', '4000', '50000']

I would like to populate my excel file and then after do a match with a simple excel formula :) !

Expected excel file generated image

Thanks for your help :) !

Answer 1

There are at least 2 ways to write the lists to an excel file.

Using pandas

import pandas as pd

files_list_database = ['2020_04_20_1.csv', '2020_04_20_2.csv', '2020_04_20_3.csv', '2020_04_20_4.csv', '2020_04_20_5.csv']
totals_list_database = [1000, 2000, 3000, 4000, 5000]
files_list_csv = ['2020_04_20_1.csv', '2020_04_20_2.csv', '2020_04_20_3.csv', '2020_04_20_4.csv', '2020_04_20_5.csv']
prices_list_csv = ['1000', '2000', '3000', '4000', '50000']

df['files_list_database'] = pd.Series(files_list_database)
df['totals_list_database'] = pd.Series(totals_list_database)
df['files_list_csv'] = pd.Series(files_list_csv)
df['prices_list_csv'] = pd.Series(prices_list_csv)

df.to_excel('test.xlsx', index=False)

Using xlsxwriter

import xlsxwriter

files_list_database = ['2020_04_20_1.csv', '2020_04_20_2.csv', '2020_04_20_3.csv', '2020_04_20_4.csv', '2020_04_20_5.csv']
totals_list_database = [1000, 2000, 3000, 4000, 5000]
files_list_csv = ['2020_04_20_1.csv', '2020_04_20_2.csv', '2020_04_20_3.csv', '2020_04_20_4.csv', '2020_04_20_5.csv']
prices_list_csv = ['1000', '2000', '3000', '4000', '50000']

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet('Sheet1')

worksheet.write('A1', 'files_list_database')
for row in range(2,len(files_list_database)+2):
    worksheet.write(f'A{row}', files_list_database[row-2])

worksheet.write('B1', 'totals_list_database')
for row in range(2,len(totals_list_database)+2):
    worksheet.write(f'B{row}', totals_list_database[row-2])

worksheet.write('C1', 'files_list_csv')
for row in range(2,len(files_list_csv)+2):
    worksheet.write(f'C{row}', files_list_csv[row-2])

worksheet.write('D1', 'prices_list_csv')
for row in range(2,len(prices_list_csv)+2):
    worksheet.write(f'D{row}', prices_list_csv[row-2])

workbook.close()

With both ways output is identical to your desired file:

*EDIT*

You could also create a dictionary that will contain all of your filenames along with their data:

import xlsxwriter

files_list_database = ['2020_04_20_1.csv', '2020_04_20_2.csv', '2020_04_20_3.csv', '2020_04_20_4.csv', '2020_04_20_5.csv']
totals_list_database = [1000, 2000, 3000, 4000, 5000]
files_list_csv = ['2020_04_20_1.csv', '2020_04_20_2.csv', '2020_04_20_3.csv', '2020_04_20_4.csv', '2020_04_20_5.csv']
prices_list_csv = ['1000', '2000', '3000', '4000', '50000']

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet('Sheet1')

# Create a dictionary with your file names and your files
all_files = {'files_list_database': files_list_database,
            'totals_list_database': totals_list_database,
            'files_list_csv': files_list_csv,
            'prices_list_csv': prices_list_csv}

for index, file in enumerate(all_files):
    worksheet.write(0, index, file)
    for row in range(1, len(all_files[file])+1):
        worksheet.write(row, index, all_files[file][row-1])

workbook.close()