카테고리 없음
<JSP> html 용 엑셀 다운로드 소스
달남
2019. 10. 28. 14:32
1.html 소스
<button type="button" class="btn btn-md-sel icon-excel" id="excelDn">엑셀 다운로드</button>엑셀 클릭용 자바스크립트 소스
$('#excelDn').click(function(){
$('#svyYr2').val($('#vn_svyYr2').val());
$("#title").val("표준분류체계");
$("#header1").val("대분류코드");
$("#header2").val("대분류명");
$("#header3").val("중분류코드");
$("#header4").val("중분류명");
$("#header5").val("소분류코드");
$("#header6").val("소분류명");
$("#frm").attr("action","<c:url value='/mjrinfo/mjrinfo0460/excelDown.do'/>");
$("#frm").attr("onsubmit","true");
$("#frm").submit();
});
2. 콘트롤러 소스
@RequestMapping(value = "/mjrinfo/mjrinfo0460/excelDown.do")
public void selectExelRst(@RequestParam Map<String, Object> pramMap, ModelMap model, HttpServletRequest request, HttpServletResponse response, Locale locale) throws Exception {
// 공통인수
pramMap.put("COLUMLAUNG", CommonUtill.getlocaleToColumStr(locale));
String svyYr2 = (pramMap.get("svyYr2") == null) ? "" : (String) pramMap.get("svyYr2");
// String schlId = (pramMap.get("schlId") == null) ? "" : (String) pramMap.get("schlId");
// pramMap.put("enYr" , svyYr);
// pramMap.put("slCd" , schlId);
// 엑셀 데이터를 생성한다.
// 헤더부분
String title = pramMap.get("title").toString();
String[] header = {
pramMap.get("header1").toString()
, pramMap.get("header2").toString()
, pramMap.get("header3").toString()
, pramMap.get("header4").toString()
, pramMap.get("header5").toString()
, pramMap.get("header6").toString()
};
String[] content = {"srs_lclft_cd", "l_nm", "srs_mclft_cd", "m_nm", "srs_sclft_cd", "s_nm"};
// 데이터부분
// List resultMap = pubinfo1700Service.selectStatRst(pramMap, locale);
List<MapDto> resultMap = mjrinfo0460Service.selectPopUpList(pramMap, locale);
List<Map> excelList = new ArrayList<Map>();
Map<String,Object> result = new HashMap();
for (int i = 0; i < resultMap.size(); i++) {
result = (Map) resultMap.get(i);
excelList.add(result);
}
// 엑셀다운
CommExcelDown commExcelDown = new CommExcelDown();
commExcelDown.ExcelDown(title, header, content, excelList, response);
}
3.엑셀 다운 호출용 자바 소스
package kr.or.kcue.com.util;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
/*import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.HSSFColor;
*/
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class CommExcelDown {
protected Log log = LogFactory.getLog(this.getClass());
public void ExcelDown(String title, String[] header, String[] content, List<Map> map, HttpServletResponse response) throws Exception {
XSSFRow row = null;
XSSFCell cell = null;
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet();
try {
workbook.setSheetName(0, title.replaceAll(" "," "));
int cellCount = header.length;
XSSFRow headerRow = sheet.createRow((short) 0);
XSSFCellStyle headerStyle = workbook.createCellStyle();
/*headerStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);*/
for (int i = 0; i < cellCount; i++) {
cell = headerRow.createCell(i);
cell.setCellValue(header[i].replaceAll(" "," "));
cell.setCellStyle(headerStyle);
}
if (map.size() > 0) {
for (int i = 0; i < map.size(); i++) {
row = sheet.createRow((short) (i + 1));
for (int j = 0; j < content.length; j++) {
cell = row.createCell(j);
String cont = "";
// log.debug("content[j]:"+content[j]);
// System.out.println("content[j]:"+content[j]);
// log.debug("map.get(i):"+map.get(i));
// System.out.println("map.get(i):"+map.get(i));
cont = String.valueOf(map.get(i).get(content[j])).replaceAll(" "," ");
if ("null".equals(cont)) {
cell.setCellValue("");
} else {
cell.setCellValue(cont);
}
//cell.setCellStyle(contentCellStyle);
}
}
} else {
row = sheet.createRow((short) 1);
for (int i = 0; i < cellCount; i++) {
cell = row.createCell(i);
if (i == 0) {
cell.setCellValue("등록된 데이터가 없습니다.");
}
//cell.setCellStyle(contentCellStyle);
}
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, cellCount - 1));
}
//autuSizeColumn after setColumnWidth setting!!
for (int i = 0; i < cellCount; i++) {
sheet.autoSizeColumn(i);
sheet.setColumnWidth(i, (sheet.getColumnWidth(i)) + 512);
}
title = URLEncoder.encode(title,"UTF-8").replaceAll("\\+", "%20");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
// response.setHeader("Content-Disposition", "attachment;filename=" + new String((title).getBytes("KSC5601"), "8859_1") + ".xlsx");
response.setHeader("Content-Disposition", "attachment;filename=" +title + ".xlsx");
workbook.write(response.getOutputStream());
} catch (NullPointerException e) {
log.error(e.getMessage());
} catch (Exception e) {
log.error(e.getMessage());
}finally {
if (workbook != null) { try { workbook.close(); } catch (Exception ex) {log.error(ex.getMessage()); } }
}
}
public void ExcelDownMerge(String title, String[] header, String[] content, List<Map> map, HttpServletResponse response) throws Exception {
XSSFRow row = null;
XSSFCell cell = null;
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet();
try {
workbook.setSheetName(0, title);
int cellCount = header.length;
XSSFRow headerRow = sheet.createRow((short) 1);
XSSFCellStyle headerStyle = workbook.createCellStyle();
/*headerStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);*/
//cell 머지 부분
row = sheet.createRow((short) 0);
cell = row.createCell(0, 3);
cell.setCellValue(title);
//cell.setCellStyle(headerStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
//cell 머지 부분 종료
for (int i = 0; i < cellCount; i++) {
cell = headerRow.createCell(i);
cell.setCellValue(header[i]);
cell.setCellStyle(headerStyle);
}
if (map.size() > 0) {
for (int i = 0; i < map.size(); i++) {
row = sheet.createRow((short) (i + 2));
for (int j = 0; j < content.length; j++) {
cell = row.createCell(j);
String cont = "";
/* log.debug("content[j]:"+content[j]);
System.out.println("content[j]:"+content[j]);
log.debug("map.get(i):"+map.get(i));
System.out.println("map.get(i):"+map.get(i));*/
cont = String.valueOf(map.get(i).get(content[j]));
if ("null".equals(cont)) {
cell.setCellValue("");
} else {
cell.setCellValue(cont);
}
//cell.setCellStyle(contentCellStyle);
}
}
} else {
row = sheet.createRow((short) 1);
for (int i = 0; i < cellCount; i++) {
cell = row.createCell(i);
if (i == 0) {
cell.setCellValue("등록된 데이터가 없습니다.");
}
//cell.setCellStyle(contentCellStyle);
}
sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 4));
}
//autuSizeColumn after setColumnWidth setting!!
for (int i = 0; i < cellCount; i++) {
sheet.autoSizeColumn(i);
sheet.setColumnWidth(i, (sheet.getColumnWidth(i)) + 512);
}
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((title).getBytes("KSC5601"), "8859_1") + ".xlsx");
workbook.write(response.getOutputStream());
} catch (NullPointerException e) {
log.error(e.getMessage());
} catch (Exception e) {
log.error(e.getMessage());
}finally {
if (workbook != null) { try { workbook.close(); } catch (Exception ex) {log.error(ex.getMessage()); } }
}
}
}