python - 表格处理模块对比与汇总，常用处理数据代码函数实例

回顾总结：

python - xlrd模块使用基本操作一览总表： https://blog.csdn.net/weixin_41176932/article/details/128076390
python - openpyxl模块使用基本操作一览总表： https://blog.csdn.net/weixin_41176932/article/details/128076894
python - 用openpyxl模块将指定路径下所有图片插入到excel表格中的三种需求方法：https://blog.csdn.net/weixin_41176932/article/details/128041082
python - csv模块的基础读写操作使用：https://blog.csdn.net/weixin_41176932/article/details/129126479

表格文件处理模块对比

• xls格式是Excel2003版本及其以前版本所生成的文件格式。 其最大的特点就是：仅有65536行、256列。因此规模过大的数据不可以使用xls格式读写。
• xlsx为Excel2007及其之后的表格格式，也是现在Excel表格的主流格式。
  与xls相比，它可以存储1048576行、16384列数据，存储相同数据，xlsx格式要比xls格式文件要小得很多。
• CSV为逗号分隔值文件。CSV逗号分隔值文件格式，其以纯文本形式存储表格数据（数字和文本），可以用Excel软件打开。

• xlrd 模块既可读取 xls 文件也可读取 xlsx 文件。
• xlwt 模块只能写xls文件，不能写xlsx文件(写xlsx程序不会报错，但最后文件无法直接打开，会报错)。默认保存在py文件相同路径下，如果该路径下有相同文件，会被新创建的文件覆盖。
• openpyxl 模块读、写和修改xlsx文件(Excel 2010 xlsx / xlsm / xltx / xltm)，不能处理xls文件。
• pandas 可以从各种文件格式 CSV、JSON、SQL、Microsoft Excel 导入数据。

常用代码实例函数

返回某个文件夹下的所有指定格式文件路径

# 获取某文档路径下所有指定xls, xlsx格式文件的路径
def selectFiles(file_path):
    '''
    :file_path: 指文件夹路径。查找某路径下符合.xls, .xlsx类型文件
    :return: ret
    '''
file_ext = ['.xls', '.xlsx']
    ret = []
    for path in os.listdir(file_path):
        path_list = os.path.join(file_path, path)  # 连接当前目录及文件或文件夹名称
        if os.path.isfile(path_list):  # 判断当前文件或文件夹是否是文件，把文件夹排除
            if (os.path.splitext(path_list)[1]) in file_ext:  # 判断取得文件的扩展名是否是.xls、.xlsx
                ret.append(path_list)
    ret.sort()  # 从小到大排序
    return ret

返回某个文件夹下的文件夹路径

# 返回某个文件夹下的文件夹（带路径），oneDoc指文件夹路径
def getListDir(oneDoc):
    os.chdir(oneDoc)
    listDoc = [f for f in os.listdir(oneDoc) if os.path.isdir(os.path.join(oneDoc, f))]
    for i in range(len(listDoc)):
        listDoc[i] = os.path.abspath(listDoc[i])
    listDoc.sort()
    return listDoc

获取执行文件路径，创建表格

# os.path.abspath(__file__)对当前所执行的模块的路径进行获取其绝对路径（不能直接在Python控制台下运行，因为在没有任何脚本执行的时候，它是没有对_ _ file_ _进行定义的。）
img_path = os.path.dirname(sys.argv[0])    # sys.argv[0]获取到一个文件的绝对路径【需要import sys】。sys.argv[0]相当于os.path.abspath(__file__)

inputWb = sys.argv[1]       # sys.argv[1]获取到第二个文件的绝对路径, 即需要插入图片的目标文件
wb = openpyxl.load_workbook(inputWb)     # 创建空的Workbook对象。打开excel
sheetList = wb.sheetnames   # 获取当前工作簿中 所有表的名字

创建文件夹并保存文件

os.chdir(img_path)   # 更改路径为运行文件img.py下的路径 python3 /user/.../img.py
# 如果文件夹不存在就创建，path:'./output'需要创建的文件夹路径
if not os.path.exists('./output'):
    os.mkdir('./output')

curr_time = datetime.datetime.now()     # 2019-07-06 14:55:56.873893 <class 'datetime.datetime'>
timeStr = './output/output_'+curr_time.strftime("%Y%m%d-%H%M%S")+'.xlsx'
wb.save(timeStr)   # 插入完成保存
print('图片插入完成：', timeStr)

查看某列有多少非空值（某开始行到遇到第一个空白单元格）

# 查看某列有多少非空值（某开始行到遇到第一个空白单元格）。如读取sheet表对象，A1开始，A列有多少个非空值 - ColNonEmpty(sheet, startrow, col)
def ColNonEmpty(sheet, startrow, col):
    '''
    :param sheet: 查找的表对象
    :param startrow: 查找的表对象开始的行
    :param col: 查找的表对象的某列（查看此列开始行到遇到第一个空白单元格有多少非空值）
    :return: num非空数, num_valueList非空数值的列表
    '''
    num_valueList = []
    for i in range(startrow, sheet.max_row):
        if sheet.cell(i, col).value is not None:    # is not None是非空值，is None是空值
            num_valueList.append(sheet.cell(i, col).value)
        else:
            break
    num = len(num_valueList)
    return num, num_valueList

查找某文件的指定字符串行列位置

