Aillieo Collection

SUBTITLE

0%

使用Python处理Excel文档

发表于 更新于 分类于

使用Python处理微软Excel的xls文件，主要涉及到3个Python包或模块：xlrd、xlwt和xlutils。

xlrd xlwt 和 xlutils

  • xlrd

用于读取xls文件中的内容，不可修改文件中的内容。读取xls文件得到一个Book对象。

  • xlwt

用于向xls文件中写入内容，无法获取xls中的内容。生成的xls文件是一个Workbook对象。

  • xlutils

可将xlrd.Book的数据复制到xlwt.Workbook，以实现对读取的xls文件进行编辑的目的。

以上可以使用pip来完成安装：

1
2
3
pip install xlrd
pip install xlwt
pip install xlutils

简单示例

先看一个简单示例，生成一个test.xls文件，然后读取该文件。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# -*- coding: utf-8 -*-

import xlrd
import xlwt
from xlutils.copy import copy

# xlwt
workbook = xlwt.Workbook(encoding="utf-8")
sheet = workbook.add_sheet("Sheet1", cell_overwrite_ok=True)
style = xlwt.easyxf(
    "font: name Arial;"
    "pattern: pattern solid, fore_colour red;"
)
sheet.write(0, 0, "writen by python", style)
sheet.row(0).set_style(style)
workbook.save("test.xls")

# xlrd
book = xlrd.open_workbook("test.xls", formatting_info=True, on_demand=True)
sheet = book.sheet_by_index(0)
cell = sheet.cell(0, 0)
print(cell.value)

# xlutils
workbook = copy(book)

主要的API

xlrd

主要涉及到3个类，Book、Sheet、Cell，分别对应xls文件（工作簿）、工作表、单元格。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
# 加载xls文件，返回一个Book对象
book = open_workbook(
    filename=None,   # 要加载的文件
    logfile=sys.stdout,   # 指定输出log的文件
    verbosity=0,   # 对应输出log的内容的量
    pickleable=True,   # Python2.4及之前的版本中将pickleable置为false会使用array以节省内存
    use_mmap=USE_MMAP,   # 是否启用mmap
    file_contents=None,   # 不使用指定文件名filename的方式而是直接根据内容file_contents加载
    encoding_override=None,   # 指定解码方式
    formatting_info=False    # 加载表内各单元格的格式信息，如果选择加载则会同时加载底部空的行及右侧空的列中的单元格
)

# ---------------------------------

# 获取名为book的Book对象的内容：
# book中工作表sheets的数量
book.nsheets
# 获取所有sheets的列表
book.sheets
# book内所有sheet名称列表
book.sheet_names
# 根据索引号获取一个Sheet对象
book.sheet_by_index(sheetx)
# 根据名称获取一个sheet对象
book.sheet_by_name(sheet_name)

# ---------------------------------

# 获取名为sheet的Sheet对象的内容

# 工作表sheet的名称
sheet.name
# 表内包含的行数
sheet.nrows
# 表内包含的列数
sheet.ncols

# 获取由第colx列单元格组成的列表，可以指定起止位置
sheet.col_slice(colx,start_rowx=0,end_rowx=None)
# 获取由第colx列单元格的值类型组成的列表，可以指定起止位置
sheet.col_types(colx,start_rowx=0,end_rowx=None)
# 获取由第colx列单元格的值组成的列表，可以指定起止位置
sheet.col_values(colx,start_rowx=0,end_rowx=None)

# 获取由第rowx列单元格组成的列表，可以指定起止位置
sheet.row_slice(rowx,start_colx=0,end_colx=None)
# 获取由第rowx列单元格的值类型组成的列表，可以指定起止位置
sheet.row_types(rowx,start_colx=0,end_colx=None)
# 获取由第rowx列单元格的值组成的列表，可以指定起止位置
sheet.row_values(rowx,start_colx=0,end_colx=None)

# 获取位于第rows行colx列的单元格
sheet.cell(rowx,colx)
# 获取位于第rows行colx列的单元格值类型
sheet.cell_type(rowx,colx)
# 获取位于第rows行colx列的单元格的值
sheet.cell_value(rows,colx)

# ---------------------------------

