1. spring boot DB 연동방법 및 예제 데이터 준비
2. 코드 작성
3. 기능 테스트
자바에서는 poi라는 라이브러리를 사용해서 엑셀 조작을 할 수 있다.
엑셀 조회나 수정 등 단순하게 조작하는 것 자체는 어렵지 않다.
다만 이걸 스프링에 적용했을 때... 가 문제란 말이지.
예를 들면 엑셀을 다운로드 할 때 서버에서 엑셀을 생성해서 클라이언트로 보내줄 때라던가?
아무튼 어렴풋이 방법은 알지만 이런건 다시 검색하느라 꽤 시간을 쓴단 말이지...
안 그래도 정리를 쭉 해둬야겠다 생각은 하고 있었는데 시간이 애매해서 미루고 있었다.
이번 기회에 Spring boot에서 poi 라이브러리를 사용해서 엑셀 파일을 조작하는 방법에 대해서 기록해둔다.
다만 기본적으로 로직에 대해 따로 설명하지는 않으니 이런식으로 사용하는구나 하고 참고만 합시다.
1. DB 연결 및 예제 데이터 준비
maven repository 사이트에 들어가서 poi를 검색합시다.
두 개의 버전을 동일하게 사용하면 된다.
복사한 내용을 build.gradle에 추가해주자.
예제에서는 글 작성 기준 최신 버전을 사용하고 있다.
추가로 이번 예제는 db에서 데이터를 불러와서 엑셀로 출력하는 것까지 다루고 있다.
db연동이 아직 안 되었다면 db dependency까지 추가해주자.
예제에서는 maria db를 사용하고 있다.
찾기 귀찮으면 복붙
/* poi */
// https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml/5.2.3
implementation 'org.apache.poi:poi:5.2.3'
implementation 'org.apache.poi:poi-ooxml:5.2.3'
/* maria db Connection */
implementation 'org.mariadb.jdbc:mariadb-java-client'
/* mybatis */
implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.2.2'
ㅇ maria db 설치
https://1545154.tistory.com/115
[설치가이드] Maria DB 설치
1. Maria db 10.2 download 2. MariaDB terminal에서 테이블 만들기 3. MariaDB database DBeaver 연결 99. HeidiSQL 연결 99. 10.11.2 install (실패기록) 흠... 개발 프로젝트에서 데이터베이는 MariaDB를 사용하라고 하네. 컴퓨
1545154.tistory.com
ㅇ maria db 연동
이건 전자정부 프레임워크 작업하면서 작성했던 글인데,
maven으로 dependency를 추가하는 것 이외에는 동일하다. application.yml부터 참고하면 된다.
https://1545154.tistory.com/119
[전자정부 프레임워크] 전자정부 프레임워크 스프링 부트 프로젝트 MariaDB 연동 후 데이터 출력까
1. pom.xml에 의존성 추가 2. application.properties 또는 application.yml에 db정보 작성 3. sql mapper작성 4. 작성한 mapper에 대한 bean 설정 이번 글에서는 전자정부 프레임워크 스트링 부트 프로젝트에서 MariaDB
1545154.tistory.com
ㅇ oracle db 연동
이건 내가 스프링 처음 공부하면서 작성했던 글인데......
잠깐 읽어봤더니 부끄럽네... 그렇다고 다시 작성하기는 귀찮지.
마찬가지로 maven으로 dependency를 추가하는 것 이외에는 동일하다. 적당히 참고하자.
https://1545154.tistory.com/70
[왕왕왕초보 Spring 실습] 4. 오라클 데이터 불러오기
작성순서 0. 참고 1. sqlmap 작성 2. pom.xml 작성 3. servlet-context.xml 수정 4. root-context.xml 작성 5. 빌드 후 실행 0. 참고 https://1545154.tistory.com/71 [왕왕왕초보 Spring 실습] 5. 실습 코드 참고 작성순서 1. foodCon
1545154.tistory.com
예제 데이터도 적당히 생성해주자.
만들기 귀찮으면 사용하기
CREATE TABLE excelData (
idx int(11) NOT NULL AUTO_INCREMENT, -- AUTO_INCREMENT,
excelA varchar(100) NOT NULL,
excelB varchar(100) NOT NULL,
excelC varchar(100) NOT NULL,
PRIMARY KEY (idx)
);
select * from excelData;
insert into excelData(excelA, excelB, excelC)
values('excelA1', 'excelB1', 'excelC1');
insert into excelData(excelA, excelB, excelC)
values('excelA2', 'excelB2', 'excelC2');
insert into excelData(excelA, excelB, excelC)
values('excelA3', 'excelB4', 'excelC4');
insert into excelData(excelA, excelB, excelC)
values('excelA4', 'excelB4', 'excelC4');
insert into excelData(excelA, excelB, excelC)
values('excelA5', 'excelB5', 'excelC5');
마지막으로 엑셀 템플릿도 작성해서 static 아래 템플릿 폴더를 만들고 저장해두자.
템플릿 저장 경로는 아래에 프로젝트 구성을 참고하면 된다.
만들기 귀찮으면 파일 다운로드해서 사용하자. 근데 이 정도 작업은 직접 만드는거랑 별 차이 없긴 한데...
2. 코드 작성
이번 예제에서는 작성해야 할 파일이 꽤 많지만 스프링 특성상 어쩔 수 없는 부분이다.
참고로 테스트 코드이므로 dao나 vo는 사용하지 않고 map으로 대체한다.
코드가 너무 길어서 사진을 올려봐야 글씨가 깨져서 그냥 코드만 올리기로 했다.
파일명을 확인하면서 아래 코드를 참고하자.
ㅇ ExcelPageController.java 작성
설명할 부분이 없다. api에 접근했을 때 html 페이지를 조회할 수 있도록 하자.
package excel.mh.controller;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import lombok.extern.slf4j.Slf4j;
@Slf4j
@Controller
public class ExcelPageController {
@GetMapping("/excel/showExcel")
public String showExcel() {
log.info("show!");
return "showExcel";
}
}
ㅇ showExcel.html 작성
마찬가지로 설명할 부분이 없다. 적당히 html 페이지를 작성해줍시다.
<!DOCTYPE HTML>
<html xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="utf-8" http-equiv="Content-Security-Policy" content="upgrade-insecure-requests">
<title>Excel SHOW TEST</title>
<link rel="stylesheet" href="/css/showExcel.css" />
</head>
<body>
<div id="menuBtn"> <div id="btnLeft"> <img src="/logo.gif"> </div> </div>
<div id="content">
<div class="subTitle"> <p style="font-size:30px;">excel search</p> </div>
<div class="excelListDiv"></div>
</div>
<div class="printDiv"></div>
<script src="/js/axios.min.js"></script>
<script src="/js/jquery.min.js"></script>
<script src="/js/function.js"></script>
<script>excelListTable();</script>
</body>
</html>
여기까지 하고 서버 실행 후 localhost/excel/showExcel > 화면 확인
ㅇ excelTable.xml 작성
서버가 정상적으로 올라왔으면 이제 db에 만들어둔 예제 데이터를 출력해보자.
mybatis에 대한 사용법을 따로 설명하지는 않는다.
<?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="excel.mh.service.databaseMapper">
<select id="searchExcelAllParam" resultType="hashMap">
select idx, excelA, excelB, excelC
from excelData
</select>
</mapper>
ㅇ databaseMapper.java
Mapper는 xml에서 작성했던 id와 동일하게 메서드를 만들고 타입은 List<Hashmap>으로 설정한다.
package excel.mh.service;
import java.util.HashMap;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface databaseMapper {
public List<HashMap<String, Object>> searchExcelAllParam();
}
ㅇ databaseService.java
database service에서도 매퍼에서 작성했던 메서드 타입과 동일하게 List<Hashmap>으로 설정한다.
다른 얘기지만 예전엔 코드 하나를 작성하는데 왜 이렇게 빙 돌아가야 하는지 이해를 못했다.
물론 지금이야 그렇게 쓰다보니 익숙해졌을 뿐이고 완전히 이해한 건 아니지만...
package excel.mh.service;
import java.util.HashMap;
import java.util.List;
import org.springframework.stereotype.Service;
import lombok.extern.slf4j.Slf4j;
@Slf4j
@Service
public class databaseService {
// DB mapper
private final databaseMapper databaseMapper;
public databaseService(databaseMapper databaseMapper) {
this.databaseMapper = databaseMapper;
}
public List<HashMap<String, Object>> searchExcel() {
log.info(">>> dbService... searchExcel search <<<");
List<HashMap<String, Object>> searchExcelAllParam = databaseMapper.searchExcelAllParam();
log.info(">>> fin <<<");
return searchExcelAllParam;
}
}
ㅇ ExcelApiController.java 작성
html 페이지에서 데이터를 요청받고 처리하는 컨트롤러를 작성하자.
그냥 하나의 컨트롤러... ExcelPageController에 작성해도 되는데 이건 그냥 내 습관이다 ㅋㅋㅋ
언제부터인가 코드가 길어지면 구분을 못하겠더라고... 나이가 들어서 그런가
이 컨트롤러에는 2개의 api가 작성되어 있다.
하나는 database에 저장되어 있는 데이터들을 조회하는 api와
다른 하나는 html 페이지에서 전송받은 데이터를 엑셀 파일로 만드는 api다.
코드에 대한 설명은 주석을 참고하고 excelService와 databaseService는 위에서 작성했던 코드를 확인해보자.
package excel.mh.controller;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.bind.annotation.*;
import excel.mh.service.excelService;
import excel.mh.service.databaseService;
import lombok.extern.slf4j.Slf4j;
@Slf4j
@CrossOrigin(origins = "*", allowedHeaders = "*") /* CORS 어노테이션 */
@RestController
public class ExcelApiController {
private final excelService excelService;
// DB service
private final databaseService databaseService;
public ExcelApiController(
excelService excelService,
databaseService databaseService) {
this.excelService = excelService;
this.databaseService = databaseService;
}
// 엑셀 다운로드
@GetMapping(value = "/api/excel/printexcel")
public void printExcelData(
HttpServletResponse response,
@RequestParam Map<String, String> excelList) throws NumberFormatException, IOException {
log.info("excel print!");
// html 페이지에서 요청받은 데이터를 엑셀 파일에 작성
Workbook wb = excelService.excelPrint(excelList);
// 컨텐츠 타입, 파일명 지정
response.setContentType("ms-vnd/excel");
response.setHeader("Content-Disposition", "attachment;filename=test.xlsx");
// 엑셀파일 저장
wb.write(response.getOutputStream());
wb.close();
}
// 데이터 조회
@GetMapping(value = "/api/excel/searchexcel")
public List<HashMap<String, Object>> searchExcelData() {
log.info("excel search!");
// database에 저장되어 있는 data를 List형태로 조회
List<HashMap<String, Object>> searchExcelAllParam = databaseService.searchExcel();
return searchExcelAllParam;
}
}
ㅇ function.js 작성
먼저 데이터를 조회해서 화면에 뿌려본다.
axios를 사용해서 ExcelApiController에 작성했던 api를 호출해서 데이터를 받아왔다.
/**************** 전역변수 ****************/
let urlPre = "https://localhost";
/**************** 조회 ****************/
function excelListTable(){
let url = urlPre+'/api/excel/searchexcel';
axios.get( url )
.then(function(res) {
let excelList = res.data;
$(".excelList").remove();
html = "";
html += "<div class='excelList'>";
html += "<div id='downloadBtn'>"
html += "<form name='excelFormData' id='targetForm' method='GET' action='/api/excel/printexcel' onsubmit='excelSubmit();'>"
html += "<input type='hidden' id='excelChk' name='excelChk' value=''/>"
html += "<input type='hidden' id='excelA' name='excelA' value=''/>"
html += "<input type='hidden' id='excelB' name='excelB' value=''/>"
html += "<input type='hidden' id='excelC' name='excelC' value=''/>"
html += "<input type='submit' id='targetBtn' value='EXCEL'/>"
html += "</form>"
html += "</div>"
html += "<table class='excelTable' style='width:100%;' border='1'>"
html += "<tr>"
html += "<th><input type='checkbox' name='mastarChkbox' onclick=excelChkFunction(this)></th>"
html += "<th>idx</th>"
html += "<th>excelA</th>"
html += "<th>excelB</th>"
html += "<th>excelC</th>"
html += "</tr>"
excelList.forEach((element) => {
let excelParams = element.excelA + "-" + element.excelB + "-" + element.excelC;
html += "<tr>"
html += "<th><input type='checkbox' name='chkbox' id="+ element.idx + " value="+ excelParams + "></th>";
html += "<th>"+element.idx+"</th>"
html += "<th>"+element.excelA+"</th>"
html += "<th>"+element.excelB+"</th>"
html += "<th>"+element.excelC+"</th>"
html += "</tr>"
});
html += "</table>"
html += "</div>"
$(".excelListDiv").append(html);
})
}
// EXCEL download
function excelSubmit(){
console.log("EXCEL Print!");
let chkBox = $("input:checkbox[name=chkbox]:checked");
let excelA = [];
let excelB = [];
let excelC = [];
document.getElementById("excelChk").value = 0;
document.getElementById("excelA").value = "";
document.getElementById("excelB").value = "";
document.getElementById("excelC").value = "";
if (chkBox.length != 0){
alert("excel list" + chkBox.length + "개 download");
for(let chkcnt=0; chkcnt<chkBox.length; chkcnt++){
let excelSplitTmp = chkBox[chkcnt].value.split("-");
excelA.push(excelSplitTmp[0]);
excelB.push(excelSplitTmp[1]);
excelC.push(excelSplitTmp[2]);
}
document.getElementById("excelChk").value = chkBox.length; // 선택한 개수
document.getElementById("excelA").value = excelA.join(",");
document.getElementById("excelB").value = excelB.join(",");
document.getElementById("excelC").value = excelC.join(",");
}
else{
alert("not chk");
}
}
// [완료]
// 체크박스 모두선택/모두해제
function excelChkFunction(ele){
const chkbox = document.getElementsByName('chkbox');
chkbox.forEach((box) => { box.checked = ele.checked; })
}
이런식으로 화면이 나오면 오케이다.
엑셀 다운로드는 form을 사용해서 ExcelApiController에 작성했던 api에 엑셀 다운로드를 요청한다.
전체 코드는 위에 function.js에 작성되어 있으니 참고하자.
ㅇ excelServiceI 작성
서비스를 작성하기 위해 인터페이스를 먼저 만들어주자.
테스트 코드라서 굳이 안 만들어도 될 것 같지만...
package excel.mh.service;
import java.io.IOException;
import java.util.Map;
import org.apache.poi.ss.usermodel.Workbook;
public interface excelServiceI {
public abstract Workbook excelPrint(Map<String, String> target) throws IOException;
}
ㅇ excelService 작성
엑셀 템플릿 시트에 클라이언트에서 받아온 데이터를 순서대로 셀에 집어넣고 반환해주면 된다.
엑셀 다운로드 컨트롤러 코드는 위에 작성되어 있는 ExcelApiController를 참고하자.
package excel.mh.service;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Map;
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.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import lombok.extern.slf4j.Slf4j;
@Slf4j
@Service
public class excelService implements excelServiceI {
private final String sep = File.separator;
private final String staticPath = System.getProperty("user.dir") + sep + "src" + sep + "main" + sep + "resources"
+ sep + "static" + sep;
public Workbook excelPrint(Map<String, String> excelList) throws IOException {
log.info("excel print ...");
String templatesPath = staticPath + "excelTemplate" + sep + "template.xlsx";
FileInputStream file = new FileInputStream(templatesPath);
Workbook wb = new XSSFWorkbook(file);
Sheet sheet = wb.getSheetAt(0);
if (!excelList.get("excelChk").equals("0")) {
Row row;
int excelRow = 1;
int excelLength = Integer.valueOf(excelList.get("excelChk"));
String[] excelA = ((String) excelList.get("excelA")).split(",");
String[] excelB = ((String) excelList.get("excelB")).split(",");
String[] excelC = ((String) excelList.get("excelC")).split(",");
for (int cnt = 0; cnt < excelLength; cnt++) {
row = sheet.createRow(excelRow);
row.createCell(0).setCellValue(excelRow);
row.createCell(1).setCellValue(excelA[cnt]);
row.createCell(2).setCellValue(excelB[cnt]);
row.createCell(3).setCellValue(excelC[cnt]);
excelRow++;
}
}
log.info("print fin!");
return wb;
}
}
3. 기능 테스트
코드 때문에 글만 길어지고 로직에 대한 설명이 없어서 이게 뭔가 싶기도 하지만...
뭐... 필요한 사람이 있으면 알아서 필터링해서 보겠지.
아무것도 체크안했을 때 다운로드
다운로드 받은 파일
선택 후 다운로드
다운로드 받은 파일
제대로 작동하는 것을 확인할 수 있다!
근데 만들고 나서 알았는데 예제 데이터 작성할 때 데이터를 잘못 만들었었다...
ㅋㅋㅋ 왜 2번 행에서 4가 나와서 잘못 작성한줄 알고 깜짝 놀랐네.
끝!
'공부 > java & Spring' 카테고리의 다른 글
| [Spring boot] IoC(Inversion of Control) 개인공부 (0) | 2023.07.02 |
|---|---|
| [Spring security] The type WebSecurityConfigurerAdapter is deprecated (0) | 2023.06.28 |
| [zxing] Spring boot QR code 생성 (java, gradle) (0) | 2023.06.21 |
| [Spring boot build error] visual studio code에서 갑자기 빌드 에러날때 (0) | 2023.05.22 |
| [403 Forbidden error] spring post method (axios post request) (1) | 2023.05.14 |
댓글