1 操作Excel文件-多种实现方式
在实际生产中，经常会用到excel来处理数据，虽然excel有强大的公式，但是很多工作也只能半自动化，配合Python使用可以自动化部分日常工作，大大提升工作效率。
openpyxl：只允许读取和写入.xlsx格式文件并进行增删改增查。xlwings：允许读取和写入.xlsx和.xls两种格式文件并进行增删改查。xlsxwriter：只允许写入.xlsx格式的文件。
比较三者，你可能会觉得 xlsxwriter 这个库也太不行了吧？其实不是的，在写入这方面前两个库比不上它，它的精华在于写入(多张样式图表、图片、表格样式修改等)。
xlsxwriterexcel_7">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. 常用操作
添加工作表样式
python">bold = workbook.add_format({
'bold': True, # 字体加粗
'border': 1, # 单元格边框宽度
'align': 'left', # 水平对齐方式
'valign': 'vcenter', # 垂直对齐方式
'fg_color': '#F4B084', # 单元格背景颜色
'text_wrap': True, # 是否自动换行
})
写入单元格数据
python"># 写入单个单元格数据
# 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)
插入图片
python"># 第一个参数是插入的起始单元格，第二个参数是图片你文件的绝对路径
worksheet1.insert_image('A1','f:\\1.jpg')
写入超链接
python">worksheet1.write_url(row, col, "internal:%s!A1" % ("要关联的工作表表名"), string="超链接显示的名字")
插入图表
python">workbook.add_chartsheet（type=""）
# 参数中的type指的是图表类型，图表类型示例如下：
# [area：面积图,bar：条形图,column：直方图,doughnut：环状图,line：折线图,pie：饼状图,scatter：散点图,radar：雷达图,stock:箱线图]
3. 创建一个简单的 XLSX 文件
假设我们有一些月度支出的数据，我们希望将其转换为 Excel XLSX 文件：
python">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文件
python">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文件
python">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()
测试记录:
pandasexcel_210">1.2 pandas库储存数据到excel
在Python中，pandas是基于NumPy数组构建的，使数据预处理、清洗、分析工作变得更快更简单。pandas是专门为处理表格和混杂数据设计的，而NumPy更适合处理统一的数值数组数据。pandas有两个主要数据结构：Series和DataFrame。
Series是一种类似于一维数组的对象，它由一组数据（各种NumPy数据类型）以及一组与之相关的数据标签（即索引）组成，即index和values两部分，可以通过索引的方式选取Series中的单个或一组值。
DataFrame是一个表格型的数据类型，每列值类型可以不同，是最常用的pandas对象。DataFrame既有行索引也有列索引，它可以被看做由Series组成的字典（共用同一个索引）。DataFrame中的数据是以一个或多个二维块存放的（而不是列表、字典或别的一维数据结构）
python">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)
openpyxlexcel_248">1.3 openpyxl库储存数据到excel
安装：pip install openpyxl==2.2.6
打开已有文件
python">from openpyxl import load_workbook
wb2 = load_workbook('文件名称.xlsx')
根据数字得到字母，根据字母得到数字
python">from openpyxl.utils import get_column_letter, column_index_from_string
# 根据列的数字返回字母
print(get_column_letter(2)) # B
# 根据字母返回列的数字
print(column_index_from_string('D')) # 4
删除工作表
python"># 方式一
wb.remove(sheet)
# 方式二
del wb[sheet]
查看表名和选择表（sheet）
python"># 显示所有表名
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
设置单元格风格
python">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 文件
python">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
python">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
python">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
