python操作Excel,你觉得哪个库更好呢?

微信公众号:python爬虫实战之路 关注可了解更多的python爬虫实战项目。问题或建议,请公众号留言

Excel数据的类型及组织方式

每一个Excel数据文件从上至下分为三个层级的对象: workbook: 每一个Excel文件就是一个workbook。 sheet: 每一个workbook中可以包含多个sheet,具体就对应Excel中我们在左下脚所看到的“sheet1”,“sheet2”等。 cell: 每一个sheet就是我们通常所看到的一个表格,可以含有m行,n列,每个确定的行号,列号所对应的一个格子就是一个cell。

Excel模块对比

平台:Mac 工具:PyCharm Python:python3.6 Excel行数:64543

openpyxl

xlrd&xlwt

xlsxwriter

读取

9.40690302848816

5.220927000045776

不支持

读写

8.909524202346802

6.502154111862183

9.416481733322144

类型

xlsx

xlsx、xls(写)

xlsx

格式

可读取

xlsx不支持

X

从一个既有的xlsx文件中读取数据,按照Excel文件的三个层级,分别来看一下以上三个库的操作方式。

xlrd & xlwt & xlutils

xlrd负责读取Excel文件,支持xls、xlsx xlwt负责写入Excel文件,只支持xls格式。对于现在已经Office2019了,还只支持2003版的格式难怪会被人抛弃。 xlutils是一个工具模块,我们一般只用到copy模块,用于修改已存在的Excel文件

open_workbook

param: encoding_override 读取Excel文件时默认使用ASCII编码。此处可指定编码。 param: formatting_info 设置是否保存Excel格式,比如加粗。一般只对简单的格式起作用,而且xlsx格式文件不支持。 这可以节省内存。在这种情况下,通过忽略文件BLANK和MULBLANK记录,“空白”单元格(它们具有自己的格式信息但没有数据)会被视为空白

param: on_demand 按需加载工作表。默认加载False,加载所有的工作表。设置True时,调用book.sheet_by_name或book.sheet_by_index才会加载工作表。book.sheets加载所有工作表。book.sheet_loaded判断表单是否已加载。book.unload_sheet卸载已加载表单。

  1. 打开workbook: import xlrd book = xlrd.open_workbook("myfile.xls") #book就赋值为一个Excel文件了 注: Book 类的方法、属性等:即就可以对上面的book进行操作了 book.nsheets: 在Book对象中的文件有多少个worksheet。 book.sheet_by_index(sheetx): 根据提供的sheetx索引来获取我们需要的sheet表,返回的是一个Sheet类的实例。 book.sheet_by_name(sheet_name): 根据提供的sheet_name来获取对应名称的sheet类对象,返回的也是一个Sheet类的对象。 book.sheet_names(): 在Book对象中的所有sheet表的名称列表。 book.sheets(): 返回在Book对象中所有的Sheet对象实例列表。
  2. 打开所需的sheet: sheet = book.sheet_by_index(0) #获得一个sheet,也可以使名字获得 print sheet.name, sheet.nrows, sheet.ncols 注: Sheet类方法、属性等: sheet.cell(rowx, colx): 根据给出的行和列的参数获取得到cell类,返回一个Cell类实例对象。 sheet.cell_type(rowx, colx): 返回对应的cell对象的Type类型。 sheet.cell_value(rowx, colx): 返回对应的cell对象的value值。 sheet.col(colx): 返回指定列的所有cell类对象序列。 sheet.name: 返回sheet对象的名称。 sheet.ncols: 返回在sheet对象中的列的数目。 sheet.nrows: 返回在sheet对象中的行的数目。 sheet.row(rowx): 返回指定的行的所有cell对象的序列。 sheet.row_values(rowx, start_colx=0, end_colx=None):获取行内容。 sheet.col_values(colx, start_rowx=0, end_rowx=None):获取列内容。
  3. 获取对应cell的值: cell=sheet.cell(rowx=29, colx=3) #根据给出的行和列的参数获取得到cell类,返回一个Cell类实例对象. sh.cell_value(rowx=29, colx=3) 注: Cell类的属性、方法如下: Cell类对象有3种属性:ctype, value, xf_index 如果在excel文件打开的时候,formatting_info未启用的时候,xf_index是为None 下面列出了cell的类型,以及他们在python中所代表的值 type symbol type number python value XL_CELL_EMPTY 0 空的字符串'' XL_CELL_TEXT 1 unicode字符串 XL_CELL_NUMBER 2 float XL_CELL_DATE 3 float XL_CELL_BOOLEAN 4 int;1 --- True,0 --- False XL_CELL_ERROR 5 int代表是一个excel内部错误码; XL_CELL_BLANK 6 空的字符串'', 注意:这个类型仅仅会出现,当函数open_workbook(..,formatting_info=True)这样设置的时候。

