如何使用Python将多个CSV文件合并到一个具有不同工作表的Excel文件中 [英] How do I combine multiple CSV files into one excel files with different sheets using Python

问题描述

我正在尝试将多个csv文件合并到一个excel文件中，从而每个文件都是xls文件中自己的工作表.

I am trying to combine multiple csv files into one excel file whereby each file is it’s own sheet in the xls file.

下面是一个Python脚本，可以将文件夹中的所有csv文件转换为相应的excel文件.

Below is a python script that can convert all csv files in a folder into there respective excel files.

   import os
   import glob
import csv
from xlsxwriter.workbook import Workbook
"""with open('output.csv', "rt", encoding = 'UTF-8') as fin:
    with open('outputconvert.csv', "wt", encoding = 'UTF-8') as fout:
        for line in fin:
            fout.write(line.replace(';',','))"""

for csvfile in glob.glob(os.path.join('.', '*.csv')):
    workbook = Workbook(csvfile[:-4] + '.xlsx')
    worksheet = workbook.add_worksheet('testws')
    with open(csvfile, '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)
    workbook.close()

它可以正常工作，但是有一种方法可以扩展它，使其可以将文件合并到一个文件中，并且每个文件都在单独的工作表中

It works fine, but is there a way I can expand it such that it can merge files into one file and each file is in a separate worksheet

预先感谢

推荐答案

参数sheet_name是常量.若要导出到多个工作表，您需要为每个工作表指定一个不同的名称.这是经过测试的解决方案:

The parameter sheet_name is constant. To export to multiple worksheets, you need to specify a different name for each worksheet. This is the tested solution:

workbook = Workbook('../your_path/joined.xlsx')
counter = 0
for csv_file in glob.glob(os.path.join('../your_path/', '*.csv')):
    sheet_name = 'Sheet_' + str(counter)
    counter += 1
    worksheet = workbook.add_worksheet(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)
workbook.close()

这篇关于如何使用Python将多个CSV文件合并到一个具有不同工作表的Excel文件中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！