CODEWITHSUNDEEP
javaexcelapache-poi
Aurangzeb Rathore
Aurangzeb Rathore

Reputation: 147

Can I traverse through an excel file using Indexes when working with Apache POI?

Please excuse me if I am not clear. English is not my first language.

I'm trying to write a code where I can traverse through the first row of an excel file until I find the column labeled 'Comments'. I want to run some action on the text in that column and then save the result in a new column at the end of the file. Can I traverse the xlsx file in a manner similar to indexes? And if so, how can I jump straight to a cell using that cell's coordinates?

public static void main(String[] args) throws IOException {

    File myFile = new File("temp.xlsx");
    FileInputStream fis = null;
    try {
        fis = new FileInputStream(myFile);
    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    @SuppressWarnings("resource")
    XSSFWorkbook myWorkBook = new XSSFWorkbook (fis);
    XSSFSheet mySheet = myWorkBook.getSheetAt(0);
    Iterator<Row> rowIterator = mySheet.iterator();


    Row row = rowIterator.next();
    Iterator<Cell> cellIterator = row.cellIterator();

    while (cellIterator.hasNext()) {

        Cell cell = cellIterator.next();

        String comment = cell.toString();

        if (comment.equals("Comments"))
        {
            System.out.println("Hello");
        }



    }


}

}

Upvotes: 0

Views: 465

Answers (3)

Divya Taparia
Divya Taparia

Reputation: 11

FileInputStream file = new FileInputStream(new File(fileLocation));

Workbook workbook = new XSSFWorkbook(file);
Sheet sheet = workbook.getSheetAt(0);

Map<Integer, List<String>> data = new HashMap<>();
int i = 0;
for (Row row : sheet) {
    data.put(i, new ArrayList<String>());
    for (Cell cell : row) {
        switch (cell.getCellTypeEnum()) {
            case STRING: ... break;
            case NUMERIC: ... break;
            case BOOLEAN: ... break;
            case FORMULA: ... break;
            default: data.get(new Integer(i)).add(" ");
        }
    }
    i++;
}

Upvotes: 1

Axel Richter
Axel Richter

Reputation: 61852

For the question "Wanted to go to the second column's 3rd row I could use coordinates like (3, 2)?":

Yes this is possible using CellUtil. Advantages over the methods in Sheet and Row are that CellUtil methods are able getting the cell if it exists already or creating the cell if it not already exists. So existing cells will be respected instead simply new creating them and so overwriting them.

Example:

import java.io.FileOutputStream;

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

import org.apache.poi.ss.util.CellUtil;

import java.util.concurrent.ThreadLocalRandom;

public class CreateExcelCellsByIndex {

 public static void main(String[] args) throws Exception {

  Workbook workbook = new XSSFWorkbook();
  Sheet sheet = workbook.createSheet();

  //put content in R3C2:
  Cell cell = CellUtil.getCell(CellUtil.getRow(3-1, sheet), 2-1); //-1 because apache poi's row and cell indexes are 0 based
  cell.setCellValue("R3C2");

  //put content in 10 random cells:
  for (int i = 1; i < 11; i++) {

   int r = ThreadLocalRandom.current().nextInt(4, 11);
   int c = ThreadLocalRandom.current().nextInt(1, 6);
   cell = CellUtil.getCell(CellUtil.getRow(r-1, sheet), c-1);
   String cellcontent = "";
   if (cell.getCellTypeEnum() == CellType.STRING) {
    cellcontent = cell.getStringCellValue() + " ";
   }
   cell.setCellValue(cellcontent + i + ":R"+r+"C"+c);
  }

  workbook.write(new FileOutputStream("CreateExcelCellsByIndex.xlsx"));
  workbook.close();

 }

}

Upvotes: 1

Andrew S
Andrew S

Reputation: 2756

I'm not sure what you mean by 2D index, but a Cell knows which column it belongs to so something like this should work:

...
Cell cell = cellIterator.next();

String comment = cell.toString();

int sourceColumnIndex = -1;
if (comment.equals("Comments")) {
    System.out.println("Hello");
    sourceColumnIndex = cell.getColumnIndex();
}
....

Similarly, define something like int targetColumnIndex to represent the column which will have the result from processing all the cells from the sourceColumnIndex column.

Upvotes: 0

Related Questions