代码之家 › 专栏 › 技术社区 › Serdia

如何使用xlsxwriter编写不带数据帧的excel文件

xlsxwriter pandas python-3.x python

Serdia · 技术社区 · 5 年前

如果我的数据框是空的,那么我只需要创建一个空的excel文件并写入其中 "There is no data for selected timeframe "

folder_list = ['San Diego', 'Vista']
if not df.empty:
    # if daraframe is not empty then do this:
    for location, d in df.groupby('OfficeLocation'):
        for folder in folder_list:
            if folder == location: 
                writer=pd.ExcelWriter(f'\\\\my\username\Documents\Python\Split DataFrame by Multiple dataframes\{folder}\{location}.xlsx',engine='xlsxwriter')                  
            else: 
                print('df is empty')
            writer.save()
# if dataframe is empty I need to create empty excel file and wirte in cell A1 "There is no data for seleted timeframe"
else:
    for folder in folder_list:
        # this creates empty file with sheet name 'Sheet1'
        writer=pd.ExcelWriter(f'\\\\my\username\Documents\Python\Split DataFrame by Multiple dataframes\{folder}\{folder}.xlsx',engine='xlsxwriter') 
        wb = writer.book
        ws = writer.sheets['Sheet1'] # this gives me an error
        writer.save()

错误:

Traceback (most recent call last):
  File "\\my\username\Documents\Python\Split DataFrame by Multiple dataframes\tempCodeRunnerFile.py", line 41, in <module>
    ws = writer.sheets['Sheet1'] # this gives me an error
KeyError: 'Sheet1'

2 回复 | 直到 5 年前

r.ook jpp 5 年前

您应该添加新工作表:

for folder in folder_list:
    # this creates empty file with sheet name 'Sheet1'
    writer=pd.ExcelWriter(f'\\\\my\username\Documents\Python\Split DataFrame by Multiple dataframes\{folder}\{folder}.xlsx', engine='xlsxwriter') 
    wb = writer.book
    ws = wb.add_worksheet() # change to this
    writer.save()

请参阅此处的相关文档: https://xlsxwriter.readthedocs.io/workbook.html#add_worksheet

如果要使用默认值 openpyxl 发动机,则相应的方法是:

ws = wb.create_sheet()

Florian Bernard 5 年前

为什么你不能像这样在同一个循环中创建它。

folder_list = ['San Diego', 'Vista']

# if daraframe is not empty then do this:
for location, d in df.groupby('OfficeLocation'):
    for folder in folder_list:
        if folder == location:
           if not df.empty: 
                writer=pd.ExcelWriter(f'\\\\my\username\Documents\Python\Split DataFrame by Multiple dataframes\{folder}\{location}.xlsx',engine='xlsxwriter')                  
            else:
                writer=pd.ExcelWriter(f'\\\\my\username\Documents\Python\Split DataFrame by Multiple dataframes\{folder}\{folder}.xlsx',engine='xlsxwriter') 
                df_empty = pd.DataFrame({"There is no data for seleted timeframe" : []})
                df_empty.to_excel(writer, index=False)
                print('df is empty')
            writer.save()

但是用你的方法你每次都会创建一个新文件。