Python操作Excel工作簿的示例代码(\*.xlsx)

前言

Excel 作为流行的个人计算机数据处理软件,混迹于各个领域,在程序员这里也是常常被处理的对象,可以处理 Excel 格式文件的 Python 库还是挺多的,比如 xlrd、xlwt、xlutils、openpyxl、xlwings 等等,但是每个库处理 Excel 的方式不同,有些库在处理时还会有一些局限性。

接下来对比一下几个库的不同,然后主要记录一下 xlwings 这个库的使用,目前这是个人感觉使用起来比较方便的一个库了,其他的几个库在使用过程中总是有这样或那样的问题,不过在特定情况下使用也是挺不错的。

EXCEL文件

Excel 被称为电子表格,其实际可以保存的格式分为很多种,但是“Excel 工作簿(*.xlsx)”和“Excel 97-2003 工作簿(*.xls)”是其中比较常用的两种,可以认为 .xls 格式的表格是 03版Excel 之前常用的格式,而 .xlsx 是 03版之后,一般指 07版Excel 之后常用的格式。

一般的 Excel 程序对于上述的两种格式都可以打开编辑,也可以相互转化存储,不过还是建议在没有特殊要求的情况下使用新版本的格式,一方面新的稳定版本可能会修复之前的一些BUG,同时也会带来进行一些优化。

我也是在写这篇总结之前才发现,一个空的 .xlsx 格式的文件大小有 7KB,而一个空的 .xls 格式的文件大小有 24KB,当我分别写入一个相同的汉字后,两个文件大小变成了 10KB 和 30KB,差距还是不小的,还有一个问题就是在将 .xlsx 格式的文件另存为 .xls 格式时还会有兼容性提示,提醒用户有些设置可能会丢失,所以能选新版本还是尽量用新版本吧。

测试环境

因为很多应用程序是不断迭代的,相对应的 Python 库也是不断迭代的,这里尽可能的给出版本号,不同的版本可能会有不同的问题:

  • 操作系统: Windows 10 随意版
  • Python: 3.75
  • xlrd: 1.2.0
  • xlwt: 1.3.0
  • xlutils: 2.0.0
  • openpyxl: 3.0.3
  • xlwings: 0.18.0

以上各个程序库使用之前自行安装就行,安装方法就不赘述了,不过可以提供一个可以快速安装镜像源,使用 pip install -i https://pypi.doubanio.com/simple 库名 可以尽可能解决下载安装缓慢的问题。

Excel具体操作

关于使用 Python 具体操作 Excel 的方法可以分为三组,配合使用 xlrd、xlwt、xlutils 操作作为第一组,使用库 openpyxl 作为第二组,而 xlwings 作为第三组,这篇总结重点总结 xlwings 的使用,其他两组简单了解。

xlrd、xlwt、xlutils

这一组操作 Excel 的库名字很形象,一个读、一个写、一个小工具,凑到一起就可以对 Excel 肆意妄为了,下面做个小练习,打开一个 Excel 文件然后修改第一个单元格的值,再另存为一个新文件,代码如下:

import xlrd
import xlwt
import xlutils.copy

def save_as_new_file(file_name, new_file_name):
 # 打开Excel文件
 rb = xlrd.open_workbook(file_name)
 # 创建一个可写入的副本
 wb = xlutils.copy.copy(rb)
 # 获得第一个sheet页签
 ws = wb.get_sheet(0)
 # 第一个单元格写入测试值
 ws.write(0, 0, 'test value')
 # 另存为一个新文件
 wb.save(new_file_name)

上述代码无论是操作 .xlsx 文件还是操作 .xls 文件都不会报错,但是另存为的 .xlsx 格式的文件会打不开,同时你会发现正常存储的 .xls 文件打开后格式全都没了,怎么办,改个参数试试,将打开文件的代码修改如下:

rb = xlrd.open_workbook(file_name, formatting_info=True)

