Excel Service

Excel 파일 포맷을 다룰 수 있는 자바 라이브러리를 제공하여, 사용자들이 데이터를 Excel 파일 포맷으로 다운받거나, 대량의 Excel 데이터를 시스템에 올릴 수 있도록 지원하기 위한 서비스이다.
Excel 서비스는 Apache POI 오픈소스를 사용하여 구현하였으며 주요 Excel접근 기능 외에 Excel 다운로드, Excel 파일 업로드 등의 기능이 있다.

엑셀 파일을 생성하여 지정된 위치에 저장하는 기능을 제공한다.
HSSFWorkbook 인스턴스를 생성하여 Excel sheet를 추가 생성할 수 있다.

엑셀 버전에 따라 엑셀 97~2003버전인 HSSFWorkbook, 엑셀 2007이상의 XSSFWorkbook, 가장 최근에 나온 성능개선버전의 SXSSFWorkbook 등의 클래스를 사용할 수 있으며, 각 클래스별 사용 법(method)는 동일하다.

HSSFWorkbook wb = new HSSFWorkbook();
 
HSSFSheet sheet1 = wb.createSheet(sheetName1);
HSSFSheet sheet2 = wb.createSheet(sheetName2);
HSSFSheet sheet = wb.createSheet();
 
// 엑셀 파일 생성
HSSFWorkbook tmp = excelService.createWorkbook(wb, filename);
 
// 파일 존재 확인
assertTrue(EgovFileUtil.isExistsFile(sb.toString()));
 
// 저장된 Sheet명 일치 점검
assertEquals(sheetName1, tmp.getSheetName(0));
assertEquals(sheetName2, tmp.getSheetName(1));

엑셀 파일 내 셀의 내용을 변경하고 저장한다.
저장된 엑셀파일을 로드할 수 있으며 지정한 sheet에 row와 cell 객체를 생성하여 텍스트를 저장하고 수정할 수 있다.

// 엑셀 파일 로드
HSSFWorkbook wb = excelService.loadWorkbook(filename);
 
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = sheet.createRow( rownum );
row.setHeight( (short) 0x349 );
HSSFCell cell = row.createCell( cellnum );
cell.setCellType( HSSFCell.CELL_TYPE_STRING );
cell.setCellValue( new HSSFRichTextString(content) );
cell.setCellStyle( cs );
 
sheet.setColumnWidth( (short) 20, (short) ( ( 50 * 8 ) / ( (double) 1 / 20 ) ) );
 
FileOutputStream out = new FileOutputStream(filename);
wb.write(out);
out.close();

엑셀 파일의 속성(셀의 크기, Border의 속성, 셀의 색상, 정렬 등)을 수정한다.
HSSFFont, HSSFCellStyle 클래스를 이용하여 셀에 적용된 폰트, 색상 및 정렬 등을 설정할 수 있다.

HSSFWorkbook wb = new HSSFWorkbook();
 
HSSFSheet sheet1 = wb.createSheet("new sheet");
HSSFSheet sheet2 = wb.createSheet("second sheet");
 
// 셀의 크기
sheet1.setDefaultRowHeight(rowheight);
sheet1.setDefaultColumnWidth(columnwidth);
 
HSSFFont f2 = wb.createFont();
HSSFCellStyle cs = wb.createCellStyle();
cs = wb.createCellStyle();
 
cs.setFont( f2 );
cs.setWrapText( true );
 
// 정렬
cs.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
cs.setFillPattern(HSSFCellStyle.DIAMONDS); // 무늬 스타일
 
// 셀의 색상
cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); // 무늬 색
cs.setFillBackgroundColor(new HSSFColor.RED().getIndex());  // 배경색
 
sheet1.setDefaultColumnStyle((short) 0, cs);

엑셀 파일 문서의 속성(Header, Footer)을 수정한다.
HSSFHeader 및 HSSFFooter 클래스로 엑셀문서의 Header와 Footer의 값과 속성을 설정할 수 있다.

// 엑셀 파일 로드
HSSFWorkbook wb = excelService.loadWorkbook(filename);
HSSFSheet sheet = wb.createSheet("doc test sheet");
 
