将数据保存到表中并将其保存到数据库，即使EXCEL java POI Apache中为空行 [英] Save the data to table and save it to database even if empty rows in EXCEL java POI Apache

问题描述

我不知道如何将内容保存在数据库中，我尝试导入可以正常工作的excel，但是如果excel的行之一为空，则会出现java.lang.IndexOutOfBoundsException

I dont know how to save the content in database, I try to import the excel it works but if the excel one of the rows are empty it appears java.lang.IndexOutOfBoundsException

-这是我的代码-

-BranchEntity.java-

-- BranchEntity.java --

公共类分支{

private static final long serialVersionUID = 1L;

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

}

-BranchService.java ---

-- BranchService.java ---

@自动连线 私有BranchRepository branchRepository;

@Autowired private BranchRepository branchRepository;

公共列表> uploadEmployee(MultipartFile multip)引发异常{

public List> uploadEmployee(MultipartFile multip) throws Exception {

    DataFormatter formatter = new DataFormatter();

    String fileNames = multip.getOriginalFilename();

    File file = new File("./reports/" + fileNames);
    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());

    branchRepository.save(content);

    workbook.close();

    return content;

}

-BranchController.java-

-- BranchController.java --

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

推荐答案

为避免java.lang.IndexOutOfBoundsException，您需要避免使用java.util.stream.*方法，因为这完全取决于使用Iterator s.但是apache poi的忙碌的开发人员指南-> 遍历行和单元格告诉:

To avoid the java.lang.IndexOutOfBoundsException you need to avoid the java.util.stream.* approach since this fully depends on using Iterators. But apache poi's Busy Developers' Guide -> Iterate over rows and cells tells:

请注意，rowIterator和cellIterator遍历行或单元格 已创建的文件，跳过空的行和单元格.

Note that a rowIterator and cellIterator iterate over rows or cells that have been created, skipping empty rows and cells.

因此不对空行和单元格进行迭代.这就是为什么不可能从cellIterator获取空单元并且失败的原因.

So empty rows and cells are not iterated. That's why getting a empty cell from a cellIterator is impossible and fails.

为避免这种情况，我们应该执行以下操作:

To avoid that we should do the following:

首先将列标题作为标题名称的Map收集到列索引.这可以通过在第一行上使用单元迭代器来完成.如果此处的单元格为空，则将跳过此列.

First collect the column headers as a Map of header names to column indexes. This can be done using a cell iterator over the first row. If cells are empty there, then this column will be skipped.

然后将内容收集到所需的List个Map中，其中单元格值映射到标头名称.必须使用第一个标题列索引到最后一个标题列索引的for循环为每一行完成此操作.如果单元格为空，则可以将单元格创建为空单元格.因此DataFormatter然后将其值作为空字符串获取.

Then collect the content into your needed List of Maps where cell values are mapped to header names. This must be done for each row using a for loop from first header column index to last header column index. If cells are empty, then the cell can be created as empty cell. So DataFormatter gets it's value as empty string then.

完整的工作示例:

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

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

public class ReadExcelToCollection {

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

  int headerRowNum = sheet.getFirstRowNum();

  // collecting the column headers as a Map of header names to column indexes
  Map<Integer, String> colHeaders = new HashMap<Integer, String>();
  Row row = sheet.getRow(headerRowNum);
  for (Cell cell : row) {
   int colIdx = cell.getColumnIndex();
   String value = formatter.formatCellValue(cell, evaluator);
   colHeaders.put(colIdx, value);
  }

  System.out.println(colHeaders);

  // collecting the content into List of Maps where cell values are mapped to header names.
  List<Map<String, String>> content = new ArrayList<Map<String, String>>();
  for (int r = headerRowNum + 1; r <= sheet.getLastRowNum(); r++) {
   row = sheet.getRow(r); if (row == null) row = sheet.createRow(r);
   Map<String, String> valuesToHeaders = new HashMap<String, String>();
   for (Map.Entry<Integer, String> entry : colHeaders.entrySet()) {
    int colIdx = entry.getKey();
    Cell cell = row.getCell(colIdx); if (cell == null) cell = row.createCell(colIdx);
    String cellValue = formatter.formatCellValue(cell, evaluator);
    valuesToHeaders.put(entry.getValue(), cellValue);
   }
   content.add(valuesToHeaders);
  }

  System.out.println(content);

  workbook.close();
 }
}

这篇关于将数据保存到表中并将其保存到数据库，即使EXCEL java POI Apache中为空行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！