python实用脚本（二）—— 使用xlrd读取excel

本期主题：
python使用xlrd读取excel的脚本

---

往期链接：

• python实用脚本（一）—— 批量修改目标文件夹下的文件名

---

# extract number of rows using Python
import xlrd

# Give the location of the file
loc = "sample.xls"
wb = xlrd.open_workbook(loc)

# return self._sheet_list[sheetx]
# 选择excel表中的哪一个sheet
sheet = wb.sheet_by_index(0)

# cell_value选择具体哪个元素,前为行，后为列 (row ,col)
print(sheet.cell_value(0, 0))
# Extracting number of rows
print(sheet.nrows)

# extract number of columns in Python
print(sheet.ncols)

# extracting all columns name in Python
for i in range(sheet.ncols):
    print(sheet.cell_value(0, i))

# extracting first column
sheet = wb.sheet_by_index(0)
for i in range(sheet.nrows):
    print(sheet.cell_value(i, 0))

# extract a particular row value
sheet = wb.sheet_by_index(0)
print(sheet.row_values(1))

增加一个新版本，可以读取合并单元格里的值

# extract number of rows using Python
import xlrd


def str_compare(sheet):
    """
    匹配对应字符
    """
    result = 0
    for i in range(sheet.ncols):
        str1 = sheet.cell_value(0, i)
        if str1 == "B":
            print(i)
            result = i
            break
    print("find B result is ", result)


def get_basic_info(sheet):
    print("行数为： ", sheet.nrows)
    print("列数为： ", sheet.ncols)
    print("第一行的值为: ", sheet.row_values(0))
    print("第二行的值为: ", sheet.row_values(1))
    # mc = sheet.merged_cells
    # print("mc is ", mc)


def get_cells_value(sheet, row, col):
    """
    获取excel中的cell元素值，如果是merged_cells，就返回Merged_cells的值,否则直接返回对应元素值
    """
    merged_cells = sheet.merged_cells
    # judge if in merged
    for merged in merged_cells:
        if (merged[0] <= row < merged[1]
                and merged[2] <= col < merged[3]):
            print("This is a merged cell, val is ", sheet.cell_value(merged[0], merged[2]))
            return sheet.cell_value(merged[0], merged[2])
    print("unmerged cell, val is ", sheet.cell_value(row, col))
    return sheet.cell_value(row, col)


def main():
    loc = "sample.xls"
    wb = xlrd.open_workbook(loc, formatting_info=True)  # formatting_info代表着是否需要管理合并的单元格
    # 选择excel表中的哪一个sheet
    sheet = wb.sheet_by_index(0)

    # 获取基本信息
    get_basic_info(sheet)

    # 将合并的单元格数据整理
    for i in range(sheet.nrows):
        for j in range(sheet.ncols):
            print("i, j, val", i, j, get_cells_value(sheet, i, j))

    # 匹配对应字符
    # str_compare(sheet)


if __name__ == "__main__":
    main()