如何加快excel读/写 [英] How to speed up excel reading/writing

问题描述

我在这里作为实习生使用 Apache POI 为我的公司读取/写入 Excel 文件.我的程序通过 excel 文件，这是一个大正方形，上面有顶行计算机名称和左列用户名.240 台计算机和 342 个用户.sheet[computer][user] 在所有空间中都是 0 并且程序为每台计算机调用 PSLoggedon 并获取当前登录的用户名并增加它们的 0 所以在一个月后运行它后，它显示谁登录了最到每台电脑.到目前为止，自从我在实际调用 PSLoggedon 之前使用套接字检查 socket.connect 以来，它运行了大约 25 分钟.

I am using Apache POI to read/write to an excel file for my company as an intern here. My program goes through the excel file which is a big square with top rows computer names and left column user names. 240 computers and 342 users. the sheet[computer][user] is 0 in all spaces and the program calls PSLoggedon for each computer and takes the username(s) currently logged on and increments their 0 so after running it after a month, it shows who is logged in the most to each computer. So far it runs in about 25 minutes since I used a socket to check socket.connect before actually calling PSLoggedon.

完全不读取或写入excel文件，只需将所有PSLoggedon调用调用到每台计算机，大约需要9分钟.因此，阅读和写作显然需要 10-15 分钟.问题是，我在计算机上调用 PSLoggedon，然后打开 excel 找到 [计算机][用户] 的 [x][y] 点，然后向它写入一个 +=1，然后关闭它.所以我想之所以花这么长时间是因为它打开和关闭文件太多了?我可能完全错了.但是我想不出一种方法来通过一次打开和读取/写入并且只打开和关闭一次文件来加快速度.有什么想法吗?

Without reading or writing at all to the excel file, just calling all the PSLoggedon calls to each computer, takes about 9 minutes. So, the reading and writing apparently takes 10-15 minutes. The thing is, I am calling PSLoggedon on the computer, then opening the excel to find the [x][y] spot of the [computer][user] and then writing to it a +=1 then closing it. So the reason it is taking this long I suppose is because it opens and closes the file so much? I could be completely wrong. But I can't think of a way to make this faster by opening and reading/writing all at once and only opening and closing the file once. Any ideas?

推荐答案

通常 Apache-POI 非常快，如果您遇到一些问题，那么您可能需要检查以下几点:

Normally Apache-POI is very fast, if you are running into some issue then you might need to check below points:

1. POI 的日志记录可能已开启，您需要将其关闭:您可以将这些 -D 之一添加到您的 JVM 设置中以执行此操作:

1. POI's logging might be on, you need to turn them off: You can add one of these –D to your JVM settings to do this:

-Dorg.apache.poi.util.POILogger=org.apache.poi.util.NullLogger

您可能将 VM 堆设置为低值，请尝试增加.

You may be setting your VM heap to low value, try to increase.

比 XLSX 更喜欢 XLS.

Prefer XLS over XLSX.

这篇关于如何加快excel读/写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！