设置活动工作表,可以用
wb.active = 0
把第一个表设置成活动工作表,但如果是知道工作表名称呢,如何设置,找遍网站也未见,经测试,可以用
wbsheet=book[‘材料汇总’]
book.active=wbsheet
来设置活动工作表
文件名为:
wb = load_workbook(‘山川材料表.xlsx’)
book = load_workbook(tag_file)
设置某个单元格为活动单元格,可以用
wbsheet.sheet_view.selection[0].activeCell = ‘A4’
wbsheet.sheet_view.selection[0].sqref = ‘A4’
book.active=wbsheet # 设置活动工作表
wbsheet.sheet_properties.tabColor = “22BBEF” # 设置标签颜色
wbsheet.sheet_view.selection[0].activeCell = ‘A4’ # 设置活动单元格
wbsheet.sheet_view.selection[0].sqref = ‘A4’
wb = load_workbook('山川材料表.xlsx')
book = load_workbook(tag_file) #能写入已存在表中
writer = pd.ExcelWriter(tag_file, engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
for sheet in wb.sheetnames:
print('读入%s ........'%sheet,flush=True)
data=(pd.read_excel(source_file,sheet_name=sheet,
headers=1,engine='openpyxl',
names=['序号','名称、规格、型号','单位','数量'],index_col=0,skiprows=2,
skipfooter=0,usecols = 'A:D'))
#print(data.head(5))
data1=data[~(data.index.isnull())] #先清洗index为空值的
#连接两个
#totaldata=pd.concat([totaldata,data1],ignore_index=True,sort=True)
totaldata=totaldata.append(data1,sort=False)
huizong=totaldata.groupby(by=['名称、规格、型号','单位']).agg(sum) #按月份分组聚合,求和 一个就用by='月份'
huizong.index.names=['名称、规格、型号','单位']
huizong.reset_index(level=None, drop=False, inplace=True, col_level=0, col_fill="") #level控制了具体要还原的那个等级的索引
#drop为False则索引列会被还原为普通列,否则会丢失
#print(huizong.head(10))
huizong=huizong.reset_index(drop=True,)
huizong.index=huizong.index+1
huizong.to_excel(writer,sheet_name ='材料汇总',startcol=0,startrow=0,index=True,header=True) #header=False表示不要表头
wbsheet=book['材料汇总']
book.active=wbsheet # 设置活动工作表
wbsheet.sheet_properties.tabColor = "22BBEF" # 设置标签颜色
wbsheet.sheet_view.selection[0].activeCell = 'A4' # 设置活动单元格
wbsheet.sheet_view.selection[0].sqref = 'A4'