Code - jhpark/cms

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; 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; } // 헤더 스타일 private CellStyle theadStyle() { CellStyle headerCellStyle = tbodyStyle(); headerCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headerCellStyle.setFillPattern(FillPatternType.BRICKS); return headerCellStyle; } // 내용 스타일 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; } // 빈칸 스타일 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; } // 세로 적재 테이블 그리기 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; } // 가로 적재 테이블 그리기 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; } // 세로형 가로 적재 테이블 그리기 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; } // 구분선 public int createHr(int rowIndex) { // tr Row row = sheet.createRow(rowIndex++); Cell cell = row.createCell(0); setCellValue(cell, " "); cell.setCellStyle(emptyStyle()); return rowIndex; } // 제목 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; } // 셀 내 데이터 입력 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(); } } } // 세로 적재 테이블 그리기 (기업정보용) public int createVerticalByEntInfo(ExcelRenderResource resource, List<T> dataList, int rowIndex) throws NoSuchFieldException, IllegalAccessException { String isIvstDscsn = null; for (String dataFieldName : resource.getDataFieldNames()) { if (dataFieldName!= "isIvstDscsn" && dataFieldName != "mvnInten" && dataFieldName != "mouInten" && dataFieldName != "rmrk") { rowIndex = createTableByEntInfo(resource, rowIndex, dataFieldName); } } rowIndex = createHr(rowIndex); // 구분선 rowIndex = createCaption(rowIndex, "세부정보"); Field field = SuperClassReflectionUtil.getField(dataList.get(0).getClass(), ("isIvstDscsn")); field.setAccessible(true); isIvstDscsn = field.get(dataList.get(0)).toString(); field.setAccessible(false); rowIndex = createTableByEntInfo(resource, rowIndex, "isIvstDscsn"); if (isIvstDscsn == "Y") { for (String dataFieldName : resource.getDataFieldNames()) { if (dataFieldName == "mvnInten" || dataFieldName == "mouInten") { rowIndex = createTableByEntInfo(resource, rowIndex, dataFieldName); } } } rowIndex = createTableByEntInfo(resource, rowIndex, "rmrk"); return rowIndex; } // 테이블 그리기 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; } // 기업정보 전체 테이블 그리기 (기업정보용) public int createByEntInfoAll(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; } }