GUI拆分Excel

  1. # -*- encoding: utf-8 -*-
  2. """
  3. @Author:暗香彻骨.沐之杰 (Addis)
  4. @Contact : qyx01@qq.com 
  5. @Time: 2022/3/8 14:02
  6. @Software： PyCharm
  7. @File: 文件处理v1.3.py
  8. @Description: 可以将一个Excel工作簿中的多个工作表拆分成多个工作簿，按照指定字段
  9. pip install pyinstaller, pyinstaller -F -w
  10. """
  11. import os
  12. import pathlib
  13. import pandas as pd
  14. from openpyxl import load_workbook
  15. import ttkbootstrap as ttk
  16. from tkinter.filedialog import askdirectory
  17. from ttkbootstrap.constants import *
  18. from ttkbootstrap.dialogs import MessageDialog
  19. from ttkbootstrap.icons import Icon
  20. from ttkbootstrap.scrolled import ScrolledText
  21. from ttkbootstrap.tooltip import ToolTip
  22. from tkinter.filedialog import askopenfilename
  25. def show_dialog(msg, **kwargs):
  26.     md = MessageDialog(title="警告！", message=msg, buttons=["OK:primary"],
  27.                        icon=Icon.error,
  28.                        alert=True,
  29.                        )
  30.     if "position" in kwargs:
  31.         position = kwargs.pop("position")
  32.     else:
  33.         position = None
  34.     md.show()
  37. class FileHandling(ttk.Frame):
  38.     def __init__(self, master):
  39.         super().__init__(master, padding=15)
  40.         self.suffix_entry = None
  41.         self.fields_combox = None
  42.         self.sheets_combox = None
  43.         self.pack(fill=BOTH, expand=YES)
  45.         _path = pathlib.Path().absolute().as_posix()
  46.         self.path_var = ttk.StringVar(value=_path)
  47.         self.term_var = ttk.StringVar(value='md')
  48.         self.filename = ttk.StringVar()
  49.         # 目标文件
  50.         self.file_path = None
  51.         # 输出文件路径
  52.         self.out_path = None
  53.         self.sheets_data = []
  54.         self.fields_data = []
  55.         # header and labelframe option container
  56.         option_text = "操作区域"
  57.         self.option_lf = ttk.Labelframe(self, text=option_text, padding=15)
  58.         self.option_lf.pack(fill=X, expand=YES, anchor=N)
  60.         self.create_path_row()
  61.         self.create_file_row()
  62.         self.create_textbox()
  63.         self.create_combox_row()
  64.         self.create_suffix_row()
  65.         self.create_author_info()
  67.     def create_path_row(self):
  68.         """添加该组件 to labelframe"""
  69.         path_row = ttk.Frame(self.option_lf)
  70.         path_row.pack(fill=X, expand=YES)
  71.         path_lbl = ttk.Label(path_row, text="路径：", width=8)
  72.         path_lbl.pack(side=LEFT, padx=(15, 0))
  73.         path_entry = ttk.Entry(path_row, bootstyle="info", textvariable=self.path_var)
  74.         path_entry.pack(side=LEFT, fill=X, expand=YES, padx=5)
  75.         browse_btn = ttk.Button(master=path_row, text="Path", command=self.on_path, bootstyle="info",
  76.                                 width=8)
  77.         browse_btn.pack(side=LEFT, padx=5)
  78.         ToolTip(path_entry, text="该路径用于保存拆分后的文件")
  80.     def create_file_row(self):
  81.         """添加该组件 to labelframe"""
  82.         file_row = ttk.Frame(self.option_lf)
  83.         file_row.pack(fill=X, expand=YES, pady=15)
  84.         file_lbl = ttk.Label(file_row, text="文件：", width=8)
  85.         file_lbl.pack(side=LEFT, padx=(15, 0))
  86.         file_entry = ttk.Entry(file_row, bootstyle="info", textvariable=self.filename)
  87.         file_entry.pack(side=LEFT, fill=X, expand=YES, padx=5)
  88.         browse_btn = ttk.Button(master=file_row, text="File", command=self.open_file, bootstyle="info",
  89.                                 width=8)
  90.         browse_btn.pack(side=LEFT, padx=5)
  92.     def create_combox_row(self):
  93.         """添加该组件 to labelframe"""
  94.         combox_row = ttk.Frame(self.option_lf)
  95.         combox_row.pack(fill=X, expand=YES, pady=15)
  97.         sheets_combox_lbl = ttk.Label(combox_row, text="工作表：", width=8)
  98.         sheets_combox_lbl.pack(side=LEFT, padx=(15, 0))
  99.         self.sheets_combox = ttk.Combobox(combox_row, values=self.sheets_data, state="readonly", bootstyle="info")
  100.         self.sheets_combox.pack(side=LEFT, padx=5)
  101.         self.sheets_combox.bind('<<ComboboxSelected>>', self.sheet_selected)
  103.         fields_combox_lbl = ttk.Label(combox_row, text="字段名：", width=8)
  104.         fields_combox_lbl.pack(side=LEFT, padx=(15, 0))
  105.         self.fields_combox = ttk.Combobox(combox_row, values=self.fields_data, state="readonly", bootstyle="info")
  106.         self.fields_combox.pack(side=LEFT, padx=5)
  108.     def create_suffix_row(self):
  109.         """添加该组件 to labelframe"""
  110.         suffix_row = ttk.Frame(self.option_lf)
  111.         suffix_row.pack(fill=X, expand=YES, pady=15)
  112.         suffix_lbl = ttk.Label(suffix_row, text="后缀名：", width=8)
  113.         suffix_lbl.pack(side=LEFT, padx=(15, 0))
  114.         self.suffix_entry = ttk.Entry(suffix_row, bootstyle="info")
  115.         self.suffix_entry.pack(side=LEFT, fill=X, padx=5)
  116.         Start_but = ttk.Button(master=suffix_row, text="开始", command=self.on_start, bootstyle="primary", width=8)
  117.         Start_but.pack(side=RIGHT, fill=X, padx=5)
  119.     def create_textbox(self):
  120.         """Create and add the widget elements"""
  121.         style = ttk.Style()
  122.         self.textbox = ScrolledText(
  123.             master=self,
  124.             highlightcolor=style.colors.primary,
  125.             highlightbackground=style.colors.border,
  126.             highlightthickness=1,
  127.             autohide=True
  128.         )
  129.         self.textbox.pack(fill=BOTH)
  130.         default_txt = "点击Path、File，然后开始拆分Excel文件吧！"
  131.         self.textbox.insert(END, default_txt + '\n')
  133.     def create_author_info(self):
  134.         author_info = ttk.Label(master=self, text="@Copyright  沐之杰.Addis  Email：qyx01@qq.com")
  135.         author_info.pack(side=RIGHT, padx=(15, 0))
  136.         version_info = ttk.Label(master=self, text="Version：1.3")
  137.         version_info.pack(side=RIGHT, padx=(15, 15))
  139.     """ 方法区 """
  141.     def insert_log(self, log, *args):
  142.         self.textbox.insert(END, log + '\n')
  144.     def on_path(self):
  145.         """输出文件路径 for directory browse"""
  146.         out_path = askdirectory(title="Browse directory")
  147.         if out_path:
  148.             self.path_var.set(out_path)  # 文本框
  149.             self.out_path = out_path
  151.     def on_start(self):
  152.         file_path = self.file_path
  153.         out_path = self.out_path
  154.         filepath_is = os.path.exists(out_path)
  155.         if filepath_is:
  156.             if len(out_path) == 0:
  157.                 show_dialog(msg="未选择目标路径！")
  158.                 return
  159.             elif len(file_path) == 0:
  160.                 show_dialog(msg="未选择目标文件！")
  161.                 return
  162.             self.operate_excel()
  163.         else:
  164.             show_dialog(msg="目标路径错误！")
  165.             return
  167.     def open_file(self):
  168.         file_path = askopenfilename(title='选择Excel文件',
  169.                                     filetypes=[('Excel', '*.xlsx'), ('All Files', '*')])
  170.         if not file_path:
  171.             show_dialog("未选择文件！")
  172.             return
  173.         self.file_path = file_path
  174.         self.filename.set(file_path)
  175.         # 读取 excel文件中的sheetname
  176.         excel_data = pd.read_excel(file_path, None)
  177.         list_sheet = list(excel_data.keys())
  178.         # list_sheet = list(excel_data)
  179.         # print(list(excel_data))
  180.         # print(list_sheet)
  181.         self.sheets_name(list_sheet)  # 方法
  183.     # 工作表名
  184.     def sheets_name(self, list_sheet):
  185.         self.sheets_data = list_sheet
  186.         self.sheets_combox["values"] = self.sheets_data
  187.         self.sheets_combox.current(0)
  189.     # 工作表名-选择
  190.     def sheet_selected(self, *args):
  191.         sheets_name = self.sheets_combox.get()
  192.         print(sheets_name)
  193.         df = pd.read_excel(self.file_path, sheet_name=sheets_name, dtype=str)
  194.         # field_name = list(df)
  195.         print(list(df))
  196.         self.fields_data = list(df)
  197.         self.fields_name(list(df))
  199.     # 字段名
  200.     def fields_name(self, field_name):
  201.         # field_name = self.field_name()
  202.         fields_data = field_name
  203.         self.fields_combox["values"] = fields_data
  204.         self.fields_combox.current(0)
  206.     # 字段名-选择
  207.     def field_selected(self, *args):
  208.         self.field_name = self.fields_combox.get()
  209.         log = "选择了字段：" + self.field_name
  210.         self.insert_log(log)
  212.     # --------------- 读取多个工作表 -----------------
  213.     def operate_excel(self, *args):
  214.         markname = str(self.suffix_entry.get())
  215.         out_path = self.out_path + '\\'
  216.         input_sheet_name = self.sheets_combox.get()
  217.         input_separation_fieldName = self.fields_combox.get()
  218.         print("文件：", self.file_path)
  219.         print("后缀名：", markname)
  220.         print("输出路径：", out_path)
  221.         print("工作表：", input_sheet_name)
  222.         print("字段名：", input_separation_fieldName)
  223.         # excel_data = pd.read_excel(self.file_path, None)
  224.         # list_sheet = list(excel_data.keys())
  225.         # print("工作表：", list_sheet)
  227.         # input_sheet_name = self.sheets_data
  228.         input_sheet_name = input_sheet_name.split(',')
  230.         circulation = 0
  231.         for only_sheet in input_sheet_name:
  232.             circulation += 1
  233.             print(only_sheet)
  234.             data = pd.read_excel(self.file_path, sheet_name=only_sheet, dtype=str)
  235.             rows = data.shape[0]  # 获取行数 shape[1]获取列数
  236.             department_list = []
  237.             # print(data.columns)
  238.             # print(input_separation_fieldName)
  239.             print("总计 %s 行记录。" % rows)
  240.             for i in range(rows):
  241.                 temp = data[input_separation_fieldName][i]
  242.                 if temp not in department_list:
  243.                     department_list.append(temp)  # 分类存在一个列表中
  245.             # 写入Excel
  246.             if circulation == 1:
  247.                 for department in department_list:
  248.                     new_df = pd.DataFrame()
  249.                     for i in range(0, rows):
  250.                         if data[input_separation_fieldName][i] == department:
  251.                             new_df = pd.concat([new_df, data.iloc[[i], :]], axis=0, ignore_index=True)
  252.                     new_df.to_excel(out_path + str(department) + markname + ".xlsx",
  253.                                     sheet_name=only_sheet,
  254.                                     index=False)  # 存成一个新excel
  256.             # writer_file = pd.ExcelWriter(file_path + '多工作表.xlsx')  # 利用pd.ExcelWriter()存多张sheets
  257.             # print(circulation)
  258.             elif circulation >= 1:
  259.                 for department in department_list:
  260.                     output_excel = pd.ExcelWriter(out_path + str(department) + markname + ".xlsx")
  261.                     book = load_workbook(output_excel)
  262.                     new_df = pd.DataFrame()
  263.                     for i in range(0, rows):
  264.                         if data[input_separation_fieldName][i] == department:
  265.                             new_df = pd.concat([new_df, data.iloc[[i], :]], axis=0, ignore_index=True)
  266.                     # new_df.to_excel(output_excel, sheet_name=only_sheet,engine='openpyxl', index=False)  # 注意加上index=FALSE 去掉index列
  267.                     # output_excel.save()
  268.                     with pd.ExcelWriter(self.output_excel, engine='openpyxl') as writer:  # doctest: +SKIP
  269.                         writer.book = book
  270.                         new_df.to_excel(writer, sheet_name=only_sheet, index=False)
  271.             else:
  272.                 print("\033[31;0m Error!")
  273.             print("End")
  274.             log = "成功：" + "总计 %s 行记录。" % rows
  275.             self.insert_log(log)
  278. if __name__ == '__main__':
  279.     app = ttk.Window(title="文件处理", themename="litera")
  280.     FileHandling(app)
  281.     app.mainloop()

