package com.webclient.beans.route; import com.alibaba.fastjson.JSONObject; import com.webclient.beans.udbudgetTask.AssetCFImportData; import com.webclient.beans.udpr.ImpExcelTool; import org.apache.poi.ss.usermodel.*; import psdi.mbo.MboRemote; import psdi.mbo.MboSetRemote; import psdi.server.MXServer; import psdi.util.MXApplicationException; import psdi.util.MXException; import psdi.webclient.system.beans.DataBean; import psdi.webclient.system.controller.MPFormData; import psdi.webclient.system.session.WebClientSession; import javax.servlet.http.HttpSession; import java.io.ByteArrayInputStream; import java.io.InputStream; import java.rmi.RemoteException; import java.text.NumberFormat; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.Date; import java.util.logging.Logger; /** * 点巡检路线批量导入 * edit 2023-11-9 */ public class RouteImportDataBean extends DataBean{ static Logger logger = Logger.getLogger(AssetCFImportData.class.getName()); public static WebClientSession wcs; public static HttpSession mxSession; public static MboRemote mainMbo; public static MPFormData mpData; public void importExecute() throws MXException, RemoteException { wcs = this.clientSession; mpData = ImpExcelTool.getInstance().checkExcelType(wcs); // 校验文件正确性 mxSession = wcs.getRequest().getSession(); if (null == mxSession.getAttribute("mxClientSession")) { mxSession.setAttribute("mxClientSession", wcs); } if (this.app != null && this.app.getAppBean() != null) { mxSession.setAttribute("appMainMbo", this.app.getAppBean().getMbo()); } mxSession.setAttribute("mpData", mpData); wcs.getResponse().setContentType("text/html; charset=UTF-8"); // 转码 try { // 读取文件 byte[] b = mpData.getFileOutputStream().toByteArray(); InputStream ips = new ByteArrayInputStream(b); Workbook workBook= WorkbookFactory.create(ips); Sheet sheet = workBook.getSheetAt(0); int beginRowIndex = 3;// 处理数据的开始行，默认从0开始 int beginColIndex = 0;// 处理数据开始列，默认从0开始 // 验证EXCEL中的数据 String result=validDataAndInsert(sheet, beginRowIndex,beginColIndex); JSONObject resObj = (JSONObject) JSONObject.parse(result); String code = resObj.getString("code"); if(resObj!=null && code !=null && !"".equals(code)){ if("10000".equals(code)){ wcs.showMessageBox("commoninfo", "commoninfo", new String[]{"导入成功!"}); app.getAppBean().save(); app.getAppBean().refreshTable(); app.getAppBean().reloadTable(); app.getAppBean().fireDataChangedEvent(); app.getAppBean().fireStructureChangedEvent(); }else{ wcs.showMessageBox("commoninfo", "commoninfo_err", new String[]{resObj.getString("msg")}); } } } catch (Exception e) { e.printStackTrace(); wcs.showMessageBox("commoninfo", "commoninfo_err2", new String[]{e.getMessage()}); } finally { System.gc(); } } public String validDataAndInsert(Sheet sheet, int beginRowIndex, int beginColIndex) throws RemoteException, MXException, ParseException { JSONObject result = new JSONObject(); int rowi=1; try { // 主单编号 int totalRows = sheet.getLastRowNum(); // 该excel表的总行数 //点巡检路线主表 MboSetRemote routeSet = MXServer.getMXServer().getMboSet("ROUTES",MXServer.getMXServer().getSystemUserInfo()); //点巡检路线主表语言表 MboSetRemote l_routeSet = MXServer.getMXServer().getMboSet("L_ROUTES",MXServer.getMXServer().getSystemUserInfo()); //点巡检路线子表 MboSetRemote route_stopSet = MXServer.getMXServer().getMboSet("route_stop",MXServer.getMXServer().getSystemUserInfo()); Date curdate=new Date(); for (int i = beginRowIndex; i <= totalRows; i++) { // System.out.println("当前插入行：：" + i + "/" + totalRows); Row row = sheet.getRow(i); // 判断何时结束循环 if (row == null || null==row.getCell(0)) { break; } rowi=row.getRowNum()+1; String orgid=getCellString(row, 0);//组织 String siteid=getCellString(row, 1);//地点 String route=getCellString(row, 3);//点巡检路线编号 MboRemote routeMbo=null; try { routeSet.setWhere("ROUTE='"+route+"' AND ORGID='"+orgid+"' AND SITEID='"+siteid+"'"); routeSet.reset(); if(null!=routeSet && routeSet.count()<=0){ routeMbo=routeSet.add(); routeMbo.setValue("ROUTE", route, 11L);//点巡检路线编号 routeMbo.setValue("DESCRIPTION", getCellString(row, 4), 2L);//点巡检路线英文描述 routeMbo.setValue("ORGID", orgid, 2L);//组织 routeMbo.setValue("SITEID", siteid, 2L);//地点 routeMbo.setValue("CREATEBY", getCellString(row, 2), 2L);//创建人工号 routeMbo.setValue("CREATEDATE", curdate, 2L);//创建时间 routeMbo.setValue("LANGCODE", "EN", 2L); routeMbo.setValue("STATUS", "INACTIVE", 2L); routeMbo.getThisMboSet().save(); routeMbo.getThisMboSet().close(); MboRemote l_fcMbo=l_routeSet.add(); l_fcMbo.setValue("OWNERID", routeMbo.getInt("ROUTESID"), 2L); l_fcMbo.setValue("LANGCODE", "ZH", 2L); l_fcMbo.setValue("DESCRIPTION", getCellString(row, 5), 2L); l_fcMbo.getThisMboSet().save(); l_fcMbo.getThisMboSet().close(); MboRemote l_fcMboFr=l_routeSet.add(); l_fcMboFr.setValue("OWNERID", routeMbo.getInt("ROUTESID"), 2L); l_fcMboFr.setValue("LANGCODE", "FR", 2L); l_fcMboFr.setValue("DESCRIPTION", getCellString(row, 6), 2L); l_fcMboFr.getThisMboSet().save(); l_fcMboFr.getThisMboSet().close(); }else{ routeMbo=routeSet.getMbo(0); } }catch (Exception e){ // System.out.println("---接着执行子表"); } MboRemote perglineMbo=routeMbo.getMboSet("ROUTE_STOP").add(); //ROUTE=:ROUTE AND SITEID=:SITEID perglineMbo.setValue("ROUTE", route, 11L);//路线编号 perglineMbo.setValue("SITEID", siteid, 2L);//地点 perglineMbo.setValue("ORGID", orgid, 2L);//组织 perglineMbo.setValue("ASSETLOCSITEID", siteid, 2L);//地点 perglineMbo.setValue("ASSETNUM", getCellString(row, 7), 2L);//资产 perglineMbo.setValue("STOPSEQUENCE", getCellString(row, 8), 2L);//序号 perglineMbo.setValue("ROUTESTOPID", getCellString(row, 8), 2L);//序号 perglineMbo.getThisMboSet().save(); perglineMbo.getThisMboSet().close(); result.put("code", "10000"); result.put("msg", "success"); } }catch (Exception e){ e.getMessage(); e.getCause(); result.put("code", "10001"); result.put("msg", "In row "+rowi+" of excel."+e.getMessage()); } return result.toString(); } private String getCellString(Row row, int i) throws MXApplicationException { Cell cell = row.getCell(i); String cellStr = ""; if (cell == null) { return cellStr; } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { cellStr = cell.getRichStringCellValue().getString().trim(); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { if (DateUtil.isCellDateFormatted(cell)) { // 自定义的日期类型 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Date date = cell.getDateCellValue(); cellStr = sdf.format(date).trim(); } else { NumberFormat nf = NumberFormat.getInstance(); nf.setGroupingUsed(false); // 设置数字格式，不自动用科学计数法 if (i == 15 || i == 18 || i == 19 || i == 21 || i == 23 || i == 47) { Integer value = (int) cell.getNumericCellValue(); // 整数 cellStr = nf.format(value).trim(); } else { double value = cell.getNumericCellValue(); // 小数 cellStr = nf.format(value).trim(); } } } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { // 公式 cellStr = cell.getCellFormula().trim(); } else { cellStr = cell.getRichStringCellValue().getString().trim(); } return cellStr; } }