POI 엑셀 다운로드

2018. 7. 11. 16:26

최대 row수는 65000 으로 설정해 놓았다.

65000이 넘어가면 새로운 시트를 생성하여 데이터 추가.

1. 디펜던시 추가

compile group: 'org.apache.poi', name: 'poi', version: '3.15'
compile group: 'org.apache.poi', name: 'poi-ooxml', version: '3.15'

2. ExcelDownView.java

import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Component;
import org.springframework.web.servlet.view.document.AbstractXlsxView;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.solution.framework.util.StringUtil;
import com.solution.framework.util.mapper.CamelMap;

/**
 * 엑셀다운로드 뷰
 * @author ljo
 */
@Component("excelView")
public class ExcelDownView extends AbstractXlsxView {

	private Sheet worksheet = null;
	private Row row = null;
	private Cell cell = null;
	private static final String[] CHECK_COMMA = {"Cnt","Amt","point","Point"};

	@Override
	protected void buildExcelDocument(Map ModelMap, Workbook workbook, HttpServletRequest request,
			HttpServletResponse response) throws Exception {

		// 공통 style
		CellStyle commonStyle = workbook.createCellStyle();
		setDefaultStyle(workbook, commonStyle);
		// 타이틀 style
		CellStyle titleStyle = workbook.createCellStyle();
		titleStyle.cloneStyleFrom(commonStyle);
		titleStyle.setFillForegroundColor(HSSFColor.AQUA.index);
		titleStyle.setAlignment (HorizontalAlignment.LEFT);
		// 컬럼 style
		CellStyle columnStyle = workbook.createCellStyle();
		columnStyle.cloneStyleFrom(commonStyle);
		@SuppressWarnings("resource")
		HSSFWorkbook hwb = new HSSFWorkbook();
		HSSFPalette palette = hwb.getCustomPalette();
		HSSFColor myColor = palette.findSimilarColor(255, 250, 192);
		short palIndex = myColor.getIndex();
		columnStyle.setFillForegroundColor(palIndex);

		// 목록 style
		CellStyle itemStyle = null;

		// 파일명
		String fileName = StringUtils.defaultIfEmpty((String) ModelMap.get("TITLE"), Long.toString(System.currentTimeMillis()));
		// 제목
		String title = StringUtils.defaultIfEmpty((String) ModelMap.get("TITLE"), "");
		// 시트명
		String sheetNm = StringUtils.defaultIfEmpty(title, "sheet");
		// 컬럼명
		String[] excelColumn = (String[]) ModelMap.get("EXCEL_COLUMN");
		// 데이터컬럼
		String[] dataColumn = (String[]) ModelMap.get("DB_COLUMN");
		// 데이터목록
		List list = (List) ModelMap.get("EXCELLIST");
		// 데이터 정렬
		Short[] align = (Short[]) ModelMap.get("CELL_ALIGN");
		int sheetCnt = 1;
		// 엑셀 최대 row
		int maxRow = 65000;
		// row
		int rowIndex = 0;
		// 시트생성
		rowIndex = setSheet(workbook, sheetNm + "" + sheetCnt++, title, rowIndex, titleStyle, excelColumn, columnStyle);

		// 목록 스타일
		// 너무 많이 생성하면 오류가 나기때문에 스타일을 먼저 생성하여 배열에 담는다.
		// 기본정렬 : center
		ArrayList styleList = new ArrayList();
		for (int j = 0; j < excelColumn.length; j++) {
			itemStyle = workbook.createCellStyle();
			if (align != null) {
				if("1".equals(StringUtil.valueOf(align[j]))){
					itemStyle.setAlignment(HorizontalAlignment.LEFT);
				}else if("3".equals(StringUtil.valueOf(align[j]))){
					itemStyle.setAlignment(HorizontalAlignment.RIGHT);
				}else{
					itemStyle.setAlignment(HorizontalAlignment.CENTER);
				}
			} else {
				itemStyle.setAlignment(HorizontalAlignment.CENTER);
			}
			styleList.add(itemStyle);
		}

		// 목록생성
		if (list != null && !list.isEmpty()) {
			for (int i = 0; i < list.size(); i++) {
				CamelMap data = new ObjectMapper().convertValue(list.get(i), CamelMap.class);
				// 최대 row 에러방지 새로운 시트생성
				if (rowIndex >= maxRow) {
					rowIndex = 0;
					rowIndex = setSheet(workbook, sheetNm + "" + sheetCnt++, title, rowIndex, titleStyle, excelColumn, columnStyle);
				}

				row = worksheet.createRow(rowIndex++);
				for (int j = 0; j < dataColumn.length; j++) {
					cell = row.createCell(j);
					cell.setCellStyle(styleList.get(j));
					if (data.get(dataColumn[j]) != null) {
						boolean chkNum = false;
						for (String chekString : CHECK_COMMA) {
							if(StringUtils.endsWith(dataColumn[j], chekString)){
								chkNum = true;
							}
						}
						String value = StringUtils.defaultString(StringUtil.valueOf(data.get(dataColumn[j])), "");
						if(chkNum){
							if(StringUtils.isNumeric(value)){
								DecimalFormat df = new DecimalFormat("#,###");
								value = df.format(new BigDecimal(value));
							}
						}
						cell.setCellValue(value);
					} else {
						cell.setCellValue("");
					}
				}
			}
		}
		// NO DATA
		else {
			row = worksheet.createRow(rowIndex++);
			for (int j = 0; j < excelColumn.length; j++) {
				commonStyle.setFillForegroundColor(HSSFColor.WHITE.index);
				cell = row.createCell(j);
				cell.setCellStyle(commonStyle);
				if (j == 0) {
					cell.setCellValue("NO DATA!");
				} else {
					cell.setCellValue("");
				}
			}
			worksheet
					.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 0, excelColumn.length - 1));
		}

		// width 자동조절
		for (int x = 0; x < worksheet.getRow(1).getPhysicalNumberOfCells(); x++) {
			worksheet.autoSizeColumn(x);
			int width = worksheet.getColumnWidth(x);
			int minWidth = excelColumn[x].getBytes().length * 450;
			int maxWidth = 18000;
			if (minWidth > width) {
				worksheet.setColumnWidth(x, minWidth);
			} else if (width > maxWidth) {
				worksheet.setColumnWidth(x, maxWidth);
			} else {
				worksheet.setColumnWidth(x, width + 2000);
			}
		}

		response.setContentType("application/vnd.ms-excel;charset=utf-8");
		response.setHeader("Content-Disposition", "attachment;filename=" + fileName + "-excel.xls");
	}

	/**
	 * Comment : 공통 style
	 * @param workbook
	 * @param commonStyle
	 */
	protected void setDefaultStyle(Workbook workbook, CellStyle commonStyle) {
		commonStyle.setBorderBottom(BorderStyle.THIN);
		commonStyle.setBottomBorderColor(HSSFColor.BLACK.index);
		commonStyle.setBorderLeft(BorderStyle.THIN);
		commonStyle.setLeftBorderColor(HSSFColor.BLACK.index);
		commonStyle.setBorderRight(BorderStyle.THIN);
		commonStyle.setRightBorderColor(HSSFColor.BLACK.index);
		commonStyle.setBorderTop(BorderStyle.THIN);
		commonStyle.setTopBorderColor(HSSFColor.BLACK.index);
		commonStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
		commonStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		Font font = workbook.createFont(); // 폰트 객체 생성
		font.setFontHeightInPoints((short) 10); // 폰트 크기
		font.setBold(true);
		// 셀 색 추가
		commonStyle.setFillBackgroundColor(HSSFColor.WHITE.index);
		commonStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		commonStyle.setFont(font); // 폰트 스타일 적용
	}

	/**
	 * Comment : 시트생성
	 * @param workbook
	 * @param sheetNm
	 * @param title
	 * @param rowIndex
	 * @param titleStyle
	 * @param highlightStyle
	 * @param highlight
	 * @param excelColumn
	 * @param columnStyle
	 * @return
	 */
	private int setSheet(Workbook workbook, String sheetNm, String title, int tmpRowIndex, CellStyle titleStyle,
			String[] excelColumn, CellStyle columnStyle) {
		int rowIndex = tmpRowIndex;
		// sheet
		worksheet = workbook.createSheet(sheetNm);

		// 타이틀
		if (StringUtils.isNotEmpty(title)) {
			row = worksheet.createRow(rowIndex++);
			row.setHeight((short) 800);
			cell = row.createCell(0);
			cell.setCellStyle(titleStyle);
			cell.setCellValue("■ " + title);
			// 타이틀 병합
			worksheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 0, excelColumn.length - 1));
		}

		// 컬럼생성
		row = worksheet.createRow(rowIndex++);
		row.setHeight((short) 400);
		for (int i = 0; i < excelColumn.length; i++) {
			cell = row.createCell(i);
			cell.setCellStyle(columnStyle);
			cell.setCellValue(excelColumn[i]);
		}
		return rowIndex;
	}
}

