一个同事每月都有一个工作，就是把某个表格里面的服务器名称整理一下，再连同uptime等存到另一个report表格中。并且表示自己已经这么手动干了三年了。。。
所以花了一点时间帮忙用Python写了一个脚本，涉及到python读入写出excel表格的两种新旧格式xls和xlsx，以及一些简单的UI诸如file exploer, popmessage等．
import pandas import xlrd import xlwt import sys import os import pandas as pd import numpy as np import tabula from openpyxl import load_workbook import xlrd import tkinter as tk from tkinter import filedialog as fd import datetime from xlutils.copy import copy import xlsxwriter def 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.path def 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("吉祥如意! (Finish) 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转成可执行文件压缩发送．