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 static org.junit.Assert.assertEquals; import static org.junit.Assert.assertNotSame; import static org.junit.Assert.assertTrue; 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 egovframework.rte.fdl.excel.util.EgovExcelUtil; import egovframework.rte.fdl.excel.vo.PersonHourVO; import egovframework.rte.fdl.filehandling.EgovFileUtil; import javax.annotation.Resource; import net.sf.jxls.transformer.XLSTransformer; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.Footer; import org.apache.poi.ss.usermodel.Header; import org.apache.poi.ss.usermodel.IndexedColors; 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.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFOddFooter; import org.apache.poi.xssf.usermodel.XSSFOddHeader; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.junit.Before; import org.junit.Test; import org.junit.runner.RunWith; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.AbstractJUnit4SpringContextTests; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; /** * FileServiceTest is TestCase of File Handling Service * @author Seongjong Yoon */ @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration(locations = { "classpath:/META-INF/spring/context-*.xml" }) public class EgovExcelXSSFServiceTest extends AbstractJUnit4SpringContextTests { @Resource(name = "excelService") private EgovExcelService excelService; private String fileLocation; private static final Logger LOGGER = LoggerFactory.getLogger(EgovExcelXSSFServiceTest.class); @Before public void onSetUp() throws Exception { this.fileLocation = "testdata"; } /** * [Flow #-1] ? ? : ?? ? */ @Test public void testWriteExcelFile() throws Exception { try { LOGGER.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())); LOGGER.debug("Delete file....{}", sb.toString()); } Workbook wb = new XSSFWorkbook(); wb.createSheet(sheetName1); wb.createSheet(sheetName2); wb.createSheet(); // ? ? Workbook tmp = excelService.createWorkbook(wb, sb.toString()); // ? ? assertTrue(EgovFileUtil.isExistsFile(sb.toString())); // ? Sheet ? ? assertEquals(sheetName1, tmp.getSheetName(0)); assertEquals(sheetName2, tmp.getSheetName(1)); } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testWriteExcelFile end...."); } } /** * [Flow #-2] ? : ? ? ? ? ? */ @Test public void testModifyCellContents() throws Exception { try { String content = "Use \n with word wrap on to create a new line"; short rownum = 2; int cellnum = 2; LOGGER.debug("testModifyCellContents start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyCellContents.xlsx"); if (!EgovFileUtil.isExistsFile(sb.toString())) { Workbook wbT = new XSSFWorkbook(); wbT.createSheet(); // ? ? excelService.createWorkbook(wbT, sb.toString()); } // ? XSSFWorkbook wb = null; wb = excelService.loadWorkbook(sb.toString(), wb); LOGGER.debug("testModifyCellContents after loadWorkbook...."); Sheet sheet = wb.getSheetAt(0); Font f2 = wb.createFont(); CellStyle cs = wb.createCellStyle(); cs = wb.createCellStyle(); cs.setFont(f2); //Word Wrap MUST be turned on cs.setWrapText(true); Row row = sheet.createRow(rownum); row.setHeight((short) 0x349); Cell cellx = row.createCell(cellnum); cellx.setCellType(XSSFCell.CELL_TYPE_STRING); cellx.setCellValue(new XSSFRichTextString(content)); cellx.setCellStyle(cs); sheet.setColumnWidth(20, (int) ((50 * 8) / ((double) 1 / 20))); //excelService.writeWorkbook(wb); FileOutputStream outx = new FileOutputStream(sb.toString()); wb.write(outx); outx.close(); // ? Workbook wb1 = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); Sheet sheet1 = wb1.getSheetAt(0); Row row1 = sheet1.getRow(rownum); Cell cell1 = row1.getCell(cellnum); // ? ? ? LOGGER.debug("cell ###{}###", cell1.getRichStringCellValue()); LOGGER.debug("cont ###{}###", content); assertNotSame("TEST", cell1.getRichStringCellValue().toString()); assertEquals(content, cell1.getRichStringCellValue().toString()); } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testModifyCellContents end...."); } } /** * [Flow #-3] ? ? : ?? ?(? ?, Border? ?, ? ?, )? */ @Test public void testWriteExcelFileAttribute() throws Exception { try { LOGGER.debug("testWriteExcelFileAttribute start...."); short rowheight = 40 * 10; 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())); LOGGER.debug("Delete file....{}", sb.toString()); } XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet1 = wb.createSheet("new sheet"); wb.createSheet("second sheet"); // ? ? sheet1.setDefaultRowHeight(rowheight); sheet1.setDefaultColumnWidth(columnwidth); Font f2 = wb.createFont(); XSSFCellStyle cs = wb.createCellStyle(); cs.setFont(f2); cs.setWrapText(true); // cs.setAlignment(CellStyle.ALIGN_RIGHT); cs.setFillPattern(CellStyle.DIAMONDS); // ? XSSFRow r1 = sheet1.createRow(0); r1.createCell(0); // ? ? cs.setFillForegroundColor(IndexedColors.BLUE.getIndex()); // cs.setFillBackgroundColor(IndexedColors.RED.getIndex()); // sheet1.setDefaultColumnStyle((short) 0, cs); Workbook tmp = excelService.createWorkbook(wb, sb.toString()); Sheet sheetTmp1 = tmp.getSheetAt(0); assertEquals(rowheight, sheetTmp1.getDefaultRowHeight()); assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth()); CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1)); LOGGER.debug("getAlignment : {}", cs1.getAlignment()); assertEquals(XSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment()); LOGGER.debug("getFillPattern : {}", cs1.getFillPattern()); assertEquals(XSSFCellStyle.DIAMONDS, cs1.getFillPattern()); LOGGER.debug("getFillForegroundColor : {}", cs1.getFillForegroundColor()); LOGGER.debug("getFillBackgroundColor : {}", cs1.getFillBackgroundColor()); LOGGER.debug( "XSSFWorkbook.getFillBackgroundColor(), XSSFColor().getIndexed() ? ? 0 ? ?"); assertEquals(IndexedColors.BLUE.getIndex(), cs1.getFillForegroundColor()); assertEquals(IndexedColors.RED.getIndex(), cs1.getFillBackgroundColor()); } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testWriteExcelFileAttribute end...."); } } /** * [Flow #-4] ? : ? ? ?(Header, Footer)? */ @Test public void testModifyDocAttribute() throws Exception { try { LOGGER.debug("testModifyDocAttribute start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyDocAttribute.xlsx"); if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } Workbook wbTmp = new XSSFWorkbook(); wbTmp.createSheet(); // ? ? excelService.createWorkbook(wbTmp, sb.toString()); // ? Workbook wb = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); 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 XSSFRichTextString("Header/Footer Test")); // Header Header header = sheet.getHeader(); header.setCenter("Center Header"); header.setLeft("Left Header"); header.setRight(XSSFOddHeader.stripFields("&IRight Stencil-Normal Italic font and size 16")); // Footer Footer footer = (XSSFOddFooter) sheet.getFooter(); footer.setCenter(XSSFOddHeader.stripFields("Fixedsys")); LOGGER.debug("Style is ... {}", XSSFOddHeader.stripFields("Fixedsys")); footer.setLeft("Left Footer"); footer.setRight("Right Footer"); // ? FileOutputStream out = new FileOutputStream(sb.toString()); wb.write(out); out.close(); assertTrue(EgovFileUtil.isExistsFile(sb.toString())); ////////////////////////////////////////////////////////////////////////// // ? Workbook wbT = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); Sheet sheetT = wbT.getSheet("doc test sheet"); Header headerT = sheetT.getHeader(); assertEquals("Center Header", headerT.getCenter()); assertEquals("Left Header", headerT.getLeft()); assertEquals(XSSFOddHeader.stripFields("Right Stencil-Normal Italic font and size 16"), headerT.getRight()); Footer footerT = sheetT.getFooter(); assertEquals("Right Footer", footerT.getRight()); assertEquals("Left Footer", footerT.getLeft()); assertEquals(XSSFOddHeader.stripFields("Fixedsys"), footerT.getCenter()); } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testModifyDocAttribute end...."); } } /** * [Flow #-5] : ?? ? ? ? */ @Test public void testGetCellContents() throws Exception { try { LOGGER.debug("testGetCellContents start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testGetCellContents.xlsx"); if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } Workbook wbTmp = new XSSFWorkbook(); wbTmp.createSheet(); // ? ? excelService.createWorkbook(wbTmp, sb.toString()); // ? Workbook wb = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); LOGGER.debug("testGetCellContents after loadWorkbook...."); Sheet sheet = wb.createSheet("cell test sheet"); CellStyle cs = wb.createCellStyle(); cs = wb.createCellStyle(); cs.setWrapText(true); 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 XSSFRichTextString("row " + i + ", cell " + j)); cell.setCellStyle(cs); } } // ? FileOutputStream out = new FileOutputStream(sb.toString()); wb.write(out); out.close(); ////////////////////////////////////////////////////////////////////////// // ? Workbook wbT = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); Sheet sheetT = wbT.getSheet("cell test sheet"); for (int i = 0; i < 100; i++) { Row row1 = sheetT.getRow(i); for (int j = 0; j < 5; j++) { Cell cell1 = row1.getCell(j); LOGGER.debug("row {}, cell : {}", i, j, cell1.getRichStringCellValue()); assertEquals("row " + i + ", cell " + j, cell1.getRichStringCellValue().toString()); } } } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testGetCellContents end...."); } } /** * [Flow #-6] ? : ? ?(?, ? )? */ @Test public void testModifyCellAttribute() throws Exception { try { LOGGER.debug("testModifyCellAttribute start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyCellAttribute.xlsx"); if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } Workbook wbTmp = new XSSFWorkbook(); wbTmp.createSheet(); // ? ? excelService.createWorkbook(wbTmp, sb.toString()); // ? XSSFWorkbook wb = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); LOGGER.debug("testModifyCellAttribute after loadWorkbook...."); Sheet sheet = wb.createSheet("cell test sheet2"); sheet.setColumnWidth((short) 3, (short) 200); // column Width CellStyle cs = wb.createCellStyle(); XSSFFont font = wb.createFont(); font.setFontHeight(16); font.setBoldweight((short) 3); font.setFontName("fixedsys"); cs.setFont(font); cs.setAlignment(XSSFCellStyle.ALIGN_RIGHT); // cell cs.setWrapText(true); for (int i = 0; i < 100; i++) { Row row = sheet.createRow(i); row.setHeight((short) 300); // row? height for (int j = 0; j < 5; j++) { Cell cell = row.createCell(j); cell.setCellValue(new XSSFRichTextString("row " + i + ", cell " + j)); cell.setCellStyle(cs); } } // ? FileOutputStream out = new FileOutputStream(sb.toString()); wb.write(out); out.close(); ////////////////////////////////////////////////////////////////////////// // ? XSSFWorkbook wbT = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); Sheet sheetT = wbT.getSheet("cell test sheet2"); LOGGER.debug("getNumCellStyles : {}", wbT.getNumCellStyles()); XSSFCellStyle cs1 = (XSSFCellStyle) wbT.getCellStyleAt((short) (wbT.getNumCellStyles() - 1)); XSSFFont fontT = cs1.getFont(); LOGGER.debug("font getFontHeight : {}", fontT.getFontHeight()); LOGGER.debug("font getBoldweight : {}", fontT.getBoldweight()); LOGGER.debug("font getFontName : {}", fontT.getFontName()); LOGGER.debug("getAlignment : {}", cs1.getAlignment()); LOGGER.debug("getWrapText : {}", cs1.getWrapText()); for (int i = 0; i < 100; i++) { Row row1 = sheetT.getRow(i); for (int j = 0; j < 5; j++) { Cell cell1 = row1.getCell(j); LOGGER.debug("row {}, cell {} : {}", i, j, cell1.getRichStringCellValue()); assertEquals(320, fontT.getFontHeight()); assertEquals(400, fontT.getBoldweight()); LOGGER.debug("fontT.getBoldweight()? ? 400? ?"); assertEquals(XSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment()); assertTrue(cs1.getWrapText()); } } } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testModifyCellAttribute end...."); } } /** * [Flow #-7] : ? ?? */ @Test public void testUseTemplate1() throws Exception { StringBuffer sb = new StringBuffer(); StringBuffer sbResult = new StringBuffer(); sb.append(fileLocation).append("/template/").append("template.xlsx"); sbResult.append(fileLocation).append("/").append("testUseTemplate1.xlsx"); Object[][] sample_data = { { "Yegor Kozlov", "YK", 5.0, 8.0, 10.0, 5.0, 5.0, 7.0, 6.0 }, { "Gisella Bronzetti", "GB", 4.0, 3.0, 1.0, 3.5, null, null, 4.0 }, }; try { XSSFWorkbook wb = null; wb = excelService.loadExcelTemplate(sb.toString(), wb); Sheet sheet = wb.getSheetAt(0); // set data for (int i = 0; i < sample_data.length; i++) { Row row = sheet.getRow(2 + i); for (int j = 0; j < sample_data[i].length; j++) { if (sample_data[i][j] == null) continue; Cell cell = row.getCell(j); if (sample_data[i][j] instanceof String) { cell.setCellValue(new XSSFRichTextString((String) sample_data[i][j])); } else { cell.setCellValue((Double) sample_data[i][j]); } } } // ? sheet.setForceFormulaRecalculation(true); excelService.createWorkbook(wb, sbResult.toString()); ////////////////////////////////////////////////////////////////////////// // ? Workbook wbT = excelService.loadWorkbook(sbResult.toString(), new XSSFWorkbook()); Sheet sheetT = wbT.getSheetAt(0); for (int i = 0; i < sample_data.length; i++) { Row row = sheetT.getRow(2 + i); for (int j = 0; j < sample_data[i].length; j++) { Cell cell = row.getCell(j); LOGGER.debug("sample_data[i][j] : {}", sample_data[i][j]); if (sample_data[i][j] == null) { assertEquals(cell.getCellType(), XSSFCell.CELL_TYPE_BLANK); } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { assertEquals((Double) sample_data[i][j], Double.valueOf(cell.getNumericCellValue())); } else { assertEquals((String) sample_data[i][j], cell.getRichStringCellValue().getString()); } } } } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testUseTemplate end...."); } } /** * [Flow #-7-1] : ? ?? * jXLS */ @Test public void testUseTemplate2() throws Exception { StringBuffer sb = new StringBuffer(); StringBuffer sbResult = new StringBuffer(); sb.append(fileLocation).append("/template/").append("template2.xlsx"); sbResult.append(fileLocation).append("/").append("testUseTemplate2.xlsx"); try { // ? . 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(sb.toString(), beans, sbResult.toString()); ////////////////////////////////////////////////////////////////////////// // ? Workbook wb = excelService.loadWorkbook(sbResult.toString(), new XSSFWorkbook()); Sheet sheet = wb.getSheetAt(0); Double[][] value = { { 5.0, 8.0, 10.0, 5.0, 5.0, 7.0, 6.0 }, { 4.0, 3.0, 1.0, 3.5, null, null, 4.0 } }; for (int i = 0; i < 2; i++) { Row row2 = sheet.getRow(i + 2); for (int j = 0; j < 7; j++) { Cell cellValue = row2.getCell((j + 2)); if (cellValue.getCellType() == Cell.CELL_TYPE_BLANK) continue; LOGGER.debug("cellTot.getNumericCellValue() : {}", cellValue.getNumericCellValue()); assertEquals(value[i][j], Double.valueOf(cellValue.getNumericCellValue())); } } } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testUseTemplate end...."); } } /** * Data type * ? ? Null ? ? */ @Test public void testCellDataFormat() throws Exception { StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testDataFormat.xlsx"); Workbook wb = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); Sheet sheet = wb.getSheetAt(0); Row row = sheet.getRow(7); Cell 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)); } }