How to add a new work sheet to work book in xlsxwriter

Question

I want to add some sheets to one workbook.

sheets = ["A.csv", "B.csv", "C.csv"]
for sh in sheets:
    workbook = xlsxwriter.Workbook('myxlsx.xlsx')
    worksheet = workbook.add_worksheet(sh)
    worksheet.write(1,1,"abcd")
    workbook.close()

But what it does is it only creates a sheet corresponding to "C.csv" and not to "A.csv" and "B.csv" From what I have got, it is because every time it loops it creates a new workbook. I want to create the 3 sheets on same workbook.

Also, there is one condition, I want to initialise the workbook constructor inside the loop only.

Semiautomatic

Why do you need to initialize the workbook constructor inside the loop? – Sexagenarian

Answer 1

Here is a sample code. the workbook constructor needs to be created outside of the for loop and it does what you are looking for!

Input csv files:

SAMPLE CODE

import os
import glob
import xlsxwriter
import csv

flist = [os.path.basename(x) for x in glob.glob(os.getcwd() + '\\*.csv')]

workbook = xlsxwriter.Workbook('split_book.xlsx')

for sh in flist:
    worksheet = workbook.add_worksheet(sh)
    with open(sh, 'rb') as f:
        reader = csv.reader(f)
        for r, row in enumerate(reader):
            for c, col in enumerate(row):
                worksheet.write(r, c, col)

workbook.close()

OUTPUT

Sexagenarian

Answer 2

Fix your code to look like this with the .close() outside of the forloop

sheets = ["A.csv", "B.csv", "C.csv"]
for sh in sheets:
   workbook = xlsxwriter.Workbook('myxlsx.xlsx')
   worksheet = workbook.add_worksheet(sh)
   worksheet.write(1,1,"abcd")
workbook.close() # should be outside the for loop

Idiotism