Python使用openpyxl读写excel文件

sljsxy

已于 2023-06-13 15:15:41 修改

阅读量1.8k

点赞数 1

分类专栏： Python 文章标签： python

于 2023-06-12 17:10:03 首次发布

本文链接：https://blog.csdn.net/sljsxy/article/details/131171689

版权

Python 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

需求：读入sample.xlsx中的信息，通过分析其中的身份证号信息，得到每个人的出生日期，性别，年龄，所在省份，星座，属相等等，将结果写入到另一个excel文件中。

首先，要使用openpyxl第三方库需要安装，安装方法如下：

pip install openpyxl

程序一开头为了能使用第三方库，需要先导入

from openpyxl import Workbook,load_workbook
from openpyxl.styles import Border,Alignment
from datetime import datetime

读入excel文件，然后读入指定工作表

 wb1 = load_workbook("sample.xlsx")
 ws1 = wb1["Sheet1"]

创建第二个excel工作簿文件，并设定工作表的名字，然后设置列宽，为了能够正常显示全部身份证号和出生日期

#创建工作簿和工作表
wb2 = Workbook()
sheet_active = wb2.active
sheet_active.title = 'info_sheet'
ws2 = wb2['info_sheet']
ws2.column_dimensions['B'].width = 20       #设置列宽
ws2.column_dimensions['D'].width = 12

定义一个函数writeline写一行数据到ws工作表

def writeLine(ws,row_index,DataList):
    for index,item in enumerate(DataList):
        cell = ws.cell(row_index, index+1, item)
        cell.border = border
        cell.alignment = alignment
    row_index += 1
    return row_index

下面就是写工作表数据，首先写标题行，然后从第一个表中读取第2到末尾行，第2到末尾列中所有单元格的值，转为列表作为原始数据。iter_rows()函数的参数含义如下：

因此为什么是第2行开始呢，因为第1行是标题行啊，为什么从第2列开始呢，因为原始excel文件的格式是这样子的：

原始数据data中每一行的数据结构此时是一个元组tuple，为了后面添加数据方便，首先用list()将其转为列表，然后调用函数get_info()，得到想要的其他数据后添加到列表后面，构成一行新的数据，用writeline()函数将数据写入到新的excel文件中。

#写标题行
title = ['姓名','身份证号','关系','出生日期','性别','年龄','所在地']
rows = writeLine(ws2, 1, title)

#写数据行
row_index = 2
data = list(ws1.iter_rows(min_row=2,min_col=2,values_only=True))
for row in data:
     pInfo = list(row)
     pInfo += list(get_info(row[1]))
     row_index = writeLine(ws2, row_index, pInfo)

其中get_info()函数就是得到出生日期，性别，年龄和所在省份等等信息。

出生日期先用身份证号的6到13位构造年月日字符串，比如身份证号'301213200010110080'中的[6:14]表示取从索引为6的字符（起点包括）取到索引为14（终点不包括），即取出'20001011',中间添加上'-'，构成'2000-10-11‘，然后利用datatime库的datetime类的格式化时间函数strptime将这个字符串转换为一个日期时间值，为了只取日期值，后面再加上.date()方法。

性别gender则是按照身份证号的倒数第二位是奇数还是偶数分别判定是'男' 还是'女'.

年龄age是由当前的年份减去身份证号里的出生年得到。

所在省份site是根据身份证号的前2位，到省份字典pDict中查找键所对应的值得到，这里使用了字典的get（）函数，get（）函数如果能找到键，则返回键对应的值，如果找不到这个键，则返回默认值即为空字符串。

