Java – Apache POI – JAVA – iterating over columns in excel

apache-poicellexceliterationjava

new to java here. I'm working on a code that reads excel files (looking at cells in columns) and then writes something that looks like the following tables:

I have an excel file that looks like this:

      col1     col2    col3    col4
      -----------------------------
row1 | 2,3,1    _        1      w
row2 | 3,2,7    _        2      x
row3 |   _      _        3      y
row4 |  4,9     _        4      z

I'm writing some values (using XLWT) in column 2 that look like this:

      col1     col2    col3    col4
      -----------------------------
row1 | 2,3,1  x,y,w      1      w
row2 | 3,2,7   y,x       2      x
row3 |   _      _        3      y
row4 |  4,9     z        4      z

Essentially, column 3 and column 1 are being compared, and if cell (1,1) has values matched in column 3, column 4's values (which correspond to column 3) are written into column 2.

I've done this in Python actually, and I considered using Jython, however, I couldn't find any docs on importing python modules into Jython code. I believe XSSF apache-poi is the only way I can deal with xlsx files in java.

Relevant page: How to do cell iteration of excel in java

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

public class Python {
    public static void main(String[] args) {
        try {


        FileInputStream file = new FileInputStream(new File("C:\\Users\\...\\Bioactives25s.xlsx"));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet worksheet = workbook.getSheetAt(0);
        for (int i = 0; i < 9999; i++) { 
            XSSFRow row = worksheet.getRow(i);
            if(row == null) {
                //iterates over rows
            }


        for (int j = 0; j < 30; j++) {
             XSSFCell cell = row.getCell(j);

                if(cell == null) {
               //iterates over cells

Mainly looking for a way to iterate through column values. I looked through the documentation: http://poi.apache.org/spreadsheet/quick-guide.html#Iterator

But I could only find code for dealing with rows and cells, not columns. I'd like to iterate through the columns though. Is there any code to deal with this?

Best Answer

Just iterate through rows and take required cell:

int columnIndex = 3;
for (int rowIndex = 0; rowIndex<3; rowIndex++){
    Row row = CellUtil.getRow(rowIndex, sheet);
    Cell cell = CellUtil.getCell(row, columnIndex);
}

Related Solutions

Java – How to efficiently iterate over each entry in a Java Map

Map<String, String> map = ...
for (Map.Entry<String, String> entry : map.entrySet()) {
    System.out.println(entry.getKey() + "/" + entry.getValue());
}

On Java 10+:

for (var entry : map.entrySet()) {
    System.out.println(entry.getKey() + "/" + entry.getValue());
}

Java – When to use LinkedList over ArrayList in Java

Summary ArrayList with ArrayDeque are preferable in many more use-cases than LinkedList. If you're not sure — just start with ArrayList.

TLDR, in ArrayList accessing an element takes constant time [O(1)] and adding an element takes O(n) time [worst case]. In LinkedList adding an element takes O(n) time and accessing also takes O(n) time but LinkedList uses more memory than ArrayList.

LinkedList and ArrayList are two different implementations of the List interface. LinkedList implements it with a doubly-linked list. ArrayList implements it with a dynamically re-sizing array.

As with standard linked list and array operations, the various methods will have different algorithmic runtimes.

For LinkedList<E>

get(int index) is O(n) (with n/4 steps on average), but O(1) when index = 0 or index = list.size() - 1 (in this case, you can also use getFirst() and getLast()). One of the main benefits of LinkedList<E>
add(int index, E element) is O(n) (with n/4 steps on average), but O(1) when index = 0 or index = list.size() - 1 (in this case, you can also use addFirst() and addLast()/add()). One of the main benefits of LinkedList<E>
remove(int index) is O(n) (with n/4 steps on average), but O(1) when index = 0 or index = list.size() - 1 (in this case, you can also use removeFirst() and removeLast()). One of the main benefits of LinkedList<E>
Iterator.remove() is O(1). One of the main benefits of LinkedList<E>
ListIterator.add(E element) is O(1). One of the main benefits of LinkedList<E>

^{Note: Many of the operations need n/4 steps on average, constant number of steps in the best case (e.g. index = 0), and n/2 steps in worst case (middle of list)}

For ArrayList<E>

get(int index) is O(1). Main benefit of ArrayList<E>
add(E element) is O(1) amortized, but O(n) worst-case since the array must be resized and copied
add(int index, E element) is O(n) (with n/2 steps on average)
remove(int index) is O(n) (with n/2 steps on average)
Iterator.remove() is O(n) (with n/2 steps on average)
ListIterator.add(E element) is O(n) (with n/2 steps on average)

^{Note: Many of the operations need n/2 steps on average, constant number of steps in the best case (end of list), n steps in the worst case (start of list)}

LinkedList<E> allows for constant-time insertions or removals using iterators, but only sequential access of elements. In other words, you can walk the list forwards or backwards, but finding a position in the list takes time proportional to the size of the list. Javadoc says "operations that index into the list will traverse the list from the beginning or the end, whichever is closer", so those methods are O(n) (n/4 steps) on average, though O(1) for index = 0.

ArrayList<E>, on the other hand, allow fast random read access, so you can grab any element in constant time. But adding or removing from anywhere but the end requires shifting all the latter elements over, either to make an opening or fill the gap. Also, if you add more elements than the capacity of the underlying array, a new array (1.5 times the size) is allocated, and the old array is copied to the new one, so adding to an ArrayList is O(n) in the worst case but constant on average.

So depending on the operations you intend to do, you should choose the implementations accordingly. Iterating over either kind of List is practically equally cheap. (Iterating over an ArrayList is technically faster, but unless you're doing something really performance-sensitive, you shouldn't worry about this -- they're both constants.)

The main benefits of using a LinkedList arise when you re-use existing iterators to insert and remove elements. These operations can then be done in O(1) by changing the list locally only. In an array list, the remainder of the array needs to be moved (i.e. copied). On the other side, seeking in a LinkedList means following the links in O(n) (n/2 steps) for worst case, whereas in an ArrayList the desired position can be computed mathematically and accessed in O(1).

Another benefit of using a LinkedList arises when you add or remove from the head of the list, since those operations are O(1), while they are O(n) for ArrayList. Note that ArrayDeque may be a good alternative to LinkedList for adding and removing from the head, but it is not a List.

Also, if you have large lists, keep in mind that memory usage is also different. Each element of a LinkedList has more overhead since pointers to the next and previous elements are also stored. ArrayLists don't have this overhead. However, ArrayLists take up as much memory as is allocated for the capacity, regardless of whether elements have actually been added.

The default initial capacity of an ArrayList is pretty small (10 from Java 1.4 - 1.8). But since the underlying implementation is an array, the array must be resized if you add a lot of elements. To avoid the high cost of resizing when you know you're going to add a lot of elements, construct the ArrayList with a higher initial capacity.

If the data structures perspective is used to understand the two structures, a LinkedList is basically a sequential data structure which contains a head Node. The Node is a wrapper for two components : a value of type T [accepted through generics] and another reference to the Node linked to it. So, we can assert it is a recursive data structure (a Node contains another Node which has another Node and so on...). Addition of elements takes linear time in LinkedList as stated above.

An ArrayList, is a growable array. It is just like a regular array. Under the hood, when an element is added at index i, it creates another array with a size which is 1 greater than previous size (So in general, when n elements are to be added to an ArrayList, a new array of size previous size plus n is created). The elements are then copied from previous array to new one and the elements that are to be added are also placed at the specified indices.

Best Answer

Related Solutions

Java – How to efficiently iterate over each entry in a Java Map

Java – When to use LinkedList over ArrayList in Java

Related Topic