# 获取名为cell的单元格Cell的内容
# 单元格的值的类型 是一个枚举类型
cell.ctype
# 单元格的值
cell.value
# ctype可以取的值及含义
# 0 XL_CELL_EMPTY 空字符串 u''
# 1 XL_CELL_TEXT 表示Unicode string
# 2 XL_CELL_NUMBER 浮点数据	float
# 3 XL_CELL_DATE 浮点数据 float
# 4 XL_CELL_BOOLEAN 整型 1表示TRUE， 0表示FALSE
# 5 XL_CELL_ERROR 整形 excel内部的错误编号
# 6 XL_CELL_BLANK 空字符串 u'' 只有在open_workbook时传入参数formatting_info=True时才会出现

xlwt

主要涉及到3个类：Workbook对应工作簿文件，Worksheet对应工作表，XFStyle对象用于控制单元格格式（XF record）。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 创建Workbook对象
xlwt.Workbook.Workbook(encoding='ascii', style_compression=0)
# style_compression表示是否对格式进行压缩 默认为0不压缩 =1表示压缩字体信息 =2表示压缩字体和XF record

# ---------------------------------

# 对于名为workbook的Workbook对象 可以有以下操作
# 添加工作表 并返回添加的工作表
workbook.add_sheet(sheet_name, cell_overwrite_ok=False)
# 获取指定名称的工作表
workbook.get_sheet(Sheet_name)
# 保存xls文件
workbook.save(file_name)

# ---------------------------------

# 对于名为worksheet的Worksheet对象 有以下操作
# 写入内容指定单元格的内容与格式
worksheet.write(rowx,colx,cell_value,style)
worksheet.row(rowx).write(colx,cell_value,style)
worksheet.col(colx).write(rows,cell_value,style)
# 将完成编辑的行flush，flush之后的行不可再编辑
worksheet.flush_row_data()

XFStyle用于指定单元格内容格式，使用easyxf函数来得到一个XFStyle对象。

1
xlwt.Style.easyxf(strg_to_parse='', num_format_str=None, field_sep=', ', line_sep=';', intro_sep=':', esc_char='\\', debug=False)

strg_to_parse是定义了格式的字符串，可以控制的格式属性包括字体（font）、对齐方式（align）、边框形式（border）、颜色样式（pattern）和单元格保护（protection）等，具体的格式属性在文章末尾详细列出。

字符串strg_to_parse语法格式如下：

1
(<element>:(<attribute> <value>,)+;)+

例如

1
'font: bold on; align: wrap on, vert centre, horiz center'  # 字体加粗 对齐方式 允许换行 垂直居中 水平居中

参数字符串num_format_str用于指定数字的格式，例如：

1
2
"#,##0.00"
"dd/mm/yyyy"

以下是xlwt.Style.easyxf的一些用例：

1
2
3
4
5
6
style1 = easyxf('font: name Times New Roman')
style2 = easyxf('font: underline single')
style3 = easyxf('border: left thick, top thick')
style4 = easyxf('pattern: pattern solid, fore_colour red;')
style5 = xlwt.easyxf(num_format_str='yyyy-mm-dd hh:mm:ss')
style6 = xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00')

xlutils

依赖xlrd和 xlwt，主要包含以下模块：

  • xlutils.copy
    可将xlrd.Book对象复制到xlwt.Workbook对象
  • xlutils.display
    更好友好更安全地显示xlrd相关对象的信息
  • xlutils.filter
    用于分割和过滤现有Excel文件到新Excel文件的小型框架
  • xlutils.margins
    获取Excel文件中包含有多少有用信息
  • xlutils.save
    将xlrd.Book对象序列化为Excel文件的工具
  • xlutils.styles
    用于处于Excel文件中格式信息的工具
  • xlutils.view
    使用workbook中工作表的视图信息

主要介绍两个函数的使用，第一个xlutils.copy.copy(wb)：

xlutils.copy.copy(wb)将一个xlrd.Book对象中的内容复制到一个xlwt.Workbook对象，并尽量多的保留其中的信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
from os.path import join
from xlrd import open_workbook
from xlutils.copy import copy

test_files = ''
# 使用xlrd打开文件testall.xls，并显示前两个单元格的内容，此时rb是只读模式
rb = open_workbook(join(test_files,'testall.xls'), formatting_info=True, on_demand=True)
print(rb.sheet_by_index(0).cell(0, 0).value)
print(rb.sheet_by_index(0).cell(0, 1).value)

