java - SpringBoot上传Excel并读取为JSON

Question

你能帮我一下吗？如何使用map()获取不同的数据类型，如字符串、整数、 boolean 值、 double 、日期等，并接受空字符串我使用.map(cell::getstringcellvalue)。它抛出错误无法从数字单元格获取字符串，因为它唯一接受字符串，而且我想传递对象，因为我仅将其测试为动态

这是我的代码:

-- Entity.java --

公开课分支{

@Column(name = "branch_code", nullable = false, length = 10)
private String branchCode;

@Column(name = "branch_desc", nullable = false, length = 100)
private String branchDescription;

@OneToMany(mappedBy = "branch", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JsonIgnore
private List<User> user;

public Branch(String branchCode, String branchDescription, List<User> user) {
    super();
    this.branchCode = branchCode;
    this.branchDescription = branchDescription;
    this.user = user;
}

public Branch() {
    super();
}

public String getBranchCode() {
    return branchCode;
}

public void setBranchCode(String branchCode) {
    this.branchCode = branchCode;
}

public String getBranchDescription() {
    return branchDescription;
}

public void setBranchDescription(String branchDescription) {
    this.branchDescription = branchDescription;
}

public List<User> getUser() {
    return user;
}

public void setUser(List<User> user) {
    this.user = user;
}

}

--UploadUtils.java--

公共(public)类UploadUtils{

public Supplier<Stream<Row>> getRowStreamSupplier(Iterable<Row> rows) {
    return () -> getStream(rows);
}

public <T> Stream<T> getStream(Iterable<T> iterable) {
    return StreamSupport.stream(iterable.spliterator(), false);
}

public Supplier<Stream<Integer>> cellIteratorSupplier(int end) {
    return () -> numberStream(end);
}

public Stream<Integer> numberStream(int end) {
    return IntStream.range(0, end).boxed();
}

}

-- ServiceFile.java --

公共(public)列表>上传(MultipartFile文件)抛出异常{

    Path tempDir = Files.createTempDirectory("");

    File tempFile = tempDir.resolve(file.getOriginalFilename()).toFile();

    file.transferTo(tempFile);

    Workbook workbook = WorkbookFactory.create(tempFile);

    Sheet sheet = workbook.getSheetAt(0);

    Supplier<Stream<Row>> rowStreamSupplier = uploadUtils.getRowStreamSupplier(sheet);

    Row headerRow = rowStreamSupplier.get().findFirst().get();

    List<String> headerCells = uploadUtils.getStream(headerRow)
            .map(Cell::getStringCellValue) 
            .collect(Collectors.toList());

    int colCount = headerCells.size();

    return rowStreamSupplier.get()
            .skip(1)
            .map(row -> {

                List<String> cellList = uploadUtils.getStream(row)
                        .map(Cell::getStringCellValue)
                        .collect(Collectors.toList());  

                return uploadUtils.cellIteratorSupplier(colCount)
                         .get()
                         .collect(toMap(headerCells::get, cellList::get));
    })
    .collect(Collectors.toList());
}

-- Controller.java --

@RequestMapping(value = "/upload", method = RequestMethod.POST)
public List<Map<String, String>> upload(MultipartFile file) throws Exception{
    return employeeService.upload(file);
}

Answer 1

如果目标是 JSON，那么最佳实践是将所有 Excel 单元格内容获取为格式化字符串。这可以使用 DataFormatter 来实现来自apache poi。方法DataFormatter.formatCellValue从单元格获取格式化字符串。该字符串看起来与 Excel 在其 GUI 中显示的单元格值相同。如果单元格内容不是文本而是数字、日期或 boolean 值，则 DataFormatter 会相应地将此内容格式化为字符串。如果在 Excel 中使用公式，则 FormulaEvaluator需要额外的。

因此，对于您的代码，您应该使用:

...
DataFormatter formatter = new DataFormatter();
...
Workbook workbook = WorkbookFactory.create(tempFile);
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
...

然后

.map((cell) -> formatter.formatCellValue(cell, evaluator))

而不是

.map(Cell::getStringCellValue)

在行的单元格流中。

完整的工作示例:

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

import java.io.File;
import java.util.*;
import java.util.function.*;
import java.util.stream.*;

public class ReadExcelUsingStreams {

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

  DataFormatter formatter = new DataFormatter();

  File file = new File("Excel.xlsx");
  Workbook workbook = WorkbookFactory.create(file);

  FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

  Sheet sheet = workbook.getSheetAt(0);

  Supplier<Stream<Row>> rowStreamSupplier = UploadUtils.getRowStreamSupplier(sheet);

  Row headerRow = rowStreamSupplier.get().findFirst().get();
  List<String> headerCells = UploadUtils.getStream(headerRow)
   .map(Cell::getStringCellValue) 
   .collect(Collectors.toList());

  int colCount = headerCells.size();

  List<Map<String, String>> content = rowStreamSupplier.get()
   .skip(1)
   .map(row -> 
    {
     List<String> cellList = UploadUtils.getStream(row)
      .map((cell) -> formatter.formatCellValue(cell, evaluator))
      .collect(Collectors.toList());  
     return UploadUtils.cellIteratorSupplier(colCount)
      .get()
      .collect(Collectors.toMap(headerCells::get, cellList::get));
    })
   .collect(Collectors.toList());

  System.out.println(content);

  workbook.close();
 }
}

class UploadUtils {

 static Supplier<Stream<Row>> getRowStreamSupplier(Iterable<Row> rows) {
    return () -> getStream(rows);
 }

 static <T> Stream<T> getStream(Iterable<T> iterable) {
    return StreamSupport.stream(iterable.spliterator(), false);
 }

 static Supplier<Stream<Integer>> cellIteratorSupplier(int end) {
    return () -> numberStream(end);
 }

 static Stream<Integer> numberStream(int end) {
    return IntStream.range(0, end).boxed();
 }
}

由于您提到了空单元格，请注意:如 Iterate over rows and cells 中所述Iterator 将仅返回文件中定义的单元格。这主要是那些有值(value)观或风格的人。真正的空单元格不会被迭代。因此，如果 Excel 工作表可能包含真正的空单元格，则无法使用使用 java.util.stream.* 的整个方法，因为它完全依赖于 Iterators.