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

Excel数据的类型及组织方式

每一个Excel数据文件从上至下分为三个层级的对象:

workbook: 每一个Excel文件就是一个workbook。

sheet: 每一个workbook中可以包含多个sheet,具体就对应Excel中我们在左下脚所看到的“sheet1”,“sheet2”等。

cell: 每一个sheet就是我们通常所看到的一个表格,可以含有m行,n列,每个确定的行号,列号所对应的一个格子就是一个cell。

Excel模块对比

平台:Mac

工具:PyCharm

Python:python3.6

Excel行数:64543

从一个既有的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卸载已加载表单。

打开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对象实例列表。

打开所需的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):获取列内容。

获取对应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获取类型判断。

日期类型可以使用xldate_as_tuple模块处理:

openpyxl

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

.一个简单创建例子

通过得到worksheet.

注意:

该方法使用_active_sheet_index属性, 默认会设置0,也就是第一个worksheet。除非手动修改,否则使用active方法得到都是第一个worksheet。

你也可以创建worksheets,通过 方法:

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

ws.title = "New Title"

默认的sheet的tab是白色的,可以通过 RRGGBB颜色来修改sheet_properties.tabColor属性从而修改sheet tab按钮的颜色:

当你设置了sheet的名称,可以将其看成workbook中的一个key。

工作表有两种获取方式

--推荐

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

遍历worksheets:

.操作数据

单元格可以看作是worksheet的key,通过key去访问和设置单元格中的数据

value = ws["A4"]

ws["A4"] = data

注意:

当worksheet在内存中被创建时,是没有包含cells的,cells是在首次访问时创建.

可以循环在内存中创建cells,这时不指定他们的值也会创建该cells些:(创建100x100cells)

通过切片Ranges指定许多cells

cell_range = ws['A1': 'C2']

同样也可以Ranges行或者列:

colC = ws['C']

col_range = ws['C:D']

rwo10 = ws[10]

rwo_range = ws[5:10]

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

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

最简单最安全的方法保存workbook是使用对象的 方法:

wb = Workbook()

wb.save('balances.xlsx')

保存的默认位置在python的根目录下。

注意:会自动覆盖已经存在文件名的文件。

我们可以导入一个文件进行读或者写,像写一样我们可以导入已经存在的workbook:

append函数

可以一次添加多行数据,从第一行空白行开始(下面都是空白行)写入

如果需要整列进行添加数据:

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

写入例子:

使用公式

.设置单元格风格--Style

先导入需要的类

分别可指定字体相关,颜色,和对齐方式。

字体

上面的代码指定了等线24号加粗斜体,字体颜色红色。直接使用cell的属性,将Font对象赋值给它。

对齐方式

也是直接使用cell的属性,这里指定垂直居中和水平居中。除了center,还可以使用等等参数。

设置行高和列宽

有时候数据太长显示不完,就需要拉长拉高单元格。

合并和拆分单元格

所谓合并单元格,即以合并区域的左上角的那个单元格为基准,覆盖其他单元格使之称为一个大的单元格。

相反,拆分单元格后将这个大单元格的值返回到原来的左上角位置。

合并后只可以往左上角写入数据,也就是区间中左边的坐标。

如果这些要合并的单元格都有数据,只会保留左上角的数据,其他则丢弃。换句话说若合并前不是在左上角写入数据,合并后单元格中不会有数据。

以下是拆分单元格的代码。拆分后,值回到A1位置。

xlsxwriter

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

.Workbook类

定义:

该类可以实现创建一个的对象。类表示整个电子表格文件。

参数(类型)为创建的文件存储路径;

参数(类型)为可选的参数,作为初始化表格内容;

例如:表示使用方法时激活字符串转换数字。

add_Worksheet([sheetname])方法

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

add_format([properties])方法

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

add_chart(options)方法

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

close()方法

.Worksheet类

代表了一个工作表,是模块操作内容最核心的一个类,例如将数据写入到单元格或者工作表格式布局等。

对象通过方法来创建。

write(row, col, *args)方法

其作用是将普通数据写入到工作表单元格。

参数为行标,为列标,坐标检索起始值为;

无名字参数为数据内容,可以是数字、公式、字符串或格式对象。

write方法已经作为其它更加具体数据类型方法的别名。如以下几种:

,写入字符串类型数据;

,写入数据类型数据;

,写入空类型数据;

,写入公式型数据;

,写入日期类型数据;

,写入逻辑类型数据;

,写入超链接类型数据。

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

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

(类型),指定行位置,起始下标为;

(类型),设置行高,单位为像素;

(类型)指定格式对象;

(类型)设置行(隐藏)、(组合分级)、(折叠)。

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

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

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

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

为行坐标,为列坐标,坐标索引起始位置为;

(类型)为图片路径;

(类型)为可选参数,指定图片的位置、比例、链接等信息。

.添加图表示例

图表

微信公众号二维码:

公众号

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20180713G0QEWC00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券