Writing to excel - Python

Question

I am newbie and i will appreciate some help for my code.I have wrote that function:

    def write_captions_to_excel_file(self, filenames, titles, indexs, description_path):
        print('writing to excel')
        workbook = Workbook(os.path.join(description_path, 'all_captions.xlsx'))
        worksheet = workbook.add_worksheet()
        firstrow = 0
        worksheet.write(firstrow, 0, 'Image name')  # 3 --> row number, column number, value
        worksheet.write(firstrow, 1, 'Titles')
        worksheet.write(firstrow, 2, 'Description')
        row = indexs + 1
        worksheet.write(row, 0, filenames)
        origin_title = titles.h1.getText()
        worksheet.write(row, 1, origin_title.capitalize())
        print(origin_title.capitalize())
        worksheet.write(row, 2, '')
        sleep(3)
        workbook.close()

The problem is that i call that function many times, first time gives me the result in row 0 and 1, then when it runs again it gives me the results in row 0 and row 2, row 1 results have been erased. Same in other's calls.

I think that every time i call the function it creates from the beginning the workbook and i lost the previous data.How can avoid that?

As i see in the documentation XlsxWriter is designed only as a file writer. It cannot read or modify an existing Excel file.

Thank you

Answer 1

Great i use lists instead of calling the function several times and it works good now.I did that: def download_portal(self): for index, link in enumerate(all_links): self.list1.append(filename) self.list2.append(origin_title.capitalize()) self.list3.append(spec_date.split("world ", 1)[1]) descriptions_folder_path = os.path.join(self.path, 'description') if not os.path.exists(descriptions_folder_path): os.mkdir(descriptions_folder_path) self.write_captions_to_excel_file(descriptions_folder_path, self.list1, self.list2, self.list3 )

    def write_captions_to_excel_file(self,description_path, firstlists, secondlists, thirdlists):
    print('writing to excel')
    workbook = Workbook(os.path.join(description_path, 'all_captions.xlsx'))
    worksheet = workbook.add_worksheet()
    row = 0
    worksheet.write(row, 0, 'Image name')  # 3 --> row number, column number, value
    worksheet.write(row, 1, 'Titles')
    worksheet.write(row, 2, 'Dates')
    row += 1
    for index, firstlist in enumerate(firstlists):
        worksheet.write(row, 0, firstlist)
        print(firstlist)
        row += 1
    row = 1
    for secondlist in secondlists:
        worksheet.write(row, 1, secondlist)
        print(secondlist)
        row += 1
    row = 1
    for thirdlist in thirdlists:
        worksheet.write(row, 2, thirdlist)
        print(thirdlist)
        row += 1
    sleep(3)
    workbook.close()

I try to use a tuple too and then store inside the 3 lists and then with only one for loop write the data to the excel but i get an error and now i use this way. It will be better to write in excel column per column, but i did my job with this way too.

Thank you

Answer 2

Welcome to SO!

You are right. You cannot append data with xlsx writer. But you can modify your code to do what you are intended to.

My suggestion would be, instead of calling the function several times, store the data in an array and pass the list to the function and iterate the list to write the whole document in a single stretch. This way will be economical by time and it will give you more readability.

Sample code will be like,

import os
import xlsxwriter

def write_captions_to_excel_file(data_list, description_path, headers):
    workbook = xlsxwriter.Workbook(os.path.join(description_path, 'all_captions.xlsx'))
    worksheet = workbook.add_worksheet()
    for row, data in enumerate(data_list):
        for col, header in enumerate(headers):
            if header == 'Titles':
                text_to_write = data[header]
            else:
                text_to_write = data[header].h1.getText().capitalize()
            worksheet.write(row+1, col, text_to_write)


def some_function():
    headers = ['Image name', 'Titles', 'Description']
    description_path = 'some/path/to/file'
    filenames = ['x', 'y', 'z']
    titles = ['z', 'y', 'x']
    data_list = []
    for i, j in enumerate(filenames):
        combined_dict = dict()
        combined_dict['Image name'] = j
        combined_dict['Titles'] = titles[i]
        data_list.append(combined_dict)
    write_captions_to_excel_file(data_list, description_path, headers)

hope this helps! Feel free to comment on any clarification.

Cheers!