#定义get_info()函数，根据身份证号计算出生日期，性别，年龄等信息
def get_info(IdNumber):
    #创建省份字典，后面用身份证号的前2位到字典里查找得到所在地，属相、星座可用类似方法
    pDict = {'11':'北京','12':'天津','13':'河北','14':'山西','15':'内蒙古','21':'辽宁',\
             '22':'吉林','23':'黑龙江','31':'上海','32':'江苏','33':'浙江','34':'安徽',\
             '35':'福建','36':'江西','37':'山东','41':'河南','42':'湖北','43':'湖南',\
             '44':'广东','45':'广西','46':'海南','50':'重庆','51':'四川','52':'贵州',\
             '53':'云南','54':'西藏','61':'陕西','62':'甘肃','63':'青海','64':'宁夏',\
             '65':'新疆','71':'台湾','81':'香港','82':'澳门'}
    birthStr = IdNumber[6:10] + '-' + IdNumber[10:12] + '-' + IdNumber[12:14]
    birth = datetime.strptime(birthStr, "%Y-%m-%d").date()
    gender = '女' if eval(IdNumber[-2])%2==0 else '男'
    age = datetime.now().year-eval(IdNumber[6:10])
    site = pDict.get(IdNumber[:2],'')
    return birth,gender,age,site

完整代码如下：

#getInfo.py
from openpyxl import Workbook,load_workbook
from openpyxl.styles import Border,Alignment
from datetime import datetime

#设置全局样式
border = Border(bottom=Side(style='thin', color='000000'),
                right=Side(style='thin', color='000000'),
                left=Side(style='thin', color='000000'),
                top=Side(style='thin', color='000000'))
alignment = Alignment(horizontal='center', vertical='center')

#定义get_info()函数，根据身份证号计算出生日期，性别，年龄等信息
def get_info(IdNumber):
    #创建省份字典，后面用身份证号的前2位到字典里查找得到所在地，属相、星座可用类似方法
    pDict = {'11':'北京','12':'天津','13':'河北','14':'山西','15':'内蒙古','21':'辽宁',\
             '22':'吉林','23':'黑龙江','31':'上海','32':'江苏','33':'浙江','34':'安徽',\
             '35':'福建','36':'江西','37':'山东','41':'河南','42':'湖北','43':'湖南',\
             '44':'广东','45':'广西','46':'海南','50':'重庆','51':'四川','52':'贵州',\
             '53':'云南','54':'西藏','61':'陕西','62':'甘肃','63':'青海','64':'宁夏',\
             '65':'新疆','71':'台湾','81':'香港','82':'澳门'}
    birthStr = IdNumber[6:10] + '-' + IdNumber[10:12] + '-' + IdNumber[12:14]
    birth = datetime.strptime(birthStr, "%Y-%m-%d").date()
    gender = '女' if eval(IdNumber[-2])%2==0 else '男'
    age = datetime.now().year-eval(IdNumber[6:10])
    site = pDict.get(IdNumber[:2],'')
    return birth,gender,age,site

#写一行数据到ws工作表
def writeLine(ws,row_index,DataList):
    for index,item in enumerate(DataList):
        cell = ws.cell(row_index, index+1, item)
        cell.border = border
        cell.alignment = alignment
    row_index += 1
    return row_index
    
def main():
    #打开sample工作簿，并打开sheet1
    try:
        wb1 = load_workbook("sample.xlsx")
        ws1 = wb1["Sheet1"]

        #创建工作簿和工作表
        wb2 = Workbook()
        sheet_active = wb2.active
        sheet_active.title = 'info_sheet'
        ws2 = wb2['info_sheet']
        ws2.column_dimensions['B'].width = 20       #设置列宽
        ws2.column_dimensions['D'].width = 12

        #写标题行
        title = ['姓名','身份证号','关系','出生日期','性别','年龄','所在地']
        rows = writeLine(ws2, 1, title)

        #写数据行
        row_index = 2
        data = list(ws1.iter_rows(min_row=2,min_col=2,values_only=True))
        for row in data:
            pInfo = list(row)
            pInfo += list(get_info(row[1]))
            row_index = writeLine(ws2, row_index, pInfo)
            
        #接收用户输入添加信息并保存
        while True:
            UserInput = input("请输入用户的姓名 身份证号 关系，以空格分隔，直接按回车键退出：\n")
            if UserInput!='':
                UserInfo = UserInput.split()
                UserInfo += get_info(UserInfo[1])
                row_index = writeLine(ws2, row_index,UserInfo)
            else:
                break

        wb2.save("info.xlsx")   #保存文件
    except FileNotFoundError:
        print("sample.xlsx文件未找到，请检查文件名和路径！")

main()

最后附上完整的代码和sample.xlsx文件下载链接

https://download.csdn.net/download/sljsxy/87898935