用Python 处理EXCEL表格数据

烫手的猪蹄儿

已于 2024-09-19 11:57:47 修改

阅读量414

点赞数 1

分类专栏：实用代码库文章标签： excel python

于 2024-08-27 14:28:25 首次发布

本文链接：https://blog.csdn.net/zhangzhibinshuai/article/details/141601389

版权

实用代码库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

场景

我本身使用Excel不怎么熟练, 但家人需要经常处理Excel, 就写了一些Python脚本协助处理,

我把其中公用的部分抽了出来, 放在下边, 有用到的可以看看

还有一些js写的处理Excel的小工具, 可直接在浏览器端执行, 没有数据上传可放心使用

功能代码:

引入openpyxl库

注意, 使用了 openpyxl 库, 只识别最新的 'xlsx' 后缀的Excel, 不识别老的xls后缀的文件

from openpyxl import load_workbook
from openpyxl import Workbook

# 下边的代码片段都需要这两个 import

界面GUI, 选择文件和sheet

windows批处理脚本, 用来打开操作菜单

# start.bat
@echo off
chcp 65001
python ./menu.py

菜单界面

import tkinter as tk
import subprocess

# 创建主窗口
root = tk.Tk()
root.title("功能列表")

# 设置窗口大小
root.geometry("512x256")


btn1 = tk.Button(root, text="考勤", command=lambda: subprocess.run(['python', './1kaoqin.py']))
btn1.grid(row=0, column=0, sticky=tk.W, padx=10, pady=5)

btn2 = tk.Button(root, text="系数分", command=lambda: subprocess.run(['python', './2xishufen.py']))
btn2.grid(row=1, column=0, sticky=tk.W, padx=10, pady=5)

grid布局, 选择文件, sheet, 保存全局变量

import tkinter as tk
from tkinter import filedialog
from tkinter import ttk

#按钮回调, 弹窗让用户选择文件 
def open_file():
    # 使用filedialog打开文件
    filepath = filedialog.askopenfilename(
        title="选择Excel文件",
        filetypes=(("Excel files", "*.xlsx"), ("all files", "*.*"))
    )
    if not filepath:
        return
    
    var_file_path.set(filepath) # 文件路径, 保存到全局变量中

    # 获取所有sheet的名字, 让用户选择
    wb = load_workbook(filepath)
    sheet_names = wb.sheetnames
    for i,v in enumerate(sheet_names):
        var_sheetname = tk.StringVar(root) # 创建一个StringVar变量来跟踪下拉菜单的当前选择
        checkbox = tk.Checkbutton(root, text=v, variable=var_sheetname, onvalue=v, offvalue='')
        checkbox.grid(row=1, column=i+1)
        checkbox_vars.append(var_sheetname)

    
#选择结果目录
def select_directory():
    directory_path = filedialog.askdirectory()
    var_target_dir.set(directory_path)
    label = tk.Label(root, text=directory_path)
    label.grid(row=3, column=1, sticky=tk.W, padx=10, pady=5)
    

def cal():
    #读取用户选择的文件和sheet名
    filepath = var_file_path.get()
    target_dir = var_target_dir.get()
    target_file = target_dir +'/(新建)季度考勤汇总.xlsx'

    # 读取数据处理的代码, 省略...

    # 数据处理完毕, 在界面上显示"结束"来提醒用户
    label = tk.Label(root, text="结束")# 创建一个标签控件
    label.grid(row=4, column=1, sticky=tk.W, padx=10, pady=5) 


#####################################开始绘制图形界面################################
# 创建主窗口
root = tk.Tk()
root.title("季度考勤汇总（注意：Excel文件后缀需要是‘xlsx’)")

# 设置窗口大小
root.geometry("512x256")

#数据文件
var_file_path = tk.StringVar(root) #定义全局变量, 存放用户选择的文件
btn_src = tk.Button(root, text="选择文件", command=open_file)
btn_src.grid(row=0, column=0, sticky=tk.W, padx=10, pady=5)

#选择sheet
checkbox_vars = [] #存放用户选择的多个sheet
label_src = tk.Label(root, text="选择Sheet名:")# 创建一个标签控件
label_src.grid(row=1, column=0, sticky=tk.E, padx=10, pady=5) # 使用grid布局管理器，并设置sticky参数为W，使控件靠左显示, padx, pady: 设置间距

#选择生成的文件保存的目录
var_target_dir = tk.StringVar(root)
button = tk.Button(root, text="结果输出目录", command=select_directory)
button.grid(row=3, column=0, sticky=tk.W, padx=10, pady=5)

#开始计算按钮
btn_ok = tk.Button(root, text="开始计算", command=cal)
btn_ok.grid(row=4, column=0, sticky=tk.W, padx=10, pady=5)



# 运行主循环
root.mainloop()

读取文件数据, 指定起始行和列

#读取文件数据
def read_file_data(filename, sheet_name, start_row=1, start_col=1):
    file_data = [];
    wb = load_workbook(filename=filename, data_only=True) #公式变为数值

    #读取财务sheet的所有数据
    ws = wb[sheet_name]
    for row in ws.iter_rows(min_row=start_row, min_col=start_col):
        row_data = [];
        #print(row)
        for cell in row:
            row_data.append(cell.value)                
        file_data.append(row_data)
    
    return file_data;

