SpringBoot上传Excel并读取为JSON [英] SpringBoot Upload Excel and read as JSON
问题描述
你能帮我吗?如何使用map()获得不同的数据类型，例如String，Integers，Boolean，Double，Date等，并接受空字符串，我使用了.map(cell :: getstringcellvalue).它抛出错误，无法从数字单元格中获取字符串，因为它仅接受字符串，而且我也想传递对象，因为我仅将其测试为动态
Can you please help me? How to get different data types using map() like String, Integers, Boolean, Double, Date etc. and accepted empty string I used .map(cell::getstringcellvalue). It throws error cannot get a string from a numeric cell because its only accept string and also I want to pass in object because I testing it to dynamic only
这是我的代码:
-Entity.java-
-- 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-
--UploadUtils.java--
公共类UploadUtils {
public class 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-
-- ServiceFile.java --
公共列表>上传(MultipartFile文件)引发异常{
public List> upload(MultipartFile file) throws Exception {
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-
-- Controller.java --
@RequestMapping(value = "/upload", method = RequestMethod.POST)
public List<Map<String, String>> upload(MultipartFile file) throws Exception{
return employeeService.upload(file);
}
推荐答案
如果目标是 JSON ，则最佳实践是将所有 Excel 单元格内容作为格式字符串获取.可以使用 DataFormatter 实现来自 apache poi .方法 DataFormatter.formatCellValue 从单元格获取格式化的字符串.该字符串看起来与 Excel 相同，并且在 GUI 中显示了单元格值.如果单元格内容不是文本而是数字，日期或布尔值，则 DataFormatter 会将其相应地格式化为字符串.如果在 Excel 中使用了公式，则 FormulaEvaluator 是额外需要的.
If the goal is JSON then best practice is getting all Excel cell contents as formatted strings. This can be achieved using DataFormatter from apache poi. The method DataFormatter.formatCellValue gets a formatted string from the cell. This string looks the same as Excel shows the cell value in it's GUI. If cell content is not text but numeric, date or boolean, then DataFormatter formats this content to a string accordingly. If formulas are used in Excel, then a FormulaEvaluator is needed additional.
因此对于您的代码，您应该使用:
So for your code you should using:
...
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();
}
}
由于您提到了空单元格，请注意:如中所述遍历行和单元格， Iterator 将仅返回文件中定义的单元格.这主要是那些具有价值或风格的东西.真正的空单元格不会被迭代.因此，如果 Excel 工作表可能包含真正为空的单元格，则不能使用整个使用 java.util.stream.* 的方法，因为它完全依赖于 Iterator s.
Since you have mentioned empty cells, please note: As told in Iterate over rows and cells the Iterator will only return the cells defined in the file. This are largely those with values or styling. Really empty cells are not iterated. So if the Excel sheet may contain really empty cells, then this whole approach using java.util.stream.* cannot be used since it fully depends on Iterators.
这篇关于SpringBoot上传Excel并读取为JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
