Splitting cell into strings in Excel - Apache POI JAVA

Question

I have an excel file that looks like this. Looking for a way to split col1 cells into separate parts (item in items).

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

CODE:

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

public class Expy {
    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 rowIndex = 0; rowIndex<50000; rowIndex++){
           for (int columnIndex = 0; columnIndex<30; columnIndex++){
            XSSFCell cell = worksheet.getRow(rowIndex).getCell(0);
            XSSFCell COL1 = worksheet.getRow(rowIndex).getCell(2);
            XSSFCell COL2 = worksheet.getRow(rowIndex).getCell(3);

            //important region
            String data = ??? ;
            String[] items = cell.split(",");
            //for (String item : items)

           if(cell != null){
           continue;}
            if(cell == COL1){
                System.out.print("it worked!!!");
            }
            if(cell == null){
                continue;
            }

I have no idea what to put in the question mark area. I tried putting in "file", I tried "worksheet", not sure what I'm supposed to write to make excel column 1 read as a string, so I can iterate over it. Very new to java.

Relevant: http://kodejava.org/how-do-i-split-a-string/

Tim Biegeleisen · Accepted Answer · 2016-01-19 00:29:18Z

3

Use XSSFCell.getStringCellValue():

XSSFCell cell = worksheet.getRow(rowIndex).getCell(0);
String contents = cell.getStringCellValue();

String[] items = contents.split(",");

for (String item : items) {
    System.out.println("Found csv item: " + item);
}

Read the Apache POI documentation for more information.

answered Jan 19, 2016 at 0:29

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Biegeleisen Over a year ago

Happy to help you out :-)

Collectives™ on Stack Overflow

Splitting cell into strings in Excel - Apache POI JAVA

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related