Python数据分析（四）-- 操作Excel文件

1 操作Excel文件-多种实现方式

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

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

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

1.1 xlsxwriter库储存数据到excel

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

优势：

  • 功能比较强：
    支持字体设置、前景色背景色、border设置、视图缩放（zoom）、单元格合并、autofilter、freeze panes、公式、data validation、单元格注释、行高和列宽设置
  • 支持大文件写入
  • 不支持读取、修改、XLS文件、透视表

1. 安装 xlsxwriter

pip install XlsxWriter -i https://pypi.douban.com/simple

2. 常用操作

添加工作表样式

bold = workbook.add_format({
                            'bold':  True,          # 字体加粗
                            'border': 1,            # 单元格边框宽度
                            'align': 'left',        # 水平对齐方式
                            'valign': 'vcenter',    # 垂直对齐方式
                            'fg_color': '#F4B084',  # 单元格背景颜色
                            'text_wrap': True,      # 是否自动换行
                            })

写入单元格数据

# 写入单个单元格数据
# row:行， col：列， data:要写入的数据, bold:单元格的样式
worksheet1.write(row, col, data, bold)
 
# 写入一整行，一整列
# A1:从A1单元格开始插入数据，按行插入， data:要写入的数据（格式为一个列表), bold:单元格的样式
worksheet1.write_row(“A1”, data, bold)
 
# A1:从A1单元格开始插入数据，按列插入， data:要写入的数据（格式为一个列表), bold:单元格的样式
worksheet1.write_column(“A1”,data,bold)

插入图片

# 第一个参数是插入的起始单元格，第二个参数是图片你文件的绝对路径
worksheet1.insert_image('A1','f:\\1.jpg')

写入超链接

worksheet1.write_url(row, col, "internal:%s!A1" % ("要关联的工作表表名"), string="超链接显示的名字")

插入图表

workbook.add_chartsheet（type=""）
 
# 参数中的type指的是图表类型，图表类型示例如下：
# [area：面积图,bar：条形图,column：直方图,doughnut：环状图,line：折线图,pie：饼状图,scatter：散点图,radar：雷达图,stock:箱线图]
  • 获得当前excel文件的所有工作表：workbook.worksheets()
  • 关闭excel文件： workbook.close()

3. 创建一个简单的 XLSX 文件

假设我们有一些月度支出的数据，我们希望将其转换为 Excel XLSX 文件：

import xlsxwriter as xw
 
 
def xw_toExcel(data, fileName):                 
    """xlsxwriter库储存数据到excel"""
    workbook = xw.Workbook(fileName)                # 创建工作簿
    worksheet1 = workbook.add_worksheet("sheet1")   # 创建子表
    worksheet1.activate()                           # 激活表
    title = ['序号', '项目支出', '消费金额']          # 设置表头
    worksheet1.write_row('A1', title)               # 从A1单元格开始写入表头
    row = 1                                         # 从第二行开始写入数据
    col = 0
    for record in data:                             # 迭代数据并逐行写入    
        worksheet1.write(row, col, record["id"])
        worksheet1.write(row, col+1, record["name"])
        worksheet1.write(row, col+2, record["expenses"])
        row += 1
    workbook.close()  # 关闭表
 
 
# "-------------数据用例-------------"
test_data = [
    {"id": 1, "name": "Rent", "expenses": 1000},
    {"id": 2, "name": "Gas", "expenses": 100},
    {"id": 3, "name": "Food", "expenses": 300},
    {"id": 4, "name": "Gym", "expenses": 50},
]
file_name = 'test.xlsx'
xw_toExcel(test_data, file_name)

4. 写入不同的类型的数据到xlsx

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

from datetime import  datetime
import xlsxwriter as xw
 
 
def xw_toExcel(data, fileName):                 
    """xlsxwriter库储存数据到excel"""
    workbook = xw.Workbook(fileName)                # 创建工作簿
    worksheet1 = workbook.add_worksheet("sheet1")   # 创建子表
    bold = workbook.add_format({'bold': True})      # 新增一个粗体格式
    money_format = workbook.add_format({'num_format': '$#,##0'}) # 新增一个数值格式代表金额
    date_format = workbook.add_format({'num_format': 'yyyy-mm-dd'}) # 增加一个时间类型的格式
    worksheet1.set_column(1, 1, 15)                 # 调整列的宽度
    worksheet1.activate()                           # 激活表
    title = ['日期', '项目支出', '消费金额']          # 设置表头
    worksheet1.write_row('A1', title, bold)               # 从A1单元格开始写入表头
    row = 1                                         # 从第二行开始写入数据
    col = 0
    for record in data:                             # 迭代数据并逐行写入
        date = datetime.strptime(record["date"], "%Y-%m-%d")
        worksheet1.write_datetime(row, col, date, date_format)
        worksheet1.write_string(row, col+1, record["name"])
        worksheet1.write_number(row, col+2, record["expenses"], money_format)
        row += 1
    
    # 写公式
    worksheet1.write(row, 0, 'Total', bold)
    worksheet1.write_formula(row, 2, '=SUM(C2:C5)', money_format)

    workbook.close()  # 关闭表
 
