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工作簿内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

时间: 2020-03-23

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

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

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中使用xlrd、xlwt操作excel表格详解

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

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高手之路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读写excel的方法

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

Python读写Excel文件方法介绍

一.读取excel 这里介绍一个不错的包xlrs,可以工作在任何平台.这也就意味着你可以在Linux下读取Excel文件. 首先,打开workbook: 复制代码 代码如下: import xlrd wb = xlrd.open_workbook('myworkbook.xls') 检查表单名字: 复制代码 代码如下: wb.sheet_names() 得到第一张表单,两种方式:索引和名字 复制代码 代码如下: sh = wb.sheet_by_index(0) sh = wb.sheet_by

python操作excel的方法

摘要: Openpyxl是一个常用的python库,用于对Excel的常用格式及其模板进行数据读写等操作. 简介与安装openpyxl库 Openpyxl is a Python library for reading and writing Excel 2010 xlsx/xlsm/xltx/xltm files. 安装 pip install openpyxl pillow:在文件中需要使用images (jpeg, png, bmp,...)时,需要安装pillow库. 注意:写操作时请关

python读写二进制文件的方法

本文实例讲述了python读写二进制文件的方法.分享给大家供大家参考.具体如下: 初学python,现在要读一个二进制文件,查找doc只发现 file提供了一个read和write函数,而且读写的都是字符串,如果只是读写char等一个字节的还行,要想读写如int,double等多字节数 据就不方便了.在网上查到一篇贴子,使用struct模块里面的pack和unpack函数进行读写.下面就自己写代码验证一下. >>> from struct import * >>> fi

python读写ini配置文件方法实例分析

本文实例讲述了python读写ini配置文件方法.分享给大家供大家参考.具体实现方法如下: import ConfigParser import os class ReadWriteConfFile: currentDir=os.path.dirname(__file__) filepath=currentDir+os.path.sep+"inetMsgConfigure.ini" @staticmethod def getConfigParser(): cf=ConfigParser

Python读取Excel的方法实例分析

本文实例讲述了Python读取Excel的方法.分享给大家供大家参考.具体如下: 今天需要从一个Excel文档(.xls)中导数据到数据库的某表,开始是手工一行行输的.后来想不能一直这样,就用Python写了下面的代码,可以很方便应对这种场景.比如利用我封装的这些方法可以很方便地生成导入数据的SQL. 当然熟悉Excel编程的同学还可以直接用VBA写个脚本生成插入数据的SQL. 还可以将.xls文件改为.csv文件,然后通过SQLyog或者Navicat等工具导入进来,但是不能细粒度控制(比如不

Python读写配置文件的方法

本文实例讲述了Python读写配置文件的方法.分享给大家供大家参考.具体分析如下: python 读写配置文件在实际应用中具有十分强大的功能,在实际的操作中也有相当简捷的操作方案,以下的文章就是对python 读写配置文件的具体方案的介绍,相信对大家学习Python有所帮助. python 读写配置文件ConfigParser模块是python自带的读取配置文件的模块.通过他可以方便的读取配置文件. 这里就来简单介绍一下python 读写配置文件的方法. 配置文件.顾名思议就是存放配置信息的文件

python 读写excel文件操作示例【附源码下载】

本文实例讲述了python 读写excel文件操作.分享给大家供大家参考,具体如下: 对excel文件的操作,python有第三方的工具包支持,xlutils,在这个工具包中包含了xlrd,xlwt等工具包.利用这些工具,可以方便的对excel 进行操作. 1. 下载 xlutils : http://pypi.python.org/pypi/xlutils 2. 安装,解压下载文件之后,可以 python setup.py install 3. 应用(生成EXCEL,遍历EXCEL,修改EXC

python操作excel的方法(xlsxwriter包的使用)

本文介绍python操作excel的方法(xlsxwriter包的使用),具体内容如下 xlsxwriter包的安装 pip install xlsxwriter Workbook类 创建一个excel文件 filename = "test.xlsx" # Workbook代表整个电子表格文件,并且存储在磁盘上.filename为字符串类型,表示创建的excel文件存储路径: wb = xlsxwriter.Workbook(filename) 创建一个sheet表:add_works

python读写Excel表格的实例代码(简单实用)

安装两个库:pip install xlrd.pip install xlwt 1.python读excel--xlrd 2.python写excel--xlwt 1.读excel数据,包括日期等数据 #coding=utf-8 import xlrd import datetime from datetime import date def read_excel(): #打开文件 wb = xlrd.open_workbook(r'test.xlsx') #获取所有sheet的名字 print