Python办公自动化入门-->Excel操作

第一部分  环境部署

环境说明：win7以上 、 python3.6.8

安装软件：

D:\Users\Administrator\PycharmProjects\办公自动化>pip uninstall docx

D:\Users\Administrator\PycharmProjects\办公自动化>pip install python-docx

D:\Users\Administrator\PycharmProjects\办公自动化>pip install xlrd

D:\Users\Administrator\PycharmProjects\办公自动化>pip install xlwt

D:\Users\Administrator\PycharmProjects\办公自动化>pip freeze
lxml==4.9.2
python-docx==0.8.11
xlrd==2.0.1

第二部分  Python xlrd读取Excel xls表格

xlrd是Python第三方模块，只能用来读取（*.xls）格式Excel表格数据，这个也是一种缺陷把。

安装模块pip install xlrd 

办公excel表格组件说明，如图，

xlrd模块使用

常用函数：操作excel列，操作excel行，操作excel单元格

现在我们来准备工作表素材，

 如上，excel工作表操作。

工作表操作：打开工作表，获取工作表数量，获取第几个工作表索引和表名。

1、 操作工作表

#!coding:utf-8
import xlrd
data = xlrd.open_workbook("January_Sales_Table.xls")
# print(data.sheet_loaded(0))   # 打印加载工作表
#data.unload_sheet(0)  # 注释工作表
# print(data.sheets())   # 获取所有工作表名
# print(data.sheets()[0])   # 获取第一个工作表名
# print(data.sheet_by_index(0))   # 根据索引获取工作表
# print(data.sheet_by_name("test1"))   # 根据表明获取工作表，表明严格区分大小写
print(data.sheet_names())   # 获取所有工作表名
print(data.nsheets)  # 返回excel工作表的数量

2、操作行

data = xlrd.open_workbook("January_Sales_Table.xls")
sheet = data.sheet_by_index(0)  # 获取第一个工作表
print(sheet.nrows)  # 获取当前sheet当前有效行数
print(sheet.row(0))  # 获取第一行数据，含类型
print(sheet.row_types(0))  # 获取第一行数据类型，输出1为文本，2为number，3data，4为布尔，5为error
print(sheet.row(0)[0])    # 获取单元格信息
print(sheet.row(0)[0].value)  # 获取单元格内容
print(sheet.row_values(0))  # 获取第一行单元格值
print(sheet.row_len(0))  # 获取单行单元格长度

3、操作列

data = xlrd.open_workbook("January_Sales_Table.xls")
sheet = data.sheet_by_index(0)  # 获取第一个工作表
print(sheet.ncols)  # 查看总列数
print(sheet.col(0)) # 查看对应列组成的列表
print(sheet.col(0)[0].value)  # 查看第一列第一行单元格数据
print(sheet.col_values(0))  # 查看该列所有单元格组成的列表
print(sheet.col_types(0))

4、操作单元格

data = xlrd.open_workbook("January_Sales_Table.xls")
sheet =data.sheet_by_index(0)  # 获取第一个工作表
print(sheet.cell(0,0))   # 获取单元格数据
print(sheet.cell_type(0,0))    # 获取单元格数据类型
print(sheet.cell(0,0).ctype)   # 获取单元格数据类型
print(sheet.cell(0,0).value)   # 获取单元格数据值
print(sheet.cell_value(0,0))   # 获取单元格数据值

第三部分  Python xlwt操作Excel xls表格

Xlwt模块，用来写入excel表格数据，pip install xlwt

创建工作簿，创建工作表，填充工作表内容，保存文件

 四个大步骤如下，

import xlwt
# 第一步：创建工作簿
wb = xlwt.Workbook()
# 第二步：创建工作表
ws = wb.add_sheet("test第一个工作表")   #工作表名称
# 第三步：填充数据
ws.write_merge(0,1,0,5,"2023年账单信息")  #合并第1-2行，1-6列，单元格内容
# 第四步：保存文件
wb.save("2023-07-test.xls")    #工作簿文件名

