openpyxl,xlrd,win32com,wxpython,logging

柔光的暖阳◎ 2021-11-02 16:32 236阅读 0赞

*  一. openpyxl常用操作总结
 *  二. xlrd常用操作总结
 *  三. win32com常用操作总结
 *  四. 自定义异常
 *  五. 判断中文
 *  六. Excel数字字母转换
 *  七. 使用wxpython进行GUI编程
 *  八. python日志配置

# 一. openpyxl常用操作总结 #

官方文档: [https://openpyxl.readthedocs.io/en/stable/tutorial.html][https_openpyxl.readthedocs.io_en_stable_tutorial.html]

import openpyxl def read_excel(filepath): # 使用openpyxl.load_workbook()打开现有工作簿 wb = openpyxl.load_workbook(filepath, read_only=True) ws = wb.active # 获取第一个sheet row = ws.max_row # 获取最大行 col = ws.max_column # 获取最大列 li = [] for row in ws.iter_rows(min_row=2, max_row=row, min_col=1, max_col=col): li2 = [] for cell in row: li2.append(cell.value) li.append((li2[5], li2[15], li2[16], li2[17]) dic = {} for i in range(1, len(li)): if li[i-1][0] == li[i][0]: if not dic.get(li[i-1][0], ''): dic.setdefault(li[i - 1][0], []).append(li[i-1]) dic[li[i][0]].append(li[i]) return dic

# 二. xlrd常用操作总结 #

import xlrd def read_excel(filepath): wb = xlrd.open_workbook(filepath) ws = wb.sheet_by_index(0) # 获取第一个sheet row = ws.nrows # 获取最大行 col = ws.ncols # 获取最大列 li = [] for rowx in range(1, row): li2 = [] for colx in range(0, col): li2.append(ws.cell_value(rowx, colx)) li.append((li2[5], li2[15], li2[16], li2[17])) dic = {} for i in range(1, len(li)): if li[i - 1][0] == li[i][0]: if not dic.get(li[i - 1][0], ''): dic.setdefault(li[i - 1][0], []).append(li[i - 1]) dic[li[i][0]].append(li[i]) return dic

# 三. win32com常用操作总结 #

import os import win32.client as win32 class ReadExcel: def __init__(self, filepath): self.xlApp = win32.gencache.EnsureDispatch('Excel.Application') self.xlApp.Visible = 0 # self.xlApp.DisplayAlerts = 0 self.filename = filename self.xlBook = self.xlApp.Workbooks.Open(filepath) self.sheet = self.xlBook.Worksheets(1) self.nrows = self.sheet.UsedRange.Rows.Count # 最大行 self.ncols = self.sheet.UsedRange.Columns.Count # 最大列 def close(self): self.xlApp.Application.Quit() def getCell(self, row, col): return self.sheet.Cells(row, col).Value def getRange(self, row1, col1, row2, col2): return self.sheet.Range(self.sheet.Cells(row1, col1), self.sheet.Cells(row2, col2)).Value def get_excel_value(filepath): excel = ReadExcel(filepath) li = [] for row in range(1, excel.nrows + 1): tb_name = excel.getCell(row, 6) field_en = excel.getCell(row, 16) field_zh = excel.getCell(row, 17) field_type = excel.getCell(row, 18) li.append((tb_name, field_en, field_zh, field_type)) dic = {} for i in range(1, len(li)): if li[i - 1][0] == li[i][0]: if not dic.get(li[i - 1][0], ''): dic.setdefault(li[i - 1][0], []).append(li[i - 1]) dic[li[i][0]].append(li[i]) return dic

# 四. 自定义异常 #

class CustomException(Exception): '''自定义异常''' def __init__(self, msg): Exception.__init__(self, msg) self.msg = msg

# 五. 判断中文 #

def is_chinese(word): for ch in word: if '\u4e00' <= ch <= '\u9fff': return True return False

# 六. Excel数字字母转换 #

def alpha2num(alpha): if type(alpha) is not str: return alpha col = 0 power = 1 for i in range(len(alpha) - 1, -1, -1): ch = alpha[i] col += (ord(ch) - ord('A') + 1) * power power *= 26 return col def num2alpha(num): if type(num) != int: return num num = num - 1 if num > 25: ch1 = chr(num % 26 + 65) ch2 = chr(num // 26 + 64) return ch2 + ch1 else: return chr(num % 26 + 65) if __name__ == '__main__': print(alpha2num('A')) print(num2alpha(1))

# 七. 使用wxpython进行GUI编程 #

import wx import os from tools.converter import MyScript wildcard = u"Excel文件 (*.xls)|*.xls|" \ u"Excel文件 (*.xlsx)|*.xlsx|" \ u"Excel文件 (*.xlsm)|*.xlsm|" \ u"Excel文件 (*.xltx)|*.xltx|" \ u"Excel文件 (*.xltm)|*.xltm|" \ u"Excel文件 (*.xlsb)|*.xlsb|" \ u"Excel文件 (*.xlam)|*.xlam" class MyFileDialog(wx.Frame): """文件选择，保存""" # ---------------------------------------------------------------------- def __init__(self): """Constructor""" wx.Frame.__init__(self, None, -1, 'xml转sql和sh', size=(600, 500)) self.filepath = '' self.savepath = '' wx.StaticText(self, -1, '接口单元:', (20, 25)) self.interface = wx.TextCtrl(self, pos=(75, 20)) self.btnChoose = wx.Button(self, -1, u"选择文件", (75, 100)) self.Bind(wx.EVT_BUTTON, self.OnBtnChoose, self.btnChoose) self.btnSave = wx.Button(self, -1, u"保存文件", (75, 140)) self.Bind(wx.EVT_BUTTON, self.OnBtnSave, self.btnSave) self.btnSubmit = wx.Button(self, -1, u"确认转换", (75, 180)) self.Bind(wx.EVT_BUTTON, self.OnBtnSubmit, self.btnSubmit) def OnBtnChoose(self, event): '''选择文件''' dlg = wx.FileDialog(self, message=u"选择文件", defaultDir=os.getcwd(), defaultFile="", wildcard=wildcard, style=wx.FD_OPEN | wx.FD_MULTIPLE | wx.FD_CHANGE_DIR) if dlg.ShowModal() == wx.ID_OK: self.filepath = dlg.GetPath() dlg.Destroy() def OnBtnSave(self, event): '''保存文件''' dlg = wx.DirDialog(self, message=u"保存文件", style=wx.DD_DEFAULT_STYLE) if dlg.ShowModal() == wx.ID_OK: self.savepath = dlg.GetPath() dlg.Destroy() def OnBtnSubmit(self, event): '''确认转换''' if not self.filepath: msg = '请选择您要转换的文件' dlg = ErrorDialog(None, -1, msg) dlg.ShowModal() dlg.Destroy() if not self.savepath: msg = '请选择保存路径' dlg = ErrorDialog(None, -1, msg) dlg.ShowModal() dlg.Destroy() if not self.interface.GetValue(): msg = '接口单元不能为空' dlg = ErrorDialog(None, -1, msg) dlg.ShowModal() dlg.Destroy() if self.filepath and self.savepath and self.interface.GetValue(): filepath = self.filepath savepath = self.savepath interface = self.interface.GetValue() logger.info(f'interface: {interface}') logger.info(f'filepath: {filepath}') logger.info(f'savepath: {savepath}') try: script = MyScript(filepath, savepath, interface) script.get_all_files() dlg = ErrorDialog(None, -1, '转换成功!') dlg.ShowModal() dlg.Destroy() except Exception as e: logger.warning(str(e), exc_info=True) dlg = ErrorDialog(None, -1, '出错了,请查看日志文件') dlg.ShowModal() dlg.Destroy() class ErrorDialog(wx.Dialog): def __init__(self, parent, id, msg): super(ErrorDialog, self).__init__(parent, id, '提示信息', size=(600, 150)) self.app = wx.GetApp() self.msg = msg self.sizer = wx.BoxSizer(wx.VERTICAL) self.sizer.Add(wx.StaticText(self, -1, self.msg), 0, wx.ALIGN_CENTER_HORIZONTAL | wx.TOP, border=30) self.sizer.Add(wx.Button(self, wx.ID_OK), 0, wx.ALIGN_CENTER_HORIZONTAL | wx.BOTTOM, border=0) self.SetSizer(self.sizer) class App(wx.App): def __init__(self): wx.App.__init__(self) def OnInit(self): self.dialog = MyFileDialog() self.dialog.Show(True) return True if __name__ == '__main__': app = App() app.MainLoop()

# 八. python日志配置 #

import logging logger = logging.getLogger(__name__) logger.setLevel(level=logging.INFO) handler = logging.FileHandler("log.txt") handler.setLevel(logging.INFO) formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s') handler.setFormatter(formatter) logger.addHandler(handler) # 注意: 在logger.xxx()中添加一个exc_info=True参数,就可以将错误信息写入日志文件中了

转载于:https://www.cnblogs.com/haitaoli/p/11077841.html

[https_openpyxl.readthedocs.io_en_stable_tutorial.html]: https://openpyxl.readthedocs.io/en/stable/tutorial.html