【2】文件读写-2-python读写Excel的(xlrd,xlwt)

用python来自动生成excel数据文件。python处理excel文件主要是第三方模块库xlrd、xlwt、xluntils和pyExcelerator,除此之外,python处理excel还可以用win32com和openpyxl模块。

方法一:

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# 读取excel数据
# 小罗的需求,取第二行以下的数据,然后取每行前13列的数据
import xlrd
data = xlrd.open_workbook('test.xls') # 打开xls文件
table = data.sheets()[0] # 打开第一张表
nrows = table.nrows # 获取表的行数
for i in range(nrows): # 循环逐行打印
   if i == 0: # 跳过第一行
      continue
   print table.row_values(i)[:13] # 取前十三列

方法二:test_xlrd.py

使用xlrd读取文件,使用xlwt生成Excel文件(可以控制Excel中单元格的格式)。但是用xlrd读取excel是不能对其进行操作的;而xlwt生成excel文件是不能在已有的excel文件基础上进行修改的,如需要修改文件就要使用xluntils模块。pyExcelerator模块与xlwt类似,也可以用来生成excel文件。

#coding=utf-8
#######################################################
#filename:test_xlrd.py
#author:defias
#date:xxxx-xx-xx
#function:读excel文件中的数据
#######################################################
import xlrd
#打开一个workbook
workbook = xlrd.open_workbook('E:\\Code\\Python\\testdata.xls')
#抓取所有sheet页的名称
worksheets = workbook.sheet_names()
print('worksheets is %s' %worksheets)
#定位到sheet1
worksheet1 = workbook.sheet_by_name(u'Sheet1')
"""
#通过索引顺序获取
worksheet1 = workbook.sheets()[0]
#或
worksheet1 = workbook.sheet_by_index(0)
"""
"""
#遍历所有sheet对象
for worksheet_name in worksheets:
worksheet = workbook.sheet_by_name(worksheet_name)
"""
#遍历sheet1中所有行row
num_rows = worksheet1.nrows
for curr_row in range(num_rows):
row = worksheet1.row_values(curr_row)
print('row%s is %s' %(curr_row,row))
#遍历sheet1中所有列col
num_cols = worksheet1.ncols
for curr_col in range(num_cols):
col = worksheet1.col_values(curr_col)
print('col%s is %s' %(curr_col,col))
#遍历sheet1中所有单元格cell
for rown in range(num_rows):
for coln in range(num_cols):
cell = worksheet1.cell_value(rown,coln)
print cell
"""
#其他写法:
cell = worksheet1.cell(rown,coln).value
print cell
#或
cell = worksheet1.row(rown)[coln].value
print cell
#或
cell = worksheet1.col(coln)[rown].value
print cell
#获取单元格中值的类型,类型 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
cell_type = worksheet1.cell_type(rown,coln)
print cell_type
"""

test_xlwt.py

#coding=utf-8
#######################################################
#filename:test_xlwt.py
#author:defias
#date:xxxx-xx-xx
#function:新建excel文件并写入数据
#######################################################
import xlwt
#创建workbook和sheet对象
workbook = xlwt.Workbook() #注意Workbook的开头W要大写
sheet1 = workbook.add_sheet('sheet1',cell_overwrite_ok=True)
sheet2 = workbook.add_sheet('sheet2',cell_overwrite_ok=True)
#向sheet页中写入数据
sheet1.write(0,0,'this should overwrite1')
sheet1.write(0,1,'aaaaaaaaaaaa')
sheet2.write(0,0,'this should overwrite2')
sheet2.write(1,2,'bbbbbbbbbbbbb')
"""
#-----------使用样式-----------------------------------
#初始化样式
style = xlwt.XFStyle() 
#为样式创建字体
font = xlwt.Font()
font.name = 'Times New Roman'
font.bold = True
#设置样式的字体
style.font = font
#使用样式
sheet.write(0,1,'some bold Times text',style)
"""
#保存该excel文件,有同名文件时直接覆盖
workbook.save('E:\\Code\\Python\\test2.xls')
print '创建excel文件完成!'

我的例子:

