多个EXCEL怎么进行快速批量替换
 
如果我们有多个Excel文件，多个Sheet，需要进行多个文件多个关键字的批量替换，将每个关键字替换成对应的文字。
 
有一个在线免费工具，不需熟悉vba或者公式函数，地址：www.excelfb.com,点击：批量替换  中的  多个Excel文件批量替换
 
 
 
   注：只进行批量查找请看文章：https://blog.csdn.net/loubo_001/article/details/91819288
 
(注意要使用谷歌浏览器)
 
 
 
使用方法如下：
 
例如，我有以下多个Excel文件，每个文件中都要进行如下替换：
 
 
 
 
1、新建一个Excel文件,制作批量替换规定文件如下：
 
        
 
2、选择要替换的文件，选择制作好的批量替换规定文件
 
3、如果全部范围都替换不需要指定列，如果固定列替换，输入Excel列号
 
4、点击“替换—生成新文件”，等待下载替换完成的文件（对于谷歌浏览器：下载多个文件很方便；火狐浏览器：要一个一个点保存）
 
5、下载的文件一般位于  计算机->下载  目录中

目标，将A列国家的名称替换为K列代码
 
 
使用VLOOKUP函数
 
=VLOOKUP(A2,$J$2:$K$250,2,0) 
 

 可参考Excel如何做到批量替换数据？
 
根据多个条件匹配数据
 
目标，根据国家和时间两个条件匹配汇率数据，将结果放在G列
 
 
在G2单元格输入以下公式
 
=VLOOKUP(A2&B2,IF({1,0},$I$2:$I$11001&$J$2:$J$11001,$K$2:$K$11001),2,)
 
公式输完,按CTRL+SHIFT+ENTER结束，下拉向下填充公式即可
 
 
参考Excel里，vlookup函数各种应用-匹配多列、多条件匹配

 
Python批量处理Excel文件信息替换Word模板
 
原由和思路
工具准备
1. 前期处理
1.1 数据处理
1.2 模板处理
  
2. 编写代码
2.1 使用Pycharm新建项目ExcelToWord
2.2 新建ETW.py主程序文件并安装相应库
2.3 主要代码块解析
  
3. 打包程序
心得体会

 
原由和思路
 
经常上级会下发一些word表格让我们基层填写，涉及人数较多，表格下发给个人之后，格式啊字体啊大小啊都不统一，每次还得重复校对，我就想能不能创建一个大型Excel数据库完成平时信息采集和更新，一次性导入下发的word表格中，并生成新的文件。
 使用Excel的原因主要是维护方便，并不是所有人都懂数据库操作，并且制作较为麻烦，尤其是遇到新加字段的时候，工作量大，不如就使用Excel存储数据，维护简单，更新及时。
 现在就再想，我码个程序，输入我需要变更的数据列名，替换掉Word模板当中的相应内容，并另存为以名称命名的新文件。但由于数据格式多，所以要求EXCEL处理的时候全部都是文本格式，这样不容易出错，如果转换为.csv格式会更加简洁。
 
工具准备
 
Python 3.7 (32bit)
 Pycharm
 Windows7 (64bit)
 Office 2010
 
1. 前期处理
 
1.1 数据处理
 
先看一下我的数据：
 
 第一步，将Excel所有单元格设置为文本后，检查容易出错的几个类型，日期，数字，金额等；
 第二步，将第一行全部改为英文描述，利于后期Python编译查找速度，及时不需要的也最好一次性都编译好，以免后期需要再重复工作；
 第三步，另存为 csv 格式文件，一定选逗号分隔的，如图：
 
 
1.2 模板处理
 
模板处理使用的是 jinja2 标签 {{xxx}}的替换模式，将所需要的替换的位置用数据的列名称替换，如图：
 
 这里替换标签里面的内容一定要和数据源的一致，并且和后面程序中的一致。
 
2. 编写代码
 
编写前先给大家看一下最后的程序样式，以便有个宏观印象。
 
 
2.1 使用Pycharm新建项目ExcelToWord
 

 使用32位Python的原因是兼容性，64位的Python打包的程序只能64位系统使用，32位的Python打包的程序可以在32位、64位系统上使用。
 
2.2 新建ETW.py主程序文件并安装相应库
 

 本程序一共需要额外安装3个库
 pywin32（32位系统打包程序必须装）
 pyinstaller（打包程序）
 docxtpl（模板替换）
 打开Pycharm的File->Settings
 
 点击加号，依次添加3个额外库。
 
 
2.3 主要代码块解析
 
引用头：
 
