Java tutorial
/* * Copyright 2002-2008 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; import java.io.File; import java.io.FileOutputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.annotation.Resource; import net.sf.jxls.transformer.XLSTransformer; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.apache.poi.hslf.usermodel.RichTextRun; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFFooter; import org.apache.poi.hssf.usermodel.HSSFHeader; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.RichTextString; 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.streaming.SXSSFCell; import org.apache.poi.xssf.streaming.SXSSFRow; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.junit.Before; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.test.AbstractDependencyInjectionSpringContextTests; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; import egovframework.rte.fdl.excel.util.EgovExcelUtil; import egovframework.rte.fdl.excel.vo.PersonHourVO; import egovframework.rte.fdl.filehandling.EgovFileUtil; /** * FileServiceTest is TestCase of File Handling Service * @author Seongjong Yoon */ @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration(locations = { "classpath:/META-INF/spring/context-*.xml" }) public class EgovExcelSXSSFServiceTest extends AbstractDependencyInjectionSpringContextTests { @Resource(name = "excelService") private EgovExcelService excelService; private String fileLocation; private static final Log log = LogFactory.getLog(EgovExcelSXSSFServiceTest.class); @Before public void onSetUp() throws Exception { this.fileLocation = "testdata"; } /** * [Flow #-1] ? ? : ?? ? */ @Test public void testWriteExcelFile() throws Exception { try { log.debug("testWriteExcelFile start...."); String sheetName1 = "first sheet"; String sheetName2 = "second sheet"; StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testWriteExcelFile.xlsx"); // delete file if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); log.debug("Delete file...." + sb.toString()); } SXSSFWorkbook wb = new SXSSFWorkbook(); wb.createSheet(sheetName1); wb.createSheet(sheetName2); wb.createSheet(); // ? ? SXSSFWorkbook tmp = excelService.createSXSSFWorkbook(wb, sb.toString()); // ? ? assertTrue(EgovFileUtil.isExistsFile(sb.toString())); // ? Sheet ? ? assertEquals(sheetName1, tmp.getSheetName(0)); assertEquals(sheetName2, tmp.getSheetName(1)); } catch (Exception e) { log.error(e.toString()); throw new Exception(e); } finally { log.debug("testWriteExcelFile end...."); } } /** * [Flow #-3] ? ? : ?? ?(? ?, Border? ?, ? ?, )? */ @Test public void testWriteExcelFileAttribute() throws Exception { try { log.debug("testWriteExcelFileAttribute start...."); short rowheight = 40; int columnwidth = 30; StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xlsx"); // delete file if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); log.debug("Delete file...." + sb.toString()); } SXSSFWorkbook wb = new SXSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); wb.createSheet("second sheet"); // ? ? sheet1.setDefaultRowHeight(rowheight); sheet1.setDefaultColumnWidth(columnwidth); Font f2 = wb.createFont(); CellStyle 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); Workbook tmp = excelService.createSXSSFWorkbook(wb, sb.toString()); Sheet sheetTmp1 = tmp.getSheetAt(0); assertEquals(rowheight, sheetTmp1.getDefaultRowHeight()); assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth()); CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1)); log.debug("getAlignment : " + cs1.getAlignment()); assertEquals(HSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment()); log.debug("getFillPattern : " + cs1.getFillPattern()); assertEquals(HSSFCellStyle.DIAMONDS, cs1.getFillPattern()); log.debug("getFillForegroundColor : " + cs1.getFillForegroundColor()); log.debug("getFillBackgroundColor : " + cs1.getFillBackgroundColor()); assertEquals(new HSSFColor.BLUE().getIndex(), cs1.getFillForegroundColor()); assertEquals(new HSSFColor.RED().getIndex(), cs1.getFillBackgroundColor()); } catch (Exception e) { log.error(e.toString()); throw new Exception(e); } finally { log.debug("testWriteExcelFileAttribute end...."); } } /** * [Flow #-4] ? : ? ? ?(Header, Footer)? */ @Test public void testModifyDocAttribute() throws Exception { try { log.debug("testModifyDocAttribute start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyDocAttribute.xls"); if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); log.debug("Delete file...." + sb.toString()); } HSSFWorkbook wbTmp = new HSSFWorkbook(); wbTmp.createSheet(); // ? ? excelService.createWorkbook(wbTmp, sb.toString()); // ? HSSFWorkbook wb = excelService.loadWorkbook(sb.toString()); log.debug("testModifyCellContents after loadWorkbook...."); HSSFSheet sheet = wb.createSheet("doc test sheet"); HSSFRow row = sheet.createRow(1); HSSFCell cell = row.createCell(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 -"); log.debug("Style is ... " + 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(); assertTrue(EgovFileUtil.isExistsFile(sb.toString())); ////////////////////////////////////////////////////////////////////////// // ? HSSFWorkbook wbT = excelService.loadWorkbook(sb.toString()); HSSFSheet sheetT = wbT.getSheet("doc test sheet"); HSSFHeader headerT = sheetT.getHeader(); assertEquals("Center Header", headerT.getCenter()); assertEquals("Left Header", headerT.getLeft()); assertEquals(HSSFHeader.font("Stencil-Normal", "Italic") + HSSFHeader.fontSize((short) 16) + "Right Stencil-Normal Italic font and size 16", headerT.getRight()); HSSFFooter footerT = sheetT.getFooter(); assertEquals("Right Footer", footerT.getRight()); assertEquals("Left Footer", footerT.getLeft()); assertEquals(HSSFHeader.font("Fixedsys", "Normal") + HSSFHeader.fontSize((short) 12) + "- 1 -", footerT.getCenter()); } catch (Exception e) { log.error(e.toString()); throw new Exception(e); } finally { log.debug("testModifyDocAttribute end...."); } } /** * [Flow #-5] : ?? ? ? ? */ @Test public void testGetCellContents() throws Exception { try { log.debug("testGetCellContents start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testGetCellContents.xls"); if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); log.debug("Delete file...." + sb.toString()); } HSSFWorkbook wbTmp = new HSSFWorkbook(); wbTmp.createSheet(); // ? ? excelService.createWorkbook(wbTmp, sb.toString()); // ? HSSFWorkbook wb = excelService.loadWorkbook(sb.toString()); log.debug("testGetCellContents after loadWorkbook...."); HSSFSheet sheet = wb.createSheet("cell test sheet"); HSSFCellStyle cs = wb.createCellStyle(); cs = wb.createCellStyle(); cs.setWrapText(true); for (int i = 0; i < 100; i++) { HSSFRow row = sheet.createRow(i); for (int j = 0; j < 5; j++) { HSSFCell cell = row.createCell(j); cell.setCellValue(new HSSFRichTextString("row " + i + ", cell " + j)); cell.setCellStyle(cs); } } // ? FileOutputStream out = new FileOutputStream(sb.toString()); wb.write(out); out.close(); ////////////////////////////////////////////////////////////////////////// // ? HSSFWorkbook wbT = excelService.loadWorkbook(sb.toString()); 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(j); log.debug("row " + i + ", cell " + j + " : " + cell1.getRichStringCellValue()); assertEquals("row " + i + ", cell " + j, cell1.getRichStringCellValue().toString()); } } } catch (Exception e) { log.error(e.toString()); throw new Exception(e); } finally { log.debug("testGetCellContents end...."); } } /** * [Flow #-6] ? : ? ?(?, ? )? */ @Test public void testModifyCellAttribute() throws Exception { try { log.debug("testModifyCellAttribute start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyCellAttribute.xls"); if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); log.debug("Delete file...." + sb.toString()); } HSSFWorkbook wbTmp = new HSSFWorkbook(); wbTmp.createSheet(); // ? ? excelService.createWorkbook(wbTmp, sb.toString()); // ? HSSFWorkbook wb = excelService.loadWorkbook(sb.toString()); log.debug("testModifyCellAttribute after loadWorkbook...."); 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(j); cell.setCellValue(new HSSFRichTextString("row " + i + ", cell " + j)); cell.setCellStyle(cs); } } // ? FileOutputStream out = new FileOutputStream(sb.toString()); wb.write(out); out.close(); ////////////////////////////////////////////////////////////////////////// // ? HSSFWorkbook wbT = excelService.loadWorkbook(sb.toString()); HSSFSheet sheetT = wbT.getSheet("cell test sheet2"); log.debug("getNumCellStyles : " + wbT.getNumCellStyles()); HSSFCellStyle cs1 = wbT.getCellStyleAt((short) (wbT.getNumCellStyles() - 1)); HSSFFont fontT = cs1.getFont(wbT); log.debug("font getFontHeight : " + fontT.getFontHeight()); log.debug("font getBoldweight : " + fontT.getBoldweight()); log.debug("font getFontName : " + fontT.getFontName()); log.debug("getAlignment : " + cs1.getAlignment()); log.debug("getWrapText : " + cs1.getWrapText()); for (int i = 0; i < 100; i++) { HSSFRow row1 = sheetT.getRow(i); for (int j = 0; j < 5; j++) { HSSFCell cell1 = row1.getCell(j); log.debug("row " + i + ", cell " + j + " : " + cell1.getRichStringCellValue()); assertEquals(16, fontT.getFontHeight()); assertEquals(3, fontT.getBoldweight()); assertEquals(HSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment()); assertTrue(cs1.getWrapText()); } } } catch (Exception e) { log.error(e.toString()); throw new Exception(e); } finally { log.debug("testModifyCellAttribute end...."); } } /** * Data type * ? ? Null ? ? */ @Test public void testCellDataFormat() throws Exception { StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testDataFormat.xls"); HSSFWorkbook wb = excelService.loadWorkbook(sb.toString()); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(7); HSSFCell cell = row.getCell(0); assertEquals("2009/04/01", EgovExcelUtil.getValue(cell)); row = sheet.getRow(8); cell = row.getCell(0); assertEquals("2009/04/02", EgovExcelUtil.getValue(cell)); } }