1. 删除一行数据
# 读取后drop
data = pd.read_excel("data.xlsx", sheetname=sheet_name)
mydata = data.drop([0], axis=0)
2. 删除sheet
def add_excel_stay(excel_name, sheet_name, df):
writer = pd.ExcelWriter(excel_name, engine='openpyxl')
book = load_workbook(writer)
writer.book = book
sheet_list = book.sheetnames
# 更新现有的sheet
if sheet_name in sheet_list:
book.remove(book.worksheets[sheet_list.index(sheet_name)])
df.to_excel(excel_writer=writer, sheet_name=sheet_name)
writer.save()
writer.close()
3.for 循环写入不同sheet
writer = pd.ExcelWriter(excel_name, engine='openpyxl')
sheet_name = ['全部', '新用户']
for sheet in sheet_name:
data = pd.read_excel(excel_name, sheet_name=sheet)
if mktime(yesterday)- mktime2(data.loc[0,'日期'])>1109500.0: #1209600.0 14天
data.drop([0],inplace=True) #删除第一行
row=user_arpu(yesterday, today, yesterday_) #list
a=pd.DataFrame(row,index=data.columns).T # new dataframe
data=data.append(a) #dataframe添加一行
data.to_excel(writer,sheet_name=sheet,index=None)
writer.save() #保存
tips:3中没有sheet_name[] 赋值的不会被保存,需要重新写入
4. 加载excel并写入数据
4.1 list of list格式
import openpyxl
def get_excel2(use_data, use_title, sheet_title, excel_name):
#use_data list of list
#use_title columns
#sheet_name sheet
#excel_name name
new = openpyxl.load_workbook(excel_name) #加载文件
sheet = new.create_sheet(title=sheet_title)
for col in range(len(use_title)):
_ = sheet.cell(row=1, column=col + 1, value=u'%s' % use_title[col])
for row in range(len(use_data)):
for col in range(len(use_title)):
_ = sheet.cell(row=row + 2, column=col + 1, value=u'%s' % use_data[row][col])
newexcel = new.save(excel_name)
return newexcel
4.2 DataFrame格式
writer = pd.ExcelWriter(excel_name, engine='openpyxl')
book = load_workbook(writer)
writer.book = book
df = buy_stay() #留存df
df.to_excel(excel_writer=writer, sheet_name='收购用户留存')
writer.save()
writer.close()