应用知识点
使用python创建、保存excel
设置表单、插入文字等
设置文字颜色,合并单元格等
excel数据读取、转换操作
python的with用法
mysql数据库
实现功能
插入图表并实现报表
with用法
def open_file():
""" 使用with语法打开一个文件 """
try:
f = open('./static/test.txt', 'r', encoding='utf-8')
rest = f.read()
print(rest)
except:
pass
finally:
f.close()
# with open('./static/test.txt', 'r', encoding='utf-8') as f:
# rest = f.read()
# print(rest)
if __name__ == '__main__':
open_file()
操作Excel
from datetime import datetime
import MySQLdb
from openpyxl import Workbook, load_workbook
from openpyxl.drawing.image import Image
from openpyxl.styles import Font, colors
class ExcelUtils(object):
"""
pip install openpyxl
pip install pillow
"""
def __init__(self):
self.wb = Workbook()
self.ws = self.wb.active
self.ws_two = self.wb.create_sheet('我的表单')
self.ws.title = '你的表单'
self.ws.sheet_properties.tabColor = 'ff0000'
self.ws_three = self.wb.create_sheet()
def do_sth(self):
# 插入数据
self.ws['A1'] = 66
self.ws['A2'] = '你好'
self.ws['A3'] = datetime.now()
for row in self.ws_two['A1:E5']:
for cell in row:
cell.value = 2
# 对数据进行求和
self.ws_two['G1'] = '=SUM(A1:E1)'
# 设置文字
font = Font(sz=18, color=colors.RED)
self.ws['A2'].font = font
# 插入图片
# img = Image('./static/temp.jpg')
# self.ws.add_image(img, 'B1')
self.ws.merge_cells('A4:E5')
self.ws.unmerge_cells('A4:E5')
self.wb.save('./static/test.xlsx')
def read_xls(self):
"""
读取excel数据
:return:
"""
ws = load_workbook('./static/template.xlsx')
names = ws.get_sheet_names()
print(names)
conn = self.get_conn()
wb = ws.active
wb = ws[names[0]]
for (i, row) in enumerate(wb.rows):
if i < 2:
continue
year = wb['A{0}'.format(i + 1)].value
max = wb['B{0}'.format(i + 1)].value
avg = wb['C{0}'.format(i + 1)].value
print(year)
if year is None:
continue
cursor = conn.cursor()
sql = 'INSERT INTO `score`(`year`, `max`, `avg`) VALUES({year}, {max}, {avg})'.format(
year=year, max=max, avg=avg)
print(sql)
cursor.execute(sql)
conn.autocommit(True)
# print(conn)
def get_conn(self):
""" 获取mysql 的连接 """
try:
conn = MySQLdb.connect(
db='user_grade',
host='localhost',
user='root',
password='',
charset='utf8'
)
except:
pass
return conn
def export_xls(self):
""" 从mysql数据库导出数据到excel """
# 获取数据库的连接
conn = self.get_conn()
cursor = conn.cursor()
# 准备查询语句 (如果数据量大,需要借助于分页查询)
sql = 'SELECT `year`, `max`, `avg` FROM `score`'
# 查询数据
cursor.execute(sql)
rows = cursor.fetchall()
# 循环写入到excel
wb = Workbook()
ws = wb.active
for (i, row) in enumerate(rows):
print(row)
(ws['A{0}'.format(i+1)],
ws['B{0}'.format(i+1)],
ws['C{0}'.format(i+1)]) = row
# 保存excel
wb.save('./static/export.xlsx')
if __name__ == '__main__':
client = ExcelUtils()
# client.do_sth()
client.read_xls()
# client.export_xls()
py+mysql图表实战
import MySQLdb
from openpyxl import load_workbook
from openpyxl.chart import AreaChart, Reference
class GaokaoExport(object):
def __init__(self):
self.wb = load_workbook('./static/tmpl.xlsx')
self.ws = self.wb.active
self.ws.title = '成绩统计'
self.ws.sheet_properties.tabColor = 'ff0000'
def get_conn(self):
""" 获取mysql 的连接 """
try:
conn = MySQLdb.connect(
db='user_grade',
host='localhost',
user='root',
password='',
charset='utf8'
)
except:
pass
return conn
def export_data(self):
# 获取数据库的连接
conn = self.get_conn()
cursor = conn.cursor()
# 准备查询语句 (如果数据量大,需要借助于分页查询)
sql = 'SELECT `year`, `max`, `avg` FROM `score`'
# 查询数据
cursor.execute(sql)
rows = cursor.fetchall()
# 循环写入到excel
row_id = 10
for (i, row) in enumerate(rows):
print(row)
(self.ws['C{0}'.format(row_id)],
self.ws['D{0}'.format(row_id)],
self.ws['E{0}'.format(row_id)]) = row
row_id += 1
# 显示图表
chart = AreaChart()
chart.title = "统计表"
chart.style = 13
chart.x_axis.title = '年份'
chart.y_axis.title = '分数'
# 横坐标
cats = Reference(self.ws, min_col=3, min_row=10, max_row=row_id)
# 数据区域
data = Reference(self.ws, min_col=4, min_row=9, max_col=5, max_row=row_id)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
self.ws.add_chart(chart, "A{0}".format(row_id+2))
# 保存excel
self.wb.save('./static/stats.xlsx')
if __name__ == '__main__':
client = GaokaoExport()
client.export_data()