保存数据到Excel

#保存新文件, 1个sheet
# file_data: 二维数组
def save_new_file(file_data, filepath):
    # 创建一个工作簿对象
    wb = Workbook()
    sheet = wb.active
    for row in file_data:
        sheet.append(row)

    # 保存工作簿到文件
    wb.save(filepath)
    return True;


#保存新文件, 多个sheet 
# file_data 是三维数组
def save_file(file_data, filepath):
    wb = Workbook()
    for sheet_name in file_data:
        wb.create_sheet(sheet_name)
        for row in file_data[sheet_name]:
            wb[sheet_name].append(row)

    wb.remove(wb['Sheet'])
    wb.save(filepath)
    return True;

判断数组中某个下标是否存在(不是每一行的每一列都有数据)

#判断数组中某个下标是否存在
def list_exists(my_list, index, default=0):
    try:
        # 尝试访问下标
        tmp = my_list[index]
    except IndexError:
        # 如果引发IndexError，说明下标不存在
        return default
    else:
        # 如果没有异常，说明下标存在
        return tmp

#判断字典中某个下标是否存在    
def dic_exists(dic, k, default):
    if k in dic:
        return dic[k]
    else:
        return default

#创建指定长度的数组 (用于行数据对齐, 否则合并文件的时候, 数据会错位)
def create_arr(len, val=0):
    return [val] * len

转为浮点数(有的单元格没有值, 读出来的数据可能是 None)

#转为浮点数    
def change_float(data, default=0):
    try:
        data = float(data)
        return data
    except ValueError:
        return default
    except TypeError:
        return default

数组转为对象(有些Excel中存放的是权重信息, 为了方便程序使用, 将读出来的list改为dic)

#数组合并为对象
def combin_arr_to_obj(arr_key, arr_value):
    obj = {}
    for index,value in enumerate(arr_key):
        obj[value] = arr_value[index];
    return obj

#将一维数组转为对象
def arr_column(arr, f1, f2):
    rs = {}
    for row in arr:
        rs[row[f1]] = row[f2]
    return rs

合并两个文件 (两个Excel中都存在一列,比如工号, 按工号将两个Excel合并起来)

# 合并两个文件
# data1:文件1的数据, field1:字段编号(a/b/c..)
def merge_file_by_field(data1, field1, data2, field2):
    map = {}
    for row in data2:
        v = row[field2]
        map[v] = row

    length = len(data2[0])
    empty_list = [''] * length
    
    rs = []
    for row in data1:
        v = row[field1]
        #echo(v)
        if v in map:
            row.extend(map[v])
            rs.append(row)
        else:
            row.extend(empty_list)
            rs.append(row)
    return rs

抽出某几列(有时候删列比较麻烦, 可以输入a,b,c...等列编号, 抽取出需要的列生成新文件)

#抽出某几列
def pick_cols(data, arr_idx):
    rs = [];
    for row in data:
        tmp = []
        for id in arr_idx:
            tmp.append[row[id]]
        rs.append(tmp)
    return rs

分组相加 (比如表格中有同一个人的不同项目得分, 要把这个人的得分相加汇总)

def group_add(data, f1, f2):
    rs = {}
    first_row = data.pop(0) #默认第一行是标题
    title= [first_row[f1], first_row[f2]]
    
    for row in data:
        v1 = row[f1]
        v2 = float(row[f2])
        if v1 in rs:
            rs[v1] = rs[v1] + v2
        else:
            rs[v1] = v2
    
    file_data = []
    file_data.append(title)
    for k in rs:
        file_data.append([k, rs[k]])
    
    return file_data

统计一维数组中元素出现的次数 (统计某列中相同的数据出现的次数)

# 计算出现次数
def group_count(data, f1, f2, new_field='出现次数'):
    group = {}

    # 默认第一行是标题
    first_row = data.pop(0)
    title= [first_row[f1], first_row[f2], new_field]
    
    #按照f1分组
    for row in data:
        v1 = row[f1]
        v2 = row[f2].strip()
        if v1 not in group:
            group[v1] = []
        group[v1].append(v2)
        
    #计算出现次数
    count = {}
    for k in group:
        count[k] = arr_item_count(group[k])

    #整理最终格式
    file_data = []
    file_data.append(title) #标题
    for k in count:
        for k1 in count[k]:
            file_data.append([k, k1, count[k][k1]]) #拆分成行
    
    return file_data

#统计一维数组中元素出现的次数
def arr_item_count(arr):
    count = {}
    for v in arr:
        if v in count:
            count[v] = count[v] + 1
        else:
            count[v] = 1
    return count

用行中的某一列数据作为字典数据的键 (可用于合并两个文件)

# 用行中的某一列的值作为key
# arr: 所有行数据 f: 列编码 keep: f列的数据是否还保存在后边的数组中 
def arr_map(arr, f, keep=False):
    rs = {}
    if keep:
        for row in arr:
            rs[row[f]] = row
    else:
        for row in arr:
            k = row.pop(f)
            rs[k] = row
    return rs