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