今天学习了python操作excel表格，感觉很有意思，整理一下笔记
文章目录
python读取文件
读取文件的方式1：直接读取，最后需要close操作
def open_file2(file):
f = open(file,"r")
re = f.read()
print(re)
f.close()
def open_file3(file):
try:
f = open(file,"r")
re = f.read()
print(re)
except:
pass
finally:
f.close()
在读取文件处理异常时，发现异常：
UnboundLocalError: local variable 'f' referenced before assignment
Traceback (most recent call last):
File "/Users/apple/Downloads/11人脸识别/projects/PyDemo/excel/use_with.py", line 35, in <module>
open_file_try()
File "/Users/apple/Downloads/11人脸识别/projects/PyDemo/excel/use_with.py", line 26, in open_file_try
f.close()
UnboundLocalError: local variable 'f' referenced before assignment
在处理异常时，更改了一下文件名看是否直接显示except的内容，但是执行后发现抱了另一个错，原因是在open文件时没有找到文件，所以f相当于未定义，最后执行finally的内容的就找不到相应的f
读取文件方式2:利用with读取，最后不用进行close操作：
def open_file(file):
with open(file,"r") as f:
re = f.read()
print(re)
使用with时，返回了一个上下文管理器,执行管理器的enter方法，如果with语句设置了目标对象，则把enter方法的返回值赋给目标对象，执行with中的代码块
使用python创建excel表格
下载相关库：
pip install openpyxl
from openpyxl import Workbook
class Client(object):
def __init__(self):
#创建excel工作表
self.wb = Workbook()
#创建excel表里的不同表单
self.ws = self.wb.active
#创建表单的三种方式
self.ws.title = "表单1"
self.ws_two = self.wb.create_sheet("表单2")
self.ws_three = self.wb.create_sheet()
def do(self):
#存储excel表到相应路径
self.wb.save("./static/one.xlsx")
if __name__ == '__main__':
client = Client()
client.do()
打开生成的excel文件，可以看出下面有三个表单，其中第三个表单未命名所以显示默认名字
向excel表中手动写入数据
from openpyxl.styles import Font, colors
from openpyxl.drawing.image import Image
#手动设置内容
self.ws['A1'] = "学号"
self.ws['B1'] = "姓名"
self.ws['C1'] = "成绩"
self.ws['A2'] = "12345"
#设置字体，颜色
font = Font(sz = 18,color=colors.RED)
self.ws['A2'].font = font
#循环赋值
i = 2
for row in self.ws['A3':'C5']:
for cell in row:
cell.value = i
i +=2
#插入图片
image = Image("./static/shishi.png")
self.ws.add_image(image,'E5')
#合并/拆分单元格
self.ws.merge_cells('A8:D9')
self.ws.merge_cells('A11:D12')
self.ws.unmerge_cells('A11:D12')
#拆分单元格只能拆分之前已经合并过的
效果：
完整代码：
from openpyxl import Workbook
from openpyxl.styles import Font, colors
from openpyxl.drawing.image import Image
class Client(object):
def __init__(self):
#创建excel工作表
self.wb = Workbook()
#创建excel表里的不同表单
self.ws = self.wb.active
self.ws.title = "表单1"
self.ws_two = self.wb.create_sheet("表单2")
self.ws_three = self.wb.create_sheet()
def do(self):
#手动设置内容
self.ws['A1'] = "学号"
self.ws['B1'] = "姓名"
self.ws['C1'] = "成绩"
self.ws['A2'] = "12345"
#设置字体，颜色
font = Font(sz = 18,color=colors.RED)
self.ws['A2'].font = font
#循环赋值
i = 2
for row in self.ws['A3':'C5']:
for cell in row:
cell.value = i
i +=2
#插入图片
image = Image("./static/shishi.png")
self.ws.add_image(image,'E5')
#合并/拆分单元格
self.ws.merge_cells('A8:D9')
self.ws.merge_cells('A11:D12')
self.ws.unmerge_cells('A11:D12')
self.wb.save("./static/one.xlsx")
if __name__ == '__main__':
client = Client()
client.do()
将excel表中的数据存入数据库
表的数据：
在数据库中设定好相应参数：
python连接mysql数据库
from openpyxl import Workbook, load_workbook
import MySQLdb
#数据库连接
def get_conn(self):
conn = MySQLdb.connect(
db = "user_grade",#数据库名称
host = "localhost",#主机地址
port = 3306,#端口号
user = "root",#db登录用户名
password = "root",#db登录密码
charset = "utf8"
)
return conn
将excel中的数据写入数据库
#写数据到数据库
def read_excel_to_db(self):
#获取要读的excel表的表
wb = load_workbook("./static/two.xlsx")
name = wb.get_sheet_names()#获取所有的表单名，这里只有一个
print(name)#成绩统计
ws = wb.active
ws = wb[name[0]]
for (i, row) in enumerate(ws.rows):
if i < 1:
continue
com_org = ws['A{0}'.format(i+1)].value #数据从A2开始
os = ws['B{0}'.format(i+1)].value
micro_com = ws['C{0}'.format(i+1)].value
mobile = ws['D{0}'.format(i+1)].value
conn = self.get_conn()
cursor = conn.cursor()
sql = 'insert into `computer`(`com_org`,`os`,`micro_com`,`mobile`) VALUES ({com_org},{os},{micro_com},{mobile})'\
.format(com_org=com_org,
os = os,
micro_com = micro_com,
mobile = mobile)
cursor.execute(sql)#执行插入的sql语句
conn.autocommit(True)#提交
结果：
读取数据库中的数据将其写入excel表中
def export_from_db_to_excel(self):
#查询数据
conn = self.get_conn()
cursor = conn.cursor()
sql = 'select `com_org`,`os`,`micro_com`,`mobile` from `computer`'
cursor.execute(sql)
rows = cursor.fetchall()
#建立导出数据的excel表
wb = Workbook()
ws = wb.active
ws['A1'] = "计算机组成原理"
ws['B1'] = '操作系统'
ws['C1'] = "微机原理"
ws['D1'] = "移动设备开发应用"
#将数据写入表中
row_id = 1
for (i,row) in enumerate(rows):
ws['A{0}'.format(row_id+1)] = row[0]
ws['B{0}'.format(row_id+1)] = row[1]
ws['C{0}'.format(row_id+1)] = row[2]
ws['D{0}'.format(row_id+1)] = row[3]
row_id += 1
'''
(
ws['A{0}'.format(row_id+1)],
ws['B{0}'.format(row_id+1)],
ws['C{0}'.format(row_id+1)],
ws['D{0}'.format(row_id+1)]
) = row
'''
wb.save("./static/export_data4.xlsx")
结果：
完整代码
from openpyxl import Workbook, load_workbook
import MySQLdb
from openpyxl.styles import Font, colors
from openpyxl.drawing.image import Image
class Client(object):
def __init__(self):
#创建excel工作表
self.wb = Workbook()
#创建excel表里的不同表单
self.ws = self.wb.active
self.ws.title = "表单1"
self.ws_two = self.wb.create_sheet("表单2")
self.ws_three = self.wb.create_sheet()
#数据库连接
def get_conn(self):
conn = MySQLdb.connect(
db = "user_grade",#数据库名称
host = "localhost",#主机地址
port = 3306,#端口号
user = "root",#db登录用户名
password = "root",#db登录密码
charset = "utf8"
)
return conn
def export_from_db_to_excel(self):
#查询数据
conn = self.get_conn()
cursor = conn.cursor()
sql = 'select `com_org`,`os`,`micro_com`,`mobile` from `computer`'
cursor.execute(sql)
rows = cursor.fetchall()
#建立导出数据的excel表
wb = Workbook()
ws = wb.active
ws['A1'] = "计算机组成原理"
ws['B1'] = '操作系统'
ws['C1'] = "微机原理"
ws['D1'] = "移动设备开发应用"
#将数据写入表中
row_id = 1
for (i,row) in enumerate(rows):
ws['A{0}'.format(row_id+1)] = row[0]
ws['B{0}'.format(row_id+1)] = row[1]
ws['C{0}'.format(row_id+1)] = row[2]
ws['D{0}'.format(row_id+1)] = row[3]
row_id += 1
'''
(
ws['A{0}'.format(row_id+1)],
ws['B{0}'.format(row_id+1)],
ws['C{0}'.format(row_id+1)],
ws['D{0}'.format(row_id+1)]
) = row
'''
wb.save("./static/export_data4.xlsx")
#写数据到数据库
def read_excel_to_db(self):
#获取要读的excel表的表
wb = load_workbook("./static/two.xlsx")
name = wb.get_sheet_names()#获取所有的表单名，这里只有一个
print(name)#成绩统计
ws = wb.active
ws = wb[name[0]]
for (i, row) in enumerate(ws.rows):
if i < 1:
continue
com_org = ws['A{0}'.format(i+1)].value #数据从A2开始
os = ws['B{0}'.format(i+1)].value
micro_com = ws['C{0}'.format(i+1)].value
mobile = ws['D{0}'.format(i+1)].value
conn = self.get_conn()
cursor = conn.cursor()
sql = 'insert into `computer`(`com_org`,`os`,`micro_com`,`mobile`) VALUES ({com_org},{os},{micro_com},{mobile})'\
.format(com_org=com_org,
os = os,
micro_com = micro_com,
mobile = mobile)
cursor.execute(sql)#执行插入的sql语句
conn.autocommit(True)#提交
def do(self):
#手动设置内容
self.ws['A1'] = "学号"
self.ws['B1'] = "姓名"
self.ws['C1'] = "成绩"
self.ws['A2'] = "12345"
#设置字体，颜色
font = Font(sz = 18,color=colors.RED)
self.ws['A2'].font = font
#循环赋值
i = 2
for row in self.ws['A3':'C5']:
for cell in row:
cell.value = i
i +=2
#插入图片
image = Image("./static/shishi.png")
self.ws.add_image(image,'E5')
#合并/拆分单元格
self.ws.merge_cells('A8:D9')
self.ws.merge_cells('A11:D12')
self.ws.unmerge_cells('A11:D12')
self.wb.save("./static/one.xlsx")
if __name__ == '__main__':
client = Client()
#client.do()
#client.read_excel_to_db()
client.export_from_db_to_excel()
2901
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
