python 2.7 操作 excel

可以使用 openpyxl、pandas、openpyxl、xlsxwriter、xlwings、pywin32、 xlrd or xlwt 等py库 进行excel读写；

以下通过 xlrd 和 xlwt，demo

pip install xlrd

pip install xlwt

import xlrd

# 1、打开文件
exceld = xlrd.open_workbook(excelpath)

# 2、获取表单对象
print '表单名字:', exceld.sheet_names()  
print '表单数量:', exceld.nsheets       
print '表单对象:', exceld.sheets()       
print '通过名字查找:', exceld.sheet_by_name("test")
print '通过索引查找:', exceld.sheet_by_index(0) 

data = xlrd.open_workbook(excelpath)
table = data.sheet_by_index(0)
for rowCount in range(table.nrows):
    val = (table.row_values(rowCount))
    for v in val:
        print v

这就完成了对excel基本数据的遍历

API： https://xlrd.readthedocs.io/en/latest/api.html#module-xlrd

 

Demo：读取 身份证号码判断年龄大于60岁 写入另一个xls

#coding:utf-8
import xlrd
import xlwt

import os

excel_retpath_merge = "D:\\result.xls"
excel_retpath_age60 = "D:\\result_age60.xls"
def filterAgeMorethan60SexMale(excelpath, excel_retpath):

    data = xlrd.open_workbook(excelpath)
    table = data.sheet_by_index(0)

    # 准备写结果xls 添加结果表单
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet("result")

    # 读取 身份证号码判断年龄大于60岁 写入结果
    index = 0
    for rowCount in range(table.nrows):
        val = (table.row_values(rowCount))
        if (index == 0):    // 第一个文件的表头 是不能删的
            for i in range(0, len(val)):
                sheet.write(index, i, val[i])
            index = index + 1
        else:
            a = val[1][6:10].encode("utf-8")
            sex = val[2]
            if len(a) == 4:
                year = int(a)
                if ((2022 - year) > 60) and sex.strip() ==u"男":
                    for i in range(0, len(val)):
                        sheet.write(index, i, val[i])
                    index = index + 1
                else:
                    print sex + " age = " + str((2022 - year)) 
            else:
                print a

    workbook.save(excel_retpath)

def copySingleFile(excelpath, xls_sheet, index, wfirst):
    data = xlrd.open_workbook(excelpath)
    table = data.sheet_by_index(0)

    for rowCount in range(table.nrows):
        if ((rowCount != 0) or wfirst):     // 第一个文件的表头 是要拷贝  其他的都不拷贝
            val = (table.row_values(rowCount))
            for i in range(0, len(val)):
                xls_sheet.write(index, i, val[i])
            index = index + 1

    return index

def mergeXls(srcPath, retpath):
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet("result")

    index = 0
    line_index = 0
    for tpath, folders, files in os.walk(srcPath): 
        for filename in files:  
            [des_filename, extname] = os.path.splitext(filename)
            if extname == ".xls":
                line_index = copySingleFile(os.path.join(tpath, filename), sheet, line_index, (index == 0))
                index = index + 1

    workbook.save(retpath)

srcPath = "C:\\Users\\xxxxxx\\Documents\\huamingce"
mergeXls(srcPath, excel_retpath_merge)
filterAgeMorethan60SexMale(excel_retpath_merge, excel_retpath_age60)