import pandas as pd
import xlwt,os,time
import selectUsecols as su
import mergeExcel as me
workbook = xlwt.Workbook(encoding = 'ascii')
worksheet = workbook.add_sheet('My Worksheet',cell_overwrite_ok=True)
font = xlwt.Font() # 为样式创建字体
font.name = '宋体'
worksheet.col(0).width = 4444 # 设置单元格宽度
worksheet.col(9).width = 8888
worksheet.col(17).width = 8888
worksheet.col(16).width = 3333
worksheet.col(4).width = 3333
worksheet.col(15).width = 3800
worksheet.row(0).height_mismatch=True
worksheet.row(0).height=20*64#20*n,20时基准数,n
font.bold = False # True黑体
alignment = xlwt.Alignment()# 设置单元格对齐方式
alignment.horz = 0x02# 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
alignment.vert = 0x01# 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)
alignment.wrap = 1# 设置自动换行
borders = xlwt.Borders()# 设置边框
borders.left = 1# 细实线:1,小粗实线:2,细虚线:3,中细虚线:4,大粗实线:5,双线:6,细点虚线:7
borders.right = 1# 大粗虚线:8,细点划线:9,粗点划线:10,细双点划线:11,粗双点划线:12,斜点划线:13
borders.top = 1
borders.bottom = 1
style = xlwt.XFStyle() # 初始化样式
style.font = font # 设定样式
style.alignment=alignment
style.borders=borders
pattern = xlwt.Pattern()# 设置背景颜色
pattern.pattern = xlwt.Pattern.SOLID_PATTERN# 设置背景颜色的模式
pattern.pattern_fore_colour = 40# 背景颜色
font2 = xlwt.Font() # 为样式创建字体
font2.name = '宋体'
font2.bold = True # True黑体
font2.colour_index = 0x09
# font2.height = 20 * 11# 字体大小,11为字号,20为衡量单位
style2 = xlwt.XFStyle() # 初始化样式
style2.font = font2 # 设定样式
style2.alignment=alignment
style2.borders=borders
style2.pattern=pattern
font1 = xlwt.Font() # 为样式创建字体
font1.name = '微软雅黑'
datastyle = xlwt.XFStyle()
datastyle.num_format_str = 'yyyy-mm-dd'#设置输出格式为日期
datastyle.borders=borders
datastyle.alignment=alignment
datastyle.font=font1
datastyle1 = xlwt.XFStyle()
datastyle1.num_format_str = '#%'#设置输出格式为百分比
datastyle1.alignment=alignment
datastyle1.borders=borders
worksheet.write(0,0,'序号',style2)
worksheet.write(0,1,'参与人姓名',style2)
worksheet.write(0,2,'工作日期',style2)
worksheet.write(0,3,'项目类型',style2)
worksheet.write(0,4,'报市场项目工时占比及报产品线类型',style2)
worksheet.write(0,5,'日期',style2)
worksheet.write(0,6,'类型',style2)
worksheet.write(0,7,'工时',style2)
worksheet.set_panes_frozen('1')# 设置冻结窗口/设置冻结为真
worksheet.set_horz_split_pos(1)# 水平冻结
worksheet.set_vert_split_pos(0)# 垂直冻结
test=r'D:\cm\20201227\市场研发分配\处理用表.xlsx'
data=pd.read_excel(test)
zhengchang=su.hee(test,[1])
data_time = time.strftime('%Y%m%d', )
for name in zhengchang:
os.makedirs('D:\\cm\\20201227\\市场研发分配\\新建文件夹\\'+name)
zheng=data[data['参与人姓名']==name]
write=pd.ExcelWriter('D:\\cm\\20201227\\市场研发分配\\新建文件夹\\'+name+'\\zhongjian.xlsx')
zheng.to_excel(write,sheet_name='Sheet1',index=False)
write.save()
write.close()
data1=pd.read_excel(write)
zhengchang1 = su.hee(write, [5])
for q in zhengchang1:
os.makedirs('D:\\cm\\20201227\\市场研发分配\\新建文件夹\\' + name+'\\'+str(q))
print(name)
print(q)
zheng_list=data1[data1['日期']==q]
hetong = zheng_list.values.tolist()
n = len(hetong)
print(n)
qq=int(n*0.47)
print(qq)
aq = 0
for ii in range(n):
worksheet.write(ii + 1, 0, hetong[ii][0], style)
worksheet.write(ii + 1, 1, hetong[ii][1], style)
worksheet.write(ii + 1, 2, hetong[ii][2], style)
worksheet.write(ii + 1, 3, hetong[ii][3], style)
worksheet.write(ii + 1, 4, hetong[ii][4], datastyle)
worksheet.write(ii + 1, 5, hetong[ii][5], style)
for qqq in range(qq):
if aq>qqq:
worksheet.write(ii + 1, 6, '研发', style)
else:
worksheet.write(ii + 1, 6, '市场', style)
aq =aq+1
worksheet.write(ii + 1, 7, str(hetong[ii][6]), style)
workbook.save('D:\\cm\\20201227\\市场研发分配\\新建文件夹\\' + name+'\\'+str(q)+'\\'+str(q)+'.xlsx')
for nn in range(n):
worksheet.write(nn + 1, 0, None, style)
worksheet.write(nn + 1, 1, None, style)
worksheet.write(nn + 1, 2, None, style)
worksheet.write(nn + 1, 3, None, style)
worksheet.write(nn + 1, 4, None, style)
worksheet.write(nn + 1, 5, None, style)
worksheet.write(nn + 1, 6, None, style)
worksheet.write(nn + 1, 7, None, style)
os.remove(write)
me.hlo('D:\\cm\\20201227\\市场研发分配\\新建文件夹','D:\\cm\\20201227\\市场研发分配\\新建文.xlsx')
selectUsecols函数:
import pandas as pd
def hee(path,b:[int]):
wb=pd.read_excel(path,usecols=b)
wbb=wb.values.tolist()
result=[]
for bbb in wbb:
result.append(bbb[0])
# print(set(result))
# bbb=pd.unique(result)
bbb=list(set(result))
return bbb
mergeExcel函数:
import os
import pandas as pd
import numpy as np
def hlo(path,path1):
dir = path#设置工作路径
#新建列表,存放文件名(可以忽略,但是为了做的过程能心里有数,先放上)
filename_excel = []
#新建列表,存放每个文件数据框(每一个excel读取后存放在数据框)
frames = []
for root, dirs, files in os.walk(dir):#os.walk(dir)在目录树中游走输出在目录中的文件名,, 返回的是一个三元组(root,dirs,files)
# root所指的是当前正在遍历的这个文件夹的本身的地址
# dirs是一个list ,内容是该文件夹中所有的目录的名字(不包括子目录)
# files同样是list, 内容是该文件夹中所有的文件(不包括子目录)
for file in files:
#print(os.path.join(root,file))
filename_excel.append(os.path.join(root,file))#os.path.join连接括号内的两个路径
df = pd.read_excel(os.path.join(root,file)) #excel转换成DataFrame
frames.append(df)
#合并所有数据
result = pd.concat(frames,axis=0)
#查看合并后的数据
bb=result.head()
cc=result.shape
result.to_excel(path1,index = False)#保存合并的数据到电脑D盘的merge文件夹中,并把合并后的文件命名为a12.csv