from tkinter import * #tkinter-python内部GUI库
from tkinter import filedialog #tkinter-python内部GUI库获取文件绝对路径
import tkinter.messagebox #tkinter-python内部GUI库弹出消息库
import csv #数据CSV格式处理库
from docxtpl import DocxTemplate #Word模板替换库
 
先介绍一下docxtpl Word模板替换 最简单的使用方法：
 
tpl = DocxTemplate('leave_temp.docx') 
#括号后是相对路径，我们采取选取绝对路径方式
# 需要替换内容以key:value的方式进行更换
context = {
   'who': '程旭阳'
}

tpl.render(context)
#渲染替换
tpl.save('leave.docx')
#另存为
 
本文中需要替换的是 No Name Age Id Addr
 我们这里考虑，以迭代的方式一个文件一个文件生成，context字典的键我们人工输入，这样有一个好处就是不需要处理多余的数据源数据，可以指定内容替换。那么久需要获取输入框的内容，也就是tkinter->entry组件，我们先建立一个Entry()组件和Button()按钮完成，输入需要更换N个变量，点击确定按钮后会更新出N个输入框来供我们填写。
 
#创建新的输入栏，输入变量名称
def newentry():
    for i in range(int(replacenumber.get())):
        entry_x = 'entry_' + str(i)#构造变量名称
        entries.append(entry_x)#储存到变量列表中方便查询
        if i<20 :
            entries[i] = Entry(frm21, width=18)
            entries[i].pack(side=TOP, expand=True, fill=X)
        elif i<40:
            entries[i] = Entry(frm22, width=18)
            entries[i].pack(side=TOP, expand=True, fill=X)
        else:
            entries[i] = Entry(frm23, width=18)
            entries[i].pack(side=TOP, expand=True, fill=X)
            #
 
replacenumber.get()会获取输入框内输入了几个需要替换的变量，之后生成。
 其中entry_x是传递变量名称的函数，然后储存到列表 entries中去
 e.g. 如果是5，那么会创造5个变量，分别是
 entry_0、entry_1、entry_2、entry_3、entry_4
 
Entry()和其他的组件的用法请参看https://www.jianshu.com/p/c9fb28367015
 【由于tkinter没有自适应布局，只好选绝对布局的方式，摆放各个部件，这里只允许创建最多60个输入框，你也可以更改】
 
替换的程序主要内容：
 
def load():
    filepath = filedialog.askopenfilename()    #获取数据源文件绝对地址
    with open(filepath,'r',encoding='gbk') as f:
        reader = csv.DictReader(f,dialect='excel')  #获取数据源内容
        #使用csv不用pandas的原因是含pandas库打包exe程序出错

		#遍历每一行将数据替换给新的Word
        for row in reader:                       
           #创建完整替换context字典，利用原先的列表
            for entry in entries:
                context[entry.get()] = row[entry.get()]
             #写好输出文件夹的路径，且以 Name 一列命名，也可以使用其他的命名
            outputpath = 'output/'+row['Name']+'.docx'    
            # 替换模板，地址为获取的模板绝对地址，docxpath是一储存绝对路径信息label
            tpl = DocxTemplate(docxpath['text'])
            tpl.render(context)
            tpl.save(outputpath)
        tkinter.messagebox.showinfo('成功', '已替换所有模板!')
        #完成后弹窗提示
 
最后就是GUI tkinter布局的问题了，难度不大，大家稍微看了上面的链接就懂布局了。
 最后我粘贴一下全部代码，解释内容较少
 
from tkinter import *
from tkinter import filedialog
import tkinter.messagebox
import csv
from docxtpl import DocxTemplate

#选取模板
def seltpl():
    tplpath = filedialog.askopenfilename()
    label['text'] = '4.已选择模板,请选择数据源CSV'
    docxpath['text'] = tplpath

#读取CVS文件绝对路径后保存数据到df中
def load():
    filepath = filedialog.askopenfilename()
    with open(filepath,'r',encoding='gbk') as f:
        reader = csv.DictReader(f,dialect='excel')

        for row in reader:
            #创建完整替换context字典
            for entry in entries:
                context[entry.get()] = row[entry.get()]
            outputpath = 'output/'+row['Name']+'.docx'
            # 替换模板
            tpl = DocxTemplate(docxpath['text'])
            tpl.render(context)
            tpl.save(outputpath)
        tkinter.messagebox.showinfo('成功', '已替换所有模板!')

