13用Python读写Excel文件

  • Z时代
  • 2024-01-10
  • 分类：综合

python

在以前，商业分析对应的英文单词是Business Analysis，大家用的分析工具是Excel，后来数据量大了，Excel应付不过来了（Excel最大支持行数为1048576行），人们开始转向python和R这样的分析工具了

XlsxWriter

xlrd&xlwt

OpenPyXL

Microsoft Excel API

介绍

可以创建 Excel 2007 或更高版本的 XLSX 文件

即 python-excel，含 xlrd、xlwt 和 xlutils 三大模块，分别提供读、写和其他功能

可以读写 Excel 2007 XLSX 和 XLSM 文件

直接通过 COM 组件与Microsoft Excel 进程通信，调用其各种功能实现对 Excel 文件的操作

读

❌

✅

✅

✅

写

✅

✅

✅

✅

修改

❌

❌

⚠️

✅

.xls

❌

✅

❌

✅

.xlsx

✅

⚠️

✅

✅

大文件

✅

❌

✅

❌

功能

强

弱

一般

超强

速度

快

快

快

超慢

系统

无限制

无限制

无限制

Windows + Excel

使用场景

要创建 XLSX 文件
不需要读取已有文件

需要实现比较复杂的功能
数据量可能会很大
需要跨平台

要读取 XLS 或 XLSX 文件
要生成 XLS 文件
需要的功能不太复杂
需要跨平台

要处理 XLSX 文件
需要修改已有文件，或者在写入过程中需要不断修改
需要的功能比较复杂
数据量可能会很大
需要跨平台

需要处理各种文件格式
需要用到特别复杂的功能
在修改文件时，不希望对原有信息造成任何意外破坏
数据量很小，或者愿意等待
仅在 Windows 中使用

XlsxWriter

Xlsx是python用来构造xlsx文件的模块，可以向excel2007+中写text，numbers，formulas 公式以及hyperlinks超链接。

可以完成xlsx文件的自动化构造，包括：合并单元格，制作excel图表等功能：

1.优点

一、功能比较强

相对而言，这是除Excel自身之外功能最强的工具了。比如我就用到了它提供的：字体设置、前景色背景色、border设置、视图缩放（zoom）、单元格合并、autofilter、freeze panes、公式、data validation、单元格注释、行高和列宽设置等等。

二、支持大文件写入

如果数据量非常大，可以启用constant memory模式，这是一种顺序写入模式，得到一行数据就立刻写入一行，而不会把所有的数据都保持在内存中。

2.缺点

一、不支持读取和修改

作者并没有打算做一个XlsxReader来提供读取操作。不能读取，也就无从修改了。它只能用来创建新的文件。我是利用xlrd把需要的信息读入后，用XlsxWriter创建全新的文件。

另外，即使是创建到一半Excel文件，也是无法读取已经创建出来的内容的（信息应该在，但是并没有相应的接口）。因为它的主要方法是write而不是set。当你在某个单元格写入数据后，除非你自己保存了相关的内容，否则还是没有办法读出已经写入的信息。从这个角度看，你无法做到读出->修改->写回，只能是写入->写入->写入。

二、不支持XLS文件

XLS是Office 2013或更早版本所使用的格式，是一种二进制格式的文件。XLSX则是用一系列XML文件组成的（最后的X代表了XML）一个压缩包。如果非要创建低版本的XLS文件，就请移步xlwt吧。

三、暂时不支持透视表（Pivot Table)

3.基本操作方法

  • 创建excel文件

import xlsxwriter
f = xlsxwriter.Workbook()    # 创建excel文件

  • 创建工作表

worksheet1 = f.add_worksheet("操作日志")  # 括号内为工作表表名

  • 添加工作表样式

样式有很多属性，更多具体样式请参考官方文档 https://xlsxwriter.readthedocs.io。

bold = f.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()

workbook.worksheets()用于获得当前工作簿中的所有工作表，这个函数的存在便利了对于工作表的循环操作，如果你想在当前工作簿的所有工作表的A1单元格中输入一个字符创‘Hello xlsxwriter’，那么这个命令就派上用场了。

  • 关闭excel文件

workbook.close()

这个命令是使用xlsxwriter操作Excel的最后一条命令，一定要记得关闭文件。

参考 https://www.jianshu.com/nb/32754082

案例

import xlsxwriter
# 新建excel表
workbook = xlsxwriter.Workbook("D:/hello.xlsx")
# 新建sheet（sheet的名称为"sheet1"）
worksheet = workbook.add_worksheet()
# 定义表头内容
Title = ["实例ID", "实例配置", "实例名", "地域", "安全组", "EIP"]
# 定义标题格式
merge_format = workbook.add_format({
    "bold": True,
    "border": 1,
    "align": "center",
    "valign": "vcenter",
    "fg_color": "#E0FFFF"
})
# 定义表头格式
title_format = workbook.add_format({
    "bold": True,
    "border": 1,
    "align": "center",
    "valign": "vcenter",
    "fg_color": "#87CEFF"
})
# 定义内容格式
data_format = workbook.add_format({
    "border": 1,
    "align": "center",
    "valign": "vcenter",
    "fg_color": "#EED8AE"
})
# 拟数据
data = [["i-1", "1C1G", "demo1", "上海", "sg-1", "172.20.2.10"],
        ["i-2", "1C1G", "demo2", "郑州", "sg-2", "172.20.2.11"],
        ["i-3", "1C1G", "demo3", "北京", "sg-3", "172.20.2.12"]]
# 合并 A1 - F1 单元格作为标题，传入参数：1：合并单元格，2：标题文字，3：标题格式
worksheet.merge_range("A1:F1", "ECS信息表", merge_format)
# 设置列宽，指定 A - F 的列宽为 25
worksheet.set_column("A:F", 25)
# 设置行号，指定行数，高度，A1 为 0，A2 为 1，以此类推
worksheet.set_row(0, 60)
# write_row ，写行，传入参数：1：行，2：内容，3：格式
worksheet.write_row("A2", Title, title_format)
i = 3
for ECS in data:
    worksheet.write_row("A" + str(i), ECS, data_format)
    i += 1
# 将excel文件保存关闭，如果没有这一行运行代码会报错
workbook.close()

在线练习：https://www.520mg.com/it

以上是 13用Python读写Excel文件 的全部内容， 来源链接： utcz.com/z/530275.html

回到顶部