package com.webclient.beans.dept; 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-8 */ public class DeptImportDataBean 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)){ 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 deptSet = MXServer.getMXServer().getMboSet("DEPT",MXServer.getMXServer().getSystemUserInfo()); //部门语言主表 MboSetRemote l_deptSet = MXServer.getMXServer().getMboSet("L_DEPT",MXServer.getMXServer().getSystemUserInfo()); 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 deptnum=getCellString(row, 2); deptSet.setWhere("DEPTNUM='"+deptnum+"' AND ORGID='"+orgid+"' AND SITEID='"+siteid+"'"); deptSet.reset(); if(null!=deptSet){ if(deptSet.isEmpty()){ MboRemote perGMbo=deptSet.add(); perGMbo.setValue("ORGID", orgid, 2L);//组织 perGMbo.setValue("SITEID", siteid, 2L);//地点 perGMbo.setValue("DEPTNUM", deptnum, 2L);//部门编号 perGMbo.setValue("DESCRIPTION", getCellString(row, 3), 2L);//部门英文描述 perGMbo.setValue("SUPNUM", getCellString(row, 6), 11L);//上级部门编号 perGMbo.setValue("DIRECTOR", getCellString(row, 7), 2L);//主管人工号 perGMbo.setValue("DEPT1", getCellString(row, 8), 2L);//主管人联系方式 perGMbo.setValue("BMJC", getCellString(row, 9), 2L);//部门简称 perGMbo.setValue("DEPTTYPE", getCellString(row, 10), 11L);//部门类代码 perGMbo.setValue("ISREPORT", getCellString(row, 11), 11L);//是否是报表使用 perGMbo.setValue("LANGCODE", "EN", 2L); perGMbo.setValue("DEPT5", 0, 2L); perGMbo.getThisMboSet().save(); perGMbo.getThisMboSet().close(); MboRemote l_fcMbo=l_deptSet.add(); l_fcMbo.setValue("OWNERID", perGMbo.getInt("DEPTID"), 2L); l_fcMbo.setValue("LANGCODE", "ZH", 2L); l_fcMbo.setValue("DESCRIPTION", getCellString(row, 4), 2L); l_fcMbo.getThisMboSet().save(); l_fcMbo.getThisMboSet().close(); MboRemote l_fcMboFr=l_deptSet.add(); l_fcMboFr.setValue("OWNERID", perGMbo.getInt("DEPTID"), 2L); l_fcMboFr.setValue("LANGCODE", "FR", 2L); l_fcMboFr.setValue("DESCRIPTION", getCellString(row, 5), 2L); l_fcMboFr.getThisMboSet().save(); l_fcMboFr.getThisMboSet().close(); result.put("code", "10000"); result.put("msg", "success"); }else{ result.put("code", "10001"); result.put("msg", "In row "+rowi+" of excel."+deptnum+" 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; } }