How to put/stream data into an Excel file on sftp

Question

What works

With the following code, I can write the content of TheList into a CSV on an SFTP.

import paramiko
import csv

# code part to make and open sftp connection

TheList = [['name', 'address'], [ 'peter', 'london']]

with sftp.open(SftpPath + "anewfile.csv", mode='w', bufsize=32768) as csvfile:
    writer = csv.writer(csvfile, delimiter=',')
    filewriter.writerows(TheList)

What doesn't work

With the following code, the Excel file is created on the SFTP, but it is empty. What is false?

import paramiko
import xlsxwriter

# code part to make and open sftp connection

TheList = [['name', 'address'], [ 'peter', 'london']]

with sftp.open(SftpPath + "anewfile.xlsx", mode='wb', bufsize=32768) as f:
    workbook = xlsxwriter.Workbook(f)
    worksheet = workbook.add_worksheet()
    for row_num, data in enumerate(TheList):
        worksheet.write_row(row_num, 0, data)

Answer 1

You need to close the Workbook. Either using the with statement:

with sftp.open(SftpPath + "anewfile.xlsx", mode='wb', bufsize=32768) as f, \
     xlsxwriter.Workbook(f) as workbook:
    worksheet = workbook.add_worksheet()
    for row_num, data in enumerate(TheList):
        worksheet.write_row(row_num, 0, data)

Or call Workbook.close explicitly:

workbook.close()