开头
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
完。
