python excel 操作

excel文件内容如下：

一、xlrd 读Excel 操作

1、打开Excel文件读取数据

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

2、常用函数

（1）获取一个sheet工作表

table = file.sheets()[0]             #通过索引顺序获取
table = file.sheet_by_index(sheet_indx)  #通过索引顺序获取
table = file.sheet_by_name(sheet_name)  #通过名称获取# 以上三个函数都会返回一个xlrd.sheet.Sheet()对象names = file.sheet_names()        #返回book中所有工作表的名字
file.sheet_loaded(sheet_name or indx)    # 检查某个sheet是否导入完毕

 （2）行操作函数

nrows = table.nrows   # 获取该sheet中的行数，注，这里table.nrows后面不带().table.row(rowx)       # 返回由该行中所有的单元格对象组成的列表,这与tabel.raw()方法并没有区别。table.row_slice(rowx, start_colx=0, end_colx=None)   # 返回由该行中所有的单元格对象组成的列表table.row_types(rowx, start_colx=0, end_colx=None)  # 返回由该行中所有单元格的数据类型组成的列表；返回值为逻辑值列表，若类型为empy则为0，否则为1table.row_values(rowx, start_colx=0, end_colx=None)    # 返回由该行中所有单元格的数据组成的列表table.row_len(rowx)     # 返回该行的有效单元格长度，即这一行有多少个数据

（3）列操作函数

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)    # 返回对应位置单元格中的数据

3、代码演示

(1) 基本函数用法演示

import os
import xlrdcurrent_path = os.getcwd()
path = os.path.join(current_path, 'test.xlsx')file = xlrd.open_workbook(path)# excel中最重要的方法就是book和sheet的操作
'''（1） 获取book（excel文件）中一个工作表 '''
sheet1 = file.sheets()[0]  # 通过索引获取sheet页
sheet1 = file.sheet_by_index(0)  # 通过索引获取sheet页
sheet1 = file.sheet_by_name('Sheet1')  # 通过名称获取sheet页
print(sheet1, sheet1, sheet1)  # 以上三个函数都会返回一个xlrd.sheet.Sheet()对象names = file.sheet_names()  # #返回文件中所有工作表的名字
print(names)  # ['Sheet1', 'Sheet2']'''（2）行的操作'''
print('-----------行操作----------------')
nrows = sheet1.nrows  # 获取该sheet中的行数，注，sheet1.nrows后面不带()
print(nrows)  # 3
# 返回由该行中所有的单元格对象组成的列表
print(sheet1.row(2))  # [text:'李四', number:10.0, text:'男', text:'二']
# 返回由该行中切片单元格对象组成的列表(rowx, start_colx=0, end_colx=None)
print(sheet1.row_slice(2, 1, 3))  # [number:10.0, text:'男']
# 返回由该行中所有单元格的数据类型组成的列表,支持开始结束列选择: table.row_types(rowx, start_colx=0, end_colx=None)
print(sheet1.row_types(2))  # array('B', [1, 2, 1, 1])# 返回由该行中所有单元格的数据组成的列表，支持开始结束列选择  table.row_values(rowx, start_colx=0, end_colx=None)
print(sheet1.row_values(2))  # ['李四', 10.0, '男', '二']# 返回该行的有效单元格长度，即这一行有多少个数据
print(sheet1.row_len(2))  # 4'''（3）列操作'''
print('-----------列操作----------------')
ncols = sheet1.ncols  # 获取列表的有效列数
print(ncols)  # 4print(sheet1.col(0))  # [text:'姓名', text:'张三', text:'李四']
print(sheet1.col_slice(0, 0, 1))  # [text:'姓名']
print(sheet1.col_types(0))  # [1, 1, 1]
print(sheet1.col_values(0))  # ['姓名', '张三', '李四']'''（4）单元格操作'''
print('-----------单元格操作----------------')
print(sheet1.cell(1, 1))  # number:8.0
print(sheet1.cell_type(1, 1))  # 2
print(sheet1.cell_value(1, 1))  # 8.0

输出内容：

<xlrd.sheet.Sheet object at 0x10b7b77c0> <xlrd.sheet.Sheet object at 0x10b7b77c0> <xlrd.sheet.Sheet object at 0x10b7b77c0>
['Sheet1', 'Sheet2']
-----------行操作----------------
3
[text:'李四', number:10.0, text:'男', text:'二']
[number:10.0, text:'男']
array('B', [1, 2, 1, 1])
['李四', 10.0, '男', '二']
4
-----------列操作----------------
4
[text:'姓名', text:'张三', text:'李四']
[text:'姓名']
[1, 1, 1]
['姓名', '张三', '李四']
-----------单元格操作----------------
number:8.0
2
8.0

（2）案例演示

import os
import xlrdcurrent_path = os.getcwd()
path = os.path.join(current_path, 'test.xlsx')xlsx = xlrd.open_workbook(path)# 通过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, "行")# 获取第3列所有值（列表生成式）
name_list = [str(table.cell_value(i, 2)) for i in range(1, nrows)]
print("第3列所有的值：", name_list)

输出：

