엑셀다운로드 Archives

Spring

Spring 엑셀 다운로드 구현

Spring 3.0 에서 – POI Library – Enum Model – AbstractExcelView 를 사용하여 간단한 엑셀다운로드 기능을 만들어보았는데 고칠 부분이 많이 보이긴 합니다..

<bean id="excelView" class="com.test.ExcelView"/>

1	<bean id="excelView" class="com.test.ExcelView"/>

public class ExcelView extends AbstractExcelView {
	@Override
	protected void buildExcelDocument(Map model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {

		ExcelInfo excels = (ExcelInfo)model.get("excelEnum");

		String fileNm = makeFileName(excels.getFileName(), request);

		response.setHeader("Content-Disposition", "attachment; filename=" + fileNm + "");
		response.setHeader("Content-Transfer-Encoding", "binary");
		response.setHeader("Cache-Control", "max-age=0, private, must-revalidate");

		HSSFSheet sheet = creatFirstSheet(workbook);

		List colNm = excels.getCellColumn();
		creatCol(sheet, colNm);

		List excelList = (List)model.get("excelList");

		if (CollectionUtils.isEmpty(excelList)) {
			return;
		}

		int rowNum = 1;
		List col = excels.getDbColumn();

		for (int i = 0; i &lt; excelList.size(); i++) {
			Map excelInfo = getExcelInfo(excelList.get(i));
			createRow(sheet, excelInfo, rowNum++, col);
		}
	}

	@SuppressWarnings("unchecked")
	private Map getExcelInfo(Object excelObj) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException {
		return PropertyUtils.describe(excelObj);
	}

	private String makeFileName(String fileNm, HttpServletRequest request) throws Exception {
		String userAgent = request.getHeader("User-Agent");

		String agentFileName = isIE(userAgent) ? URLEncoder.encode(fileNm, "UTF-8") : new String(fileNm.getBytes("UTF-8"), "8859_1");
		return agentFileName + "_" + DateUtil.getDate() + ".xls";
	}

	private boolean isIE(String userAgent) {
		return userAgent.indexOf("MSIE") &gt; -1;
	}

	private HSSFSheet creatFirstSheet(HSSFWorkbook workbook) {
		HSSFSheet sheet = workbook.createSheet();
		workbook.setSheetName(0, "ExportData");
		return sheet;
	}

	private void creatCol(HSSFSheet sheet, List colNm) {
		HSSFRow header = sheet.createRow(0);
		HSSFCell cell = header.createCell(0);
		for (int i = 0; i &lt; colNm.size(); i++) {
			cell.setCellValue(colNm.get(i));
			cell = header.createCell(i + 1);
		}
	}

	private void createRow(HSSFSheet sheet, Map excelInfo, int rowNum, List col) {
		HSSFRow row = sheet.createRow(rowNum);
		HSSFCell cell = row.createCell(0);
		for (int i = 0; i &lt; col.size(); i++) {
			cell.setCellValue(String.valueOf(excelInfo.get(col.get(i))));
			cell = row.createCell(i + 1);
		}
	}
}

public class ExcelView extends AbstractExcelView {

@Override

protected void buildExcelDocument(Map model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {

ExcelInfo excels = (ExcelInfo)model.get("excelEnum");

String fileNm = makeFileName(excels.getFileName(), request);

response.setHeader("Content-Disposition", "attachment; filename=" + fileNm + "");

response.setHeader("Content-Transfer-Encoding", "binary");

response.setHeader("Cache-Control", "max-age=0, private, must-revalidate");

HSSFSheet sheet = creatFirstSheet(workbook);

List colNm = excels.getCellColumn();

creatCol(sheet, colNm);

List excelList = (List)model.get("excelList");

if (CollectionUtils.isEmpty(excelList)) {

return;

}

int rowNum = 1;

List col = excels.getDbColumn();

for (int i = 0; i < excelList.size(); i++) {

Map excelInfo = getExcelInfo(excelList.get(i));

createRow(sheet, excelInfo, rowNum++, col);

}

@SuppressWarnings("unchecked")

private Map getExcelInfo(Object excelObj) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException {

return PropertyUtils.describe(excelObj);

}

private String makeFileName(String fileNm, HttpServletRequest request) throws Exception {

String userAgent = request.getHeader("User-Agent");

String agentFileName = isIE(userAgent) ? URLEncoder.encode(fileNm, "UTF-8") : new String(fileNm.getBytes("UTF-8"), "8859_1");

return agentFileName + "_" + DateUtil.getDate() + ".xls";

}

private boolean isIE(String userAgent) {

return userAgent.indexOf("MSIE") > -1;

}

private HSSFSheet creatFirstSheet(HSSFWorkbook workbook) {

HSSFSheet sheet = workbook.createSheet();

workbook.setSheetName(0, "ExportData");

return sheet;

}

private void creatCol(HSSFSheet sheet, List colNm) {

HSSFRow header = sheet.createRow(0);

HSSFCell cell = header.createCell(0);

for (int i = 0; i < colNm.size(); i++) {

cell.setCellValue(colNm.get(i));

cell = header.createCell(i + 1);

}

private void createRow(HSSFSheet sheet, Map excelInfo, int rowNum, List col) {

HSSFRow row = sheet.createRow(rowNum);

HSSFCell cell = row.createCell(0);

for (int i = 0; i < col.size(); i++) {

cell.setCellValue(String.valueOf(excelInfo.get(col.get(i))));

cell = row.createCell(i + 1);

}

model.addAttribute("excelList", testBO.getTestList());
model.addAttribute("excelEnum", ExcelInfo.Test);
return "excelView";

model.addAttribute("excelList", testBO.getTestList());

model.addAttribute("excelEnum", ExcelInfo.Test);

return "excelView";

public enum ExcelInfo {
NextClass("Test",
		Arrays.asList(
			"id",
			"name",
		),
		Arrays.asList(
			"아이디",
			"이름",
		)
	);

	private String fileName;

	private List dbColumn;

	private List cellColumn;

	private ExcelInfo(String fileNm, List dbColumn, List cellColumn) {
		this.fileName = fileNm;
		this.dbColumn = dbColumn;
		this.cellColumn = cellColumn;
	}

	public String getFileName() {
		return fileName;
	}

	public List getDbColumn() {
		return dbColumn;
	}

	public List getCellColumn() {
		return cellColumn;
	}
}

public enum ExcelInfo {

NextClass("Test",

Arrays.asList(

"id",

"name",

Arrays.asList(

"아이디",

"이름",

)

);

private String fileName;

private List dbColumn;

private List cellColumn;

private ExcelInfo(String fileNm, List dbColumn, List cellColumn) {

this.fileName = fileNm;

this.dbColumn = dbColumn;

this.cellColumn = cellColumn;

}

public String getFileName() {

return fileName;

}

public List getDbColumn() {

return dbColumn;

}

public List getCellColumn() {

return cellColumn;

}

글쓴이 SilverNine, 8 년8 년전