Reading and writing Excel documents with Python-Python Tutorial-php.cn

Reading and writing Excel documents with Python

不言

Release： 2018-04-04 16:01:32

Original

2904 people have browsed it

本篇文章给大家分享的是用Python读写Excel文档，有需要的朋友可以参考一下文中的内容

1.读取Excel(需要安装xlrd)：

import xlrd
  
fname = "reflect.xls"
bk = xlrd.open_workbook(fname)
shxrange = range(bk.nsheets)
try:
 sh = bk.sheet_by_name("Sheet1")
except:
 print "no sheet in %s named Sheet1" % fname
#获取行数
nrows = sh.nrows
#获取列数
ncols = sh.ncols
print "nrows %d, ncols %d" % (nrows,ncols)
#获取第一行第一列数据
cell_value = sh.cell_value(1,1)
#print cell_value
  
row_list = []
#获取各行数据
for i in range(1,nrows):
 row_data = sh.row_values(i)
 row_list.append(row_data)

Copy after login

2.写入Excel（需安装pyExcelerator）

from pyExcelerator import *
 
w = Workbook()  #创建一个工作簿
ws = w.add_sheet(&#39;Hey, Hades&#39;)  #创建一个工作表
ws.write(0,0,&#39;bit&#39;) #在1行1列写入bit
ws.write(0,1,&#39;huang&#39;) #在1行2列写入huang
ws.write(1,0,&#39;xuan&#39;) #在2行1列写入xuan
w.save(&#39;mini.xls&#39;)  #保存

Copy after login

3.再举个自己写的读写Excel的例子

读取reflect.xls中的某些信息进行处理后写入mini.xls文件中。　

import xlrd
from pyExcelerator import *
  
w = Workbook()
ws = w.add_sheet(&#39;Sheet1&#39;)
 
fname = "reflect.xls"
bk = xlrd.open_workbook(fname)
shxrange = range(bk.nsheets)
try:
 sh = bk.sheet_by_name("Sheet1")
except:
 print "no sheet in %s named Sheet1" % fname
 
nrows = sh.nrows
ncols = sh.ncols
print "nrows %d, ncols %d" % (nrows,ncols)
  
cell_value = sh.cell_value(1,1)
#print cell_value
  
row_list = []
mydata = []
for i in range(1,nrows):
 row_data = sh.row_values(i)
 pkgdatas = row_data[3].split(&#39;,&#39;)
 #pkgdatas.split(&#39;,&#39;)
 #获取每个包的前两个字段
 for pkgdata in pkgdatas:
  pkgdata = &#39;.&#39;.join((pkgdata.split(&#39;.&#39;))[:2])
  mydata.append(pkgdata)
 #将列表排序
 mydata = list(set(mydata))
 print mydata
 #将列表转化为字符串
 mydata = &#39;,&#39;.join(mydata)
 #写入数据到每行的第一列
 ws.write(i,0,mydata)
 mydata = []
 row_list.append(row_data[3])
#print row_list
 
w.save(&#39;mini.xls&#39;)

Copy after login

4.需要根据Excel文件中满足特定要求的apk的md5值来从服务器获取相应的apk样本，这样做：　

import xlrd
import os
import shutil
  
fname = "./excelname.xls"
bk = xlrd.open_workbook(fname)
shxrange = range(bk.nsheets)
try:
 #打开Sheet1工作表
 sh = bk.sheet_by_name("Sheet1")
except:
 print "no sheet in %s named Sheet1" % fname
#获取行数
nrows = sh.nrows
#获取列数
ncols = sh.ncols
#print "nrows %d, ncols %d" % (nrows,ncols)
#获取第一行第一列数据
cell_value = sh.cell_value(1,1)
#print cell_value
  
row_list = []
#range(起始行,结束行)
for i in range(1,nrows):
 row_data = sh.row_values(i)
 if row_data[6] == "HXB":
  filename = row_data[3]+".apk"
  #print "%s %s %s" %(i,row_data[3],filename)
  filepath = r"./1/"+filename
  print "%s %s %s" %(i,row_data[3],filepath)
  if os.path.exists(filepath):
   shutil.copy(filepath, r"./myapk/")

Copy after login

使用xlwt3进行Excel文件的写操作：

import xlwt3
 
if __name__ == &#39;__main__&#39;:
     
    datas = [[&#39;a&#39;, &#39;b&#39;, &#39;c&#39;], [&#39;d&#39;, &#39;e&#39;, &#39;f&#39;], [&#39;g&#39;, &#39;h&#39;]]#二维数组
    file_path = &#39;D:\\test.xlsx&#39;
     
    wb = xlwt3.Workbook()
    sheet = wb.add_sheet(&#39;test&#39;)#sheet的名称为test
     
    #单元格的格式
    style = &#39;pattern: pattern solid, fore_colour yellow; &#39;#背景颜色为黄色
    style += &#39;font: bold on; &#39;#粗体字
    style += &#39;align: horz centre, vert center; &#39;#居中
    header_style = xlwt3.easyxf(style)
     
    row_count = len(datas)
    col_count = len(datas[0])
    for row in range(0, row_count):
        col_count = len(datas[row])
        for col in range(0, col_count):
            if row == 0:#设置表头单元格的格式
                sheet.write(row, col, datas[row][col], header_style)
            else:
                sheet.write(row, col, datas[row][col])
    wb.save(file_path)

Copy after login

输出的文件内容如下图：