读取excel dataframe split 写入excel

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
import pandas as pd
import os
import copy
import shutil
import re
import xlrd, xlwt
from xlutils.copy import copy as xl_copy
from openpyxl import load_workbook
import numpy as np
def getInfoOfDigtalChannel(path):
# 去除首字母D， =.=
getInfo = pd.read_excel(path, sheet_name=Digital, dtype=str, keep_default_na=False, header=1)
info = pd.DataFrame(getInfo.iloc[:, :])
if info.empty:
print('file is empty')
else:
for i in range(0, info.keys().size):
if re.search('DUT.*Channel', info.keys()[i]):
for j in range(0, len(info)):
# print(re.split('^D',str(info.iat[j, i]))[-1])
info.iat[j, i] = re.split('^D', str(info.iat[j, i]))[-1]
# print(info.iat[j, i])
fileTo = os.path.join(OutputPath, 'channel_map_'+Digital+'.xlsx')
info.to_excel(fileTo, header=1, index=False, encoding='utf-8', sheet_name=Digital)
def getInfoOfDPSChannel(path):
# 去除首字母D， =.=
getInfo = pd.read_excel(path, sheet_name=Power, dtype=str, keep_default_na=False, header=1)
info = pd.DataFrame(getInfo.iloc[:, :])
if info.empty:
print('file is empty')
else:
print('file is not empty')
for i in range(0, info.keys().size):
if re.search('DUT.*Channel', info.keys()[i]):
for j in range(0, len(info)):
m = re.split('_P', str(info.iat[j, i]))[0]
n = re.split('_P', str(info.iat[j, i]))[-1]
if (re.search('-P', n)):
p = re.findall('(.*?)-P', n)[0]
q = re.split('-P', n)[-1]
if (len(p) == 1):
p = '0' + p
if (len(q) == 1):
q = '0' + q
info.iat[j, i] = m + p + '-' + m + q
else:
if (len(n) == 1):
n = '0' + n
info.iat[j, i] = m + n
fileTo = os.path.join(OutputPath, 'channel_map_'+Power+'.xlsx')
info.to_excel(fileTo, header=1, index=False, encoding='utf-8', sheet_name=Power)
def getInfoOfUtlityChannel(path):
getInfo = pd.read_excel(path, sheet_name=Utility, dtype=str, keep_default_na=False, header=1)
info = pd.DataFrame(getInfo.iloc[:, :])
if info.empty:
print('file is empty')
else:
print('file is not empty')
for i in range(0, info.keys().size):
for j in range(0, len(info)):
if re.search('_URW', info.iat[j, i]):
# print(str(info.iat[j, i]))
x = re.findall('UB(.*?)_URW', str(info.iat[j, i]))[0]
y = re.split('_URW', str(info.iat[j, i]))[-1]
if re.search('^0', y) and re.search('^((?!09).)*$', y):
y = '0' + str(int(y) + 1)
# print(y)
if re.search('^1d', y) or re.search('09', y):
y = str(int(y) + 1)
# print("y---", y)
z = 'UT'+ x + y
info.iat[j, i] = z
fileTo = os.path.join(OutputPath, 'channel_map_'+Utility+'.xlsx')
info.to_excel(fileTo, header=1, index=False, encoding='utf-8', sheet_name=Utility)
Digital = '1826S_IO'
Power = '1826_PG'
Utility = 'CTRL'
path = os.path.join(os.getcwd(), 'input\map.xlsx')
OutputPath = os.path.join(os.getcwd(), 'output')
# 判断是否存在output文件夹
if (os.path.exists(OutputPath)):
shutil.rmtree(OutputPath)
print('output dir has been rm -rf and new makedirs')
os.makedirs(OutputPath)
getInfoOfDigtalChannel(path)
getInfoOfDPSChannel(path)
getInfoOfUtlityChannel(path)