问题描述
我在这里遇到python,openpyxl和Excel文件的大问题.我的目标是将一些计算出的数据写入Excel中的预配置模板.我加载此模板并在其上写入数据.有两个问题:
I have a big problem here with python, openpyxl and Excel files. My objective is to write some calculated data to a preconfigured template in Excel. I load this template and write the data on it. There are two problems:
- 我正在谈论编写具有超过200万个单元格(分为几页)的Excel图书.
- 我成功做到了,但是等待时间是不可思议的.
我不知道其他解决此问题的方法.也许openpyxl不是解决方案.我试图用xlsb编写,但是我认为openpyxl不支持这种格式.我也尝试过使用优化的写入器和读取器,但是由于大数据,保存时出现了问题.但是,输出文件大小最大为10 MB.我对此非常执着.你知道还有其他方法吗?
I don't know other way to solve this problem. Maybe openpyxl is not the solution. I have tried to write in xlsb, but I think openpyxl does not support this format. I have also tried with optimized writer and reader, but the problem comes when I save, due to the big data. However, the output file size is 10 MB, at most. I'm very stuck with this. Do you know if there is another way to do this?
谢谢.
推荐答案
在使用内存时,文件大小并不是真正的问题,而是在内存中的单元数.您的用例确实会将openpyxl
推到目前的极限,该极限当前旨在支持 优化阅读或优化写作,但不能同时支持两者.您可能要尝试的一件事是使用use_iterators=True
读取openpyxl
,这将为您提供一个生成器,您可以从xlsxwriter
调用该生成器,该生成器应该能够为您编写一个新文件.创建文件时,xlsxwriter
当前比openpyxl
快得多.该解决方案并不完美,但可能对您有用.
The file size isn't really the issue when it comes to memory use but the number of cells in memory. Your use case really will push openpyxl
to the limits at the moment which is currently designed to support either optimised reading or optimised writing but not both at the same time. One thing you might try would be to read in openpyxl
with use_iterators=True
this will give you a generator that you can call from xlsxwriter
which should be able to write a new file for you. xlsxwriter
is currently significantly faster than openpyxl
when creating files. The solution isn't perfect but it might work for you.
这篇关于openpyxl:使用python编写大型excel文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!