[Spring] Apache POI 를 이용한 엑셀 파일 읽기
2022. 2. 28. 22:40
반응형
[Spring] Apache POI 를 이용한 엑셀 파일 읽기
✨ 의존성
- Spring Boot
- Spring Web
- Thymeleaf
- Lombok
1. Apache POI , Tika 관련 의존성 추가
maven일 경우 pom.xml
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.tika/tika-core -->
<dependency>
<groupId>org.apache.tika</groupId>
<artifactId>tika-core</artifactId>
<version>2.3.0</version>
</dependency>
gradle일 경우 build.gradle
// https://mvnrepository.com/artifact/org.apache.poi/poi
implementation group: 'org.apache.poi', name: 'poi', version: '4.1.2'
implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '4.1.2'
// https://mvnrepository.com/artifact/org.apache.tika/tika-core
implementation group: 'org.apache.tika', name: 'tika-core', version: '2.3.0'spring boot라면 version 입력을 하지 않아도 될 것이다.
2. 파일 입력 폼
resources/templates/index.html
<!DOCTYPE HTML>
<html lang="ko" xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org"
xmlns:layout="http://www.ultraq.net.nz/thymeleaf/layout">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
</head>
<body>
<form th:action="@{/excel/read}" method="POST" enctype="multipart/form-data">
<input type="file" th:name="file1">
<input th:type="submit" value="제출" />
</form>
</body>
</html>
3. 객체
ExcelData.java
package model;
import lombok.*;
import javax.persistence.*;
import java.util.ArrayList;
import java.util.List;
@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
@Builder
public class ExcelData {
private int num;
private String name;
}
4. 컨트롤러
ExcelDataController.java
import model.ExcelData;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.tika.Tika;
import org.apache.tika.exception.TikaException;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
@Controller
public class ExcelDataController {
@PostMapping("/excel/read")
public String readExcel(@RequestParam("file") MultipartFile file, Model model) throws TikaException, IOException { // 2
List<ExcelData> dataList = new ArrayList<>();
try (InputStream is = file.getInputStream();) {
Tika tika = new Tika();
String mimeType = tika.detect(is);
if (isAllowedMIMEType(mimeType)) {
Workbook workbook = new XSSFWorkbook(file.getInputStream());
Sheet worksheet = workbook.getSheetAt(0);
String atchFileId = null;
for (int i = 1; i < worksheet.getPhysicalNumberOfRows(); i++) { // 1번째 행부터 끝까지
Row row = worksheet.getRow(i);
ExcelData data = new ExcelData();
data.setNum((int) row.getCell(0).getNyumericCellValue());
data.setName(row.getCell(1).getStringCellValue());
dataList.add(data);
}
model.addAttribute("list", dataList);
} else {
throw new IOException();
}
} catch (Exception e) {
throw new TikaException("ERROR");
}
return "list";
}
private boolean isAllowedMIMEType(String mimeType) {
if (mimeType.equals("application/x-tika-ooxml"))
return true;
return false;
}
}MultipartFile
Spring 환경이라면 Spring에서 제공하고 있는 MultipartFile 클래스와 MultipartHttpServletRequest 클래스를 사용해서 File 업로드 기능을 구현할 수 있다. 클라이언트에서 서버로 HTTP 요청을 할 때, Content-Type 필드의 내용을 multipart/form-data로 요청한다.
@RequestParam 어노테이션과 함께 MultipartFile 타입을 사용한다.
Apache Tika 라이브러리
Apache Tika 를 사용하여 MIME TYPE를 체크하였다.
5. 리스트 화면
resources/templates/list.html
<html xmlns="http://www.w3.org/1999/xhtml"
xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Title</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css">
</head>
<body>
<table class="table table-striped">
<thead>
<tr>
<th scope="col">#</th>
<th scope="col">번호</th>
<th scope="col">이름</th>
</tr>
</thead>
<tbody>
<tr th:each="data : ${datas}" >
<td th:text="${dataStat.index}"></td>
<td th:text="${data.num}"></td>
<td th:text="${data.name}"></td>
</tr>
</tbody>
</table>
</body>
</html>Stat을 사용해서 상태변수 (index)를 접근하였다.
참고
https://stackoverflow.com/questions/50849800/how-to-read-excel-file-using-spring-boot
https://caileb.tistory.com/152
728x90
반응형
'web > Spring' 카테고리의 다른 글
| [Spring Boot] @ConfigurationProperties 사용법 (0) | 2022.03.07 |
|---|---|
| [Spring MVC] Thymeleaf 반복문 처리, 상태변수 접근 (index) (0) | 2022.03.01 |
| [Spring Mybatis] resultMap (0) | 2022.02.11 |
| [Spring Boot] 기본 포트 설정하는 방법 (0) | 2022.01.26 |
| [Spring MVC] Thymeleaf 타임리프 기본 기능- 텍스트 text, utext (0) | 2022.01.18 |
Written by ner.o
개발자 네로의 개발 일기,
자바를 좋아합니다 !
댓글 개