0x00 openpyxl模块
这个模块可以让你读写excel文件
0x01 读取数据
代码如下：
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from openpyxl import load_workbook
#设置data_only=True，否则如果文件中有计算公式的话读出来的是公式不是数值
wb = load_workbook(filename='aa.xlsx',data_only=True)
sheetnames = wb.get_sheet_names() #获得所有表名
print u"存在表：%s" % sheetnames
ws = wb.get_sheet_by_name(sheetnames[0])
print u"第一张表表名为：%s" % ws.title #Sheet1
rows = ws.max_row #行数
columns = ws.max_column #列数
print "表%s有%d行%d列" % (ws.title,rows,columns) #10 2 共10行2列
print
print u"取部分数据："
print ws['A1'].value,ws['B1'].value
print ws['A2'].value,ws['B2'].value
print ws.cell(row=1, column=2).value
print u"\n输出表%s的所有数据：" % ws.title
for x in range(1,rows+1):
for y in range(1,columns+1):
print ws.cell(row=x,column=y).value,'\t',
print
结果如下：
0x02 写入数据
代码如下：
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from openpyxl import Workbook
wb = Workbook()
# 建表
ws1 = wb.active # 第一张表需要这样写，代表从第一张表开始
ws1.title = 's1'
ws2 = wb.create_sheet(title='s2')
ws3 = wb.create_sheet(title='s3')
# 写入数据
ws1['A1'] = 1111
ws1['A2'] = 2222
ws1['A3'] = 3333
ws2['A1'] = 'ssssssssss'
ws2['B1'] = 'dddddddddd'
for x in range(1,4):
for y in range(1,8):
v = int(str(x)+str(y))
_ = ws3.cell(column=x,row=y,value=v)
wb.save(filename='test.xlsx') # 保存数据
结果如下：
0x03 实际案例
$ ls
111.txt 222.txt t2x.py
$ cat 111.txt
aaa--111--AAA
bbb--222--BBB
ccc--333--CCC
ddd--444--DDD
$ cat 222.txt
hello--xiaoming--hello,world!
test--xiaohua--This is a test message.
weather--lihua--It will be sunny tomorrow.
以--进行分割，写入xlsx
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# code by reber <1070018473@qq.com>
from openpyxl import Workbook
def write2xlsx():
def get_content(filename):
data = []
with open(filename) as f:
lines = f.readlines()
for line in lines:
data.append(line.strip().split('--'))
return data
def write2sheet(ws,filename):
data = get_content(filename)
# print data
for x in range(1,len(data)+1): #第几行
for y in xrange(1,4): #第几列
print data[x-1][y-1],
_ = ws.cell(row=x,column=y,value=data[x-1][y-1])
print
wb = Workbook()
fs = ['111.txt','222.txt']
for index,filename in enumerate(fs):
sheet_name = filename.split('.')[0]
ws = wb.create_sheet(title=sheet_name,index=index)
write2sheet(ws, filename)
wb.save(filename='test.xlsx')
write2xlsx()
若未作声明则文章版权归本人(@reber )所有，转载请注明原文链接：
