app user .com books cnblogs python代码 mat else
1. 公司需要问卷调查,有两份问卷, 1)是spss问卷,2)是excel问卷。spss问卷数据不全,但有各种标签, excel呢, 生成的数据直接把选项变成了值
2. 现在需要把excel的选项值变成1, 2这种数字{1:“满意”}
3. 妹子已经把vba写好了,虽然一堆错误,我百度了半天,改了很多:(
由于需要执行很多vba命令,我就用py的字符串拼接,生成了,900多份,怀疑vba的代码有问题,凭直觉,但我不会vba,又懒得查,只能让cpu去做重复工作了
安装方法
```
python3 -m pip install pypiwin32
```
生成vba命令
- #!/usr/bin/env python
- # -*- coding:utf-8 -*-
- import savReaderWriter
- filepath = "/opt/code/my_code/testStata/5976d077606f07d4418b46eb160938.sav"
- a = ‘‘‘Dim m
- m = 1
- Columns("‘‘‘
- b = ‘‘‘").Select
- m = 1
- On Error GoTo Err_Handle‘‘‘
- c = ‘‘‘
- For m = 1 To 65
- Selection.Find(What:="‘‘‘
- d = ‘‘‘", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
- :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
- False, MatchByte:=False, SearchFormat:=False).Activate
- ActiveCell.Replace What:="‘‘‘
- e = ‘‘‘", Replacement:="‘‘‘
- f = ‘‘‘", LookAt:=xlPart, _
- SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
- ReplaceFormat:=False
- Next m
- ‘‘‘
- g = ‘‘‘
- Exit Sub
- Err_Handle:
- End Sub
- ‘‘‘
- sum = 0
- totalStr = ‘\n************hello************\n\n‘
- excelList = [‘A‘,
- ‘B‘,
- ‘C‘,
- ‘D‘,
- ‘E‘,
- ‘F‘,
- ‘G‘,
- ‘H‘,
- ‘I‘,
- ‘J‘,
- ‘K‘,
- ‘L‘,
- ‘M‘,
- ‘N‘,
- ‘O‘,
- ‘P‘,
- ‘Q‘,
- ‘R‘,
- ‘S‘,
- ‘T‘,
- ‘U‘,
- ‘V‘,
- ‘W‘,
- ‘X‘,
- ‘Y‘,
- ‘Z‘,
- "AA",
- "AB",
- "AC",
- "AD",
- "AE",
- "AF",
- "AG",
- "AH",
- "AI",
- "AJ",
- "AK",
- "AL",
- "AM",
- "AN",
- "AO",
- "AP",
- "AQ",
- "AR",
- "AS",
- "AT",
- "AU",
- "AV",
- "AW",
- "AX",
- "AY",
- "AZ",
- "BA",
- "BB",
- "BC",
- "BD",
- "BE",
- "BF",
- "BG",
- "BH",
- "BI",
- "BJ",
- "BK",
- "BL",
- "BM",
- "BN",
- "BO",
- "BP",
- "BQ",
- "BR",
- "BS",
- "BT",
- "BU",
- "BV",
- "BW",
- "BX",
- "BY",
- "BZ",
- "CA",
- "CB",
- "CC",
- "CD",
- "CE",
- "CF",
- "CG",
- "CH",
- "CI",
- "CJ",
- "CK",
- "CL",
- "CM",
- "CN",
- "CO",
- "CP",
- "CQ",
- "CR",
- "CS",
- "CT",
- "CU",
- "CV",
- "CW",
- "CX",
- "CY",
- "CZ",
- "DA",
- "DB",
- "DC",
- "DD",
- "DE",
- "DF",
- "DG",
- "DH",
- "DI",
- "DJ",
- "DK",
- "DL",
- "DM",
- "DN",
- "DO",
- "DP",
- "DQ",
- "DR",
- "DS",
- "DT",
- "DU",
- "DV",
- "DW",
- "DX",
- "DY",
- "DZ",
- "EA",
- "EB",
- "EC",
- "ED",
- "EE",
- "EF",
- "EG",
- "EH",
- "EI",
- "EJ",
- "EK",
- "EL",
- "EM",
- "EN",
- "EO",
- "EP",
- "EQ",
- "ER",
- "ES",
- "ET",
- "EU",
- "EV",
- "EW",
- "EX",
- "EY",
- "EZ",
- "FA",
- "FB",
- "FC",
- "FD",
- "FE",
- "FF",
- "FG",
- "FH",
- "FI",
- "FJ",
- "FK",
- "FL",
- "FM",
- "FN",
- "FO",
- "FP",
- "FQ",
- "FR",
- "FS",
- "FT",
- "FU",
- "FV",
- "FW",
- "FX",
- "FY",
- "FZ",
- "GA",
- "GB",
- "GC",
- "GD",
- "GE",
- "GF",
- "GG",
- "GH",
- "GI",
- "GJ",
- "GK",
- "GL",
- "GM",
- "GN",
- "GO",
- "GP",
- "GQ",
- "GR",
- "GS",
- "GT",
- "GU",
- "GV",
- "GW",
- "GX",
- "GY",
- "GZ",
- "HA",
- "HB",
- "HC",
- "HD",
- "HE",
- "HF",
- "HG",
- "HH",
- "HI",
- "HJ",
- "HK",
- "HL",
- "HM",
- "HN",
- "HO",
- "HP",
- "HQ",
- "HR",
- "HS",
- "HT",
- "HU",
- "HV",
- "HW",
- "HX",
- "HY",
- "HZ",
- "IA",
- "IB",
- "IC",
- "ID",
- "IE",
- "IF",
- "IG",
- "IH",
- "II",
- "IJ",
- "IK",
- "IL",
- "IM",
- "IN",
- "IO",
- "IP",
- "IQ",
- "IR",
- "IS",
- "IT",
- "IU",
- "IV",
- "IW",
- "IX",
- "IY",
- "IZ",
- "JA",
- "JB",
- "JC",
- "JD",
- "JE",
- "JF",
- "JG",
- "JH",
- "JI",
- "JJ",
- "JK",
- "JL",
- "JM",
- "JN",
- "JO",
- "JP",
- "JQ",
- ]
- def readSpss():
- with savReaderWriter.SavReader(filepath, ioUtf8=True) as read:
- ret = read.getSavFileInfo()
- return ret[4], ret[2], ret[5], ret[6]
- def vbaStr(totalStr, sum, readSpss):
- formats, varnames, varLabels, valueLabels = readSpss()
- for i in range(len(varnames)):
- if varnames[i] in valueLabels:
- subvalueLables = {}
- for j in valueLabels[varnames[i]]:
- subvalueLables[int(j)] = valueLabels[varnames[i]][j]
- # totalStr += a + excelList[i] + ":" + excelList[i] + b
- for zz in subvalueLables:
- totalStr += a + excelList[i] + ":" + excelList[i] + b
- totalStr += c + subvalueLables[zz] + d + subvalueLables[zz] + e + str(zz) + f
- totalStr += g
- sum += 1
- totalStr += "\n************hello************\n\n"
- # totalStr += g
- totalStr += "=============================\n\n"
- else:
- continue
- return totalStr, sum
- totalStr, sum = vbaStr(totalStr, sum, readSpss)
- print(totalStr)
- print(sum)
用Python在执行vba命令方法,有点类似执行shell的形式,但必须在windows环境下面执行,不能在linux下面
注意问题:
- 1. 必须另存为xlsm文件格式
- 2. 第二,在执行Python脚本的时候必须是打开excel的
- 3. 必须新建模块(vba中)
- 4. vba代码必须有函数
- 5. office必须开启宏,否则不生效
- 6. 打开vba快捷键alt + f11
vba代码
- Sub test() 《============这个函数很重要,对应着python
- Dim m
- m = 1
- Columns("G:G").Select
- m = 1
- On Error GoTo Err_Handle
- For m = 1 To 65
- Selection.Find(What:="男", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
- :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
- False, MatchByte:=False, SearchFormat:=False).Activate
- ActiveCell.Replace What:="男", Replacement:="1", LookAt:=xlPart, _
- SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
- ReplaceFormat:=False
- Next m
- Exit Sub
- Err_Handle:
- End Sub
python代码
- #!/usr/bin/env python
- # -*- coding:utf-8 -*-
- import win32com.client
- filename = "C:/Users/Administrator/Desktop/test.xlsm"
- xls = win32com.client.Dispatch(‘Excel.Application‘)
- xls.Workbooks.Open(Filename=filename)
- xls.DisplayAlerts = 0
- xls.Run("test1")
用python批量执行VBA代码
来源: http://www.bubuko.com/infodetail-2297494.html