[Spring Boot] Reading spreadsheets with Apache POI

Intro

In this time, I will try reading spreadsheets.
These spreadsheets are sent from the client-side to the server-side.

Sending spreadsheets

First, I will change the sending files function.
To read spreadsheets in the server-side, I will send them as "multipart/form-data".

index.page.ts

...
    sendFile() {
        const fileInput = document.getElementById("selected_file_input") as HTMLInputElement;
        if (fileInput?.files == null || fileInput.files.length <= 0) {
            return;
        }
        const file = fileInput.files[0]!;
        const reader = new FileReader();
        reader.onload = () => {
            const data = reader.result;
            if (data == null || typeof (data) === "string") {
                return;
            }
            const formData = new FormData();
            formData.append("file", new Blob([data]), encodeURI(file.name));
            fetch("http://localhost:8080/files", {
                mode: "cors",
                method: "POST",
                headers: {
                    // remove "Content-Type"
                },
                body: formData
            })
            .then(res => res.json())
            .then(res => console.log(res))
            .catch(err => console.error(err));
        }
        reader.readAsArrayBuffer(file);
    }
}

FileController.java

...
    @PostMapping("/files")
    public ActionResult uploadFile(HttpServletRequest request, @RequestBody MultipartFile file) {
        if (file == null) {
            return ActionResult.getFailedResult("Failed uploading");
        }
        return files.startGenerating(file);
    }
}

Reading spreadsheets

Adding Apache POI

To read spreadsheets, I will add Apache POI into "build.gradle".

build.gradle

...
dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
    implementation 'org.springframework.boot:spring-boot-starter'
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.apache.poi:poi:5.2.5'
    implementation 'org.apache.poi:poi-ooxml:5.2.5'

    developmentOnly 'org.springframework.boot:spring-boot-devtools'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
}
...

Opening and reading a spreadsheet

SpreadsheetEditor.java

package jp.masanori.springbootsample.files;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.util.Iterator;
import java.util.Optional;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.web.multipart.MultipartFile;

public class SpreadsheetEditor {
    public static Optional<String> edit(MultipartFile file) {
        try {
            System.out.println(URLDecoder.decode(file.getOriginalFilename(), "UTF-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            // To search Sheet, Row, and Cell by their names, I should get Iterators and search them.
            Sheet sheet = getSheetByName(wb, "SampleSheet");
            if (sheet == null) {
                return Optional.empty();
            }
            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
                Row row = rows.next();
                Iterator<Cell> cells = row.cellIterator();
                while (cells.hasNext()) {
                    Cell cell = cells.next();
                    String cellValueType = cell.getCellType().name();
                    // To get cell values, I must use type-specific methods.
                    switch (cellValueType) {
                        case "STRING":
                            System.out.println("String: " + cell.getStringCellValue());
                            break;
                        case "NUMERIC":
                            System.out.println("Numeric: " + cell.getNumericCellValue());
                            break;
                        default:
                            break;
                    }
                }
            }
            return Optional.of("OK");
        } catch (FileNotFoundException e) {
            System.out.println(e.getMessage());
        } catch (IOException e) {
            System.out.println(e.getMessage());
        }
        return Optional.empty();
    }
    private static Sheet getSheetByName(Workbook book, String sheetName) {
        Iterator<Sheet> sheets = book.sheetIterator();
        while (sheets.hasNext()) {
            Sheet s = sheets.next();
            if (s.getSheetName().equals(sheetName)) {
                return s;
            }
        }
        return null;
    }
}

Blog