openpyxl读写文件demo

开头

python处理Excel一直是自己头痛的问题，因为选择太多，有panda, 有csv, 有今天使用openpyxl。特别记录一下openpyxl的使用

安装

pip install openpyxl

简单的写demo

要知道一个前提是row是行，column是列， 我们主要的操作都是对Excel的行和列的操作

创建一个工作本

from openpyxl import Workbook # 引入头部文件
import datetime
import time
wb = Workbook()
ws = wb.active

# 创建表
ws.title = 'test1' # 这个是本子的名，如果需要另外起一个本子就是
ws.column_dimensions['A'].width = 25  # 设置列宽

ws1 = wb.create_sheet("test2") # 在同一个工作本子里再创建一个表
ws1.column_dimensions['A'].width = 25  # 设置列宽

# 写单元格
ws.cell(row=1, column=1).value='单元格1'

# 合并单元格
ws.merge_cells("A1:D1") # 指定的单元格，也可以输入数字


# 保存文件
wb.save('三国志-{}新.xlsx'.format(str(datetime.now())[:13])

简单的读demo

def read_excel(input_file_name):
    """
    从xlsx文件中读取数据
    """
    wb = openpyxl.load_workbook(input_file_name)
    
    # 可以使用workbook对象的sheetnames属性获取到excel文件中哪些表有数据
    print(wb.sheetnames)
    ws = wb.get_sheet_by_name('阴阳师')
    max_row = ws.max_row
    print(max_row)

    # 从第几行开始
    for row in range(3, max_row + 1):
        val = ws.cell(row=row, column=4).value # 具体读哪一列
        print(val)

以上就是简单的demo， 知道怎么写文件之后，就可以直接实战了

实战1

将不规则的字典写到excel中

from openpyxl import load_workbook
import locale
import collections  # 转换成有序字典
import json
from openpyxl import Workbook
"""1.josn 文件
{
    "乐观": [
      "要吐了  家底掏空 终于搞到第一套爆伤了",
      "可能这就是精神胜利大法师吧",
      "快四百天了，终于出了个能看的速度"
    ],
    "仇恨": [
      "被骗子骗了碎片后的报复记录过程：前两天在公频上看到有人喊鬼吞",
      "被骗子骗了碎片后的报复记录过程：前两天在公频上看到有人喊鬼吞",
      "吧务是策划的狗？100w给个小鹿发个****都删贴 nb",
      "网易凭什么这么针对我"
    ],
    "兴奋": [
      "终于拿到印记了  以后可以安心摸鱼了",
      "终于刷到了，我好激动啊"
    ],
    "安逸": [
      "顶着签到到了黄牌了，一般不发帖，安安心心当个吃瓜群众，今天也",
      "第一次囤这么多",
      "高级非酋成就达成",
      "回顾一下，还挺有意思的",
      "舒服了舒服了"
    ],
    "愉快": [
      "大佬的车，我爱！",
      "组队遇到一个越南小姐姐 我这英语没给中国九年义务教育丢脸吧",
      "破势氵"
    "愤怒": [
      "我带你双狗粮自动魂土你还哔哔赖赖的，我没纸人不想刷了你还有脾",
      "挂一个愿区白莲花愿意吃瓜的进来恰恰"
    ],
    "抱怨": [
      "这皮肤怎么用不了啦？？？？",
      "为了这个皮肤去砸百鬼，两次冻在面前砸不到，spssr我不配，"
    "无感": [
      "安梦奇缘劲舞团的建议",
      "关于式神赠送，",
    ],
    "无聊": [
      "氵",
      "烬天玉藻前，八岐大蛇，泷夜叉姬，炼狱茨木童子，赤影妖刀姬，不",
      "直接+15以示敬意"
    ],
    "期待": [
      "来个兔丑挖土司机  32秒全自动 最好长期搞得"
    ],
    "焦虑": [
      "服务器是崩了么我怎么进不去",
      "大佬们来救救萌新吧！！！坐标：愿予必成作案地点：斗技、道馆、",
      "求个够20级的yys绑定（我百闻牌）救救孩子吧",
      "鬼王之宴我还没队伍这可咋搞哦"
    ],
    "疑惑": [
      "听说光这一个御魂就能卖好几万",
      "新区 萌新不懂就问19天肝了5六星是什么水平",
      "有大佬在吗？本人小白，不知道这个能不能用啊？"
      "这个御魂是不是废的，能用吗"
    ],
    "疑问": [
      "大佬们，我是个小萌新，我这号是二月份玩儿的128开局，几个月",
      "老哥们垒石蜀步打不过嘟嘟吗？战法，河内  重整 避其锋芒"
    ],
    "着迷": [
      "百鬼夜行，每日一点一滴的积累",
      "从此以后，我就是网易的舔狗了！",
      "3年长跑",
      "发个帖纪念一下我靠运气喂出来的545阿离不知火加强了而且我为",
      "终于集齐了大部分挖土(魂土:)阵容"
    ],
    "绝望": [
      "吐了，死活出不了缘结神碗",
      "这…下一步怎么走呢，没得玩了想换号了",
      "老哥们 受不了了",
      "三年前脱坑，今日回归，一脸懵逼，想删游戏。",
      "救救孩子吧 九游端账号  手机坏了"
    ],
    "苦恼": [
      "求助斗技问题，我三拉茶几极限就是1950分，老实被离白日吃分",
      "夫 妻 混 合 双 打",
      "现在不加好友都能看到你放啥结界卡了？还是说我遇到奇葩了。惹不",
      "天哪 这个茶几面板到底要什么提升啊"
    ],
    "认同": [
      "好像最近出了个很大的关于强魂规律的瓜，就谈谈我个人的看法吧",
      "我希望以后你们可以叫我一声大佬",
      "半夜睡不着，总结一下yys带骗术",
      "整个阴阳师应该没有比这个更好的御魂了吧？",
      "分享一个特别平民的阴界之门70层阵容（无大舅茨林和爆伤套）（",
      "【记录】真正新手记录各种问题和求解答的帖子"
    ]
  }
"""
base_file = r'1.json'
new_file = r'a.xlsx'

with open(base_file, 'r', encoding='utf-8') as f:
    new_json = json.loads(f.read())
od = collections.OrderedDict()

for i in new_json:
    od[i] = new_json.get(i)  # 转成有序字典
wb = Workbook()
ws = wb.active
ws.append(list(od.keys()))
for i, new in enumerate(od):
    i_list = od.get(new)
    for j in range(len(i_list)):
        print(i_list[j]) # 测试
        ws.cell(j + 2, i + 1).value = i_list[j]
wb.save(new_file)

实战2

将数据库中的内容查找出来， 并按照指定的格式来排版（加边框，指定字体，字体居中，自动换行，）

from test import db
from openpyxl import Workbook
import datetime
import time
from openpyxl.styles import Font, colors,Border, Alignment, Side, PatternFill

# 这个是导出格式化表格的一个类
class OutputMsg():
    def __init__(self):
        self.title_font = Font(name='微软雅黑', size=11, italic=False,color=colors.BLACK, bold=False) # 表头的指定文字格式
        self.font = Font(name='微软雅黑') # 正文的文字格式
        self.alignment = Alignment(horizontal='center', vertical='center',wrapText=True) # 单元格居中，wrapText自动换行
        self.fill = PatternFill("solid", fgColor="ffeb9c") # 单元格填充为黄色
        
    def find_the_contents(self):
        """
        查找sql文件
        """
        sql = """
                SELECT
                a.publish_time as '发布时间',
                a.attitudes_count as '点赞数',
                a.reposts_count as '转发数',
                a.comments_count as '评论数',
                a.content as '内容',
                a.user_name as '用户名',
                 "用户主页链接",
                 "链接",
                who_publish "对应平台"
            FROM
                weibo_chaohua_tiezi a
                where who_publish = '#郭德纲#'
                order by publish_time DESC limit 2
        """
        data_list = db.query(sql)
        return data_list

    def add_border(self, data):
        # Excel添加边框
        side = Side(border_style='thin', color=colors.BLACK) # 边框的宽度和颜色
        for row in data:
            row.border=Border(top=side, bottom=side, left=side, right=side) # 上下左右画线

    def white_excel(self):
        # 写excel
        wb = Workbook()
        ws = wb.active
        data = list() # 画边框时候的使用
        ws.title = '#发布帖子#'
        ws.column_dimensions['A'].width = 25 # 指定表格长度
        ws.column_dimensions['B'].width = 8
        ws.column_dimensions['C'].width = 8
        ws.column_dimensions['D'].width = 8
        ws.column_dimensions['E'].width = 50
        ws.column_dimensions['F'].width = 25
        ws.column_dimensions['G'].width = 40
        ws.column_dimensions['H'].width = 45
        ws.column_dimensions['I'].width = 25
        
        # 表格第一行，默认指定值
        ws.cell(row=1, column=1).value='发布时间' 
        ws.cell(row=1, column=2).value='点赞数'
        ws.cell(row=1, column=3).value='转发数'
        ws.cell(row=1, column=4).value='评论数'
        ws.cell(row=1, column=5).value='内容'
        ws.cell(row=1, column=6).value='用户名'
        ws.cell(row=1, column=7).value='用户主页链接'
        ws.cell(row=1, column=8).value='用户发布链接'
        ws.cell(row=1, column=9).value='对应平台'
        
        # 给表格第一列加格式
        for i in range(1, 10):
            for j in range(1, 2):
                ws.cell(row=j, column=i).font = self.title_font
                ws.cell(row=j, column=i).alignment = self.alignment
                ws.cell(row=j, column=i).fill = self.fill
                data.append(ws.cell(row=j, column=i))
        
        now_row = 2 # 从第二行开始填充数据，随着数据的增加而变化
        base_row = 2 # 就是从一开始的表格开始，不会变化
        data_list = self.find_the_contents() # 拿到数据
        if data_list:
            for datas in data_list:
                ws.cell(row=now_row, column=1, value=datas['发布时间']).number_format = 'yyyy/mm/dd hh:mm:ss' # 指定单元格为时间格式
                ws.cell(row=now_row, column=2, value=datas['点赞数'])
                ws.cell(row=now_row, column=3, value=datas['转发数'])
                ws.cell(row=now_row, column=4, value=datas['评论数'])
                ws.cell(row=now_row, column=5, value=datas['内容'])
                ws.cell(row=now_row, column=6, value=datas['用户名'])
                ws.cell(row=now_row, column=7, value=datas['用户主页链接'])
                ws.cell(row=now_row, column=8, value=datas['链接'])
                ws.cell(row=now_row, column=9, value=datas['对应平台'])
                now_row += 1 # 新增一行就增加下一行
            for i in range(1, 10):
                for j in range(base_row, now_row):
                    ws.cell(row=j, column=i).font = self.font # 指定字体
                    ws.cell(row=j, column=i).alignment = self.alignment # 指定居中
                    data.append(ws.cell(row=j, column=i)) # 操作的单元格, i为1到10列， j为具体的行数
                
        else:
            print('没有爬取到对应的话题链接')
            

        self.add_border(data)
        
        wb.save('三国志-{}新.xlsx'.format(str(datetime.now())[:13]))
        
        
    
if __name__ == '__main__':
    msg = OutputMsg()
    msg.white_excel()

实战三

将数据库的两个表合并拼成一张表

def to_xsls(xlsxname, tx_analyze_id):
    if os.path.exists(f'{xlsxname}.xlsx'):
        os.remove(f'{xlsxname}.xlsx')
    wb = Workbook()
    xls = wb.create_sheet("评论内容", 0)
    tz_table_items = ["点赞数", "转发数", "评论数", "内容", "用户名", "用户主页链接", "微博链接", '评论时间', '评论内容', '评论人的昵称',
                      '评论用户主页链接', '所属楼层']
    for i, chaohua_table_item in enumerate(tz_table_items):
        xls.cell(1, i + 1).value = chaohua_table_item

    sql = f''
    have_data = db2.get_all(sql)

    if have_data:
        for i, hd in enumerate(have_data):
            # publish_time = hd['publish_time'].strftime('%Y-%m-%d %H:%M:%S')
            attitudes_count = hd['like_num']  # 点赞数
            reposts_count = hd['repost_num']  # 转发数
            comments_count = hd['comment']  # 评论数
            content = hd['content']  # 内容
            user_name = hd['user_name']  # 用户名
            user_link = hd['user_link']  # 用户主页链接
            weibo_link = hd['weibo_url']  # 微博链接
            created_at = hd['created_at']  # 评论时间
            bcontent = hd['bcontent']  # 评论内容
            buser_name = hd['buser_name']  # 评论人的昵称
            comment_link = hd['comment_link']  # 评论用户主页链接
            floor = hd['floor']  # 评论用户主页链接
            xls.cell(i + 2, 1).value = ''
            xls.cell(i + 2, 1).value = attitudes_count
            xls.cell(i + 2, 2).value = reposts_count
            xls.cell(i + 2, 3).value = comments_count
            xls.cell(i + 2, 4).value = content
            xls.cell(i + 2, 5).value = user_name
            xls.cell(i + 2, 6).value = user_link
            xls.cell(i + 2, 7).value = weibo_link
            xls.cell(i + 2, 8).value = created_at
            xls.cell(i + 2, 9).value = bcontent
            xls.cell(i + 2, 10).value = buser_name
            xls.cell(i + 2, 11).value = comment_link
            xls.cell(i + 2, 12).value = floor

    ws1 = wb.create_sheet("转发内容", 1) # 表示创建第二张表
    sql1 = """SELECT
                CONCAT( "https://weibo.com/7071395667/", wb_id ) "微博连接",
                CONCAT( "https://weibo.com/u/", user_id ) "用户主页连接",
                created_at "发布时间",
                user_name "用户名字",
                content "评论内容"
            FROM
                `wb_transpond_2`
            WHERE
                wb_id = ''
            ORDER BY
                created_at DESC"""
    have_data2 = db.query(sql1)
    data = list()  # 画边框时候的使用
    ws1.cell(row=1, column=1).value = '微博连接'
    ws1.cell(row=1, column=2).value = '用户主页连接'
    ws1.cell(row=1, column=3).value = '发布时间'
    ws1.cell(row=1, column=4).value = '用户名字'
    ws1.cell(row=1, column=5).value = '评论内容'
    now_row = 2  # 从第二行开始填充数据，随着数据的增加而变化
    base_row = 2  # 就是从一开始的表格开始，不会变化
    if have_data2:
        for datas in have_data2:
            ws1.cell(row=now_row, column=1, value=datas['连接'])
            ws1.cell(row=now_row, column=2, value=datas['主页连接'])
            ws1.cell(row=now_row, column=3, value=datas['发布时间']).number_format = 'yyyy/mm/dd hh:mm:ss'  # 指定单元格为时间格式
            ws1.cell(row=now_row, column=4, value=datas['用户名字'])
            ws1.cell(row=now_row, column=5, value=datas['评论内容'])
            now_row += 1  # 新增一行就增加下一行

    wb.save(f'{xlsxname}.xlsx')


to_xsls('评论', 888)

实战4 为表格加上边框 实现 居中，换行

def check_sql_data(y_timestamp, t_timestamp):
    """拿到今天的评论数据"""
    side = Side(border_style='thin', color=colors.BLACK)  # 边框的宽度和颜色
    alignment = Alignment(horizontal='center', vertical='center', wrapText=True)  # 文本内容， 边框里面自动换行
    title_font = Font(name='微软雅黑', size=11, italic=False, color=colors.BLACK, bold=False)  # 标题字体
    first_sql = f"""
        SELECT
            updated_time "更新时间",
            send_time "创建时间",
            user_name "用户名字",
            CONCAT("https://www.taptap.com/user/", user_id) "用户主页",
            score "评分",
            device	"机型",
            played_tips "游戏时间",
            CONCAT('https://www.taptap.com/review/', article_id )'帖子链接',
            contents "评论内容",
            funnies "欢乐值",
            ups "点赞数",
            downs "踩"
        FROM
            `scrapy_taptap`
            where updated_time between {y_timestamp} and {t_timestamp}
            ORDER BY updated_time DESC
    """
    result = db.query(first_sql)
    if os.path.exists(f'{t_timestamp}.xlsx'):
        os.remove(f'{t_timestamp}.xlsx')
    wb = Workbook()
    xls = wb.create_sheet("taptap_评论区内容", 0)
    xls.column_dimensions['A'].width = 20  # 指定表格长度
    xls.column_dimensions['B'].width = 20
    xls.column_dimensions['C'].width = 10
    xls.column_dimensions['D'].width = 25
    xls.column_dimensions['E'].width = 5
    xls.column_dimensions['F'].width = 20
    xls.column_dimensions['G'].width = 25
    xls.column_dimensions['H'].width = 25
    xls.column_dimensions['I'].width = 50
    xls.column_dimensions['J'].width = 8
    xls.column_dimensions['K'].width = 8
    xls.column_dimensions['L'].width = 8
    table_items = ["更新时间", "创建时间", "用户名字", "用户主页", "评分", "机型", "游戏时间", '帖子链接', '评论内容', '欢乐值', '点赞数', '踩']
    for i, table_item in enumerate(table_items):
        xls.cell(1, i + 1).value = table_item
        xls.cell(1, i + 1).border = Border(top=side, bottom=side, left=side, right=side)
        xls.cell(1, i + 1).alignment = alignment
        xls.cell(1, i + 1).font = title_font

    if result:
        now_row = 2
        base_row = 2  # 就是从一开始的表格开始，不会变化
        for data in result:
            xls.cell(row=now_row, column=1, value=timestamp2str(data['更新时间'])).number_format = 'yyyy/mm/dd hh:mm:ss'  # 指定单元格为时间格式
            xls.cell(row=now_row, column=2, value=timestamp2str(data['创建时间'])).number_format = 'yyyy/mm/dd hh:mm:ss'  # 指定单元格为时间格式
            xls.cell(row=now_row, column=3, value=data['用户名字'])
            xls.cell(row=now_row, column=4, value=data['用户主页'])
            xls.cell(row=now_row, column=5, value=data['评分'])
            xls.cell(row=now_row, column=6, value=data['机型'])
            xls.cell(row=now_row, column=7, value=data['游戏时间'])
            xls.cell(row=now_row, column=8, value=data['帖子链接'])
            xls.cell(row=now_row, column=9, value=data['评论内容'])
            xls.cell(row=now_row, column=10, value=data['欢乐值'])
            xls.cell(row=now_row, column=11, value=data['点赞数'])
            xls.cell(row=now_row, column=12, value=data['踩'])
            now_row += 1  # 新增一行就增加下一行

        for i in range(1, 13):
            for j in range(base_row, now_row):
                xls.cell(row=j, column=i).border = Border(top=side, bottom=side, left=side, right=side)  # 操作的单元格, i为1到10列， j为具体的行数
                xls.cell(row=j, column=i).alignment = alignment

    wb.save(f'{t_timestamp}.xlsx')


t_timestamp = int(time.time()) - int(time.time() - time.timezone) % 86400 + 86400  # 今天
y_timestamp = t_timestamp - 15 * 86400  # 昨天


if __name__ == "__main__":
    print(t_timestamp, y_timestamp)
    check_sql_data(y_timestamp=y_timestamp, t_timestamp=t_timestamp)

这四个实战基本都包含了目前我工作中的需求，可以根据具体需求在类上做对应的修改。

参考的官方文档为：https://openpyxl.readthedocs.io/en/stable/usage.html

完。