精华内容
下载资源
问答
  • 导读::可以方便的利用VBA读取Excel工作薄中的数据。经常需要引用其它工作薄中的数据。 关键词:VBA,工作薄,数据读取  引言  工作中,使用Excel进行数据处理时,经常需要引用其它工作薄中的数据工作薄,用...
    导读::可以方便的利用VBA读取Excel工作薄中的数据。经常需要引用其它工作薄中的数据。
    关键词:VBA,工作薄,数据读取

      引言
      工作中,使用Excel进行数据处理时,经常需要引用其它工作薄中的数据工作薄,用VBA编程能帮助用户在不打开工作薄或看似不打开工作薄的情况下取得其它工作薄中的数据,下面有几种方法可以实现,假设当前工作表:“F:\test.xls”,第一个工作表名称为“sheet1”,其它工作薄如:“F:\源数据.xls”,第一个工作表名称为“销售数据”。读取引用工作薄的工作表数据范围A1:E20。在当前工作表中利用“控件工具箱”,制作命令按钮名称和下面过程名对应。
      1 使用公式引用工作簿中的工作表数据
      Private Sub btn1 ()
      Dim wshpath As String
      wshpath="'" &ThisWorkbook.Path &"\[源数据.xls]销售数据'!"
      With Sheet1.Range("A1:E20")'激活当前工作表A1:E20区域
      .FormulaR1C1="=" &wshpath &"RC"'在A1:E20区域写入对其它工作表单元格引用
      .Value=.Value'将该区域的公式转换为数值
      End With
      End Sub
      此方法适合应用于需要引用的数据不是太多的情况中国论文下载中心。
      2 使用Excel对象的方法引用工作簿中的工作表数据
      Private Sub btn2()
      Dim FilePath As String
      Dim i,j As Long
      Dim sBrow,sErow,sBCol,sEcol As Long'源表的行数,列数
      Dim tBrow,tBcol As Long '设置当前工作表开始行、列数
      sBrow=1
      sErow=20
      sBCol=1
      sEcol=5
      tBrow=1
      tBcol=1
      FilePath=ThisWorkbook.Path &"\源数据.xls"
      Set xlapp=CreateObject("Excel.Application")'创建EXCEL对象
      Set xlbook=xlapp.Workbooks.Open(FilePath)'创建工件簿对象
      Set xlsheet=xlbook.Worksheets(1)'创建工作表对象
      xlapp.Visible=False
      '以下将数据复制到当前工作表中
      For i=sBrow To sErow
      For j=sBCol To sEcol
      ThisWorkbook.Sheets(1).Cells(tBrow+i-1,tBcol+j-1)=xlsheet.Cells(i,j)
      Next
      Next
      '关闭对象代码省略
      End Sub
      该方法编程应用灵活,较为简单工作薄,可以修改添加代码对其它多个工作薄进行读取数据。
      3 使用ExecuteExcel4Macro方法引用工作簿中的工作表数据
      Private Sub btn3()
      Dim FilePath As String
      Dim i,j,sBrow,sErow,sBCol,sEcol,tBrow,tBcol As Long
      sBrow=1
      sErow=20
      sBCol=1
      sEcol=5
      tBrow=1
      tBcol=1
      FilePath=ThisWorkbook.Path &"\[源数据.xls]"
      For i=sBrow To sErow
      For j=sBCol To sEcol
      Sheets(1).Cells(tBrow+i-1,tBcol+j-1)=ExecuteExcel4Macro("'"&FilePath &"报名表" &"'!r" & i& "c" &j)
      Next
      Next
      End Sub
      4 使用ADO的方式引用工作簿中的工作表数据。
      在编写代码之前打开Visual Basic编辑器,单击“工具”菜单->“引用”命令,在打开的引用对话框中,选中“MicrosoftActiveX Data Objects 2.0 Library”,点击确定,就可利用ADO来读取Excel工作薄中数据。
      Private Sub btn4()
      Dim Sql As String
      Dim i,nR As Integer
      Dim Cnn As ADODB.Connection
      Dim rs As ADODB.Recordset
      With Sheet1
      .Cells.Clear
      Set Cnn=New ADODB.Connection
      '以下用ADO连接并打开被应用工作薄
      With Cnn
      .Provider="microsoft.jet.oledb.4.0"
      .ConnectionString="Extended Properties=Excel 8.0;" &"Data Source=" &ThisWorkbook.Path &"\源数据"
      .Open
      End With
      '以下筛选表中数据生成记录集,第一行默认为字段名
      Set rs=New ADODB.Recordset
      Sql="select * from [销售数据$]"
      rs.Open Sql,Cnn,adOpenKeyset,adLockOptimistic
      '以下将字段名放入首行,将数据写入工作表
      For i=0 To rs.Fields.Count-1
      .Cells(1,i+1)=rs.Fields(i).Name
      Next
      nR=.Range("A65536").End(xlUp).Row
      .Range("A" &nR+1).CopyFromRecordset rs
      End With
      rs.Close
      Cnn.Close
      Set rs=Nothing
      Set Cnn=Nothing
      End Sub
      结束语:
      通过以上几种方法工作薄,可以方便的利用VBA读取Excel工作薄中的数据,其中一、二种方法在打开Excel工作薄时使其隐藏而使用户看不见,三、四种方法真正意义上没有打开工作薄而读取其数据。在实际VBA应用中,需要读取更多的工作薄,用户则可添加对文件夹及文件的操作代码完成,从而提高工作效率。

    参考文献:
    [1]John Walkenbach著.盖江南,王勇等译.Excel 2003高级VBA编程宝典[M].电子工业出版社,2005.
    [2]Excel Home.Excel应用大全[M].人民邮电出版社,2008
    展开全文
  • Excel VBA读取 ACCESS 数据 连接

    千次阅读 2018-09-26 17:34:25
    Excel VBA读取 ACCESS 数据 连接 Sub access_test() Dim CONN As Object Dim RST As Object Dim SQL As String Sheets("Sheet1").Cells.Clear Set CONN = CreateObject("adodb....

    随笔2

    Sub access_test()

    Dim CONN As Object
    Dim RST As Object
    Dim SQL As String
    
    Sheets("Sheet1").Cells.Clear
    
    Set CONN = CreateObject("adodb.connection")
    Set RST = CreateObject("adodb.recordset")
    
    SQL = "select * from 律师函8月分"
    
    CONN.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\新建 Microsoft Access 数据库.accdb" & ";ACE OLEDB:Database Password="
    
     Set RST = CONN.Execute(SQL)
    
    For i = 1 To RST.Fields.Count
        Cells(1, i) = RST.Fields(i - 1).Name
    Next
    
    
    Sheets("Sheet1").[a2].CopyFromRecordset RST
    CONN.Close
    Set C0NN = Nothing
    

    End Sub

    展开全文
  • ## # 用ExcelVBA能够从Excel读取数据,也能够实现数据写入到单元格中,但是怎么实现在一个Function中实现先读取数据,再写到表的其他单元格呢?**__ Public Function test1() As String Dim strArray() As ...
  • Python VS VBA 读取Excel中的文本框组件

    千次阅读 2021-02-27 19:32:58
    Python读取Excel的文本框 基本需求 今天看到了一个很奇怪的问题,要读取Excel文件的文本框中的文本,例如这种: 本以为openxlpy可以读取,但查看openxlpy官方文档并没有找到相应的API,咨询了几个大佬,他们也没有...

    作者:小小明

    Python读取Excel的文本框

    基本需求

    今天看到了一个很奇怪的问题,要读取Excel文件的文本框中的文本,例如这种:

    本以为openxlpy可以读取,但查看openxlpy官方文档并没有找到相应的API,咨询了几个大佬,他们也没有处理过类似的问题。

    无赖之下,我就准备发挥我较强的数据解析能力,自己写个方法来读取这些东西。

    处理代码

    xlsx文件的本质是xml格式的压缩包,解压文件做xml解析提取出相应的数据即可。

    本来准备用lxml作xpath解析xml,但实际测试发现,这些xml文件存在大量的命名空间,解析起来异常复杂,试了好几个普通的xml解析的库,可以顺利解析,但我觉得还不如正则方便,所以我最终选择了使用正则表达式作xml解析。

    最终处理代码如下:

    import re
    import os
    import shutil
    from zipfile import ZipFile
    
    
    def read_xlsx_textbox_text(xlsx_file):
        tempdir = tempfile.gettempdir()
        basename = os.path.basename(xlsx_file)
        xml_names = []
        with ZipFile(xlsx_file) as zip_file:
            for name in zip_file.namelist():
                if name.startswith("xl/drawings/drawing"):
                    zip_file.extract(name, tempdir)
                    destname = f"{tempdir}/{name}"
                    xml_names.append(destname)
        result = []
        for xml_name in xml_names:
            with open(xml_name, encoding="utf-8") as f:
                text = f.read()
            lines = re.findall("<a:p>(.*?)</a:p>", text)
            for line in lines:
                runs = re.findall("<a:t>(.*?)</a:t>", line)
                result.append("".join(runs).replace('&lt;', '<').replace(
                    '&gt;', '>').replace('&amp;', '&'))
        return "\n".join(result)
    

    测试一下:

    result = read_xlsx_textbox_text("test.xlsx")
    print(result)
    

    结果:

    什么是JSON?
    
    就是一种数据格式;比如说,我们现在规定,有一个txt文本文件,用来存放一个班级的成绩;然后呢,我们规定,这个文本文件里的学生成绩的格式,是第一行,就是一行列头(姓名 班级 年级 科目 成绩),接下来,每一行就是一个学生的成绩。那么,这个文本文件内的这种信息存放的格式,其实就是一种数据格式。
    
    学生 班级 年级 科目 成绩
    张三 一班 大一 高数 90
    李四 二班 大一 高数 80
    
    ok,对应到JSON,它其实也是代表了一种数据格式,所谓数据格式,就是数据组织的形式。比如说,刚才所说的学生成绩,用JSON格式来表示的话,如下:
    
    [{"学生":"张三", "班级":"一班", "年级":"大一", "科目":"高数", "成绩":90}, {"学生":"李四", "班级":"二班", "年级":"大一", "科目":"高数", "成绩":80}]
    
    其实,JSON,很简单,一点都不复杂,就是对同样一批数据的,不同的一种数据表示的形式。
    
    JSON的数据语法,其实很简单:如果是包含多个数据实体的话,比如说多个学生成绩,那么需要使用数组的表现形式,就是[]。对于单个数据实体,比如一个学生的成绩,那么使用一个{}来封装数据,对于数据实体中的每个字段以及对应的值,使用key:value的方式来表示,多个key-value对之间用逗号分隔;多个{}代表的数据实体之间,用逗号分隔。
    ...
    

    这样我们就顺利实现了,从一个Excel文件中,读取全部的文本框的文本。

    注意:如果你有啥特殊的其他需求,可以根据实际情况修改代码,也可以联系本文作者(小小明)进行相应的定制。

    读取xls文件的文本框内容

    上面的方法,仅支持xlsx格式文件的读取,如果要读取xls格式,我们需要先进行格式转换。

    完整代码:

    import win32com.client as win32
    
    
    def read_xls_textbox_text(xls_file):
        excel_app = win32.gencache.EnsureDispatch('Excel.Application')
    #     excel_app.DisplayAlerts = False
        try:
            wb = excel_app.Workbooks.Open(xls_file)
            xlsx_file = xls_file+"x"
            wb.SaveAs(xlsx_file, FileFormat=51)
        finally:
            excel_app.Quit()
        return read_xlsx_textbox_text(xlsx_file)
    

    如果你希望存在同名的xlsx文件时不提示,关闭注释即可

    测试读取:

    print(read_xls_textbox_text(r"E:\tmp\test2.xls"))
    

    结果:

    我们的数据从哪里来?
    互联网行业:网站、app、系统(交易系统。。)
    传统行业:电信,人们的上网、打电话、发短信等等数据
    
    数据源:网站、app
    
    都要往我们的后台去发送请求,获取数据,执行业务逻辑;app获取要展现的商品数据;发送请求到后台进行交易和结账
    
    后台服务器,比如Tomcat、Jetty;但是,其实在面向大量用户,高并发(每秒访问量过万)的情况下,通常都不会直接是用Tomcat来接收请求。这种时候,通常,都是用Nginx来接收请求,并且后端接入Tomcat集群/Jetty集群,来进行高并发访问下的负载均衡。
    
    比如说,Nginx,或者是Tomcat,你进行适当配置之后,所有请求的数据都会作为log存储起来;接收请求的后台系统(J2EE、PHP、Ruby On Rails),也可以按照你的规范,每接收一个请求,或者每执行一个业务逻辑,就往日志文件里面打一条log。
    网站/app会发送请求到后台服务器,通常会由Nginx接收请求,并进行转发
    ...
    

    xls格式批量转xlsx

    假如我们有一批xls文件,希望批量转换为xlsx:

    我的实现方式是整个文件夹都转换完毕再关闭应用,这样相对来说处理更快一些,但可能更耗内存,代码如下:

    import win32com.client as win32  # 导入模块
    from pathlib import Path
    import os
    
    def format_conversion(xls_path, output_path):
        if not os.path.exists(output_path):
            os.makedirs(output_path)
        excel_app = win32.gencache.EnsureDispatch('Excel.Application')
        try:
            for filename in Path(xls_path).glob("[!~]*.xls"):
                dest_name = f"{output_path}/{filename.name}x"
                wb = excel_app.Workbooks.Open(filename)
                wb.SaveAs(dest_name, FileFormat=51)
                print(dest_name, "保存完成")
        finally:
            excel_app.Quit()
    

    测试一下:

    excel_path = r"F:\excel文档"
    output_path = r"E:\tmp\excel"
    format_conversion(excel_path, output_path)
    

    结果:

    E:\tmp\excel/008.离线日志采集流程.xlsx 保存完成
    E:\tmp\excel/009.实时数据采集流程.xlsx 保存完成
    E:\tmp\excel/011.用户访问session分析-模块介绍.xlsx 保存完成
    E:\tmp\excel/012.用户访问session分析-基础数据结构以及大数据平台架构介绍.xlsx 保存完成
    E:\tmp\excel/013.用户访问session分析-需求分析.xlsx 保存完成
    E:\tmp\excel/014.用户访问session分析-技术方案设计.xlsx 保存完成
    E:\tmp\excel/015.用户访问session分析-数据表设计.xlsx 保存完成
    E:\tmp\excel/018.用户访问session分析-JDBC原理介绍以及增删改查示范.xlsx 保存完成
    E:\tmp\excel/019.数据库连接池原理.xlsx 保存完成
    ...
    

    批量提取xlsx文件的文本框文本

    上面我们已经获得了一个xlsx文件的文件夹,下面我们的需求是,提取这个文件夹下每个xlsx文件的文本框内容将其保存为对应的txt格式。

    处理代码:

    from pathlib import Path
    
    xlsx_path = r"E:\tmp\excel"
    for filename in Path(xlsx_path).glob("[!~]*.xlsx"):
        filename = str(filename)
        destname = filename.replace(".xlsx", ".txt")
        print(filename, destname)
        txt = read_xlsx_textbox_text(filename)
        with open(destname, "w") as f:
            f.write(txt)
    

    执行后,已经顺利得到相应的txt文件:

    需求升级

    上面的读取方法是将整个excel文件所有的文本框内容都合并在一起,但有时我们的excel文件的多个sheet都存在文本框,我们希望能够对不同的sheet进行区分:

    下面我们改进我们的读取方法,使其返回每个sheet名对应的文本框文本,先测试一下。

    首先解压所需的文件:

    from zipfile import ZipFile
    from pathlib import Path
    import shutil
    import os
    import tempfile
    import re
    
    xlsx_file = "test3.xlsx"
    
    tempdir = tempfile.gettempdir()
    basename = os.path.basename(xlsx_file)
    xml_names = []
    sheets_names = None
    ids = []
    with ZipFile(xlsx_file) as zip_file:
        for name in zip_file.namelist():
            if name.startswith("xl/drawings/drawing"):
                zip_file.extract(name, tempdir)
                destname = f"{tempdir}/{name}"
                xml_names.append(destname)
            elif name == "xl/workbook.xml":
                zip_file.extract(name, tempdir)
                sheets_names = f"{tempdir}/{name}"
            elif name.startswith("xl/worksheets/_rels/sheet"):
                tmp = name.lstrip("xl/worksheets/_rels/sheet")
                ids.append(int(tmp[:tmp.find(".")])-1)
    print(xml_names, sheets_names, ids)
    

    结果:

    ['C:\\Users\\Think\\AppData\\Local\\Temp/xl/drawings/drawing1.xml', 'C:\\Users\\Think\\AppData\\Local\\Temp/xl/drawings/drawing2.xml', 'C:\\Users\\Think\\AppData\\Local\\Temp/xl/drawings/drawing3.xml', 'C:\\Users\\Think\\AppData\\Local\\Temp/xl/drawings/drawing4.xml', 'C:\\Users\\Think\\AppData\\Local\\Temp/xl/drawings/drawing5.xml'] C:\Users\Think\AppData\Local\Temp/xl/workbook.xml [0, 1, 2, 4, 5]
    

    读取sheet名称:

    with open(sheets_names, encoding="utf-8") as f:
        text = f.read()
    sheet_names = re.findall(
        '<sheet .*?name="([^"]+)" .*?/>', text)
    tmp = []
    for inx in ids:
        tmp.append(sheet_names[inx])
    sheet_names = tmp
    sheet_names
    

    结果:

    ['JSON', '数据库连接池', '实时数据采集', '工厂设计模式', '页面转化率']
    

    解析:

    result = {}
    for sheet_name, xml_name in zip(sheet_names, xml_names):
        with open(xml_name, encoding="utf-8") as f:
            xml = f.read()
        lines = re.findall("<a:p>(.*?)</a:p>", xml)
        tmp = []
        for line in lines:
            runs = re.findall("<a:t>(.*?)</a:t>", line)
            tmp.append("".join(runs).replace('&lt;', '<').replace(
                '&gt;', '>').replace('&amp;', '&'))
        result[sheet_name] = "\n".join(tmp)
    result
    

    结果(省略了大部分文字):

    {'JSON': '什么是JSON?....',
     '数据库连接池': 'java程序\n数据库连接\n数据库连接\n数据库连接\nMySQL...',
     '实时数据采集': '...实时数据,通常都是从分布式消息队列集群中读取的,比如Kafka....',
     '工厂设计模式': '如果没有工厂模式,可能会出现的问题:....',
     '页面转化率': '用户行为分析大数据平台\n\n页面单跳转化率,....'}
    

    可以看到已经顺利的读取到每个sheet对应的文本框内容,而且一一对应。

    分别读取每个sheet对应文本框文本

    我们整合并封装一下上面的过程为一个方法:

    import re
    import os
    from zipfile import ZipFile
    import tempfile
    
    
    def read_xlsx_textbox_text(xlsx_file, combine=False):
        tempdir = tempfile.gettempdir()
        basename = os.path.basename(xlsx_file)
        xml_names = []
        sheets_names = None
        ids = []
        with ZipFile(xlsx_file) as zip_file:
            for name in zip_file.namelist():
                if name.startswith("xl/drawings/drawing"):
                    zip_file.extract(name, tempdir)
                    destname = f"{tempdir}/{name}"
                    xml_names.append(destname)
                elif name == "xl/workbook.xml":
                    zip_file.extract(name, tempdir)
                    sheets_names = f"{tempdir}/{name}"
                elif name.startswith("xl/worksheets/_rels/sheet"):
                    tmp = name.lstrip("xl/worksheets/_rels/sheet")
                    ids.append(int(tmp[:tmp.find(".")])-1)
        with open(sheets_names, encoding="utf-8") as f:
            text = f.read()
        sheet_names = re.findall(
            '<sheet .*?name="([^"]+)" .*?/>', text)
        tmp = []
        for inx in ids:
            tmp.append(sheet_names[inx])
        sheet_names = tmp
        result = {}
        for sheet_name, xml_name in zip(sheet_names, xml_names):
            with open(xml_name, encoding="utf-8") as f:
                xml = f.read()
            lines = re.findall("<a:p>(.*?)</a:p>", xml)
            tmp = []
            for line in lines:
                runs = re.findall("<a:t>(.*?)</a:t>", line)
                tmp.append("".join(runs).replace('&lt;', '<').replace(
                    '&gt;', '>').replace('&amp;', '&'))
            result[sheet_name] = "\n".join(tmp)
        if combine:
            return "\n".join(result.values())
        return result
    

    调用方式:

    result = read_xlsx_textbox_text("test3.xlsx")
    print(result)
    

    可以传入combine=True,将sheet的结果合并到一个文本,但这样不如直接调用之前编写的方法。

    批量提取文本框文本分sheet单独保存

    下面,我们的需求是对每个xlsx文件创建一个同名文件夹,每个文件夹下根据sheet名称单独保存文本框的文本。

    处理代码:

    from pathlib import Path
    import os
    
    xlsx_path = r"E:\tmp\excel"
    for filename in Path(xlsx_path).glob("[!~]*.xlsx"):
        dest = filename.with_suffix("")
        if not os.path.exists(dest):
            os.mkdir(dest)
        filename = str(filename)
        print(filename, dest)
        result = read_xlsx_textbox_text(filename)
        for txtname, txt in result.items():
            with open(f"{dest}/{txtname}", "w") as f:
                f.write(txt)
            print(f"\t{dest}/{txtname}")
    

    经测试顺利的为每个excel文件创建了一个目录,每个目录下根据哪些sheet存在文本框就有相应的sheet名文件。

    使用Python调用VBA解决需求

    VBA官方文档地址:https://docs.microsoft.com/zh-cn/office/vba/api/overview/excel

    整体而言,上面自行解析xml的方法还是挺麻烦的,在写完上面的方法后我灵机一动,VBA不就有现成的读取文本框的方法吗?而Python又可以全兼容的写VBA代码,那问题就简单了。通过VBA,不仅代码简单,而且不用考虑格式转换的问题,直接可以解决问题,读取代码如下:

    import win32com.client as win32
    
    
    def read_excel_textbox_text(excel_file, app=None, combine=False):
        if app is None:
            excel_app = win32.gencache.EnsureDispatch('Excel.Application')
        else:
            excel_app = app
        wb = excel_app.Workbooks.Open(excel_file)
        result = {}
        for sht in wb.Sheets:
            if sht.Shapes.Count == 0:
                continue
            lines = []
            for shp in sht.Shapes:
                try:
                    text = shp.TextFrame2.TextRange.Text
                    lines.append(text)
                except Exception as e:
                    pass
            result[sht.Name] = "\n".join(lines)
        if app is None:
            excel_app.Quit()
        if combine:
            return "\n".join(result.values())
        return result
    

    测试读取:

    result = read_excel_textbox_text(r'F:\jupyter\test\提取word图片\test3.xlsx')
    print(result)
    

    顺利读出结果。

    批量处理:

    from pathlib import Path
    import os
    
    xlsx_path = r"E:\tmp\excel"
    app = win32.gencache.EnsureDispatch('Excel.Application')
    try:
        for filename in Path(xlsx_path).glob("[!~]*.xls"):
            dest = filename.with_suffix("")
            if not os.path.exists(dest):
                os.mkdir(dest)
            filename = str(filename)
            print(filename, dest)
            result = read_excel_textbox_text(filename, app)
            for txtname, txt in result.items():
                with open(f"{dest}/{txtname}", "w") as f:
                    f.write(txt)
                print(f"\t{dest}/{txtname}")
    finally:
        app.Quit()
    

    经测试,VBA处理的缺点也很明显,63个文件耗时达到25秒,而直接解析xml耗时仅259毫秒,性能差别不在一个数量级。

    使用xlwings解决需求

    苹果电脑是不支持VBA的,上面调用VBA的代码对于苹果电脑来说无效,但所幸的是xlwings在0.21.4版本中新增了访问文本框文本的属性text。

    作为Windows上Pywin32和Mac上appscript的智能包装的xlwings,已经通过appscript实现了在Mac系统上对文本框文本的访问:

    import xlwings as xw
    
    app = xw.App(visible=True, add_book=False)
    wb = app.books.open(r'test3.xlsx')
    for sht in wb.sheets:
        print("-------------", sht.name)
        for shp in sht.shapes:
            if hasattr(shp, 'text') and shp.text:
                print(shp.text)
    wb.close()
    app.quit()
    

    注意:如果你的xlwings没有这个属性,请注意升级:

    pip install xlwings -U

    总结

    读取excel中的数据,基本没有VBA干不了的事,python调用VBA也很简单,直接使用pywin32即可。当然2007的xlsx本质上是xml格式的压缩包,解析xml文本也没有读不了的数据,只是代码编写起来异常费劲,当然也得你对xlsx的存储原理较为了解。

    这样VBA与直接解析xml的优劣势就非常明显了:

    • VBA是excel应用直接支持的API,代码编写起来相对很简单,但执行效率低下。苹果电脑无法使用VBA,可以使用xlwings已经封装好的方法实现。
    • 直接解析xml文件,需要对excel的存储格式较为了解,编码起来很费劲,但是执行效率极高。

    作为读者的你有何看法呢?欢迎你在下方留言区发表你的看法。

    展开全文
  • 摘要: 因为现在项目在开发新的API,我需要根据API方法论去校验这个API的计算逻辑和计算结果是否正确,而且需要将计算逻辑用Excel记录下来,所以就用了ExcelVBA。1. 什么是VBA?具体含义大家可以看百度百科,我就...

    摘要: 因为现在项目在开发新的API,我需要根据API方法论去校验这个API的计算逻辑和计算结果是否正确,而且需要将计算逻辑用Excel记录下来,所以就用了Excel的VBA。

    1. 什么是VBA?

    81fbfa7f7de3

    具体含义大家可以看百度百科,我就直接上图了,每个单元格都是调用了VBA的宏计算语言函数,我就简称这个Excel含有macro计算逻辑吧。

    2.问题是什么?

    大家都知道要比较一个API的准确性,我们需要自己根据方法论去把所有原始数据都推演计算一遍,得到结果。然后在跟开发做的API response做下对比,如果两个结果相同,则表示这个API计算正确。

    现在我们PO不让我重写一遍开发的计算逻辑了,规定所有计算相关的逻辑都在Excel里面做好计算模板sheet,然后我将所有原始数据都放到计算模板同一个文件夹下。在含有Marco计算逻辑的那个excel(计算模板sheet)里面完成数据的引用和计算。

    然后将API返回的Response也写入到同目录下的一个Excel中(样式跟计算模板计算出来的结果相同),模板里面将比较API response和Excel中的计算结果,然后输出一个如上图一样的一个sheet页(二维矩阵,单元格里的内容是TRUE或者FALSE)。

    我要做的就是:将原始数据放到规定名字和格式的Excel中,将最终API的response也放到Excel中,然后打开这个含有macro函数的excel,查看sheet页中是否含有FALSE,如果有,告诉对应的行和列名称并输出。若是没有FALSE,这说明比对通过,API pass。

    难点是:

    我用Python写函数,现在Python用pandas打开这个含有Macro函数的Excel后,读出的数据永远都是Nan,因为它不能识别那些macro函数。。。所以我无法判断该API是否pass。

    3. 手动操作Excel的时候要怎样看到是TRUE或FALSE呢?

    81fbfa7f7de3

    在打开这个有macro函数的Excel之前,我们需要打开所有它引用的其他Excel。然后回到这个Excel的时候,这些宏会自动引用其他excel相应的数据,并得出计算结果。

    4. 用Python要怎样实现这一手动逻辑呢?

    在Python中调用VBA去控制这些Excel即可。

    5. 具体要怎样实现呢?

    a. 下载Python扩展包: pywin32 (这个有32和64位之分)

    可以直接去下载zip文件,然后安装;

    但我建议直接用命令: pip install pywin32

    b. 在自己python文件中引用:

    from win32com import client

    c. 在python中写VBA:

    81fbfa7f7de3

    81fbfa7f7de3

    finally中的语句非常关键,相当于open一个文件之后进行读写,最终一定要关闭对应的io流一样!

    那个计算模板是不允许改动的,所以在关闭那个xlBook的时候总是提示是否需要保存,在Jenkins上配置这个Project之后,所有相关操作肯定是无法手动干预的。所以我就采取了最直接的方式:

    xlApp.DisplayAlerts = 0

    就是不弹出所有提示框。

    先关闭计算模板Excel,然后关闭该Excel依赖的所有其他Excel。

    最后退出Excel应用程序。

    (若是最后退出程序以后发现还有一个空白的Excel窗口,这时候可以用 xlApp.visible=0 来隐藏)

    所有这些都执行完了以后,这些被操作过的文件就不会被锁住了。(若是被锁住,以后用python读取的时候都会提示IOException哦~~)

    因为项目太忙了,所以写的都是流水账,如果大家有什么更好的建议或者疑问,可以给我留言哦,谢谢阅读~~

    展开全文
  • 以下实例的内容是,通过VBA读取SQL数据库,将结果填充到Excel表。希望本文对您有所帮助。Dim i As Integer, j As Integer, sht As Worksheet 'i,j为整数变量;sht 为excel工作表对象变量,指向某一工作表Dim ...
  • VBA得到EXCEL表格中的行数和列数 每种方法中上面的是Excel的行数,下面的是Excel的列数。方法1: ActiveSheet.UsedRange.Rows.Count ActiveSheet.UsedRange.Columns.Count 缺点:有时可能会比实际数大一些,...
  • 因为现在项目在开发新的API,我需要根据API方法论去校验这个API的计算逻辑和计算结果是否正确,而且需要将计算逻辑用Excel记录下来,所以就用了ExcelVBA。1. 什么是VBA?具体含义大家可以看百度百科,我就直接上图...
  • Word VBA读取Excel表格中的数据 前天有一个项目需要写详细设计报告,其中有一块重要内容是把Excel中的每一行数据做成一个Word中的表格。一共将近100行数据,如果我一行一行地手动去做,估计手就废了,于是采用Word...
  • 在word通过VBA打开excel数据文件 可以将excel的内容作为word doc文件读取的代码
  • Excel(Microsoft office)是现在最常用的办公软件,主要涉及电子表格制作、数据处理、报表输出展示以及更高端的还有金融建模等;我们知道,在需要批处理多个Excel工作表以及工作簿的时候,需要用到一个自动化的利器...
  • Excel工作表中的数据,可以直接处理,不过如果想快的话最好读入到数组处理。前面说过读入数组可以循环读取,也可以直接给数组赋值读取。虽然这个方式速度快,但只适合读取连续的列,对于分开的列,这种方法不可行,...
  • 这篇我们谈谈如何使用VBA打开另一个excel文件,并读取数据。那在此之前,我们测试一下,从当前excel文件读取数据的简单VBA代码。现在有一个九九乘法表的数据,如下图。 如果我们有两个乘数,需要从九九乘法表里面...
  • Sub test_oledb() Dim objconn As ADODB.Connection Dim objrs As ADODB.Recordset Set objconn = New ADODB.Connection objconn.Open "provider=Microsoft.ACE.OLEDB....extended properties='excel 12.0;hdr=no...
  • Excel使用VBA读取实时WebService股票数据

    万次阅读 热门讨论 2017-08-03 18:05:05
    环境:Win7+Excel2016 采用的是腾讯提供的股票接口,例如:http://qt.gtimg.cn/q=sh600016,返回输入如下: v_sh600016="1~民生银行~600016~8.58~8.68~8.67~886218~499700~386518~8.58~772~8.57~6361~8.56~...
  • 具体含义大家可以看百度百科,我就直接上图了,每个单元格都是调用了VBA的宏计算语言函数,我就简称这个Excel含有macro计算逻辑吧。 2.问题是什么? 大家都知道要比较一个API的准确性,我们需要自己根据方法论去把...
  • 有一个客户在日常工作中需要处理数据量比较大的CSV文件,直接使用excel打开会比较慢,所以他想有个工具可以快速的将CSV文件转换成excel文件类型,问我能否使用Access来实现,正好前一段时间发了个VBA转换Excel文件...
  • 因为现在项目在开发新的API,我需要根据API方法论去校验这个API的计算逻辑和计算结果是否正确,而且需要将计算逻辑用Excel记录下来,所以就用了ExcelVBA。1. 什么是VBA?具体含义大家可以看百度百科,我就直接上图...
  • 生产每月都会制作文件并公示,现在想做一个EXCEL,写一个宏,将文件夹里的3个月的公示EXCEL里的数据读取出来,并合并,然后点击姓名便生成一个新的sheel.将这个人12个月的完成定额绘制成一个曲线图。在这12个月里面...
  • 2、新建一个“合并数据.xlsm“文档(文件名称自定义即可,xlsm为启用宏的Excel文件格式)。3、打开“合并数据.xlsm“文件,在”Sheet1“工作表处右键→查看代码(快捷键:“Alt+F11”,或者打开“开发工具”-选择...
  • 有个excel订单表 想在想读取 其中几个单元格里的数据 然后新增为一张表的几率 请教用access VBA 应该怎么做
  • VBA操作网页读取数据自动填入EXCEL表中
  • Excel版本:Microsoft Office Professional Plus 2010 引用: VISA-COM 5.9 Type Library Keysight VISA COM Resource Manager 平均模式时防止操作未完成发生错误: -&gt;使能ESE中OPC,bit 0,&amp;...
  • 论坛里的各位高手,大家好:由于小弟需要统计一份内含数万条数据的txt文档,将下图所示的指定位置数据读取excel指定位置,求助各位老师这个代码应该怎么编写,本人新手,希望老师能尽可能地在代码后面注释,以便...
  • Attribute VB_Name = "模块1" Public classCount As Integer ...'若工作表处于保护状态,则程序无法读取并修改,请取消保护并保存,以使用此程序 Sub 成绩统计自动化() '以下两行代码为了提高运算速度...
  • 文章目录1、背景首次签合同人员.xlsx模板:简易劳动合同.docx2、python代码问题3、word vba1) 根据模板批量生成文件2) 批量打印文件4、总结:5、PS: 1、背景 行政部的同事每次都要根据excel里面的信息,制作word版的...
  • VBA读取电子称数据

    2014-08-14 17:44:47
    EXCEL进行电子称的读数,简单可靠,无需复杂的数据库技术支持!
  • 用vb读取excel中的数据

    2009-03-03 11:12:08
    用VB or VBA 读取excel所有数据 取得excel文件所有内容

空空如也

空空如也

1 2 3 4 5 ... 14
收藏数 275
精华内容 110
关键字:

vba读取excel数据