pandas写入数据到Excel

pandas将dataframe写入Excel

# 大数据写入
split_count = df.shape[0] // 1000000 + 1
for i in range(split_count):
    writer = pd.ExcelWriter("{}.xlsx".format(i),
                            engine='xlsxwriter',
                            options={'strings_to_urls': False})	# 防止URL丢失
    df[i * 1000000:(i + 1) * 1000000].to_excel(writer, header=True, index=False, encoding="utf_8_sig")
    writer.save()

往已经存在的Excel文件里追加数据(写入一个新的sheet)

import pandas as pd

# 往Excel里追加sheet和数据
# mode='a',engine="openpyxl"
writer = pd.ExcelWriter("test.xlsx", mode="a", engine="openpyxl")
data = pd.read_excel('append.xlsx')
data.to_excel(writer,index=False,sheet_name='9')
writer.save()

覆盖Excel文件的某个sheet

# 往Excel写入同名sheet不会覆盖而是创建一个新的sheet，并在sheet名后加一个1
# 先将Excel某个sheet删除在写入同名sheet
import pandas as pd

writer = pd.ExcelWriter('test.xlsx',mode='a',engine='openpyxl')
wb = writer.book
wb.remove(wb['9'])
data = pd.read_excel('append.xlsx')
data.to_excel(writer,sheet_name='9')
writer.save()

往已有sheet里面追加数据

import pandas as pd
from openpyxl import load_workbook
writer = pd.ExcelWriter('test.xlsx',mode='a',engine='openpyxl')
book = load_workbook('test.xlsx')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

data = pd.read_excel('append.xlsx',skiprows=1) # 要添加的数据,skiprows=1 跳过表头，实现数据无缝衔接
for sheet_name in book.worksheets:
    if sheet_name.title =='9':
        data.to_excel(writer,sheet_name=sheet_name.title,startrow=sheet_name._current_row)  # sheet_name._current_row 最下行
writer.save()
writer.close()