用python写excel文件
安装xlwt
pip install xlwt
例子
#!/usr/bin/python
# _*_ coding:utf-8 _*_
#----------------------------------------------------------------------------
# import modules
#----------------------------------------------------------------------------
import os
import xlwt
def set_style(name, height, bold = False):
style = xlwt.XFStyle() #初始化样式
font = xlwt.Font() #为样式创建字体
font.name = name
font.bold = bold
font.color_index = 4
font.height = height
style.font = font
return style
def write_excel():
#创建工作簿
workbook = xlwt.Workbook(encoding='utf-8')
#创建sheet
data_sheet = workbook.add_sheet('demo')
row0 = [u'字段名称', u'大致时段', 'CRNTI', 'CELL-ID']
row1 = [u'测试', '15:50:33-15:52:14', 22706, 4190202]
#生成第一行和第二行
for i in range(len(row0)):
data_sheet.write(0, i, row0[i], set_style('Times New Roman', 220, True))
data_sheet.write(1, i, row1[i], set_style('Times New Roman', 220, True))
#保存文件
workbook.save('demo.xls')
if __name__ == '__main__':
write_excel()
print u'创建demo.xlsx文件成功'
用python 读excel
安装xlrd
[root[@localhost](https://my.oschina.net/u/570656) excel]# pip install xlrd
Downloading/unpacking xlrd
Downloading xlrd-1.0.0.tar.gz (2.6Mb): 2.6Mb downloaded
Running setup.py egg_info for package xlrd
warning: no files found matching 'README.html'
Installing collected packages: xlrd
Running setup.py install for xlrd
changing mode of build/scripts-2.7/runxlrd.py from 644 to 755
warning: no files found matching 'README.html'
changing mode of /usr/local/bin/runxlrd.py to 755
Successfully installed xlrd
Cleaning up...
例子
#!/usr/bin/python
# -*- coding: utf-8 -*-
import xdrlib ,sys
import xlrd
def open_excel(file= 'demo.xls'):
try:
data = xlrd.open_workbook(file)
return data
except Exception,e:
print str(e)
#根据索引获取Excel表格中的数据 参数:file:Excel文件路径 colnameindex:表头列名所在行的所以 ,by_index:表的索引
def excel_table_byindex(file= 'demo.xls',colnameindex=0,by_index=0):
data = open_excel(file)
table = data.sheets()[by_index]
nrows = table.nrows #行数
ncols = table.ncols #列数
colnames = table.row_values(colnameindex) #某一行数据
list =[]
for rownum in range(1,nrows):
row = table.row_values(rownum)
if row:
app = {}
for i in range(len(colnames)):
app[colnames[i]] = row[i]
list.append(app)
return list
#根据名称获取Excel表格中的数据 参数:file:Excel文件路径 colnameindex:表头列名所在行的所以 ,by_name:Sheet1名称
def excel_table_byname(file= 'demo.xls',colnameindex=0,by_name=u'demo'):
data = open_excel(file)
table = data.sheet_by_name(by_name)
nrows = table.nrows #行数
colnames = table.row_values(colnameindex) #某一行数据
list =[]
for rownum in range(1,nrows):
row = table.row_values(rownum)
if row:
app = {}
for i in range(len(colnames)):
app[colnames[i]] = row[i]
list.append(app)
return list
def main():
tables = excel_table_byindex()
for row in tables:
print row
tables = excel_table_byname()
for row in tables:
print row
if __name__=="__main__":
main()