#创建新的输入栏，输入变量名称
def newentry():
    for i in range(int(replacenumber.get())):
        entry_x = 'entry_' + str(i)#构造变量名称
        entries.append(entry_x)#储存到变量列表中方便查询
        if i<20 :
            entries[i] = Entry(frm21, width=18)
            entries[i].pack(side=TOP, expand=True, fill=X)
        elif i<40:
            entries[i] = Entry(frm22, width=18)
            entries[i].pack(side=TOP, expand=True, fill=X)
        else:
            entries[i] = Entry(frm23, width=18)
            entries[i].pack(side=TOP, expand=True, fill=X)

#主窗口top实例化
top = Tk()
top.geometry("400x600")
top.title("使用前必须阅读说明书")
context = {} 
entries = []

#frm1
frm1 = Frame()
frm1.pack(side=TOP)
label2 = Label(frm1, text='1.请输入需要替换几个变量', font=("黑体", 20), wraplength=400, justify="left", anchor="nw")
label2.pack(side=TOP, expand=YES, fill=X)
#frm11\frm12
frm11 = Frame(frm1)
frm11.pack()
replacenumber = Entry(frm11)
replacenumber.pack(side=LEFT, fill=X, expand=YES)
Button(frm11, text='确定', command=newentry).pack(side=LEFT, fill=X, expand=YES)

#frm2
frm2 = Frame()
frm2.pack(side=TOP)
label3 = Label(frm2, text='2.请将变量名称填写在下列输入框框内', font=("黑体", 16), wraplength=400, justify="left", anchor="nw")
label3.pack(side=TOP, expand=YES, fill=X)
frm21 = Frame(frm2)
frm21.pack(side=LEFT)
frm22 = Frame(frm2)
frm22.pack(side=LEFT)
frm23 = Frame(frm2)
frm23.pack(side=LEFT)
frm3 = Frame()
frm3.pack(side=BOTTOM)

#储存模板位置标签栏（不显示）
docxpath =  Label(top, text='', bg="pink", font=("黑体", 20), wraplength=1, justify="left", anchor="nw")

#frm3提示信息标签栏
label = Label(frm3, text='3.请先选择需要替换的DOCX模板', font=("黑体", 20), wraplength=400, justify="left", anchor="nw")
label.pack(side=TOP)
Button(frm3, text='3.选择模板', command=seltpl).pack(side=LEFT, fill=X, expand=YES)
Button(frm3, text='4.选择数据源并开始替换', command=load).pack(side=LEFT, fill=X, expand=YES)

#启动程序
top.mainloop()
 
3. 打包程序
 
在Pycharm 的终端Terminal 输入
 pyinstaller -F -w ETK.py
 

 完成打包exe文件，输出到项目地址dist文件夹中，此时人工添加output文件夹（没来及做，有时间的可以自己代码生成）
 打开ETK.exe文件
 输入变量5个，确定后输入变量名称，注意一定要写正确
 
 点击选择模板，选择好模板文件 .docx，然后点击选择数据源CSV，之后就会弹窗说替换完成，我们可以去output文件夹下面去看一下。
 
 确实生成了新的文件，打开其中一个。
 
 已完成替换。
 
心得体会
 
pyinstaller打包pandas 和 numpy时候都有点问题，我还没解决，网上有人解决了，我这里还是用了旧一点的 csv库。
GUI界面美化没有做，网上很多好看的GUI用的Qt，我这边因为功能比较简单就没有美化，主要还是分享和记录第一次发博客。
很多地方还可以优化，希望大家能多多提意见，共同进步。

 
用公式提取多个Excel文件的数据
 
待提取数据的Excel文件
新建公式，提取文件名
提取文件名到Excel中
用文件名和地址组成字符串
将引用的字符串以数字类型粘贴
用查找和替换功能在字符串前加“=”
用查找和替换功能更改单元地址的引用方式，提取其他数据
 

 
待提取数据的Excel文件
 
在文件夹 E:\temp\销售清单\ 中是不同公司的销售清单Excel文件。现在要将所有公司地址提取到一新的Excel中进行汇总。
 
 
 
新建公式，提取文件名
 
公式名称为：提取文件名
 引用位置为：=FILES(Sheet5!$A$1)： 
 
提取文件名到Excel中
 
用公式：=Index(提取文件名，row(1:1))将第一个文件名提取出来，再通过下拉单元格将所有文件名提取出来。
 
 
 
用文件名和地址组成字符串
 
用文件名和地址组成字符串，用于对文件的应用。
 ="‘E:\temp\销售清单\["&A2&"]Sheet1’!$A$2"
 
 
将引用的字符串以数字类型粘贴
 

 
 
用查找和替换功能在字符串前加“=”
 
在字符串前加“=”，将字符串变成公式。
 
 
 
用查找和替换功能更改单元地址的引用方式，提取其他数据