2

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.

4
  • 2
    try reading cell by cell and then append it to string Commented Nov 28, 2017 at 8:55
  • @SpringLearner I am doing that already and it works .. but I'd like to optimize my solution by avoiding nested loop. Commented Nov 28, 2017 at 8:59
  • 2
    @andrew Why? What's wrong with the nested loop? Do you have any performance issues with it? Commented Nov 28, 2017 at 9:06
  • What do you expect to be "the string value of a row in Excel"? A Excel row contains multiple cells, each cell having their own value and not all of those cell values must be strings. Commented Nov 28, 2017 at 9:21

6 Answers 6

2

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();
Sign up to request clarification or add additional context in comments.

Comments

1

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();

Comments

0

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()

2 Comments

Only if the cell really contains a string, and not something like a formatted number or formatted date....
Of course yes, It's also possible to use : cell.getCellType() to retrieve the value type and adapt your code logic.
0

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

1 Comment

Well good luck while trying this method on an Excel file. An Excel is either a binary file (*.xls) or an ZIP archive containing multiple XML files in a directory structure (*.xlsx), but never a file which can be interpreted as rows of text.
0
     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;
                 }
            }

2 Comments

This will fail for non-string cells, eg formatted numbers
I think this answer could be improved by also adding an explanation. Just a code sample alone is not helpful, and could lead people to using it in inappropriate circumstances.
0

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.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.