一个同事每月都有一个工作，就是把某个表格里面的服务器名称整理一下，再连同uptime等存到另一个report表格中。并且表示自己已经这么手动干了三年了。。。

所以花了一点时间帮忙用Python写了一个脚本，涉及到python读入写出excel表格的两种新旧格式xls和xlsx，以及一些简单的UI诸如file exploer, popmessage等．

import pandasimport xlrdimport xlwtimport sysimport osimport pandas as pdimport numpy as npimport tabulafrom openpyxl import load_workbookimport xlrdimport tkinter as tkfrom tkinter import filedialog as fdimport datetimefrom xlutils.copy import copyimport xlsxwriterdef open_exel(file = './data/Availability-Monthly-Report-HK.xls'):    try:        data = pd.read_excel(io=file, sheet_name=0, header=3, names=["Name", "Up_time_per", "Down_Time_per", "Up_time", "Down_Time", "Type", "Path",  "Service_level"])        data.index = np.arange(0, len(data))        return data    except Exception as e:        print(str(e))def extract_data(data):    try:        use_value = []        for i in data.index:            ori_name = data.at[i, "Name"]            new_name = ori_name.split(".")[0]            data.at[i, "Name"] = new_name            use_value.append(new_name)            # print(new_name)            # print(i)        return use_value    except Exception as e:        print(str(e))class Browse(tk.Frame):    """ Creates a frame that contains a button when clicked lets the user to select    a file and put its filepath into an entry.    """    def __init__(self, master, container = '', title = '', initialdir=r".",                  filetypes=( ("All files", "*.*"), ('Excel files','*.xlsx'))):        super().__init__(master)        self.filepath = tk.StringVar()        self.title = tk.StringVar()        self._title = title        self._container = container        self._initaldir = initialdir        self._filetypes = filetypes        self._create_widgets()        self._display_widgets()    def _create_widgets(self):        self._label = tk.Label(self, textvariable=self.title, )        self._entry = tk.Entry(self, textvariable=self.filepath)        self._button = tk.Button(self, text="Browse...", command=self.browse)        self._submit = tk.Button(self, text="submit", command=self.submit)    def _display_widgets(self):        self.title.set(self._title)        self._label.pack(fill='x')        self._button.pack(side='left',)        self._entry.pack(side='left',fill='x', expand=True)        self._submit.pack(anchor='se')    def browse(self):        self.filepath.set(fd.askopenfilename(initialdir=self._initaldir,                                             filetypes=self._filetypes))        def submit(self):        self._container.set_path(self.filepath.get())        print("submit: ", self._container.get_path())        # root.quit()class FileBrowse(tk.Frame):    """ Creates a frame that contains a button when clicked lets the user to select    a file and put its filepath into an entry.    """    def __init__(self, master, container = '', title = '', initialdir=r"."):        super().__init__(master)        self.filepath = tk.StringVar()        self.title = tk.StringVar()        self._title = title        self._container = container        self._initaldir = initialdir        self._create_widgets()        self._display_widgets()    def _create_widgets(self):        self._label = tk.Label(self, textvariable=self.title, )        self._entry = tk.Entry(self, textvariable=self.filepath)        self._button = tk.Button(self, text="Browse...", command=self.browse)        self._submit = tk.Button(self, text="submit", command=self.submit)    def _display_widgets(self):        self.title.set(self._title)        self._label.pack(fill='x')        self._button.pack(side='left',)        self._entry.pack(side='left',fill='x', expand=True)        self._submit.pack(anchor='se')    def browse(self):        self.filepath.set(fd.askdirectory(initialdir=self._initaldir))        def submit(self):        self._container.set_path(self.filepath.get())        print("submit: ", self._container.get_path())class plat_ui(tk.Frame):    """ Creates a frame that contains a banch of browse    """    def __init__(self, parent, containers=[]):        super().__init__(parent)        self.filepath = tk.StringVar()        self._containers = containers        self._create_widgets()        self._display_widgets()    def _create_widgets(self):        self._browser0 = Browse(root, title = 'Availability monthly report', container = self._containers[0], filetypes=(('Excel files','*.xls'), ("All files", "*.*")))        self._browser1 = Browse(root, title = 'Target Bilag file', container = self._containers[1], filetypes=(('Excel files','*.xlsx'), ("All files", "*.*")))        self._apply = tk.Button(root, text="apply", command=self.apply)    def _display_widgets(self):        self._browser0.pack(fill='x', expand=True)        self._browser1.pack(fill='x', expand=True)        self._apply.pack(fill='x', expand=True)            def apply(self):        print("processing...")        try:            msg = exec(self._containers[0], self._containers[1])            popupmsg(msg)        except Exception as e:            popupmsg(e)def popupmsg(msg):    popup = tk.Tk()    popup.wm_title("结果")    label = tk.Label(popup, text=msg)    label.pack(side="top", fill="x", pady=10)    B1 = tk.Button(popup, text="Okay", command = popup.destroy)    B1.pack()    popup.mainloop()class input_path():    def __init__(self):        #self.path = ''        pass    def set_path(self, new_path):        self.path = new_path    def get_path(self):        return self.pathdef exec(path0, path1):    template_path = path0.get_path()    target_path = path1.get_path()    mid_output = './data/mid_output.xls'    final_output = './data/final_output.xlsx'        repdata = open_exel(template_path)    use_value = extract_data(repdata)    # rb =  xlrd.open_workbook(template_path)    # wb = copy(rb)    # ws = wb.get_sheet(0)    # for i in range(len(use_value)):    #     ws.write(i+4, 1, use_value[i])    # wb.save(mid_output)    book = load_workbook(filename = target_path)    # active_book = book.active    raw_sheet = book['Raw']    count = 0    counter = 0    for row in raw_sheet.iter_rows(min_row=2):        count += 1    if count <= len(use_value):        for row in raw_sheet.iter_rows(min_row=2, max_row=len(use_value)+1):            row[0].value = repdata['Name'][counter]            row[1].value = repdata['Up_time_per'][counter]            row[2].value = repdata['Down_Time_per'][counter]            row[3].value = repdata['Up_time'][counter]            row[4].value = repdata['Down_Time'][counter]            counter += 1    else:        for row in raw_sheet.iter_rows(min_row=2):            if counter <= len(use_value):                row[0].value = repdata['Name'][counter]                row[1].value = repdata['Up_time_per'][counter]                row[2].value = repdata['Down_Time_per'][counter]                row[3].value = repdata['Up_time'][counter]                row[4].value = repdata['Down_Time'][counter]                counter += 1            else:                row[0].value = None                row[1].value = None                row[2].value = None                row[3].value = None                row[4].value = None                counter += 1    book.save(target_path)    return("吉祥如意!\n (Finish) \n original rows in Raw is " +str(count) + "and new rows in Raw is " + str(len(use_value)))if __name__ == '__main__':    root = tk.Tk()    root.geometry("500x200")    availability_monthly_report_path = input_path()    oracle_database_path = input_path()    pathes = [availability_monthly_report_path, oracle_database_path]    platform = plat_ui(root, containers=pathes)    root.mainloop()    print("§ Finished §")

 

 这项工作唯一有意义的点在于让我了解了python处理xls和xlsx时会有蛮大区别的，因为xls是较早格式的excel表格格式，虽然仍能用python的各种库读写，但其本身的各种格式例如表格颜色等会在读入时丢失．我最终需要将pd.dataframe写入xlsx，但是写入xls的代码我以备注的格式依然保留，以做区分．

最后因为同事电脑并没有python环境，使用auto-py-to-exe转成可执行文件压缩发送．