openpyxl读取xlsx_openpyxl does not support the old .xls file format

在做游戏的时候，经常会使用到execel来做静态配置表。之前我们使用读取xls文件的时候都是使用的xlrd库。但是这个库对于后来的xlsx的版本支持不算太好：
比如说：当你使用xlrd来加载xlsx文件的时候，在代码中加入了
xlrd.open_workbook(filePath,formatting_info=True)
formatting_info=True
马上会得到这个报错提示：

Traceback (most recent call last):
  File "xxxxxxxx\test_read_excel_color.py", line 7, in <module>
    xlrd.open_workbook(r'./xxxxx.xlsx',formatting_info=True)
  File "C:\Python27\lib\site-packages\xlrd\__init__.py", line 422, in open_workbook
    ragged_rows=ragged_rows,
  File "C:\Python27\lib\site-packages\xlrd\xlsx.py", line 751, in open_workbook_2007_xml
    raise NotImplementedError("formatting_info=True not yet implemented")
NotImplementedError: formatting_info=True not yet implemented
1
2
3
4
5
6
7
8

新版本的xlsx的格式还没有实现。
官网中formatting_info的解释是：

> formatting_info –
The default is False, which saves memory. In this case, “Blank” cells, which are those with their own formatting information but no data, are treated as empty by ignoring the file’s BLANK and MULBLANK records. This cuts off any bottom or right “margin” of rows of empty or blank cells. Only cell_value() and cell_type() are available.
1
2

这个option使用与节约内存的。在这个情况下，空的单元格，存在格式信息但是没有数据，将会被当成空来对待。这将会裁剪掉任何底部，右边的“边缘”空的表格。只有cell_value()和cell_type是有效的。
实际上在当关闭了这个option之后，当程序需要去加载cell中的颜色代码的时候将会存在下面的问题。

Traceback (most recent call last):
  File "xxxxx\test_read_execel_color1.py", line 10, in <module>
    xf_idx  = xws1.cell_xf_index(0,0)
  File "C:\Python27\lib\site-packages\xlrd\sheet.py", line 420, in cell_xf_index
    self.req_fmt_info()
  File "C:\Python27\lib\site-packages\xlrd\sheet.py", line 1664, in req_fmt_info
    raise XLRDError("Feature requires open_workbook(..., formatting_info=True)")
XLRDError: Feature requires open_workbook(..., formatting_info=True)
1
2
3
4
5
6
7
8

还不知道里面还存在一些啥其他的问题不。关闭了这个option之后，有些xlrd的代码就不能这么写了。还是很不爽。

后来去查了一下现在大概大家都会开始使用openpyxl库来读取配置表了。记录一下当前掌握的一些基本用法。
在他主页上直接这么介绍：
A Python library to read/write Excel 2010 xlsx/xlsm files
在下面的文章中我们将会对比的来这两者的代码编写。

加载execel

openpyxl

from openpyxl import load_workbook
wb=load_workbook(filename = r'./mx_svr_list.xlsx', data_only=True)
ws1=wb.get_sheet_by_name("Sheet1")
# data_only 防止读取到的内容是公式，而不是最终值
1
2
3
4

xlrd

xwb=xlrd.open_workbook(r'./mx_svr_list.xls',formatting_info=True)
xws1=xwb.sheet_by_index(0)
1
2

读取Sheet中的内容

openpyxl

print(ws1.cell(row=1,column=1).value)
    for sheet_name in wb.get_named_ranges():
        sheet=wb.get_sheet_by_name(sheet_name)
        for i in range(1,sheet.max_row+1):
            print(sheet.cell(row=i,column=1).value)

# 直接通过栏读取内容
for i in range(4,ws1.max_row+1):
    AssetID=ws1['A{}'.format(i)].value
    From=ws1['B{}'.format(i)].value
    To=ws1['C{}'.format(i)].value
    Name=ws1['G{}'.format(i)].value
    Rarity=ws1['M{}'.format(i)].value
    ShowName=ws1['N{}'.format(i)].value
    Description=ws1['P{}'.format(i)].value
    Image=ws1['S{}'.format(i)].value
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

xlrd

    xwb=xlrd.open_workbook(filename = IP_CONFIG_FILE,formatting_info=True)
    sheet=xwb.sheet_by_index(0)
    for i in range(0,sheet.nrows):
        value=sheet.cell_value(i,0)
1
2
3
4

注意openpyxl的下标是从1开始的，xlrd的下标从0开始。

读取cell中的颜色

openpyxl

c = ws1.cell(row=1,column=1)
fill = c.fill
front = c.font
print(c.value)
print(fill.start_color.rgb)
print(front.color.rgb)
服务器名称
FF00B050
FFFFFF00
1
2
3
4
5
6
7
8
9

这里他的排列次序是：
[alpha通道:R:G:B]
这里可以将单元格里面的字体颜色和背景颜色都打印出来。
对于openpyxl中的api介绍大家可以查阅这个文档

xlrd

xf_idx  = xws1.cell_xf_index(0,0)
xf_list = xwb.xf_list[xf_idx]
color   = xwb.colour_map[xf_list.background.pattern_colour_index]
print(color)
f = xwb.font_list[xf_list.font_index]
color = xwb.colour_map[f.colour_index]
print(color)
服务器名称
(0, 128, 0)
(255, 255, 0)
1
2
3
4
5
6
7
8
9
10

具体xlrd的api方法介绍可以查阅这里
对比一下看这两套库的实现，openpyxl实现的要更加友好一些。他将最后的结果封装成了类。而且这个格式里面是支持alpha通道的。而xlrd库使用就比较繁琐了。他需要先到sheet里面读取到这个cell的xformatting的信息，然后到book对象中的xf_list中读取这个对象出来。字体的信息还需要通过xformatting中font_index到book中的font_list中抓取出font对象，然后在book的colour_map中定位到这个RGB信息，而且这个颜色中是没有alpha通道信息。xlrd条理还是算清晰。只是说操作起来比较繁琐。
有个忧伤的故事，就是openpyxl是不支持xls版本的。

Traceback (most recent call last):
  File "F:\tmp\xls_read\MyTest.py", line 4, in <module>
    wb = load_workbook('./gonghuiMap.xls')
  File "C:\Python27\lib\site-packages\openpyxl\reader\excel.py", line 151, in load_workbook
    archive = _validate_archive(filename)
  File "C:\Python27\lib\site-packages\openpyxl\reader\excel.py", line 105, in _validate_archive
    raise InvalidFileException(msg)
InvalidFileException: openpyxl does not support the old .xls file format, please use xlrd to read this file, or convert it to the more recent .xlsx file format.
1
2
3
4
5
6
7
8