其中参数 formatting_info=True 就表示打开Excel时保留原有的格式,但是这是相对于 .xls 格式的文件,对于 .xlsx 格式的文件直接跑出异常 raise NotImplementedError("formatting_info=True not yet implemented"),就因为处理不了 .xlsx 格式的文件,我暂时没有使用这几个库操作 Excel。

还有一点,这几个库操作单元格时,行和列的索引是从0开始的。

openpyxl

首先说这个库主要用来操作 .xlsx 格式的文件,对于 .xls 格式的文件无法打开,会报 openpyxl does not support the old .xls file format 这样的错误,但是可以存储成这样的格式,再次打开时会有格式不匹配的警告,但是基础的数据还在,所以还是优先用来操作 .xls 格式的文件吧。

写一个新文件的常见用法:

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import Font, Fill, Alignment, PatternFill

def write_new_excel(file_name):
 # 创建一个excel文档
 wb = Workbook()
 # 获得当前激活的sheet对象
 ws = wb.active
 # 给A2单元格赋值
 ws['A2'] = 'This is A2 cell'
 # 一行添加多列数据
 ws.append([1, 2, 'hello'])
 # 添加新的sheet
 ws = wb.create_sheet(title='NewInfo',index=0)
 # 设置单元格的值
 ws['A1'] = 'This is new sheet'

 # 保存excel
 wb.save(file_name)

读取和改写一个原有文件的常见用法:

def read_update_excel(file_name):
 # 加载Excel表
 wb = load_workbook(file_name)
 # 打印sheet数量
 print('sheet count:', len(wb.sheetnames))
 # 打印所有sheet名字
 print('sheet name list:', wb.sheetnames)
 # 获取第一个sheet对象
 ws = wb[wb.sheetnames[0]]
 # 打印sheet表行数和列数
 print('rows count:', ws.max_row, 'cols count:', ws.max_column)
 # 更新单元格A1的内容
 ws['A1'] = 'this is A1'
 # 在第二行位置插入一行
 ws.insert_rows(2)
 # 删除第五行
 ws.delete_rows(5)
 # 获取单元格对象,对应B2单元格
 cell = ws.cell(2,2)
 # 设置单元格内容
 cell.value = 'this is B2'
 # 修改字体格式为粗体
 cell.font = Font(bold=True)
 # 修改单元格格式
 cell.fill = PatternFill("solid", fgColor="F0CDCD")

 # 保存原文件或另存一个文件
 wb.save(file_name)

使用这个库遇到的情况,存储带有样式的数据没有发现问题,但是当加入一个计算公式后,另存为一个文件时明显文件尺寸变小了,但是数据和公式没有发现有问题。

有资料说处理速度真的很慢,因为我处理的文件比较小,但是没有发现这方面的问题,还有一个问题就是说Excel中的宏全部丢失,这个测试的时候确实是丢了,只不过这个好像和文件格式有关,要想保存宏需要存储为 .xlsm 格式,但是 openpyxl 使用来操作 .xlsx 文件的,存储时会导致宏丢失,强行存储为 .xlsm 格式会导致最终的文件打不开。

还有一点,这个库操作单元格时,行和列的索引是从1开始的。

xlwings

这个库在操作的首先要创建一个 App,通过这个创建出来的 App 对象来操作 Excel,非常像把 Excel 的各种操作 api 封装到一起,然后通过这个 App 对象来调用,如果在创建 App 的时候不设置隐藏参数,是会正常打开 Excel 程序的。

使用 xlwings 的基本方式:

import xlwings as xw

# 设置Excel程序不可见
app = xw.App(visible=False, add_book=False)

# 通过 app 操作 Excel文件
# app.bala bala bala .....
# app.bala bala bala .....

# 优雅的退出
app.quit()

创建一个新的 Excel 文件并写入数据:

def write_new_excel(app, file_name):
 # 创建新的 Excel 表
 wb = app.books.add()
 # 获取当前活动的sheet
 ws = wb.sheets.active
 # 初始化二维区域的值
 arr_data = [[1, 2, 3], [4, 5, 6], [7, 8, 'end']]
 # 设置到新建的Excel中
 ws.range('A1:B3').value=arr_data
 # 设置单独一个单元格的值
 ws.range('A4').value='this is A4'
 # 设置单独一个单元格的值
 ws[3,1].value='this is B4'
 # 保存Excel文件
 wb.save(file_name)
 wb.close()

需要注意的是通过行索引和列索引修改单元格时,起始索引是0。

读入已有 Excel 表格并修改

def read_update_excel(app, file_name):
 # 加载已有的表格
 load_wb = app.books.open(file_name)
 # 获取Excel表中第一个sheet
 load_ws = load_wb.sheets[0]
 # 打印sheet的名字
 print(load_ws.name)
 # 根据sheet名字获取sheet对象
 load_ws = load_wb.sheets[load_ws.name]
 # 获取当前活动的sheet
 load_ws = load_wb.sheets.active

 # 获取存在数据的行数和列数
 rows = load_ws.api.UsedRange.Rows.count
 cols = load_ws.api.UsedRange.Columns.count
 print('rows count:', rows, 'cols count:', cols)

 # 修改指定单元格数据(A1单元格)
 load_ws[0,0].value='this is A1'

 # 有空行或空列时获取准确的行列数量
 print(load_ws.used_range.shape)

 # 从A1单元格开始扩展到非空行空列,最后的行数和列数
 print((load_ws.range('A1').expand().last_cell.row,
  load_ws.range('A1').expand().last_cell.column))

 # 从A1单元格开始扩展到非空行空列,最后的行数和列数
 print((load_ws.range('A1').expand().last_cell.row,
  load_ws.range('A1').expand().last_cell.column))

 # 从A1单元格开始扩展到非空行空列,最后形状
 print(load_ws.range(1,1).expand().shape)

 # 从A1单元格开始扩展到非空行空列,最后的行数和列数
 print((load_ws.range('A1').expand('table').rows.count,
  load_ws.range('A1').expand('table').columns.count))

 # 保存修改后的Excel
 load_wb.save(file_name)
 load_wb.close()

Excel 增加删除行和列

def insert_delete_rowscols(app, file_name):
 # 加载已有的表格
 load_wb = app.books.open(file_name)
 # 获取当前活动的sheet
 load_ws = load_wb.sheets.active

 # 从第2行开始插入4行,也就是说2-5行变成新插入的空行
 load_ws.api.rows('2:5').insert
 # 删除第6行和第7行
 load_ws.api.rows('6:7').delete
 # 插入一个单元格,实际测试效果是B列从B2开始向下移动,B2为新添加的单元格
 load_ws.range('B2').api.insert
 # 插入新的一列
 load_ws.api.columns('B').insert
 # 删除一列
 load_ws.api.columns('C').delete

 # 保存修改后的Excel
 load_wb.save(file_name)
 load_wb.close()

单元格宽高查询设置与合并

def cell_operation(app, file_name):
 # 加载已有的表格
 load_wb = app.books.open(FILE_PATH_ROOT + file_name)
 # 获取当前活动的sheet
 load_ws = load_wb.sheets.active

 # 合并单元格
 load_ws.range('A2:A3').api.merge

 #获取单元格
 cell = xw.Range('B2')
 # 打印单元格所在的行和列
 print("row is:", cell.row, "col is:", cell.column)

 # 打印当前格子的高度和宽度
 print("cell.width:", cell.width, "cell.height:", cell.height)

 # 设置当前格子的高度和宽度
 cell.row_height = 32
 cell.column_width = 64

 # 指定单元格的高度和宽度自适应
 cell.columns.autofit()
 cell.rows.autofit()

 # 再次打印当前格子的高度和宽度
 print("cell.width:", cell.width, "cell.height:", cell.height)

 # 保存修改后的Excel
 load_wb.save(file_name)
 load_wb.close()