def read_panel_snps(input_file,output_file):
    workbook = xlrd.open_workbook(input_file)
    writebook = xlwt.Workbook()


    worksheets = workbook.sheet_names()
    for i in range(len(worksheets)):
        worksheet1= workbook.sheet_by_name(worksheets[i])
        # sheet_name = "sheet"+str()
        sheet_name = writebook.add_sheet("%s"%worksheets[i],cell_overwrite_ok=True)

        num_rows = worksheet1.nrows
        num_cols = worksheet1.ncols

        # for cur_row in range(num_rows):
        #     row = worksheet1.row_values(cur_row)
        #     print row
        #
        # for cur_col in range(num_cols):
        #     row = worksheet1.col_values(cur_col)
        #     print row

        for rown in range(num_rows):
            for coln in range(num_cols):
                cell = worksheet1.cell_value(rown,coln)
                gw_id,mutation = transform_id(cell)
                if mutation != "!":
                    sheet_name.write(rown,coln,gw_id)
                else:
                    sheet_name.write(rown,coln,cell)
                # print cell


        # print worksheets
    writebook.save("%s"%output_file)

其他例子

## Startup Code Begin
import xlwt
from xlwt import *
import string
import os
from datetime import datetime
import time
from time import gmtime, strftime
current_date = strftime("%b %d %Y (%H:%M)")
## Startup Code End
## Modules Begin

def _size_col(sheet, col):
    return sheet.col_width(col)

def _size_row(sheet, row):
    return sheet.row_height(row)
## Modules End
## Style variable Begin
tittle_style = xlwt.easyxf('font: height 400, name Arial Black, colour_index blue, bold on; align: wrap on, vert centre, horiz center;' "borders: top double, bottom double, left double, right double;")
subtittle_left_style = xlwt.easyxf('font: height 240, name Arial, colour_index brown, bold on, italic on; align: wrap on, vert centre, horiz left;' "borders: top double, bottom double, left double;")
subtittle_right_style = xlwt.easyxf('font: height 240, name Arial, colour_index brown, bold on, italic on; align: wrap on, vert centre, horiz left;' "borders: top double, bottom double, right double;")
subtittle_top_and_bottom_style = xlwt.easyxf('font: height 240, name Arial, colour_index black, bold off, italic on; align: wrap on, vert centre, horiz left;' "borders: top double, bottom double;")
blank_style = xlwt.easyxf('font: height 650, name Arial, colour_index brown, bold off; align: wrap on, vert centre, horiz left;' "borders: top double, bottom double, left double, right double;")
normal_style = xlwt.easyxf('font: height 240, name Arial, colour_index black, bold off; align: wrap on, vert centre, horiz left;' "borders: top double, bottom double, left double, right double;")
## Style variable End
## Module Add Begin
def print_blank_line(A, B, C, D):
    ws.write_merge(A, B, C, D, "", xlwt.easyxf('font: height 240, name Arial, colour_index black, bold off, italic on; align: wrap on, vert centre, horiz left;'))
## Module Add End
## Variable Begin
Excel = xlwt.Workbook()
## Variable End
## Sheet Name Begin
ws = Excel.add_sheet('Test Sheet')
## Sheet Name End
## Column Width Determine Begin
for A in range(100):
    ws.col(A).width = 600
## Column Width Determine Begin
## Tittle Picture Begin
ws.write_merge(0, 0, 0, 42, "", xlwt.easyxf('font: height 700, name Arial, colour_index brown, bold off; align: wrap on, vert centre, horiz left;'))
##ws.insert_bitmap('test.bmp', 0, 0, 60, 5, 1.75, 1)
## Tittle Picture End
## Subtittle Write Begin
# Variable Begin
job_name = "Nicolas_Parametric"
fab_name = "Nicolas_Parametric"
printed_by = "Nic"
printed_date = current_date

# Variable End
border_type = ["dashed", "double", ""]

ws.write_merge(1, 1, 0, 6, "Job Name:", xlwt.easyxf('font: height 240, name Arial, colour_index black, bold on, italic on; align: wrap on, vert centre, horiz left;' "borders: top double, bottom double, left double;")) #合并了6个行的单元格

