[SPIRNG] Java Excel 구현 (JXL 라이브러리 활용)

excel.zip

0.03MB

 

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();

}

}