Excel Service

Excel Service

1. 개요

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

2. 설명

주요기능

Excel 파일 생성

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

Sample Source

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

Excel 파일 수정

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

Sample Source

// 엑셀 파일 로드
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();

Excel 파일 속성 수정

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

Sample Source

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

Excel 문서 속성 수정

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

Sample Source

// 엑셀 파일 로드
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 내용을 추출할 수 있다.

Sample Source

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 등의 클래스를 이용하여 셀의 폰트, 사이즈 등의 셀 속성을 수정할 수 있다.

Sample Source

// 엑셀 파일 로드
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 오픈소스를 사용하여 작성된 템플릿에 지정된 값을 저장한다.

Sample Source

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

Excel Template

<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" />
 
<bean class="org.springframework.web.servlet.view.BeanNameViewResolver">
	<property name="order" value="0" />
</bean>

Sample Source

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"));
 
			}
		}
	}
}

Excel 업로드

Configuration

<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>

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

Sample Source

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

Query

<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>

Excel Service

1. 개요

2. 설명

주요기능

Excel 파일 생성

Sample Source

Excel 파일 수정

Sample Source

Excel 파일 속성 수정

Sample Source

Excel 문서 속성 수정

Sample Source

셀 내용 추출

Sample Source

셀 속성 추출

Sample Source

공통 템플릿 사용

Sample Source

Excel Template

Template 적용결과

Excel 다운로드

Configuration

Sample Source

Excel 업로드

Configuration

Sample Source

Query

N. 참고자료