Excel单元格内容存在不同的格式,可使用cell.ctype获取类型判断。 ctype : 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error 日期类型可以使用xldate_as_tuple模块处理:

value = sheet.cell_value(row, col)
if sheet.cell(row, col).ctype == 3:
    date_value = xlrd.xldate_as_tuple(value, book.datemode) #输出元组 (2018, 2, 23, 0, 0, 0)

openpyxl

openpyxl支持可读可写操作,不支持xls文件的读写

. 一个简单创建例子

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws['A1'] = 42
ws.append([1, 2, 3])
import datetime
ws['A3'] = datetime.datetime.now().strftime("%Y-%m-%d")
wb.save("sample.xlsx")

通过openpyxl.workbook.Workbook.active()得到worksheet. ws = wb.active 注意: 该方法使用_active_sheet_index属性, 默认会设置0,也就是第一个worksheet。除非手动修改,否则使用active方法得到都是第一个worksheet。 你也可以创建worksheets,通过 openpyxl.workbook.Workbook.create_sheet() 方法: ws1 = wb.create_sheet("Mysheet") #插入到最后(default)ws2 = wb.create_sheet("Mysheet", 0) #插入到最开始的位置

创建的sheet的名称会自动创建,按照sheet,sheet1,sheet2自动增长,通过title属性可以修改其名称。 ws.title = "New Title"

默认的sheet的tab是白色的,可以通过 RRGGBB颜色来修改sheet_properties.tabColor属性从而修改sheet tab按钮的颜色: ws.sheet_properties.tabColor = "1072BA" 当你设置了sheet的名称,可以将其看成workbook中的一个key。

工作表有两种获取方式 ws3 = wb["New Title"] --推荐 ws4 = wb.get_sheet_by_name("New Title")

查看workbook中的所有worksheets名称:book.sheetnames 遍历worksheets:

for sheet in book:
    print sheet.title

. 操作数据

单元格可以看作是worksheet的key,通过key去访问和设置单元格中的数据 value = ws["A4"] ws["A4"] = data 注意:

  1. 当worksheet在内存中被创建时,是没有包含cells的,cells是在首次访问时创建.
  2. 可以循环在内存中创建cells,这时不指定他们的值也会创建该cells些:(创建100x100cells)
for i in range(1,101):
    for j in range(1,101):
        ws.cell(row=i, column=j)

通过切片Ranges指定许多cells cell_range = ws['A1': 'C2'] 同样也可以Ranges行或者列: colC = ws['C'] col_range = ws['C:D'] rwo10 = ws[10] rwo_range = ws[5:10]

也可以使用 openpyxl.worksheet.Worksheet.iter_rows() 方法:(需要指定行->行,截止列)

for row in ws.iter_rows(min_col=1, min_row=1, max_col=3, max_row=2):
    for cell in row:
        print cell.value

如果你需要遍历所有文件的行或列,可以使用openpyxl.worksheet.Worksheet.rows() 属性:tuple(ws.rows)或者 openpyxl.worksheet.Worksheet.columns() 属性:tuple(ws.columns)

最简单最安全的方法保存workbook是使用openpyxl.workbook.Workbook对象的 openpyxl.workbook.Workbook.save()方法: wb = Workbook() wb.save('balances.xlsx')

保存的默认位置在python的根目录下。 注意:会自动覆盖已经存在文件名的文件。 我们可以导入一个文件进行读或者写,像写一样我们可以导入openpyxl.load_workbook()已经存在的workbook:

from openpyxl import load_workbook
wb2 = load_workbook('test.xlsx')
print wb2.get_sheet_names()

append函数

可以一次添加多行数据,从第一行空白行开始(下面都是空白行)写入 如果需要整列进行添加数据:

lst = ["test1", "teset2", "test3", "test4"]
for row in range(1, 40):
    ws1.append(lst)
# 添加多行 
rows = [ ['Number', 'data1', 'data2'], [2, 40, 30], [3, 40, 25], [4, 50, 30], [5, 30, 10], [6, 25, 5], [7, 50, 10]]
map(ws1.append, rows)

由于append函数只能按行写入。如果我们想按列写入呢? 写入例子:

# workbook相关
from openpyxl import Workbook
from openpyxl.compat import range
from openpyxl.utils import get_column_letter
wb = Workbook()
dest_filename = 'empty_book.xlsx'  
ws1 = wb.active
ws1.title = "range names"  
for row in range(1, 40):
    ws1.append(["test%s" % i for i in range(6)])
