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

喜欢ヅ旅行 2023-01-13 06:06 292阅读 0赞

### 文章目录 ###

*  一.xlsxwriter模块
 *   *  1.1 xlsxwriter模块介绍
     *  1.2 xlsxwriter 实例
     *   *  1.2.1 创建一个简单的xlsx文件
         *  1.2.2 将指定格式的数据录入xlsx文件
         *  1.2.3 写入不同的类型的数据到xlsx
         *  1.2.4 将数据库查询出来的数据录入xlsx文件
 *  二.openpyxl模块读取excel文件
 *  参考:

# 一.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][image.png 1]

### 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][image.png 2]

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

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

*  [`write_string()`][write_string]
 *  [`write_number()`][write_number]
 *  [`write_blank()`][write_blank]
 *  [`write_formula()`][write_formula]
 *  [`write_datetime()`][write_datetime]
 *  [`write_boolean()`][write_boolean]
 *  [`write_url()`][write_url]

接上一个章节，增加一个日期类型的列写入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

[image.png]: /images/20221022/df041a6b66cf4e319da3a154aa6db37e.png
[image.png 1]: /images/20221022/ce1a0c412476435b85de89e1b08b7699.png
[image.png 2]: /images/20221022/83d005d9fad74d54b95f8eb1cc432a27.png
[write_string]: https://xlsxwriter.readthedocs.io/worksheet.html#write_string
[write_number]: https://xlsxwriter.readthedocs.io/worksheet.html#write_number
[write_blank]: https://xlsxwriter.readthedocs.io/worksheet.html#write_blank
[write_formula]: https://xlsxwriter.readthedocs.io/worksheet.html#write_formula
[write_datetime]: https://xlsxwriter.readthedocs.io/worksheet.html#write_datetime
[write_boolean]: https://xlsxwriter.readthedocs.io/worksheet.html#write_boolean
[write_url]: https://xlsxwriter.readthedocs.io/worksheet.html#write_url