python实现excel读取

excel读取

一、前言

作者使用的是xlrd和openpyxl两个三方库，xlrd用来读取xls格式excel，openpyxl用来读取xlsx格式excel。

二、方法封装

1、读取xls

def read_xls(filepath):
    wb = xlrd.open_workbook(filepath)
    sheet = wb.sheet_by_index(0)
    rows_num = sheet.nrows
    cols_num = sheet.ncols
    row_values = [sheet.row_values(i) for i in range(rows_num)]
    return rows_num, cols_num, row_values

2、读取xlsx

def read_xlsx(filepath):
    workbook = openpyxl.load_workbook(filepath)
    sheet = workbook.active
    rows_num = sheet.max_row
    cols_num = 9
    row_values = []
    for row in sheet.values:
        row_value = []
        for value in row:
            if value is None:
                value = ''
            row_value.append(value)
        row_values.append(row_value[:-1])
    workbook.close()
    return rows_num, cols_num, row_values

三、扩展

如果使用openpyxl读取一个xlsx的多个sheet页时，可以使用如下示例：

workbook = openpyxl.load_workbook(filepath)
sheet_names = workbook.sheetnames
for sheet_name in sheet_names:
	sheet = workboook[sheet_name]
	values_list = list(sheet.values)

或者和封装的方法中一样，使用取出来放入list中