python批量查询(excel)数据

需求:

批量查询数据并导出到另一个excel表格中.

思路:

• 一开始想用c++,但excel对c++的支持并不好,只得选择了python
• 第一次使用python, 学了不少东西, 还是得记录一下, 做个总结,以后用的时候也好查找
• 主要使用了python的openpyxl库.支持读写xlsx文件
• 实现了二维列表的按首列大小的排序,用以进行二分查找
• 使用pyinstaller将源代码打包成exe 程序,以便没有python环境也能执行

源代码:

import os
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.styles import Alignment


def read_excel_xlsx(path):
    # 加载excel
    wb = load_workbook(path)
    # 获取所有sheet名字
    sheets_name = wb.sheetnames
    # 通过sheet名字获取表格对象
    table = wb[sheets_name[0]]

    # 将表格内容存储到lists中
    lists = []
    for row in table.rows:
        temp_list = []
        for cell in row:
            temp_list.append(cell.value)
        lists.append(temp_list)
    # 返回lists
    return lists


def binary_search(arr, left, right, x):
    if right >= left:
        mid = int(left + (right - left) / 2)
        if arr[mid][0] == x:
            return mid
        elif arr[mid][0] > x:
            return binary_search(arr, left, mid - 1, x)
        else:
            return binary_search(arr, mid + 1, right, x)
    else:
        # 不存在
        return -1


def select(index, data):
    ans = []
    # 将index列表按照第一列升序排列
    index.sort(key=lambda x: x[0], reverse=False)
    ans.append(['学号', '姓名', '电话'])

    for i in range(0, len(data)):
        # 二分查找值
        tmp = binary_search(index, 0, len(index)-1, data[i][0])
        if tmp != -1:
            ans.append(data[i])
            del index[tmp]
    return ans


def write_excel_xlsx(path, value):
    workbook = Workbook()
    table = workbook.active
    table.title = "Sheet1"
    for i in range(0, len(value)):
        for j in range(0, len(value[i])):
            table.cell(row=i + 1, column=j + 1, value=str(value[i][j]))

    table.merge_cells(start_row=len(value)+1, start_column=1, end_row=len(value)+1, end_column=3)
    num = (index_len - len(index_list)) / index_len
    table.cell(len(value)+1, 1).value = '查询比例' + str(round(num * 100, 4)) + '%'
    table.cell(row=len(value)+1, column=1).alignment = Alignment(horizontal='center', vertical='center')
    workbook.save(path)
    print("xlsx格式表格写入数据成功！")


read_excel_index_path = "index.xlsx"
index_list = read_excel_xlsx(read_excel_index_path)

read_excel_data_path = "data.xlsx"
data_list = read_excel_xlsx(read_excel_data_path)

# 删除列表第一行无用数据
del index_list[0]
del data_list[0]

# 记录总长度,用来统计为查询到的比例
index_len = len(index_list)

# 查询到的结果保存到列表
answer = select(index_list, data_list)
# 将查询到的数据写到excel中
write_excel_result_data_path = "result_data.xlsx"
write_excel_xlsx(write_excel_result_data_path, answer)

# index中值是否都已查到
if len(index_list) == 0:
    print("所有结果均以查到, 请核对")
else:
    print("以下为未查询到的索引")
    for n in range(0, len(index_list)):
        print(index_list[n])

os.system("pause")

打包命令
# pyinstaller -F xxx.py

-F表示打包成一个exe

总结:
python真香