I am working on a project which involves reading excel data using Apache POI. I have a situation where I have to find out the maximum length of a column.
我正在开发一个涉及使用Apache POI读取Excel数据的项目。我有一种情况,我必须找出列的最大长度。
For example
A sheet has 3 rows r1, r2, r3 and 2 columns c1 and c2 and all contains string data. Now say string size in r1c1 is 5, in r2c1 is 6 and in r3c1 is 7.
例如,一张纸有3行r1,r2,r3和2列c1和c2,并且都包含字符串数据。现在说r1c1中的字符串大小为5,r2c1中的字符串大小为6,r3c1中的字符串大小为7。
Is there any method available in Apache POI such that
Apache POI中是否有任何可用的方法
foo(c1) will return 7.
foo(c1)将返回7。
Currently I am using foo loop to iterate over all the cells in the column and calculate the maximum size, however it is hitting the performance as excel file is large.
目前我正在使用foo循环迭代列中的所有单元格并计算最大大小,但是当excel文件很大时它会达到性能。
I went through the documentation but can't find one. Is for loop only way to do it?
我浏览了文档但找不到。是循环的唯一方法吗?
Why I want this
为什么我要这个
I have to write a functionality where application can upload excel in db. DDL statement should be created on the fly and to generate ddl statement, I need to know the max length of column. I know I am trying to reinvent the wheel as db like MySQL already has this plugin, but here I am not suppose to use that because I want to keep everything in one place. I went through the source code of plugin but it is too complex to copy in the project.
我必须编写一个应用程序可以在db中上传excel的功能。应该动态创建DDL语句并生成ddl语句,我需要知道列的最大长度。我知道我正在尝试重新发明*,因为像MySQL这样的db已经有了这个插件,但在这里我不想使用它,因为我想把所有东西放在一个地方。我浏览了插件的源代码,但是在项目中复制太复杂了。
1 个解决方案
#1
2
May be you can solve it using getColumnWidth : If your maximum length in a column always match the width of the column (no useless space except for the left and right margins), you might be able to "guess" the maximum length only using the column width and dividing it by the length 1 character is supposed to have (IF they all have the same length). But even if it possible you might need to consider the margins + the tiny space between two characters in the calculation.
也许你可以使用getColumnWidth来解决它:如果你的列中的最大长度总是与列的宽度匹配(除了左边距和右边距之外没有无用的空间),你可能只能使用“猜测”来猜测最大长度。列宽度除以长度为1的字符应该具有(如果它们都具有相同的长度)。但即使有可能,您也可能需要考虑边距+计算中两个字符之间的微小空间。
With a general and very simple exemple : if your column is 10 units and you know that one character have a length of 1 unit, then you know that the maximum string has 10 characters.
一般而且非常简单的例子:如果你的列是10个单位,并且你知道一个字符的长度为1个单位,那么你知道最大字符串有10个字符。
This is very very theoric as I cannot code anything right now, but this is the only solution I can imagine besides what you have already done.
这是非常非常理论的,因为我现在无法编写任何代码,但这是我能想象的唯一解决方案,除了你已经做过的事情。
#1
2
May be you can solve it using getColumnWidth : If your maximum length in a column always match the width of the column (no useless space except for the left and right margins), you might be able to "guess" the maximum length only using the column width and dividing it by the length 1 character is supposed to have (IF they all have the same length). But even if it possible you might need to consider the margins + the tiny space between two characters in the calculation.
也许你可以使用getColumnWidth来解决它:如果你的列中的最大长度总是与列的宽度匹配(除了左边距和右边距之外没有无用的空间),你可能只能使用“猜测”来猜测最大长度。列宽度除以长度为1的字符应该具有(如果它们都具有相同的长度)。但即使有可能,您也可能需要考虑边距+计算中两个字符之间的微小空间。
With a general and very simple exemple : if your column is 10 units and you know that one character have a length of 1 unit, then you know that the maximum string has 10 characters.
一般而且非常简单的例子:如果你的列是10个单位,并且你知道一个字符的长度为1个单位,那么你知道最大字符串有10个字符。
This is very very theoric as I cannot code anything right now, but this is the only solution I can imagine besides what you have already done.
这是非常非常理论的,因为我现在无法编写任何代码,但这是我能想象的唯一解决方案,除了你已经做过的事情。