python - 如何将DataFrame保存到xlsx文件中的Sheet2中

Question

我使用 Pandas 创建了一个简单的 DataFrame，我需要将其添加/替换到我的 Xlsx 文件中的 Sheet2。 Xlsx 文件中的工作表为:Sheet1、Sheet2我有两个问题:

首先，即使我指定了sheetName，它所做的只是删除数据库中已有的所有工作表，并仅创建一个具有数据指定名称的工作表。

第二是我无法从数据框中删除列索引。我尝试添加“index_col=None”，但它所做的只是导致错误。

到目前为止的代码:

import pandas as pd
from pandas import ExcelWriter
from pandas import ExcelFile
import numpy as np

Number1 = '123456'
Number2 = '987654'
Number3 = '888888'
Comp = 'HelloAll'
excel_file = 'C:/Users/username/Desktop/testFile.xlsx'

data = {'Number1':  [Number1],
        'Number2':  [Number2],
        'Number3':  [Number3],
        'Comp':  [Comp]
        }

df = pd.DataFrame (data, columns = ['Number1','Number2', 'Number3', 'Comp'])
pd.set_option('display.max_columns', None)
print (df)
with ExcelWriter(excel_file) as writer:
    df.to_excel(writer, sheet_name='Sheet2')

预期结果:

Number1 Number2 Number3 Comp
123456  987654  888888  HelloAll

Answer 1

1) 为了保留文件的表，您需要做的第一件事就是导入所有文件，然后重新写入它们。对我来说有效的一种方法是使用 xlsxwriter:

import pandas as pd

# Read the 1st sheet and import it as first dataframe
#df1 = pd.read_excel('testFile.xlsx', sheet_name='Sheet1')
df1 = pd.DataFrame({'A': [1,2,3], 'B': [4,5,6]}) # for this example i created a simple df

# Read the 2nd sheet and import it as second dataframe
#df2 = pd.read_excel('testFile.xlsx', sheet_name='Sheet2')

# Do some data manipulation
Number1 = '123456'
Number2 = '987654'
Number3 = '888888'
Comp = 'HelloAll'

data = {'Number1': [Number1],
        'Number2': [Number2],
        'Number3': [Number3],
        'Comp': [Comp]}

df2 = pd.DataFrame (data, columns = ['Number1','Number2', 'Number3', 'Comp'])

# Kickstart the xlsxwriter
writer = pd.ExcelWriter('testFile.xlsx', engine='xlsxwriter')
df1.to_excel(writer, sheet_name='Sheet1', index=False)
df2.to_excel(writer, sheet_name='Sheet2', index=False)

# Finally write the file
writer.save()

2)关于第二个问题，只需在编写文件时使用参数index=False即可。