List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt
@Override public XSSFSheet getSheetAt(int index)
From source file:com.fota.fota3g.deviceMgt.controller.TargetGroupMgtCTR.java
License:Open Source License
/** * targetGroup insert - Excel// w w w.j a va2s .c om * @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 ww . j a v a 2 s. c om * @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 w w w. ja v a 2 s . co m * @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 w w w.j av a 2 s.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.gettec.fsnip.fsn.service.market.impl.ResourceServiceImpl.java
/** * excel?/*ww w . j a v a 2s.c o m*/ */ @Override public List<TestProperty> paseExcelByResource(Resource resource) throws ServiceException { if (resource == null) { return null; } List<TestProperty> items = new ArrayList<TestProperty>(); try { if (resource.getFileName().toUpperCase().endsWith("XLS")) { WorkbookSettings ws = new WorkbookSettings(); ws.setEncoding("iso-8859-1"); Workbook wb = Workbook.getWorkbook(new ByteArrayInputStream(resource.getFile()), ws); Sheet sheet = wb.getSheet(0); if (sheet.getRows() < 1) { return null; } for (int i = 1; i < sheet.getRows(); i++) { if (sheet.getCell(0, i).getContents().trim().length() < 1 || sheet.getCell(3, i).getContents().trim().length() < 1) { continue; } TestProperty itm = new TestProperty(); itm.setName(sheet.getCell(0, i).getContents().trim()); itm.setUnit(sheet.getCell(1, i).getContents().trim()); itm.setTechIndicator(sheet.getCell(2, i).getContents().trim()); itm.setResult(sheet.getCell(3, i).getContents().trim()); itm.setAssessment("?"); if (sheet.getCell(4, i).getContents().trim().contains("?")) { itm.setAssessment("??"); } itm.setStandard(sheet.getCell(5, i).getContents().trim()); items.add(itm); } if (wb != null) { wb.close(); } } else { XSSFRow row = null; XSSFWorkbook xwb = new XSSFWorkbook(new ByteArrayInputStream(resource.getFile())); XSSFSheet sheet = xwb.getSheetAt(0); for (int i = sheet.getFirstRowNum(); i <= sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); if (row == null || row.getCell(0) == null || row.getCell(0).toString().trim().length() < 1 || row.getCell(3).toString().trim().length() < 1) { continue; } TestProperty itm = new TestProperty(); itm.setName(row.getCell(0).toString().trim()); itm.setUnit(row.getCell(1).toString().trim()); itm.setTechIndicator(row.getCell(2).toString().trim()); itm.setResult(row.getCell(3).toString().trim()); itm.setAssessment("?"); if (row.getCell(4).toString().trim().contains("?")) { itm.setAssessment("??"); } itm.setStandard(row.getCell(5).toString().trim()); items.add(itm); } } } catch (Exception e) { throw new ServiceException("MkTestResourceServiceImpl.paseExcelByResource() " + e.getMessage(), e); } return items; }
From source file:com.gnadenheimer.ddjjinformativa.FXMLDocumentController.java
XSSFSheet getSheet() {
try {//from w w w. jav a2 s. co m
InputStream ExcelFileToRead = new FileInputStream(txtFile.getText());
XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
XSSFWorkbook test = new XSSFWorkbook();
return wb.getSheetAt(0);
} catch (Exception ex) {
ex.printStackTrace();
return null;
}
}
From source file:com.griffinslogistics.book.BookForTransportationJSFController.java
public void postProcessXLS(Object document) { XSSFWorkbook wb = (XSSFWorkbook) document; try {/*from w w w .j a v a 2s. co m*/ XSSFSheet sheet = wb.getSheetAt(0); for (int i = 2; i <= sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); row.setHeightInPoints(30); XSSFCell cell = sheet.getRow(i).getCell(0); String text = cell.getStringCellValue(); boolean value = Boolean.parseBoolean(text); if (value) { cell.setCellValue(""); } else { cell.setCellValue("?"); } } } catch (Exception e) { String message = e.getMessage(); e.printStackTrace(); } }
From source file:com.gtja.qh.TransferCtrl.TransferCtrl.java
private boolean transferToTxt(String inputFilePath, String outFileDir) { File inputFile = new File(inputFilePath); String inputFileName = inputFile.getName(); String extension = inputFileName.lastIndexOf(".") == -1 ? "" : inputFileName.substring(inputFileName.lastIndexOf(".") + 1); StringBuffer input = null;// ww w. j ava 2s . co m if ("xls".equals(extension)) { //JXL?excel 2003??xlsx? try { //?excel InputStream is = new FileInputStream(inputFilePath); jxl.Workbook rwb = jxl.Workbook.getWorkbook(is); jxl.Sheet rs = rwb.getSheet(0); int rsRows = rs.getRows(); input = new StringBuffer(); for (int i = 1; i < rsRows; i++) { if (rs.getCell(5, i).getContents().equals("")) { String line = "A999@" + rs.getCell(4, i).getContents() + "@" + rs.getCell(6, i).getContents() + "\r\n"; line = line.replaceAll(",", ""); input.append(line); } else { continue; } } } catch (Exception e) { e.printStackTrace(); return false; } } else if ("xlsx".equals(extension)) { //POI ?excel 2007,??excel 2003 try { InputStream fs = new FileInputStream(inputFilePath); XSSFWorkbook wb; wb = new XSSFWorkbook(fs); XSSFSheet sheet = wb.getSheetAt(0); int rows = sheet.getPhysicalNumberOfRows(); input = new StringBuffer(); for (int i = 1; i < rows; i++) { Row row = sheet.getRow(i); if (row == null) { continue; } if (row.getCell(5).getStringCellValue().equals("")) { if (row.getCell(4) == null) { row.createCell(4); row.getCell(4).setCellValue(""); } if (row.getCell(6) == null) { row.createCell(6); row.getCell(6).setCellValue(""); } String tradeCode = row.getCell(4).getStringCellValue(); Double amount = null; String amt = null; if (row.getCell(6).getCellType() == CELL_TYPE_NUMERIC) { amount = row.getCell(6).getNumericCellValue(); amt = new DecimalFormat("0.00").format(amount); } else { if (row.getCell(6).getStringCellValue().length() == 0) { amount = null; amt = ""; } else { amount = new DecimalFormat("0.00").parse(row.getCell(6).getStringCellValue()) .doubleValue(); //String?Double amt = amount.toString(); } } String line = "A999@" + tradeCode + "@" + amt + "\r\n"; line = line.replaceAll(",", ""); input.append(line); } else { continue; } } } catch (Exception e) { e.printStackTrace(); return false; } } try { // String fileName = "0004_00000001_" + frame.getDate().getText() + "_DailyFundChg"; String outFile = outFileDir + "\\" + fileName + ".txt"; File file = new File(outFile); if (!file.exists()) { file.createNewFile(); } // OutputStream os = new FileOutputStream(file); os.write(input.toString().getBytes()); os.flush(); os.close(); return true; } catch (Exception e) { e.printStackTrace(); JOptionPane.showMessageDialog(null, "???", "?", JOptionPane.ERROR_MESSAGE); return false; } }
From source file:com.gtja.qh.TransferCtrl.TransferCtrl.java
private boolean transferToDbf(String inputFilePath, String outFileDir) throws DBFException { //DBF,?// www . j a va2 s . c om DBFField[] fields = new DBFField[6]; fields[0] = new DBFField(); fields[0].setName("ACCOUNTID"); fields[0].setDataType(DBFField.FIELD_TYPE_C); fields[0].setFieldLength(6); fields[1] = new DBFField(); fields[1].setName("PARTID"); fields[1].setDataType(DBFField.FIELD_TYPE_C); fields[1].setFieldLength(4); fields[2] = new DBFField(); fields[2].setName("CLIENTID"); fields[2].setDataType(DBFField.FIELD_TYPE_C); fields[2].setFieldLength(8); fields[3] = new DBFField(); fields[3].setName("AMOUNT"); fields[3].setDataType(DBFField.FIELD_TYPE_N); fields[3].setFieldLength(23); fields[3].setDecimalCount(2); fields[4] = new DBFField(); fields[4].setName("MONEYTYPE"); fields[4].setDataType(DBFField.FIELD_TYPE_C); fields[4].setFieldLength(4); fields[5] = new DBFField(); fields[5].setName("TYPEMEMO"); fields[5].setDataType(DBFField.FIELD_TYPE_C); fields[5].setFieldLength(40); DBFWriter writer = new DBFWriter(); try { writer.setFields(fields); } catch (Exception e) { e.printStackTrace(); return false; } //excel?? File inputFile = new File(inputFilePath); String inputFileName = inputFile.getName(); String extension = inputFileName.lastIndexOf(".") == -1 ? "" : inputFileName.substring(inputFileName.lastIndexOf(".") + 1); if ("xls".equals(extension)) { //JXL?excel 2003??xlsx? try { //?excel InputStream is = new FileInputStream(inputFilePath); jxl.Workbook rwb = jxl.Workbook.getWorkbook(is); jxl.Sheet rs = rwb.getSheet(0); int rsRows = rs.getRows(); for (int i = 1; i < rsRows; i++) { if (rs.getCell(5, i).getContents().equals("")) { Object[] rowData = new Object[6]; rowData[0] = "000101"; rowData[1] = "0001"; rowData[4] = "A999"; String tradeCode = rs.getCell(4, i).getContents(); Double amount = null; if (rs.getCell(6, i).getType() == CellType.NUMBER) { NumberCell numberCell = (NumberCell) rs.getCell(6, i); amount = numberCell.getValue(); } else { if (rs.getCell(6, i).getContents().length() == 0) { amount = null; } else { amount = new DecimalFormat("0.00").parse(rs.getCell(6, i).getContents()) .doubleValue(); //String?Double } } String typeMemo = rs.getCell(7, i).getContents(); rowData[2] = tradeCode; rowData[3] = amount; rowData[5] = typeMemo; writer.addRecord(rowData); } else { continue; } } } catch (Exception e) { e.printStackTrace(); return false; } } else if ("xlsx".equals(extension)) { //POI ?excel 2007,??excel 2003 try { InputStream fs = new FileInputStream(inputFilePath); XSSFWorkbook wb; wb = new XSSFWorkbook(fs); //wb = new XSSFWorkbook(inputFilePath); XSSFSheet sheet = wb.getSheetAt(0); int rows = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < rows; i++) { Row row = sheet.getRow(i); if (row == null) { continue; } if (row.getCell(5).getStringCellValue().equals("")) { Object[] rowData = new Object[6]; rowData[0] = "000101"; rowData[1] = "0001"; rowData[4] = "A999"; if (row.getCell(4) == null) { row.createCell(4); row.getCell(4).setCellValue(""); } if (row.getCell(6) == null) { row.createCell(6); row.getCell(6).setCellValue(""); } if (row.getCell(7) == null) { row.createCell(7); row.getCell(7).setCellValue(""); } String tradeCode = row.getCell(4).getStringCellValue(); Double amount = null; if (row.getCell(6).getCellType() == CELL_TYPE_NUMERIC) { amount = row.getCell(6).getNumericCellValue(); } else { row.getCell(6).setCellType(CELL_TYPE_STRING); if (row.getCell(6).getStringCellValue().length() == 0) { amount = null; } else { amount = new DecimalFormat("0.00").parse(row.getCell(6).getStringCellValue()) .doubleValue(); } } String typeMemo = row.getCell(7).getStringCellValue(); rowData[2] = tradeCode; rowData[3] = amount; rowData[5] = typeMemo; writer.addRecord(rowData); } else { continue; } } } catch (Exception e) { e.printStackTrace(); return false; } } try { // String fileName = "0001_SG01_" + frame.getDate().getText() + "_1_ClientCapitalDetail"; String outFile = outFileDir + "\\" + fileName + ".dbf"; File file = new File(outFile); if (!file.exists()) { file.createNewFile(); } OutputStream os = new FileOutputStream(file); writer.write(os); //? os.close(); return true; } catch (Exception e) { e.printStackTrace(); JOptionPane.showMessageDialog(null, "???", "?", JOptionPane.ERROR_MESSAGE); return false; } }
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 a2 s . 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; }