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

循环写入每张工作表而不擦除以前的数据

ordereddict xlsxwriter openpyxl dataframe pandas

0

machukovich · 技术社区 · 1 年前

我有一个 ordereddict 我实现了将其传输到具有多个密钥的新excel文件。它的每个键都对应于excel文件中的一张表。我想编写一个循环,使我能够在文件中所有工作表的A1位置上书写。

下面的代码写下了我的 有序的判决 在一个新的excel文件中:

# Importing modules
import openpyxl as op
import pandas as pd
import numpy as np
import xlsxwriter
from openpyxl import Workbook, load_workbook

# Defining my file
my_file = r'\machukovich\Desktop\stack.xlsx'

# Loading the file into a dictionary of Dataframes
dfs_my_file = pd.read_excel(my_file, sheet_name=None, skiprows=2)

# The path of the new file I wish to write on
my_new_path = r'\machukovich\Desktop\new.xlsx'

# At this point I have made a few modifications in dfs_my_file which are non important to the core of this question.

# Create a Pandas Excel writer using XlsxWriter as the engine.

with pd.ExcelWriter(my_new_path, engine="xlsxwriter") as writer:
    for sheet_name, df in dfs_my_file.items():
            df.to_excel(writer, sheet_name=sheet_name, startrow=6, index=False)

# Close the Pandas Excel writer and output the Excel file.
writer.close()    
writer.save()

我的 数据集 对于 dfs_my_file文件 (有序字典):

{'Sheet_1':     ID      Name  Surname  Grade
 0  104  Eleanor     Rigby      6
 1  168  Barbara       Ann      8
 2  450    Polly   Cracker      7
 3   90   Little       Joe     10,
 'Sheet_2':     ID       Name   Surname  Grade
 0  106       Lucy       Sky      8
 1  128    Delilah  Gonzalez      5
 2  100  Christina   Rodwell      3
 3   40      Ziggy  Stardust      7,
 'Sheet_3':     ID   Name   Surname  Grade
 0   22   Lucy  Diamonds      9
 1   50  Grace     Kelly      7
 2  105    Uma   Thurman      7
 3   29   Lola      King      3}

我尝试使用以下代码( 我不知道如何在excel文件的每张表格中都写下下面的字符串 )以下为:

# Defining workbook and worksheet

workbook = xlsxwriter.workbook
worksheet = writer.sheets[sheet_name]

# I tried with this iteration 

with pd.ExcelWriter(my_new_path, engine="xlsxwriter") as writer:
        for sheet_name, df in dfs_my_file():
            worksheet.write('A1', 'RANDOM TEXT 1')
            worksheet.write('A2', 'RANDOM TEXT 2')

但我最终还是犯了下面的错误,没有理解它的起源。

KeyError                                  Traceback (most recent call last)
~\AppData\Local\Temp/ipykernel_18328/702301548.py in <module>
      1 workbook = xlsxwriter.workbook
----> 2 worksheet = writer.sheets[sheet_name]
      3 
      4 with pd.ExcelWriter(my_new_path, engine="xlsxwriter") as writer:
      5         for sheet_name, df in dfs_my_file():

KeyError: 'Sheet_1'

以前有人经历过这个错误吗?你能帮我写excel文件吗?当做

0 回复 | 直到 1 年前