xlrd/xlwt读写excel文件

1.安装

pip install xlrd
pip install xlwt

2.xlrd读excel

import xlrd
path=""
book=xlrd.open_workbook(path)
sheet0=book.sheets()[0]#表格索引，从0开始
print(sheet0)
sheet1=book.sheets()[1]
print(sheet1)
rows=sheet1.nrows
cols=sheet1.ncols
print("表格总行数：",rows)
print("表格总列数：",cols)
print("第一行的值:",sheet1.row_values(0))
print("第一列的值:",sheet1.col_values(0))
print("第一行第一列的值为：",sheet1.cell(0,0).value)

excel表格内容为:

运行结果为：

<xlrd.sheet.Sheet object at 0x0000023EEF7C29C8>
<xlrd.sheet.Sheet object at 0x0000023EEF7C2188>
表格总行数： 4
表格总列数： 4
第一行的值: ['0.0', '0.1', '0.2', 0.3]
第一列的值: ['0.0', '1.0', '2.0', '3.0']
第一行第一列的值为： 0.0

其他常见用法：

zz=book.sheets()#以列表形式返回所有的表
sheet0=book.sheet_by_index(0)#根据索引获取列表
sheet1=book.sheet_by_name('Sheet1') #根据表名获取
def sheet_loaded(sheet_name_or_index):#加载表格，表格不存在则返回False
    # returns True if sheet is loaded, ``False`` otherwise.
    pass

def unload_sheet(sheet_name_or_index):#删除表格
    #param sheet_name_or_index: Name or index of sheet to be unloaded.
    pass

3.xlwt写excel

import xlwt
path2=""
book=xlwt.Workbook()
sheet0=book.add_sheet('testsheet0')
sheet0.write(0,0,"name")
sheet0.write(0,1,'sex')
sheet0.write(0,2,'age')
sheet0.write(1,0,'Tom')
sheet0.write(1,1,'mail')
sheet0.write(1,2,18)
sheet0.write(2,0,'Jain')
sheet0.write(2,1,'famail')
sheet0.write(2,2,22)
book.save(path2)

运行结果为：

3.xlwt设置字体颜色

import xlwt

work_sheet = xlwt.Workbook()
sheet =work_sheet.add_sheet("Sheet1")
style_origin ="font:colour_index orange;"
orange_style = xlwt.easyxf(style_origin)
sheet.write(0, 0, "[0,0]",orange_style)
sheet.write(0, 1, "[0,1]",orange_style)
sheet.write(0, 2, "[0,2]",orange_style)
sheet.write(1, 0, "[1,0]",orange_style)

work_sheet.save("new.xls")

4.xlwt设置单元格颜色

import os
cur_dir = os.path.dirname(__file__)
from xlwt import *

work_sheet = Workbook()
sheet =work_sheet.add_sheet("Sheet1")
# 创建样式对象
yellow_style = XFStyle()
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = Style.colour_map["yellow"]
# 样式加字体
yellow_style.pattern = pattern

# 往表中写入内容
sheet.write(0, 0, '0', style=yellow_style)
sheet.write(1, 0, '10', style=yellow_style)
sheet.write(2, 0, '20', style=yellow_style)
sheet.write(2, 2, '22', style=yellow_style)
work_sheet.save("new.xls")

5.xlwt设置列宽

from xlwt import *

work_sheet = Workbook()
sheet =work_sheet.add_sheet("Sheet1")

sheet.col(0).width = 3000
sheet.col(1).width = 5000
sheet.write(0, 0, '0')
sheet.write(1, 0, '10')
sheet.write(2, 0, '20')
sheet.write(2, 2, '22')
work_sheet.save("new.xls")