# 使用xlutils.copy，将xlrd.Book中的内容复制到xlwt.Workbook
wb = copy(rb)

# 修改wb的第一个单元格的内容
wb.get_sheet(0).write(0,0,'changed!')
wb.save(join(temp_dir.path,'output.xls'))

# 再次加载刚才的文件 发现内容被修改了
rb = open_workbook(join(temp_dir.path,'output.xls'))
print(rb.sheet_by_index(0).cell(0, 0).value)
print(rb.sheet_by_index(0).cell(0, 1).value)

另一个是xlutils.filter中的函数xlutils.filter.process(reader, *chain)：

模块xlutils.filter中包含有一些内置的模块reader、writer和filter，以及用于将它们串联起来的函数process() ，主要功能是过滤和分割Excel文件。

reader用于从数据源中获取数据，并将其转化为一系列xlrd.Book对象，然后会调用第一个filter相关的方法。模块内提供有一些基础的reader类。
filter用户获取特定任务需要的结果，在filter中必须要定义一些特定的方法，这些方法的实现中可以根据需要填写任意功能，但通常会以调用下一个filter的对应方法作为结束。
writer会处理参数链中最后一个filter中特定的方法。writer通常用于从数据源复制信息并将其写入输出文件。由于在writer中涉及到很多工作而通常只有向目标位置写入二进制数据会略有不同，模块内也提供了一些基础的writer类。
process(reader, *chain)可以将内置或自定义的reader、writer和 filter串联起来执行。
接下来是一个完整的示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
import os
from xlutils.filter import BaseReader
from xlutils.filter import BaseWriter
from xlutils.filter import BaseFilter
from xlutils.filter import process

test_files = ""


class MyReader(BaseReader):

    def __init__(self, filename):

        self.filename = filename

    def get_filepaths(self):

        return os.path.join(test_files, self.filename)


class MyFilter:

    def __init__(self, name):
        self.name = name

    def start(self):
        print(self.name, 'start')
        self.next.start()

    def workbook(self, rdbook, wtbook_name):
        print(self.name, 'workbook', rdbook, wtbook_name)
        self.next.workbook(rdbook, wtbook_name)

    def sheet(self, rdsheet, wtsheet_name):
        print(self.name, 'sheet', rdsheet, wtsheet_name)
        self.next.sheet(rdsheet, wtsheet_name)

    def set_rdsheet(self, rdsheet):
        print(self.name, 'set_rdsheet', rdsheet)
        self.next.sheet(rdsheet, wtsheet_name)

    def row(self, rdrowx, wtrowx):
        print(self.name, 'row', rdrowx, wtrowx)
        self.next.row(rdrowx, wtrowx)

    def cell(self, rdrowx, rdcolx, wtrowx, wtcolx):
        print(self.name, 'cell', rdrowx, rdcolx, wtrowx, wtcolx)
        self.next.cell(rdrowx, rdcolx, wtrowx, wtcolx)

    def finish(self):
        print(self.name, 'finish')
        self.next.finish()


class EvenFilter(BaseFilter):
    def row(self, rdrowx, wtrowx):
        if not rdrowx % 2:
            self.next.row(rdrowx, wtrowx)

    def cell(self, rdrowx, rdcolx, wtrowx, wtcolx):
        if not rdrowx % 2:
            self.next.cell(rdrowx, rdcolx, wtrowx, wtcolx)


class MyWriter(BaseWriter):

    def get_stream(self, filename):
        return open(os.path.join(test_files, filename), 'wb')


process(
    MyReader({"test1.xls","test2.xls"}),
    MyFilter('before filter'),
    EvenFilter(),
    MyFilter('after filter'),
    MyWriter()
)

XFStyle格式

