让我头疼一下午的Excel合并单元格

Excel导出常见问题

excel导出其实不算什么难事

在网上copy下模板代码,填充自己的业务数据,提供一个http接口基本就可以得到你要导出的数据了。

但是,凡事都有例外,截止今天,excel导出我遇到的主要是两大类问题

1、大数据量的excel数据,比如几十万条甚至更多的数据导出

2、因为excel中内容的问题,导致导出后的excel不能直接打开,报错“由于一些内容不可取,Excel无法打开xxx.xlsx。是否要打开并修复此工作簿?”

针对第一种大数据量问题,我遇到的主要问题是excel存储的记录上限和导出超时等问题

解决方法是将导出格式为xls升级为xlsx,xls每个sheet最多支持65536条记录,xlsx最多支持1048576条记录;超时则可以采用前端直接返回,后端异步取数据并导出的方式避免超时。

这种情况不是今天要介绍的重点,今天要介绍的第二种情况的解决思路。

需求描述

1、层级关系最多为四级

2、对于相同层级,如果内容相同需要纵向合并单元格,空白行不需要合并

3、样例数据如下所示


一级目录1,二级目录1,三级目录1,四级目录2,

一级目录1,二级目录1,三级目录3,

一级目录1,二级目录1,三级目录5,

一级目录1,二级目录3,

一级目录1,二级目录5,三级目录5,

一级目录2,二级目录2,三级目录2,

一级目录2,二级目录2,三级目录3,

一级目录2,二级目录4,三级目录4,

一级目录2,二级目录7,

一级目录3,二级目录6,三级目录4,

一级目录3,二级目录6,三级目录10,

一级目录4,

一级目录5,二级目录8,三级目录6,

解决思路

将上面样例数据存入一个集合中,遍历每条记录并存放到相应的单元格。

如果不需要合并单元格,到这里,就可以提供导出的Excel了。

但是重点是合并单元格。

遇到的问题

初步排查

自认为代码已经就位,调用接口,Excel文件也成功下载了,结果打开的那一刻一个对话框让我头疼了一下午。

报错信息如下

20181015-1-导出报错信息.png

第一反应是肯定数据错乱了,估计是单元格之间相互挤占,数据肯定也是不堪入目。

但是我按照智能的Excel提示,点击“打开并修复”后发现,数据没有我想的那么糟,甚至仔细看看,发现居然没有问题。

有点小激动的同时,心里还是有点不爽,总不能让别人每次导出的时候都使用这个智能的“打开并修复”功能才能看导出的数据吧。

但是光从这个报错信息来看确实没有什么线索,于是网上找了一通与“由于一些内容不可取,Excel无法打开xxx.xlsx。是否要打开并修复此工作簿?”有关的解决方法。虽然有不少人遇到过这样的问题,但是引起问题的原因不太一样,有些是因为sheet的命名包含特殊字符,有些是导出的Excel内容中有非法字符,还有说要在response的header中加入Content-length字段的。

进一步排查

搜了一通,没有什么进展,这时候想起来在刚刚点击“打开并修复”后,还弹出了一个对话框,于是点击对话框中的查看

image.png

得到线索如下


<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>修复结果到 xxx.xml</logFileName><summary>在文件“/Users/jackie/Downloads/xxx.xlsx”中检测到错误</summary><removedRecords summary="以下是已删除记录的列表:"><removedRecord>已删除的记录: /xl/worksheets/sheet1.xml 的 合并单元格</removedRecord></removedRecords></recoveryLog>

排除了前面提到的种种非法字符的原因,看到线索里的“合并单元格”,基本可以断定这是因为在合并单元格的过程中出了问题。

寻找问题根本原因

结合合并单元格导致Excel表格无法打开的症状在网上搜索一通

http://www.360doc.com/content/14/0107/11/14931240_343269914.shtml这篇文章给出了解决思路

我将下载的Excel表格的后缀从xlsx改为zip并打开

image.png

打开sheet1.xml文件,找到mergeCells标签,将其内容拷贝到XML在线格式化工具中查看

经过人眼搜索,终于发现了问题所在

image.png

    ...

    <mergeCell ref="B175:B189"/>

    <mergeCell ref="B176:B190"/>

    ...

这里显然出现了覆盖合并的情况,进而导致打开Excel报错的情况(后面经过测试发现,重复合并单元格也会出现同样的报错信息)

顺着这个思路,排查代码,不断调试测试,考虑各种情况下的合并单元格场景,最终搞定了这个稍稍复杂的合并单元格的Excel导出功能。

一点思考

虽然知道是合并单元格导致的问题,但是在实际调整代码时花费了几乎一个下午,曾经一度头大到不想思考。

回头想想,在这个问题上有两大收获。

1、排查问题的思路很重要

问题的现象已经摆在眼前,排查了不是非法字符的原因,就应该寻找其他原因

利用一切可以利用的手头信息比如上面简短而关键的报错日志信息。

活用搜索引擎,这种问题肯定已经有前人踩过雷,去看下他们是怎么排雷的就好,不用自己再去研究排雷的具体方法了。

2、写代码之前先想好

现在想想这段合并单元格的代码是不是可以写的更加漂亮,我想应该是可以的,但是能不能从30行精简为10行甚至5行,我想这不太可能。

因为这个导出合并时会遇到各种情况,比如连续相同的单元格何时合并,空白行如何保证不合并,某空白行区域前和后又如何实现合并等问题。

所以,写这段代码前应该先梳理所有可能的场景包括一些特殊情况,尽其所能罗列所有的情况,这样才能保证在应对各种情形的数据时正常导出。

代码稍后我会放到项目rome里

项目地址:https://github.com/DMinerJackie/rome

对了,导出效果图呈上


image.png
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 151,829评论 1 331
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 64,603评论 1 273
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 101,846评论 0 226
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 42,600评论 0 191
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 50,780评论 3 272
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 39,695评论 1 192
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,136评论 2 293
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,862评论 0 182
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,453评论 0 229
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,942评论 2 233
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,347评论 1 242
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,790评论 2 236
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,293评论 3 221
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,839评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,448评论 0 181
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 34,564评论 2 249
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 34,623评论 2 249

推荐阅读更多精彩内容