如何在Java中将Excel行转换为字符串值？

Question

Java POI中是否有直接内置函数将Excel行转换为字符串？ 我尝试了“row.toString（）”，但它返回了该行的内存位置

Answer 1

您可以尝试在java中使用Files类。它有一些方法，例如

Files.lines(Path path)

接收路径并返回流的。使用流，您可以拆分行并应用您的方法。您可以查看它的API，了解它的其他方法，无论哪种方法更适用

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

Answer 2

不要遍历所有单元格/行，只需通过以下方式选择特定单元格：

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

Answer 3

你可以在单元格上迭代

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

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

对于检索单元格字符串，您可以使用：

cell.getStringCellValue()

Answer 4

     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;
                 }
            }

Answer 5

需要执行此操作的所有代码都在Apache POI documentation中给出，特别是iterating over rows and cells和getting the cell contents。阅读文档通常会有所帮助

假设您想做一些简单的事情，比如用逗号将所有单元格连接在一起（注意，这不是生成CSV的好方法，因为您还需要转义！）你可以这样做：

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

Answer 6

您可以使用java8foreach来获得可能更方便的代码：

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

StringJoiner是连接值的好选择。对于获取单元格值，toString可以正常工作。toString将获得任何单元格类型的正确值。唯一的问题是toString不会解析公式，它会得到公式本身。但是您可以根据自己的需要修改代码