package com.webclient.beans.user; 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-13 */ public class UserImportDataBean 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 = 2;// 处理数据的开始行，默认从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)){ app.getAppBean().refreshTable(); app.getAppBean().reloadTable(); app.getAppBean().fireDataChangedEvent(); app.getAppBean().fireStructureChangedEvent(); wcs.showMessageBox("commoninfo", "commoninfo", new String[]{"导入成功!"}); // app.getAppBean().save(); }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 maxuserSet = MXServer.getMXServer().getMboSet("MAXUSER",MXServer.getMXServer().getSystemUserInfo()); //权限主表 // MboSetRemote groupuserSet = MXServer.getMXServer().getMboSet("GROUPUSER",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 userid=getCellString(row, 0);//用户 maxuserSet.setWhere("USERID='"+userid+"'"); maxuserSet.reset(); if(null!=maxuserSet && maxuserSet.count()<=0){ MboRemote routeMbo=maxuserSet.add(11L); routeMbo.setValue("USERID", userid, 2L); routeMbo.setValue("PERSONID", userid, 2L); routeMbo.setValue("LOGINID", userid, 2L); routeMbo.setValue("DEFSITE", getCellString(row, 1), 2L); routeMbo.setValue("STATUS", "ACTIVE", 2L); routeMbo.setValue("TYPE", "TYPE 1", 2L); routeMbo.setValue("QUERYWITHSITE", 1, 2L); routeMbo.setValue("PASSWORD", "Sunda@2023", 11L); routeMbo.setValue("FORCEEXPIRATION", 0, 2L); routeMbo.setValue("FAILEDLOGINS", 0, 2L); routeMbo.setValue("SYSUSER", 0, 2L); routeMbo.setValue("INACTIVESITES", 0, 2L); routeMbo.setValue("SCREENREADER", 0, 2L); routeMbo.setValue("ISCONSULTANT", 0, 2L); routeMbo.setValue("SIDENAV", 0, 2L); routeMbo.setValue("SIDENAV", 0, 2L); routeMbo.setValue("EMAILPSWD", 0, 11L); routeMbo.setValue("FORCEEXPIRATION", 0, 11L); routeMbo.setValue("passwordinput", "Sunda@2023", 11L); routeMbo.setValue("passwordcheck", "Sunda@2023", 11L); routeMbo.getThisMboSet().save(11L); routeMbo.getThisMboSet().close(); result.put("code", "10000"); result.put("msg", "success"); }else{ result.put("code", "10001"); result.put("msg", "In row "+rowi+" of excel."+userid+" already exists. "); return result.toString(); } } }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; } }