登录
  • 人们都希望被别人需要 却往往事与愿违
  • 投资是预测资产收益的活动, 而投机是预测市场心理的活动@凯恩斯

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

不懂编程 Benny小土豆 3687次浏览 6236字 6个评论
文章目录[显示]

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

import openpyxl

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

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

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

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

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

我的单元格都乱套了……

这可咋整……不管了,掏出了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功能,很容易就发现遍历单元格的方法:

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

我们可以很清楚的看到,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 copyv.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.nrowsread_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

这个新加的表头也要封装

于是咱咔咔咔的要给这个类再加个方法,再给构造方法添加个参数啥的(因为要处理两个不同的表头样式)

啪ㄟ( ▔, ▔ )ㄏ手疼ヽ(*。>Д<)o゜

难道不知道有个东西叫做继承吗?这个类已经很纯粹了,就是给Excel前面加两列,这么加个不相干的方法进去那就是老鼠屎里进了米饭……啊不是米饭里进了老鼠屎。继承下更好哟(^U^)ノ~YO

结果

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

完美(o゜▽゜)o☆

全部源代码以及示例文件可以戳下面的章鱼猫哦o(=•ェ•=)m
开源地址


文章版权归原作者所有丨本站默认采用CC-BY-NC-SA 4.0协议进行授权|
转载必须包含本声明,并以超链接形式注明原作者和本文原始地址:
https://www.bennythink.com/python-excel-add-column.html
喜欢 (2)
分享:-)
Benny小土豆
关于作者:
If you have any further questions, feel free to contact me in English or Chinese.
发表我的评论(代码和日志请使用Pastebin或Gist)
取消评论

                     

去你妹的实名制!

  • 昵称 (必填)
  • 邮箱 (必填,不要邮件提醒可以随便写)
  • 网址 (选填)
(6)个小伙伴在吐槽
  1. 半夜读着读着手机自动播放音乐,吓我一跳。
    尔今夏2018-12-26 01:07 回复
    • Benny小土豆
      小黛:怪我噢?
      Benny小土豆2018-12-26 09:17 回复
  2. execl功能真是强大啊
    汽修软件2018-10-22 14:04 回复
  3. 有 Excel 版就会有 OpenDocument 版(笑
    布偶君2018-09-25 17:20 回复
    • Benny小土豆
      ?这是不可能滴
      Benny小土豆2018-09-26 13:54 回复
      • 为什么?业界(仮)不使用 OpenDocument?
        布偶君2018-09-26 14:03 回复