ws.write_merge(1, 1, 7, 21, job_name, xlwt.easyxf('font: height 240, name Arial, colour_index black, bold off, italic on; align: wrap on, vert centre, horiz left;' "borders: top double, bottom double;"))
ws.write_merge(2, 2, 0, 6, "Fab Name:", xlwt.easyxf('font: height 240, name Arial, colour_index black, bold on, italic on; align: wrap on, vert centre, horiz left;' "borders: top double, bottom double, left double;"))
ws.write_merge(2, 2, 7, 21, fab_name, xlwt.easyxf('font: height 240, name Arial, colour_index black, bold off, italic on; align: wrap on, vert centre, horiz left;' "borders: top double, bottom double;"))
ws.write_merge(1, 1, 22, 27, "Printed By:", xlwt.easyxf('font: height 240, name Arial, colour_index black, bold on, italic on; align: wrap on, vert centre, horiz left;' "borders: top double, bottom double;"))
ws.write_merge(1, 1, 28, 42, printed_by, xlwt.easyxf('font: height 240, name Arial, colour_index black, bold off, italic on; align: wrap on, vert centre, horiz left;' "borders: top double, bottom double, right double;"))
ws.write_merge(2, 2, 22, 27, "Printed Date:", xlwt.easyxf('font: height 240, name Arial, colour_index black, bold on, italic on; align: wrap on, vert centre, horiz left;' "borders: top double, bottom double;"))
ws.write_merge(2, 2, 28, 42, printed_date, xlwt.easyxf('font: height 240, name Arial, colour_index black, bold off, italic on; align: wrap on, vert centre, horiz left;' "borders: top double, bottom double, right double;"))
print_blank_line(3, 3, 0, 42)
ws.write_merge(4, 4, 0, 42, "Report Name", xlwt.easyxf('font: height 400, name Arial, colour_index red, bold on, italic on, underline on; align: wrap on, vert centre, horiz center;'))
print_blank_line(5, 5, 0, 42)
ws.write_merge(6, 6, 0, 1, "#", xlwt.easyxf('font: height 240, name Arial, colour_index black, bold on, italic on; align: wrap on, vert centre, horiz left;' "borders: top double, bottom double, left double, right double;"))
ws.write_merge(6, 6, 2, 7, "Piecemark", xlwt.easyxf('font: height 240, name Arial, colour_index black, bold on, italic on; align: wrap on, vert centre, horiz left;' "borders: top double, bottom double, left dashed, right double;"))
ws.write_merge(6, 6, 8, 10, "Qty", xlwt.easyxf('font: height 240, name Arial, colour_index black, bold on, italic on; align: wrap on, vert centre, horiz left;' "borders: top double, bottom double, left dashed, right double;"))
ws.write_merge(6, 6, 11, 20, "Size", xlwt.easyxf('font: height 240, name Arial, colour_index black, bold on, italic on; align: wrap on, vert centre, horiz left;' "borders: top double, bottom double, left dashed, right double;"))
ws.write_merge(6, 6, 21, 34, "Status", xlwt.easyxf('font: height 240, name Arial, colour_index black, bold on, italic on; align: wrap on, vert centre, horiz left;' "borders: top double, bottom double, left dashed, right double;"))
ws.write_merge(6, 6, 35, 42, "Date", xlwt.easyxf('font: height 240, name Arial, colour_index black, bold on, italic on; align: wrap on, vert centre, horiz left;' "borders: top double, bottom double, left dashed, right double;"))
## Subtittle Write End
normal_font = xlwt.easyxf('font: height 240, name Arial, colour_index black; align: wrap on, vert centre, horiz left;' "borders: top dashed, bottom dashed, left double, right double;")

for A in range(98):
    ws.write_merge(7 + A, 7 + A, 0, 1, "", normal_font)
    ws.write_merge(7 + A, 7 + A, 2, 7, "", normal_font)
    ws.write_merge(7 + A, 7 + A, 8, 10, "", normal_font)
    ws.write_merge(7 + A, 7 + A, 11, 20, "", normal_font)
    ws.write_merge(7 + A, 7 + A, 21, 34, "", normal_font)
    ws.write_merge(7 + A, 7 + A, 35, 42, "", normal_font)
## File Save As Begin
Excel.save('Test.xls')
## File Save As End
#### Start File Begin

参考资料:

http://www.jb51.net/article/77626.htm

个人公众号,比较懒,很少更新,可以在上面提问题,如果回复不及时,可发邮件给我: tiehan@sina.cn

Sam avatar
About Sam
专注生物信息 专注转化医学