Excel 파일 포맷을 다룰 수 있는 자바 라이브러리를 제공하여, 사용자들이 데이터를 Excel 파일 포맷으로 다운받거나, 대량의 Excel 데이터를 시스템에 올릴 수 있도록 지원하기 위한 서비스이다.
Excel 서비스는 Apache POI 오픈소스를 사용하여 구현하였으며 주요 Excel접근 기능 외에 Excel 다운로드, Excel 파일 업로드 등의 기능이 있다.
Excel 서비스 3.0버전에서는 기존 버전을 refactoring 하였다.
기존의 메소드(xls, xlsx)을 지원하는 메소드들의 이름을 하나로 하여 Parameter 방식으로 구분자를 추가하였다.
또한, 기존에 iBatis 뿐만 아니라 MyBatis도 지원하는 클래스를 추가하였다.
엑셀 파일을 생성하여 지정된 위치에 저장하는 기능을 제공한다.
Workbook 인스턴스를 생성하여 Excel sheet를 추가 생성할 수 있다.
엑셀 버전에 따라 엑셀 97~2003버전(xls)인 HSSFWorkbook, 엑셀 2007이상(xlsx)의 XSSFWorkbook 클래스를 사용할 수 있으며, 각 클래스별 사용 법(method)는 동일하다.
String sheetName1 = "first sheet"; String sheetName2 = "second sheet"; StringBuffer sb = new StringBuffer(); // 엑셀 필요버전에 맞는 확장자를 선택하면 됨 sb.append(fileLocation).append("/").append("testWriteExcelFile.xls"); sb.append(fileLocation).append("/").append("testWriteExcelFile.xlsx"); // Workbook을 필요버전에 맞는 클래스를 선택하면 됨 Workbook wb = new HSSFWorkbook(); // xls 버전 Workbook wb = new SXSSFWorkbook(); //xlsx 버전 wb.createSheet(sheetName1); wb.createSheet(sheetName2); wb.createSheet();
엑셀 파일 내 셀의 내용을 변경하고 저장한다.
저장된 엑셀파일을 로드할 수 있으며 지정한 sheet에 row와 cell 객체를 생성하여 텍스트를 저장하고 수정할 수 있다.
// xls엑셀 파일 로드 Workbook wb = excelService.loadWorkbook(filename); // xlsx엑셀 파일 로드 XSSFWorkbook wb = null; wb = excelService.loadWorkbook(sb.toString(), wb); log.debug("testModifyCellContents after loadWorkbook...."); Sheet sheet = wb.getSheetAt(0); Font f2 = wb.createFont(); CellStyle cs = wb.createCellStyle(); cs = wb.createCellStyle(); cs.setFont(f2); cs.setWrapText(true); Row row = sheet.createRow(rownum); row.setHeight((short) 0x349); Cell cell = row.createCell(cellnum); // xls 엑셀방식일 경우 cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(content)); // xlsx 엑셀방식일 경우 cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(new XSSFRichTextString(content)); cell.setCellStyle(cs); sheet.setColumnWidth(20, (int) ((50 * 8) / ((double) 1 / 20))); FileOutputStream out = new FileOutputStream(filename); wb.write(out); out.close();
엑셀 파일 문서의 속성(Header, Footer)을 수정한다.
Header 및 Footer 클래스로 엑셀문서의 Header와 Footer의 값과 속성을 설정할 수 있다.
// 엑셀 파일 로드 Workbook wb = excelService.loadWorkbook(filename); // xls 버전 Workbook wb = excelService.loadWorkbook(filename, new XSSFWorkbook()); // xlsx 버전 LOGGER.debug("testModifyCellContents after loadWorkbook...."); Sheet sheet = wb.createSheet("doc test sheet"); Row row = sheet.createRow(1); Cell cell = row.createCell(1); cell.setCellValue(new HSSFRichTextString("Header/Footer Test")); // xls 버전 cell.setCellValue(new XSSFRichTextString("Header/Footer Test")); // xlsx 버전 // Header Header 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"); // xls 버전 header.setRight(XSSFOddHeader.stripFields("&IRight Stencil-Normal Italic font and size 16")); // xlsx 버전 // Footer // xls 버전 Footer footer = sheet.getFooter(); footer.setCenter(HSSFHeader.font("Fixedsys", "Normal") + HSSFHeader.fontSize((short) 12) + "- 1 -"); // xlsx 버전 Footer footer = (XSSFOddFooter) sheet.getFooter(); footer.setCenter(XSSFOddHeader.stripFields("Fixedsys")); footer.setLeft("Left Footer"); footer.setRight("Right Footer"); // 엑셀 파일 저장 FileOutputStream out = new FileOutputStream(filename); wb.write(out); out.close();
엑셀 파일을 읽어 특정 셀의 값을 얻어온다.
HSSFCell 클래스의 getRichStringCellValue, getNumericCellValue, getStringCellValue 등 다양한 type의 Cell 내용을 추출할 수 있다.
Workbook wbT = excelService.loadWorkbook(filename); // xls 버전 Workbook wbT = excelService.loadWorkbook(filename, new XSSFWorkbook()); // xlsx 버전 Sheet sheetT = wbT.getSheet("cell test sheet"); for (int i = 0; i < 100; i++) { Row row = sheet.createRow(i); for (int j = 0; j < 5; j++) { Cell cell = row.createCell(j); cell.setCellValue(new HSSFRichTextString("row " + i + ", cell " + j)); // xls 버전 cell.setCellValue(new XSSFRichTextString("row " + i + ", cell " + j)); // xlsx 버전 cell.setCellStyle(cs); } }
특정 셀의 속성(폰트, 사이즈 등)을 수정한다.
HSSFFont, HSSFCellStyle 등의 클래스를 이용하여 셀의 폰트, 사이즈 등의 셀 속성을 수정할 수 있다.
// 엑셀 파일 로드 Workbook wb = excelService.loadWorkbook(filename); // xls 버전 Workbook wb = excelService.loadWorkbook(filename, new XSSFWorkbook()); //xlsx 버전 Sheet sheet = wb.createSheet("cell test sheet2"); sheet.setColumnWidth((short) 3, (short) 200); // column Width CellStyle cs = wb.createCellStyle(); Font font = wb.createFont(); font.setFontHeight((short) 16); font.setBoldweight((short) 3); font.setFontName("fixedsys"); cs.setFont(font); cs.setAlignment(CellStyle.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)); // xls 버전 cell.setCellValue(new XSSFRichTextString("row " + i + ", cell " + j)); // xlsx 버전 cell.setCellStyle( cs ); } } // 엑셀 파일 저장 FileOutputStream out = new FileOutputStream(filename); 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 클래스 작성(xls 버전)
public class CategoryExcelView extends AbstractExcelView { private static final Logger LOGGER = LoggerFactory.getLogger(CategoryExcelView.class); @Override protected void buildExcelDocument(Map model, HSSFWorkbook wb, HttpServletRequest req, HttpServletResponse resp) throws Exception { HSSFCell cell = null; LOGGER.debug("### buildExcelDocument start !!!"); HSSFSheet sheet = wb.createSheet("User List"); sheet.setDefaultColumnWidth(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"); LOGGER.debug("### buildExcelDocument cast"); 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 LOGGER.debug("### buildExcelDocument VO : {} started!!", i); UsersVO category = (UsersVO) categories.get(i); cell = getCell(sheet, 3 + i, 0); setText(cell, category.getId()); cell = getCell(sheet, 3 + i, 1); setText(cell, category.getName()); cell = getCell(sheet, 3 + i, 2); setText(cell, category.getDescription()); cell = getCell(sheet, 3 + i, 3); setText(cell, category.getUseYn()); cell = getCell(sheet, 3 + i, 4); setText(cell, category.getRegUser()); LOGGER.debug("### buildExcelDocument VO : {} end!!", i); } else { // Map LOGGER.debug("### buildExcelDocument Map : {} started!!", i); 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")); LOGGER.debug("### buildExcelDocument Map : {} end!!", i); } } } }
View 클래스 작성(xlsx 버전)
public class CategoryPOIExcelView extends AbstractPOIExcelView { private static final Logger LOGGER = LoggerFactory.getLogger(CategoryPOIExcelView.class); @Override protected void buildExcelDocument(Map model, XSSFWorkbook wb, HttpServletRequest req, HttpServletResponse resp) throws Exception { XSSFCell cell = null; LOGGER.debug("### buildExcelDocument start !!!"); XSSFSheet sheet = wb.createSheet("User List"); sheet.setDefaultColumnWidth(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"); LOGGER.debug("### buildExcelDocument cast"); 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 LOGGER.debug("### buildExcelDocument VO : {} started!!", i); UsersVO category = (UsersVO) categories.get(i); cell = getCell(sheet, 3 + i, 0); setText(cell, category.getId()); cell = getCell(sheet, 3 + i, 1); setText(cell, category.getName()); cell = getCell(sheet, 3 + i, 2); setText(cell, category.getDescription()); cell = getCell(sheet, 3 + i, 3); setText(cell, category.getUseYn()); cell = getCell(sheet, 3 + i, 4); setText(cell, category.getRegUser()); LOGGER.debug("### buildExcelDocument VO : {} end!!", i); } else { // Map LOGGER.debug("### buildExcelDocument Map : {} started!!", i); 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")); LOGGER.debug("### buildExcelDocument Map : {} end!!", i); } } } }
<bean id="excelService" class="egovframework.rte.fdl.excel.impl.EgovExcelServiceImpl"> <property name="mapClass" value="egovframework.rte.fdl.excel.upload.EgovExcelTestMapping" /> <property name="sqlSessionTemplate" ref="sqlSessionTemplate" /> </bean> <bean id="excelBigService" class="egovframework.rte.fdl.excel.impl.EgovExcelServiceImpl"> <property name="mapClass" value="egovframework.rte.fdl.excel.upload.EgovExcelTestMapping" /> <property name="mapBeanName" value="mappingBean" /> <property name="sqlMapClient" ref="sqlMapClient" /> </bean> <bean id="mappingBean" class="egovframework.rte.fdl.excel.upload.EgovExcelBigTestMapping" />
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; } }
Mapping 클래스 작성
public class EgovExcelTestMapping extends EgovExcelMapping { private static final Logger LOGGER = LoggerFactory.getLogger(EgovExcelTestMapping.class); @Override public EmpVO mappingColumn(Row row) { Cell cell0 = row.getCell(0); Cell cell1 = row.getCell(1); Cell cell2 = row.getCell(2); EmpVO vo = new EmpVO(); vo.setEmpNo(new BigDecimal(cell0.getNumericCellValue())); vo.setEmpName(EgovExcelUtil.getValue(cell1)); vo.setJob(EgovExcelUtil.getValue(cell2)); LOGGER.debug("########### vo is {}", vo.getEmpNo()); LOGGER.debug("########### vo is {}", vo.getEmpName()); LOGGER.debug("########### vo is {}", vo.getJob()); return vo; } }
<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>