超过 75000 个单元格的 Excel 文档，用代码操作如何更有效率？

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

JLPT日语真题网

只收集JLPT日语真题，助您快速通过日语考试。

开始学习

Promoted by orion1

PRO

这是一个创建于 3220 天前的主题，其中的信息可能已经有所发展或是发生改变。

Excel 文档情况：

Excel 2003 格式；
将近 40 列，以后可能更多；
一般 2000 行，多的话可能达到 6000 多行；
这个 Excel 文档是外部服务商的网站生成导出的，无法让服务商改代码；

现在，要找出这个 Excel 中，如果整列的所有单元格都为 “ No ”，则把这一列删除（其中所有列的第一行是列标题）；文档非常多，人工查找简直要命。

已经尝试过用 C# 搭配 Office Excel 组件自动化操作，但在低配置的电脑上（ 1G 内存那种）直接崩了。客户的员工电脑也不能让人升级配置…
也尝试了用 PHP 搭配 PHPExcel 操作，扔到阿里云小鸡上，一样挂了……

求一个更有效率的自动化处理方案，不管是写代码，还是宏脚本都可以…

EXCEL

文档

代码

自动化

44 条回复 • 2017-01-07 23:29:45 +08:00

yangqi

2017-01-06 00:29:27 +08:00

如果就是简单文本的话，存成 csv 然后再处理

直接写宏脚本也可以，性能怎么样不知道

starvedcat

2017-01-06 00:29:56 +08:00

要不试试转换成 csv 再操作。。？

ra1983

2017-01-06 00:45:28 +08:00 via Android

这种文件不算大，常年处理 50 万行以上的文件
我们用 SpreadsheetGear, 性能可以接受
个人经验，如果要删除行 /列，从最后一行 /列开始处理文件

October15

2017-01-06 00:49:36 +08:00 via Android

一种方案，
前提：文件转 xlsx 格式，电脑装至少 MSoffice2007 ；
用宏，操作速度很快，内存占用基本上 office 开文件是多少就是多少，不带界面的宏可以改写成独立的 vbs 、 js 文件，或者 C#程序（引用微软的 microsoft.office.interop.excel ）， vbs 版本的

ihuotui

2017-01-06 00:50:28 +08:00

csv 然后文本操作

DarsyCheuk

2017-01-06 00:55:24 +08:00 via iPhone

之前用 python 處理過 excel ，情況跟你有點像， 400 行就挂了，感觉转成 csv 靠谱点？看看楼下的解决方案=͟͟͞͞•̫͡•ʔ

Xs0ul

2017-01-06 00:58:49 +08:00

python pandas 几行代码的事儿

Tink

PRO

2017-01-06 01:08:22 +08:00 via iPhone

这个用 python 就能操作啊

2017-01-06 01:35:38 +08:00

谢谢大家 :)

@yangqi @starvedcat @ihuotui @DarsyCheuk
转 CSV 不太可行，表格存在“合并单元格”的情况，有横有纵，怕转了 CSV 更不好判断；

@Tink @Xs0ul
用 C# 和 PHP 也都是几行代码的事… 问题是在 1G 内存的机器上不要崩…

@October15
安装 2007 不太可能… 客户的机器多…

@ra1983
从最后一列开始处理，如果符合条件就立马删除？如果在此基础上，每删 1 列就保存一次、再打开，这个不知道能否起码保证不卡死

yangqi

2017-01-06 02:02:19 +08:00

@RE 这样看就只有写宏代码了，比较直接

starvedcat

2017-01-06 02:05:55 +08:00

你确定崩溃是因为配置太低了吗，会不会是其他原因

2017-01-06 02:40:49 +08:00

@starvedcat 准确的讲应该是运行了时间很长之后变成无响应了，而在 8G 内存的机器上则能顺利完成。

shiny

2017-01-06 02:48:59 +08:00 via iPhone

Excel 能导出 csv 的话 PHP 可以按行读取，存个变量，目测不会占多少内存。

shiny

2017-01-06 02:50:34 +08:00 via iPhone

能升内存解决就升个内存，几百块钱的事情，按 V2EX 的起步价，一天工资都要好几百了。

Xs0ul

2017-01-06 03:51:19 +08:00

@RE 客户公司里难道一台正经一点的机器都没有吗？我自己 16g 内存的笔记本，处理过 5000*5000 级别的，也处理过 200w * 100 这样的，内存要求并不高的样子。我觉得是不是因为，如果 1G 的员工电脑装的 Windows ，去掉操作系统和别的程序。。其实可能几乎都不剩什么内存了？

如果你有空在 vps 上试试 Python 的话，参考这个链接试试看（我倒是觉得你后来提到的合并单元格会麻烦一点，我没碰到过这个情况）：
http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.dropna.html

starvedcat

2017-01-06 03:56:22 +08:00

同意楼上说法，我觉得应该可以用编程方法解决的，用不了那么多内存

princelai

2017-01-06 07:46:37 +08:00 via Android

我常年处理 1 万 3 千+行， 10-15 列的表， pandas 几秒钟的事，但是我是 i3+8g

BingoXuan