HSSFRow row = sheet.createRow(1);
HSSFCell cell = row.createCell((short) 1);
cell.setCellValue(new HSSFRichTextString("Header/Footer Test"));
 
// Header
HSSFHeader header = sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") + HSSFHeader.fontSize((short) 16) + "Right Stencil-Normal Italic font and size 16");
 
// Footer
HSSFFooter footer = sheet.getFooter();
footer.setCenter(HSSFHeader.font("Fixedsys", "Normal") + HSSFHeader.fontSize((short) 12) + "- 1 -");
footer.setLeft("Left Footer");
footer.setRight("Right Footer");
 
// 엑셀 파일 저장
FileOutputStream out = new FileOutputStream(sb.toString());
wb.write(out);
out.close();

엑셀 파일을 읽어 특정 셀의 값을 얻어온다.
HSSFCell 클래스의 getRichStringCellValue, getNumericCellValue, getStringCellValue 등 다양한 type의 Cell 내용을 추출할 수 있다.

HSSFWorkbook wbT = excelService.loadWorkbook(filename);
HSSFSheet sheetT = wbT.getSheet("cell test sheet");
 
for (int i = 0; i < 100; i++) {
	HSSFRow row1 = sheetT.getRow(i);
 
	for (int j = 0; j < 5; j++) {
		HSSFCell cell1 = row1.getCell((short) j);
		assertEquals("row " + i + ", cell " + j, cell1.getRichStringCellValue().toString());
	}
}

특정 셀의 속성(폰트, 사이즈 등)을 수정한다.
HSSFFont, HSSFCellStyle 등의 클래스를 이용하여 셀의 폰트, 사이즈 등의 셀 속성을 수정할 수 있다.

// 엑셀 파일 로드
HSSFWorkbook wb = excelService.loadWorkbook(sb.toString());
 
HSSFSheet sheet = wb.createSheet("cell test sheet2");
sheet.setColumnWidth((short) 3, (short) 200);	// column Width
 
HSSFCellStyle cs = wb.createCellStyle();
HSSFFont font = wb.createFont();
font.setFontHeight((short) 16);
font.setBoldweight((short) 3);
font.setFontName("fixedsys");
 
cs.setFont(font);
cs.setAlignment(HSSFCellStyle.ALIGN_RIGHT);	// cell 정렬
cs.setWrapText( true );
 
for (int i = 0; i < 100; i++) {
	HSSFRow row = sheet.createRow(i);
	row.setHeight((short)300); // row의 height 설정
 
	for (int j = 0; j < 5; j++) {
		HSSFCell cell = row.createCell((short) j);
		cell.setCellValue(new HSSFRichTextString("row " + i + ", cell " + j));
		cell.setCellStyle( cs );
	}
}
 
// 엑셀 파일 저장
FileOutputStream out = new FileOutputStream(sb.toString());
wb.write(out);
out.close();

공통 템플릿을 사용하여 일관성을 유지한다. jXLS 오픈소스를 사용하여 작성된 템플릿에 지정된 값을 저장한다.

List<PersonHourVO> persons = new ArrayList<PersonHourVO>();
PersonHourVO person = new PersonHourVO();
person.setName("Yegor Kozlov");
person.setId("YK");
person.setMon(5.0);
person.setTue(8.0);
person.setWed(10.0);
person.setThu(5.0);
person.setFri(5.0);
person.setSat(7.0);
person.setSun(6.0);
 
persons.add(person); 
 
PersonHourVO person1 = new PersonHourVO();
person1.setName("Gisella Bronzetti");
person1.setId("GB");
person1.setMon(4.0);
person1.setTue(3.0);
person1.setWed(1.0);
person1.setThu(3.5);
person1.setSun(4.0);
 
persons.add(person1); 
 
Map<String, Object> beans = new HashMap<String, Object>();
beans.put("persons", persons);
XLSTransformer transformer = new XLSTransformer();
 
transformer.transformXLS(filename, beans, sbResult.toString());

