操作 excel 读 xlrd

安装相关模块 pip install xlrd

import xlrd
import datetime

# 打开文件
wb = xlrd.open_workbook('test.xlsx')

# 获取表
table = wb.sheets()[0]
table2 = wb.sheet_by_index(1)
table3 = wb.sheet_by_name(u'Sheet3')

# 获取行列值
nrows = table.nrows
ncols = table.ncols
print(nrows)
print(ncols)

# 循环遍历整行数据
for i in range(nrows-42):
    print(table.row_values(i))

# 单元格处理
cell_A1 = table.cell(0, 0).value
cell_B2 = table.row(0)[1].value
cell_A2 = table.col(1)[0].value

print(cell_A1, cell_B2, cell_A2)

# 获取所有的sheet 名字
sheets = wb._sheet_names
print(sheets)

# 获取单元格内容数据类型
sheet2 = sheets[1]
cellB2 = wb.sheet_by_name(sheet2).cell(1, 1).value.encode('utf-8')
print(cellB2)
stype = wb.sheet_by_name(sheet2).cell(1, 1).ctype
print(stype)
print(wb.sheet_by_name(sheets[0]).cell(7, 4).value)
print("type:", wb.sheet_by_index(0).cell(7, 4).ctype)
data_value = xlrd.xldate_as_tuple(wb.sheet_by_index(0).cell_value(7, 4), wb.datemode)
print(data_value)
print("dateformat:", datetime.date(*data_value[:3]).strftime('%Y/%m/%d'))
stype = wb.sheet_by_name(sheets[0]).cell(7, 4).ctype
print(stype)


# 读取合并单元格的内容
# merged_cells返回的这四个参数的含义是：(row,row_range,col,col_range),其中[row,row_range)
# 包括row,不包括row_range,col也是一样，即(1, 3, 4, 5)的含义是：第1到2行（不包括3）合并，
# (7, 8, 2, 5)的含义是：第2到4列合并。
sheet2.merged_cells
sheet2.cell_value(1, 4)

merge = []
for (rlow, rhigh, clow, chigh) in sheet2.merged_cells:
    merge.append([rlow, clow])
for index in merge:
    print(sheet2.cell_value(index[0], index[1]))