1. pom.xml
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
2. Controller.java
@RequestMapping(value = "/stat/excel", method = RequestMethod.GET)
public View StatExcelView(ModelMap model, HttpServletRequest request) throws Exception {
return new StatExcelView();
}
3. StatExcelView.java
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.web.servlet.view.document.AbstractJExcelView;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class StatExcelView extends AbstractJExcelView {
protected void buildExcelDocument(Map<String, Object> model, WritableWorkbook workbook, HttpServletRequest request,
HttpServletResponse response) throws Exception {
String mode = "modeName";
String title = "test";
String fileName = createFileName(mode + "_Statistics");
setFileNameToResponse(request, response, fileName);
WritableSheet sheet = workbook.createSheet(title, 0);
// Cell Width Size
sheet.setColumnView(0, 30);
sheet.setColumnView(1, 20);
sheet.setColumnView(2, 15);
sheet.setColumnView(3, 20);
sheet.setColumnView(4, 20);
sheet.setColumnView(5, 20);
sheet.setColumnView(6, 20);
sheet.setColumnView(7, 20);
sheet.setColumnView(8, 20);
WritableCellFormat titleFormat = ExcelUtil.setTitleFormat();
WritableCellFormat sumFormat = ExcelUtil.setTitleFormat2();
sheet.addCell(new jxl.write.Label(0, 0, "일자", titleFormat));
sheet.addCell(new jxl.write.Label(0, 1, "합계", sumFormat));
sheet.addCell(new jxl.write.Label(1, 0, "Title1", titleFormat));
sheet.addCell(new jxl.write.Label(2, 0, "Title2", titleFormat));
sheet.addCell(new jxl.write.Label(3, 0, "Title3", titleFormat));
sheet.addCell(new jxl.write.Label(4, 0, "Title4", titleFormat));
sheet.addCell(new jxl.write.Label(5, 0, "Title5", titleFormat));
sheet.addCell(new jxl.write.Label(6, 0, "Title6", titleFormat));
sheet.addCell(new jxl.write.Label(7, 0, "Title7", titleFormat));
sheet.addCell(new jxl.write.Label(8, 0, "Title8", titleFormat));
sheet.addCell(new jxl.write.Label(1, 1, "", sumFormat));
sheet.addCell(new jxl.write.Label(2, 1, "", sumFormat));
sheet.addCell(new jxl.write.Label(3, 1, Integer.toString(0), sumFormat));
sheet.addCell(new jxl.write.Label(4, 1, Integer.toString(0), sumFormat));
sheet.addCell(new jxl.write.Label(5, 1, Integer.toString(0), sumFormat));
sheet.addCell(new jxl.write.Label(6, 1, Integer.toString(0), sumFormat));
sheet.addCell(new jxl.write.Label(7, 1, Double.toString(0) + "%", sumFormat));
sheet.addCell(new jxl.write.Label(8, 1, Double.toString(0) + "%", sumFormat));
WritableCellFormat cellFormat = ExcelUtil.setCellFormat();
sheet.addCell(new jxl.write.Label(0, 2, "1900-01-01", cellFormat));
sheet.addCell(new jxl.write.Label(1, 2, "0", cellFormat));
sheet.addCell(new jxl.write.Label(2, 2, "0", cellFormat));
sheet.addCell(new jxl.write.Label(3, 2, "0", cellFormat));
sheet.addCell(new jxl.write.Label(4, 2, "0", cellFormat));
sheet.addCell(new jxl.write.Label(5, 2, "0", cellFormat));
sheet.addCell(new jxl.write.Label(6, 2, "0", cellFormat));
sheet.addCell(new jxl.write.Label(7, 2, "0", cellFormat));
sheet.addCell(new jxl.write.Label(8, 2, "0", cellFormat));
}
private void setFileNameToResponse(HttpServletRequest request, HttpServletResponse response, String fileName) {
String userAgent = request.getHeader("User-Agent");
if (userAgent.indexOf("MSIE 5.5") >= 0) {
response.setContentType("doesn/matter");
response.setHeader("Content-Disposition", "filename=\"" + fileName + "\"");
}
else {
response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
}
}
private String createFileName(String fileName) {
SimpleDateFormat fileFormat = new SimpleDateFormat("yyyyMMdd_HHmmss");
return new StringBuilder(fileName).append("_").append(fileFormat.format(new Date())).append(".xls").toString();
}
}
4. ExcelUtil.java
import java.text.SimpleDateFormat;
import java.util.Date;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.ScriptStyle;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
public class ExcelUtil {
/**
* <pre>엑셀 제목 Format</pre>
* @return 엑셀 Cell format
* @throws Exception
* @return WritableCellFormat
*/
public static WritableCellFormat setTitleFormat() throws Exception {
WritableCellFormat titleFormat = new WritableCellFormat();
try {
titleFormat.setFont(new WritableFont(WritableFont.ARIAL, // Font
// Type
13, // Font Size
WritableFont.BOLD, // Font Weight
false, // Font Italic 여부
UnderlineStyle.NO_UNDERLINE, // Underline
Colour.BLACK, // Font Color
ScriptStyle.NORMAL_SCRIPT // Script Style
));
titleFormat.setAlignment(Alignment.CENTRE); // Cell Text-align
titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // Cell
// Vertical
// Align
titleFormat.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
titleFormat.setBackground(Colour.GREY_25_PERCENT);
} catch (Exception e) {
e.printStackTrace();
}
return titleFormat;
}
/**
* <pre>엑셀 제목 Format</pre>
* @return
* @throws Exception
* @return WritableCellFormat
*/
public static WritableCellFormat setTitleFormat2() throws Exception {
WritableCellFormat titleFormat = new WritableCellFormat(NumberFormats.TEXT);
try {
titleFormat.setFont(new WritableFont(WritableFont.ARIAL, // Font
// Type
13, // Font Size
WritableFont.BOLD, // Font Weight
false, // Font Italic 여부
UnderlineStyle.NO_UNDERLINE, // Underline
Colour.BLACK, // Font Color
ScriptStyle.NORMAL_SCRIPT // Script Style
));
titleFormat.setAlignment(Alignment.CENTRE); // Cell Text-align
titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // Cell
// Vertical
// Align
titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
titleFormat.setBackground(Colour.YELLOW2);
} catch (Exception e) {
e.printStackTrace();
}
return titleFormat;
}
/**
* <pre>엑셀 일반 셀 스타일</pre>
* @return 엑셀 Cell format
* @throws Exception
* @return WritableCellFormat
*/
public static WritableCellFormat setCellFormat() throws Exception {
WritableCellFormat cellFormat = new WritableCellFormat(NumberFormats.TEXT);
try {
cellFormat.setFont(new WritableFont(WritableFont.ARIAL, 11, WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.GRAY_80, ScriptStyle.NORMAL_SCRIPT));
cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
cellFormat.setAlignment(Alignment.CENTRE);
} catch (Exception e) {
e.printStackTrace();
}
return cellFormat;
}
public static String createFileName(String fileName) {
SimpleDateFormat fileFormat = new SimpleDateFormat("yyyyMMdd_HHmmss");
return new StringBuilder(fileName).append("_").append(fileFormat.format(new Date())).append(".xls").toString();
}
}
'Develops > Spirng' 카테고리의 다른 글
[SPRING] Tiles Framework 이란? (화면 레이아웃 라이브러리) (0) | 2024.07.14 |
---|---|
[SPRING] JPA(Java Persistence API)이란? (0) | 2024.03.24 |
[SPRING] 로그인 및 회원 관리(등록, 수정, 삭제) 기능 예시 (0) | 2024.03.03 |