List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet iterator
@Override
public Iterator<Row> iterator()
From source file:com.fota.fota3g.deviceMgt.controller.TargetGroupMgtCTR.java
License:Open Source License
/** * targetGroup insert - Excel/*from ww w. j a v a 2 s .c o m*/ * @param param * @param response * @return */ @RequestMapping(value = "/addTargetGroupExcel") @ResponseBody public ModelAndView addTargetGroupExcel(HttpServletRequest request, HttpServletResponse response, ModelMap model) throws Exception { // logger.warn(" >>> TargetGroupMgtCTR - addTargetGroupExcel() start... "); String retMsg = ""; // ? ? String path = "/jb_log/excelUpload/targetGroup/"; File pysicalFolder = new File(path); if (!pysicalFolder.exists() || !pysicalFolder.isDirectory()) { pysicalFolder.mkdirs(); } // ? MultipartRequest multi = new MultipartRequest(request, path, 10 * 1024 * 1024, "utf-8"); String upFileName = multi.getFilesystemName("deviceIdF"); // logger.warn("path : " + path + " ### name : " + upFileName); // user HttpSession httpSession = request.getSession(false); UserLoginVO userVo = (UserLoginVO) httpSession.getAttribute("userInfo"); String userId = userVo.getUserId(); TargetGroupVo vo = new TargetGroupVo(); vo.setMoId(userId); vo.setListGroupName(multi.getParameter("listGroupName")); vo.setDescription(multi.getParameter("description")); vo.setDelYN(multi.getParameter("delYN")); vo.setDeviceType(multi.getParameter("deviceType")); vo.setInputType(multi.getParameter("inputType")); // logger.warn(" >>> TargetGroupMgtCTR - addTargetGroupExcel() vo info\r\n" // + "moId : " + vo.getMoId() + "\r\n" // + "listGroupName : " + vo.getListGroupName() + "\r\n" // + "description : " + vo.getDescription() + "\r\n" // + "delYN : " + vo.getDelYN() + "\r\n" // + "deviceType : " + vo.getDeviceType() + "\r\n" // + "inputType : " + vo.getInputType() + "\r\n" // ); // poi ? ? ? File upfile = new File(path + upFileName); // poi try { FileInputStream file = new FileInputStream(upfile); //.xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); // XSSFSheet sheet = workbook.getSheetAt(0); // Iterator<Row> rowIterator = sheet.iterator(); // ? ? ctn list ? List<String> ctnList = new ArrayList<String>(); // ? // rowIterator.next(); String tempCtn = ""; while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); tempCtn = ""; // ctn Cell cell = row.getCell(0); tempCtn = cell.toString().trim(); if (tempCtn != null && !tempCtn.isEmpty()) { ctnList.add(tempCtn); } } // logger.warn("-----------------------------------------------------------------"); // for(int i=0; i<ctnList.size(); i++) { // logger.warn("ctnList[" + i + "] : " + ctnList.get(i)); // } // logger.warn("-----------------------------------------------------------------"); // ? (console) ? // ? ? '?' ?? vo.setInputType("console"); StringBuffer sbTempDevIdC = new StringBuffer(); for (int i = 0; i < ctnList.size(); i++) { sbTempDevIdC.append(ctnList.get(i) + "\n"); } // logger.warn("ctn str >>>>>\r\n" + sbTempDevIdC.toString()); vo.setDeviceIdC(sbTempDevIdC.toString()); vo = targetSVC.insertTargetGroup(vo); } catch (Exception e) { logger.warn("?? ? ."); e.printStackTrace(); model.addAttribute("msg", "?? ? ."); } // db ? ? upfile.delete(); return new ModelAndView(ajaxMainView, model); }
From source file:com.fota.fota3g.selffotaMgt.controller.ListManagementCTR.java
License:Open Source License
/** * confirm //w w w.j a va2 s. com * @param param * @param response * @return */ @RequestMapping(value = "/registListMgt") @ResponseBody public ModelAndView registListMgt(HttpServletRequest request, HttpServletResponse response, ModelMap model) throws Exception { // logger.warn(" >>>>> Self Fota - ListManagementCTR - registListMgt() start..."); String retMsg = "-1"; // ? ? String path = "/jb_log/excelUpload/selfFotaList/"; File pysicalFolder = new File(path); if (!pysicalFolder.exists() || !pysicalFolder.isDirectory()) { pysicalFolder.mkdirs(); } // ? MultipartRequest multi = new MultipartRequest(request, path, 10 * 1024 * 1024, "utf-8"); String upFileName = multi.getFilesystemName("file"); // ? ? if (upFileName == null || upFileName.equals("")) { model.addAttribute("msg", "?? ."); return new ModelAndView(ajaxMainView, model); } // db Map Map<String, String> entity = new HashMap<String, String>(); // user HttpSession httpSession = request.getSession(false); UserLoginVO userVo = (UserLoginVO) httpSession.getAttribute("userInfo"); String userId = userVo.getUserId(); // int devCnt = 0; entity.put("reg_id", userId); entity.put("file_name", upFileName); entity.put("file_path", path); // 3G FOTA src ? path ? (path + name ) entity.put("company_name", VoV.replaceRoundBracket(multi.getParameter("maker"))); // entity.put("device_cnt", devCnt + ""); // device count excel file ? count ?. // poi ? ? ? File upfile = new File(path + upFileName); List<Map<String, String>> devList = new ArrayList<Map<String, String>>(); // poi try { FileInputStream file = new FileInputStream(upfile); //.xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); // XSSFSheet sheet = workbook.getSheetAt(0); // Iterator<Row> rowIterator = sheet.iterator(); // ? ? Map<String, String> devInfo = null; // ? rowIterator.next(); while (rowIterator.hasNext()) { devInfo = new HashMap<String, String>(); Row row = rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); // model Cell cell = row.getCell(0); devInfo.put("device_model_name", cell.toString().trim()); // previous version cell = row.getCell(1); devInfo.put("device_pre_ver", cell.toString().trim()); // next version cell = row.getCell(2); devInfo.put("device_app_ver", cell.toString().trim()); // ctn cell = row.getCell(3); devInfo.put("ctn", cell.toString().trim()); // imei cell = row.getCell(4); devInfo.put("device_imei", cell.toString().trim()); devList.add(devInfo); } entity.put("device_cnt", devList.size() + ""); // logger.warn("insertXls data : " + entity.toString()); int insertRet = listManagementSVC.insertXls(entity); // logger.warn("insertXls ret : " + insertRet); String master_idx = ""; if (insertRet > 0) { master_idx = listManagementSVC.getInsertedMasterIdx(entity); // logger.warn("getInsertedMasterIdx ret (master_idx) : " + master_idx); } int count = 0; // ? db ? insert for (int i = 0; i < devList.size(); i++) { devList.get(i).put("reg_id", userId); devList.get(i).put("master_idx", master_idx); devList.get(i).put("company_name", entity.get("company_name").toString()); devList.get(i).put("ncn", sdpApi.getNcnInfo(devList.get(i).get("ctn").toString())); // devList.get(i).put("ncn", devList.get(i).get("ctn").toString() + "0001"); // test // logger.warn("devList[" + i + "] insertXlsFotaMng data : " + devList.get(i).toString()); insertRet = listManagementSVC.insertXlsFotaMng(devList.get(i)); // logger.warn("devList[" + i + "] insertXlsFotaMng ret : " + insertRet); count++; } String msg = "? [ " + devList.size() + " ] [ " + count + " ] ? ? ??."; //IMEI , NCN ? // count = devList.size() - count; // msg = msg + "(IMEI : " + count + " , NCN : " + ncnErrCnt + " )"; retMsg = "1"; model.addAttribute("msg", msg); } catch (Exception e) { logger.warn("?? ? ."); e.printStackTrace(); upfile.delete(); model.addAttribute("msg", "?? ? ."); } model.addAttribute("retMsg", retMsg); return new ModelAndView(ajaxMainView, model); }
From source file:com.fota.fota3g.selffotaMgt.controller.ListManagementCTR.java
License:Open Source License
/** * confirm //from ww w. j ava 2 s. com * @param param * @param response * @return */ @RequestMapping(value = "/setXmlData_back") @ResponseBody public ModelAndView setXmlDataRead_back(HttpServletRequest request, HttpServletResponse response, @ModelAttribute("ListManagementVo") ListManagementVo vo, ModelMap model) throws Exception { logger.warn("#############setXmlData################"); { Date now = new Date(); logger.info("File update start : " + now.toString()); String default_path = "F:\\servicefota\\jboss\\download\\"; // ? ? File temp_folder = new File(default_path); if (!temp_folder.exists() || !temp_folder.isDirectory()) { temp_folder.mkdirs(); } // ?? : 500M int maxfilesize = 500 * 1024 * 1024; MultipartRequest multi = null; File upfile = null; try { // ? logger.warn("#############multi start################"); multi = new MultipartRequest(request, default_path, maxfilesize, "utf-8"); upfile = new File(default_path + multi.getFilesystemName("file")); vo.setFileName(upfile.getName()); // logger.warn("#############multi end################" + upfile); // logger.warn("#############multi end################" + upfile); File upfiles = new File(default_path + upfile.getName()); FileInputStream file = new FileInputStream(upfiles); // logger.warn("#############getCell model################"); //.xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); // logger.warn("#############getCell model################"); // XSSFSheet sheet = workbook.getSheetAt(0); // logger.warn("#############getCell model################"); // Iterator<Row> rowIterator = sheet.iterator(); // logger.warn("#############getCell model################"); // ?? List<ListManagementVo> rs = new ArrayList(); // ? rowIterator.next(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); logger.warn("#############getCell model################" + row.cellIterator()); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); ListManagementVo tmp = new ListManagementVo(); rs.add(tmp); // model Cell cell = row.getCell(0); logger.warn("#############getCell model################" + cell); tmp.setDeviceModelName(cell.toString().trim()); if (tmp.getDeviceModelName().length() > 50) { tmp.setDeviceModelName(tmp.getDeviceModelName().substring(0, 49)); // ? logger.warn("#############getCell model################" + tmp.getDeviceModelName()); } //before version cell = row.getCell(1); logger.warn("#############getCell before version################" + cell); tmp.setDevicePerVer(cell.toString().trim()); if (tmp.getDevicePerVer().length() > 50) { tmp.setDevicePerVer(tmp.getDevicePerVer().substring(0, 49)); // ? } //after version cell = row.getCell(2); logger.warn("#############getCell after version################" + cell); tmp.setDeviceAppVer(cell.toString().trim()); if (tmp.getDeviceAppVer().length() > 50) { tmp.setDeviceAppVer(tmp.getDeviceAppVer().substring(0, 49)); // ? } //ctn version cell = row.getCell(3); logger.warn("#############getCell ctn version################" + cell); tmp.setCtn(cell.toString().trim()); if (tmp.getCtn().length() > 50) { tmp.setCtn(tmp.getCtn().substring(0, 49)); // ? } //imei version cell = row.getCell(4); logger.warn("#############getCell imei version################" + cell); tmp.setDeviceImei(cell.toString().trim()); if (tmp.getDeviceImei().length() > 50) { tmp.setDeviceImei(tmp.getDeviceImei().substring(0, 49)); // ? } } for (ListManagementVo tmp : rs) { // mySVC.addDevInfo(tmp); } ListManagementVo footer = new ListManagementVo(); rs.add(footer); file.close(); upfile.delete(); model.addAttribute("gridData", rs); // vo.setBizTypeId(VoV.valid(multi.getParameter("bizTypeId"), 30)); // vo.setSvcId(VoV.valid(multi.getParameter("svcId"), 30, 4)); // vo.setMakerId(VoV.valid(multi.getParameter("makerId"), 30, 4)); // vo.setDevModelId(VoV.valid(multi.getParameter("devModelId"), 30, 4)); // vo.setFotaType(VoV.valid(multi.getParameter("fotaType"), 50)); // if (!vo.getFotaType().equals("") && !vo.getFotaType().equals("PUSH") && !vo.getFotaType().equals("POLLING") && !vo.getFotaType().equals("PUSHPOLLING")) // throw new Exception(); // // //vo.setFirmwareId(VoV.valid(multi.getParameter("firmwareId"), 30, 4)); // //vo.setFirmwareDesc(VoV.valid(multi.getParameter("firmwareDesc"), 1000, 9)); // vo.setFirmwareVer(VoV.valid(multi.getParameter("firmwareVer"), 100, 4)); // vo.setFirmwareMakerVer(VoV.valid(multi.getParameter("firmwareMakerVer"), 100, 8)); // vo.setVerMemo(VoV.valid(multi.getParameter("verMemo"), 1000, 9)); // // // ? ? // String path = default_path + vo.getDevModelId() + "/"; // File pysicalfolder = new File(path); // if(!pysicalfolder.exists() || !pysicalfolder.isDirectory()) { // pysicalfolder.mkdirs(); // } // vo.setFilePath(VoV.valid(path, 200)); // // // ??? // File destFile = new File(path + upfile.getName()); // if(destFile.exists()){ // destFile.delete(); // } // FileUtils.moveFile(upfile, destFile); // // //CRC, '0' // vo.setCrc("0"); // vo.setCretNm(((UserLoginVO)request.getSession().getAttribute("userInfo")).getUserNm()); // } catch (Exception e) { // ?? // ??? ? ? if (upfile != null && upfile.exists()) { upfile.delete(); } String errorMSG = e.getMessage(); if (errorMSG.indexOf("exceeds") != 0) { model.addAttribute("error", "?? 500MB ?."); } logger.info("File update error : " + errorMSG); response.setStatus(500); return new ModelAndView(ajaxMainView, model); } // mySVC.regi(vo); now = new Date(); logger.info("File update end : " + now.toString()); // return new ModelAndView (ajaxMainView, model); } // int retValue = 0; // vo.setModId("sooya118"); // vo.setConfirmState("2"); // // retValue = listManagementSVC.updataConfirm(vo); // retValue = listManagementSVC.updateHold(vo); // // model.addAttribute("retValue", retValue); // return new ModelAndView(ajaxMainView, model); }
From source file:com.fota.fotastatMgt.controller.FotastatCTR.java
License:Open Source License
/** * ? /*from www .j ava 2s . c o m*/ * ? ?. * @param vo * @param model * @return */ @RequestMapping(value = "/checkUploadExcelFile") public ModelAndView checkUploadExcelFile(HttpServletRequest request, HttpServletResponse response, ModelMap model) throws Exception { // logger.warn(" >>>>>>>>>> FotastatCTR - uploadExcelFil() start ... "); String retMsg = ""; // ? ? File pysicalFolder = new File(PATH); if (!pysicalFolder.exists() || !pysicalFolder.isDirectory()) { pysicalFolder.mkdirs(); } // ? MultipartRequest multi = new MultipartRequest(request, PATH, SIZE, "utf-8"); String upFileName = multi.getFilesystemName("file"); // logger.warn("upFile : " + upFile); FotastatSearchVO vo = new FotastatSearchVO(); vo.setBizTypeId(VoV.valid(multi.getParameter("bizTypeId"), 30, 4)); vo.setSvcId(VoV.valid(multi.getParameter("svcId"), 30, 4)); vo.setDevMakerId(VoV.valid(multi.getParameter("devMakerId"), 30, 4)); vo.setDevModelId(VoV.valid(multi.getParameter("devModelId"), 30, 4)); vo.setModemMakerId(VoV.valid(multi.getParameter("modemMakerId"), 30, 4)); vo.setModemModelId(VoV.valid(multi.getParameter("modemModelId"), 30, 4)); vo.setDealerNm(VoV.valid(multi.getParameter("dealerNm"), 50, 2)); // logger.warn("value check...\r\n" // + "bizTypeId : " + vo.getBizTypeId() + "\r\n" // + "svcId : " + vo.getSvcId() + "\r\n" // + "DevMakerId : " + vo.getDevMakerId() + "\r\n" // + "DevModelId : " + vo.getDevModelId() + "\r\n" // + "ModemMakerId : " + vo.getModemMakerId() + "\r\n" // + "ModemModelId : " + vo.getModemModelId() + "\r\n" // + "DealerNm : " + vo.getDealerNm() + "\r\n" // ); // vo.setPage("1"); vo.setRowsPerPage("1000"); if (upFileName == null || upFileName.equals("")) { model.addAttribute("msg", "?? ."); return new ModelAndView(ajaxMainView, model); } // poi ? ? ? File upfile = new File(PATH + upFileName); // poi try { FileInputStream file = new FileInputStream(upfile); //.xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); // XSSFSheet sheet = workbook.getSheetAt(0); // Iterator<Row> rowIterator = sheet.iterator(); // ? ? ctn list ? List<String> ctnList = new ArrayList<String>(); // ? rowIterator.next(); String tempCtn = ""; while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); tempCtn = ""; // ctn Cell cell = row.getCell(0); tempCtn = cell.toString().trim(); ctnList.add(tempCtn); } // ? ctn ? // db ? ctn vo = mySVC.search(vo); List<String> checkedCtnList = new ArrayList<String>(); int checkCount = 0; int fotaPossibilityCount = 0; for (int i = 0; i < ctnList.size(); i++) { for (int j = 0; j < vo.getResults().size(); j++) { if (ctnList.get(i).equals(vo.getResults().get(j).getCtn())) { checkedCtnList.add(ctnList.get(i)); checkCount++; // P(), S(), V(?) FOTA // W(), E(?), F() if (!((vo.getResults().get(j).getProcessStatCd().equals("") || vo.getResults().get(j).getProcessStatCd().equals("P")) || (vo.getResults().get(j).getProcessStatCd().equals("") || vo.getResults().get(j).getProcessStatCd().equals("S")) || (vo.getResults().get(j).getProcessStatCd().equals("?") || vo.getResults().get(j).getProcessStatCd().equals("V")))) { fotaPossibilityCount++; } } } } retMsg = " : " + ctnList.size() + "\r\n" + "? : " + checkCount + "\r\n" + " ? : " + (ctnList.size() - checkCount) + "\r\n" + " (? : " + fotaPossibilityCount + ")"; file.close(); upfile.delete(); model.addAttribute("retMsg", retMsg); model.addAttribute("checkedCtnList", checkedCtnList); model.addAttribute("fotaPossibilityCount", fotaPossibilityCount); } catch (Exception e) { logger.warn("?? ? ."); e.printStackTrace(); upfile.delete(); model.addAttribute("msg", "?? ? ."); } return new ModelAndView(ajaxMainView, model); }
From source file:com.FuntionLibrary.java
public Boolean isCellContentPresent(XSSFSheet sheet, String content) { Boo = false;/*from ww w . jav a 2s. co m*/ try { String CellContent = null; // Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); // Traversing over each row of XLSX file while (rowIterator.hasNext()) { if (Boo == true) break; Row row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { if (Boo == true) break; Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue() + "\t"); CellContent = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue() + "\t"); CellContent = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue() + "\t"); CellContent = cell.getStringCellValue(); break; default: //CellContent = null; } if (CellContent.equalsIgnoreCase(content)) { if (MainGui.EditExistingButtonClicked == true) { break; } Boo = true; break; //else { // throw new Exception("Data Already exists"); // } } else { Boo = false; } } System.out.println(""); if (MainGui.EditExistingButtonClicked == true && CellContent.equalsIgnoreCase(content)) { break; } } } catch (Exception e) { JOptionPane.showMessageDialog(null, e.getMessage()); } return Boo; }
From source file:com.hack23.cia.service.external.vdem.impl.VdemServiceImpl.java
License:Apache License
@Override public List<Question> getQuestions() { final List<Question> list = new ArrayList<>(); try {//from w w w.j a v a2s. c om final XSSFWorkbook myWorkBook = new XSSFWorkbook( VdemServiceImpl.class.getResourceAsStream("/V-DemQuestionIDsv5(2016).xlsx")); final XSSFSheet mySheet = myWorkBook.getSheetAt(0); final Iterator<Row> rowIterator = mySheet.iterator(); rowIterator.next(); while (rowIterator.hasNext()) { final Row row = rowIterator.next(); final Question question = new Question(); if (row.getCell(0) == null) { question.setTag(row.getCell(1).toString()); question.setName(row.getCell(2).toString()); } else { question.setQuestionId(row.getCell(0).toString()); question.setTag(row.getCell(1).toString()); question.setName(row.getCell(2).toString()); } list.add(question); } myWorkBook.close(); } catch (final IOException e) { LOGGER.warn("Problem loading", e); } return list; }
From source file:com.huawei.gsm.controller.AdminController.java
private void parseExcelFile(File file) throws IOException, InvalidFormatException { XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); Map<String, Site> sites = new HashMap<>(); Set<Cell> cells = new HashSet<>(); if (rowIterator.hasNext()) { rowIterator.next();/*from w ww .j a v a 2 s. c o m*/ } int i = 0; while (rowIterator.hasNext()) { Row row = rowIterator.next(); String siteId; String cellIndex; String cellId; try { row.getCell(1).getStringCellValue(); } catch (NullPointerException ex) { break; } if (row.getCell(0).getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { double sId = row.getCell(0).getNumericCellValue(); siteId = String.valueOf(sId).replaceAll("\\.?0*$", ""); } else { siteId = row.getCell(0).getStringCellValue(); } if (row.getCell(6).getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { double cId = row.getCell(6).getNumericCellValue(); cellIndex = String.valueOf(cId).replaceAll("\\.?0*$", ""); } else { cellIndex = row.getCell(6).getStringCellValue(); } if (row.getCell(8).getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { double ceId = row.getCell(8).getNumericCellValue(); cellId = String.valueOf(ceId).replaceAll("\\.?0*$", ""); } else { cellId = row.getCell(8).getStringCellValue(); } Site site = new Site(); site.setAddress(row.getCell(2).getStringCellValue()); site.setGroup((row.getCell(5).getStringCellValue().toLowerCase().equalsIgnoreCase("GOLDEN_SITE") ? SiteGroup.GOLDEN_SITE : SiteGroup.EVENT_SITE)); site.setSiteId(siteId); site.setSiteName(row.getCell(1).getStringCellValue()); site.setLatitude(row.getCell(4).getNumericCellValue()); site.setLongitude(row.getCell(3).getNumericCellValue()); site.setCells(new ArrayList<>()); sites.put(siteId, site); Cell cell = new Cell(); cell.setCellId(cellId); cell.setCellIndex(cellIndex); cell.setCellName(row.getCell(7).getStringCellValue()); cell.setFrequency(row.getCell(9).getStringCellValue()); cell.setSite(site); cells.add(cell); i++; } Iterator<Site> iter = sites.values().iterator(); while (iter.hasNext()) { Site site = iter.next(); cells.stream().filter((Cell s) -> { return s.getSite().getSiteId().equalsIgnoreCase(site.getSiteId()); }).forEach(s -> { site.getCells().add(s); }); } List<Site> sitesX = new ArrayList<>(sites.values()); siteService.saveSiteBatch(sitesX); }
From source file:com.infovity.iep.loader.util.SupplierLoaderUtil.java
public static ArrayList<String[]> getDataFromFile(File inputFile, String sheetName) { ArrayList<String[]> data = new ArrayList<String[]>(); File selectedFile = null;//w w w . j a v a 2 s. c o m FileInputStream fis = null; ; XSSFWorkbook workbook = null; //inputFile = new File("C:\\Users\\INFOVITY-USER-029\\Desktop\\DataLoader\\Consolidated Supplier Data Capture Template v4.0.xlsx"); boolean sheetFound = false; XSSFSheet sheet = null; try { int sheetIndex = -1; fis = new FileInputStream(inputFile); workbook = new XSSFWorkbook(fis); int noOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < noOfSheets; i++) { sheet = workbook.getSheetAt(i); if (sheet.getSheetName().equals(sheetName)) { sheetFound = true; sheetIndex = i; selectedFile = inputFile; break; } } XSSFWorkbook myWorkBook; try { myWorkBook = new XSSFWorkbook(selectedFile); // Return first sheet from the XLSX workbook // XSSFSheet mySheet = myWorkBook.getSheetAt(0); // Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); String query; String[] values = null; // Traversing over each row of XLSX file while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (rowHasData(row) && (row.getRowNum() >= 9)) { int endColumn = row.getLastCellNum(); int startColumn = row.getFirstCellNum(); // For each row, iterate through each columns values = new String[endColumn + 2]; for (int i = startColumn; i < endColumn; i++) { String cellValue; Cell cell = row.getCell(i); values[0] = Integer.toString(row.getRowNum() + 1); if (cell != null) { if (cell.getCellType() == cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)) { DateFormat df = new SimpleDateFormat("yyyy/MM/dd"); Date cellDateValue = cell.getDateCellValue(); cellValue = df.format(cellDateValue); } else { cell.setCellType(cell.CELL_TYPE_STRING); cellValue = cell.getStringCellValue().replaceAll("'", ""); } if (!"".equals(cellValue) && cellValue != null) { values[i + 1] = cellValue; } else if (cellValue.isEmpty() || "".equals(cellValue)) { values[i + 1] = ""; } } else { values[i + 1] = ""; } } data.add(values); } } } catch (InvalidFormatException ex) { Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex); } } catch (IOException ex) { Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex); } finally { try { fis.close(); workbook.close(); } catch (IOException ex) { Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex); } } return data; }
From source file:com.knsi.PerformanceFrame.java
public void WritePerformance() throws Exception { /*just create a new cell at the end of each row at to that add values of the list*/ XSSFWorkbook details = new XSSFWorkbook( new FileInputStream(new File(t4.getSelectedItem().toString() + ".xlsx"))); XSSFSheet spreadsheet1 = details.getSheet("Performance"); XSSFRow row;// w w w . j a va 2 s. c o m Iterator<Row> rowIterator = spreadsheet1.iterator(); while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); //Iterator < Cell > cellIterator = row.cellIterator(); //System.out.println("the last column in this sheet is "+row.getLastCellNum()); nameList.add(row); /*for(int i=0;i<2;i++) { Cell cell = cellIterator.next(); if(i==0) { studentid.setText(cell.getStringCellValue()); } else { studentname.setText(cell.getStringCellValue()); } System .out.print(cell.getStringCellValue() + " \t\t " ); }*/ //System .out.println(); } }
From source file:com.larasolution.serverlts.FileUploadHandler.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // tablename=request.getParameter(tablename) //process only if its multipart content FileOutputStream fos = new FileOutputStream("C:\\uploads\\data.csv"); String list = ""; List<List> allData = new ArrayList<List>(); List<String> parameters = new ArrayList<String>(); if (ServletFileUpload.isMultipartContent(request)) { try {//from w w w. j a v a2s .com StringBuilder data = new StringBuilder(); List<FileItem> multiparts = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request); System.out.println(multiparts); for (FileItem item : multiparts) { if (item.isFormField()) { parameters.add(item.getFieldName()); System.out.println(parameters); } if (!item.isFormField()) { String name = new File(item.getName()).getName(); item.write(new File(UPLOAD_DIRECTORY + File.separator + name)); //System.out.println(File.separator); // Get the workbook object for XLSX file XSSFWorkbook wBook = new XSSFWorkbook( new FileInputStream(UPLOAD_DIRECTORY + File.separator + name)); XSSFSheet zz = wBook.getSheetAt(0); FormulaEvaluator formulaEval = wBook.getCreationHelper().createFormulaEvaluator(); Row row; Cell cell; // Iterate through each rows from first sheet Iterator<Row> rowIterator = zz.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: data.append(cell.getBooleanCellValue()).append(","); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { data.append( com.larasolution.modle.getDate.getDate5(cell.getDateCellValue())) .append(","); } else { data.append(cell.getNumericCellValue()).append(","); } break; case Cell.CELL_TYPE_STRING: data.append(cell.getStringCellValue()).append(","); break; case Cell.CELL_TYPE_BLANK: data.append("" + ","); break; case Cell.CELL_TYPE_FORMULA: Double value = Double.parseDouble(formulaEval.evaluate(cell).formatAsString()); data.append(String.format("%.2f", value)).append(","); break; default: data.append(cell).append(""); } } data.append("\r\n"); //String k = data.substring(0, data.length() - 3); //ls.add(k); // data.setLength(0); } fos.write(data.toString().getBytes()); fos.close(); // } } savetosql(); request.setAttribute("message", "successfully uploaded "); } catch (Exception ex) { request.setAttribute("message", "File Upload Failed due to " + ex); } } else { request.setAttribute("message", "Sorry this Servlet only handles file upload request"); } request.setAttribute("arrayfile", allData); request.setAttribute("names", parameters); RequestDispatcher disp = getServletContext().getRequestDispatcher("/FileUploadResult.jsp"); disp.forward(request, response); // System.out.println(allData.size()); // response.sendRedirect("send.jsp?arrayfile=" + list + ""); //request.getRequestDispatcher("/send.jsp?arrayfile='"+ls+"'").forward(request, response); }