메모장
엑셀 다운로드 (자바, 자바스크립트)
현호s
2022. 4. 24. 18:11
반응형
# 엑셀 다운로드 관련
- 엑셀 다운로드 기능 구현 관련 자바소스
@RequestMapping(value="goExcelFileDown.ex", method=RequestMethod.POST)
public void goExcelFileDown(HashMap map, HttpServletRequest req, HttpServletResponse response) throw Exception {
String columnIdsArray = req.getParameter("columnArr");
String titleArray = req.getParameter("titleArr");
List<String> columnIdList = new ArrayList<String>(Arrays.asList(columnIdsArray.split(",");
List<String> titleList = new ArrayList<String>(Arrays.asList(titleArrayArray.split(",");
XSSFWorkbook xlsWb = new XSSFWorkbook(); // xlsx 엑셀 2007 이상
Sheet sheet1 = xlsWb.createSheet("시트1이름");
Sheet sheet2 = xlsWb.createSheet("시트2이름");
// 폰트
Font hFont = xlsWb.createFont();
hFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
// xlsx 스타일
// 제목 스타일
CellStyle hstyle = xlsWb.createCellStyle();
hStyle.setAlignment(CellStyle.ALIGN_CENTER);
hStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
hStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
hStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
hStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
hStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
hStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
hStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
hStyle.setFont(hFont);
// 내용 스타일
CellStyle bstyle = xlsWb.createCellStyle();
bStyle.setAlignment(CellStyle.ALIGN_CENTER);
bStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
bStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
bStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
bStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
bStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// 내용 스타일 - 조건에 따라 다른 스타일 적용위함.
CellStyle dstyle = xlsWb.createCellStyle();
dStyle.setAlignment(CellStyle.ALIGN_CENTER);
dStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
dStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
dStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
dStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
dStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
dStyle.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
dStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
Row row1 = null;
Row row2 = null;
Cell cell1 = null;
Cell cell2 = null;
int colIndex1 = 0;
int colIndex2 = 0;
row1 = sheet1.createRow(0);
row1.setHeight((short)450);
sheet1.addMergeRegion(new CellRangeAddress(0, 0, 3, 4)); // 셀 병합
sheet1.addMergeRegion(new CellRangeAddress(0, 0, 5, 6)); // 셀 병합
for (Iterator iterator = titleList.iterator(); iterator.hasNext();) {
String string = (String) iterator.next();
cell1 = row.createCell(colIndex1);
cell1.setCellValue(string);
cell1.setCellStyle(hStyle);
// 셀에 따라 넓이 다르게 설정
if (colIndex1 == 2) {
sheet1.setColumnWidth(colIndex1, 14000);
} else if (colIndex1 == 6) {
sheet1.setColumnWidth(colIndex1, 10000);
} else {
sheet1.setColumnWidth(colIndex1, 4500);
}
colIndex1++;
}
row2 = sheet2.createRow(0);
row2.setHeight((short)450);
sheet2.addMergeRegion(new CellRangeAddress(0, 0, 3, 4)); // 셀 병합
sheet2.addMergeRegion(new CellRangeAddress(0, 0, 5, 6)); // 셀 병합
for (Iterator iterator = titleList.iterator(); iterator.hasNext();) {
String string = (String) iterator.next();
cell2 = row.createCell(colIndex2);
cell2.setCellValue(string);
cell2.setCellStyle(hStyle);
// 셀에 따라 넓이 다르게 설정
if (colIndex2 == 2) {
sheet2.setColumnWidth(colIndex2, 14000);
} else if (colIndex2 == 6) {
sheet2.setColumnWidth(colIndex2, 10000);
} else {
sheet2.setColumnWidth(colIndex2, 4500);
}
colIndex2++;
}
Liist<Map<String, String>> resultMap1 = new ArrayList<Map<String, String>>();
Liist<Map<String, String>> resultMap2 = new ArrayList<Map<String, String>>();
resultMap1 = (List<Map<String, String>>) dao.selectResult1();
resultMap2 = (List<Map<String, String>>) dao.selectResult2();
int rowIndex1 = 0;
int rowIndex2 = 0;
for (Map<String, String> forMap : resultMap1) {
row1 = sheet1.createRow(rowIndex1);
row1.setHeight((short)450);
colIndex1 = 0;
for (Iterator iterator = columnIdList.iterator(); iterator.hasNext();) {
String string = (String) iterator.next();
cell1 = row1.createCell(colIndex1);
String cellVal = (EgovStringUtil.isNull(String.valueOf(forMap.get(string))) || String.valueOf(forMap.get(string)).equals("null")) ? "" : string.valueOf(forMap.get(string));
cell1.setCellValue(cellVal);
celll1.setCellStyle(bStyle);
colIndex1++;
}
rowIndex1++;
}
for (Map<String, String> forMap : resultMap2) {
row2 = sheet2.createRow(rowIndex2);
row2.setHeight((short)450);
colIndex2 = 0;
for (Iterator iterator = columnIdList.iterator(); iterator.hasNext();) {
String string = (String) iterator.next();
cell2 = row2.createCell(colIndex2);
String cellVal = (EgovStringUtil.isNull(String.valueOf(forMap.get(string))) || String.valueOf(forMap.get(string)).equals("null")) ? "" : string.valueOf(forMap.get(string));
cell2.setCellValue(cellVal);
celll2.setCellStyle(bStyle);
colIndex2++;
}
rowIndex2++;
}
try {
String pathDir = JProperties.getString(GlobalVariables.DEFAULT_FILE_UPLOAD_PATH_KEY) + File.separator + "fi" + File.separator + "9999" + File.separator + "99" File.separator + "99";
String fileName = File.separator + ".xlsx";
String readlFileName = "파일명입력.xlsx";
File xlsFileDir = new File(pathDir);
if (!xlsFileDir.exists()) xlsFileDir.mkdirs();
File xlsFile = new File(pathDir + fileName);
if (xlsFile.exists()) xlsFileDir.delete();
if (xlsFile.createNewFile()) {
FileOutputStream fileOut = new FileOutputStream(xlsFile);
xlsWb.write(fileOut);
String strFileName = URLEncoder.encode(new String(realFileName.getBytes("8859_1"), "euc-kr"), "UTF-8");
response.setHeader("Content-Disposition", "attachment; filename = " + strFileName + ";");
FileInputStream fis = new FileInputStream(xlsFile);
BufferedInputStream bis = new BufferedInputStream(fis);
ServletOutputStream so = response.getOutputStream();
BufferedOutputStream bos = new BufferedOutputStream(so);
byte[] data = new byte[2048];
int input = 0;
while ((input = bis.read(data)) != -1) {
bos.write(data, 0, input);
bos.flush();
}
if (bos != null) bos.close();
if (bis != null) bis.close();
if (so != null) so.close();
if (fis != null) fis.close();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}## 자바스크립트
- 엑셀 다운로드 관련 자바 스크립트.
function excelDown() {
if (!confirm("다운로드 하시겠습니까?")) return;
var columnIdsArray = new Array();
var titleArray = new Array();
columnIdsArray.push("PROJECT_NO");
columnIdsArray.push("PROJECT_NAME");
columnIdsArray.push("ACCOUNT_OWNER");
titleArray.push("사업부호");
titleArray.push("사업명");
titleArray.push("책임자");
var paramMap = "";
var xhr = new XMLHttpRequest();
xhr.onreadystatechange = function() {
if (this.readSate == 4 && this.status == 200) {
var filename = "";
var disposition = xhr.getResponseHeader('Content-Disposition');
if (disposition && disposition.indexOf('attachment') != -1) {
var filenameRegex = /filename[^;=\n]*=((['"]).*?\2|[^;\n]*)/;
var matches = filenameRegex.exec(disposition);
if (matches != null && matches[1]) filename = matches[1].replace(/['"]/g, '');
}
}
window.navigator.msSaveOrOpenBlob(this.response, "파일명입력.xlsx");
}
xhr.open('POST', '<c:url value="goExcelFileDown.ex" />');
xhr.responseType = 'blob';
xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");
xhr.send(paramMap + "columnIdsArray=" + columnIdsArray + "&titleArray=" + titleArray);
}반응형