List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getRow
@Override public HSSFRow getRow(int rowIndex)
From source file:edu.tum.cs.conqat.quamoco.ManualFindingsReader.java
License:Apache License
/** {@inheritDoc} */ @Override/* w w w . j av a2 s . c om*/ public MetricCollection process() throws ConQATException { MetricCollection result = new MetricCollection(); HSSFSheet sheet = ExcelUtils.readFirstSheet(file); ExcelUtils.checkName(sheet, 0, "Measure"); ExcelUtils.checkName(sheet, 1, "Location"); ExcelUtils.checkName(sheet, 2, "Message"); FindingGroup group = new FindingReport().getOrCreateCategory(findingCategory) .getOrCreateFindingGroup(findingGroup); for (int i = 1; i < sheet.getLastRowNum() + 1; i++) { HSSFRow row = sheet.getRow(i); String measureName = ExcelUtils.getCellText(row, 0); String location = ExcelUtils.getCellText(row, 1); String message = ExcelUtils.getCellText(row, 2); QualifiedNameLocation qnl = new QualifiedNameLocation(location, location, location); String findingname = getClass().getSimpleName(); // not used anymore after restrusturing to new findings framwork of 21.12.2012 Finding finding = group.createFinding(qnl); finding.setValue(EFindingKeys.MESSAGE.toString(), message); add(measureName, result, finding); } return result; }
From source file:edu.tum.cs.conqat.quamoco.quickeval.MeasureListerTest.java
License:Apache License
/** Basic test */ public void testSimple() throws Exception { MeasureLister ml = new MeasureLister(); File tmp = getTmpDirectory(); tmp.mkdirs();//from ww w . ja v a 2 s . c om ml.file = tmp.getAbsolutePath() + "/out.xls"; QualityModel qm = ModelHarness.createQualityModel("m"); ManualInstrument instr = ModelHarness.createManualInstrument("i"); Measure measure = ModelHarness.createMeasure("m", Type.NUMBER); instr.setDetermines(measure); qm.getMeasures().add(measure); ml.setQualityModels(new QualityModel[] { qm }); ml.process(); HSSFSheet sheet = ExcelUtils.readFirstSheet(new File(ml.file)); HSSFRow titleRow = sheet.getRow(0); assertEquals("Measure", ExcelUtils.getCellContent(titleRow, 0)); }
From source file:edu.tum.cs.conqat.quamoco.quickeval.MeasureReader.java
License:Apache License
/** Read row in sheet and process content */ private void processRow(HSSFSheet sheet, int rowNum) throws ConQATException { HSSFRow row = sheet.getRow(rowNum); if (row == null) { getLogger().warn("row " + rowNum + " does not exist."); return;//from w w w. java2 s. c o m } String measureName = ExcelUtils.getCellContent(row, 0); getLogger().warn("in row " + rowNum + " there is measure " + measureName); if (measureName.isEmpty()) { return; } Type type = Enum.valueOf(Type.class, ExcelUtils.getCellContent(row, 1)); if (TypeUtils.isPrimitive(type)) { String valueString = ExcelUtils.getCellContent(row, 3); storeManualInstrument(measureName, type, valueString); } else { String message = "Cannot read value for measure " + type + " since it is not primitive. Skipping measure!"; getLogger().warn(message); } }
From source file:egovframework.oe1.cms.cmm.web.EgovOe1DicGovTermController.java
License:Apache License
/** * ? /*w ww . j a va 2 s . c o m*/ * @param ComDefaultVO - ? VO * @param model * @return "forward:/cms/cmm/selectDicGovTermList.do" * @exception Exception */ @RequestMapping("/cms/cmm/addDicGovTermExcelOK.do") public String addDicGovTermExcelRegister(final HttpServletRequest request, @ModelAttribute("egovOe1DicGovTermVO") EgovOe1DicGovTermVO egovOe1DicGovTermVO, BindingResult bindingResult, Model model, SessionStatus status) throws Exception { Boolean isAuthenticated = EgovUserDetailsHelper.isAuthenticated(); if (!isAuthenticated) { return "/cms/com/EgovLoginUsr"; //? ?? } if (bindingResult.hasErrors()) { model.addAttribute("egovOe1DicGovTermVO", egovOe1DicGovTermVO); return "/cms/cmm/addDicGovTermExcel"; } model.addAttribute("searchMode", egovOe1DicGovTermVO); // EgovOe1LoginVO user = (EgovOe1LoginVO) EgovUserDetailsHelper.getAuthenticatedUser(); try { final MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request; final Map<String, MultipartFile> files = multiRequest.getFileMap(); Iterator<Entry<String, MultipartFile>> itr = files.entrySet().iterator(); MultipartFile file; String filePath = ""; while (itr.hasNext()) { Entry<String, MultipartFile> entry = itr.next(); file = entry.getValue(); if (!"".equals(file.getOriginalFilename())) { String _storePath = propertyService.getString("Globals.fileStorePath"); File saveFolder = new File(_storePath); if (!saveFolder.exists() || saveFolder.isFile()) { saveFolder.mkdirs(); } String orginFileName = file.getOriginalFilename(); int _index = orginFileName.lastIndexOf("."); String fileName = orginFileName.substring(0, _index); String fileExt = orginFileName.substring(_index + 1); long _size = file.getSize(); if (!"".equals(orginFileName)) { filePath = _storePath + File.separator + "EgovStandardTermStatus.xls"; file.transferTo(new File(filePath)); } //System.out.println("?=====>"+orginFileName); //System.out.println("?=====>"+fileName); //System.out.println("?=====>"+fileExt); //System.out.println("??=====>"+_size); //System.out.println("??=====>"+filePath); if ((fileExt.equals("xls") || fileExt.equals("xlsx")) && (filePath != null && !"".equals(filePath))) { HSSFWorkbook wbT = excelService.loadWorkbook(filePath); HSSFSheet sheetT = wbT.getSheet(wbT.getSheetName(0)); HSSFRow rowValue = sheetT.getRow(1); HSSFCell cellValue1 = rowValue.getCell(1); // HSSFCell cellValue2 = rowValue.getCell(2); // HSSFCell cellValue3 = rowValue.getCell(3); //? HSSFCell cellValue4 = rowValue.getCell(4); //? HSSFCell cellValue5 = rowValue.getCell(5); //? HSSFCell cellValue6 = rowValue.getCell(6); //? HSSFCell cellValue8 = rowValue.getCell(8); //? String AdministWordSe = (cellValue1 + "").trim(); String AdministWordNm = (cellValue2 + "").trim(); String AdministWordEngNm = (cellValue3 + "").trim(); String AdministWordAbrv = (cellValue4 + "").trim(); String AdministWordDc = (cellValue5 + "").trim(); String ThemaRelm = (cellValue6 + "").trim(); String UseAt = (cellValue8 + "").trim(); //System.out.println("AdministWordSe====>"+AdministWordSe); //System.out.println("AdministWordNm====>"+AdministWordNm); //System.out.println("AdministWordEngNm====>"+AdministWordEngNm); //System.out.println("AdministWordAbrv====>"+AdministWordAbrv); //System.out.println("AdministWordDc====>"+AdministWordDc); //System.out.println("ThemaRelm====>"+ThemaRelm); //System.out.println("UseAt====>"+UseAt); if (!"".equals(AdministWordSe) || !"".equals(AdministWordNm) || !"?".equals(AdministWordEngNm) || !"?".equals(AdministWordAbrv) || !"?".equals(AdministWordDc) || !"?".equals(ThemaRelm) || !"?".equals(UseAt)) { model.addAttribute("resultMsg", " EXCEL ?? . ."); return "/cms/cmm/EgovDicGovTermExcelRegist"; } List<EgovOe1DicGovTermVO> dicGovTermListVO = new ArrayList<EgovOe1DicGovTermVO>(); for (int i = 2; i <= sheetT.getLastRowNum(); i++) { HSSFRow row1 = sheetT.getRow(i); HSSFCell cell1 = row1.getCell(1); //(/??) HSSFCell cell2 = row1.getCell(2); // HSSFCell cell3 = row1.getCell(3); //? HSSFCell cell4 = row1.getCell(4); //? HSSFCell cell5 = row1.getCell(5); // HSSFCell cell6 = row1.getCell(6); //? HSSFCell cell8 = row1.getCell(8); // AdministWordSe = (cell1 + "").trim(); AdministWordNm = (cell2 + "").trim(); AdministWordEngNm = (cell3 + "").trim(); AdministWordAbrv = (cell4 + "").trim(); AdministWordDc = (cell5 + "").trim(); ThemaRelm = (cell6 + "").trim(); UseAt = (cell8 + "").trim(); if (("".equals(AdministWordSe) || "??".equals(AdministWordSe)) && (!"".equals(AdministWordSe) && AdministWordSe != null) && (!"".equals(AdministWordNm) && AdministWordNm != null) && (!"".equals(AdministWordEngNm) && AdministWordEngNm != null) && (!"".equals(AdministWordAbrv) && AdministWordAbrv != null) && (!"".equals(AdministWordDc) && AdministWordDc != null)) { EgovOe1DicGovTermVO egovOe1DicGovTermExcelVO = new EgovOe1DicGovTermVO(); if ("".equals(AdministWordSe)) { egovOe1DicGovTermExcelVO.setAdministWordSe("001"); } else { egovOe1DicGovTermExcelVO.setAdministWordSe("002"); } egovOe1DicGovTermExcelVO.setAdministWordNm(AdministWordNm); egovOe1DicGovTermExcelVO.setAdministWordEngNm(AdministWordEngNm); egovOe1DicGovTermExcelVO.setAdministWordAbrv(AdministWordAbrv); egovOe1DicGovTermExcelVO.setAdministWordDc(AdministWordDc); egovOe1DicGovTermExcelVO.setThemaRelm(ThemaRelm); if (UseAt.equals("")) { egovOe1DicGovTermExcelVO.setUseAt("Y"); } else { egovOe1DicGovTermExcelVO.setUseAt("N"); } egovOe1DicGovTermExcelVO.setFrstRegisterId(user.getMberId()); dicGovTermListVO.add(egovOe1DicGovTermExcelVO); for (int kk = 0; kk < dicGovTermListVO.size(); kk++) { if (kk > 1) { if (dicGovTermListVO.get(kk - 1).getAdministWordNm().trim() .equals(AdministWordNm) && dicGovTermListVO.get(kk - 1).getAdministWordEngNm().trim() .equals(AdministWordEngNm) && dicGovTermListVO.get(kk - 1).getAdministWordAbrv().trim() .equals(AdministWordAbrv)) { model.addAttribute("resultMsg", (i + 1) + "??? , ?, ?? . ? ."); return "/cms/cmm/EgovDicGovTermExcelRegist"; } } else if (kk == 1) { if (dicGovTermListVO.get(kk - 1).getAdministWordNm().trim() .equals(AdministWordNm) && dicGovTermListVO.get(kk - 1).getAdministWordEngNm().trim() .equals(AdministWordEngNm) && dicGovTermListVO.get(kk - 1).getAdministWordAbrv().trim() .equals(AdministWordAbrv)) { model.addAttribute("resultMsg", (i + 1) + "??? , ?, ?? . ? ."); return "/cms/cmm/EgovDicGovTermExcelRegist"; } } } } else { if (!"".equals(AdministWordSe) && !"??".equals(AdministWordSe)) { model.addAttribute("resultMsg", (i + 1) + "??? ? /?? . ? ."); } if ("".equals(AdministWordSe) || AdministWordSe == null) { model.addAttribute("resultMsg", (i + 1) + "??? ? . ? ."); } if ("".equals(AdministWordNm) || AdministWordNm == null) { model.addAttribute("resultMsg", (i + 1) + "??? ? . ? ."); } if ("".equals(AdministWordEngNm) || AdministWordEngNm == null) { model.addAttribute("resultMsg", (i + 1) + "??? ?? . ? ."); } if ("".equals(AdministWordAbrv) || AdministWordAbrv == null) { model.addAttribute("resultMsg", (i + 1) + "??? ? . ? ."); } if ("".equals(AdministWordDc) || AdministWordDc == null) { model.addAttribute("resultMsg", (i + 1) + "??? ? . ? ."); } return "/cms/cmm/EgovDicGovTermExcelRegist"; } } // ? ?? . egovOe1DicGovTermService.deleteExcelDicGovTerm(); List<EgovOe1DicGovTermVO> list = dicGovTermListVO; for (EgovOe1DicGovTermVO dicGovTermVo : list) { egovOe1DicGovTermService.insertExcelDicGovTerm(dicGovTermVo); } } else { model.addAttribute("resultMsg", " EXCEL ?? . ."); return "/cms/cmm/EgovDicGovTermExcelRegist"; } } } status.setComplete(); model.addAttribute("resultMsg", " Excel ? ? "); return "forward:/cms/cmm/selectDicGovTermList.do"; } catch (Exception ex) { model.addAttribute("resultMsg", " Excel ? ? , Excel ? ? ?. "); return "/cms/cmm/EgovDicGovTermExcelRegist"; } }
From source file:egovframework.oe1.cms.cmm.web.EgovOe1DicTermController.java
License:Apache License
/** * ? /*from www . j a va 2 s. c om*/ * @param ComDefaultVO - ? VO * @param model * @return "forward:/cms/cmm/selectDicTermList.do" * @exception Exception */ @RequestMapping("/cms/cmm/addDicTermExcelOK.do") public String addDicTermExcelRegister(final HttpServletRequest request, @ModelAttribute("egovOe1DicTermVO") EgovOe1DicTermVO egovOe1DicTermVO, BindingResult bindingResult, Model model, SessionStatus status) throws Exception { Boolean isAuthenticated = EgovUserDetailsHelper.isAuthenticated(); if (!isAuthenticated) { return "/cms/com/EgovLoginUsr"; //? ?? } if (bindingResult.hasErrors()) { model.addAttribute("egovOe1DicTermVO", egovOe1DicTermVO); return "/cms/cmm/addDicTermExcel"; } model.addAttribute("searchMode", egovOe1DicTermVO); // EgovOe1LoginVO user = (EgovOe1LoginVO) EgovUserDetailsHelper.getAuthenticatedUser(); try { final MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request; final Map<String, MultipartFile> files = multiRequest.getFileMap(); Iterator<Entry<String, MultipartFile>> itr = files.entrySet().iterator(); MultipartFile file; String filePath = ""; while (itr.hasNext()) { Entry<String, MultipartFile> entry = itr.next(); file = entry.getValue(); if (!"".equals(file.getOriginalFilename())) { String _storePath = propertyService.getString("Globals.fileStorePath"); File saveFolder = new File(_storePath); if (!saveFolder.exists() || saveFolder.isFile()) { saveFolder.mkdirs(); } String orginFileName = file.getOriginalFilename(); int _index = orginFileName.lastIndexOf("."); String fileName = orginFileName.substring(0, _index); String fileExt = orginFileName.substring(_index + 1); long _size = file.getSize(); if (!"".equals(orginFileName)) { filePath = _storePath + File.separator + "StandardTermStatus.xls"; file.transferTo(new File(filePath)); } // System.out.println("?=====>"+orginFileName); // System.out.println("?=====>"+fileName); // System.out.println("?=====>"+fileExt); // System.out.println("??=====>"+_size); // System.out.println("??=====>"+filePath); if ((fileExt.equals("xls") || fileExt.equals("xlsx")) && (filePath != null && !"".equals(filePath))) { HSSFWorkbook wbT = excelService.loadWorkbook(filePath); HSSFSheet sheetT = wbT.getSheet(wbT.getSheetName(0)); HSSFRow rowValue = sheetT.getRow(2); HSSFCell cellValue0 = rowValue.getCell(0); // HSSFCell cellValue1 = rowValue.getCell(1); // HSSFCell cellValue2 = rowValue.getCell(2); //?(Full Name) HSSFCell cellValue3 = rowValue.getCell(3); //? HSSFCell cellValue4 = rowValue.getCell(4); // HSSFCell cellValue6 = rowValue.getCell(6); //? String synonm = (cellValue0 + "").trim(); String wordNm = (cellValue1 + "").trim(); String engNm = (cellValue2 + "").trim(); String wordNmEngAbrv = (cellValue3 + "").trim(); String wordDc = (cellValue4 + "").trim(); String useAt = (cellValue6 + "").trim(); //System.out.println("synonm====>"+synonm); //System.out.println("wordNm====>"+wordNm); //System.out.println("engNm====>"+engNm); //System.out.println("wordNmEngAbrv====>"+wordNmEngAbrv); //System.out.println("wordDc====>"+wordDc); //System.out.println("useAt====>"+useAt); if (!"".equals(synonm) || !"".equals(wordNm) || !"?(Full Name)".equals(engNm) || !"?".equals(wordNmEngAbrv) || !"".equals(wordDc) || !"?".equals(useAt)) { model.addAttribute("resultMsg", " EXCEL ?? . ."); return "/cms/cmm/EgovDicTermExcelRegist"; } List<EgovOe1DicTermVO> dicTermListVO = new ArrayList<EgovOe1DicTermVO>(); for (int i = 3; i <= sheetT.getLastRowNum(); i++) { HSSFRow row1 = sheetT.getRow(i); HSSFCell cell0 = row1.getCell(0); //(/??) HSSFCell cell1 = row1.getCell(1); // HSSFCell cell2 = row1.getCell(2); //? HSSFCell cell3 = row1.getCell(3); //? HSSFCell cell4 = row1.getCell(4); // HSSFCell cell6 = row1.getCell(6); // synonm = (cell0 + "").trim(); wordNm = (cell1 + "").trim(); engNm = (cell2 + "").trim(); wordNmEngAbrv = (cell3 + "").trim(); wordDc = (cell4 + "").trim(); useAt = (cell6 + "").trim(); // System.out.println("synonm=======>"+synonm); // System.out.println("wordNm=======>"+wordNm); // System.out.println("engNm=======>"+engNm); // System.out.println("wordNmEngAbrv=======>"+wordNmEngAbrv); // System.out.println("wordDc=======>"+wordDc); // System.out.println("useAt=======>"+useAt); if (("".equals(synonm) || "??".equals(synonm)) && (!"".equals(synonm) && synonm != null) && (!"".equals(wordNm) && wordNm != null) && (!"".equals(engNm) && engNm != null) && (!"".equals(wordNmEngAbrv) && wordNmEngAbrv != null)) { EgovOe1DicTermVO egovOe1DicTermExcelVO = new EgovOe1DicTermVO(); egovOe1DicTermExcelVO.setSynonm(synonm); egovOe1DicTermExcelVO.setWordNm(wordNm); egovOe1DicTermExcelVO.setEngNm(engNm); egovOe1DicTermExcelVO.setWordNmEngAbrv(wordNmEngAbrv); egovOe1DicTermExcelVO.setWordDc(wordDc); if (useAt.equals("")) { egovOe1DicTermExcelVO.setUseAt("Y"); } else { egovOe1DicTermExcelVO.setUseAt("N"); } egovOe1DicTermExcelVO.setFrstRegisterId(user.getMberId()); dicTermListVO.add(egovOe1DicTermExcelVO); for (int kk = 0; kk < dicTermListVO.size(); kk++) { if (kk > 1) { // System.out.println("synonm=======>"+egovOe1DicTermExcelVO.getSynonm()); // System.out.println("wordNm=======>"+egovOe1DicTermExcelVO.getWordNm()); // System.out.println("engNm=======>"+egovOe1DicTermExcelVO.getEngNm()); // System.out.println("wordNmEngAbrv=======>"+egovOe1DicTermExcelVO.getWordNmEngAbrv()); // System.out.println("wordDc=======>"+egovOe1DicTermExcelVO.getWordDc()); // System.out.println("useAt=======>"+egovOe1DicTermExcelVO.getUseAt()); // System.out.println("dicTermListVO.get(kk-1).getWordNm().trim()====>"+dicTermListVO.get(kk-1).getWordNm().trim()); // System.out.println("kk=================="+kk); // System.out.println("jj=================="+i); // System.out.println("============================================"); if (dicTermListVO.get(kk - 1).getWordNm().trim().equals(wordNm)) { model.addAttribute("resultMsg", (i + 1) + "??? ? . ? ."); return "/cms/cmm/EgovDicTermExcelRegist"; } if (dicTermListVO.get(kk - 1).getEngNm().trim().equals(engNm)) { model.addAttribute("resultMsg", (i + 1) + "??? ?(Full Name)? . ? ."); return "/cms/cmm/EgovDicTermExcelRegist"; } if (dicTermListVO.get(kk - 1).getWordNmEngAbrv().trim() .equals(wordNmEngAbrv)) { model.addAttribute("resultMsg", (i + 1) + "??? ?? . ? ."); return "/cms/cmm/EgovDicTermExcelRegist"; } } else if (kk == 1) { // System.out.println("synonm=======>"+egovOe1DicTermExcelVO.getSynonm()); // System.out.println("wordNm=======>"+egovOe1DicTermExcelVO.getWordNm()); // System.out.println("engNm=======>"+egovOe1DicTermExcelVO.getEngNm()); // System.out.println("wordNmEngAbrv=======>"+egovOe1DicTermExcelVO.getWordNmEngAbrv()); // System.out.println("wordDc=======>"+egovOe1DicTermExcelVO.getWordDc()); // System.out.println("useAt=======>"+egovOe1DicTermExcelVO.getUseAt()); // System.out.println("dicTermListVO.get(kk-1).getWordNm().trim()====>"+dicTermListVO.get(kk-1).getWordNm().trim()); // System.out.println("kk=================="+i); // System.out.println("============================================"); if (dicTermListVO.get(kk - 1).getWordNm().trim().equals(wordNm)) { model.addAttribute("resultMsg", (i + 1) + "??? ? . ? ."); return "/cms/cmm/EgovDicTermExcelRegist"; } if (dicTermListVO.get(kk - 1).getEngNm().trim().equals(engNm)) { model.addAttribute("resultMsg", (i + 1) + "??? ?(Full Name)? . ? ."); return "/cms/cmm/EgovDicTermExcelRegist"; } if (dicTermListVO.get(kk - 1).getWordNmEngAbrv().trim() .equals(wordNmEngAbrv)) { model.addAttribute("resultMsg", (i + 1) + "??? ?? . ? ."); return "/cms/cmm/EgovDicTermExcelRegist"; } } } } else { if (!"".equals(synonm) && !"??".equals(synonm)) { model.addAttribute("resultMsg", (i + 1) + "??? ? /?? . ? ."); } if ("".equals(synonm) || synonm == null) { model.addAttribute("resultMsg", (i + 1) + "??? ? . ? ."); } if ("".equals(wordNm) || wordNm == null) { model.addAttribute("resultMsg", (i + 1) + "??? ? . ? ."); } if ("".equals(engNm) || engNm == null) { model.addAttribute("resultMsg", (i + 1) + "??? ?(Full Name)? . ? ."); } if ("".equals(wordNmEngAbrv) || wordNmEngAbrv == null) { model.addAttribute("resultMsg", (i + 1) + "??? ? . ? ."); } if ("".equals(wordDc) || wordDc == null) { model.addAttribute("resultMsg", (i + 1) + "??? ? . ? ."); } return "/cms/cmm/EgovDicTermExcelRegist"; } } // ? ?? . egovOe1DicTermService.deleteExcelDicTerm(); List<EgovOe1DicTermVO> list = dicTermListVO; for (EgovOe1DicTermVO dicTermVO : list) { if ("??".equals(dicTermVO.getSynonm())) { egovOe1DicTermService.insertExcelDicTermSynonm(dicTermVO); } else { egovOe1DicTermService.insertExcelDicTerm(dicTermVO); } } } else { model.addAttribute("resultMsg", " EXCEL ?? . ."); return "/cms/cmm/EgovDicTermExcelRegist"; } } } status.setComplete(); model.addAttribute("resultMsg", " Excel ? ? "); return "forward:/cms/cmm/selectDicTermList.do"; } catch (Exception ex) { model.addAttribute("resultMsg", " Excel ? ? , Excel ? ? ?. "); return "/cms/cmm/EgovDicTermExcelRegist"; } }
From source file:egovframework.oe1.cms.cmm.web.EgovOe1DicWordController.java
License:Apache License
/** * ? /*w w w. j a v a 2 s . c o m*/ * @param EgovOe1DicWordVO - ? VO * @param model * @return "forward:/cms/cmm/selectDicWordList.do" * @exception Exception */ @RequestMapping("/cms/cmm/addDicWordExcelOK.do") public String addDicWordExcelRegister(final HttpServletRequest request, @ModelAttribute("egovOe1DicWordVO") EgovOe1DicWordVO egovOe1DicWordVO, BindingResult bindingResult, Model model, SessionStatus status) throws Exception { Boolean isAuthenticated = EgovUserDetailsHelper.isAuthenticated(); if (!isAuthenticated) { return "/cms/com/EgovLoginUsr"; //? ?? } if (bindingResult.hasErrors()) { model.addAttribute("egovOe1DicWordVO", egovOe1DicWordVO); return "/cms/cmm/addDicWord"; } model.addAttribute("searchMode", egovOe1DicWordVO); // EgovOe1LoginVO user = (EgovOe1LoginVO) EgovUserDetailsHelper.getAuthenticatedUser(); try { final MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request; final Map<String, MultipartFile> files = multiRequest.getFileMap(); Iterator<Entry<String, MultipartFile>> itr = files.entrySet().iterator(); MultipartFile file; String filePath = ""; while (itr.hasNext()) { Entry<String, MultipartFile> entry = itr.next(); file = entry.getValue(); if (!"".equals(file.getOriginalFilename())) { String _storePath = propertyService.getString("Globals.fileStorePath"); File saveFolder = new File(_storePath); if (!saveFolder.exists() || saveFolder.isFile()) { saveFolder.mkdirs(); } String orginFileName = file.getOriginalFilename(); int _index = orginFileName.lastIndexOf("."); String fileName = orginFileName.substring(0, _index); String fileExt = orginFileName.substring(_index + 1); long _size = file.getSize(); if (!"".equals(orginFileName)) { filePath = _storePath + File.separator + "StandardWordStatus.xls"; file.transferTo(new File(filePath)); } //System.out.println("?=====>"+orginFileName); //System.out.println("?=====>"+fileName); //System.out.println("?=====>"+fileExt); //System.out.println("??=====>"+_size); //System.out.println("??=====>"+filePath); if ((fileExt.equals("xls") || fileExt.equals("xlsx")) && (filePath != null && !"".equals(filePath))) { HSSFWorkbook wbT = excelService.loadWorkbook(filePath); HSSFSheet sheetT = wbT.getSheet(wbT.getSheetName(0)); HSSFRow rowValue = sheetT.getRow(2); HSSFCell cellValue0 = rowValue.getCell(0); // HSSFCell cellValue1 = rowValue.getCell(1); //?(Full Name) HSSFCell cellValue2 = rowValue.getCell(2); //? HSSFCell cellValue3 = rowValue.getCell(3); // HSSFCell cellValue5 = rowValue.getCell(5); //? String wordNm = (cellValue0 + "").trim(); String engNm = (cellValue1 + "").trim(); String wordNmEngAbrv = (cellValue2 + "").trim(); String wordDc = (cellValue3 + "").trim(); String useAt = (cellValue5 + "").trim(); //System.out.println("wordNm====>"+wordNm); //System.out.println("engNm====>"+engNm); //System.out.println("wordNmEngAbrv====>"+wordNmEngAbrv); //System.out.println("wordDc====>"+wordDc); //System.out.println("useAt====>"+useAt); if (!"".equals(wordNm) || !"?(Full Name)".equals(engNm) || !"?".equals(wordNmEngAbrv) || !"".equals(wordDc) || !"?".equals(useAt)) { model.addAttribute("resultMsg", " EXCEL ?? . ."); return "/cms/cmm/EgovDicWordExcelRegist"; } List<EgovOe1DicWordVO> dicWordListVO = new ArrayList<EgovOe1DicWordVO>(); for (int i = 3; i <= sheetT.getLastRowNum(); i++) { HSSFRow row1 = sheetT.getRow(i); HSSFCell cell0 = row1.getCell(0); // HSSFCell cell1 = row1.getCell(1); //? HSSFCell cell2 = row1.getCell(2); //? HSSFCell cell3 = row1.getCell(3); // HSSFCell cell5 = row1.getCell(5); // wordNm = (cell0 + "").trim(); engNm = (cell1 + "").trim(); wordNmEngAbrv = (cell2 + "").trim(); wordDc = (cell3 + "").trim(); useAt = (cell5 + "").trim(); if ((!"".equals(wordNm) && wordNm != null) && (!"".equals(engNm) && engNm != null) && (!"".equals(wordNmEngAbrv) && wordNmEngAbrv != null) && (!"".equals(wordDc) && wordDc != null)) { EgovOe1DicWordVO egovOe1DicWordExcelVO = new EgovOe1DicWordVO(); egovOe1DicWordExcelVO.setWrdNm(wordNm); egovOe1DicWordExcelVO.setWrdEngNm(engNm); egovOe1DicWordExcelVO.setWrdEngAbrv(wordNmEngAbrv); egovOe1DicWordExcelVO.setWrdDc(wordDc); if (useAt.equals("")) { egovOe1DicWordExcelVO.setUseAt("Y"); } else { egovOe1DicWordExcelVO.setUseAt("N"); } egovOe1DicWordExcelVO.setFrstRegisterId(user.getMberId()); dicWordListVO.add(egovOe1DicWordExcelVO); for (int kk = 0; kk < dicWordListVO.size(); kk++) { if (kk > 1) { if (dicWordListVO.get(kk - 1).getWrdNm().trim().equals(wordNm)) { model.addAttribute("resultMsg", (i + 1) + "??? ? . ? ."); return "/cms/cmm/EgovDicWordExcelRegist"; } if (dicWordListVO.get(kk - 1).getWrdEngNm().trim().equals(engNm)) { model.addAttribute("resultMsg", (i + 1) + "??? ?(Full Name)? . ? ."); return "/cms/cmm/EgovDicWordExcelRegist"; } if (dicWordListVO.get(kk - 1).getWrdEngAbrv().trim() .equals(wordNmEngAbrv)) { model.addAttribute("resultMsg", (i + 1) + "??? ?? . ? ."); return "/cms/cmm/EgovDicWordExcelRegist"; } } else if (kk == 1) { if (dicWordListVO.get(kk - 1).getWrdNm().trim().equals(wordNm)) { model.addAttribute("resultMsg", (i + 1) + "??? ? . ? ."); return "/cms/cmm/EgovDicWordExcelRegist"; } if (dicWordListVO.get(kk - 1).getWrdEngNm().trim().equals(engNm)) { model.addAttribute("resultMsg", (i + 1) + "??? ?(Full Name)? . ? ."); return "/cms/cmm/EgovDicWordExcelRegist"; } if (dicWordListVO.get(kk - 1).getWrdEngAbrv().trim() .equals(wordNmEngAbrv)) { model.addAttribute("resultMsg", (i + 1) + "??? ?? . ? ."); return "/cms/cmm/EgovDicWordExcelRegist"; } } } } else { if ("".equals(wordNm) || wordNm == null) { model.addAttribute("resultMsg", (i + 1) + "??? ? . ? ."); } if ("".equals(engNm) || engNm == null) { model.addAttribute("resultMsg", (i + 1) + "??? ?(Full Name)? . ? ."); } if ("".equals(wordNmEngAbrv) || wordNmEngAbrv == null) { model.addAttribute("resultMsg", (i + 1) + "??? ?? . ? ."); } if ("".equals(wordDc) || wordDc == null) { model.addAttribute("resultMsg", (i + 1) + "??? ? . ? ."); } return "/cms/cmm/EgovDicWordExcelRegist"; } } // ? ?? . egovOe1DicWordService.deleteExcelDicWord(); List<EgovOe1DicWordVO> list = dicWordListVO; for (EgovOe1DicWordVO dicWordVo : list) { egovOe1DicWordService.insertExcelDicWord(dicWordVo); } } else { model.addAttribute("resultMsg", " EXCEL ?? . ."); return "/cms/cmm/EgovDicWordExcelRegist"; } } } status.setComplete(); model.addAttribute("resultMsg", " Excel ? ? "); return "forward:/cms/cmm/selectDicWordList.do"; } catch (Exception ex) { model.addAttribute("resultMsg", " Excel ? ? , Excel ? ? ?. "); return "/cms/cmm/EgovDicWordExcelRegist"; } }
From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java
License:Apache License
/** * [Flow #-5] : ?? ? ? ? //from w w w .ja v a 2s.c o m */ @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...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java
License:Apache License
/** * [Flow #-6] ? : ? ?(?, ? )? // ww w .ja va2 s . co m */ @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...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java
License:Apache License
/** * Data type //ww w .ja v a 2 s . co m * ? ? 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)); }
From source file:eionet.gdem.conversion.excel.writer.ExcelConversionHandler.java
License:Mozilla Public License
@Override public void addCell(String type, String str_value, String style_name) { HSSFSheet _sheet = wb.getSheetAt(currentSheet); HSSFRow _row = _sheet.getRow(currentRow); HSSFCell _cell = _row.createCell((currentCell)); Double number_value = null;/*w w w.j a v a 2 s.c o m*/ Boolean boolean_value = null; boolean isNumber = false; boolean isBoolean = false; if (type == null) { type = (String) getDefaultParams("data_type"); } if (type != null) { if (type.equals("float") || type.equals("number")) { if (str_value != null) { try { number_value = new Double(str_value); isNumber = true; } catch (Exception e) { // the value is not number, it will be inserted as a string // System.out.println(e.toString()); } } else { isNumber = true; } } else if (type.equals("boolean")) { if (str_value != null) { try { boolean_value = new Boolean(str_value); isBoolean = true; } catch (Exception e) { // the value is not boolean, it will be inserted as a string // System.out.println(e.toString()); } } else { isBoolean = true; } } else if (type.equals("date")) { if (str_value != null) { try { // cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyymmdd")); /* * * The way how to handle user defined formats not supported right now HSSFDataFormat format = * wb.createDataFormat(); HSSFCellStyle style = wb.createCellStyle(); * style.setDataFormat(format.getFormat("yyyymmdd")); _cell.setCellStyle(style); */ // cellStyle.setDataFormat(new HSSFDataFormat("yyyymmdd")); /* * try{ l_value=Long.parseLong(value); System.out.println(String.valueOf(l_value)); isLong=true; } * catch(Exception e){ System.out.println(e.toString()); } */ /* * if (isLong){ Date d = new Date(); _cell.setCellStyle(cellStyle); //_cell.setCellValue(d); * _cell.setCellValue(value); //System.out.println(d.toString()); isDate=true; } else * _cell.setCellValue(value); */ // System.out.println("hh"); } catch (Exception e) { System.out.println(e.toString()); } } } } if (isNumber) { if (number_value != null) { _cell.setCellValue(number_value.doubleValue()); } _cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); } else if (isBoolean) { if (boolean_value != null) { _cell.setCellValue(boolean_value.booleanValue()); } _cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN); } else { _cell.setCellType(HSSFCell.CELL_TYPE_STRING); // _cell.setEncoding(HSSFCell.ENCODING_UTF_16 );// _cell.setCellValue(str_value); } short idx = -1; if (style_name != null) { idx = getStyleIdxByName(style_name, ExcelStyleIF.STYLE_FAMILY_TABLE_CELL); } if (idx < 0) { Short short_idx = (Short) getDefaultParams("style"); if (short_idx != null) { idx = short_idx.shortValue(); } } if (idx > -1) { _cell.setCellStyle(wb.getCellStyleAt(idx)); } // calculates the col with according to the first row if (currentRow == 0 && idx > -1) { short colStyleWidth = 0; HSSFCellStyle style = wb.getCellStyleAt(idx); int f_i = style.getFontIndex(); HSSFFont font = wb.getFontAt((short) f_i); // character size short size = font.getFontHeightInPoints(); if (columns.size() > currentCell) { RowColumnDefinition column = columns.get(currentCell); String column_style_name = column.getStyleName() == null ? "" : column.getStyleName(); ExcelStyleIF definedStyle = getStyleByName(column_style_name, ExcelStyleIF.STYLE_FAMILY_TABLE_CELL); if (definedStyle != null) { colStyleWidth = definedStyle.getColumnWidth(); } } short width = (short) (_sheet.getDefaultColumnWidth() * size * 25); if (colStyleWidth > 0) { width = colStyleWidth; } else if (str_value.length() > 0) { width = (short) (str_value.length() * size * 50); } _sheet.setColumnWidth(currentCell, width); } currentCell = _cell.getColumnIndex() + 1; // System.out.println("Cell" + currentCell+ "-" + value); }