执行结果如下，

  

 不妨继续补充数据，如上截图展示。

import xlwt
# 第一步：创建工作簿
wb = xlwt.Workbook()
# 第二步：创建工作表
ws = wb.add_sheet("test第一个工作表")   #工作表名称
# 第三步：填充数据
ws.write_merge(0,1,0,5,"2023年账单信息")  #合并第1-2行，1-6列，单元格内容

# 写入货币数据
data=(("日期", "个数", "大小", "比例", "合格率", "合格数"),
      ("2021-3-20", 5.6, 4, 0.42, 0.23, 6.4),
      ("2021-3-20", 3.2, 2, 0.92, 0.34, 8.3),
      ("2021-3-20", 4.1, 2, 0.22, 0.13, 6.0))
for i, item in enumerate(data): # 函数enumerate包含索引
    for j, val in enumerate(item):
        ws.write(i+2, j, val)  # 跳过前两行
# 第四步：保存文件
wb.save("2023-07-test.xls")    #工作簿文件名

学习下xlwt样式，

titlestyle = xlwt.XFStyle()  # 初始化样式
titlefont = xlwt.Font()
titlefont.name = "宋体"
titlefont.bold = True  #加粗
titlefont.height = 11*20  #字号
titlefont.colour_index = 0x08 #字体颜色
titlestyle.font = titlefont

# 单元格对齐方式
cellalign = xlwt.Alignment()
cellalign.horz = 0x02  # 垂直方向
cellalign.vert = 0x01  # 水平方向
titlestyle.alignment = cellalign

# 设置边框
borders = xlwt.Borders()
borders.right = xlwt.Borders.DASHED
borders.bottom = xlwt.Borders.DOTTED
titlestyle.borders = borders

# 背景颜色
datestyle = xlwt.XFStyle()
bgcolor = xlwt.Pattern()
bgcolor.pattern = xlwt.Pattern.SOLID_PATTERN
bgcolor.pattern_fore_colour = 22
datestyle.pattern = bgcolor

项目实战：基于xlrd模块实现考试系统题库管理

通过以上学习，我们可以实战学习下

 目的：将excel表格数据导入数据库中。

Excel导入试题到数据库操作步骤：通过xlrd模块读取Excel数据，通过pymysql模块连接数据库，组装数据、执行插入操作，关闭数据库连接

原Excel表格信息如下，

 

代码展示如下，数据库查看。pip install pymysql

import  xlrd

data = xlrd.open_workbook("data2.xls")
sheet = data.sheet_by_index(0)  # 获取工作表
questionList=[]  # 构建试题列表

# 定义试题类
class Question():
    pass

for i in range(sheet.nrows):
    if i>1:
        obj=Question()   # 构建试题对象,注意其中的括号不能掉
        obj.subject=sheet.cell(i,1).value  # 题目
        obj.questionType = sheet.cell(i,2).value  # 题型
        obj.optionA = sheet.cell(i, 3).value  # 选项A
        obj.optionB = sheet.cell(i, 4).value  # 选项B
        obj.optionC = sheet.cell(i, 5).value  # 选项C
        obj.optionD = sheet.cell(i, 6).value  # 选项D
        obj.score = sheet.cell(i, 7).value  # 分值
        obj.answer = sheet.cell(i, 8).value  # 正确答案
        questionList.append(obj)
# print(questionList)
# for i in questionList:
#     print(i.subject)

# 将试题导入MySQL中
# pymysql pip install
from mysqlhelper import *
# 1、 连接搭配数据库
db=dbhelper('test.mysql.one',3306,"root","xxx","bgzdh")
# 2、 插入语句
sql = "insert into xlrd(subject,questionType,optionA,optionB,optionC,optionD,score,answer) values (%s,%s,%s,%s,%s,%s,%s,%s)"
val=[]  # 空列表用来存储元组
for item in questionList:
    val.append((item.subject,item.questionType,item.optionA,item.optionB,item.optionC,item.optionD,item.score,item.answer))
