외국 블로그를 참고하여 스프링 부트와 MySql을 이용해서 데이터를 엑셀 파일로 만드는 기능을 구현하였다.
프로젝트는 메이븐이 아닌 그래들로 작업하였고 Excel Style이 추가되었다.
Technology
- Java 11
- Spring Boot 2.6.10 (with Spring Web MVC)
- Gradle
- Apache POI 5.2.2
Spring Boot Rest API: Download Excel File
클라이언트가 요청을 보내면 서버는 MySQL 테이블의 데이터를 포함하는 Excel 파일로 응답을 반환한다.
"Content-disposition" : "attachment; filename=[yourFileName]"
"Content-Type" : "application/vnd.ms-excel"Setup Spring Boot Download Excel File project
- build.gradle 의존성 추가
implementation group: 'org.apache.poi', name: 'poi-scratchpad', version: '4.1.2'
implementation 'org.apache.poi:poi-ooxml:4.1.2'
implementation 'org.apache.poi:poi:4.1.2'- Dto 파일 생성
- Repository 파일 생성
- Mybatis Mapper 파일 생성
Controller
@GetMapping("/download")
public ResponseEntity<Resource> getFile() {
String filename = "filename.xlsx";
InputStreamResource file = new InputStreamResource(deviceService.load());
return ResponseEntity.ok()
.header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + filename)
.contentType(MediaType.parseMediaType("application/vnd.ms-excel"))
.body(file);
}Service
Basic 코드
/**
* 엑셀 다운로드
* @return
*/
public ByteArrayInputStream load() {
List<DeviceDto> list = deviceRepository.findAll();
ByteArrayInputStream in = DeviceListToExcel(list);
return in;
}
/**
* 엑셀 다운로드 상세 로직
* @param deviceCondition
* @return
*/
private ByteArrayInputStream DeviceListToExcel(List<DeviceDto> deviceCondition) {
try (Workbook workbook = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream();) {
String TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
String[] HEADERs = { "단말아이디", "단말 유형", "단말명", "설치 지역", "방송번호 유형", "TCP 사용여부" };
String SHEET = "device";
// CellStyle 설정
CellStyle numberCellStyle = workbook.createCellStyle();
numberCellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_YELLOW.getIndex());
numberCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Sheet sheet = workbook.createSheet(SHEET);
// Header
Row headerRow = sheet.createRow(0);
for (int col = 0; col < HEADERs.length; col++) {
Cell cell = headerRow.createCell(col);
cell.setCellValue(HEADERs[col]);
}
int rowIdx = 1;
for (DeviceDto divice : deviceCondition) {
Row row = sheet.createRow(rowIdx++);
row.createCell(0).setCellValue(divice.getDvcId());
row.createCell(1).setCellValue(divice.getDvcTypeCd());
row.createCell(2).setCellValue(divice.getDvcNickNm());
row.createCell(3).setCellValue(divice.getDongriCd());
row.createCell(4).setCellValue(divice.getDvcSeq());
// CellStyle 적용
Cell cell5 = row.createCell(5);
cell5.setCellStyle(numberCellStyle);
cell5.setCellValue(divice.getDvcUseYn());
}
workbook.write(out);
return new ByteArrayInputStream(out.toByteArray());
} catch (IOException e) {
throw new RuntimeException("fail to import data to Excel file: " + e.getMessage());
}
}응용한 코드
private ByteArrayInputStream createExcel(Map<String, String> typeMap,
Dto txmrHtbtQueStateDt,
List<Dto> dto1,
List<Dto> dto2,
List<Dto> dto3) {
// ㅎㅎ...
try (Workbook workbook = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream();) {
/**
* 초기값 설정
*/
int rowCount = 0;
int cellCount = 0;
Cell cell;
String SHEET = "시트 이름";
int txmrTypeCdMapSize = typeMap.size();
/**
* Sheet 작성 Start
*/
Sheet sheet = workbook.createSheet(SHEET);
// Table Header
CellStyle tableHeaderCellStyle = workbook.createCellStyle();
tableHeaderCellStyle.setWrapText(true);
tableHeaderCellStyle.setAlignment(HorizontalAlignment.CENTER);
tableHeaderCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
tableHeaderCellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
tableHeaderCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
tableHeaderCellStyle.setBorderLeft(BorderStyle.THIN);
tableHeaderCellStyle.setBorderRight(BorderStyle.THIN);
tableHeaderCellStyle.setBorderTop(BorderStyle.THIN);
tableHeaderCellStyle.setBorderBottom(BorderStyle.THIN);
// Header 제목
Font HeaderFontStyle = workbook.createFont();
HeaderFontStyle.setColor(IndexedColors.BLACK.getIndex());
HeaderFontStyle.setBold(true);
CellStyle HeaderFontCellStyle = workbook.createCellStyle();
HeaderFontCellStyle.setFont(HeaderFontStyle);
// Table Body V1
CellStyle TableBodyV1CellStyle = workbook.createCellStyle();
TableBodyV1CellStyle.setBorderLeft(BorderStyle.THIN);
TableBodyV1CellStyle.setBorderRight(BorderStyle.THIN);
TableBodyV1CellStyle.setBorderTop(BorderStyle.THIN);
TableBodyV1CellStyle.setBorderBottom(BorderStyle.THIN);
// Table Body V2
CellStyle TableBodyV2CellStyle = workbook.createCellStyle();
TableBodyV2CellStyle.setAlignment(HorizontalAlignment.CENTER);
TableBodyV2CellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
TableBodyV2CellStyle.setBorderLeft(BorderStyle.THIN);
TableBodyV2CellStyle.setBorderRight(BorderStyle.THIN);
TableBodyV2CellStyle.setBorderTop(BorderStyle.THIN);
TableBodyV2CellStyle.setBorderBottom(BorderStyle.THIN);
/**
* 업데이트 날짜
*/
Row headerRow01 = sheet.createRow(rowCount++);
cell = headerRow01.createCell(cellCount++);
Date updateDt = txmrHtbtQueStateDt.getTxmrHtbtQueStateDt();
SimpleDateFormat formatter = new SimpleDateFormat("yyyy년 MM월 dd일"); // yyyy-MM-dd HH:mm:ss
String format = formatter.format(updateDt);
cell.setCellValue("(업데이트 : " + format + ")");
rowCount++;
/**
* 첫번째 테이블
*/
// Row 생성
Row headerRow02 = sheet.createRow(rowCount++);
cellCount = 0;
cell = headerRow02.createCell(cellCount++);
cell.setCellStyle(HeaderFontCellStyle);
cell.setCellValue("첫번째 테이블");
// Cell 병합
sheet.addMergedRegion(new CellRangeAddress(0,0,0,5));
sheet.addMergedRegion(new CellRangeAddress(rowCount,rowCount+1,0,0));
sheet.addMergedRegion(new CellRangeAddress(rowCount,rowCount+1,1,1));
sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount,2,3));
sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount,4, txmrTypeCdMapSize +3));
// Row 생성
Row headerRow03 = sheet.createRow(rowCount++);
cellCount = 0;
cell = headerRow03.createCell(cellCount++);
cell.setCellStyle(tableHeaderCellStyle); //위에서 설정한셀 스타일 셋팅
cell.setCellValue("표이름1");
cell = headerRow03.createCell(cellCount++);
cell.setCellStyle(tableHeaderCellStyle);
cell.setCellValue("표이름2");
cell = headerRow03.createCell(cellCount++);
cell.setCellStyle(tableHeaderCellStyle);
cell.setCellValue("표이름3");
cell = headerRow03.createCell(cellCount++);
cell.setCellStyle(tableHeaderCellStyle);
cell = headerRow03.createCell(cellCount++);
cell.setCellStyle(tableHeaderCellStyle);
cell.setCellValue("표이름4");
for (int i = 1; i < txmrTypeCdMapSize; i ++) {
cell = headerRow03.createCell(cellCount++);
cell.setCellStyle(tableHeaderCellStyle);
}
// Row 생성
Row headerRow04 = sheet.createRow(rowCount++);
String[] HEADERs = { "표이름1", "표이름2", "표이름5", "표이름6" };
int col = 0;
for (col = 0; col < HEADERs.length; col++) {
cell = headerRow04.createCell(col);
cell.setCellStyle(tableHeaderCellStyle);
cell.setCellValue(HEADERs[col]);
}
createTableHeaderForTxmrTypeCd(typeMap, tableHeaderCellStyle, headerRow04, col);
// Row 생성
for (TxmrBrctDto txmrBrctDto : dto1) {
Row row = sheet.createRow(rowCount++);
int index = 0;
cell = row.createCell(index++);
cell.setCellValue(txmrBrctDto.getDongriNm());
cell.setCellStyle(TableBodyV1CellStyle);
cell = row.createCell(index++);
cell.setCellValue(txmrBrctDto.getALL());
cell.setCellStyle(TableBodyV2CellStyle);
cell = row.createCell(index++);
cell.setCellValue(txmrBrctDto.getS99());
cell.setCellStyle(TableBodyV2CellStyle);
cell = row.createCell(index++);
cell.setCellValue(txmrBrctDto.getS95());
cell.setCellStyle(TableBodyV2CellStyle);
createTableBodyForTxmrTypeCd(typeMap, TableBodyV2CellStyle, txmrBrctDto, row, index);
}
rowCount++;
/**
* 두번째 테이블
*/
Row headerRow05 = sheet.createRow(rowCount++);
cellCount = 0;
cell = headerRow05.createCell(cellCount++);
cell.setCellStyle(HeaderFontCellStyle);
cell.setCellValue("두번째 테이블");
// Cell 병합
sheet.addMergedRegion(new CellRangeAddress(rowCount,rowCount + 1,0,0));
sheet.addMergedRegion(new CellRangeAddress(rowCount,rowCount,1,2));
// Row 생성
Row headerRow06 = sheet.createRow(rowCount++);
cellCount = 0;
cell = headerRow06.createCell(cellCount++);
cell.setCellStyle(tableHeaderCellStyle); //위에서 설정한셀 스타일 셋팅
cell.setCellValue("표이름4");
cell = headerRow06.createCell(cellCount++);
cell.setCellStyle(tableHeaderCellStyle);
cell.setCellValue("표이름3");
cell = headerRow06.createCell(cellCount++);
cell.setCellStyle(tableHeaderCellStyle);
// Row 생성
Row headerRow07 = sheet.createRow(rowCount++);
String[] HEADERsV2 = { "표이름4", "표이름5", "표이름6" };
for (col = 0; col < HEADERsV2.length; col++) {
cell = headerRow07.createCell(col);
cell.setCellStyle(tableHeaderCellStyle);
cell.setCellValue(HEADERsV2[col]);
}
// Row 생성
for (TxmrBrctDto txmrBrctDto : dto2) {
Row row = sheet.createRow(rowCount++);
int index = 0;
cell = row.createCell(index++);
cell.setCellValue(txmrBrctDto.getTxmrTypeNm());
cell.setCellStyle(TableBodyV1CellStyle);
cell = row.createCell(index++);
cell.setCellValue(txmrBrctDto.getS99());
cell.setCellStyle(TableBodyV2CellStyle);
cell = row.createCell(index++);
cell.setCellValue(txmrBrctDto.getS95());
cell.setCellStyle(TableBodyV2CellStyle);
}
rowCount++;
/**
* 세번째 테이블
*/
// Row 생성
Row headerRow08 = sheet.createRow(rowCount++);
cellCount = 0;
cell = headerRow08.createCell(cellCount++);
cell.setCellStyle(HeaderFontCellStyle);
cell.setCellValue("세번째 테이블");
// Cell 병합
sheet.addMergedRegion(new CellRangeAddress(rowCount,rowCount + 1,0,0));
sheet.addMergedRegion(new CellRangeAddress(rowCount,rowCount + 1,1,1));
sheet.addMergedRegion(new CellRangeAddress(rowCount,rowCount,2, txmrTypeCdMapSize +1));
// Row 생성
Row headerRow09 = sheet.createRow(rowCount++);
cellCount = 0;
cell = headerRow09.createCell(cellCount++);
cell.setCellStyle(tableHeaderCellStyle); //위에서 설정한셀 스타일 셋팅
cell.setCellValue("제조사");
cell = headerRow09.createCell(cellCount++);
cell.setCellStyle(tableHeaderCellStyle);
cell.setCellValue("표이름2");
cell = headerRow09.createCell(cellCount++);
cell.setCellStyle(tableHeaderCellStyle);
cell.setCellValue("단말유형");
for (int i = 1; i < txmrTypeCdMapSize; i ++) {
cell = headerRow09.createCell(cellCount++);
cell.setCellStyle(tableHeaderCellStyle);
}
// Row 생성
Row headerRow10 = sheet.createRow(rowCount++);
String[] HEADERsV3 = { "제조사", "표이름2"};
for (col = 0; col < HEADERsV3.length; col++) {
cell = headerRow10.createCell(col);
cell.setCellStyle(tableHeaderCellStyle);
cell.setCellValue(HEADERsV3[col]);
}
createTableHeaderForTxmrTypeCd(typeMap, tableHeaderCellStyle, headerRow10, col);
// Row 생성
for (TxmrBrctDto txmrBrctDto : dto3) {
Row row = sheet.createRow(rowCount++);
int index = 0;
cell = row.createCell(index++);
cell.setCellValue(txmrBrctDto.getTxmrMakerNm());
cell.setCellStyle(TableBodyV1CellStyle);
cell = row.createCell(index++);
cell.setCellValue(txmrBrctDto.getALL());
cell.setCellStyle(TableBodyV2CellStyle);
createTableBodyForTxmrTypeCd(typeMap, TableBodyV2CellStyle, txmrBrctDto, row, index);
}
/**
* Cell 여백 지정
*/
for (int i = 0; i < txmrTypeCdMapSize + 4; i++) {
sheet.autoSizeColumn(i, true);
sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 2000);
}
workbook.write(out);
return new ByteArrayInputStream(out.toByteArray());
} catch (IOException e) {
throw new RuntimeException("fail to import data to Excel file: " + e.getMessage());
}
}Test
Download를 누르면 Excel 파일을 다운받을 수 있다.
Result
(데이터를 지운 완성본)
이런 노가다는.. 안녕해 안녕..
Reference
오늘도 호기심을 발휘한다!