File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
package com.takensoft.common.excel.service;
import com.takensoft.common.excel.resource.ExcelRenderResource;
import com.takensoft.common.excel.resource.ExcelRenderResourceFactory;
import com.takensoft.common.excel.util.SuperClassReflectionUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.util.ObjectUtils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.nio.file.Paths;
import java.util.Base64;
import java.util.List;
/**
* @author takensoft
* @since 2024.04.09
* @modification
* since | author | description
* 2024.05.30 | 박정하 | 최초 등록
* 2025.03.17 | 방선주 | 기업용 엑셀 다운로드 기능 삭제
*
* 엑셀 생성 서비스
*/
public class ExcelCreateService<T> {
private Workbook workbook;
private Sheet sheet;
private ExcelRenderResource resource;
private List<T> dataList;
private static final String BASE64_PNG_PRE_FIX = "data:image/png;base64,";
public ExcelCreateService(Workbook workbook, Sheet sheet, List<T> dataList, Class<T> type) {
this.workbook = workbook;
this.sheet = sheet;
this.resource = ExcelRenderResourceFactory.prepareRenderResource(type);
this.dataList = dataList;
}
/**
* @param
* @return CellStyle - 엑셀 셀 스타일
*
* 헤더 스타일
*/
private CellStyle theadStyle() {
CellStyle headerCellStyle = tbodyStyle();
headerCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headerCellStyle.setFillPattern(FillPatternType.BRICKS);
return headerCellStyle;
}
/**
* @param
* @return CellStyle - 엑셀 셀 스타일
*
* 내용 스타일
*/
private CellStyle tbodyStyle() {
CellStyle bodyCellStyle = workbook.createCellStyle();
bodyCellStyle.setBorderLeft(BorderStyle.THIN);
bodyCellStyle.setBorderRight(BorderStyle.THIN);
bodyCellStyle.setBorderTop(BorderStyle.THIN);
bodyCellStyle.setBorderBottom(BorderStyle.THIN);
bodyCellStyle.setWrapText(true);
return bodyCellStyle;
}
/**
* @param
* @return CellStyle - 엑셀 셀 스타일
*
* 빈칸 스타일
*/
private CellStyle emptyStyle() {
CellStyle emptyStyle = workbook.createCellStyle();
emptyStyle.setBorderLeft(BorderStyle.NONE);
emptyStyle.setBorderRight(BorderStyle.NONE);
emptyStyle.setBorderTop(BorderStyle.NONE);
emptyStyle.setBorderBottom(BorderStyle.NONE);
return emptyStyle;
}
/**
* @param resource - 엑셀 렌더 리소스
* @param dataList - 엑셀 데이터 리스트
* @param rowIndex - 엑셀 행 인덱스
* @return CellStyle - 엑셀 셀 스타일
*
* 세로 적재 테이블 그리기
*/
public int createVertical(ExcelRenderResource resource, List<T> dataList, int rowIndex) throws NoSuchFieldException, IllegalAccessException {
for (String dataFieldName : resource.getDataFieldNames()) {
// tr
Row row = sheet.createRow(rowIndex++);
// th
Cell cell = row.createCell(0);
String value = resource.getExcelHeaderName(dataFieldName);
cell.setCellValue(value);
cell.setCellStyle(theadStyle());
int columnIndex = 1;
// td
for (T data : dataList) {
cell = row.createCell(columnIndex++);
Field field = SuperClassReflectionUtil.getField(data.getClass(), (dataFieldName));
field.setAccessible(true);
Object cellValue = field.get(data);
field.setAccessible(false);
setCellValue(cell, cellValue);
}
}
return rowIndex;
}
/**
* @param resource - 엑셀 렌더 리소스
* @param dataList - 엑셀 데이터 리스트
* @param rowIndex - 엑셀 행 인덱스
* @return int - 엑셀 행 인덱스
*
* 가로 적재 테이블 그리기
*/
public int createHorizontal(ExcelRenderResource resource, List<T> dataList, int rowIndex) throws NoSuchFieldException, IllegalAccessException {
// tr
Row row = sheet.createRow(rowIndex++);
// th
int columnIndex = 0;
for (String dataFieldName : resource.getDataFieldNames()) {
Cell cell = row.createCell(columnIndex++);
String value = resource.getExcelHeaderName(dataFieldName);
cell.setCellValue(value);
cell.setCellStyle(theadStyle());
}
// td
for (T data : dataList) {
row = sheet.createRow(rowIndex++);
columnIndex = 0;
for (String dataFieldName : resource.getDataFieldNames()) {
Cell cell = row.createCell(columnIndex++);
Field field = SuperClassReflectionUtil.getField(data.getClass(), (dataFieldName));
field.setAccessible(true);
Object cellValue = field.get(data);
field.setAccessible(false);
setCellValue(cell, cellValue);
}
}
return rowIndex;
}
/**
* @param resource - 엑셀 렌더 리소스
* @param dataList - 엑셀 데이터 리스트
* @param rowIndex - 엑셀 행 인덱스
* @return int - 엑셀 행 인덱스
*
* 세로형 가로 적재 테이블 그리기
*/
public int createVH(ExcelRenderResource resource, List<T> dataList, int rowIndex) throws NoSuchFieldException, IllegalAccessException {
for (T data : dataList) {
for (String dataFieldName : resource.getDataFieldNames()) {
// tr
Row row = sheet.createRow(rowIndex++);
int columnIndex = 0;
// th
Cell cell = row.createCell(columnIndex++);
String value = resource.getExcelHeaderName(dataFieldName);
cell.setCellValue(value);
cell.setCellStyle(theadStyle());
// td
cell = row.createCell(columnIndex++);
Field field = SuperClassReflectionUtil.getField(data.getClass(), (dataFieldName));
field.setAccessible(true);
Object cellValue = field.get(data);
field.setAccessible(false);
setCellValue(cell, cellValue);
}
}
return rowIndex;
}
/**
* @param rowIndex - 엑셀 행 인덱스
* @return int - 엑셀 행 인덱스
*
* 구분선
*/
public int createHr(int rowIndex) {
// tr
Row row = sheet.createRow(rowIndex++);
Cell cell = row.createCell(0);
setCellValue(cell, " ");
cell.setCellStyle(emptyStyle());
return rowIndex;
}
/**
* @param rowIndex - 엑셀 행 인덱스
* @param caption - 엑셀 캡션
* @return int - 엑셀 행 인덱스
*
* 제목
*/
public int createCaption(int rowIndex, String caption) {
// tr
Row row = sheet.createRow(rowIndex++);
Cell cell = row.createCell(0);
setCellValue(cell, caption);
cell.setCellStyle(emptyStyle());
return rowIndex;
}
/**
* @param cell - 엑셀 셀
* @param cellValue - 엑셀 셀 값
*
* 셀 내 데이터 입력
*/
private void setCellValue(Cell cell, Object cellValue) {
if (cellValue instanceof Number) {
Number numberValue = (Number) cellValue;
cell.setCellValue(numberValue.doubleValue());
cell.setCellStyle(tbodyStyle());
return;
}
cell.setCellValue(ObjectUtils.isEmpty(cellValue) ? "" : String.valueOf(cellValue));
cell.setCellStyle(tbodyStyle());
}
/**
* @author 박정하
* @since 2024.03.28
*
* 차트 이미지 넣기
*/
public void chartDraw(Sheet sheet, String chart, int rowNum, int cellNum) {
if (chart != null && chart.equals("")) {
InputStream in = null;
FileOutputStream fos = null;
File imageFile = null;
try {
String encodingStr = chart.replace(BASE64_PNG_PRE_FIX, "");
Base64.Decoder decoder = Base64.getDecoder();
byte[] decodeImg = decoder.decode(encodingStr);
String uploadPath = Paths.get("C:", "upload-files").toString();
File dir = new File(uploadPath);
if (dir.exists() == false) {
dir.mkdirs();
}
uploadPath = dir.getPath();
imageFile = new File(uploadPath + "/chartImg.png");
fos = new FileOutputStream(imageFile);
fos.write(decodeImg);
in = new FileInputStream(imageFile);
byte[] bytes = IOUtils.toByteArray(in);
int pictureIdx = workbook.addPicture(bytes, SXSSFWorkbook.PICTURE_TYPE_PNG);
final CreationHelper helper = workbook.getCreationHelper();
final Drawing drawing = sheet.createDrawingPatriarch();
final ClientAnchor anchor = helper.createClientAnchor();
// 이미지를 출력할 CELL 위치 선정
anchor.setCol1(cellNum++);
anchor.setRow1(rowNum++);
// 이미지 그리기
final Picture pict = drawing.createPicture(anchor, pictureIdx);
pict.resize(); // 이미지 사이즈 비율 설정
rowNum = rowNum + 20;
} catch(Exception e) {
e.printStackTrace();
} finally {
IOUtils.closeQuietly(in);
IOUtils.closeQuietly(fos);
imageFile.delete();
}
}
}
/**
* @param resource - 엑셀 렌더 리소스
* @param rowIndex - 엑셀 행 인덱스
* @param dataFieldName - 엑셀 셀 값
*
* 테이블 그리기
*/
private int createTableByEntInfo(ExcelRenderResource resource, int rowIndex, String dataFieldName) throws NoSuchFieldException, IllegalAccessException {
// tr
Row row = sheet.createRow(rowIndex++);
// th
Cell cell = row.createCell(0);
String value = resource.getExcelHeaderName(dataFieldName);
cell.setCellValue(value);
cell.setCellStyle(theadStyle());
// td
cell = row.createCell(1);
Field field = SuperClassReflectionUtil.getField(dataList.get(0).getClass(), (dataFieldName));
field.setAccessible(true);
Object cellValue = field.get(dataList.get(0));
field.setAccessible(false);
setCellValue(cell, cellValue);
return rowIndex;
}
}