python对xlsx不同sheet的操作

• 新建多个空白xlsx

import pandas as pd

df = pd.DataFrame()
file_list = ['file1','file2','file3']
for f in file_list:
	df.to_excel(f+'.xlsx')

• 新建多个sheet的xlsx

import pandas as pd

df = pd.DataFrame()
sheet_list = ['sheet1','sheet2','sheet3']
writer = pd.ExcelWriter(f+'.xlsx')
for s in sheet_list:
   df.to_excel(writer,s)
writer.save()
writer.close()

• 新建多个含多个sheet的xlsx

import pandas as pd

df = pd.DataFrame()
file_list = ['file1','file2','file3']
sheet_list = ['sheet1','sheet2','sheet3']
for f in file_list:
	writer = pd.ExcelWriter(f+'.xlsx')
	for s in sheet_list:
		df.to_excel(writer,s)
	writer.save()
	writer.close()

• 为已存在xlsx添加空白sheet

import openpyxl

wb = openpyxl.load_workbook(r'file1.xlsx')
wb.create_sheet(title = 'Sheet2')
wb.save(r'file1.xlsx')

• 为已存在xlsx添加sheet

import pandas as pd

old_df = pd.read_excel('file1.xlsx', sheet_name = None)
new_df = pd.DataFrame()
writer = pd.ExcelWriter('file1.xlsx')
for s in old_df:
	old_df[s].to_excel(writer, sheet_name = s)
new_df.to_excel(writer, sheet_name = 'sheet2')
writer.save()
writer.close()

• 读取xlsx中指定sheet的内容

import pandas as pd

data_xls = pd.io.excel.ExcelFile('file1.xlsx')
df = pd.read_excel(data_xls, sheetname = 'sheet1')

• 读取xlsx中的多个sheet的名称及内容

import pandas as pd

data_xls = pd.io.excel.ExcelFile('file1.xlsx')
data = {}
name = data_xls.sheet_names()
for n in name:
	df = pd.read_excel(data_xls, sheetname = n)
	data[n] = df

本文地址：https://blog.csdn.net/wanancat/article/details/110452538