它对我有效
,可以保留所有数据和样式格式。import xlwings as xw
import pandas as pd
#create DF
df = pd.DataFrame([[7,2,3],[1,2,3]], columns=list('ABC'))
#load workbook
app = xw.App(visible=False)
wb = xw.Book('doc.xlsx')
ws = wb.sheets['Sheet1']
#Update workbook at specified range
ws.range('A2').options(index=False).value = df
#Close workbook
wb.save()
wb.close()
app.quit()
[结果]
将数据框复制到特定的 Excel 工作表 Sheet1
的特定位置单元格 A2
,而不会丢失任何信息。在此示例中,图表会自动更新,引用 A2:C4
。
openpyxl
来避免操作系统特定性。 - Vitalizzaredf.to_excel(writer, sheet_name='Sheet1', header=None, index=False,
startcol=1, startrow=2)
需要相应地更新sheet_name
因此,您的整个代码可能如下所示:
import pandas as pd
from openpyxl import load_workbook
fn = r'C:\YourFolder\doc.xlsx'
book = load_workbook(fn)
df = pd.DataFrame([[1,2,3],[1,2,3]], columns=list('ABC'))
writer = pd.ExcelWriter(fn, engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df.to_excel(writer, sheet_name='Sheet1', header=None, index=False,
startcol=1, startrow=2)
writer.save()
让我们来看看这个工作流程:
import pandas as pd
import openpyxl
# create a dummy data frame
df = pd.DataFrame({
'one': [*'abcd'],
'two': [*'wxyz'],
})
# write data into a new file
file_name = 'test_openpyxl.xlsx'
df.to_excel(file_name)
# NOTE: stop here and see the data in Excel,
# close the file before continuing
# create a writer with parameters mode and if_sheet_exists
writer = pd.ExcelWriter(
path=file_name,
engine='openpyxl',
mode='a', # append data to the file
if_sheet_exists='overlay' # keep previous data on existing sheets
)
# update data and write changes to the file
df['two'] = df['two'].str.upper()
df.to_excel(
writer, # use openpyxl writer
sheet_name='Sheet1', # to update data on the existing sheet
columns=['two'], # in this only columns
header=False, # with no headers
index=False, # and no index printed
startrow=1, # at this specific position
startcol=2, # with rows and cols numbering from 0
)
writer.close() # file is saved by default when close
此方法在Linux和Windows上同样适用。请注意,xlsxwriter
不支持追加模式,因此我们必须使用openpyxl
来更新现有数据。最好避免同时在Python和Excel中处理同一个文件。
关键参数是if_sheet_exists
与mode='a'
,可以是以下之一:
error
引发ValueError错误。new
创建一个新的工作表,其名称由引擎确定。replace
在写入之前删除工作表的内容。overlay
将内容写入现有工作表而不删除旧内容。您可以检查给定的工作表是否存在,然后删除现有的工作表并添加一个新的。
import xlwings as xw
def df_to_excel_util(excel,sheet_to_dataFrame_map):
with xw.App(visible=False) as app:
wb = app.books.open(excel)
current_sheets = [sheet.name for sheet in wb.sheets]
for sheet_name in sheet_to_dataFrame_map.keys():
if sheet_name in current_sheets:
wb.sheets[sheet_name].delete()
new_sheet = wb.sheets.add(after=wb.sheets.count)
new_sheet.range('A1').value = sheet_to_dataFrame_map.get(sheet_name)
new_sheet.name = sheet_name
wb.save()
你可以使用pd.read_excel()
读取Excel文件,将第一行复制到你的DataFrame中,并将DataFrame保存为同名的Excel文件。