|
我用的是python3,用来处理excel的第三方库是openpyxl,先奉上源文件(1.xlsx)和目标文件(2.xlsx)。
代码如下:
- import re
- from openpyxl import load_workbook
- def add_sheet(sheet_name, title_list, data_dict):
- wb.create_sheet(sheet_name)
- ws = wb[sheet_name]
- ws.append(title_list)
- ln = 1
- for k1, v1 in data_dict.items():
- ln += 1
- ws.cell(row=ln, column=1, value=k1)
- for k2, v2 in v1.items():
- ws.cell(row=ln, column=title_list.index(k2) + 1, value=v2)
- ws.cell(row=ln, column=len(title_list), value=sum(v1.values()))
- ws['A%d' % (ln + 1)] = '合计'
- for k, v in type_dict.items():
- ws.cell(row=ln + 1, column=title_list.index(k) + 1, value=v)
- ws.cell(row=ln + 1, column=len(title_list), value=sum(type_dict.values()))
- wb = load_workbook('1.xlsx')
- wb.copy_worksheet(wb['title0'])
- ws = wb['title0 Copy']
- ws.title = 'Sheet1'
- new_title_list = ['支付日期', '类型', '带宽', '合约期']
- for new_title in new_title_list:
- ws.cell(row=1, column=ws.max_column+1, value=new_title)
- type_dict, city_type_dict, date_type_dict = {}, {}, {}
- for row in ws.iter_rows(min_row=2):
- row[-4].value = row[-5].value[:10]
- name = row[9].value
- if '新装' in name and '续包' in name:
- val = '裸宽'
- elif '升' in name:
- val = '提速'
- elif '融合' in name:
- val = '融合'
- elif '续' in name:
- val = '续包'
- elif '新装' in name:
- val = '新装'
- else:
- val = '其他'
- row[-3].value = val
- type_dict[val] = type_dict[val] + 1 if type_dict.get(val) else 1
- city = row[17].value
- if city_type_dict.get(city):
- if city_type_dict[city].get(val):
- city_type_dict[city][val] += 1
- else:
- city_type_dict[city][val] = 1
- else:
- city_type_dict[city] = {}
- city_type_dict[city][val] = 1
- date = row[-4].value
- if date_type_dict.get(date):
- if date_type_dict[date].get(val):
- date_type_dict[date][val] += 1
- else:
- date_type_dict[date][val] = 1
- else:
- date_type_dict[date] = {}
- date_type_dict[date][val] = 1
- row[-3].value = val
- info = row[10].value
- row[-2].value = re.search(r'\d+M', info).group() if re.search(r'\d+M', info) else '其他'
- row[-1].value = re.search(r'\d+个月', info).group() if re.search(r'\d+个月', info) else '其他'
- title_list = ['归属城市', '裸宽', '其他', '融合', '提速', '新装', '续包', '合计']
- add_sheet('Sheet2', title_list, city_type_dict)
- title_list[0] = '支付日期'
- add_sheet('Sheet3', title_list, date_type_dict)
- wb.save('2.xlsx')
复制代码 |
|