在现有内容下显示

Question

def test(sheet, row=2):

    writer = pd.ExcelWriter("testing.xlsx", engine='openpyxl')

    df1 = pd.DataFrame(['Title'], index=[0])
    df2 = pd.DataFrame({'user': ['Bob', 'Jane', 'Alice'], 
                       'income': [40000, 50000, 42000]})

    df1.to_excel(writer, sheet, startrow=0, startcol=0, header=None, \
                 index=False)
    df2.to_excel(writer, sheet, startrow=row, index=False)

    writer.save()
    writer.close()

test('aaa')

现在，这个小函数创建了一个Excel工作表。你知道吗

描述：

假设存在Excel工作表。我想用下面的代码在现有行下面写些别的东西。你知道吗

In [1]: import pandas as pd

In [2]: writer = pd.ExcelWriter("testing.xlsx", engine='openpyxl')

In [4]: from openpyxl import load_workbook

In [5]: book = load_workbook("testing.xlsx")

In [6]:     df3 = pd.DataFrame({'amount': ['Chest', 'Bras', 'Braa'], 
   ...:                        'income': [40000, 50000, 42000]})
   ...: 

In [8]: writer.book = book

In [9]: ws = book.get_sheet_by_name('aaa')

In [10]: writer.book
Out[10]: <openpyxl.workbook.workbook.Workbook at 0x7f9add4e3048>

In [12]: writer.book.sheetnames
Out[12]: ['aaa']

In [13]: row = ws.max_row

In [14]: row
Out[14]: 6

In [15]: df3.to_excel(writer, "aaa", startrow=row + 2, index=False)

In [16]: writer.save()

这个代码几乎按我想要的方式工作。它将创建一个新的工作表aaa1，并将df的内容按正常方式放在第8行。我希望aaa1的内容低于aaa的内容，而不是创建新的表aaa1。我怎样才能修好它呢？你知道吗

Answer 1

不幸的是，如果你只是使用pandas.DataFrame.to\ u excel. 如果你愿意在第二部分使用openpyxl，我有一个解决方案。我使用熊猫样式来格式化单元格，就像格式化第一部分一样。你知道吗

import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows

writer = pd.ExcelWriter("testing.xlsx", engine='openpyxl')

book = load_workbook("testing.xlsx")

df3 = pd.DataFrame({'amount': ['Chest', 'Bras', 'Braa'], 
                'income': [40000, 50000, 42000]})

writer.book = book
ws = book['aaa']

row = ws.max_row

#best way to replicate your insertion of 2 blank rows which 
ws.append(tuple())
ws.append(tuple())

for r in dataframe_to_rows(df3, index=False, header=True):
    ws.append(r)

#a quick way to give you the same style as you get with the first part
for row in ws.iter_rows(min_row=row +3, max_col=2, max_row=row +3):
    for cell in row:
        cell.style = 'Pandas'

writer.save()

对于所需的输出：