import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
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.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.Region;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.web.servlet.view.AbstractView;
import com.kt.aib.ws.config.util.DataMap;
@Component("excelDownloadView")
public class ExcelDownloadView extends AbstractView{
public ExcelDownloadView() {
// TODO Auto-generated constructor stub
setContentType("application/download; ccharset=utf-8");
}
@Override
protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {
// TODO Auto-generated method stub
String excelVersion = (String)model.get(ExcelConstant.EXCEL_VERSION);
String excelFileName = (String)model.get(ExcelConstant.EXCEL_FILE_NMAE);
StringBuffer fileName = new StringBuffer();
fileName.append(excelFileName+"_"+currentDate("YYYYMMDD_HHMMSS"));
Workbook workbook;
if("2003".equals(excelVersion)){
workbook = new HSSFWorkbook();
buildExcelDocument2003(model, workbook, request, response);
fileName.append(".xls");
}else{
workbook = new XSSFWorkbook();
buildExcelDocument2007(model, workbook, request, response);
fileName.append(".xlsx");
}
setResponse(request,response,fileName.toString());
ServletOutputStream out = response.getOutputStream();
workbook.write(out);
if(out != null){out.close();}
}
private void setResponse(HttpServletRequest request, HttpServletResponse response, String fileName) throws UnsupportedEncodingException{
String userAgent = request.getHeader("User-Agent");
if (userAgent.indexOf("MSIE 5.5") > -1) { // MS IE 5.5 이하
response.setHeader("Content-Disposition", "filename=" + URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "\\ ") + ";");
} else if (userAgent.indexOf("MSIE") > -1) { // MS IE (보통은 6.x 이상 가정)
response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "\\ ") + ";");
} else if (userAgent.indexOf("Trident") > -1) { // MS IE 11
response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "\\ ") + ";");
} else { // 모질라나 오페라
response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("euc-kr"), "latin1").replaceAll("\\+", "\\ ") + ";");
}
}
private String currentDate(String format){
SimpleDateFormat sd = new SimpleDateFormat(format);
return sd.format(new Date());
}
private void buildExcelDocument2003(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) {
// TODO Auto-generated method stub
// init
List<DataMap> excelDataList = (List<DataMap>)model.get(ExcelConstant.EXCEL_DATA_LIST);
DataMap headerDataMap = excelDataList.get(0);
int rowTitleNum = 0;
int rowTitleSubNum = 2;
int rowHeaderNum = 3;
int rowDataNum = 4;
// create style, font
HSSFFont titleFont = (HSSFFont)workbook.createFont();
titleFont.setFontHeightInPoints((short)16);
// titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle titleCellStyle = (HSSFCellStyle)workbook.createCellStyle();
titleCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
titleCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
titleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
titleCellStyle.setFont(titleFont);
HSSFCellStyle titleSubCellStyle = (HSSFCellStyle)workbook.createCellStyle();
titleSubCellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
titleSubCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFCellStyle headerCellStyle = (HSSFCellStyle)workbook.createCellStyle();
headerCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headerCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
headerCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headerCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headerCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
headerCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
HSSFCellStyle dataCellStyle = (HSSFCellStyle)workbook.createCellStyle();
dataCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
dataCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
dataCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
dataCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// create sheet
HSSFSheet sheet = (HSSFSheet)workbook.createSheet("sheet01");
// create title row
HSSFRow titleRow = sheet.createRow(rowTitleNum);
titleRow.setHeightInPoints(34);
HSSFCell titleCell;
for(int i = 0 ; i < headerDataMap.keyList().size() ; i++){
titleCell = titleRow.createCell(i);
if(i == 0){
titleCell.setCellValue("TITLE");
}
titleCell.setCellStyle(titleCellStyle);
}
sheet.addMergedRegion(new CellRangeAddress(rowTitleNum,rowTitleNum,0,(headerDataMap.keyList().size()-1)));
// create titleSub row
HSSFRow titleSubRow = sheet.createRow(rowTitleSubNum);
HSSFCell titleSubCell;
for(int i = 0 ; i < headerDataMap.keyList().size() ; i++){
titleSubCell = titleSubRow.createCell(i);
if(i == 0){
titleSubCell.setCellValue("작성일자 :"+currentDate("YYYY년 MM월 DD일 HH:MM:SS"));
}
titleSubCell.setCellStyle(titleSubCellStyle);
}
sheet.addMergedRegion(new CellRangeAddress(rowTitleSubNum,rowTitleSubNum,0,(headerDataMap.keyList().size()-1)));
// create header row
HSSFRow headerRow = sheet.createRow(rowHeaderNum);
HSSFCell hearderCell;
for(int i = 0 ; i < headerDataMap.keyList().size() ; i++){
hearderCell = headerRow.createCell(i);
hearderCell.setCellStyle(headerCellStyle);
hearderCell.setCellValue((String)headerDataMap.get(i));
}
// create data row
HSSFRow row = null;
HSSFCell cell = null;
for(int rowIdx = 0 ; rowIdx < excelDataList.size() ; rowIdx++){
row = sheet.createRow(rowDataNum+rowIdx);
DataMap rowData = excelDataList.get(rowIdx);
for(int cellIdx = 0 ; cellIdx < headerDataMap.keyList().size() ; cellIdx++){
cell = row.createCell(cellIdx);
String cellKey = (String)headerDataMap.keyList().get(cellIdx);
Object cellData = rowData.get(cellKey);
if(cellData instanceof String){
cell.setCellValue((String)cellData);
}else if(cellData instanceof Integer){
cell.setCellValue((Integer)cellData);
}else if(cellData instanceof Long){
cell.setCellValue((Long)cellData);
}else if(cellData instanceof Double){
cell.setCellValue((Double)cellData);
}
cell.setCellStyle(dataCellStyle);
}
}
// create cell size
for(int i = 0 ; i < headerDataMap.keyList().size() ; i++){
// sheet.autoSizeColumn(i,true);
}
}
private void buildExcelDocument2007(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) {
// TODO Auto-generated method stub
// init
List<DataMap> excelDataList = (List<DataMap>)model.get(ExcelConstant.EXCEL_DATA_LIST);
DataMap headerDataMap = excelDataList.get(0);
int rowTitleNum = 0;
int rowTitleSubNum = 2;
int rowHeaderNum = 3;
int rowDataNum = 4;
// create style, font
XSSFFont titleFont = (XSSFFont)workbook.createFont();
titleFont.setFontHeightInPoints((short)16);
// titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
XSSFCellStyle titleCellStyle = (XSSFCellStyle)workbook.createCellStyle();
titleCellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
titleCellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
titleCellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
titleCellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
titleCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
titleCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
titleCellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER_SELECTION);
titleCellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
titleCellStyle.setFont(titleFont);
XSSFCellStyle titleSubCellStyle = (XSSFCellStyle)workbook.createCellStyle();
titleSubCellStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
titleSubCellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
XSSFCellStyle headerCellStyle = (XSSFCellStyle)workbook.createCellStyle();
headerCellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
headerCellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
headerCellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
headerCellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
headerCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
headerCellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
XSSFCellStyle dataCellStyle = (XSSFCellStyle)workbook.createCellStyle();
dataCellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
dataCellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
dataCellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
dataCellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
// create sheet
XSSFSheet sheet = (XSSFSheet)workbook.createSheet("sheet01");
// create title row
XSSFRow titleRow = sheet.createRow(rowTitleNum);
titleRow.setHeightInPoints(34);
XSSFCell titleCell;
for(int i = 0 ; i < headerDataMap.keyList().size() ; i++){
titleCell = titleRow.createCell(i);
if(i == 0){
titleCell.setCellValue("TITLE");
}
titleCell.setCellStyle(titleCellStyle);
}
sheet.addMergedRegion(new CellRangeAddress(rowTitleNum,rowTitleNum,0,(headerDataMap.keyList().size()-1)));
// create titleSub row
XSSFRow titleSubRow = sheet.createRow(rowTitleSubNum);
XSSFCell titleSubCell;
for(int i = 0 ; i < headerDataMap.keyList().size() ; i++){
titleSubCell = titleSubRow.createCell(i);
if(i == 0){
titleSubCell.setCellValue("작성일자 :"+currentDate("YYYY년 MM월 DD일 HH:MM:SS"));
}
titleSubCell.setCellStyle(titleSubCellStyle);
}
sheet.addMergedRegion(new CellRangeAddress(rowTitleSubNum,rowTitleSubNum,0,(headerDataMap.keyList().size()-1)));
// create header row
XSSFRow headerRow = sheet.createRow(rowHeaderNum);
XSSFCell hearderCell;
for(int i = 0 ; i < headerDataMap.keyList().size() ; i++){
hearderCell = headerRow.createCell(i);
hearderCell.setCellStyle(headerCellStyle);
hearderCell.setCellValue((String)headerDataMap.get(i));
}
// create data row
XSSFRow row = null;
XSSFCell cell = null;
for(int rowIdx = 0 ; rowIdx < excelDataList.size() ; rowIdx++){
row = sheet.createRow(rowDataNum+rowIdx);
DataMap rowData = excelDataList.get(rowIdx);
for(int cellIdx = 0 ; cellIdx < headerDataMap.keyList().size() ; cellIdx++){
cell = row.createCell(cellIdx);
String cellKey = (String)headerDataMap.keyList().get(cellIdx);
Object cellData = rowData.get(cellKey);
if(cellData instanceof String){
cell.setCellValue((String)cellData);
}else if(cellData instanceof Integer){
cell.setCellValue((Integer)cellData);
}else if(cellData instanceof Long){
cell.setCellValue((Long)cellData);
}else if(cellData instanceof Double){
cell.setCellValue((Double)cellData);
}
cell.setCellStyle(dataCellStyle);
}
}
// create cell size
for(int i = 0 ; i < headerDataMap.keyList().size() ; i++){
// sheet.autoSizeColumn(i,true);
}
}
}