Spring MVC project 기준, Pom.xml에 다음과 같은 dependency를 추가한다.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
servlet-context.xml에 BeanNameViewResolver로 view를 매핑해준다.
<beans:bean id="beanNameResolver" class="org.springframework.web.servlet.view.BeanNameViewResolver">
<beans:property name="order" value="-1"></beans:property></beans:bean>
<beans:bean id="customExcel" class="com.myapp.controller.CustomExcel"></beans:bean>
customExcel 이름에 해당하는 view 컴포넌트 생성
1. AbstractXlsxView 를 상속받는다. http통신의 contentType이 엑셀로 매핑된다.
4. Template을 디자인한다. leggo
package com.myapp.controller;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
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.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.web.servlet.view.document.AbstractXlsxView;
@Component
public class CustomExcel extends AbstractXlsxView {
@Override
protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request,
HttpServletResponse response) throws Exception {
String fileName = URLEncoder.encode("test","utf-8") + ".xlsx";
response.setHeader("Content-Disposition", "attachment; filename=\""+fileName+"\";");
response.setHeader("Content-Transfer-Encoding", "binary");
@SuppressWarnings("unchecked")
Map<String, Object> excelModel = (Map<String, Object>) model.get("excel");
@SuppressWarnings("unchecked")
List<String> colName = (List<String>) excelModel.get("colName");
logger.info(colName);
@SuppressWarnings("unchecked")
List<List<String>> rowList = (List<List<String>>) excelModel.get("rowList");
logger.info(rowList);
String title = (String)excelModel.get("title");
Sheet sheet1 = workbook.createSheet("cookieShop");
Row row;
Cell cell;
int rowIdx = 0;
int cellIdx = 0;
row = sheet1.createRow(rowIdx++);
cell = row.createCell(cellIdx++);
cell.setCellValue(title);
cell.setCellStyle(takeCellStyle(workbook, "title"));
sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
for (int i = 1; i<6; i ++) {
cell = row.createCell(i);
cell.setCellStyle(takeCellStyle(workbook, "title"));
}
rowIdx++;
row = sheet1.createRow(rowIdx++);
cell = row.createCell(cellIdx++);
for (int i = 0; i< colName.size(); i++) {
cell = row.createCell(i);
cell.setCellValue(colName.get(i));
cell.setCellStyle(takeCellStyle(workbook, "data"));
}
for (List<String> tempList : rowList ) {
row = sheet1.createRow(rowIdx++);
cell = row.createCell(cellIdx++);
for (int i = 0; i< tempList.size(); i++) {
cell = row.createCell(i);
cell.setCellValue((String)tempList.get(i));
cell.setCellStyle(takeCellStyle(workbook, "data"));
}
}
for (int i=0; i<=12; i++){
sheet1.autoSizeColumn(i);
sheet1.setColumnWidth(i, (sheet1.getColumnWidth(i))+(short)1024);
}
}
public static CellStyle takeCellStyle(Workbook xlsxWb, String type) {
CellStyle cellStyle = xlsxWb.createCellStyle();
Font font = xlsxWb.createFont();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setBorderBottom(BorderStyle.MEDIUM);
cellStyle.setBorderLeft(BorderStyle.MEDIUM);
cellStyle.setBorderRight(BorderStyle.MEDIUM);
cellStyle.setBorderTop(BorderStyle.MEDIUM);
if(type.equals("title")) {
cellStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
font.setFontHeightInPoints((short) 11);
font.setBold(true);
font.setFontName("함초롬돋움");
cellStyle.setFont(font);
} else if(type.equals("data")) {
font.setFontHeightInPoints((short) 11);
font.setFontName("함초롬돋움");
cellStyle.setFont(font);
}
return cellStyle;
}
}
컨트롤러에서는 더 자유로운 방식으로 이렇게 보내면 됨.. (sample)
package com.myapp.controller;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;
@Controller
public class CustomController {
private final Logger logger = LoggerFactory.getLogger(this.getClass());
private final String className = this.getClass().toString();
@RequestMapping("/test/custom/excel")
public ModelAndView customExcel() throws Exception {
ModelAndView mv = new ModelAndView();
Map<String, Object> excelModel = new HashMap<>();
String title = "소소 제과점의 일별 판매실적";
excelModel.put("title", title);
List<String> colName = new ArrayList<>();
colName.add("날짜");
colName.add("마들렌");
colName.add("마카롱");
colName.add("쫀득꼬끄");
colName.add("다쿠아즈");
colName.add("뉴욕타임즈초코칩쿠키");
excelModel.put("colName", colName);
List<List<String>> rowList = new ArrayList<>();
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
Date startDate = formatter.parse("2020-12-25");
Date endDate = formatter.parse("2020-12-31");
LocalDate start = startDate.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
LocalDate end = endDate.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
for (LocalDate date = start; date.isBefore(end); date = date.plusDays(1)) {
List<String> tempObj = new ArrayList<>();
tempObj.add(date.toString());
tempObj.add(String.valueOf((int)(Math.random()*100)));
tempObj.add(String.valueOf((int)(Math.random()*100)));
tempObj.add(String.valueOf((int)(Math.random()*100)));
tempObj.add(String.valueOf((int)(Math.random()*100)));
tempObj.add(String.valueOf((int)(Math.random()*100)));
rowList.add(tempObj);
}
excelModel.put("rowList", rowList);
mv.addObject("excel", excelModel);
mv.setViewName("customExcel");
return mv;
}
}