2017-01-06 08:40:07 +08:00 via iPhone

我试过 pandas 分别读取共一万行乘二十多列的 excel 数据，只要 io 没问题，其实 python 批量处理也是很快的。

truehyp

2017-01-06 08:50:35 +08:00 via Android

我会转成 csv ，用 shell 命令处理， linux 下 sort ， cut 等命令效率挺高的

okampfer

2017-01-06 09:09:31 +08:00

有没有比较好用的 web 端 excel 替代品？

BBrother

2017-01-06 09:26:33 +08:00

这个不是 vba 就能解决吗？先把所有 'No' 替换成 0 ，然后 sum 一下，不等于 0 的全部 Sheet1.Columns(列号).Delete

300

2017-01-06 10:32:57 +08:00

我怎么觉得转换成数据库会好一点=。=

2017-01-06 10:57:28 +08:00 via iPhone

@BBrother 机智，这个应该有戏！

loudis

2017-01-06 10:59:31 +08:00

明显用 vba 应该很容易搞定啊

wang9571

2017-01-06 11:05:04 +08:00

VBA+1 ，不过没必要进行替换，可以使用 CountIf

yuedingwangji

2017-01-06 16:10:15 +08:00

@DarsyCheuk python 处理 excel ， 400 行就挂，这么弱？

yuedingwangji

2017-01-06 16:11:27 +08:00

@truehyp 请问 shell 如何解决 excel 中一个单元格多行的问题？

October15

2017-01-06 16:44:38 +08:00 via Android

@loudis
@wang9571
@BBrother
有不装 excel 用 VBA 的方法吗？

BBrother

2017-01-06 16:49:51 +08:00

@October15 不知道，为什么有这么奇怪的需求？ office 之外，什么场景会单独用 vba ？

billion

2017-01-06 16:53:29 +08:00

你知道 Python 里面有一个东西叫做生成器吗？
你的电脑之所以会崩溃，是因为你一次性读入了所有的内容。但是使用生成器，可以每次只读取一行内容。这样就不会导致内存占用太大。

vmebeh

2017-01-06 16:58:15 +08:00 via iPhone

用筛选，筛选整个表，如果下拉框只有“ No ”，没有“（空白）”或其他那么这列就可以删了
自动化可以考虑 VBA

crazykuma

2017-01-06 17:00:57 +08:00

pandas

2017-01-06 17:09:18 +08:00

@billion 兄台估计还不明白 .xls 格式……

2017-01-06 17:15:00 +08:00

@loudis @wang9571 @vmebeh

主题中还有一个需求没说出来，就是需要到另一份 excel 去查找数据，合并成一个新表格，所以觉得 vba 并不是很合适。

此前是卡在逐个单元格判断这一块，经 @BBrother 和 @wang9571 的提醒，改成 CountIf 之后效率提高了很多，现在该来做更蛋疼的合并表格了。

发帖询问还是很有帮助的，除了知道了很多工具意外，也能获得更广阔的编程思路，后者比工具其实更有价值！

October15

2017-01-06 17:16:09 +08:00 via Android

@BBrother 我的建议也是装 office 用宏也就是 VBA ，然后你看题主 9L 回复我的

2017-01-06 17:20:13 +08:00

@October15 话说 Office 2003 也有宏啊…… 为啥前提一定要 2007 呢

vmebeh

2017-01-06 17:20:15 +08:00 via iPhone

最好是放一部分样本出来

October15

2017-01-06 17:28:02 +08:00 via Android

@RE VBA 可以做跨表查询和表格合并，
查询我直接用过 range(范围). find 性能不错，一列一千数据，一个个在一列 8 万的里面查询也就几分钟
合并的话复制粘贴就好了，如果用 usedange.copy 可以只复制筛选后显示的部分

2017-01-06 17:31:24 +08:00

@October15 想做成 C# 的原因是比较傻瓜化，万一操作的人不懂 VBA 还要搞个培训什么的… 能一键搞定当然更省事嘛，只要 countif / find 这些的效率不错，做成 C# 的话合并自然就更简单啦~

October15

2017-01-06 17:32:24 +08:00 via Android

@RE 只是我觉得 07 比 03 好用😅，另外 xls 最大行数比 xlsx 少， xlsx 文件体积小

2017-01-06 17:34:09 +08:00

@October15 要是 xlsx 也省心唉… 内部是 xml 格式，分段读取都不是问题了…

October15

2017-01-06 17:46:28 +08:00 via Android

@RE VBA 纯数据处理的宏，改成 C#、 vbs 、 js 难度不大， API 都一样语法差别而已。有一个宏写过 vbs 和 C#版本，但是 vbs 是 VB 子集更好改就是， vbs 、 js 脚本优势是双击直接运行，不用先开 excel 运行宏， notpad 直接改，装了 office 都能用

loudis

2017-01-06 18:20:21 +08:00

跨文件更该用 VBA 啊，用公式跨表格可能会很慢造成卡死。

VBA 直接可以打开多个文件处理，写好了代码，放在一个空文件里运行即可，多好。

mkeith

2017-01-07 23:29:45 +08:00

python 导入到 sqlite 分析然后再导出呢