<jx:forEach var="persons" items="${persons}">
${persons.name}	${persons.id}	${persons.mon}	${persons.tue}	${persons.wed}	${persons.thu}	${persons.fri}	${persons.sat}	${persons.sun}	$[C4+D4+E4+F4+G4+H4+I4]
</jx:forEach>
		Total Hrs:	$[SUM(C4)]	$[SUM(D4)]	$[SUM(E4)]	$[SUM(F4)]	$[SUM(G4)]	$[SUM(H4)]	$[SUM(I4)]	$[SUM(J4)]

<bean id="categoryExcelView" class="egovframework.rte.fdl.excel.download.CategoryExcelView" />
 
<!--
XSSF 형태의 다운로드의 경우 다음의 View를 등록하여 사용한다.
<bean id="CategoryPOIExcelView" class="egovframework.rte.fdl.excel.download.CategoryPOIExcelView" />
-->
 
<bean class="org.springframework.web.servlet.view.BeanNameViewResolver">
	<property name="order" value="0" />
</bean>

Controller 클래스 작성 Map 사용

@RequestMapping("/sale/listExcelCategory.do")
public ModelAndView selectCategoryList() throws Exception {
 
	List<Map> lists = new ArrayList<Map>();
 
	Map<String, String> mapCategory = new HashMap<String, String>();
	mapCategory.put("id", "0000000001");
	mapCategory.put("name", "Sample Test");
	mapCategory.put("description", "This is initial test data.");
	mapCategory.put("useyn", "Y");
	mapCategory.put("reguser", "test");
 
	lists.add(mapCategory);
 
	mapCategory.put("id", "0000000002");
	mapCategory.put("name", "test Name");
	mapCategory.put("description", "test Deso1111");
	mapCategory.put("useyn", "Y");
	mapCategory.put("reguser", "test");
 
	lists.add(mapCategory);
 
	Map<String, Object> map = new HashMap<String, Object>();
	map.put("category", lists);
 
	return new ModelAndView("categoryExcelView", "categoryMap", map);
}

VO 사용

@RequestMapping("/sale/listExcelVOCategory.do")
public ModelAndView selectCategoryVOList() throws Exception {
 
	List<UsersVO> lists = new ArrayList<UsersVO>();
 
	UsersVO users = new UsersVO();
 
	//Map<String, String> mapCategory = new HashMap<String, String>();
	users.setId("0000000001");
	users.setName("Sample Test");
	users.setDescription("This is initial test data.");
	users.setUseYn("Y");
	users.setRegUser("test");
 
	lists.add(users);
 
	users.setId("0000000002");
	users.setName("test Name");
	users.setDescription("test Deso1111");
	users.setUseYn("Y");
	users.setRegUser("test");
 
	lists.add(users);
 
	Map<String, Object> map = new HashMap<String, Object>();
	map.put("category", lists);
 
	return new ModelAndView("categoryExcelView", "categoryMap", map);
}

View 클래스 작성

public class CategoryExcelView extends AbstractExcelView {
	@Override
	protected void buildExcelDocument(Map model, HSSFWorkbook wb,
			HttpServletRequest req, HttpServletResponse resp) throws Exception {
		HSSFCell cell = null;
 
		HSSFSheet sheet = wb.createSheet("User List");
		sheet.setDefaultColumnWidth((short) 12);
 
		// put text in first cell
		cell = getCell(sheet, 0, 0);
		setText(cell, "User List");
 
		// set header information
		setText(getCell(sheet, 2, 0), "id");
		setText(getCell(sheet, 2, 1), "name");
		setText(getCell(sheet, 2, 2), "description");
		setText(getCell(sheet, 2, 3), "use_yn");
		setText(getCell(sheet, 2, 4), "reg_user");
 
		Map<String, Object> map= (Map<String, Object>) model.get("categoryMap");
		List<Object> categories = (List<Object>) map.get("category");
 
		boolean isVO = false;
 
		if (categories.size() > 0) {
			Object obj = categories.get(0);
			isVO = obj instanceof UsersVO;
		}
 
		for (int i = 0; i < categories.size(); i++) {
 
			if (isVO) {	// VO
 
				Map<String, String> category = (Map<String, String>) categories.get(i);
 
				cell = getCell(sheet, 3 + i, 0);
				setText(cell, category.get("id"));
 
				cell = getCell(sheet, 3 + i, 1);
				setText(cell, category.get("name"));
 
				cell = getCell(sheet, 3 + i, 2);
				setText(cell, category.get("description"));
 
				cell = getCell(sheet, 3 + i, 3);
				setText(cell, category.get("useyn"));
 
				cell = getCell(sheet, 3 + i, 4);
				setText(cell, category.get("reguser"));
			} else {	// Map
 
				Map<String, String> category = (Map<String, String>) categories.get(i);
 
				cell = getCell(sheet, 3 + i, 0);
				setText(cell, category.get("id"));
 
				cell = getCell(sheet, 3 + i, 1);
				setText(cell, category.get("name"));
 
				cell = getCell(sheet, 3 + i, 2);
				setText(cell, category.get("description"));
 
				cell = getCell(sheet, 3 + i, 3);
				setText(cell, category.get("useyn"));
 
				cell = getCell(sheet, 3 + i, 4);
				setText(cell, category.get("reguser"));
 
			}
		}
	}
}

