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)