pythonexcel数据居中_Python 自动化操作 Excel 看这一篇就够了

0 Python Excel库对比

我们先来看一下python中能操作Excel的库对比(一共九个库)：

1 Python xlrd 读取 操作Excel

1.1 xlrd模块介绍

(1)什么是xlrd模块？python操作excel主要用到xlrd和xlwt这两个库，即xlrd是读excel，xlwt是写excel的库。

(2)为什么使用xlrd模块？在UI自动化或者接口自动化中数据维护是一个核心，所以此模块非常实用。

xlrd模块可以用于读取Excel的数据，速度非常快，推荐使用！

1.2 安装xlrd模块到python官网下载http://pypi.python.org/pypi/xlrd模块安装，前提是已经安装了python 环境。

或者在cmd窗口 pip install xlrd

pip install xlrd

我这里是anaconda自带有xlrd，所以提示已经安装：

1.3 使用介绍常用单元格的数据类型

empty(空的)

string(text)

number

date

boolean

error

blank(空白表格)

导入模块

import xlrd打开Excel文件读取数据

data = xlrd.open_workbook(filename)#文件名以及路径，如果路径或者文件名有中文给前面加一个 r常用的函数

excel中最重要的方法就是book和sheet的操作

(1)获取book(excel文件)中一个工作表

table = data.sheets()[0] #通过索引顺序获取

table = data.sheet_by_index(sheet_indx) #通过索引顺序获取

table = data.sheet_by_name(sheet_name) #通过名称获取

# 以上三个函数都会返回一个xlrd.sheet.Sheet()对象

names = data.sheet_names() #返回book中所有工作表的名字

data.sheet_loaded(sheet_name or indx) # 检查某个sheet是否导入完毕

(2) 行的操作

nrows = table.nrows

# 获取该sheet中的行数，注，这里table.nrows后面不带().

table.row(rowx)

# 返回由该行中所有的单元格对象组成的列表,这与tabel.raw()方法并没有区别。

table.row_slice(rowx)

# 返回由该行中所有的单元格对象组成的列表

table.row_types(rowx, start_colx=0, end_colx=None)

# 返回由该行中所有单元格的数据类型组成的列表；

# 返回值为逻辑值列表，若类型为empy则为0，否则为1

table.row_values(rowx, start_colx=0, end_colx=None)

# 返回由该行中所有单元格的数据组成的列表

table.row_len(rowx)

# 返回该行的有效单元格长度，即这一行有多少个数据

(3)列(colnum)的操作

ncols = table.ncols

# 获取列表的有效列数

table.col(colx, start_rowx=0, end_rowx=None)

# 返回由该列中所有的单元格对象组成的列表

table.col_slice(colx, start_rowx=0, end_rowx=None)

# 返回由该列中所有的单元格对象组成的列表

table.col_types(colx, start_rowx=0, end_rowx=None)

# 返回由该列中所有单元格的数据类型组成的列表

table.col_values(colx, start_rowx=0, end_rowx=None)

# 返回由该列中所有单元格的数据组成的列表

(4)单元格的操作

table.cell(rowx,colx)

# 返回单元格对象

table.cell_type(rowx,colx)

# 返回对应位置单元格中的数据类型

table.cell_value(rowx,colx)

# 返回对应位置单元格中的数据

1.4 实战训练

我们先在表格放入以下数据，点击保存：

使用xlrd模块进行读取：

import xlrd

xlsx = xlrd.open_workbook('./3_1 xlrd 读取 操作练习.xlsx')

# 通过sheet名查找：xlsx.sheet_by_name("sheet1")

# 通过索引查找：xlsx.sheet_by_index(3)

table = xlsx.sheet_by_index(0)

# 获取单个表格值 (2,1)表示获取第3行第2列单元格的值

value = table.cell_value(2, 1)

print("第3行2列值为",value)

# 获取表格行数

nrows = table.nrows

print("表格一共有",nrows,"行")

# 获取第4列所有值(列表生成式)

