单个excel中的多个选项卡

Question

我正在使用下面的代码创建一个带有多个选项卡的excel，该选项卡基于路径上的csv文件。我的路径中有两个文件。因此，与其在一个excel中获得两个选项卡，不如在一个选项卡中获得一个空白选项卡。请帮我修复这个代码

import os
import glob
import xlsxwriter
import csv
import pandas
path='/axp/buanalytics/csgsn/dev/GSN/VGEN_Files/Test/Tulu/VG/Data/'
flist = [os.path.basename(x) for x in glob.glob(os.getcwd() + '/axp/buanalytics/csgsn/dev/GSN/VGEN_Files/Test/Tulu/VG/Data/*.csv')]

workbook = xlsxwriter.Workbook('/axp/buanalytics/csgsn/dev/GSN/VGEN_Files/Test/Tulu/VG/Data/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()

Answer 1

使用pandas读取文件并合并所有文件

import os
import pandas as pd

csv_names = [files for files in os.listdir("Your Directory/")]    #get names of csv files in directory "Directory/"

writer = pd.ExcelWriter('Multiple Workbooks.xlsx', engine='xlsxwriter')

for files in csv_names:
    df = pd.read_csv(os.path.join("Your Directory",files)) #read csv file
    filename = files[:-4]                                  #remove ".csv" from filename
    df.to_excel(writer, sheet_name=filename)              #add to workbook

writer.save()

简而言之，您可以使用将选项卡添加到数据帧中

writer = pd.ExcelWriter('Multiple Workbooks.xlsx', engine='xlsxwriter')
df1.to_excel(writer, sheet_name="SheetName")
df2.to_excel(writer, sheet_name="SheetName2")

Answer 2

三个问题：

1）flist=[glob.glob（os.getcwd（）+'/axp/buanalytics/csgsn/dev/GSN/VGEN_Files/Test/Tulu/VG/Data/*.csv'）中x的os.path.basename（x）]

假设os.getcwd（）与您的路径相同，您将得到两次路径名。这意味着flist将为空。既然您已经经历了设置路径的麻烦，为什么不

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

2）同上

workbook = path + 'split_book.xlsx'

3）文件应作为文本文件打开

with open(sh, 'r') as f

尝试一下，你的程序应该会工作。你不需要熊猫来做这件事，是为了以后吗