ws2 = wb.create_sheet(title="Pi")
ws2['F5'] = 3.14  
ws3 = wb.create_sheet(title="Data")
for row in range(10, 20):
    for col in range(27, 54):
        _ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
print(ws3['AA10'].value)
wb.save(filename=dest_filename)

使用公式

from openpyxl import Workbook
wb = Workbook()
ws = wb.active

# add a simple formula 
for i in range(1, 20):
    ws.append([i * num for num in range(1, 4)])
for i in range(1, 20):
    ws["D%s" % i] = "=SUM(A{0}, C{0})".format(i)
wb.save("formula.xlsx")

. 设置单元格风格--Style

先导入需要的类from openpyxl.styles import Font, colors, Alignment 分别可指定字体相关,颜色,和对齐方式。

字体

bold_itatic_24_font = Font(name='等线', size=24, italic=True, color=colors.RED, bold=True) 
sheet['A1'].font = bold_itatic_24_font

上面的代码指定了等线24号加粗斜体,字体颜色红色。直接使用cell的font属性,将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')

xlsxwriter

只支持写入,不支持读取,也不支持查询,不支持xls格式

. Workbook类

Workbook类定义:Workbook(filename[,options]) 该类可以实现创建一个XlsxWriterWorbook对象。Workbook类表示整个电子表格文件。

  • 参数filenameString类型)为创建的Excel文件存储路径;
  • 参数optionsDict类型)为可选的Workbook参数,作为初始化表格内容;  例如:{'strings_to_numbers' : True}表示使用worksheet.write()方法时激活字符串转换数字。

add_Worksheet([sheetname])方法

添加一个新的工作表,参数sheetname(String类型)为可选参数,默认为sheet1;比如说按照表单顺序该工作表为第五张,则默认名为sheet5。

# -*- coding: utf-8 -*-  
import xlsxwriter 
workbook = xlsxwriter.Workbook('Demo2.xlsx') # 创建一个名为Dome2.xlsx的表格 
worksheet1 = workbook.add_worksheet() # 添加第一个表单,默认为sheet1 
worksheet2 = workbook.add_worksheet('Files') # 添加一个名为File的表单 
worksheet3 = workbook.add_worksheet() # 添加一个表单,默认为sheet3 
workbook.close()

add_format([properties])方法

在工作表中创建一个新的格式对象来格式化单元格。参数propertiesDict类型)为指定一个格式属性的字典。

bold = workbook.add_format() 
bold.set_bold() # 设置为加粗

add_chart(options)方法

在工作表中创建一个图表对象,内部通过insert_chart()方法来实现,参数options(dict类型)为图标指定一个字典属性。

# 设置线条类型的图表对象
chart = workbook.add_chart({'type' : 'line'}) 

close()方法

# 关闭工作表文件
workbook.close() 

. Worksheet类

Worksheet代表了一个Excel工作表,是XlsxWriter模块操作Excel内容最核心的一个类,例如将数据写入到单元格或者工作表格式布局等。  Workbook对象通过add_worksheet()方法来创建。

write(row, col, *args)方法

其作用是将普通数据写入到工作表单元格。  参数row为行标,col为列标,坐标检索起始值为0;  *args无名字参数为数据内容,可以是数字、公式、字符串或格式对象。  write方法已经作为其它更加具体数据类型方法的别名。如以下几种:

  • write_string(),写入字符串类型数据;
  • write_number(),写入数据类型数据;
  • write_blank(),写入空类型数据;
  • write_formula(),写入公式型数据;
  • write_datetime(),写入日期类型数据;
  • wrtie_boolean(),写入逻辑类型数据;
  • write_url(),写入超链接类型数据。
import xlsxwriter 
workbook = xlsxwriter.Workbook('Demo2.xlsx') # 创建一个名为Dome2.xlsx的表格 
worksheet1 = workbook.add_worksheet() # 添加第一个表单,默认为sheet1 
worksheet2 = workbook.add_worksheet('Files') # 添加一个名为File的表单 
worksheet3 = workbook.add_worksheet() # 添加一个表单,默认为sheet3 
worksheet1.write(0, 0, 'Hello') # write_string() 
worksheet1.write(1, 0, 1.23) # write_number() 
worksheet1.write(2, 0, '') # write_blank() 
worksheet1.write(3, 0, None) # write_blank() 
worksheet1.write(4, 0, '=SIN(PI()/4)') # write_formula() 
workbook.close()

set_row(row, height, cell_format, options)方法

其作用是设置行单元格属性。 

  • rowint类型),指定行位置,起始下标为0; 
  • heightfloat类型),设置行高,单位为像素; 
  • cell_formatformat类型)指定格式对象; 
  • optionsdict类型)设置行hidden(隐藏)、level(组合分级)、collapsed(折叠)。
