近期接到了一些活,需要处理xlsx表格,记录一下处理的过程:

给了一张大工作簿,里头只有一个Sheet。
总体要求是:

  1. 将文本数据改为数值数据
  2. 筛出符合条件的数据
  3. 根据第四列的值,将一个大表的数据分到不同的Sheet中。例如第四列有:张三、李四、王五,那么就建立三个Sheet叫做张三、李四、王五,然后把大表中每一行的第四列凡是为张三的,放到叫张三的Sheet中,李四王五类推。

对于第一步,首先选中欲处理的列,然后按下组合键Alt + D(此时松开D但是Alt仍然按着下面按)+ E(也可以 数据-分列)(再备忘下,Word中相对全屏可以按Alt + V + U),然后一直下一步,走完就会发现已经变为数字了~

第二步就不说了,直接看到第三步。
我又把第三步又拆成了两步,一是先拆成不同的单个文件,文件名为第四列的对应值;第二步为将这些单个文件的表合并起来,合并的时候就按照文件名将数据复制到不同的对应的Sheet里面。

为啥拆成两步呢?因为在找解决方案的过程中,先遇到了第一个,然后又找到了后一个,正好拼一块了~

一是先拆成不同的单个文件,文件名为第四列的对应值。这一步直接使用宏来实现。

首先打开欲处理的表格,按下Alt + F11,然后在弹出的窗口中,查看左侧上方有个树形的小窗口(工程 - VBAProject),选中当前的文档对应的VBAProject,一定要选中当前文档对应的VBAProject啊啊啊啊!!!
然后顶栏:插入-模块(再强调下,一定要选中当前文档对应的VBAProject啊啊,不然会出问题的(指处理成其他文档了或者写到不知道什么地方去了)),在弹出的窗口里面复制以下代码:

Sub 保留表头拆分数据为若干新工作簿()
    Dim arr, d As Object, k, t, i&, lc%, rng As Range, c%
    c = Application.InputBox("请输入拆分列号", , 4, , , , , 1)
    If c = 0 Then Exit Sub
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    arr = [a1].CurrentRegion
    lc = UBound(arr, 2)
    Set rng = [a1].Resize(, lc)
    Set d = CreateObject("scripting.dictionary")
    For i = 2 To UBound(arr)
        If Not d.Exists(arr(i, c)) Then
            Set d(arr(i, c)) = Cells(i, 1).Resize(1, lc)
        Else
            Set d(arr(i, c)) = Union(d(arr(i, c)), Cells(i, 1).Resize(1, lc))
        End If
    Next
    k = d.Keys
    t = d.Items
    For i = 0 To d.Count - 1
        With Workbooks.Add(xlWBATWorksheet)
            rng.Copy .Sheets(1).[a1]
            t(i).Copy .Sheets(1).[a2]
            .SaveAs Filename:=ThisWorkbook.Path & "\" & k(i) & ".xlsx"
            .Close
        End With
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox "完毕"
End Sub

注:代码来自https://blog.csdn.net/ntotl/article/details/79141314

接着想办法让这段代码跑起来~可以直接在编辑VBA的窗口按F4,也可以点击绿色的运行按钮,也可以画一个按钮,然后选择我们刚才复制进去的宏。

我们这里要处理第4列,那就输入4就是了~【原谅我没有上图片叭阿巴阿巴阿巴阿巴阿巴】

到这里,已经拆分成很多个文件了(和当前处理的xlsx在一个文件夹里头)。下面考虑合并——

使用到了Python当中的openpyxl,记得提前安装哟~
(这里我们把拆分后的数据放到了cfcj文件夹里头)

import os
import openpyxl

# 获取文件夹下所有文件
filelist = os.walk("./cfcj")

# 获取文件名
# f = open("todolist.txt", encoding='UTF-8')
# f_content = f.read()
# todolist = f_content.split("\n")

# 打开一个新的文档并获取文档中的第一个工作表,用于保存从其他文档中复制过来的工作表
new_wb = openpyxl.Workbook()

def ReadWritedata(filename):
    # 读取源数据
    print(filename)
    wb = openpyxl.load_workbook(filename)
    ws = wb[wb.sheetnames[0]]
    # 新建sheet表,sheet名这里也操作了
    new_ws = new_wb.create_sheet(filename[7:-5], 0)
    # 从源数据中复制到新建的sheet表中
    for value in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=1, max_col=ws.max_column, values_only=True):
        value = list(value)
        new_ws.append(value)
    wb.close()


for (dirpath, dirnames, filenames) in filelist:
    for filename in filenames:
        ReadWritedata(os.path.join(dirpath, filename))

# for x in todolist:
#     ReadWritedata("./cfcj\\" + x + ".xlsx")

new_wb.save('fffinal.xlsx')
new_wb.close()

下面就好了~

标签: none

According to some rules, comments are banned. :)