0

I have an excel sheet having columns A to P. But I want to take the columns B to F, I to K, L, P

How can I achieve this?

Any help is highly appreciated.

Thanks in advance.

I have declared a property in properties file as below

excel.specific.columns= "B:F,I:K,L,P"

Then I declared a list in the java class for the above property.

But not getting idea how to use that list.

Sujit Kumar
  • 15
  • 3
  • 1
    Does this answer your question? [How to get columns from Excel files using Apache POI?](https://stackoverflow.com/questions/2922692/how-to-get-columns-from-excel-files-using-apache-poi) – Gastón Schabas Aug 09 '23 at 19:03
  • @Gaston Schabas thanks for your reply. I will implement this. But how can I get the range by reading the property from the properties file to get the range of the workbook – Sujit Kumar Aug 09 '23 at 19:43
  • I don't know if the library offers something for that. Looks like `"B:F,I:K,L,P"` follows the pattern that all values are splitted by a `,` and it could be a range splitted by a `:` or just one column. Maybe using the split function is enough. If you want something more complex, you should dig in the docs of the lib or implement your own parser. A json could be more readable and easier to parse. It depends on the complexity you need to solve your issue – Gastón Schabas Aug 09 '23 at 23:58
  • Possibly https://poi.apache.org/apidocs/dev/org/apache/poi/ss/util/AreaReference.html will do the parsing work for you? – Gagravarr Aug 10 '23 at 11:01

1 Answers1

0

You will have to parse the property value you set and collect a list of column numbers. The you can call row.getCell(columnNumber) for each column number to get the actual values. Here is a quick and dirty example to show you what the parser may look like. Hope it helps.

import java.util.List;
import java.util.ArrayList;

public class CherryPickedColumns {

    public static void main(String[] args) {
        String range_prop = "B:F,I:K,L,P";
        
        String[] ranges = range_prop.split(",");
        
        List<Integer> columns = new ArrayList<Integer>();

        for(int i=0; i< ranges.length; i++) {
            if(ranges[i].length() == 1) {
                columns.add(ranges[i].charAt(0)-'A');
            } else if (ranges[i].length() == 3 && ranges[i].charAt(1) == ':') {
                char start = ranges[i].charAt(0);
                char end = ranges[i].charAt(2);
                while(start <= end) {
                    columns.add(start-'A');
                    start += 1;
                }
            }
        }

        System.out.println("my cherry picked columns:" + columns);
    }
}
user9035826
  • 126
  • 6