几个库支持情况对比

虽然前面写了这么多方法,但是遇到一个实际的问题时还是会犹豫,到底用哪种方式呢?下面做一个简单的对比,只是根据我做的实验来简单对比,如果有不准确甚至是错误的地方,欢迎大家指出来,我会尽快改正的。

情景/库 xlrd、xlwt、xlutils openpyxl xlwings
读取.xls 可以带有样式读取 不支持 可以读取
保存.xls 可以带有样式保存 可以保存,但是提示文件扩展名不匹配,可以看到原始数据 可以保存,但是提示文件扩展名不匹配,可以看到原始数据
读取.xlsx 可以读取,但没有样式 可以带有样式读取 可以带有样式读取
保存.xlsx 保存后打不开 可以带有样式保存 可以带有样式保存
读取.xlsm 可以读取,但没有样式和宏 可以读取,但没有宏 可以读取包含宏的表格
保存.xlsm 保存后打不开,存成 .xls 格式宏丢失 保存后打不开,存成 .xls想 格式宏丢失 存储后宏还在
增删行和列 没有直接方法 支持 支持
另存后大小 .xls 文件没有变化 .xlsx 文件会变小 .xls、.xlsx 文件没有变化
使用建议 只操作.xls文件可以考虑 只操作.xlsx文件可以考虑,不能带有宏 一个比较好的选择,使用时感觉速度稍微有点慢

总结

  1. Excel 表格程序经过版本的更替发生了很大的变化,出现了相同内容时 .xls 比 .xlsx 格式的文件大很多的情况
  2. 基于上一点考虑,如果能使用的新版的表格,那么就放弃旧的格式的吧
  3. 还有一个神奇的情况,一个带有少量数据的 .xlsx 格式的表格要比一个空表格还要小,这是什么情况,暂时没弄明白怎么回事,求知道的大神告知一二

