List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetName
@Override public String getSheetName(int sheetIndex)
From source file:de.fionera.javamailer.dataProcessors.parseFilesForImport.java
/** * Gets a XLS file and parse it/*w w w. j a v a 2s . c o m*/ * @param file The XLS File that you want to get parsed * @return A ArrayList where the first object is a Array containing the Data and the Second the Header */ public ArrayList<Object> parseXLSFile(File file) { int index = -1; HSSFWorkbook workbook = null; try { try { FileInputStream inputStream = new FileInputStream(file); workbook = new HSSFWorkbook(inputStream); } catch (IOException ex) { ex.printStackTrace(); } assert workbook != null; String[] strings = new String[workbook.getNumberOfSheets()]; //get all sheet names from selected workbook for (int i = 0; i < strings.length; i++) { strings[i] = workbook.getSheetName(i); } JFrame frame = new JFrame("Input Dialog"); String selectedsheet = (String) JOptionPane.showInputDialog(frame, "Which worksheet you want to import ?", "Select Worksheet", JOptionPane.QUESTION_MESSAGE, null, strings, strings[0]); if (selectedsheet != null) { for (int i = 0; i < strings.length; i++) { if (workbook.getSheetName(i).equalsIgnoreCase(selectedsheet)) index = i; } HSSFSheet sheet = workbook.getSheetAt(index); HSSFRow row = sheet.getRow(0); if (row != null) { headers = new String[row.getLastCellNum()]; for (int i = 0; i < row.getLastCellNum(); i++) { headers[i] = row.getCell(i).toString(); } } data = new String[sheet.getLastRowNum()][]; for (int j = 1; j < sheet.getLastRowNum() + 1; j++) { row = sheet.getRow(j); int rowCount = row.getLastCellNum(); String[] dataRow = new String[rowCount]; for (int i = 0; i < rowCount; i++) { HSSFCell cell = row.getCell(i, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK); dataRow[i] = cell.toString(); } data[j - 1] = dataRow; } } else { return null; } } catch (Exception e) { e.printStackTrace(); } ArrayList<Object> returnData = new ArrayList<>(); returnData.add(data); returnData.add(headers); return returnData; }
From source file:edu.ucsd.bioeng.coreplugin.tableImport.tests.ExcelNetworkSheetReaderTest.java
License:Open Source License
/** * DOCUMENT ME!/* ww w .java2s.c o m*/ * * @throws Exception DOCUMENT ME! */ public void testReadTable() throws Exception { InputStream is = null; POIFSFileSystem excelIn; try { is = getClass().getResource(NETWORK_FILE).openStream(); excelIn = new POIFSFileSystem(is); } finally { if (is != null) { is.close(); } } HSSFWorkbook wb = new HSSFWorkbook(excelIn); HSSFSheet sheet = wb.getSheetAt(0); List<String> delimiters = new ArrayList<String>(); delimiters.add(TextFileDelimiters.TAB.toString()); String[] galAttrName = { "Source", "Target", "Interaction", "edge bool attr", "edge string attr", "edge float attr" }; Byte[] galAttrTypes = { CyAttributes.TYPE_STRING, CyAttributes.TYPE_STRING, CyAttributes.TYPE_STRING, CyAttributes.TYPE_BOOLEAN, CyAttributes.TYPE_STRING, CyAttributes.TYPE_FLOATING }; NetworkTableMappingParameters mapping = new NetworkTableMappingParameters(delimiters, TextFileDelimiters.PIPE.toString(), galAttrName, galAttrTypes, null, null, 0, 1, 2, null); reader = new ExcelNetworkSheetReader(wb.getSheetName(0), sheet, mapping); CyNetwork net = Cytoscape.createNetwork(reader, false, null); /* * test cases */ assertEquals("Yeast Network Sheet 1", net.getTitle()); assertEquals(331, net.getNodeCount()); assertEquals(362, net.getEdgeCount()); CyAttributes attr = Cytoscape.getEdgeAttributes(); assertTrue(attr.getBooleanAttribute("YGL122C (pp) YOL123W", "edge bool attr")); assertFalse(attr.getBooleanAttribute("YKR026C (pp) YGL122C", "edge bool attr")); assertEquals(1.2344543, attr.getDoubleAttribute("YBL026W (pp) YOR167C", "edge float attr")); assertEquals("abcd12706", attr.getStringAttribute("YBL026W (pp) YOR167C", "edge string attr")); assertEquals("abcd12584", attr.getStringAttribute("YPL248C (pd) ?", "edge string attr")); Cytoscape.destroyNetwork(net); }
From source file:edu.ucsd.bioeng.coreplugin.tableImport.tests.ExcelNetworkSheetReaderTest.java
License:Open Source License
public void testReadTableWithEmptyRows() throws Exception { String network = "/empty_attr_row.xls"; InputStream is = null;//from w ww .j a v a 2s . c o m POIFSFileSystem excelIn; try { is = getClass().getResource(network).openStream(); excelIn = new POIFSFileSystem(is); } finally { if (is != null) { is.close(); } } HSSFWorkbook wb = new HSSFWorkbook(excelIn); HSSFSheet sheet = wb.getSheetAt(0); List<String> delimiters = new ArrayList<String>(); delimiters.add(TextFileDelimiters.TAB.toString()); String[] galAttrName = { "Gene 1", "Gene 2", "Interaction Type", "Gene", "GO Group" }; Byte[] galAttrTypes = { CyAttributes.TYPE_STRING, CyAttributes.TYPE_STRING, CyAttributes.TYPE_STRING, CyAttributes.TYPE_STRING, CyAttributes.TYPE_STRING }; NetworkTableMappingParameters mapping = new NetworkTableMappingParameters(delimiters, TextFileDelimiters.PIPE.toString(), galAttrName, galAttrTypes, null, null, 0, 1, 2, null); CyNetwork net = null; try { reader = new ExcelNetworkSheetReader(wb.getSheetName(0), sheet, mapping, 1); net = Cytoscape.createNetwork(reader, false, null); } catch (Exception ee) { ee.printStackTrace(); fail("Caught exception"); } assertEquals(222, net.getNodeCount()); assertEquals(443, net.getEdgeCount()); CyAttributes attr = Cytoscape.getEdgeAttributes(); // test some random edges assertEquals("cc", attr.getStringAttribute("YDR459C (cc) YNL271C", "interaction")); assertEquals("Transport", attr.getStringAttribute("YDR459C (cc) YNL271C", "GO Group")); assertEquals("YPR011C", attr.getStringAttribute("YDR459C (cc) YNL271C", "Gene")); assertNull(attr.getStringAttribute("YEL040W (cc) YER016W", "GO Group")); assertNull(attr.getStringAttribute("YEL040W (cc) YER016W", "Gene")); Cytoscape.destroyNetwork(net); }
From source file:egovframework.oe1.cms.cmm.web.EgovOe1DicGovTermController.java
License:Apache License
/** * ? /*from w w w . j ava 2s .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 w w w.ja v a2s .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
/** * ? //from w w w . j av a2 s . c om * @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:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.DBExcelUtility.java
License:BSD License
public String getExcelDataSet(InputStream is, String tableName) throws Exception { StringBuffer sb = new StringBuffer(); HSSFWorkbook wb = ExcelUtility.createWorkbook(is); int cnt = wb.getNumberOfSheets(); sb.append("<dataset>\n"); for (int i = 0; i < cnt; i++) { String name = wb.getSheetName(i); if (name.indexOf(tableName) != -1) { sb.append(loadSheetXML(wb, name)); }//from w w w . ja va 2 s . c o m } sb.append("</dataset>\n"); return sb.toString(); }
From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.DBExcelUtility.java
License:BSD License
public String getExcelDataSet(InputStream is, ArrayList allTablesList) throws Exception { StringBuffer sb = new StringBuffer(); HSSFWorkbook wb = ExcelUtility.createWorkbook(is); int cnt = wb.getNumberOfSheets(); sb.append("<dataset>\n"); tblsList = new ArrayList(); for (int i = 0; i < cnt; i++) { String name = wb.getSheetName(i); if (name.startsWith("tbl_") || name.startsWith("TBL_")) { sb.append(loadSheetXML(wb, name)); }/*from w w w .j av a 2s.co m*/ } sb.append(appendOtherTables(allTablesList)); sb.append("</dataset>\n"); //System.out.println(sb.toString()); return sb.toString(); }
From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.DBExcelUtility.java
License:BSD License
public ExcelResultSet getExcelResultSet(InputStream is, String resultname) throws Exception { ExcelResultSet excelResultSet = null; HSSFWorkbook wb = ExcelUtility.createWorkbook(is); int cnt = wb.getNumberOfSheets(); int row = 2;// w w w. java 2 s . co m int tblnamerow = 2; short col = 1; short tblnamecol = 1; for (int i = 0; i < cnt; i++) { String name = wb.getSheetName(i); if (name.startsWith("exp_") || name.startsWith("EXP_")) { HSSFSheet hssfsheet = ExcelUtility.createHSSFSheet(wb, name); String rsname = ExcelUtility.getString(hssfsheet, tblnamerow, tblnamecol); if (rsname.equalsIgnoreCase(resultname)) { excelResultSet = createExcelResultSet(hssfsheet); break; } } } return excelResultSet; }
From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.DBExcelUtility.java
License:BSD License
public ArrayList createDataObject(InputStream is, String resultname) throws Exception { ArrayList datalist = new ArrayList(); ExcelResultSet excelResultSet = null; HSSFWorkbook wb = ExcelUtility.createWorkbook(is); int cnt = wb.getNumberOfSheets(); int row = 2;/*from ww w. jav a 2 s . c o m*/ int tblnamerow = 2; short col = 1; short tblnamecol = 1; for (int i = 0; i < cnt; i++) { String name = wb.getSheetName(i); if (name.startsWith("event_") || name.startsWith("EVENT_")) { HSSFSheet hssfsheet = ExcelUtility.createHSSFSheet(wb, name); String rsname = ExcelUtility.getString(hssfsheet, tblnamerow, tblnamecol); //System.out.println(rsname + "," + resultname); if (rsname.equalsIgnoreCase(resultname)) { excelResultSet = createExcelResultSet(hssfsheet); if (excelResultSet != null) { ArrayList rows = excelResultSet.getRows(); String[] colnames = excelResultSet.getColumns(); String[] coltypes = excelResultSet.getType(); String[] convertedtypes = convertDataTypes(coltypes); String classname = excelResultSet.getClassname(); int rowcnt = rows.size(); for (int x = 0; x < rowcnt; x++) { ExcelRow ro = (ExcelRow) (rows.get(x)); Object object = excelRowToObject(ro, classname, colnames, convertedtypes); if (object != null) { datalist.add(object); } } } } } } return datalist; }