print(val)
db.excutemanydata(sql, val)

import pymysql

class dbhelper():
    def __init__(self, host, port, user, passwd, db, charset="utf8"):
        self.host=host
        self.port = port
        self.user = user
        self.passwd = passwd
        self.db = db
        self.charset = charset
    # 创建一个连接
    def connection(self):
        # 1、创建连接
        self.conn = pymysql.connect(host=self.host,
                                    port=self.port,
                                    user=self.user,
                                    passwd=self.passwd,
                                    db=self.db,
                                    charset=self.charset)
        # 2、创建游标
        self.cur = self.conn.cursor()
    # 关闭连接
    def closeconnection(self):
        self.cur.close()
        self.conn.close()
    # 查询一条数据
    def getonedata(self, sql):
        try:
            self.connection()
            self.cur.execute(sql)
            result = self.cur.fetchone()
            self.closeconnection()
        except Exception:
            print(Exception)
        return result
    # 查询多条数据
    def getalldata(self, sql):
        try:
            self.connection()
            self.cur.execute(sql)
            result = self.cur.fetchall()
            self.closeconnection()
        except Exception:
            print(Exception)
        return result
    # 添加、修改、删除
    def excutedata(self, sql):
        try:
            self.connection()
            self.cur.execute(sql)
            self.conn.commit()
            self.closeconnection()
        except Exception:
            print(Exception)
    # 批量插入
    def excutemanydata(self, sql, vals):
        try:
            self.connection()
            self.cur.executemany(sql, vals)
            self.conn.commit()
            self.closeconnection()
        except Exception:
            print(Exception)

这里需要准备数据库