name_list = [str(table.cell_value(i, 3)) for i in range(1, nrows)]

print("第4列所有的值：",name_list)

打印结果：

列表生成式介绍：

列表生成式学习链接：

2 Python xlwt 写入 操作Excel(仅限xls格式！)

xlwt可以用于写入新的Excel表格或者在原表格基础上进行修改，速度也很快，推荐使用！

2.1 pip安装xlwt

pip install xlwt

我这里是anaconda自带有xlwt，所以提示已经安装：

2.2 使用xlwt创建新表格并写入

一开始目录下只有这两个文件：

编写xlwt新表格写入程序:

# 3.2.2 使用xlwt创建新表格并写入

def fun3_2_2():

# 创建新的workbook(其实就是创建新的excel)

workbook = xlwt.Workbook(encoding= 'ascii')

# 创建新的sheet表

worksheet = workbook.add_sheet("My new Sheet")

# 往表格写入内容

worksheet.write(0,0, "内容1")

worksheet.write(2,1, "内容2")

# 保存

workbook.save("新创建的表格.xls")

生成的表格内容如下：

2.3 xlwt 设置字体格式

程序示例：

# 3.2.3 xlwt设置字体格式

def fun3_2_3():

# 创建新的workbook(其实就是创建新的excel)

workbook = xlwt.Workbook(encoding= 'ascii')

# 创建新的sheet表

worksheet = workbook.add_sheet("My new Sheet")

# 初始化样式

style = xlwt.XFStyle()

# 为样式创建字体

font = xlwt.Font()

font.name = 'Times New Roman' #字体

font.bold = True #加粗

font.underline = True #下划线

font.italic = True #斜体

# 设置样式

style.font = font

# 往表格写入内容

worksheet.write(0,0, "内容1")

worksheet.write(2,1, "内容2",style)

# 保存

workbook.save("新创建的表格.xls")

效果如下：

2.4 xlwt 设置列宽xlwt中列宽的值表示方法：默认字体0的1/256为衡量单位。

xlwt创建时使用的默认宽度为2960，既11个字符0的宽度

所以我们在设置列宽时可以用如下方法：

width = 256 * 20 256为衡量单位，20表示20个字符宽度

程序示例：

# 3.2.4 设置列宽

def fun3_2_4():

# 创建新的workbook(其实就是创建新的excel)

workbook = xlwt.Workbook(encoding= 'ascii')

# 创建新的sheet表

worksheet = workbook.add_sheet("My new Sheet")

# 往表格写入内容

worksheet.write(0,0, "内容1")

worksheet.write(2,1, "内容2")

# 设置列宽

worksheet.col(0).width = 256*20

# 保存

workbook.save("新创建的表格.xls")

效果如下：

2.5 xlwt 设置行高

在xlwt中没有特定的函数来设置默认的列宽及行高

行高是在单元格的样式中设置的，你可以通过自动换行通过输入文字的多少来确定行高

程序示例：

# 3.2.5 设置行高

def fun3_2_5():

# 创建新的workbook(其实就是创建新的excel)

workbook = xlwt.Workbook(encoding= 'ascii')

# 创建新的sheet表

worksheet = workbook.add_sheet("My new Sheet")

# 往表格写入内容

worksheet.write(0,0, "内容1")

worksheet.write(2,1, "内容2")

# 设置行高

style = xlwt.easyxf('font:height 360;') # 18pt,类型小初的字号

row = worksheet.row(0)

row.set_style(style)

# 保存

workbook.save("新创建的表格.xls")

效果如下：

2.6 xlwt 合并列和行

程序示例：

# 3.2.6 合并列和行

def fun3_2_6():

# 创建新的workbook(其实就是创建新的excel)

workbook = xlwt.Workbook(encoding= 'ascii')

# 创建新的sheet表

worksheet = workbook.add_sheet("My new Sheet")

# 往表格写入内容

worksheet.write(0,0, "内容1")

