SpringBoot 上传 Excel 并读取为 JSON [英] SpringBoot Upload Excel and read as JSON
问题描述
你能帮我吗?如何使用 map() 获取不同的数据类型，如 String、Integers、Boolean、Double、Date 等，并接受我使用的空字符串 .map(cell::getstringcellvalue).它抛出错误无法从数字单元格中获取字符串，因为它只接受字符串，而且我想传入对象，因为我仅将其测试为动态
这是我的代码:
-- Entity.java --
公共类分支{
@Column(name = "branch_code", nullable = false, length = 10)私人字符串分支代码；@Column(name = "branch_desc", nullable = false, length = 100)私有字符串分支描述；@OneToMany(mappedBy = "branch", 级联 = CascadeType.ALL, fetch = FetchType.LAZY)@JsonIgnore私人列表<用户>用户;公共分支(字符串分支代码，字符串分支描述，列表<用户>用户){极好的();this.branchCode = 分支代码；this.branchDescription = 分支描述；this.user = 用户；}公共分支(){极好的();}公共字符串 getBranchCode() {返回分支代码；}public void setBranchCode(String branchCode) {this.branchCode = 分支代码；}公共字符串 getBranchDescription() {返回分支描述；}public void setBranchDescription(String branchDescription) {this.branchDescription = 分支描述；}公共列表<用户>获取用户(){返回用户；}public void setUser(List user) {this.user = 用户；} }
--UploadUtils.java--
公共类 UploadUtils {
public Supplier>getRowStreamSupplier(Iterable rows) {返回 () ->获取流(行)；}公共 <T>流<T>getStream(Iterable iterable) {返回 StreamSupport.stream(iterable.spliterator(), false);}公共供应商cellIteratorSupplier(int end) {返回 () ->数字流(结束)；}公共流<整数>numberStream(int end) {返回 IntStream.range(0, end).boxed();}
}
-- ServiceFile.java --
public List> 上传(MultipartFile 文件)抛出异常 {
路径 tempDir = Files.createTempDirectory("");文件 tempFile = tempDir.resolve(file.getOriginalFilename()).toFile();file.transferTo(tempFile);工作簿工作簿 = WorkbookFactory.create(tempFile);Sheet sheet = workbook.getSheetAt(0);供应商<流<行>>rowStreamSupplier = uploadUtils.getRowStreamSupplier(sheet);行 headerRow = rowStreamSupplier.get().findFirst().get();列表<字符串>headerCells = uploadUtils.getStream(headerRow).map(Cell::getStringCellValue).collect(Collectors.toList());int colCount = headerCells.size();返回 rowStreamSupplier.get().跳过(1).map(行 -> {列表<字符串>cellList = uploadUtils.getStream(row).map(Cell::getStringCellValue).collect(Collectors.toList());返回 uploadUtils.cellIteratorSupplier(colCount).得到().collect(toMap(headerCells::get, cellList::get));}).collect(Collectors.toList());}-- Controller.java --
@RequestMapping(value = "/upload", method = RequestMethod.POST)public List>上传(MultipartFile 文件)抛出异常{返回员工服务.上传(文件)；} 如果目标是 JSON，那么最佳实践是将所有 Excel 单元格内容作为格式化字符串.这可以使用 DataFormatter 来实现来自 apache poi.方法 DataFormatter.formatCellValue 从单元格中获取格式化的字符串.此字符串看起来与 Excel 相同，在其 GUI 中显示单元格值.如果单元格内容不是文本而是数字、日期或布尔值，则 DataFormatter 相应地将此内容格式化为字符串.如果在 Excel 中使用公式，则 FormulaEvaluator 是额外需要的.
因此对于您的代码，您应该使用:
<预><代码>...DataFormatter formatter = new DataFormatter();...工作簿工作簿 = WorkbookFactory.create(tempFile);FormulaEvaluator 评估器 = workbook.getCreationHelper().createFormulaEvaluator();...然后
.map((cell) -> formatter.formatCellValue(cell, evaluator))代替
.map(Cell::getStringCellValue)在行的单元流中.
完整的工作示例:
import org.apache.poi.ss.usermodel.*;导入 java.io.File;导入 java.util.*;导入 java.util.function.*;导入 java.util.stream.*;公共类 ReadExcelUsingStreams {public static void main(String[] args) 抛出异常 {DataFormatter formatter = new DataFormatter();File file = new File("Excel.xlsx");工作簿工作簿 = WorkbookFactory.create(file);FormulaEvaluator 评估器 = workbook.getCreationHelper().createFormulaEvaluator();Sheet sheet = workbook.getSheetAt(0);供应商<流<行>>rowStreamSupplier = UploadUtils.getRowStreamSupplier(sheet);行 headerRow = rowStreamSupplier.get().findFirst().get();列表<字符串>headerCells = UploadUtils.getStream(headerRow).map(Cell::getStringCellValue).collect(Collectors.toList());int colCount = headerCells.size();列表<地图<字符串，字符串>>内容 = rowStreamSupplier.get().跳过(1).map(行 ->{列表<字符串>cellList = UploadUtils.getStream(row).map((cell) -> formatter.formatCellValue(cell, evaluator)).collect(Collectors.toList());返回 UploadUtils.cellIteratorSupplier(colCount).得到().collect(Collectors.toMap(headerCells::get, cellList::get));}).collect(Collectors.toList());System.out.println(内容);workbook.close();}}类 UploadUtils {静态供应商<Stream<Row>>getRowStreamSupplier(Iterable rows) {返回 () ->获取流(行)；}静态 <T>流<T>getStream(Iterable iterable) {返回 StreamSupport.stream(iterable.spliterator(), false);}静态供应商cellIteratorSupplier(int end) {返回 () ->数字流(结束)；}静态流<整数>numberStream(int end) {返回 IntStream.range(0, end).boxed();}}
由于您提到了空单元格，请注意:如中所述遍历行和单元格，Iterator 将只返回文件中定义的单元格.这主要是那些具有价值观或风格的人.真正的空单元格不会被迭代.因此，如果 Excel 工作表可能包含真正的空单元格，则无法使用使用 java.util.stream.* 的整个方法，因为它完全依赖于 Iterators.
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
Here's my code:
-- Entity.java --
public class Branch {
@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 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 --
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 --
@RequestMapping(value = "/upload", method = RequestMethod.POST)
public List<Map<String, String>> upload(MultipartFile file) throws Exception{
return employeeService.upload(file);
}
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();
...
and then
.map((cell) -> formatter.formatCellValue(cell, evaluator))
instead of
.map(Cell::getStringCellValue)
in the cell stream of the rows.
Complete working example:
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();
}
}
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屋！
