python操作excel
来源:互联网 发布:单片机开机和弦音 编辑:程序博客网 时间:2024/05/15 05:07
需要安装的软件包:
python-3.0.1.msi,pywin32-213.zip
源码:
#!/usr/bin/env python
# Filenae: all_excel_to_xml.pyw
from tkinter import Tk, messagebox #python2.6及以前的版本中有所不同
import win32com.client as win32
import re
import codecs
warn = lambda app: messagebox.showwarning(app, 'Exit?')
srcfile_ID = 1 #源文件选择
srcfile = { #源文件
1 : r'D:/test/xml/osd_msg_box_eng.xml',
2 : r'D:/test/xml/osd_popup_fre.xml',
3 : r'D:/test/xml/osd_setup_fre.xml',#r'D:/test/xml/osd_setup_eng.xml',#
4 : r'D:/test/xml/osd_status_fre.xml'
}
#desfile = r'D:/test/xml/result/tmp.xml' #输出文件
desfile = { #输出文件
1 : r'D:/test/xml/result/tmp.xml',#osd_msg_box_rus.xml',#
2 : r'D:/test/xml/result/osd_popup_rus.xml',
3 : r'D:/test/xml/result/osd_setup_rus.xml',#tmp.xml',#
4 : r'D:/test/xml/result/osd_status_rus.xml'
}
comfile = r'D:/test/excel/翻译英文文档(二次翻译后整理).xls' #字符对照表
sheetname = { #表单选择
1 : 'osd_msg_box_new',
2 : 'osd_popup',
3 : 'osd_setup',
4 : 'osd_status'
}
language = 10 #语言选择(excel文档中语言所在列号)
def fun_1(line, rep):
rel = line
m2 = re.search('</.*(T|t)ext>(</listEntry>|)$', line)
m = re.sub('</.*(T|t)ext>(</listEntry>|)$', '', line)
m1 = re.search('^.*(T|t)ext.*>', m)
if(rep != ''):
s = [m1.group(), rep, m2.group(), '/n']
else :
if m1 is not None: #</passwordText>特殊处理
s = [m1.group(), m2.group(), '/n']
else:
return rel
rel = ''.join(s)
return rel
def fun_2(line, rep):
rel = line
m1 = re.search('^.*<.*text="', line)
m = re.sub('^.*<.*text="', '', line)
m2 = re.search('">$', line)
if(rep != ''):
s = [m1.group(), rep, m2.group(), '/n']
else:
s = [m1.group(), m2.group(), '/n']
rel = ''.join(s)
return rel
def fun_3(line, rep):
rel = line
m2 = re.search('</.*StrList(CD|JPEG|)>$', line)
m = re.sub('</.*StrList(CD|JPEG|)>$', '', line)
m1 = re.search('^.*<.*StrList(CD|JPEG|).*>', m)
if(rep != ''):
s = [m1.group(), rep, m2.group(), '/n']
else:
s = [m1.group(), m2.group(), '/n']
rel = ''.join(s)
return rel
def fun_4(line, rep):
rel = line
m2 = re.search('</info>$', line)
m = re.sub('</info>$', '', line)
m1 = re.search('^.*<info>', m)
if(rep != ''):
s = [m1.group(), rep, m2.group(), '/n']
else:
s = [m1.group(), m2.group(), '/n']
rel = ''.join(s)
return rel
def fun_5(line, rep):
rel = line
m2 = re.search('</defineStr>$', line)
m = re.sub('</defineStr>$', '', line)
m1 = re.search('^.*<defineStr.*>', m)
if(rep != ''):
s = [m1.group(), rep, m2.group(), '/n']
else:
s = [m1.group(), m2.group(), '/n']
rel = ''.join(s)
return rel
def xml():
#指定要用到的window应用调用。
app = 'Excel'
xl = win32.gencache.EnsureDispatch('%s.Application' % app)
#打开文件
ss = xl.Workbooks.Open(comfile)
#指定操作的页面
sh = ss.Sheets(sheetname[srcfile_ID])
xl.Visible = False
ab = {
'1' : fun_1,
'2' : fun_2,
'3' : fun_3,
'4' : fun_4,
'5' : fun_5
}
linenum = 0
i = 2
fr = open(srcfile[srcfile_ID], 'r')
##注意文件编码方式
fw = codecs.open(desfile[srcfile_ID], 'w', 'utf-8')
while True:
line = fr.readline();
if len(line) == 0:
break
linenum += 1
key = '0'
m = None
m = re.match('.*<.*(T|t)ext.*>.*</.*(T|t)ext>(</listEntry>|)$', line)
if m is not None:
key = '1'
m = re.match('.*<.*text=".*">$', line)
if m is not None:
key = '2'
m = re.match('.*</.*StrList(CD|JPEG|)>$', line)
if m is not None:
key = '3'
m = re.match('.*<info>.*</info>$', line)
if m is not None:
key = '4'
m = re.match('^.*<defineStr.*</defineStr>$', line)
if m is not None:
key = '5'
if key is not '0':
n_tmp = sh.Cells(i, 1).Value
if(n_tmp == None):
n = 0
else:
n = float(n_tmp)
while n == 0 and sh.Cells(i+1, 1).Value != None:
i += 1
n_tmp = sh.Cells(i, 1).Value
if(n_tmp == None):
n = 0
else:
n = float(n_tmp)
tmp = None
if(n == linenum):
tmp = sh.Cells(i, language).Value
i += 1
#print('%d Value:%f linenum;%d %s' % (i, n, linenum, tmp))
##注意excel表项为None与字符串‘None’的区分。
if(None == tmp):
tmp = ''
s = ab[key](line, str(tmp))
fw.write(s)
else:
fw.write(line)
fr.close()
fw.close()
warn(app)
ss.Close(False)
xl.Application.Quit()
if __name__ == '__main__':
Tk().withdraw()
xml()
print('End !')
- Flask Excel python操作excel
- 使用python操作excel
- python 操作 excel
- python操作excel
- python操作excel
- python操作Excel word
- python操作Excel
- Python操作Excel
- python操作excel
- python 写Excel操作
- Python Excel操作
- python操作excel
- python 操作Excel
- python操作excel
- python操作excel
- Python操作Excel
- Python-操作Excel
- Python操作Excel
- 代理类和虚复制函数
- 在PEA上海做演讲主题:大型、高负载网站架构和应用初探(转载)
- ORACLE 直连代码
- 负载均衡技术
- 开始我的第一个博客
- python操作excel
- 职场谈判不要谈“价钱” 只需说明“价值”几何
- ANT示例代码
- LoadRunner脚本回放问题及解决
- 长矛或是匕首或是烧火棍(高手兵刃C++)
- 需要在网上建立一个自己的空间来督促学习
- 用短信控制PC
- 40种网站设计常用技巧(转载)
- Makefile学习笔记1:Linux平台Makefile文件的编写基础篇(zz)