<bean id="excelService“ class="egovframework.rte.fdl.excel.impl.EgovExcelServiceImpl">
	<property name="propertyPath" value="excelInfo.xml" />
	<property name="mapClass" value="egovframework.rte.cvpl.service.impl.EgovExcelTestMapping" />
	<property name="sqlMapClient" ref="sqlMapClient" />
</bean>
 
<!--
XSSF 형태의 다운로드의 경우 다음의 excelService를 등록하여 사용한다.
<bean id="excelService“ class="egovframework.rte.fdl.excel.impl.EgovPOIExcelServiceImpl">
	<property name="propertyPath" value="excelInfo.xml" />
	<property name="mapClass" value="egovframework.rte.cvpl.service.impl.EgovExcelTestMapping" />
	<property name="sqlMapClient" ref="sqlMapClient" />
</bean>
-->

• class : egovframework.rte.fdl.excel.impl.EgovExcelServiceImpl
• propertyPath : xml형식의 엑셀 형식정보 위치
• mapClass : 개발자가 작성한 VO와 Query의 mapping을 위한 클래스
• sqlMapClient : ibatis의 sqlMapClient

VO 클래스 작성

public class EmpVO implements Serializable {
 
	private BigDecimal empNo;
	private String empName;
	private String job;
 
	public BigDecimal getEmpNo() {
		return empNo;
	}
 
  public void setEmpNo(BigDecimal empNo) {
  	this.empNo = empNo;
  }
 
	public String getEmpName() {
		return empName;
	}
 
	public void setEmpName(String empName) {
		this.empName = empName;
	}
 
	public String getJob() {
		return job;
	}
 
	public void setJob(String job) {
		this.job = job;
	}
}

• 엑셀과 Query의 mapping을 위한 VO클래스

Mapping 클래스 작성

public class EgovExcelTestMapping extends EgovExcelMapping {
 
	@Override
	public EmpVO mappingColumn(HSSFRow row) {
		HSSFCell cell0 = row.getCell((short) 0);
		HSSFCell cell1 = row.getCell((short) 1);
		HSSFCell cell2 = row.getCell((short) 2);
 
		EmpVO vo = new EmpVO();
 
		vo.setEmpNo(new BigDecimal(cell0.getNumericCellValue()));
		vo.setEmpName(cell1.getRichStringCellValue().toString());
		vo.setJob(cell2.getRichStringCellValue().toString());
 
		return vo;
	}
}

• 엑셀과 VO의 mapping을 위한 mapping클래스
• EgovExcelMapping 클래스를 상속받아서 mappingColumn 메소드를 오버라이드하여 구현
• HSSFCell 클래스에서 엑셀 값을 추출하여 Query를 실행시키기 위한 VO와 mapping

<sqlMap namespace="EmpBatchInsert">
	<typeAlias alias="empVO" type="egovframework.rte.fdl.excel.vo.EmpVO" />
	<insert id="insertEmpUsingBatch" parameterClass="empVO">
		<![CDATA[
			insert into EMP (
				EMP_NO,
				EMP_NAME,
				JOB
			) values (
				#empNo#,
				#empName#,
				#job#
			)
		]]>
	</insert>
</sqlMap>