# "-------------数据用例-------------"
test_data = [
    {"date": "2023-10-24", "name": "Rent", "expenses": 1000},
    {"date": "2023-10-25", "name": "Gas", "expenses": 100},
    {"date": "2023-10-27", "name": "Food", "expenses": 300},
    {"date": "2023-10-30", "name": "Gym", "expenses": 50},
]
file_name = 'test_1.xlsx'
xw_toExcel(test_data, file_name)

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

import pymysql
from datetime import datetime
import xlsxwriter

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

sql1 = "select cou_name, cou_credit from tb_course"
cursor.execute(sql1)

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

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

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

# 写表头
worksheet.write('A1', 'course', bold)
worksheet.write('B1', 'course_credit', 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()

测试记录:

1.2 pandas库储存数据到excel

        在Python中，pandas是基于NumPy数组构建的，使数据预处理、清洗、分析工作变得更快更简单。pandas是专门为处理表格和混杂数据设计的，而NumPy更适合处理统一的数值数组数据。pandas有两个主要数据结构：Series和DataFrame。

        Series是一种类似于一维数组的对象，它由一组数据（各种NumPy数据类型）以及一组与之相关的数据标签（即索引）组成，即index和values两部分，可以通过索引的方式选取Series中的单个或一组值。

        DataFrame是一个表格型的数据类型，每列值类型可以不同，是最常用的pandas对象。DataFrame既有行索引也有列索引，它可以被看做由Series组成的字典（共用同一个索引）。DataFrame中的数据是以一个或多个二维块存放的（而不是列表、字典或别的一维数据结构）

import pandas as pd

def pd_to_excel(data, file_name):
    ids = []
    names = []
    prices = []
    for item in data:
        ids.append(item["id"])
        names.append(item["name"])
        prices.append(item["expenses"])
    df_data = {
        '序号': ids,
        '项目支出': names, 
        '消费金额': prices
    }
    df = pd.DataFrame(df_data)
    df.to_excel(file_name, index=False)

# "-------------数据用例-------------"
test_data = [
    {"id": 1, "name": "Rent", "expenses": 1000},
    {"id": 2, "name": "Gas", "expenses": 100},
    {"id": 3, "name": "Food", "expenses": 300},
    {"id": 4, "name": "Gym", "expenses": 50},
]
file_name = 'test_2.xlsx'
pd_to_excel(test_data, file_name)

1.3 openpyxl库储存数据到excel

安装：pip install openpyxl==2.2.6

打开已有文件

from openpyxl import load_workbook
wb2 = load_workbook('文件名称.xlsx')

根据数字得到字母，根据字母得到数字

from openpyxl.utils import get_column_letter, column_index_from_string
 
# 根据列的数字返回字母
print(get_column_letter(2)) # B
# 根据字母返回列的数字
print(column_index_from_string('D')) # 4

删除工作表

# 方式一
wb.remove(sheet)
# 方式二
del wb[sheet]

查看表名和选择表（sheet）

# 显示所有表名
print(wb.sheetnames)
['Sheet2', 'New Title', 'Sheet1']
 
# 遍历所有表
for sheet in wb:
    print(sheet.title)
 
# sheet 名称可以作为 key 进行索引
ws3 = wb["New Title"]
ws4 = wb.get_sheet_by_name("New Title")
ws is ws3 is ws4        # True

设置单元格风格

from openpyxl.styles import Font, colors, Alignment
 
# 字体
## 指定等线24号，加粗斜体，字体颜色红色。直接使用cell的font属性，将Font对象赋值给它
bold_itatic_24_font = Font(name='等线', size=24, italic=True, color=colors.RED, bold=True)
sheet['A1'].font = bold_itatic_24_font
 
# 对齐方式
 
## 使用cell的属性aligment，这里指定垂直居中和水平居中。除了center，还可以使用right、left等等参数。
## 设置B1中的数据垂直居中和水平居中
sheet['B1'].alignment = Alignment(horizontal='center', vertical='center')
 
## 设置行高和列宽
### 第2行行高
sheet.row_dimensions[2].height = 40
### C列列宽
sheet.column_dimensions['C'].width = 30
 
# 合并和拆分单元格
## 所谓合并单元格，即以合并区域的左上角的那个单元格为基准，覆盖其他单元格使之称为一个大的单元格。
## 相反，拆分单元格后将这个大单元格的值返回到原来的左上角位置。
# 合并单元格， 往左上角写入数据即可
sheet.merge_cells('B1:G1') # 合并一行中的几个单元格
sheet.merge_cells('A1:C3') # 合并一个矩形区域中的单元格
合并后只可以往左上角写入数据，也就是区间中:左边的坐标。
如果这些要合并的单元格都有数据，只会保留左上角的数据，其他则丢弃。换句话说若合并前不是在左上角写入数据，合并后单元格中不会有数据。
以下是拆分单元格的代码。拆分后，值回到A1位置。
sheet.unmerge_cells('A1:C3')

创建一个 XLSX 文件

import openpyxl as op

def op_to_excel(data, file_name):
    '''openpyxl库储存数据到excel'''
    wb = op.Workbook()          # 创建工作簿对象
    ws = wb['Sheet']            # 创建子表
    ws.append(['序号', '项目支出', '消费金额']) # 添加表头
    for item in data:
        d = item["id"], item["name"], id["expenses"]
        ws.append(d)        # 每次写入一行
    wb.save(file_name)

# "-------------数据用例-------------"
test_data = [
    {"id": 1, "name": "Rent", "expenses": 1000},
    {"id": 2, "name": "Gas", "expenses": 100},
    {"id": 3, "name": "Food", "expenses": 300},
    {"id": 4, "name": "Gym", "expenses": 50},
]
file_name = 'test_3.xlsx'
pd_to_excel(test_data, file_name)

小结
        各个库最适合的应用场景:

  • 不想使用 GUI 而又希望赋予 Excel 更多的功能，openpyxl 与 xlsxwriter，你可二者选其一；
  • 需要进行科学计算，处理大量数据，建议 pandas+xlsxwriter 或者 pandas+openpyxl；
  • 想要写 Excel 脚本，会 Python 但不会 VBA 的同学，可考虑 xlwings 或 DataNitro；
  • 至于 win32com，不管是功能还是性能都很强大，有 windows 编程经验的同学可以使用。不过它相当于是 windows COM 的封装，自身并没有很完善的文档，新手使用起来略有些痛苦。

2 格式互换

2.1 .mat转.csv

import pandas as pd
import scipy
from scipy import io
import os
#遍历文件夹
for dirname, _, filenames in os.walk('./data'):
    for filename in filenames:
        print(os.path.join(dirname, filename))
        # print(filename)
        # print(os.path.realpath(filename))  # 获取当前文件路径
        print(os.path.dirname(os.path.realpath(filename)))  # 从当前文件路径中获取目录
        # print(os.path.basename(os.path.realpath(filename)))  # 获取文件名
        (file, ext) = os.path.splitext(os.path.realpath(filename))
        # print(file)
        print(os.path.basename(os.path.realpath(file)))  # 获取文件名
        # print(ext)
        print(dirname)


        path = os.path.join(dirname, filename)
        # 1、导入文件
        matfile = scipy.io.loadmat(path)
        # 2、加载数据
        datafile = list(matfile.values())[-1]
        # 3、构造一个表的数据结构，data为表中的数据
        dfdata = pd.DataFrame(data=datafile)
        # 4、保存为.csv格式的路径
        datapath = dirname+'\\'+os.path.basename(os.path.realpath(file))+'.csv'
        # 5、保存为.txt格式的路径
        dfdata.to_csv(datapath, index=False)

2.2 .csv转.npy

import pandas as pd
import numpy as np

# 先用pandas读入csv
data = pd.read_csv("xxxx.csv")
# 再使用numpy保存为npy
np.save("xxx.npy", data)

参考

  • Python玩转Excel神器xlsxwriter详解：https://www.jianshu.com/p/6c979f0c6516
  • Python写入Excel文件-多种实现方式：https://blog.csdn.net/qq_44695727/article/details/109174842
  • python的xlsxwriter模块：https://blog.csdn.net/u010520724/article/details/115758171
  • Python-Excel 模块哪家强？：https://zhuanlan.zhihu.com/p/23998083
  • Getting Started with XlsxWriter：https://xlsxwriter.readthedocs.io/contents.html

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.mfbz.cn/a/112230.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

初识JavaScript(一)

初识JavaScript(一)

文章目录 一、JavaScript介绍二、JavaScript简介1.ECMAScript和JavaScript的关系2.ECMAScript的历史3.什么是Javascript？4.JavaScript的作用?5.JavaScript的特点 三、JavaScript基础1.注释语法2.JavaScript的使用 四、JavaScript变量与常量变量关键字var和let的区别…
阅读更多...
Android广播BroadcastReceiver

Android广播BroadcastReceiver

BroadcastReceiver组件 BroadcastReceiver是Android中的一个组件，用于接收和处理系统广播或应用内广播。它可以监听系统事件或应用内自定义的广播，并在接收到广播时执行相应的操作。 广播是一种用于在应用组件之间传递消息的机制。通过发送广播&#x…
阅读更多...
RT-DERT：在实时目标检测上，DETRs打败了yolo

RT-DERT：在实时目标检测上，DETRs打败了yolo

文章目录 摘要1、简介2. 相关研究2.1、实时目标检测器2.2、端到端目标检测器2.3、用于目标检测的多尺度特征 3、检测器的端到端速度3.1、 NMS分析3.2、端到端速度基准测试 4、实时DETR4.1、模型概述4.2、高效的混合编码器4.3、IoU-aware查询选择4.4、RT-DETR的缩放 5、实验5.1、…
阅读更多...
Microsoft SQL Server 缓冲区错误漏洞(CVE-2018-8273)解决方法

Microsoft SQL Server 缓冲区错误漏洞(CVE-2018-8273)解决方法

前言： 在一次漏洞扫描中，扫描出紧急漏洞Microsoft SQL Server 缓冲区错误漏洞(CVE-2018-8273) 根据修复建议找补丁。 一、漏洞详情 二、寻找补丁 根据漏洞修复建议去下载补丁 目前厂商已发布升级补丁以修复漏洞，补丁获取链接：h…
阅读更多...
k8s中kubectl命令式对象、命令式对象配置、声明式对象配置管理资源介绍

k8s中kubectl命令式对象、命令式对象配置、声明式对象配置管理资源介绍

目录 一.kubernetes资源管理简介 二.三种资源管理方式优缺点比较 三.命令式对象管理介绍 1.kubectl命令语法格式 2.资源类型 （1）通过“kubectl api-resources”来查看所有的资源 （2）每列含义 （3）常…
阅读更多...
【Docker】十分钟完成mysql8安装，你也可以的！！！

【Docker】十分钟完成mysql8安装，你也可以的！！！

十分钟完成mysql8安装，你也可以的 前言安装步骤1.创建安装目录2.创建docker-compose.yml3.启动容器4.mysql开启远程连接5.连接mysql 总结 前言 本文基于Docker安装mysql:8.0.29，首先确保系统安装了docker和docker-compose。 没有使用过docker朋友可以去…
阅读更多...
leetcode-哈希表

leetcode-哈希表

1. 理论 从哈希表的概念、哈希碰撞、哈希表的三种实现方式进行学习 哈希表：用来快速判断一个元素是否出现集合里。也就是查值就能快速判断，O（1）复杂度； 哈希碰撞：拉链法，线性探测法等。只是一种…
阅读更多...
【年终特惠】基于最新导则下生态环评报告编制技术暨报告篇、制图篇、指数篇、综合应用篇系统性实践技能提升

【年终特惠】基于最新导则下生态环评报告编制技术暨报告篇、制图篇、指数篇、综合应用篇系统性实践技能提升

根据生态环评内容庞杂、综合性强的特点，依据生态环评最新导则，将内容分为4大篇章(报告篇、制图篇、指数篇、综合篇)、10大专题(生态环评报告编制、土地利用图的制作、植被类型及植被覆盖度图的制作、物种适宜生境分布图的制作、生物多样性测定、生物量及…
阅读更多...
高压放大器在电火花加工中的作用是什么

高压放大器在电火花加工中的作用是什么

高压放大器在电火花加工中扮演着至关重要的角色。下面安泰电子将详细介绍高压放大器在电火花加工中的作用。 电火花加工是一种精密加工技术，广泛应用于制造业的模具制造、航空航天、汽车零部件等领域。它通过在工件表面产生高频电火花放电的方式，实现对材…
阅读更多...
uniapp实现路线规划

uniapp实现路线规划

UniApp是一个基于Vue.js框架开发的跨平台应用开发框架，可以同时构建iOS、Android、H5等多个平台的应用。它使用了基于前端技术栈的Web开发方式，通过编写一套代码，即可在不同平台上运行和发布应用。 UniApp具有以下特点： 跨平台开…
阅读更多...
Unity内打开网页的两种方式（自带浏览器、内嵌浏览器）

Unity内打开网页的两种方式（自带浏览器、内嵌浏览器）

1.自带浏览器 这个比较简单，直接调用unity官方的API即可，会直接使用默认浏览器打开网页，这里就不多做解释了。 代码 public InputField input;private void OpenUrlByUnity(){string inputStr input.text;if (!string.IsNullOrEmpty(input…
阅读更多...
单链表练习

单链表练习

单链表练习 相关内容： 1.再理解：LNode、*LinkList 2.单链表的整表创建（头插法和尾插法） 3.单链表的读取、插入、删除 4.单链表的整表删除 //单链表的初始化、创建、插入、删除、查找 //结点的结构体：数据域、指针域 …
阅读更多...
开放式耳机能保护听力吗，开放式耳机跟骨传导耳机哪个更好？

开放式耳机能保护听力吗，开放式耳机跟骨传导耳机哪个更好？

如果从严格意义上来讲的话，开放式耳机中的骨传导耳机是能保护听力，现如今的开放式耳机是一个统称，将所有不入耳的类目全部规划到一块。因此在开放式耳机中存在着一些耳机是只能够保持周边环境音，而不是保护听力的。 下面让我来给…
阅读更多...
linux下的程序环境和预处理(gcc演示)

linux下的程序环境和预处理(gcc演示)

1. 程序的翻译环境和执行环境 在ANSI C的任何一种实现中，存在两个不同的环境。 第1种是翻译环境，在这个环境中源代码被转换为可执行的机器指令。 第2种是执行环境，它用于实际执行代码。 2. 详解编译链接 2.1 翻译环境 组成一个程序的…
阅读更多...
MySQL连接的原理⭐️4种优化连接的手段性能提升240%

MySQL连接的原理⭐️4种优化连接的手段性能提升240%

MySQL连接的原理⭐️4种优化连接的手段性能提升240%🚀 前言 上两篇文章我们说到MySQL优化回表的三种方式：索引条件下推ICP、多范围读取MRR与覆盖索引 MySQL的优化利器⭐️索引条件下推，千万数据下性能提升273%🚀 MySQL的优化…
阅读更多...
函数式接口详解（Java）

函数式接口详解（Java）

函数式接口详解（Java）_函数式接口作为参数_凯凯凯凯.的博客-CSDN博客 函数式接口：有且仅有一个抽象方法的接口 Java中函数式编程体现就是Lambda表达式，所以函数式接口就是可以适用于Lambda使用的接口 只有确保接口中仅有一个抽…
阅读更多...
【设计模式】第7节：创建型模式之“建造者模式”

【设计模式】第7节：创建型模式之“建造者模式”

Builder模式，中文翻译为建造者模式或者构建者模式，也有人叫它生成器模式。 在创建对象时，一般可以通过构造函数、set()方法等设置初始化参数，但当参数比较多，或者参数之间有依赖关系，需要进行复杂校验时&a…
阅读更多...
Springboot 集成 Seata

Springboot 集成 Seata

Seata 是一款开源的分布式事务解决方案，致力于提供高性能和简单易用的分布式事务服务。Seata 将为用户提供了 AT、TCC、SAGA 和 XA 事务模式，为用户打造一站式的分布式解决方案。Seata官网 1.找到适合的Seata版本 参考：SpringCloudAlibaba S…
阅读更多...
Linux学习之进程二

Linux学习之进程二

目录 进程状态 R (running)运行状态与s休眠状态： disk sleep（深度睡眠状态） T (stopped)（暂停状态） t----tracing stop(追踪状态) X死亡状态（dead） Z(zombie)-僵尸进程 孤儿进程 进程优…
阅读更多...
python捕获异常和scapy模块的利用

python捕获异常和scapy模块的利用

Python捕获异常 ​ 当程序运行时，因为遇到未知的错误而导致中止运行，便会出现Traceback 消息，打印异常。异常即是一个事件，该事件会在程序执行过程中发生，影响程序的正常执行。一般情况下，在Python 无法正…
阅读更多...
最新文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发 尧图建网站