自动化办公从EXCEL VBA转PYTHON xlwings+pandas自用代码对比实现_excel函数转化为python语言

#一、**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
1
2
3
4
5
6
7
8
9
10
11
12

#二、**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               #字体加粗
1
2
3
4
5
6
7
8
9
10
11
12
13

#三、**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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

#四、**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()                                      #清除字典，为下一个合并列做好准备
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

#五、**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()                                      #清除字典，为下一个合并列做好准备
'''
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