使用Python批量转换文件夹中CSV文件为Excel表格时遭遇‘Workbook’对象无‘add_worksheet’属性错误

Hey there, this error is a straightforward mix-up between different Excel library methods! The xlwt.Workbook class doesn't have an add_worksheet() method — that's a method from libraries like xlsxwriter. For xlwt, the correct method to add a worksheet is add_sheet().

Here's your corrected code that should work:

import glob
import xlwt
import csv
import os

dest = r'C:\Users\PycharmProjects\pylenium-test\Results'
# Note: xlwt only supports .xls files, not .xlsx (more on that below)
workbook = xlwt.Workbook()
counter = 0

for csv_file in glob.glob(os.path.join(dest, '*.csv')):
    sheet_name = 'Sheet_' + str(counter)
    counter += 1
    # Replace add_worksheet with xlwt's correct add_sheet method
    worksheet = workbook.add_sheet(sheet_name)
    with open(csv_file, 'rt', encoding='utf8') as f:
        reader = csv.reader(f)
        for r, row in enumerate(reader):
            for c, col in enumerate(row):
                worksheet.write(r, c, col)

# Save as .xls (xlwt can't create .xlsx files without issues)
workbook.save(os.path.join(dest, "Combined.xls"))

A Simpler Alternative for .xlsx Files #

If you need a .xlsx output instead of the older .xls format, or just want cleaner code, using pandas is way more efficient. It handles all the CSV parsing and Excel writing automatically:

import pandas as pd
import glob
import os

dest = r'C:\Users\PycharmProjects\pylenium-test\Results'
csv_files = glob.glob(os.path.join(dest, '*.csv'))

# Use ExcelWriter to combine all CSVs into one workbook
with pd.ExcelWriter(os.path.join(dest, "Combined.xlsx")) as writer:
    for idx, csv_file in enumerate(csv_files):
        # Read CSV into a DataFrame
        df = pd.read_csv(csv_file, encoding='utf8')
        # Write DataFrame to a new sheet
        df.to_excel(writer, sheet_name=f'Sheet_{idx}', index=False)

This approach eliminates the need to manually loop through rows and columns, and it natively supports modern .xlsx files.

内容的提问来源于stack exchange，提问作者Gowri Lakshmi