openpyxl库简单操作

import pandas as pd
import numpy as np
import re
import xlsxwriter
import openpyxl
#pd.options.display.max_columns = None
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)



wb1 = openpyxl.Workbook()
data_m = pd.read_excel('.xlsx',sheet_name = 0)
row = data_m.shape[0]
col = data_m.shape[1]
for i in range(6):
    wb1.create_sheet(str(i))#创建sheet
wb1.remove(wb1['Sheet'])#删除sheet
wb1.save('.xlsx')#此处保存，否则后面的读写操作会报错
data_chucun = []
for i in range(2004,2019):
    data_c = pd.read_excel('.xlsx',sheet_name = str(i))
    data_chucun.append(data_c)
#print(data_chucun[1])
for m in range(0,row):
    for n in range(3,col):
        data_list = pd.DataFrame(np.zeros((15,1)),columns = ['待插值'])
    #    print(data_list)
        for i in range(2004,2019):
            data = data_chucun[i-2004]
            a = data.iloc[m,n]
            a = str(a)
            a = a.replace(' ','')
            data_list.iloc[i-2004,0] = float(a)
    #    print(data_list)
        data_list.fillna(data_list.interpolate()) #等差插值
        data_list.fillna(method='backfill',inplace=True) #后向插值
        data_list.fillna(method='ffill',inplace=True) #前向插值
        print(m,'\t',n,'\t','{}%'.format((m-1)*(n-3)/((row * col))*100))
        for u in range(2004,2019):
            wb1[str(u)].cell(m+1,n+1).value = data_list.iloc[u-2004,0]
wb1.save('.xlsx')