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

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

一、 读取多个sheet

import xlrd
import pandas as pd
from pandas import DataFrame
 
DATA_DIR = 'E:/' 
excel_name = '%s2017.xls' % DATA_DIR
wb = xlrd.open_workbook(excel_name)
# print(wb)
 
# 获取workbook中所有的表格
sheets = wb.sheet_names()
# print(sheets)
 
# 循环遍历所有sheet
df_28 = DataFrame()
for i in range(len(sheets)):


    # skiprows=2 忽略前两行
    df = pd.read_excel(excel_name, sheet_name=i, skiprows=2, index=False, encoding='utf8')
    df_28 = df_28.append(df)
# 去除缺省值
df_28 = df_28.dropna()
df_28 = df_28.reset_index(drop=True)
print(len(df_28))

二、自动生成

方法一：

#!/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

五、报错

报错1

python3 中报错：

xlrd.biffh.XLRDError: Excel xlsx file； not supported

原因是最近xlrd更新到了2.0.1版本，只支持.xls文件

解决办法：

pip uninstall xlrd
pip install xlrd==1.2.0

参考资料

• http://www.jb51.net/article/77626.htm
• https://blog.csdn.net/sinat_37868031/article/details/113376079