3. 사용법

@RequestMapping(value="/export/Excel")
public String exportExcel(HttpServletRequest request, FrontAdVo vo, HttpSession session, ModelMap modelMap) throws Exception{
	//타이틀
	modelMap.put("TITLE", "REPORT");

	//필드명
	modelMap.put("EXCEL_COLUMN", new String[]{
		"일자",
		"매체명",
		"지면명"
	});

	//컬럼명
	modelMap.put("DB_COLUMN", new String[]{
		"viewDay",
		"coNm",
		"scriptNm"
	});

	/**
	 * 정렬
	 * align
	 * 1 : left
	 * 2 : center
	 * 3 : right
	 */
	modelMap.put("CELL_ALIGN", new Short[]{
			2,
			2,
			2
	});
	List list = dao.excelList(vo);
	modelMap.put("EXCELLIST", list);
	return "excelView";
}

4. String boot BeanNameViewResolver 설정

@Bean
public ViewResolver viewResolver() {
	return new BeanNameViewResolver();
}

광고 : https://play.google.com/store/apps/details?id=com.ljo.blocktube

이게불낙이야! - 광고차단 - Google Play 앱

동영상 광고 및 배너 광고를 제거합니다.

play.google.com

'개발 > java&kotlin' 카테고리의 다른 글

Spring boot - logback 로그처리 (0)	2020.03.27
java 로 캡챠 구현 (0)	2020.03.26
WebBindingInitializer 활용 (0)	2018.07.12
spring 크로스 도메인 처리 (0)	2018.07.12
spring boot 스케줄러 설정 (0)	2018.07.11

LJO

POI 엑셀 다운로드

'개발 > java&kotlin' 카테고리의 다른 글

티스토리툴바