Save the data to table and save it to database even if empty rows in EXCEL java POI Apache

Question

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

-- Here's my code --

-- BranchEntity.java --

public class Branch {

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

@Autowired private BranchRepository branchRepository;

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

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

Axel Richter · Accepted Answer · 2019-11-21 13:49:53Z

1

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:

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

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:

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.

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.

Complete working example:

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

answered Nov 21, 2019 at 13:49

Axel Richter

62.5k7 gold badges70 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bryan Caliwag Over a year ago

Amazing Sir @Axel thank you for resolving not empty rows but I have a problem how to insert it the content to Object in Branch in short converting map to object? To save it to database? Do you know spring sir @Axel?

Axel Richter Over a year ago

@Bryan Caliwag: You was told already how Stackoverflow works. One question per question block. Not multiple questions per question block. And in no case: I've put some code in the question block. Please do my work for me now. As far as i see, the branchRepository.save(content); should do the insert the content to Branch objects, shouldn't it?

Bryan Caliwag Over a year ago

Yes sir @Axel it must save to Branch, Okay I will create another question Thank you very much I appreciate your effort ^_^

Collectives™ on Stack Overflow

Save the data to table and save it to database even if empty rows in EXCEL java POI Apache

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related