暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Python常用模块12-python的xlsxwriter模块(操作excel)

Python常用模块12-python的xlsxwriter模块(操作excel)

原创 只是甲 2021-04-16
1968

Table of Contents

一.xlsxwriter模块

1.1 xlsxwriter模块介绍

在实际生产中，经常会用到excel来处理数据，虽然excel有强大的公式，但是很多工作也只能半自动化，配合python使用可以自动化部分日常工作，大大提升工作效率。

openpyxl：只允许读取和写入.xlsx格式文件并进行增删改增查。
xlwings：允许读取和写入.xlsx和.xls两种格式文件并进行增删改查。
xlsxwriter：只允许写入.xlsx格式的文件。

比较三者，你可能会觉得xlsxwriter这个库也太不行了吧？其实不是的，在写入这方面前两个库比不上它，它的精华在于写入(多张样式图表、图片、表格样式修改等)。话不多说，下面开始讲解！

xlsxwriter是用于创建Excel XLSX文件的Python模块，可用于将文本、数字、公式和超链接写入Excel2007 + XLSX文件中的多个工作表。它支持格式化等功能。

优势:
它比其他模块支持更多的Excel特性。
它对Excel生成的文件具有很高的保真度。在大多数情况下，生成的文件100%等同于Excel生成的文件。
它有大量的文档、示例文件和测试。
它是快速的，可以配置为使用非常少的内存，甚至非常大的输出文件。

安装xlsxwriter

pip install XlsxWriter

一个简单的测试
代码:

import xlsxwriter

workbook = xlsxwriter.Workbook('hello.xlsx')
worksheet = workbook.add_worksheet()

worksheet.write('A1', 'Hello world')

workbook.close()

当前目录下生成一个hello.xlsx的目录
image.png

1.2 xlsxwriter 实例

1.2.1 创建一个简单的xlsx文件

使用一个元祖嵌套列表，将数据录入xlsx文件并使用简单的公式

import xlsxwriter

# 创建一个workbook,新增一个worksheet
workbook = xlsxwriter.Workbook('Expenses01.xlsx')
worksheet = workbook.add_worksheet()

# 以下的元组嵌套列表数据需要写入上面创建的worksheet
expenses = (
    ['Rent', 1000],
    ['Gas', 100],
    ['Food', 300],
    ['Gym', 50],
)

# 定义起始行和列为0
row = 0
col = 0

# 遍历数据并逐行写入xlsx文件
for item, cost in (expenses):
    worksheet.write(row,col, item)
    worksheet.write(row, col + 1, cost)
    row += 1

# 写一个公式汇总数据
worksheet.write(row, 0, 'Total')
worksheet.write(row, 1, '=sum(B1:B4)')

# 关闭workbook
workbook.close()

测试记录:
image.png

1.2.2 将指定格式的数据录入xlsx文件

上一节我们测试了一个简单的xlsx写入，现在对上一节的例子进行一个简单的优化。

  1. 增加列头
  2. 将金额加上$ 符号

代码:

import xlsxwriter

# 创建一个workbook,新增一个worksheet
workbook = xlsxwriter.Workbook('Expenses02.xlsx')
worksheet = workbook.add_worksheet()

# 新增一个粗体格式
bold = workbook.add_format({'bold': True})

# 新增一个数值格式代表金额
money = workbook.add_format({'num_format': '$#,##0'})

# 写入表头
worksheet.write('A1', 'Item', bold)
worksheet.write('B1', 'Cost', bold)

# 以下的元组嵌套列表数据需要写入上面创建的worksheet
expenses = (
    ['Rent', 1000],
    ['Gas', 100],
    ['Food', 300],
    ['Gym', 50],
)

# 定义起始行和列为0
row = 1
col = 0

# 遍历数据并逐行写入xlsx文件
for item, cost in (expenses):
    worksheet.write(row,col, item)
    worksheet.write(row, col + 1, cost, money)
    row += 1

# 写一个公式汇总数据
worksheet.write(row, 0, 'Total' ,bold)
worksheet.write(row, 1, '=sum(B2:B5)' ,money)

# 关闭workbook
workbook.close()

测试记录:
image.png

1.2.3 写入不同的类型的数据到xlsx

worksheet的write其实是根据不同类型的的别名

接上一个章节，增加一个日期类型的列写入xlsx文件

代码:

from datetime import datetime
import xlsxwriter

# 创建一个workbook和worksheet
workbook = xlsxwriter.Workbook('Expenses03.xlsx')
worksheet = workbook.add_worksheet()

