package egovframework.cmn.cmn;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.servlet.view.AbstractView;
import egovframework.rte.psl.dataaccess.util.EgovMap;
public class ExcelView extends AbstractView {
/** The content type for an Excel response */
private static final String CONTENT_TYPE_XLSX = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
/**
* Default Constructor. Sets the content type of the view for excel files.
*/
public ExcelView() {
}
@Override
protected boolean generatesDownloadContent() {
return true;
}
/**
* Renders the Excel view, given the specified model.
*/
@Override
protected final void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {
XSSFWorkbook workbook = new XSSFWorkbook();
setContentType(CONTENT_TYPE_XLSX);
buildExcelDocument(model, workbook, request, response);
// Set the filename
String sFilename = "";
if(model.get("filename") != null){
sFilename = (String)model.get("filename");
}else if(request.getAttribute("filename") != null){
sFilename = (String)request.getAttribute("filename");
}else{
sFilename = getClass().getSimpleName();
}
response.setContentType(getContentType());
String header = request.getHeader("User-Agent");
sFilename = sFilename.replaceAll("\r","").replaceAll("\n","");
if(header.contains("MSIE") || header.contains("Trident") || header.contains("Chrome")){
sFilename = URLEncoder.encode(sFilename,"UTF-8").replaceAll("\\+","%20");
response.setHeader("Content-Disposition","attachment;filename="+sFilename+".xlsx;");
}else{
sFilename = new String(sFilename.getBytes("UTF-8"),"ISO-8859-1");
response.setHeader("Content-Disposition","attachment;filename=\""+sFilename + ".xlsx\"");
}
// Flush byte array to servlet output stream.
ServletOutputStream out = response.getOutputStream();
out.flush();
workbook.write(out);
out.flush();
// Don't close the stream - we didn't open it, so let the container handle it.
// http://stackoverflow.com/questions/1829784/should-i-close-the-servlet-outputstream
}
@SuppressWarnings("unchecked")
protected void buildExcelDocument(Map<String, Object> model, XSSFWorkbook wb, HttpServletRequest req, HttpServletResponse resp) throws Exception {
List<Map<String, Object>> dataMapList = (List<Map<String, Object>>) model.get("dataMapList");
for(Map<String, Object> dataMap : dataMapList) {
XSSFCell cell = null;
String sheetNm = (String) dataMap.get("sheetNm"); // 엑셀 시트 이름
String[] titleArr = (String[]) dataMap.get("titleArr"); // 각 컬럼 이름
String[] fieldArr = (String[]) dataMap.get("fieldArr"); // 각 컬럼의 변수 이름
List<EgovMap> dataList = (List<EgovMap>) dataMap.get("list"); // 데이터가 담긴 리스트
CellStyle cellStyle = wb.createCellStyle(); // 제목셀의 셀스타일
cellStyle.setWrapText(true); // 줄 바꿈
cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // 셀 색상
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 셀 색상 패턴
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 셀 가로 정렬
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 셀 세로 정렬
cellStyle.setDataFormat((short)0x31); // 셀 데이터 형식
cellStyle.setBorderRight(HSSFCellStyle.BORDER_DOUBLE);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_DOUBLE);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
// 셀 폰트색상, bold처리
Font font = wb.createFont();
font.setColor(HSSFColor.WHITE.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellStyle.setFont(font);
CellStyle cellStyle2 = wb.createCellStyle(); // 데이터셀의 셀스타일
cellStyle2.setWrapText(true); // 줄 바꿈
cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 셀 가로 정렬
cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 셀 세로 정렬
cellStyle2.setDataFormat((short)0x31); // 셀 데이터 형식
cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
XSSFSheet sheet = wb.createSheet(sheetNm);
sheet.setDefaultColumnWidth(12);
// 컬럼명 삽입
for(int i=0; i<titleArr.length; i++){
setText(getCell(sheet, 0, i), titleArr[i]);
getCell(sheet, 0, i).setCellStyle(cellStyle);
sheet.autoSizeColumn(i);
int columnWidth = (sheet.getColumnWidth(i))*5;
sheet.setColumnWidth(i, columnWidth);
if(dataList.size() < 1){
cell = getCell(sheet, 1, i);
if(i==0){
setText(cell, "등록된 정보가 없습니다.");
}
cell.setCellStyle(cellStyle2);
}
}
if(dataList.size() > 0){ // 저장된 데이터가 있을때
// 리스트 데이터 삽입
for (int i = 0; i<dataList.size(); i++) {
EgovMap dataEgovMap = dataList.get(i);
// 맨 앞 컬럼인 "번호"는 idx라는 이름으로 여기서 생성하여 넣어준다.
dataEgovMap.put("idx", (i+1)+"");
for(int j=0; j<fieldArr.length; j++){
String data = String.valueOf(dataEgovMap.get(fieldArr[j]));
cell = getCell(sheet, 1 + i, j);
setText(cell, data);
cell.setCellStyle(cellStyle2);
}
}
}else{ // 저장된 데이터가 없으면 셀 병합
// 셀 병합(시작열, 종료열, 시작행, 종료행)
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, titleArr.length-1));
}
}
}
/**
* Convenient method to obtain the cell in the given sheet, row and column.
*
* <p>Creates the row and the cell if they still doesn't already exist.
* Thus, the column can be passed as an int, the method making the needed downcasts.</p>
*
* @param sheet a sheet object. The first sheet is usually obtained by workbook.getSheetAt(0)
* @param row thr row number
* @param col the column number
* @return the XSSFCell
*/
protected XSSFCell getCell(XSSFSheet sheet, int row, int col) {
XSSFRow sheetRow = sheet.getRow(row);
if (sheetRow == null) {
sheetRow = sheet.createRow(row);
}
XSSFCell cell = sheetRow.getCell((short) col);
if (cell == null) {
cell = sheetRow.createCell((short) col);
}
return cell;
}
/**
* Convenient method to set a String as text content in a cell.
*
* @param cell the cell in which the text must be put
* @param text the text to put in the cell
*/
protected void setText(XSSFCell cell, String text) {
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(text);
}
}
3. SearchVO.java (VO 변수 추가)
4. XxxController.java (Controller 엑셀다운로드 함수 추가)
@RequestMapping("disorderAllList.do")
public ModelAndView selectDisorderAllList(@ModelAttribute("searchVO") SearchVO searchVO, HttpServletRequest request, HttpServletResponse response, BindingResult bindingResult) throws Exception {
//코드관리 목록 조회
List<?> list = cfgService.selectDisorderList(searchVO);
//코드1관리 목록 조회
List<?> list1 = cfgService.selectCauseList(searchVO);
//코드2관리 목록 조회
List<?> list2 = cfgService.selectActionList(searchVO);
ModelAndView mav = new ModelAndView("excelView");
List<Map<String, Object>> dataMapList = new ArrayList<Map<String, Object>>();
Map<String, Object> subMap = null;
for(int i = 0 ; i < searchVO.getSchSheetNmArr().length ; i++) {
subMap = new HashMap<String, Object>();
subMap.put("titleArr", searchVO.getSchTitleArrArr()[i]);
subMap.put("fieldArr", searchVO.getSchFieldArrArr()[i]);
subMap.put("sheetNm", searchVO.getSchSheetNmArr()[i]);
if(i == 0) subMap.put("list", list);
else if(i == 1) subMap.put("list", list1);
else if(i == 2) subMap.put("list", list2);
dataMapList.add(i, subMap);
}
mav.addObject("dataMapList", dataMapList);
mav.addObject("filename", searchVO.getSchFileNm());
return mav;
}
5. common.js (공통 Tabulator 엑셀 다운로드 함수 추가)
//Tabulator 엑셀 다운로드
function downBaseExcelTabulator(vAction, vFileNm, vSheetNmArr, vSheetArr, vParams){
//action 정보 세팅
var vForm = document.createElement("FORM");
vForm.action = vAction;
vForm.method = "POST";
//파일명 세팅
appendMakeInput(vForm, "schFileNm", vFileNm);
//시트명 세팅
for(var i = 0 ; i < vSheetNmArr.length ; i++){
appendMakeInput(vForm, "schSheetNmArr[" + i + "]", vSheetNmArr[i]);
}
//sheet 컬럼 정보 세팅
for(var j = 0 ; j < vSheetArr.length ; j++){
var vColumns = vSheetArr[j];
for(var jj = 0 ; jj < vColumns.length ; jj++){
if(vColumns[jj].download != false){
appendMakeInput(vForm, "schTitleArrArr[" + j + "]", vColumns[jj].title);
appendMakeInput(vForm, "schFieldArrArr[" + j + "]", vColumns[jj].field);
}
}
}
//params 정보 세팅
if(vParams != null && vParams != ""){
for(var k = 0 ; k < vParams.length ; k++){
appendMakeInput(vForm, vParams[k].name, vParams[k].value);
}
}
window.document.body.appendChild(vForm);
vForm.submit();
}
//Tabulator 단일 시트 엑셀 다운로드
function downOneExcelTabulator(vAction, vFileNm, vSheetNm, vSheet, vParams){
var vSheetNmArr = [vSheetNm];
var vSheetArr = [vSheet.getColumnDefinitions()];
downBaseExcelTabulator(vAction, vFileNm, vSheetNmArr, vSheetArr, vParams);
}
//Tabulator 멀티 시트 엑셀 다운로드
function downMultiExcelTabulator(vAction, vFileNm, vSheetNmArr, vSheetArr, vParams){
var reSheetArr = new Array();
for(var i = 0 ; i < vSheetArr.length ; i++){
var tempArr = new Array();
for(var j = 0 ; j < vSheetArr[i].getColumns().length ; j++){
tempArr[j] = vSheetArr[i].getColumns()[j].getDefinition();
}
reSheetArr[i] = tempArr;
}
downBaseExcelTabulator(vAction, vFileNm, vSheetNmArr, reSheetArr, vParams);
}
//input hidden 객체 만들어 붙이기
function appendMakeInput(obj, nm, val){
var vInput = document.createElement("INPUT");
vInput.name = nm;
vInput.value = val;
vInput.type = "hidden";
obj.append(vInput);
}
6. Xxx.js (각 화면 엑셀 다운로드 함수 추가)