# 查找某文件的指定字符串行列位置
def getStringPositon(sheet):
    '''
    :param sheet: 查询的表对象sheet
    :return:  Ref Des的StartStringCol开始列数,StartStringRow开始行数位置
    '''
    # startPosition = 1
    for i in range(25, 100):
        for j in range(1, 20):
            if (sheet.cell(i, j).value == 'Ref Des'):
                StartStringCol = j
                StartStringRow = i
                break
    return StartStringCol,StartStringRow
    
StartCol,StartRow = getStringPositon(sheet_file)

带格式复制整个模板工作表到新工作表sheet（保留合并格式）

import copy
import openpyxl
# 复制整个模板表格
def copy_wholeSheet(wsCopy,wsNewCopy):
    '''
    wsCopy：复制的sheet
    wsNewCopy：复制出来的新sheet名
    '''
    # tab颜色
    wsNewCopy.sheet_properties.tabColor = wsCopy.sheet_properties.tabColor

    # 开始处理合并单元格形式为“(<CellRange A1：A4>,)，替换掉(<CellRange 和 >,)' 找到合并单元格
    wm = list(wsCopy.merged_cells)
    if len(wm) > 0:
        for i in range(0, len(wm)):
            cell2 = str(wm[i]).replace('(<CellRange ', '').replace('>,)', '')
            wsNewCopy.merge_cells(cell2)

    for i, row in enumerate(wsCopy.iter_rows()):
        wsNewCopy.row_dimensions[i+1].height = wsCopy.row_dimensions[i+1].height
        for j, cell in enumerate(row):
            wsNewCopy.column_dimensions[openpyxl.utils.get_column_letter(j+1)].width = wsCopy.column_dimensions[openpyxl.utils.get_column_letter(j+1)].width
            wsNewCopy.cell(row=i + 1, column=j + 1, value=cell.value)

            # 设置单元格格式
            source_cell = wsCopy.cell(i+1, j+1)
            target_cell = wsNewCopy.cell(i+1, j+1)
            target_cell.fill = copy.copy(source_cell.fill)
            if source_cell.has_style:
                target_cell._style = copy.copy(source_cell._style)
                target_cell.font = copy.copy(source_cell.font)
                target_cell.border = copy.copy(source_cell.border)
                target_cell.fill = copy.copy(source_cell.fill)
                target_cell.number_format = copy.copy(source_cell.number_format)
                target_cell.protection = copy.copy(source_cell.protection)
                target_cell.alignment = copy.copy(source_cell.alignment)
# 调用
wb_input = openpyxl.load_workbook(wb_Path)
wsNewCopy = wb_input.create_sheet(title)       # 如果工作表title不存在则新建
copy_wholeSheet(wsCopy=wb_input.active,wsNewCopy=wsNewCopy)

带格式复制（插入）指定的多行数据

（合并单元格的格式待解决）

# 复制插入多行数据
def insert_multi_row(ws_copy,copy_rows_start,ws_insert,insert_rows_start,num):
    '''
    @Desc   :   复制插入多行数据 
        ws_copy = 要复制的工作薄名称 
        copy_rows_start = 需要复制的开始行 
        ws_insert = 需要插入的工作表  
        insert_rows_start = 需要插入的开始行  
        num = 复制的行数
    @return :   None
    '''
    copy_rows_start = int(copy_rows_start)
    insert_rows_start = int(insert_rows_start)
    num = int(num)

    # 遍历每一行，循环插入数据
    for i in range(num):
        insert_rows = insert_rows_start + i
        copy_rows = copy_rows_start + i
        # ws_insert.insert_rows(insert_rows) #指定行号先插入空白行
        cols = ws_copy.max_column #获取列数
        for i in range(1,cols):
            #复制一行数据粘贴到另一行
            #单元格值及内容复制粘贴
            ws_insert.cell(insert_rows, i).value = ws_copy.cell(copy_rows, i).value
            ws_insert.cell(insert_rows, i).number_format = ws_copy.cell(copy_rows, i).number_format
            ws_insert.cell(insert_rows, i).font = copy(ws_copy.cell(copy_rows, i).font)
            ws_insert.cell(insert_rows, i).alignment = copy(ws_copy.cell(copy_rows, i).alignment)
            ws_insert.cell(insert_rows, i).fill = copy(ws_copy.cell(copy_rows, i).fill)
            ws_insert.cell(insert_rows, i).border = copy(ws_copy.cell(copy_rows, i).border)        
        # 行高调整
        ws_insert.row_dimensions[insert_rows + 1].height = ws_insert.row_dimensions[insert_rows].height
        ws_insert.row_dimensions[insert_rows].height = ws_copy.row_dimensions[copy_rows].height

在表格中查找特定值所在行值位置（区分/不区分大小写）

# 在Excel表格中查找特定值所在列值行值, 判断search_string的位置
def getStartPosition(sheet):
    '''
    :param sheet: 查询的表对象sheet
    :return:  start_position_row，为search String的行数位置 
    '''
    start_position_row = 1

    # 要查找的字符串
    search_string = "search String" 
    # 查找字符串
    for row in sheet.iter_rows():    # 遍历每一行
        for cell in row:    # 遍历每一个单元格
            if search_string in str(cell.value):     # 判断单元格的值是否包含指定字符串，区分大小写
            #if search_string.lower() in str(cell.value).lower():     # 判断单元格的值是否包含指定字符串，不区分大小写，str.lower()全小写，str.upper()全大写，
                start_position_row = cell.row
                print(f"找到字符串: {search_string} 在 {cell.coordinate}行{cell.row}列{cell.column}")   # 打印单元格位置
    return start_position_row