CODEWITHSUNDEEP
javaexcelstringapache-poirow
Andrew
Andrew

Reputation: 55

how to convert Excel row to string value in Java?

Is there a direct built-in function in Java POI to convert an Excel row to string? I have tried "row.toString()" but it returned the memory location of the row.

Upvotes: 2

Views: 9545

Answers (6)

Fagner Fonseca
Fagner Fonseca

Reputation: 293

You could use the java8 foreach to get maybe a more convenient code:

try (
    InputStream inputStream = RowToString.class.getResourceAsStream("excel.xlsx");
    Workbook workbook = WorkbookFactory.create(inputStream);
) {
    Sheet sheet = workbook.getSheetAt(0);
    List<String> rows = new ArrayList<>();
    sheet.forEach(row -> {
        StringJoiner joiner = new StringJoiner("|");
        row.forEach(cell -> joiner.add(cell.toString()));
        rows.add(joiner.toString());
    });

    rows.forEach(System.out::println);
}

The StringJoiner is a good choice to concatenate the values. For get the cell values, the toString works fine. The toString will get the proper value of any cell type. The only problem is that the toString won't resolve a formula, it will get the formula itself. But you can adapt the code for your own necessity.

Upvotes: 0

Gagravarr
Gagravarr

Reputation: 48336

All the code you need to do this is given in the Apache POI documentation, specifically iterating over rows and cells and getting the cell contents. Reading the documentation often helps!

Assuming you want to do something simple like join all the cells together with commas (note - not a good way to generate a CSV as you also need to escape things!) you'd do something like this:

// Load the file, get the first sheet
Workbook wb = WorkbookFactory.create(new File("input.xls"));
Sheet sheet = wb.getSheetAt(0);

// To turn numeric and date cells into friendly formatted strings
DataFormatter formatter = new DataFormatter();

// Process every row
for (Row row : sheet) {
   StringBuffer text = new Stringbuffer();
   // Turn each cell into a string, and append
   for (Cell : row) {
      if (! text.isEmpty()) { text.append(", "); }
      text.append(formatter.formatCellValue(cell));
   }

   // TODO Do something useful with the string
   String rowAsText = text.toString();
}

// Tidy up
wb.close();

Upvotes: 2

Sameera
Sameera

Reputation: 83

     XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File("path")));

        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    Iterator<Cell> cellIterator = row.cellIterator();
          while (cellIterator.hasNext()) 
            {
               Cell cell = cellIterator.next();
                switch (evaluator.evaluateInCell(cell).getCellType()) 
                {                  
                    case Cell.CELL_TYPE_NUMERIC:
                        double a = cell.getNumericCellValue();
                        break;
                    case Cell.CELL_TYPE_STRING:
                        String val = cell.getStringCellValue();
                        break;
                 }
            }

Upvotes: 0

Jonas
Jonas

Reputation: 198

Dont iterate through all Cells/Lines, just pick your certain Cell by following:

int x = 5;
int y = 8;   
// open your File
Workbook wb = new XSSFWorkbook(file);
// Get your excel sheet nr. 1
XSSFSheet sheet = Workbook.getSheetAt(0);
// get your Row from Y cordinate
Row yourRow = sheet.getRow(y);
// get your Cell from X cordinate
Cell yourCell = cell = yourRow.getCell(x);
// be sure that the pointingCell is an String or else it will catch Exception.
String cellString = cell.getStringCellValue();

Upvotes: 1

ong
ong

Reputation: 75

You can try using the Files class in java. It has methods for e.g

Files.lines(Path path)

that takes in a path and returns a Stream. With a stream you can split the rows and apply your methods. You can take a look at its API for its other methods whichever may be more applicable.

https://docs.oracle.com/javase/9/docs/api/java/nio/file/Files.html

Upvotes: 0

Zibaire
Zibaire

Reputation: 127

You can iterate over Cells, 

       Row row = (Row) rows.next();
        Iterator cells = row.cellIterator();

     while (cells.hasNext()) {
      Cell cell = (Cell) cells.next();
...
}

And for retrieve a string of a cell you can use :

cell.getStringCellValue()

Upvotes: 0

Related Questions