Java tutorial
/* * Copyright 2008-2014 MOPAS(Ministry of Public Administration and Security). * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package egovframework.rte.fdl.excel.impl; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List; import java.util.Locale; import egovframework.rte.fdl.cmmn.exception.BaseException; import egovframework.rte.fdl.excel.EgovExcelMapping; import egovframework.rte.fdl.excel.EgovExcelService; import egovframework.rte.fdl.filehandling.EgovFileUtil; import egovframework.rte.fdl.string.EgovObjectUtil; import org.apache.commons.io.FileUtils; import org.apache.commons.io.FilenameUtils; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.mybatis.spring.SqlSessionTemplate; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.BeansException; import org.springframework.context.ApplicationContext; import org.springframework.context.ApplicationContextAware; import org.springframework.context.MessageSource; import com.ibatis.sqlmap.client.SqlMapClient; /** * ?. * * <p><b>NOTE:</b> ??.</p> * * @author * @since 2009.06.01 * @version 1.0 * @see <pre> * == ?(Modification Information) == * * ? ? * ----------- -------- --------------------------- * 2009.06.01 ? * 2013.05.22 ? XSSF, SXSSF? (xlsx ?) * 2013.05.29 mapBeanName property ? * 2014.05.14 ? refactoring ? mybatis * * </pre> */ public class EgovExcelServiceImpl implements EgovExcelService, ApplicationContextAware { private static final Logger LOGGER = LoggerFactory.getLogger(EgovExcelServiceImpl.class); private MessageSource messageSource; private ApplicationContext applicationContext; private String mapClass; private String mapBeanName; private EgovExcelServiceDAO dao; private EgovExcelServiceMapper excelBatchMapper; private SqlMapClient sqlMapClient; private SqlSessionTemplate sqlSessionTemplate; public void setApplicationContext(ApplicationContext applicationContext) throws BeansException { this.applicationContext = applicationContext; this.messageSource = (MessageSource) applicationContext.getBean("messageSource"); } /** * MessageSource . * * @return the messageSource */ protected MessageSource getMessageSource() { return messageSource; } /** * ibatis ? . * * @param sqlMapClient * @throws Exception */ public void setSqlMapClient(SqlMapClient sqlMapClient) throws Exception { this.sqlMapClient = sqlMapClient; dao = new EgovExcelServiceDAO(this.sqlMapClient); } /** * mybatis ? . * * @param sqlSessionTemplate * @throws Exception */ public void setSqlSessionTemplate(SqlSessionTemplate sqlSessionTemplate) throws Exception { this.sqlSessionTemplate = sqlSessionTemplate; excelBatchMapper = new EgovExcelServiceMapper(this.sqlSessionTemplate); } /** * Excel Cell VO mapping ?. * * @param mapClass * @throws Exception */ public void setMapClass(String mapClass) throws BaseException { this.mapClass = mapClass; LOGGER.debug("mapClass : {}", mapClass); } /** * Excel Cell VO mapping Bean name (mapClass ). * * @param mapBeanName * @throws BaseException */ public void setMapBeanName(String mapBeanName) throws BaseException { this.mapBeanName = mapBeanName; LOGGER.debug("mapBeanName : {}", mapBeanName); } /** * Workbook ? ? ?? ?. * * @param wb * @param filepath * @return * @throws Exception */ public Workbook createWorkbook(Workbook wb, String filepath) throws BaseException, FileNotFoundException, IOException { String fullFileName = filepath; LOGGER.debug("EgovExcelServiceImpl.createWorkbook : templatePath is {}", FilenameUtils.getFullPath(fullFileName)); // ? if (!EgovFileUtil.isExistsFile(FilenameUtils.getFullPath(fullFileName))) { LOGGER.debug("make dir {}", FilenameUtils.getFullPath(fullFileName)); try { FileUtils.forceMkdir(new File(FilenameUtils.getFullPath(fullFileName))); } catch (IOException e) { throw new IOException( "Cannot create directory for path: " + FilenameUtils.getFullPath(fullFileName)); } } FileOutputStream fileOut = new FileOutputStream(fullFileName); LOGGER.debug("EgovExcelServiceImpl.createWorkbook : templatePath is {}", fullFileName); try { LOGGER.debug("ExcelServiceImpl loadExcelObject ..."); wb.write(fileOut); } catch (Exception e) { LOGGER.error(getMessageSource().getMessage("error.excel.runtime.error", new Object[] { "createWorkbook" }, Locale.getDefault()), e); } finally { LOGGER.debug("ExcelServiceImpl loadExcelObject end..."); fileOut.close(); } return wb; } /** * Template ?? ?. * * @param templateName * @return * @throws Exception */ public Workbook loadExcelTemplate(String templateName) throws BaseException, FileNotFoundException, IOException { FileInputStream fileIn = new FileInputStream(templateName); Workbook wb = null; LOGGER.debug("EgovExcelServiceImpl.loadExcelTemplate : templatePath is {}", templateName); try { LOGGER.debug("ExcelServiceImpl loadExcelTemplate ..."); POIFSFileSystem fs = new POIFSFileSystem(fileIn); wb = new HSSFWorkbook(fs); } catch (Exception e) { LOGGER.error(getMessageSource().getMessage("error.excel.runtime.error", new Object[] { "EgovExcelServiceImpl loadExcelTemplate" }, Locale.getDefault()), e); } finally { LOGGER.debug("ExcelServiceImpl loadExcelTemplate end..."); fileIn.close(); } return wb; } /** * xlsx Template ?? ?. * * @param templateName * @param wb * @return * @throws Exception */ public XSSFWorkbook loadExcelTemplate(String templateName, XSSFWorkbook wb) throws BaseException, FileNotFoundException, IOException { FileInputStream fileIn = new FileInputStream(templateName); LOGGER.debug("EgovExcelServiceImpl.loadExcelTemplate(XSSF) : templatePath is {}", templateName); try { LOGGER.debug("ExcelServiceImpl loadExcelTemplate(XSSF) ..."); wb = new XSSFWorkbook(fileIn); } catch (Exception e) { LOGGER.error( getMessageSource().getMessage("error.excel.runtime.error", new Object[] { "EgovExcelServiceImpl loadExcelTemplate(XSSF)" }, Locale.getDefault()), e); } finally { LOGGER.debug("ExcelServiceImpl loadExcelTemplate(XSSF) end..."); fileIn.close(); } return wb; } /** * ?? . * * @param filepath * @return * @throws Exception */ public Workbook loadWorkbook(String filepath) throws BaseException, FileNotFoundException, IOException { FileInputStream fileIn = new FileInputStream(filepath); Workbook wb = loadWorkbook(fileIn); fileIn.close(); return wb; } /** * xlsx ?? . * * @param filepath * @param wb * @return XSSFWorkbook * @throws Exception */ public XSSFWorkbook loadWorkbook(String filepath, XSSFWorkbook wb) throws BaseException, FileNotFoundException, IOException { FileInputStream fileIn = new FileInputStream(filepath); wb = loadWorkbook(fileIn, wb); fileIn.close(); return wb; } /** * ?? . * * @param filepath * @return * @throws Exception */ public Workbook loadWorkbook(InputStream fileIn) throws BaseException { Workbook wb = null; try { LOGGER.debug("ExcelServiceImpl loadWorkbook ..."); POIFSFileSystem fs = new POIFSFileSystem(fileIn); wb = new HSSFWorkbook(fs); } catch (Exception e) { LOGGER.error(getMessageSource().getMessage("error.excel.runtime.error", new Object[] { "loadWorkbook" }, Locale.getDefault()), e); } return wb; } /** * xlsx ?? . * * @param fileIn * @param wb * @return * @throws Exception */ public XSSFWorkbook loadWorkbook(InputStream fileIn, XSSFWorkbook wb) throws BaseException { try { LOGGER.debug("ExcelServiceImpl loadWorkbook(XSSF) ..."); wb = new XSSFWorkbook(fileIn); } catch (Exception e) { LOGGER.error(getMessageSource().getMessage("error.excel.runtime.error", new Object[] { "loadWorkbook(XSSF)" }, Locale.getDefault()), e); } return wb; } /** * ?? DB? ?.<br/> * ? .<br/> * commit .<br/> * sqlSessionTemplate(mybatis), sqlMapClient(ibatis) . * * @param queryId * @param sheet * @param start (default : 0) * @param commitCnt (default :0) * @return * @throws Exception */ public Integer uploadExcel(String queryId, Sheet sheet, int start, long commitCnt) throws BaseException, Exception { LOGGER.debug("sheet.getPhysicalNumberOfRows() : {}", sheet.getPhysicalNumberOfRows()); Integer rowsAffected = 0; try { long rowCnt = sheet.getPhysicalNumberOfRows(); long cnt = (commitCnt == 0) ? rowCnt : commitCnt; LOGGER.debug("Runtime.getRuntime().totalMemory() : {}", Runtime.getRuntime().totalMemory()); LOGGER.debug("Runtime.getRuntime().freeMemory() : {}", Runtime.getRuntime().freeMemory()); long startTime = System.currentTimeMillis(); for (int idx = start, i = start; idx < rowCnt; idx = i) { List<Object> list = new ArrayList<Object>(); LOGGER.debug("before Runtime.getRuntime().freeMemory() : {}", Runtime.getRuntime().freeMemory()); EgovExcelMapping mapping = null; if (mapBeanName != null) { mapping = (EgovExcelMapping) applicationContext.getBean(mapBeanName); } else if (mapClass != null) { mapping = (EgovExcelMapping) EgovObjectUtil.instantiate(mapClass); } else { throw new RuntimeException( getMessageSource().getMessage("error.excel.property.error", null, Locale.getDefault())); } for (i = idx; i < rowCnt && i < (cnt + idx); i++) { Row row = sheet.getRow(i); list.add(mapping.mappingColumn(row)); } // insert // spring ibatis? batch ? ? exception ?? rollback ? ?. // ibatis ? batch sqlMapClient.startTransaction() ?? ? ? ! if (sqlSessionTemplate != null) { rowsAffected += excelBatchMapper.batchInsert(queryId, list); } else if (sqlMapClient != null) { rowsAffected += dao.batchInsert(queryId, list); } else { throw new RuntimeException(getMessageSource().getMessage("error.excel.persistence.error", null, Locale.getDefault())); } LOGGER.debug("after Runtime.getRuntime().freeMemory() : {}", Runtime.getRuntime().freeMemory()); LOGGER.debug("rowsAffected : {}", rowsAffected); } LOGGER.debug("batchInsert time is {}", (System.currentTimeMillis() - startTime)); } catch (Exception e) { throw new Exception(e); } LOGGER.debug("uploadExcel result count is {}", rowsAffected); return rowsAffected; } /** * ?? ? DB upload . * * @param queryId * @param fileIn * @param start(default : 0) * @param commitCnt(default : 0) * @return * @throws Exception */ public Integer uploadExcel(String queryId, InputStream fileIn, int start, long commitCnt) throws BaseException, Exception { Workbook wb = loadWorkbook(fileIn); Sheet sheet = wb.getSheetAt(0); return uploadExcel(queryId, sheet, start, commitCnt); } /** * xlsx ?? ? DB upload . * * @param queryId * @param fileIn * @param start * @param commitCnt * @param wb * @return * @throws Exception */ public Integer uploadExcel(String queryId, InputStream fileIn, int start, long commitCnt, XSSFWorkbook wb) throws BaseException, Exception { wb = loadWorkbook(fileIn, wb); Sheet sheet = wb.getSheetAt(0); return uploadExcel(queryId, sheet, start, commitCnt); } /** * ?? . * * @param queryId * @param fileIn * @return * @throws Exception */ public Integer uploadExcel(String queryId, InputStream fileIn) throws BaseException, Exception { return uploadExcel(queryId, fileIn, 0, 0); } /** * xlsx ?? . * * @param queryId * @param fileIn * @return * @throws Exception */ public Integer uploadExcel(String queryId, InputStream fileIn, XSSFWorkbook type) throws BaseException, Exception { return uploadExcel(queryId, fileIn, 0, 0, type); } /** * ?? DB? ?.<br/> * ? . * * @param queryId * @param fileIn * @param sheetIndex * @param start (default : 0) * @param commitCnt (default : 0) * @return * @throws Exception */ public Integer uploadExcel(String queryId, InputStream fileIn, short sheetIndex, int start, long commitCnt) throws BaseException, Exception { Workbook wb = loadWorkbook(fileIn); Sheet sheet = wb.getSheetAt(sheetIndex); return uploadExcel(queryId, sheet, start, commitCnt); } /** * xlsx ?? DB? ?.<br/> * ? . * * @param queryId * @param fileIn * @param sheetIndex * @param start (default : 0) * @param commitCnt (default : 0) * @param wb * @return * @throws Exception */ public Integer uploadExcel(String queryId, InputStream fileIn, short sheetIndex, int start, long commitCnt, XSSFWorkbook wb) throws BaseException, Exception { wb = loadWorkbook(fileIn, wb); Sheet sheet = wb.getSheetAt(sheetIndex); return uploadExcel(queryId, sheet, start, commitCnt); } /** * ?? DB? ?.<br/> * ? . * * @param queryId * @param fileIn * @param sheetName * @param start (default : 0) * @param commitCnt (default : 0) * @return * @throws Exception */ public Integer uploadExcel(String queryId, InputStream fileIn, String sheetName, int start, long commitCnt) throws BaseException, Exception { Workbook wb = loadWorkbook(fileIn); Sheet sheet = wb.getSheet(sheetName); return uploadExcel(queryId, sheet, start, commitCnt); } /** * xlsx ?? DB? ?.<br/> * ? . * * @param queryId * @param fileIn * @param sheetName * @param start (default : 0) * @param commitCnt (default : 0) * @param wb * @return * @throws Exception */ public Integer uploadExcel(String queryId, InputStream fileIn, String sheetName, int start, long commitCnt, XSSFWorkbook wb) throws BaseException, Exception { wb = loadWorkbook(fileIn, wb); Sheet sheet = wb.getSheet(sheetName); return uploadExcel(queryId, sheet, start, commitCnt); } }