python: add more new row

发布时间 2023-09-14 15:47:34作者: ®Geovin Du Dream Park™

https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_excel.html

def appendDataToExcel(excelName, sheetName, data):
    """
    EXCEL向后行插入数据
    :param excelName: EXCEL文件
    :param sheetName: 工作表名
    :param data: 数据集合
    :return:
    """
    columns = []
    for k, v in data.items():
        columns.append(k)
    df = pd.DataFrame(data, index= None)
    #print(df)
    df_source = None
    if os.path.exists(excelName):
       dfdata = pd.read_excel(excelName, sheet_name=sheetName)
       df_source = pd.DataFrame(dfdata)
    if df_source is not None:
       df_dest = pd.concat([df_source, df], ignore_index=True)# df_source.append(df)
    else:
       df_dest = df
    #print(df_dest)
    df_dest.to_excel(excelName, sheet_name=sheetName, index = False, columns=columns)

  

调用:

    #df1 = pd.read_excel('test.xlsx', sheet_name='person')
    #df2 = pd.DataFrame(df1)
    #print(df2)
    data = {'Name': ['Geovin Du', 'Jack', 'Steve', 'Ricky'], 'Age': [28, 34, 29, 42],
            'City': ['wuhan', 'chongqin', 'beijing', 'shanghai']}
    columns = []
    for k, v in data.items():
        columns.append(k)
    print(columns)
    df3 = pd.DataFrame(data);
    #df2.append(df3)
    print(df3)

    df2 = df3.copy()

    with pd.ExcelWriter('output.xlsx') as writer:
        df3.to_excel(writer, sheet_name='Sheet1')
        df2.to_excel(writer, sheet_name='Sheet2')

    #pd.concat()
    #df2=pd.concat([df2, df3], ignore_index=True)
    #print(df2)
    #df2.to_excel('test.xlsx', sheet_name='person', index=False)  # index false为不写入索引
    appendDataToExcel('test.xlsx', 'person', data)
    data = {'Name': ['Geovin', '涂聚文', '涂聚文', '涂年生'], 'Age': [12, 14, 19, 12],'City': ['深圳', '重庆', '北京','西安']}
    appendDataToExcel('test.xlsx', 'person', data)
    appendDataToExcel('test.xlsx', 'person', data)