格式属性

  • font
    • bold
      布尔值，默认为False
    • charset
      可选值见下节，默认值是sys_default
    • colour （或color_index、 colour_index、 color ）
      可选值见下节，默认值是automatic
    • escapement
      可选值为none、superscript或 subscript，默认值none
    • family
      包含字体的font family的字符串，默认值None
    • height
      使用20乘以point size得到的高度值，默认是200，对应10pt
    • italic
      布尔值，默认为False
    • name
      包含字体名称的字符串，默认为Arial
    • outline
      布尔值，默认为False
    • shadow
      布尔值，默认为False
    • struck_out
      布尔值，默认为False
    • underline
      布尔值或者none、single、single_acc、 double、double_acc其中之一。默认值是none
  • alignment（或align）
    • direction（或dire）
      general、lr、rl之一，默认值general
    • horizontal（或horiz、horz）
      One of the following:
      general、left、center|centre、right、filled、justified、center|centre_across_selection、distributed其中之一，默认值是general
    • indent（或inde）
      缩进值0到15，默认值0
    • rotation（或rota）
      -90到+90之间的整数值或stacked、none之一，默认值是none
    • shrink_to_fit（或shri、shrink）
      布尔值，默认为False
    • vertical（或vert）
      top、center|centre、bottom、justified、distributed其中之一，默认为bottom
    • wrap
      布尔值，默认为False
  • borders（或border）
    • left
      边框样式，详见下节
    • right
      边框样式，详见下节
    • top
      边框样式，详见下节
    • bottom
      边框样式，详见下节
    • diag
      边框样式，详见下节
    • left_colour（或left_color）
      颜色值，详见下节，默认为automatic
    • right_colour（或right_color）
      颜色值，详见下节，默认为automatic
    • top_colour（或top_color）
      颜色值，详见下节，默认为automatic
    • bottom_colour（或bottom_color）
      颜色值，详见下节，默认为automatic
    • diag_colour（或diag_color）
      颜色值，详见下节，默认为automatic
    • need_diag_1
      布尔值，默认为False
    • need_diag_2
      布尔值，默认为False

  • pattern

    • back_colour（或back_color、pattern_back_colour、pattern_back_color）
      颜色值，详见下节，默认为automatic

    • fore_colour（或fore_color、pattern_fore_colour、pattern_fore_color）
      颜色值，详见下节，默认为automatic

    • pattern

      no_fill、none、solid、solid_fill、solid_pattern、fine_dots、alt_bars、sparse_dots、thick_horz_bands、thick_vert_bands、thick_backward_diag、thick_forward_diag、big_spots、bricks、thin_horz_bands、thin_vert_bands、thin_backward_diag、thin_forward_diag、squares、diamonds其中之一，默认为none

  • protection
    • cell_locked
      布尔值，默认为True
    • formula_hidden
      布尔值，默认为False

取值说明

布尔型

True可以表示为1、 yes、 true 或 on；

False可以表示为0、 no、 false或 off。

charset

字符集的可选值如下：

1
2
3
4
5
6
ansi_latin, sys_default, symbol, apple_roman,
ansi_jap_shift_jis, ansi_kor_hangul, ansi_kor_johab,
ansi_chinese_gbk, ansi_chinese_big5, ansi_greek,
ansi_turkish, ansi_vietnamese, ansi_hebrew,
ansi_arabic, ansi_baltic, ansi_cyrillic, ansi_thai,
ansi_latin_ii, oem_latin_i

color

颜色可选值如下：

aqua dark_red_ega light_blue plum
black dark_teal light_green purple_ega
blue dark_yellow light_orange red
blue_gray gold light_turquoise rose
bright_green gray_ega light_yellow sea_green
brown gray25 lime silver_ega
coral gray40 magenta_ega sky_blue
cyan_ega gray50 ocean_blue tan
dark_blue gray80 olive_ega teal
dark_blue_ega green olive_green teal_ega
dark_green ice_blue orange turquoise
dark_green_ega indigo pale_blue violet
dark_purple ivory periwinkle white
dark_red lavender pink yellow

borderline

可以是0到13的整数值，或者以下值其中之一：

1
2
3
4
no_line, thin, medium, dashed, dotted, thick, double, hair,
medium_dashed, thin_dash_dotted, medium_dash_dotted,
thin_dash_dot_dotted, medium_dash_dot_dotted,
slanted_medium_dash_dotted

REFERENCE

https://github.com/python-excel
http://xlrd.readthedocs.io/en/latest/
http://xlwt.readthedocs.io/en/latest/api.html
http://xlutils.readthedocs.io/en/latest/