Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Apache POI - JAVA - iterating over columns in excel

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?

like image 803
Kat Avatar asked Jan 07 '16 20:01

Kat


1 Answers

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);
}
like image 177
dlopatin Avatar answered Sep 28 '22 00:09

dlopatin