CODEWITHSUNDEEP
javaexcelapache-poi
g_p
g_p

Reputation: 5499

How to get the maximum lenght of a column in excel using POI

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.

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.

Is there any method available in Apache POI such that

foo(c1) will return 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.

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.

Upvotes: 2

Views: 3242

Answers (1)

Flyout91
Flyout91

Reputation: 860

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.

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.

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.

Upvotes: 2

Related Questions