使用Python给带合并单元格的Excel加一列

文章目录[显示]

前几天有这么一个需求，要给Excel表格的最前面加入两列。我想这不是非常简单吗？几行代码而已：

import openpyxl

wb = openpyxl.load_workbook('simple.xlsx')
sh = wb.active
sh.insert_cols(1)
wb.save('result.xlsx')

非常简单，自己还找了个表格试了下，好像没问题。

然后我发现我要处理的表格是带有合并单元格的，用这几行代码处理下发现……

我的单元格都乱套了……

这可咋整……不管了，掏出了openpyxl

openpyxl

处理xlsx合并单元格插入列

其实处理思路很简单，遍历每一个单元格，给纵坐标+2，然后样式什么的也拷贝一份写到新的表格里就好了。但是一片不大不小的水域，总是有那么不多不少的水坑。

如何判断合并单元格

在openpyxl中，合并单元格是以ws.merged_cells这个列表表示的，这个列表里面的形式如下：I1:I2 D1:E1 A1:A2 B1:C1 F1:H1，每一对用冒号连接的坐标表示这两个之间是合并的。

这样问题就简单啦，我们只需要把坐标+2就好了，比如说A1:B2变C1:E2，K1:J5变M1:L1，Z1:AA1变AB1:AC1，等等什么？Z1:AA1变AB1:AC1？没办法我这个Excel有点长……

这个不是普通的加法啊，通过ord()和chr()来回转换吧？进坑了吧少年?

二十六进制？？

我们了解到，Excel的坐标变化是字母表的进位，也就是26进制！这……二进制，八进制，十六进制都是咱比较常用的，这二十六进制是什么鬼？来我们if…elif…elif….elif写一波……

def twenty_six(coord, size):
# A1:B2, AB1:AC2
    left = coord.split(':')[0]
    right = coord.split(':')[-1]
    left_char = left[0]
    right_char = right[0]

    if len(left_char) == 1 and len(right_char) == 1:
        return "%s:%s" % (chr(ord(left_char + size)), chr(ord(right_char + size)))
    elif len(left_char) == 2 and len(right_char) == 2:
        return "%s:%s" % (chr(ord(left_char + size)), chr(ord(right_char + size)))

哎？那要坐标是A1:AZ3的呢？不用想了这段代码是错的⛲

那么我们来创造人类史上的第一个26进制吧！小伙子，又进坑了吧……?

要什么26进制啊，直接弄个A, B, C, D….AA, AB, AC….BC, BD….ZZ一共26*26个元素的列表，通过index查得了。

所以所谓的“26进制”咱可以这么处理一波：

import itertools
import string

table = list(itertools.chain(string.ascii_uppercase,(''.join(pair) for pair in itertools.product(string.ascii_uppercase, repeat=2))))

def twenty_six(char, size):
    index = table.index(char)
    return table[index + size]

其中char是表示字母（不带数字的那种）

喜欢挑刺的童鞋们可能会说，你这是列表，列表查找可能得全都遍历完了才查找到，你这太浪费时间了。得用字典。

