如何根据列表导入多个csv文件，并将其附加到多个工作表中

Question

我有多个CSV文件要导入到多个以相同的CSV文件命名的工作表中。

但是，我在创建/追加多个工作表时有困难。

如果我使用ExcelWriter(pathDestination, mode = 'a')，则会出现FileNotFoundError。如果我使用ExcelWriter(pathDestination)，那么只会在工作表中创建最后一个CSV文件。

在执行to_excel时，如何改进代码而不需要列出每个csvpath?

import openpyxl
import numpy as np
import pandas as pd 
import os
pathDestination = 'Downloads/TemplateOne.xlsx'
csvpathI = '2019_27101220_Export.csv'
csvpathII = '2019_27101220_Import.csv'
csvpathIII = '2020_27101220_Export.csv'
csvpathIV = '2020_27101220_Import.csv'
csvpath_list = [csvpathI, csvpathII, csvpathIII, csvpathIV]
for csvpath in csvpath_list:
df = pd.read_csv(csvpath)

conversion_unit = 1000
supplymentry_conversion_unit = 1000
df['quantity_converted'] = np.multiply(df['Quantity'],conversion_unit)
df['supplimentry_quantity_converted'] = np.multiply(df['Supplimentary Quantity'],conversion_unit)
csvnames = os.path.basename(csvpath).split(".")[0]

with pd.ExcelWriter(pathDestination) as writer:
df.to_excel(writer, sheet_name = csvnames, index = False)`

Answer 1

您需要将循环放在上下文管理器中，以便将每个(df)保存到单独的工作表中。

Try this:

conversion_unit = 1000
supplymentry_conversion_unit = 1000
with pd.ExcelWriter(pathDestination) as writer:
for csvpath in csvpath_list:
df = pd.read_csv(csvpath)
df['quantity_converted'] = df['Quantity'].mul(conversion_unit)
df['supplimentry_quantity_converted'] = df['Supplimentary Quantity'].mul(supplymentry_conversion_unit)
df.to_excel(writer, sheet_name = csvpath.split(".")[0], index = False)