创建数据库表字段
mysql> create database bgzdh default charset utf8mb4 collate utf8mb4_unicode_ci;  
mysql>      
CREATE TABLE `xlrd` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subject` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `questionType` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `optionA` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `optionB` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `optionC` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `optionD` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `answer` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

完成了该项目实战。

第四章 xlsxwriter生成图表

Xlsxwriter模块

xlsxwriter，用于生成的Excel表格插入数据、图表等操作

安装：pip install xlsxwriter

导入：imoprt xlsxwriter

 

优势：

xlsxwriter较其他模块支持更多的Excel功能

100%兼容Excel xlsx文件，支持Excel 2003， Excel 2007等版本

xlsxwriter处理速度更快，支持大文件写入

写文本到excel

import xlsxwriter

wb = xlsxwriter.Workbook("testdata.xlsx")

# 创建sheet
sheet = wb.add_worksheet("newsheet")
# 写入
sheet.write(0, 0, "2020年度")  #写入单元格数据
sheet.merge_range(1, 0, 2, 2, "第一季度销售统计")
data = (
    ["一月份", 500, 450],
    ["一月份", 500, 450],
    ["一月份", 500, 450]
)
sheet.write_row(3, 0, ["月份", "预期销售额", "实际的销售额"])

for index, item in enumerate(data):
    sheet.write_row(index+4, 0, item)

# 写入excel公式
sheet.write(7, 1, "=sum(B5:B7)")
sheet.write(7, 2, "=sum(C5:C7)")
sheet.write_url(9,0,"http://www.baidu.com", string="更多数据")
sheet.insert_image(10, 0, "test3.bmp")  # 插入图片
wb.close()

对写入内容进行可视化配置

cell_format = wb.add_format({"bold": True}) #格式对象
cell_format1 = wb.add_format() #格式对象
cell_format1.set_bold()
cell_format1.set_fg_color("red")
cell_format1.set_font_size(14)
cell_format1.set_align("center")
cell_format2 = wb.add_format()
cell_format2.set_bg_color("#808080") # 设置背景颜色

写入图表到Excel

实现：写入文本、图片、超链接等；写入格式化配置；写入图表。

# 写入
# chart = wb.add_chart({'type': 'column'})  # 直方图
chart = wb.add_chart({'type': 'line'})# 直线图
chart.set_title({'name': '第一季度销售统计'})
# X Y 描述信息
chart.set_x_axis({'name': '月份'})
chart.set_y_axis({'name': '销售额'})
# 数据
chart.add_series({
    'name': '实际销售额',
    'categories': '=newsheet!$A$5:$A$7',
    'values': ['newsheet', 4, 1, 6, 1],
    'data_labels':{'value': True}
})
chart.add_series({
    'name': '预期销售额',
    'categories': '=newsheet!$A$5:$A$7',
    'values': ['newsheet', 4, 2, 6, 2]
})
sheet.insert_chart('A23', chart)

学习了这么多，我来一个实战项目把。

项目实战：某培训机构就业数据分析

实现步骤：

  步骤一：xlrd模块读取Excel数据
  步骤二：xlsxwriter模块生成就业数据图表
  步骤三：xmtplib模块发送附件邮件

smtplib模块对smtp进行了封装，提供了更便捷的方式发送电子邮件。如图

  

原始表格数据，见附件，这里以为单个sheet截图示例。

发送邮件的话，需要提前设置发送邮箱配置，例如163邮箱设置截图。

 开始编写脚本，

import xlrd
import xlsxwriter
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication
from email.mime.text import MIMEText

# 1、 读取
data = xlrd.open_workbook("info.xls")
classinfo = []
for sheet in data.sheets():
    dict = {'name': sheet.name, "avgsalary": 0}  # 班级信息
    sum = 0  # 存储薪资
    for i in range(sheet.nrows):
        if i>1:
            # aa=sheet.cell(i,5).value
            # print(aa,type(aa))
            sum+=float(sheet.cell(i,5).value)
    dict['avgsalary']=sum/(sheet.nrows-2)
    classinfo.append(dict)
print(classinfo)

# 2、 写入Excel
workbook = xlsxwriter.Workbook('newinfo.xlsx')
sheet=workbook.add_worksheet() # 创建工作表
# 写入班级数据
nameinfo=[]
salaryinfo=[]
for item in classinfo:
    nameinfo.append(item['name'])
    salaryinfo.append(item['avgsalary'])
sheet.write_column('A1', nameinfo)
sheet.write_column('B1', salaryinfo)
# 写入图表
chart = workbook.add_chart({'type': 'column'})
# 标题
chart.set_title({'name': '平均就业薪资'})
# 数据源
chart.add_series({
    'name': '班级',
    'categories': '=Sheet1!$A$1:$A$3',
    'values': '=Sheet1!$B$1:$B$3',
})
sheet.insert_chart('A7', chart)
workbook.close()

# 3、 发送邮件
host_server = 'smtp.163.com'  # 主机地址
sender = "186xxxx592@163.com" # 发件人邮箱
code ="xxx"     # 发件人邮箱密码、授权码
user1 = "186xxxx592@163.com"   # 收件人
# 准备邮件数据
mail_title = "！！！3月份平均就业薪资"   # 标题
mail_content = "3月份平均就业薪资,具体信息请查看附件"   # 内容
# 构建附件
attachment = MIMEApplication(open('newinfo.xlsx', 'rb').read())
attachment.add_header('Content-Disposition', 'attachment', filename='data.xlsx')

# SMTP
smtp = smtplib.SMTP(host_server)
smtp.login(sender, code)
msg = MIMEMultipart()  # 带附件的实例
msg['Subject'] = mail_title
msg['From'] = sender
msg['To'] = user1
msg.attach(MIMEText(mail_content))
msg.attach(attachment)
smtp.sendmail(sender, user1, msg.as_string())

执行脚本，演示结果如下，正常接收到邮件内容，包含附件信息。