喂(#`O′)童鞋，如果用字典的话，那么需要不仅能从键（字母）找到值，还得需要通过值找到键，这……行行行，没啥不可能的，键值对反转嘛，面试必会题目。那么用字典，table大概就是这么构造：

使用dict方法

table = [dict(zip(itertools.chain(string.ascii_uppercase, 
(''.join(pair) for pair in itertools.product(string.ascii_uppercase, repeat=2))),
[i for i in range(26 * 26)])),
dict(zip([i for i in range(26 * 26)], itertools.chain(string.ascii_uppercase,
(''.join(pair) for pair in itertools.product(string.ascii_uppercase, repeat=2)))))]

使用字典推导(ﾉ*･ω･)ﾉ：

base = dict(zip(itertools.chain(string.ascii_uppercase,
(''.join(pair) for pair in itertools.product(string.ascii_uppercase, repeat=2))),
[i for i in range(26 * 26)]))

table = [base, {v: k for k, v in base.items()}]

table[0]表示从字母查找数字，给数字+2之后在table[1]从数字查找字母，调用就更简单了。我爱(ﾉ*･ω･)ﾉ。

然而实际上，676个元素有点少，字典的空间换时间几乎是划不来的。而且dict()和zip()方法以及遍历字典也会花费一点时间，代码还写得很长，所以简单起见生成个列表就够了。

处理字母式坐标

在解决了26进制之后，我们只需要把字母从一个合并单元格坐标中提取出来就可以了，要完成这项工作其实方法挺多的，比较简单的方法是直接用正则表达式。一搜就有。这里就不写了。

遍历单元格内容和样式

我们需要遍历每个单元格，多亏了PyCharm的Evaluate Expression功能，很容易就发现遍历单元格的方法：

我们可以很清楚的看到，i这个变量的每一个元素都包含了这个表格的全部信息，包括内容，边框，填充，字体等等。那么获取到这些信息就很简单了，一个双循环即可：

for i in ws.rows:
    for v in i:
        print(v.coordinate, v.fill, v.border, v.value)

v.coordinate表示的是单元格的字母式坐标，我们可以给上面封装好的twenty_six调用下，就能够做好新的移动之后的坐标了。

那么拷贝样式吧。

拷贝单元格样式

在openpyxl中，给单元格写入样式非常简单，大概如下就可以了：

ws['A1'].fill='Your fill style'

我们把上面遍历过来的内容直接赋值就可以了，大概如下：

for i in ws.rows:
    for v in i:
        coord = '移动之后的坐标'
        # copy fill border and everything
        write_ws[coord].fill = v.fill
        write_ws[coord].font = v.border
        write_ws[coord].border = v.border

童鞋，你又进坑了━((*′д｀)爻(′д｀*))━!!!!

尽管通过Evaluate能够看到右面的类型是左边接受的类型，但是还是报错。为什么不尝试下浅拷贝呢？嗯这是好用的。

from copy import copy把v.fill替换成copy(v.fill)就好了

给合并单元格应用样式

这个有点特殊，openpyxl官网写好了一个，咱拿过来直接用就好了。

拷贝单元格内容

单元格内容用v.value就能读到了。但是那些合并了的单元格，究竟是读取哪个坐标才能读到呢?别猜了，让我来告诉你，左上角的。其他的读取出来的都是None

所以拷贝单元格内容就太简单了，判断下就好了。

if v.value:
    self.write_ws[coord] = v.value

啪(￣ε(#￣)打飞你。如果某个单元格的内容是0，0是布尔假值啊……那岂不是丢数据了？？

小可爱，判断下，这是个特例。于是乎吭哧吭哧……

elif type(v.value) == int:
    pass

啪(。>︿<)_θ再次打飞你。难道不知道有个东西叫isinstance()嘛？后面的类型可以是元组，列表或者单个类型（类啊，int啊，float啊啥的都可以）

elif isinstance(v.value, float):
    pass

组装……

好吧，那么坑都踩的差不多了，组装组装就好了，先拷贝每个单元格，再给merge_cell拷贝过去，基本上就没啥了。

biu biu biu于是别人给你发了一个xls格式的Excel。

openpyxl不支持xls哦。

转换格式？

怕啥，openpyxl不支持读xls，我们就找个东西给转换成xlsx，再拿他处理。什么pandas啊，pyexcel啊都行，要不随手找个什么在线转换的支持REST API的咱用requests走起啊……

你看，这个?是什么，黑乎乎的我看不清，要不你跳进去看下。

不用试了，那些库把xls转换成xlsx之后，合并单元格基本丢的差不多的了。用在线转换，比如说牛逼哄哄的Google Docs？恐怕我是要请黛真知子当我的辩护律师呀……啊不，我愿意，非常愿意，真的真的那就在线转换吧。小黛请你为我唱一首歌(/≧▽≦)/

哦那个对不起，其实我家小黛唱歌好好听的。爱老婆的清唱??(*￣3￣)╭

(づ￣ 3￣)づ

xlrd xlwt xlutils

所以说到底，我们又用回来了xlrd这一系列？不过好在思路已经有了，那么就简单了。

读取合并单元格

同样的，借助PyCharm的Debug，咱很快就找到了合并单元格存于read_ws.merged_cells中。当然要遍历啦，每个元素都是一个包含了四个数字的元组。这四个数字都是啥啊……哎嘿嘿坐标呗，↖↘酱紫的呗，从0开始数数哦，前开后闭哦（哦对不起我高中数学就没及格过）。

for (rlow, rhigh, clow, chigh) in read_ws.merged_cells:
    print(rlow, rhigh, clow, chigh)

那怎么读取到合并单元格的值呢？当然还是左上角的坐标啦。那是什么呢，反正就4个数字，排列组合也就十几种，哎……好吧，是rlow和clow啦。读取的话就read_ws.cell(1,2).value就拿到了。

写入合并单元格

这个也很简单啦，write_merge()接受4个必选参数，也就是坐标，1个可选参数插入的值，另一个可选参数样式。

所以复制合并单元格就这样子：

for (rlow, rhigh, clow, chigh) in read_ws.merged_cells:
    # 2nd & 4th parameter have to -1
    write_ws.write_merge(rlow, rhigh - 1, clow + size, chigh - 1 + size, read_ws.cell(rlow, clow).value)

读取单元格样式

xlrd的样式是怎么读出来的呢？找遍了sh.cell()的返回值也没发现有什么结构是存着样式的。

猛然间发现一个sh.cell_xf_index()似乎可以拿到样式的索引的……还有个wb.xf_list，于是用索引去取，完美了。

应用单元格样式

样式已经取到了，那么赋值给关键字参数style，❌；copy一下赋值，❌……类型似乎正确啊……

一把梭一把梭，拿起来就是干，我不会编程，全都是抄StackOverflow的。

xlrd的样式不能应用给xlwt哦亲亲。(╯‵□′)╯︵┻━┻气得我当场就把桌子掀了。

w = XLWTWriter()
process(XLRDReader(wb, 'unknown.xls'), w)
w.style_list

从这个w.style_list取下标，这个值就是可以传给xlwt的关键字参数style的了✔○( ＾皿＾)っHiahiahia…

遍历并写入单元格内容和样式

xlrd的read_ws.nrows和read_ws.ncols表示了这个表格总计的行数和列数，简单粗暴双循环，同样需要注意布尔值为假的0：

for i in range(__nrows):
    for j in range(__ncols):
        # copy style and value
        if read_ws.cell(i, j).value:
            write_ws.write(i, j + size, read_ws.cell(i, j).value, style=__generate_style(i, j))
        elif isinstance(read_ws.cell(i, j).value, float):
            write_ws.write(i, j + size, read_ws.cell(i, j).value)

?是一种神奇的东西，我们每个人都见过，但是却又不知道它的本质，不知道它究竟为何。

?有的时候，可能某些单元格，它是有填充色的，但是内容为空哦。那你这……不就错了吗？

解决方案也很简单，再来个else，继续copy style但是插入空值呗……哎？有没有人想问下openpyxl就没有这个问题啊，怎么这就出现了……(*/ω＼*)那个你最好看下openpyxl我是在哪里应用的样式。

人生在世，?总是有的，说不定哪一天走在马路上就……这样做可能会抛出ValueError的异常。

这是为啥呢？其实我也不知道。try一波捕获了就好了。

那么封装成一个类

这还用说嘛，简单，把openpyxl和xlrd家族的封装到类里，__init__()接受俩参数，也就是输入和输出文件名，该用双下划线“保护”的成员变量就写上，该静态方法就静态方法，最后保留一个insert_col()接受一个size参数，一个save()保存文件就得了，实例能访问读取和写入的workbook和worksheet就足够了。

为什么要有save()呢？

因为灵活啊，万一人家想插了两列空白，顺便加点字呢？难不成要让人再打开一遍文件啊？当然不了，直接实例调用写入的worksheet的那个变量，想怎么来就怎么来了。

这个新的列的表头也得你加

自定义前景色

这没啥，很简单，xlwt和openpyxl都带，自己看下文档，很容易就做出来了。

不过需要注意的是，xlwt的设计有一点秀逗，这个插入的背景色的话，如果想自定义的话，是用不了十六进制的颜色值的……那么自定义颜色值大概是……

pattern = xlwt.Pattern()
xlwt.add_palette_colour("custom_color", 0x21)
c.write_wb.set_colour_RGB(0x21, 0, 204, 255)
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x21