#一、**VBA实现:**取消EXCEL横向合并单元格并使数据左对齐
'取消指定列如“A”列的合并单元格并设置为不换行左对齐格式
Sub XMerge()
For i = 1 To Range("A65536").End(xlUp).Row + 1 '取A列数据的最大行数
If Range("A" & i).MergeCells = True Then '如果单元格为合并单元格
With Range("A" & i)
.HorizontalAlignment = xlLeft '设置单元格左对齐
.WrapText = False '取消自动换行
.MergeArea.MergeCells = False '取消合并单元格
End With
End If
Next
End Sub
#二、**PYTHON xlwings实现:**取消EXCEL横向合并单元格并使数据左对齐
import xlwings as xw #导入xlwings模块用于操作EXCEL,结合VBA代码(.api)
app = xw.apps.active #当前活动app(EXCEL)
wb = app.books.active #当前活动工作薄,方法2:打开指定工作簿xw.Book(r'D:\PY\test.xlsx')
ws = wb.sheets.active #当前活动工作表,方法2:wb.sheets[0],即sheet1
nrows = ws.used_range.last_cell.row #可以正确获取总行数!***!
#print(nrows) #测试点
for i in range(1,nrows):
if ws.range(i,1).api.MergeCells: #合并单元格为真
ws.range(i,1).unmerge() #取消合并单元格
ws.range(i,1).api.HorizontalAlignment = -4131 #设置单元格左对齐
ws.range(i,1).api.WrapText = False #取消自动换行
ws.range(i,1).rows.autofit() #自适应,注意加括号
ws.range(i,1).api.Font.Bold = True #字体加粗
#三、**VBA实现:**取消EXCEL第1列合并单元格并合并第3列对应分行数据到一个单元格(填充指定列数据)
Sub HRow()
'For i = 1 To Range("A65536").End(xlDown).Row '取A列最大行数,2的20次方=1048576
Dim a(), i%, n% '定义动态数组a(),%:整数型变量(Integer),&:长整型变量(Long),$:字符串变量(String)
n = Range("A65536").End(xlUp).Row + 1 '相同结果:n = Mid(UsedRange.Address, 9) 或者UsedRange.Rows.Count
For i = 1 To n '取A列n行数,并从1到n循环
If Range("A" & i).MergeCells = True Then '如果单元格为合并单元格
x = Range("A" & i).MergeArea.Rows.Count - 1 '取X等于合并单元格的总行数-1,数组下标从0起所以要减1
If x > 0 Then '多行合并单元格才进行处理
ReDim a(x) '用X重定义数组大小
For j = Range("A" & i).MergeArea.Rows.Count To 1 Step -1 '取j循环计数,从合并单元格总行数逐1递减
a(j - 1) = Range("C" & (j + i - 1)) '属于A合并项的C列每行单元格数据逐项赋值对应数组成员,+i-1考虑从第二项合并单元格起的C列行数
If j > 1 Then 'j=1时为当前行不应删除
Rows(j + i - 1).Delete shift:=xlUp '保留原合并单元格对应的第一行,其余行删除
End If
Next '完成所有合并单元格对应C列所有行单元格数据的数组赋值
Cells(i, 3) = Join(a, Chr(10)) '用Join函数即可以完成数组中元数值的连接
'For k = 1 To UBound(a) '从数组中循环取所有值
'Cells(i, 3) = Cells(i, 3) & Chr(10) & a(k) '将数组中的所有值连接并写入当前合并单元格对应第一行C列单元格
'Next '完成所有数组值写入单元格
End If
End If '完成一个合并项的数据处理
Next '处理下一个合并项数据
End Sub
#四、**PYTHON xlwings实现:**取消EXCEL第1列合并单元格并合并第3列对应分行数据到一个单元格(填充指定列数据)
#这个程序是为了从WORD复制表格到EXCLE后整理数据,最后表格再放回WORD :)
#在EXCEL中表格的特征是第1列有合并列(第1列是序号,第2列是名称),对应第3列是分行的数据
#要做的是:取消第1列合并,把第3列的数据并到1个单元格中,第2列会跟随第1列
import xlwings as xw #导入xlwings模块用于操作EXCEL,结合VBA代码(.api)
app = xw.apps.active #当前活动app(EXCEL)
wb = app.books.active #当前活动工作薄,方法2:打开指定工作簿xw.Book(r'D:\PY\test.xlsx')
ws = wb.sheets.active #当前活动工作表,方法2:wb.sheets[0],即sheet1
k = ws.used_range.last_cell.row #删除第3列空值行,后面用pandas dropna在DataFrame数据结构中操作速度快多了
while k > 0: #从表格最后删起,从前删行数不停变动不好处理
if ws.range(k,1).value is None and ws.range(k,3).value is None: #第1列和第3列都为空值行
ws.api.Rows(k).Delete() #删除空行
k -= 1 #记数值递减
nrows = ws.used_range.last_cell.row #删除空行后重新取总行数,可以正确获取行数的方法!
ls = {} #创建空字典ls
for i in range(1,nrows): #for循环整个表格
if ws.range(i,1).api.MergeCells: #A列合并单元格为真
x = ws.range(i,1).api.MergeArea.Rows.Count - 1 #取A列合并单元格行数,-1留一行存放第3列合并数据
if x > 0: #条件为超过1行
j = ws.range(i,1).api.MergeArea.Rows.Count #取A列合并单元格行数,重取避免行数变化
for j in range(j,0,-1): #for循环,当前合并单元格行数递减1到1,不等于0
ls[j-1] = ws.range(j + i -1,3).value #赋值ls字典,keys从j-1到0,value为第3列每行数据
if j > 1: #当前合并单元格行数>1
ws.api.Rows(j + i - 1).Delete() #留合并列第1行,删除其他多余行
lst = reversed(ls.values()) #字典键值从高到低需要反向
ws.range(i,3).value = chr(10).join('%s' %id for id in lst) #换行连接每行数据,连接前需遍历转str
ls.clear() #清除字典,为下一个合并列做好准备
#五、**PYTHON xlwings+pandas提高效率 VS VBA:**取消EXCEL第1列合并单元格并合并第3列对应分行数据到一个单元格(填充指定列数据)
#这个程序是为了从WORD复制表格到EXCLE后整理数据,最后表格再放回WORD :)
#在EXCEL中表格的特征是第1列有合并列(第1列是序号,第2列是名称),对应第3列是分行的数据
#要做的是:取消第1列合并,把第3列的数据并到1个单元格中,第2列会跟随第1列
import xlwings as xw #导入xlwings模块用于操作EXCEL,结合VBA代码(.api)
import pandas as pd #导入pandas模块用于处理数据,提高效率(VS VBA数组)
app = xw.apps.active #当前活动app(EXCEL)
wb = app.books.active #当前活动工作薄,方法2:打开指定工作簿xw.Book(r'D:\PY\test.xlsx')
ws = wb.sheets.active #当前活动工作表,方法2:wb.sheets[0],即sheet1
#df=ws.range('A1').options(pd.DataFrame,header=1,index=False,expand='table').value #错误:只取到表格前3行,可能有横向合并
sht = wb.sheets[ws].used_range.value #正确读取数据的方法,取到表格全部数据
df = pd.DataFrame(sht) #写入DataFrame,表格中的合并单元格在DataFrame数据格式中被打散了
'''1、替换以下删除空行循环:使用pandas内部函数dropna在DataFrame数据结构中只需一条语(效率提高1)
k = ws.used_range.last_cell.row #删除第3列空值行,pandas dropna可能因为有合并列,仅第3列空没用成功
while k > 0: #从表格最后删起,从前删行数不停变动不好处理
if ws.range(k,1).value is None and ws.range(k,3).value is None: #第1列和第3列都为空值行
ws.api.Rows(k).Delete() #删除空行
k -= 1 #记数值递减
'''
df.dropna(axis=0,how='all',inplace=True) #在DataFrame数据结构中正确处理删除空行
df = df.reset_index(drop=True) #重置DataFrame数据结构索引
#预处理避免表格本身出现项目无序号错误
for i in df.index:
if df.iloc[i,0] is None and df.iloc[i,1] is not None:
df.iloc[i,0] = '无序号' #df.at[i,0] = '无序号'
#在DataFrame数据结构中合并行打散,如果第1列数据有空值错误,则以下处理会出问题
for i in df.index: #在DataFrame数据结构中原合并行已打散,填入第一行相同数据用于分组
if df.iloc[i,0] is None and df.iloc[i,2] is not None: #第1列数据空和第3列数据非空
df.iloc[i,0] = df.iloc[i-1,0] #填充第1列空行数据与同组第1行相同
df.iloc[i,1] = df.iloc[i-1,1] #填充第3列空行数据与同组第1行相同
'''参考用
df[2].astype('str')
df = df.groupby([0,1])[2].apply(list).to_frame()
df[2] = df[2].apply(lambda x:str(x).replace('[','').replace(']',''))
'''
df[2] = df.groupby([0,1])[2].transform(lambda x : '\n'.join(x)) #按1、2列分组连接/合并第3列所有行数据
#df = df.drop_duplicates() #会误删分组中需保留的重复项
cols = [0,1] #第1列、第2列,去除连续重复项的正确方法
df = df.loc[(df[cols].shift()!=df[cols]).any(axis=1)]
'''无效,因为drop不改变原DF数据,另外尽量不用遍历
j = len(df) - 1
while j >0:
if df.iloc[j,0] == df.iloc[j-1,0] and df.iloc[j,1] == df.iloc[j-1,1]:
df.drop(index=j)
j -= 1
'''
nrows = ws.used_range.last_cell.row #原表格总行数
ws.range('A1:'+('C'+str(nrows))).clear() #清空表格数据
ws.range('A1').value=df.values #将DataFrame数据写入表格,df/df.values前者带行列号
'''用XLwings操作表格特别慢,且循环条件判断容易出问题,尽量不用遍历
j = ws.used_range.last_cell.row #表格当前总行数
while j > 0: #在表格中处理重复数据,只删除同组重复项
if ws.range(j,1).value is not None and ws.range(j,1).value == ws.range(j-1,1).value and ws.range(j,2).value == ws.range(j-1,2).value:
ws.api.Rows(j).Delete()
j -= 1
'''
#print(df.columns)
#print(len(df))
#print(df.isnull())
#print(df)
#writer=pd.ExcelWriter(app.path)
#df.to_excel(writer,sheet_name=ws)
'''表格数据进入DataFrame数据结构之后没有了合并单元格,以下方法无效
nrows = ws.used_range.last_cell.row #删除空行后重新取总行数,可以正确获取行数的方法!
ls = {} #创建空字典ls
for i in range(1,nrows): #for循环整个表格
if ws.range(i,1).api.MergeCells: #A列合并单元格为真
x = ws.range(i,1).api.MergeArea.Rows.Count - 1 #取A列合并单元格行数,-1留一行存放第3列合并数据
if x > 0: #条件为超过1行
j = ws.range(i,1).api.MergeArea.Rows.Count #取A列合并单元格行数,重取避免行数变化
for j in range(j,0,-1): #for循环,当前合并单元格行数递减1到1,不等于0
ls[j-1] = ws.range(j + i -1,3).value #赋值ls字典,keys从j-1到0,value为第3列每行数据
if j > 1: #当前合并单元格行数>1
ws.api.Rows(j + i - 1).Delete() #留合并列第1行,删除其他多余行
lst = reversed(ls.values()) #字典键值从高到低需要反向
ws.range(i,3).value = chr(10).join('%s' %id for id in lst) #换行连接每行数据,连接前需遍历转str
ls.clear() #清除字典,为下一个合并列做好准备
'''