package kr.wisestone.owl.web.view;
|
|
import java.beans.PropertyDescriptor;
|
import java.lang.reflect.InvocationTargetException;
|
import java.lang.reflect.Method;
|
import java.util.List;
|
import java.util.Map;
|
|
import javax.servlet.ServletOutputStream;
|
import javax.servlet.http.HttpServletRequest;
|
import javax.servlet.http.HttpServletResponse;
|
|
import kr.wisestone.owl.annotation.Viewer;
|
import kr.wisestone.owl.common.MessageAccessor;
|
import kr.wisestone.owl.constant.Constants;
|
import kr.wisestone.owl.util.CommonUtil;
|
import kr.wisestone.owl.vo.*;
|
import org.slf4j.LoggerFactory;
|
import org.slf4j.Logger;
|
import org.apache.poi.hssf.util.HSSFColor;
|
import org.apache.poi.ss.util.CellRangeAddress;
|
import org.apache.poi.xssf.usermodel.*;
|
import org.springframework.beans.BeanUtils;
|
import org.springframework.beans.factory.annotation.Autowired;
|
|
@Viewer
|
public class ExcelView extends AbstractExcelView {
|
private static final Logger log = LoggerFactory.getLogger(ExcelView.class);
|
|
public static final Integer EXCEL_EXPORT_MAX_COUNT = 65000;
|
|
@Autowired
|
protected MessageAccessor messageAccessor;
|
|
@SuppressWarnings({"unused", "unchecked"})
|
@Override
|
protected void buildExcelDocument(Map<String, Object> model, XSSFWorkbook workbook, HttpServletRequest request,
|
HttpServletResponse response) throws Exception {
|
|
XSSFSheet sheet;
|
XSSFRow mergeRow = null;
|
XSSFRow mergeRow2 = null;
|
XSSFRow sheetRow = null;
|
XSSFRow sheetRow2 = null;
|
|
ExportExcelVo excelInfo = (ExportExcelVo) model.get(Constants.EXCEL);
|
String localeFileName = this.findLocaleToText(excelInfo.getFileName());
|
|
response.setContentType("application/vnd.ms-excel; charset=UTF-8");
|
response.setCharacterEncoding("UTF-8");
|
response.setHeader("Content-Disposition", "attachment; filename=" + CommonUtil.getDisposition(localeFileName, CommonUtil.getBrowser(request)) + ".xlsx");
|
|
// 총 데이터양을 뽑아 낸다.
|
int totalCount = excelInfo.getDatas().size();
|
|
// 시트 생성
|
sheet = workbook.createSheet(excelInfo.getFileName());
|
int currentRowNum = 0;
|
|
// 셀 스타일을 설정한다.
|
XSSFCellStyle sheetTitleCellStyle = this.makeSheetTitleStyle(workbook);
|
XSSFCellStyle gridPageCellStyle = this.makeGridPageStyle(workbook);
|
XSSFCellStyle gridHeaderCellStyle = this.makeGridHeaderStyle(workbook);
|
|
Boolean merge = Boolean.FALSE; // merge 여부 확인
|
|
int colIndex = 0;
|
for (ExportExcelAttrVo attrInfo : excelInfo.getAttrInfos()) {
|
if (attrInfo.getMergeCount() == 1) {
|
merge = true;
|
//셀 병합
|
if (totalCount >= 2) {
|
totalCount = totalCount/ 2;
|
int rowNum3 = 3 + totalCount + (totalCount - 2);
|
for (int rowNum1 = 3; rowNum1 <= rowNum3; rowNum1 += 2) {
|
int rowNum2 = rowNum1 + 1;
|
sheet.addMergedRegion(new CellRangeAddress(rowNum1, rowNum2, 0, 0));
|
}
|
}
|
}
|
sheet.setColumnWidth(colIndex++, (attrInfo.getWidth() + 1) * 2 * 256);
|
attrInfo.setCellStyle(this.makeCellStyle(workbook, this.makeFont(workbook, XSSFFont.BOLDWEIGHT_NORMAL),
|
attrInfo.getAlign(), XSSFCellStyle.VERTICAL_CENTER, XSSFCellStyle.BORDER_THIN));
|
}
|
|
int size = excelInfo.getDatas().size();
|
// merge 를 사용할 경우
|
if (merge) {
|
size = excelInfo.getDatas().size() / 2;
|
}
|
|
this.writeSheetTitle(sheet, sheetTitleCellStyle, currentRowNum++, localeFileName, excelInfo.getAttrInfos()
|
.size() - 1, (short) 1024);
|
|
// 건수 표시 제어
|
if (!excelInfo.getHideCount()) {
|
this.writeGridPage(sheet, gridPageCellStyle, currentRowNum++, size, excelInfo
|
.getAttrInfos().size() - 1);
|
}
|
|
this.writeGridHeader(sheet, gridHeaderCellStyle, currentRowNum++, excelInfo.getAttrInfos());
|
|
for (int index = 0; index < excelInfo.getDatas().size(); index++) {
|
if (EXCEL_EXPORT_MAX_COUNT <= index) {
|
break;
|
}
|
|
Object data = excelInfo.getDatas().get(index);
|
XSSFRow row = sheet.createRow(currentRowNum++);
|
|
int currentColNum = 0;
|
for (ExportExcelAttrVo attrInfo : excelInfo.getAttrInfos()) {
|
this.writeData(workbook, row, currentColNum++, data, attrInfo);
|
}
|
}
|
|
ServletOutputStream out = null;
|
try {
|
out = response.getOutputStream();
|
workbook.write(out);
|
} catch (Exception e) {
|
} finally {
|
if (out != null) {
|
out.close();
|
}
|
}
|
|
}
|
|
private XSSFCellStyle makeSheetTitleStyle(XSSFWorkbook workbook) {
|
XSSFFont font = this.makeFont(workbook, XSSFFont.BOLDWEIGHT_BOLD, (short) 20);
|
XSSFCellStyle cellStyle = this.makeCellStyle(workbook, font, XSSFCellStyle.ALIGN_CENTER,
|
XSSFCellStyle.VERTICAL_CENTER, XSSFCellStyle.BORDER_NONE);
|
|
return cellStyle;
|
}
|
|
private XSSFCellStyle makeGridPageStyle(XSSFWorkbook workbook) {
|
XSSFFont font = this.makeFont(workbook, XSSFFont.BOLDWEIGHT_BOLD);
|
XSSFCellStyle cellStyle = this.makeCellStyle(workbook, font, XSSFCellStyle.ALIGN_RIGHT,
|
XSSFCellStyle.VERTICAL_CENTER, XSSFCellStyle.BORDER_NONE);
|
|
return cellStyle;
|
}
|
|
private XSSFCellStyle makeGridHeaderStyle(XSSFWorkbook workbook) {
|
XSSFFont font = this.makeFont(workbook, XSSFFont.BOLDWEIGHT_BOLD);
|
XSSFCellStyle cellStyle = this.makeCellStyle(workbook, font, XSSFCellStyle.ALIGN_CENTER,
|
XSSFCellStyle.VERTICAL_CENTER, XSSFCellStyle.BORDER_THIN);
|
|
// 셀에 색깔 채우기
|
cellStyle.setFillForegroundColor(new HSSFColor.GREY_25_PERCENT().getIndex());
|
cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
|
|
return cellStyle;
|
}
|
|
private XSSFCellStyle makeCellStyle(XSSFWorkbook workbook, XSSFFont font, short hAlign, short vAlign, short border) {
|
XSSFCellStyle cellStyle = workbook.createCellStyle();
|
|
cellStyle.setFont(font);
|
cellStyle.setAlignment(hAlign);
|
cellStyle.setVerticalAlignment(vAlign);
|
cellStyle.setBorderTop(border);
|
cellStyle.setBorderLeft(border);
|
cellStyle.setBorderBottom(border);
|
cellStyle.setBorderRight(border);
|
cellStyle.setWrapText(true);
|
|
return cellStyle;
|
}
|
|
private XSSFFont makeFont(XSSFWorkbook workbook) {
|
XSSFFont font = workbook.createFont();
|
font.setFontName("맑은 고딕");
|
font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
|
font.setColor(HSSFColor.BLACK.index);
|
|
return font;
|
}
|
|
private XSSFFont makeFont(XSSFWorkbook workbook, short bold) {
|
XSSFFont font = workbook.createFont();
|
font.setFontName("맑은 고딕");
|
font.setBoldweight(bold);
|
font.setColor(HSSFColor.BLACK.index);
|
|
return font;
|
}
|
|
private XSSFFont makeFont(XSSFWorkbook workbook, short bold, short size) {
|
XSSFFont font = workbook.createFont();
|
font.setFontName("맑은 고딕");
|
font.setBoldweight(bold);
|
font.setColor(HSSFColor.BLACK.index);
|
font.setFontHeightInPoints(size);
|
|
return font;
|
}
|
|
@SuppressWarnings("unchecked")
|
private void writeData(XSSFWorkbook workbook, XSSFRow row, int colnum, Object data, ExportExcelAttrVo attrInfo) {
|
XSSFCell cell = row.createCell(colnum);
|
cell.setCellStyle(attrInfo.getCellStyle());
|
|
String cellValue = "";
|
|
if (this.getAttrValue(data, attrInfo.getName()) != null) {
|
if (attrInfo.getI18Prefix() != null) {
|
if (this.getAttrValue(data, attrInfo.getName()) instanceof List) {
|
StringBuffer sb = new StringBuffer("");
|
List<String> values = (List<String>) this.getAttrValue(data, attrInfo.getName());
|
int idx = 0;
|
for (String value : values) {
|
if (idx != 0) {
|
sb.append("\n");
|
}
|
sb.append(this.messageAccessor.message(attrInfo.getI18Prefix() + value));
|
idx++;
|
}
|
cellValue = sb.toString();
|
}
|
else {
|
cellValue = this.messageAccessor.message(attrInfo.getI18Prefix() + this.getAttrValue(data, attrInfo.getName()).toString());
|
}
|
}
|
else {
|
if (this.getAttrValue(data, attrInfo.getName()) instanceof List) {
|
StringBuffer sb = new StringBuffer("");
|
List values = (List) this.getAttrValue(data, attrInfo.getName());
|
int idx = 0;
|
for (Object value : values) {
|
if (idx != 0) {
|
sb.append("\n");
|
}
|
|
if (value instanceof BaseVo) {
|
sb.append(this.getAttrValue(value, attrInfo.getVoAttrName()));
|
}
|
else {
|
sb.append(value);
|
}
|
idx++;
|
}
|
cellValue = sb.toString();
|
}
|
else {
|
cellValue = this.getAttrValue(data, attrInfo.getName()).toString();
|
}
|
}
|
}
|
|
if (attrInfo.getReplaceWithNewlineYn()) {
|
cellValue = CommonUtil.replaceBrWithNewline(cellValue);
|
}
|
|
cell.setCellValue(cellValue);
|
}
|
|
private void writeGridHeader(XSSFSheet sheet, XSSFCellStyle cellStyle, int rownum, List<ExportExcelAttrVo> attrInfos) {
|
XSSFRow row = sheet.createRow(rownum);
|
|
int colnum = 0;
|
|
for (ExportExcelAttrVo attrInfo : attrInfos) {
|
XSSFCell cell = row.createCell(colnum++);
|
cell.setCellStyle(cellStyle);
|
cell.setCellValue(attrInfo.getDesc());
|
}
|
|
}
|
|
private void writeSheetTitle(XSSFSheet sheet, XSSFCellStyle cellStyle, int rownum, String title, int mergeCount,
|
short height) {
|
XSSFRow row = sheet.createRow(rownum);
|
row.setHeight(height);
|
|
XSSFCell cell = row.createCell(0);
|
cell.setCellStyle(cellStyle);
|
cell.setCellValue(title);
|
|
sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 0, mergeCount));
|
}
|
|
private void writeGridPage(XSSFSheet sheet, XSSFCellStyle cellStyle, int rownum, int size, int mergeCount) {
|
XSSFRow row = sheet.createRow(rownum);
|
|
XSSFCell cell = row.createCell(0);
|
cell.setCellStyle(cellStyle);
|
cell.setCellValue(size + this.messageAccessor.message("common.few")); // 건수
|
|
sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 0, mergeCount));
|
}
|
|
/**
|
* 총 건수를 다국어 메세지로 전환한다.
|
*
|
* @param string
|
*/
|
private String findLocaleToText(String string) {
|
MessageVo message = this.messageAccessor.getMessage(string);
|
return message.getMessage();
|
}
|
|
/**
|
* 오브젝트로 부터 attr 속성의 값을 반환한다.
|
*
|
* @param data
|
* @param attr
|
* @return
|
* @throws IllegalAccessException
|
* @throws InvocationTargetException
|
*/
|
private Object getAttrValue(Object data, String attr) {
|
Object value = null;
|
if (data instanceof Map) {
|
value = ((Map) data).get(attr);
|
}
|
else {
|
PropertyDescriptor descriptor = BeanUtils.getPropertyDescriptor(data.getClass(), attr);
|
Method method = descriptor.getReadMethod();
|
|
try {
|
value = method.invoke(data, null);
|
} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
|
value = "";
|
}
|
}
|
|
return value;
|
}
|
|
}
|
