에라모르겠다(‘◇’)?
[springboot] 엑셀 파일 업로드 업로드된 데이터 db 데이터 insert 본문
참고 : https://stackoverflow.com/questions/50849800/how-to-read-excel-file-using-spring-boot
How to read excel file using spring boot
I am making a spring boot application which will take the excel file and store its content and store it in database. I have tried many ways..but not successful. Does anyone have an idea about how t...
stackoverflow.com
* 원래 게시판 만들라다 급 유저로 변경함..그래서 패키지명 이상한데 흐린눈...ㅇㅅㅇ^^
1. DB 테이블 생성(my-sql)
create table excel_test(
idx int auto_increment ,
user_name varchar(100),
user_email varchar(100),
user_id varchar(100),
user_phone varchar(100),
user_type varchar(100),
reg_date timestamp default current_timestamp
,primary key(idx)
);
2. pom.xml 추가
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.12</version>
</dependency>
<!-- excel 2007 over-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.12</version>
</dependency>
3. MainController / main.html / success.html 작성 (메인화면에서 파일 업로드 -> 성공 화면으로 돌아감 )
(1) mainContrller
package com.study.excel.controller;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
@Controller
public class MainController {
@GetMapping("/")
public String main() {
return "main"; //메인화면
}
@GetMapping("/success")
public String sucess() {
return "success"; //성공화면
}
}(2) main.html
<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>메인화면</title>
</head>
<body>
<h1>메인입니당 엑셀 업로드 테스트~</h1>
<form th:action="@{/test/addExcel}" method="POST" enctype="multipart/form-data">
<input type="file" th:name="file">
<input th:type="submit" value="업로드 !" />
</form>
</body>
</html>엑셀 양식 폼
excel_test.xlsx
0.01MB
4. DTO
package com.study.excel.board.dto;
import lombok.Data;
@Data
public class ExcelTestDTO {
private String userName;
private String userEmail;
private String userId;
private String userPhone;
private String userType;
}
5. Controller
package com.study.excel.board.controller;
import java.io.IOException;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import com.study.excel.board.dto.ExcelDTO;
import com.study.excel.board.dto.ExcelTestDTO;
import com.study.excel.service.ExcelTestService;
import lombok.RequiredArgsConstructor;
@Controller
@RequiredArgsConstructor
@RequestMapping("/test")
public class ExcelTestController {
private final ExcelTestService ets;
@PostMapping("addExcel")
public String readExcel(@RequestParam("file") MultipartFile file, Model model)
throws IOException {
XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
XSSFSheet worksheet = workbook.getSheetAt(0);
for(int i=1;i<worksheet.getPhysicalNumberOfRows() ;i++) {
ExcelTestDTO excel = new ExcelTestDTO();
DataFormatter formatter = new DataFormatter();
XSSFRow row = worksheet.getRow(i);
String userName = formatter.formatCellValue(row.getCell(0));
String userEmail = formatter.formatCellValue(row.getCell(1));
String userId = formatter.formatCellValue(row.getCell(2));
String userPhone = formatter.formatCellValue(row.getCell(3));
String userType = formatter.formatCellValue(row.getCell(4));
excel.setUserName(userName);
excel.setUserEmail(userEmail);
excel.setUserId(userId);
excel.setUserPhone(userPhone);
excel.setUserType(userType);
ets.insertExcel(excel);
}
return "redirect:/success";
}
}6. Service / ServiceImpl
1. ExcelTestService
package com.study.excel.board.service;
import com.study.excel.board.dto.ExcelTestDTO;
public interface ExcelTestService {
//엑셀 등록
public int insertExcel(ExcelTestDTO excel) ;
}
2. ExcelTestServiceImpl
package com.study.excel.board.service;
import org.springframework.stereotype.Service;
import com.study.excel.board.dto.ExcelTestDTO;
import com.study.excel.board.mapper.ExcelTestMapper;
import lombok.RequiredArgsConstructor;
@Service
@RequiredArgsConstructor
public class ExcelTestServiceImpl implements ExcelTestService {
private final ExcelTestMapper etm;
@Override
public int insertExcel(ExcelTestDTO excel) {
return etm.insertExcel(excel);
}
}7. Mapper
package com.study.excel.board.mapper;
import org.apache.ibatis.annotations.Mapper;
import com.study.excel.board.dto.ExcelTestDTO;
@Mapper
public interface ExcelTestMapper {
// 엑셀 등록
public int insertExcel(ExcelTestDTO excel) ;
}8. mapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.study.excel.board.mapper.ExcelTestMapper">
<insert id="insertExcel" parameterType="com.study.excel.board.dto.ExcelTestDTO">
INSERT INTO excel_test
(
user_name,
user_email,
user_id,
user_phone,
user_type,
reg_date
)
VALUES
(#{userName}
,#{userEmail}
,#{userId}
,#{userPhone}
,#{userType}
,NOW()
)
</insert>
</mapper>-- 결과
-- db 확인
추가로 튜닝해서 확장자가 엑셀이 맞는지 확인하는 코드랑
엑셀 파일이 없는 경우 파일을 선택해달라는 alert창 띄우는
스크립트 등 추가할거 추가해서 사용하면 될듯?
간단하게 db에 insert 되는 코드 !
피드백 및 기타 댓글 환영합니다 !
'Language > Java' 카테고리의 다른 글
| [spring] zip bomb detected ! The file would exceed max 에러 발생 해결방법 (0) | 2023.07.28 |
|---|---|
| [spring] DTO 사용하여 (object) 엑셀 HEADER, VALUE 만들기 (0) | 2023.07.17 |
| [springboot] HTTP 요청 (REST API) 참고용 (0) | 2023.07.10 |
'Language/Java' Related Articles
more