API请求

def get_url_batch(address, lng='', lat=''):
    headers = {
        'User-Agent': 'Mozilla/5.0 (Linux; Android 4.0.4; Galaxy Nexus Build/IMM76B) AppleWebKit/535.19 (KHTML, like Gecko) Chrome/18.0.1025.133 Mobile Safari/535.19',
        "Content-Type": "application/json",
        "AppKey": "915610589609328640"
    }
    # 互联网url
    # url = ""
    # 政务外网url
    url = ""
    # 需要的参数
    params = {
        "page": 1,
        "limit": 10,
        "address": address,
    }
    try:
        r = requests.post(url, json=params, headers=headers)
        # print(r.text)
        res_json = json.loads(r.text)
        res_data = res_json["data"]
        # print(res_data)
        # 打印序号,先用global关键字变成一个全局变量
        global serialNumber
        serialNumber += 1
        time_now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        if len(res_data["rows"]) > 0:
            print(serialNumber, res_data["rows"][0])
            address = res_data["rows"][0]["address"]
            lat = res_data["rows"][0]["lat"]
            lng = res_data["rows"][0]["lng"]

            res = [address, lng, lat, time_now]

            # res_dict = {
            #     "province": res_data["rows"][0]["province"],
            #     "city": res_data["rows"][0]["city"],
            #     "town": res_data["rows"][0]["town"],
            #     "address": res_data["rows"][0]["address"],
            #     "lat": res_data["rows"][0]["lat"],
            #     "lng": res_data["rows"][0]["lng"]
            # }
            # print(res_dict)
            return res

        else:
            print(serialNumber, res_data["rows"])
            address = ""
            lat = ""
            lng = ""
            res = [address, lng, lat, time_now]
            return res
    except Exception as e:
        print(e)
        address = address
        lat = "异常"
        lng = "异常"
        res = [address, lng, lat, time_now]
        return res

def read_excel_v2():
    """
    返回多列更新到Excel
    @return:
    """
    df = pd.read_excel(r'../../../data_static/files/excel/地址获取经纬度.xlsx', sheet_name="结果")
    # df["标准地址"] = df["地址"].apply(lambda x: get_url(x))  # 将返回结果直接保存在新的一列
    df[["标准地址", "lng", "lat", "时间"]] = df.apply(lambda x: get_url_batch(x['地址']), axis=1, result_type='expand')
    # df[['lng_', 'lat_']] = df.apply(lambda x: bd09_to_wgs84(x['lng'], x['lat']), axis=1, result_type='expand')

    # 获取当前日期作为文件名的后缀
    time_now = datetime.now().strftime('%Y-%m-%d')
    out_file = r"../../../data_static/files/excel/地址获取经纬度 {_time}.xlsx".format(_time=time_now)
    df.to_excel(out_file, sheet_name='结果', index=False)