# 合并 第1行到第2行 的 第0列到第3列

worksheet.write_merge(1, 2, 0, 3, 'Merge Test')

# 保存

workbook.save("新创建的表格.xls")

效果如下：

2.7 xlwt 添加边框

程序示例：

# 3.2.7 添加边框

def fun3_2_7():

# 创建新的workbook(其实就是创建新的excel)

workbook = xlwt.Workbook(encoding= 'ascii')

# 创建新的sheet表

worksheet = workbook.add_sheet("My new Sheet")

# 往表格写入内容

worksheet.write(0,0, "内容1")

# 设置边框样式

borders = xlwt.Borders() # Create Borders

# May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR,

# MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED,

# MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.

# DASHED虚线

# NO_LINE没有

# THIN实线

borders.left = xlwt.Borders.DASHED

borders.right = xlwt.Borders.DASHED

borders.top = xlwt.Borders.DASHED

borders.bottom = xlwt.Borders.DASHED

borders.left_colour = 0x40

borders.right_colour = 0x40

borders.top_colour = 0x40

borders.bottom_colour = 0x40

style = xlwt.XFStyle() # Create Style

style.borders = borders # Add Borders to Style

worksheet.write(0, 0, '内容1', style)

worksheet.write(2,1, "内容2")

# 保存

workbook.save("新创建的表格.xls")

效果如下：

2.8 xlwt为单元格设置背景色

程序示例：

# 设置单元格背景色

def fun3_2_8():

# 创建新的workbook(其实就是创建新的excel)

workbook = xlwt.Workbook(encoding= 'ascii')

# 创建新的sheet表

worksheet = workbook.add_sheet("My new Sheet")

# 往表格写入内容

worksheet.write(0,0, "内容1")

# 创建样式

pattern = xlwt.Pattern()

# May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12

pattern.pattern = xlwt.Pattern.SOLID_PATTERN

# May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow,

# 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow ,

# almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...

pattern.pattern_fore_colour = 5

style = xlwt.XFStyle()

style.pattern = pattern

# 使用样式

worksheet.write(2,1, "内容2",style)

效果如下：

2.9 xlwt设置单元格对齐

使用xlwt中的Alignment来设置单元格的对齐方式，其中horz代表水平对齐方式，vert代表垂直对齐方式。VERT_TOP = 0x00 上端对齐

VERT_CENTER = 0x01 居中对齐(垂直方向上)

VERT_BOTTOM = 0x02 低端对齐

HORZ_LEFT = 0x01 左端对齐

HORZ_CENTER = 0x02 居中对齐(水平方向上)

HORZ_RIGHT = 0x03 右端对齐

程序示例：

# 设置单元格对齐

def fun3_2_9():

# 创建新的workbook(其实就是创建新的excel)

workbook = xlwt.Workbook(encoding= 'ascii')

# 创建新的sheet表

worksheet = workbook.add_sheet("My new Sheet")

# 往表格写入内容

worksheet.write(0,0, "内容1")

# 设置样式

style = xlwt.XFStyle()

al = xlwt.Alignment()

# VERT_TOP = 0x00 上端对齐

# VERT_CENTER = 0x01 居中对齐(垂直方向上)

# VERT_BOTTOM = 0x02 低端对齐

# HORZ_LEFT = 0x01 左端对齐

# HORZ_CENTER = 0x02 居中对齐(水平方向上)

# HORZ_RIGHT = 0x03 右端对齐

al.horz = 0x02 # 设置水平居中

al.vert = 0x01 # 设置垂直居中

style.alignment = al

# 对齐写入

worksheet.write(2,1, "内容2",style)

# 保存

workbook.save("新创建的表格.xls")

效果如下：

3 Python xlutils 修改 操作Excel

xlutils可用于拷贝原excel或者在原excel基础上进行修改，并保存；

3.1 pip安装xlutils

pip install xlutils

安装过程：

3.2 xlutils拷贝源文件(需配合xlrd使用)

表格内容如下：