第3行2列值为 10.0
表格一共有 3 行
第3列所有的值： ['女', '男']

二、xlwt 写Excel操作

1、使用xlwt创建新表格并写入

import xlwtdef fun1():'''创建新的Excel，并写入内容'''# 创建新的workbook（其实就是创建新的excel）workbook = xlwt.Workbook(encoding='ascii')# 创建新的sheet表worksheet = workbook.add_sheet("MyNewSheet")# 往表格写入内容 （1行1列写入'内容1'，3行2列写入'内容2'）worksheet.write(0, 0, '内容1')worksheet.write(2, 1, '内容2')# 保存workbook.save("NewExcel.xls")

NewExcel.xls 内容：

 2、xlwt设置字体

import xlwtdef fun2():'''xlwt 设置字体格式'''# 创建新的workbook（其实就是创建新的excel）workbook = xlwt.Workbook(encoding='ascii')# 创建新的sheet表worksheet = workbook.add_sheet("NewSheet2")# 初始化样式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("NewExcel2.xls")

结果：

 3、xlwt 设置列宽

    xlwt中列宽的值表示方法：默认字体0的1/256为衡量单位。xlwt创建时使用的默认宽度为2960，既11个字符0的宽度。
    所以我们在设置列宽时可以用如下方法：width = 256 * 20    256为衡量单位，20表示20个字符宽度。

import xlwtdef fun3():'''xlwt 设置列宽xlwt中列宽的值表示方法：默认字体0的1/256为衡量单位。xlwt创建时使用的默认宽度为2960，既11个字符0的宽度所以我们在设置列宽时可以用如下方法：width = 256 * 20    256为衡量单位，20表示20个字符宽度'''# 创建新的workbook（其实就是创建新的excel）workbook = xlwt.Workbook(encoding='ascii')# 创建新的sheet表worksheet = workbook.add_sheet("NewSheet3")# 往表格写入内容worksheet.write(0, 0, "内容1")worksheet.write(2, 1, '内容2')# 设置列宽worksheet.col(0).width = 256 * 20# 保存workbook.save("NewExcel.xls")

结果：

 4、xlwt 设置行高

import xlwtdef fun4():'''xlwt 设置行高行高是在单元格的样式中设置的，你可以通过自动换行通过输入文字的多少来确定行高'''# 创建新的workbook（其实就是创建新的excel）workbook = xlwt.Workbook(encoding='ascii')# 创建新的sheet表worksheet = workbook.add_sheet("NewSheet3")# 往表格写入内容worksheet.write(0, 0, "内容1")worksheet.write(2, 1, '内容2')# 设置行高style = xlwt.easyxf('font:height 720;')  # 36pt,类型小初的字号row = worksheet.row(0)row.set_style(style)# 保存workbook.save("NewExcel.xls")

结果：

5、xlwt合并列和行

import xlwtdef fun5():'''合并列和行'''# 创建新的workbook（其实就是创建新的excel）workbook = xlwt.Workbook(encoding='ascii')# 创建新的sheet表worksheet = workbook.add_sheet("NewSheet3")# 往表格写入内容worksheet.write(0, 0, "内容1")# 合并 第2行到第3行 的 第1列到第4列worksheet.write_merge(1, 2, 0, 3, '合并内容')# 保存workbook.save("NewExcel.xls")

结果：

 6、xlwt添加边框

import xlwt
def fun6():# 创建新的workbook（其实就是创建新的excel）workbook = xlwt.Workbook(encoding='ascii')# 创建新的sheet表worksheet = workbook.add_sheet("My new Sheet")# 设置边框样式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.DASHEDborders.right = xlwt.Borders.DASHEDborders.top = xlwt.Borders.DASHEDborders.bottom = xlwt.Borders.DASHEDborders.left_colour = 0x40borders.right_colour = 0x40borders.top_colour = 0x40borders.bottom_colour = 0x40style = xlwt.XFStyle()  # Create Stylestyle.borders = borders  # Add Borders to Styleworksheet.write(2, 3, '内容1', style)worksheet.write(2, 1, "内容2")# 保存workbook.save("NewExcel.xls")

结果：

 

 7、设置单元格背景颜色

def fun7():# 创建新的workbook（其实就是创建新的excel）workbook = xlwt.Workbook(encoding='ascii')# 创建新的sheet表worksheet = workbook.add_sheet("My new Sheet")# 创建样式pattern = xlwt.Pattern()# May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12pattern.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 = 5style = xlwt.XFStyle()style.pattern = pattern# 往表格写入内容worksheet.write(0, 0, "内容1")# 使用样式worksheet.write(2, 1, "内容2", style)# 保存workbook.save("NewExcel.xls")

结果：

 8、设置单元格对齐

def fun8():# 创建新的workbook（其实就是创建新的excel）workbook = xlwt.Workbook(encoding='ascii')# 创建新的sheet表worksheet = workbook.add_sheet("My new Sheet")# 设置样式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(0, 0, "内容1")# 使用样式worksheet.write(2, 1, "内容2", style)# 保存workbook.save("NewExcel.xls")

结果：