import openpyxl

(相关资料图)

import re

dataBook = openpyxl.load_workbook("[file name]")

name_list = dataBook.sheetnames

# 将特定block数据存储为list

def getlist(sheetname, colmin, colmax, rowmin, rowmax):

listname = []

for i in range(rowmin, rowmax):     # 获取特定列数据

for j in range(colmin, colmax):     # 获取特定行数据

a = sheetname.cell(row=i, column=j).value

listname.append(a)

return listname

# 在Excel中写入column

def writeExcelColumn(sheetname, listname, colNum):

for i in range(len(listname)):

sheetname.cell(i+1, colNum, listname[i])

# 仅保留list中的数值

def numOnlyFilter(listname):

m = []  # a list for the entire leakage data

for i in range(len(listname)):

m.append(listname[i])

if m[i] != None:

m[i] = re.sub(r"([^.\d\-\n\s]+)", "", str(listname[i]))

return m

# 将目标 column 以 \n 分为3列

def divideCols(Colname, ColIdx, col1, col2, col3):

dlist = []

for cell in list(worksheet.columns)[ColIdx]:

a = cell.value

dlist.append(a)

print(dlist)

for i in range(1, worksheet.max_row+1):

if "\n" in str(dlist[i-1]):

worksheet[col1 + str(i)] = worksheet[Colname + str(i)].value.split('\n')[0]

worksheet[col2 + str(i)] = worksheet[Colname + str(i)].value.split('\n')[1]

worksheet[col3 + str(i)] = worksheet[Colname + str(i)].value.split('\n')[2]

else:

worksheet[col1 + str(i)] = worksheet[Colname + str(i)].value

worksheet[col2 + str(i)] = worksheet[Colname + str(i)].value

worksheet[col3 + str(i)] = worksheet[Colname + str(i)].value

# -----------------------------------------------MOSFET_SOA--------------------------------------------------------------

worksheet = dataBook[name_list[0]]

m_list = worksheet.merged_cells  # 合并单元格的位置信息，可迭代对象（单个是一个'openpyxl.worksheet.cell_range.CellRange'对象），print后就是excel坐标信息

cr = []

# breakpoint()

for m_area in m_list:

# 合并单元格的起始行坐标、终止行坐标

r1, r2, c1, c2 = m_area.min_row, m_area.max_row, m_area.min_col, m_area.max_col

# 纵向合并单元格的位置信息提取出

if r2 - r1 > 0:

cr.append((r1, r2, c1, c2))

# print('Merged cells: %s' % str(m_area))

# print(cr)

# 把合并单元格的信息提取出再拆分

for r in cr:

if r[2] > 12:

worksheet.unmerge_cells(start_row=r[0], end_row=r[1],

start_column=r[2], end_column=r[3])

for row in range(r[0], r[1]):

worksheet.cell(row=row+1, column=r[3], value=worksheet.cell(r[0], r[3]).value)

Gox = getlist(worksheet, 13, 14, 1, worksheet.max_row+1)

NBTI = getlist(worksheet, 14, 15, 1, worksheet.max_row+1)

leakage = getlist(worksheet, 15, 16, 1, worksheet.max_row+1)

m1 = numOnlyFilter(Gox)

m2 = numOnlyFilter(NBTI)

m3 = numOnlyFilter(leakage)

writeExcelColumn(worksheet, m1, 13)

writeExcelColumn(worksheet, m2, 14)

writeExcelColumn(worksheet, m3, 15)

temp = getlist(worksheet, 13, 16, 1, worksheet.max_row+1)   # 用来判断是否分列的temp list

if "\n" in temp[2]:

worksheet.insert_cols(idx=14, amount=3)

worksheet.insert_cols(idx=18, amount=3)

worksheet.insert_cols(idx=22, amount=3)

divideCols('U', 20, 'V', 'W', 'X')

divideCols('Q', 16, 'R', 'S', 'T')

divideCols('M', 12, 'N', 'O', 'P')

worksheet.delete_cols(idx=21, amount=1)

worksheet.delete_cols(17, 1)

worksheet.delete_cols(13, 1)

print("Max column no. now = ", worksheet.max_column)

# limit flatten for length

lengthList = getlist(worksheet, 6, 7, 1, worksheet.max_row+1)

L = []  # a list for the post Length data

for i in range(len(lengthList)):

L.append(lengthList[i])

if L[i] != None:

L[i] = re.sub(r"[^.\d\<]+", "  ", str(lengthList[i]))

if "<" in L[i]:

L[i] = re.sub(r"[^.\d]+", "  ", str(lengthList[i]))

a = re.search(r"[.\d]+$", L[i])

b = float(a.group()) - 0.001

L[i] = re.sub(r"[.\d]+$", str(b), L[i])

writeExcelColumn(worksheet, L, 6)

# ------------------------------------------------Vt_PVT----------------------------------------------------------------

worksheet1 = dataBook[dataBook.sheetnames[3]]

aList = []

for cell in list(worksheet1.rows)[0]:  # 获取特定行数据

print(cell.value, end=" ")

a = cell.value

aList.append(a)

# print()

# print(aList)

# 检测并删除第一行中每格第一位含有的 “_” 字符

newrow1 = ""

n = 0   # 被删除的字符位置

n1 = 0

string = ""

bList = []  # 用来存放替换后的第一行数据

cList = []  # 记录被替换单元格位置

for k in range(0, len(aList)):

for i in range(0, len(aList[k])):

string = aList[k]

if string[0] == "_":

n1 = k

cList.append(n1)

if i != n:

newrow1 = newrow1 + string[i]

newrow1 = newrow1 + " "

def Convert(string1):   # convert string to a list

li = list(string1.split(" "))

return li

bList = Convert(newrow1)

# print(bList)

for i in range(0, len(aList)):  # 合并为新的list

if i in cList:

aList[i] = bList[i]

# print(aList)

for i in range(worksheet1.max_column):

worksheet1.cell(1, i+1, aList[i])

dataBook.save('[file name]')