python 读取excel

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import  warnings
warnings.filterwarnings('ignore')
import openpyxl
from openpyxl.cell import MergedCell
import os
import  re

def read_excel(FileName,SheetName,ColLength=None,StartRowCol=(2,1)):
    try:
        #读excel
        if not os.path.exists(FileName):
            print("[%s] 文件不存在"%(FileName))
            return []
        wb= openpyxl.load_workbook(filename =FileName, data_only = True)
        ws =wb[SheetName]
        RowId = StartRowCol[0]
        StartCol = StartRowCol[1]
        ColLength = ColLength if ColLength else ws.max_column
        ColLength = StartCol-1 + ColLength
        #MaxRow = int(RowEnd) if RowEnd and int(RowEnd)<ws.max_row else ws.max_row
        MaxRow = ws.max_row
        ExcelData =[]
        while MaxRow >= RowId:
            ColId=StartCol
            RowData =[]
            while ColId<=ColLength:
                cell = ws.cell(row=RowId,column=ColId)
                if isinstance(cell,MergedCell):#判断该单元格是否合并单元格
                    for merged_range in ws.merged_cell_ranges:  # 循环查找该单元格所属的合并区域
                        if cell.coordinate in merged_range:
                            # 获取合并区域左上角的单元格作为该单元格的值返回
                            cell = ws.cell(row=merged_range.min_row, column=merged_range.min_col)
                            break
                value =str(cell.value) if str(cell.value)!='None'  else ''
                RowData.append(value)
                ColId+=1

            if len(set(RowData))<2 and RowData[0] == '':
                pass
            else:
                ExcelData.append(RowData)
            RowId+=1
        wb.close()
        print('读取 [%s]文件 [%s]表数据完成...'%(FileName,SheetName))
        return ExcelData
    except Exception as e:
        print(e)
        return []
if __name__ == '__main__':
    read_excel("test.xlsx",'Sheet1',ColLength=None,StartRowCol=(2,1))