到此这篇关于Python操作Excel工作簿的示例代码(\*.xlsx)的文章就介绍到这了,更多相关Python操作Excel工作簿内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • Python第三方库xlrd/xlwt的安装与读写Excel表格

    前言 相信大家都应该有所体会,在平时经常会遇到处理 Excel 表格数据的情况,人工处理起来实在是太麻烦了,我们可以使用 Python 来解决这个问题,我们需要两个 Python 扩展, xlrd 和 xlwt . xlrd和xlwt是Python的第三方库,所以是需要自己安装的,可以在python的官网https://pypi.python.org/pypi下载该模块来安装,也可以通过其他手段,比如easy_install或者pip,下面来看看详细的安装介绍与读写Excel表格的方法吧. 使用

  • python高手之路python处理excel文件(方法汇总)

    用python来自动生成excel数据文件.python处理excel文件主要是第三方模块库xlrd.xlwt.xluntils和pyExcelerator,除此之外,python处理excel还可以用win32com和openpyxl模块. 方法一: 小罗问我怎么从excel中读取数据,然后我百了一番,做下记录 excel数据图(小罗说数据要给客户保密,我随手写了几行数据): python读取excel文件代码: #!/usr/bin/env python # -*- coding: utf-

  • python中使用xlrd、xlwt操作excel表格详解

    最近遇到一个情景,就是定期生成并发送服务器使用情况报表,按照不同维度统计,涉及python对excel的操作,上网搜罗了一番,大多大同小异,而且不太能满足需求,不过经过一番对源码的"研究"(用此一词让我觉得颇有成就感)之后,基本解决了日常所需.主要记录使用过程的常见问题及解决. python操作excel主要用到xlrd和xlwt这两个库,即xlrd是读excel,xlwt是写excel的库.可从这里下载https://pypi.python.org/pypi.下面分别记录python

  • 用python读写excel的方法

    本文实例讲述了用python读写excel的方法.分享给大家供大家参考.具体如下: 最近需要从多个excel表里面用各种方式整理一些数据,虽然说原来用过java做这类事情,但是由于最近在学python,所以当然就决定用python尝试一下了.发现python果然简洁很多.这里简单记录一下.(由于是用到什么学什么,所以不算太深入,高手勿喷,欢迎指导) 一.读excel表 读excel要用到xlrd模块,官网安装(http://pypi.python.org/pypi/xlrd).然后就可以跟着里面

  • Python读写Excel文件的实例

    最近由于经常要用到Excel,需要根据Excel表格中的内容对一些apk进行处理,手动处理很麻烦,于是决定写脚本来处理.首先贴出网上找来的读写Excel的脚本. 1.读取Excel(需要安装xlrd): #-*- coding: utf8 -*- import xlrd fname = "reflect.xls" bk = xlrd.open_workbook(fname) shxrange = range(bk.nsheets) try: sh = bk.sheet_by_name(

  • Python使用xlwt模块操作Excel的方法详解

    本文实例讲述了Python使用xlwt模块操作Excel的方法.分享给大家供大家参考,具体如下: 部分摘自官网文档. 该模块安装很简单 $ pip install xlwt 先来个简单的例子: #!/usr/bin/python #coding=utf-8 # ============================================================================== # # Filename: demo.py # Description: exc

  • Python操作Excel之xlsx文件

    前言 之前处理excel的读写时用的是xlrd/xlwt,但是这两个库有个缺点就是只对xls的格式处理的比较好,对以xlsx结尾的格式就不行了.由于现在大家使用的都是最新版本的office,excel的格式都是xlsx,因此此时再继续用xlrd/xlwt处理就不合适了,庆幸的是对于xlsx文件的读写,我们还可以使用openpyxl来操作. 我对excel并不熟悉,平时也不怎么用,所以对excel的处理很简单,只是简单的读写,这里演示的也是简单的读写操作,具体的高级功能,可以参考文后的链接地址.

  • 使用python将大量数据导出到Excel中的小技巧分享

    (1) 问题描述:为了更好地展示数据,Excel格式的数据文件往往比文本文件更具有优势,但是具体到python中,该如何导出数据到Excel呢?如果碰到需要导出大量数据又该如何操作呢? 本文主要解决以上两个问题. (2)具体步骤如下: 1.第一步,安装openpyxl, 使用pip install openpyxl即可,但是在windows下安装的是2.2.6版本,但是centos自动安装的是4.1版本,(多谢海哥的提醒). 写的代码在windows下运行没问题,但centos上却报错了,说是e

  • Python操作Excel工作簿的示例代码(\*.xlsx)

    前言 Excel 作为流行的个人计算机数据处理软件,混迹于各个领域,在程序员这里也是常常被处理的对象,可以处理 Excel 格式文件的 Python 库还是挺多的,比如 xlrd.xlwt.xlutils.openpyxl.xlwings 等等,但是每个库处理 Excel 的方式不同,有些库在处理时还会有一些局限性. 接下来对比一下几个库的不同,然后主要记录一下 xlwings 这个库的使用,目前这是个人感觉使用起来比较方便的一个库了,其他的几个库在使用过程中总是有这样或那样的问题,不过在特定情

  • C++实现将数据写入Excel工作表的示例代码

    目录 安装Spire.XLS for C++ 在 C++ 中将文本或数字值写入单元格 完整代码 效果图 在 C++ 中将数组写入指定的单元格范围 完整代码 效果图 直观的界面.出色的计算功能和图表工具,使Excel成为最流行的个人计算机数据处理软件.在独立的数据包含的信息量太少,而过多的数据又难以理清头绪时,制作成表格是数据管理的最有效手段之一.这样不仅可以方便整理数据,还可以方便我们查找和应用数据.后期我们还可以对具有相似表格框架,相同性质的数据进行合并汇总工作.在本文中,您将学习如何使用 S

  • python实现excel公式格式化的示例代码

    之前跟一些小伙伴有个讨论: 大概就是很多跟数据打交道的朋友都面对过很复杂的excel公式,有时嵌套层数特别多,肉眼观看很容易蒙圈. 有了这样的需求,我就有了解决问题的想法,说干就干,于是一个比较牛逼的excel公式格式化的工具就出现了. 效果体验 先看看效果吧: =IF(C11>100%*C4,IF(C11<=200%*C4,C11*50%-C4*15%,C11*60%-C4*35%),IF(C11<=C4*50%,C11*30%,C11*40%-C4*5%)) 的格式化结果是: =IF

  • 使用python创建Excel工作簿及工作表过程图解

    1 配置信息: 创建 excel 工作簿之前的路径 2 创建工作簿 创建工作簿之后的路径 工作簿内容 3 创建工作表 工作簿信息 以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持我们.

  • python对Excel的读取的示例代码

    在python自动化中,经常会遇到对数据文件的操作,比如添加多名员工,但是直接将员工数据写在python文件中,不但工作量大,要是以后再次遇到类似批量数据操作还会写在python文件中吗? 应对这一问题,可以将数据写excel文件,针对excel 文件进行操作,完美解决. 本文仅介绍python对excel的操作 安装xlrd 库 xlrd库 官方地址:https://pypi.org/project/xlrd/ pip install xlrd 笔者在安装时使用了 pip3 install x

  • 使用Python操作excel文件的实例代码

    使用的类库 pip install openpyxl 操作实现 •工作簿操作 # coding: utf-8 from openpyxl import Workbook # 创建一个excel工作簿 wb = Workbook() # 打开一个工作簿 wb = load_workbook('test.xlsx') # 保存工作簿到文件 wb.save('save.xlsx') •工作表操作 # 获得当前的工作表对象 ws = wb.active # 通过工作表名称得到工作表对象 ws = wb.

  • Python批量处理工作簿和工作表的实现示例

    目录 批量新建并保存工作簿 批量打开一个文件夹中的打开工作簿 批量重命名一个工作簿的所有工作表 批量重命名多个工作簿 批量重命名多个工作簿中的同名工作表 将一个工作簿的所有工作表批量复制到其他工作簿 按条件将一个工作表拆分为多个工作簿 批量新建并保存工作簿 代码 import xlwings as xw # 启动 Excel,但不新建工作簿 app = xw.App(visible=True,add_book=False) for i in range(5): #新建工作簿 workbook =

  • python操作excel让工作自动化

    某局某领导给了3只excel文件,一只里面有4个sheet需要处理,一个sheet有250+列,算下来总共有3000+列需要手动反复插入.删除列.拷贝.求和,所以给了4天的时间要完成. 我不愿意做大量简单而且又是重复性工作,看了看3只表格的格式基本一样,于是我用python写了30行代码完成了这个"艰巨"任务. 0x01 用python操作excel需要安装xlrd.xlwt(或者其他的模块也行). 读取excel文件:workbook = xlrd.open_workbook('fi

  • Python操作excel的方法总结(xlrd、xlwt、openpyxl)

    前言 在处理excel数据时发现了xlwt的局限性–不能写入超过65535行.256列的数据(因为它只支持Excel 2003及之前的版本,在这些版本的Excel中行数和列数有此限制),这对于实际应用还是不够的.为此经过一番寻找发现了一个支持07/10/13版本Excel的openpyxl,虽然功能很强大,但是操作起来感觉没有xlwt方便.下面分别说下几个模块的常用操作. xlrd xlrd是用来从Excel中读写数据的,但我平常只用它进行读操作,写操作会遇到些问题.用xlrd进行读取比较方便,

  • 如何基于python操作excel并获取内容

    这篇文章主要介绍了如何基于python操作excel并获取内容,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 背景:从excel表中获取请求url.请求数据.请求类型.预期结果 因此,需要学会如何使用python从excel获取这些信息 #coding=utf-8 import xlrd #创建对象时,获取对应excel 表格 #读取Excel行数 #获取单元格内容 class OperationExcel: def __init__(self

随机推荐