【环境】
Windows 10 下,Python 3.6,使用第三方包 openpyxl。
【config.ini】[config]
;Excel文件名
XlFile=D:\test\test.xlsx
;需处理的表单名
SheetName=Sheet1
【trim_cell_for_excel.py】#encoding:utf-8
#author:walker
#date:-09-26
#summary:去除Excel单元格内字符串前后的空白
importos
importsys
importtime
importopenpyxl
fromconfigparserimportConfigParser
StartTime=time.time()
cur_dir_fullpath=os.path.dirname(os.path.abspath(__file__))
XlFile=r''
SheetName=r''
defReadConfig():
r"""读取配置文件"""
globalXlFile,SheetName
cfg=ConfigParser()
cfgFile=os.path.join(cur_dir_fullpath,r'config.ini')
ifnotos.path.exists(cfgFile):
input(cfgFile+'notfound')
sys.exit(-1)
withopen(cfgFile,mode='rb')asf:
content=f.read()
ifcontent.startswith(b'\xef\xbb\xbf'):#去掉utf8bom头
content=content[3:]
cfg.read_string(content.decode('utf8'))
ifnotcfg.sections():
input('Readconfig.inifailed...')
sys.exit(-1)
XlFile=cfg.get('config','XlFile').strip()
ifnotos.path.exists(XlFile):
print('Error:notexists%s'%XlFile)
sys.exit(-1)
print('XlFile:%s'%XlFile)
SheetName=cfg.get('config','SheetName').strip()
print('SheetName:%s'%SheetName)
print('Readconfig.inisuccessed!')
defMain():
print('Load%s...'%XlFile)
wb=openpyxl.load_workbook(XlFile)
print('Load%ssuccess!'%XlFile)
sheet=wb[SheetName]
foriinrange(1,sheet.max_row+1):
forjinrange(1,sheet.max_column+1):
rawVal=sheet.cell(i,j).value
ifnotisinstance(rawVal,str):
continue
sheet.cell(i,j).value=rawVal.strip()
print('Save%s...'%XlFile)
wb.save(XlFile)
print('Save%ssuccess!'%XlFile)
if__name__=='__main__':
ReadConfig()
Main()
print('Timetotal:%.2fs'%(time.time()-StartTime))
print('Currenttime:%s'%time.strftime(
'%Y-%m-%d%H:%M:%S',time.localtime(time.time())))