SpringBoot Upload Excel and read as JSON

Question

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

Answer 1

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.