import xlsxwriter 
# 创建一个名为Dome2.xlsx的表格
workbook = xlsxwriter.Workbook('Demo2.xlsx')  
# 添加第一个表单,默认为sheet1 
worksheet1 = workbook.add_worksheet() 
# 在单元格A1写入‘Hello’字符串 
worksheet1.write('A1', 'Hello') 
# 定一个加粗的格式对象 
cell_format = workbook.add_format({'bold': True}) 
# 第一行单元格高度为40px,且引用加粗格式对象 
worksheet1.set_row(1, None, None, {'hidden': True}) 
# 隐藏第2行单元格 
worksheet1.set_row(0, 40, cell_format) workbook.close()

set_column(first_col, last_col, cell_format, options)方法

同上一个方法类似,这个方法是用于设置一列或多列单元格属性。

insert_image(row, col, image[,options])方法

其作用是插入图片到指定单元格,支持PNG\JPG\BMP等图片格式 

  • row为行坐标,col为列坐标,坐标索引起始位置为0; 
  • imagestring类型)为图片路径; 
  • optionsdict类型)为可选参数,指定图片的位置、比例、链接url等信息。
import xlsxwriter 
workbook = xlsxwriter.Workbook('Demo2.xlsx') # 创建一个名为Dome2.xlsx的表格 
worksheet1 = workbook.add_worksheet() # 添加第一个表单,默认为sheet1 
worksheet1.insert_image(2, 2, 'blog.png') # 在第三行第三列插入一张图片 
workbook.close()

. 添加图表示例

#!/usr/bin/python
#coding: utf-8
import xlsxwriter
workbook = xlsxwriter.Workbook('chart.xlsx')
worksheet = workbook.add_worksheet()
#新建图标对象
chart = workbook.add_chart({'type': 'column'})
#向 excel 中写入数据,建立图标时要用到
data = [
    [1, 2, 3, 4, 5],
    [2, 4, 6, 8, 10],
    [3, 6, 9, 12, 15],
]
worksheet.write_column('A1', data[0])
worksheet.write_column('B1', data[1])
worksheet.write_column('C1', data[2])
#向图表中添加数据,例如第一行为:将A1~A5的数据转化为图表
chart.add_series({'values': '=Sheet1!$A$1:$A$5'})
chart.add_series({'values': '=Sheet1!$B$1:$B$5'})
chart.add_series({'values': '=Sheet1!$C$1:$C$5'})
#将图标插入表单中
worksheet.insert_chart('A7', chart)
workbook.close()

图表

原文发布于微信公众号 - python爬虫实战之路(gh_f26a568fdea8)

原文发表时间:2018-07-13

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏一“技”之长

Java开发GUI之列表 原

    awt包中的List控件可以创建一个选择列表,此列表可以支持单选,也可以支持多选。

3352
来自专栏Google Dart

AngularDart4.0 指南- 模板语法二 顶

Class绑定语法类似于属性(property)绑定。 以前缀类开始,可选地跟一个点(.)和一个CSS类的名字替代括号内的元素属性:[class.class-n...

1252
来自专栏河湾欢儿的专栏

less

less的编译有两大类 第一类:基于node环境编译less 第二类:基于浏览器环境 第一类

862
来自专栏禹都一只猫博客

Go语言简介 — 特性

1271
来自专栏c#开发者

selenum参考手册中文翻译

Added by SpringSideTeam, last edited by SpringSideTeam on 2006-11-23  (view chan...

2876
来自专栏小古哥的博客园

HTML5复习整理

一、推出的目标 web浏览器兼容性低;文档结构不明确;web应用程序的功能受限 二、语法的改变 内容类型(html或htm);DOCTYPE声明简化;指定字符编...

3787
来自专栏WindCoder

Angular6自定义表单控件方式集成Editormd

曾经找到过“Editor.md”,看之心喜,一直想在Angular中集成下这款markdownpad编辑器玩,在网上也只找到一篇通过指令集成的,虽然可以实现,但...

5232
来自专栏企鹅号快讯

不到200行 JavaScript 代码如何实现富文本编辑器

作者:吴锴 http://www.wukai.me/2017/12/31/rich-text-editor-in-less-than-200-lines-jav...

2257
来自专栏静晴轩

你所不知道的setTimeout

JavaScript提供定时执行代码的功能,叫做定时器(timer),主要由setTimeout()和setInterval()这两个函数来完成。它们向任务队列...

47612
来自专栏破晓之歌

vue2.5入门(推荐,差代码) 原

1012

扫码关注云+社区

领取腾讯云代金券