openpyxl,xlrd,win32com,wxpython,logging
目录
一. openpyxl常用操作总结
官方文档: 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参数,就可以将错误信息写入日志文件中了