# 新增一个粗体格式
bold = workbook.add_format({'bold': True})

# 新增一个数值格式代表金额
money_fromat = workbook.add_format({'num_format': '$#,##0'})

# 增加一个时间类型的格式
date_format = workbook.add_format({'num_format': 'yyyy-mm-dd'})

# 调整列的宽度
worksheet.set_column(1, 1, 15)

# 写表头
worksheet.write('A1', 'Item', bold)
worksheet.write('B1', 'Date', bold)
worksheet.write('C1', 'Cost', bold)

# 需要写入的数据
expenses = (
     ['Rent', '2013-01-13', 1000],
     ['Gas',  '2013-01-14',  100],
     ['Food', '2013-01-16',  300],
     ['Gym',  '2013-01-20',   50],
 )

# 定义起始行和列为0
row = 1
col = 0

for item, date_str, cost in (expenses):
    # 调整时间类型的格式
    date = datetime.strptime(date_str, "%Y-%m-%d")

    worksheet.write_string   (row, col, item)
    worksheet.write_datetime (row, col + 1,date,date_format)
    worksheet.write_number   (row, col + 2, cost, money_fromat)
    row += 1

# 写公式
worksheet.write(row, 0 , 'Total', bold)
worksheet.write(row, 2 , '=sum(C2:C5)', money_fromat)

workbook.close()

测试记录:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Nz70f6Fl-1618552893257)(https://upload-images.jianshu.io/upload_images/2638478-fff3a90932471208.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)]

1.2.4 将数据库查询出来的数据录入xlsx文件

代码:

import pymysql
from datetime import datetime
import xlsxwriter

# 创建mysql连接
conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='abc123',db='zqs')
cursor = conn.cursor()

sql1 = "select empno,ename , mgr,hiredate from emp where 1 = 1"
headers = ["empno","ename","mgr","hiredate"]

cursor.execute(sql1)

rows = cursor.fetchall()
fields = cursor.description # 获取列名

# 创建一个workbook和worksheet
workbook = xlsxwriter.Workbook('emp01.xlsx')
worksheet = workbook.add_worksheet()

# 新增一个粗体格式
bold = workbook.add_format({'bold': True})

# 写表头
worksheet.write('A1', 'empno', bold)
worksheet.write('B1', 'ename', bold)
worksheet.write('C1', 'mgr', bold)
worksheet.write('D1', 'hiredate', bold)

#数据坐标0,0 ~ row,col   row取决于：result的行数；col取决于fields的总数
for row in range(1,len(rows)+1):
    for col in range(0,len(fields)):
        worksheet.write(row,col,u'%s' % rows[row-1][col])
workbook.close()

# 关闭连接
cursor.close()
conn.close()

测试记录:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FQNBfvRk-1618552893259)(https://upload-images.jianshu.io/upload_images/2638478-dd960236b22c930c.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)]

二.openpyxl模块读取excel文件

代码：

import openpyxl

wb = openpyxl.load_workbook('emp01.xlsx')

# 获取sheet名
sheets = (wb.sheetnames)

# 获取行数和列数
sheet=wb['Sheet1']
max_row = sheet.max_row
max_column = sheet.max_column


# 遍历excel数据
datas = []
# 从第二行开始,第一行是表头
for i in range(2,max_row+1):
    for j in range(1, max_column+1):
        data = sheet.cell(i,j).value
        datas.append(data)

print(datas)

测试记录:

E:\python\learn_python1\venv\Scripts\python.exe E:/python/learn_python1/excel/openpyxl_test1.py
['7369', 'SMITH', '7902', '1980-12-17', '7499', 'ALLEN', '7698', '1981-02-20', '7521', 'WARD', '7698', '1981-02-22', '7566', 'JONES', '7839', '1981-04-02', '7654', 'MARTIN', '7698', '1981-09-28', '7698', 'BLAKE', '7839', '1981-05-01', '7782', 'CLARK', '7839', '1981-06-09', '7788', 'SCOTT', '7566', '1987-06-13', '7839', 'KING', 'None', '1981-11-17', '7844', 'TURNER', '7698', '1981-09-08', '7876', 'ADAMS', '7788', '1987-06-13', '7900', 'JAMES', '7698', '1981-12-03', '7902', 'FORD', '7566', '1981-12-03', '7934', 'MILLER', '7782', '1982-01-23']

Process finished with exit code 0

参考:

1.https://xlsxwriter.readthedocs.io/index.html
2.https://www.jianshu.com/p/6c979f0c6516

python
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

Python（共38篇）
DBA自学python之路

评论