kostyan_SV kostyan_SV - 1 month ago 18
Java Question

How to bind HTML form with file input on it and Spring RestController on backend (no JS)

I have Spring RestController with autowired JpaRepository and H2 embedded database on the backend.

@RestController
public class BooksController {

@Autowired
private BookRepository repository;

// POST method will be here
}


UI is a simple form with
<input type="file">
tag

<form class="form-horizontal" method="POST" action="/book/upload" enctype="multipart/form-data">
<div class="form-group">
<label for="fileInput">Specify path to XLS file:</label>
<input type="file" name="file" id="fileInput"
accept=".csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel">
<input type="submit" class="btn btn-primary" value="Upload">
</div>
</form>


I need a method where I can proccess files from input form. How can I bind UI input form with RestController? Is there a special libs for working with XLS files on the backend?

The main goal is parsing of XLS files and store in DB separate column data.

Answer

The solution was to use MultipartFile as RequestParam, consumes as MULTIPART_FORM_DATA_VALUE in RestController's method.

@RequestMapping(value = "/book/upload",
        method = RequestMethod.POST,
        consumes = MediaType.MULTIPART_FORM_DATA_VALUE,
        produces = MediaType.APPLICATION_JSON_VALUE)
public String upload(@RequestParam(value = "file") MultipartFile file) throws IOException {

    HashMap<String, List<String>> data = ReadExcelUtil.readData(file);
    StringBuilder response = new StringBuilder();
    data.forEach((String key, List<String> value) -> {
        response.append("Data for ").append(key).append(" column:\n");
        value.forEach((String val) -> response.append(val).append("\n"));
    });
    return response.toString();
}

For working with XLS files ReadExcelUtil was implemented. It contains readData method which returns HashMap where keys - values form first row (Titles), values - list of data for current column.

public class ReadExcelUtil {

    public static HashMap<String, List<String>> readData(MultipartFile file) {

        HashMap<String, List<String>> result = new HashMap<>();
        InputStream inputStream;
        try {
            inputStream = file.getInputStream();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        Workbook workbook = null;
        String extension = FilenameUtils.getExtension(file.getOriginalFilename());
        switch (extension.toLowerCase()) {
            case "xls": {
                try {
                    workbook = new HSSFWorkbook(inputStream);
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
                break;
            }
            case "xlsx": {
                try {
                    workbook = new XSSFWorkbook(inputStream);
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
                break;
            }
            default:
                throw new RuntimeException("Unsupported file extension. Valid are XLS and XLSX.");
        }

        Sheet sheet = workbook.getSheetAt(0);
        for (int i = 0; i < sheet.getRow(0).getLastCellNum(); i++) {
            final int cellIndex = i;
            final List<String> value = new ArrayList<>();
            final String[] key = {""};
            sheet.forEach((Row row) -> {
                row.forEach((Cell cell) -> {
                    final int rowIndex = row.getRowNum();
                    if (cellIndex == cell.getColumnIndex()) {
                        if (rowIndex == 0) {
                            key[0] = getCellValue(cell);
                        } else {
                            value.add(getCellValue(cell));
                        }
                    }
                });
                result.put(key[0], value);
            });
        }
        return result;
    }

    protected static String getCellValue(Cell cell) {

        String result = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    result = String.valueOf(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    result = String.valueOf(cell.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_STRING:
                    result = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_BLANK:
                    break;
                case Cell.CELL_TYPE_ERROR:
                    break;
            }
        }
        return result;
    }
}