python读取Excel文件

首先创建一个表格文档：具体代码如下，对操作表格进行分装，也可以不封装，看个人喜好

excel_utils.py文件

import os
import xlrd   #内置模块、第三方模块pip install  自定义模块


class ExcelUtils():
    def __init__(self,file_path,sheet_name):
        self.file_path = file_path
        self.sheet_name = sheet_name
        self.sheet = self.get_sheet()  # 整个表格对象

    def get_sheet(self):
        wb = xlrd.open_workbook(self.file_path)
        sheet = wb.sheet_by_name(self.sheet_name)
        return sheet

    def get_row_count(self):
        row_count = self.sheet.nrows
        return row_count

    def get_col_count(self):
        col_count = self.sheet.ncols
        return col_count

    # 获取表格里的内容，三种方式
    def __get_cell_value(self,row_index, col_index):
        cell_value = self.sheet.cell_value(row_index,col_index)
        return cell_value

    def get_merged_info(self):
        merged_info = self.sheet.merged_cells
        return merged_info

    def get_merged_cell_value(self,row_index, col_index):
        """既能获取普通单元格的数据又能获取合并单元格数据"""
        # cell_value = None  # 屏蔽下句代码，使用这句代码一定🉐️有合并单元格一起，不然无法读取数据，有缺陷，不推荐这种方式使用
        cell_value = self.__get_cell_value(row_index, col_index) # 使用此句代码，屏蔽上句代码，读取正常单元格，
        # 合并单元格也可以读取，相对比较完善
        for (rlow, rhigh, clow, chigh) in self.get_merged_info():
            if (row_index >= rlow and row_index < rhigh):
                if (col_index >= clow and col_index < chigh):
                    cell_value = self.__get_cell_value(rlow, clow)
                    break;  # 防止循环去进行判断出现值覆盖的情况
                else:
                    cell_value = self.__get_cell_value(row_index, col_index)
            else:
                cell_value = self.__get_cell_value(row_index, col_index)

        return cell_value
    def get_sheet_data_by_dict(self):
        all_data_list = []
        first_row = self.sheet.row(0)  #获取首行数据
        for row in range(1, self.get_row_count()):
            row_dict = {}
            for col in range(0, self.get_col_count()):
                row_dict[first_row[col].value] = self.get_merged_cell_value(row, col)
            all_data_list.append(row_dict)
        return all_data_list


# if __name__=='__main__':
#     current_path = os.path.dirname(__file__)
#     excel_path = os.path.join(os.path.dirname(__file__), '「表格地址」/data/datashell.xlsx')
#     excelUtils = ExcelUtils(excel_path, "源数据一")
#     for row in excelUtils.get_sheet_data_by_dict():
#         print( row )

读取表格data.py文件

import os
import xlrd
from excel_utils import ExcelUtils

excel_path = os.path.join(os.path.dirname(__file__), '「表格地址」/data/datashell.xlsx')
excelUtils = ExcelUtils(excel_path, "源数据一")
# print(  excelUtils.get_merged_cell_value(4,0) )
# print( excelUtils.get_row_count() )
# sheet_list = []
# for row in range(1,excelUtils.get_row_count()): #
#     row_dict = {}
#     row_dict["数据一"] = excelUtils.get_merged_cell_value(row,1)
#     row_dict["数据"] = excelUtils.get_merged_cell_value(row, 1)
#     row_dict["gfg"] = excelUtils.get_merged_cell_value(row, 2)
#     row_dict["fd"] = excelUtils.get_merged_cell_value(row, 3)
#     sheet_list.append(row_dict)
#
# for row in sheet_list:
#     print( row )

all_data_list = []
try:
    first_row = excelUtils.sheet.row(0)
    if first_row is not None:
        # print(first_row)
        for row in range(1, excelUtils.get_row_count()):
            row_dict = {}
            for col in range(0, excelUtils.get_col_count()):
                row_dict[first_row[col].value] = excelUtils.get_merged_cell_value(row, col)
            all_data_list.append(row_dict)
        for row in all_data_list:
            print(row)
except:
    print('无数据文档')