package com.webclient.beans.uditemcfg; import com.alibaba.fastjson.JSONObject; import com.webclient.beans.udpr.ImpExcelTool; import org.apache.poi.ss.usermodel.*; import psdi.mbo.MboRemote; import psdi.mbo.MboSet; 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; /** * 批量导入报表统计备件配置表 * @author lip * @date 2024-05-15 15:37 * */ public class ItemcfgImportData extends DataBean{ static Logger logger = Logger.getLogger(ItemcfgImportData.class.getName()); public static WebClientSession wcs; public static HttpSession mxSession; 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开始 String tableName = "PRLINE"; //要处理的表名 需求计划明细项子表 // 验证EXCEL中的数据 String result=validDataAndInsert(sheet, beginRowIndex,beginColIndex, tableName, wcs); 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(); this.app.getDataBean("results_showlist").reloadTable(); this.app.getDataBean("results_showlist").refreshTable(); wcs.showMessageBox("commoninfo", "commoninfo", new String[]{"导入成功!"}); }else{ wcs.showMessageBox("commoninfo", "commoninfo_err", new String[]{resObj.getString("msg")}); } } } catch (Exception e) { e.printStackTrace(); } finally { System.gc(); } } public String validDataAndInsert(Sheet sheet, int beginRowIndex, int beginColIndex, String tableName, WebClientSession wcs) throws RemoteException, MXException, ParseException { JSONObject result = new JSONObject(); int rowi=1; try { // 主单编号 int totalRows = sheet.getLastRowNum(); // 该excel表的总行数 MboSetRemote uditemcfgSet = MXServer.getMXServer().getMboSet("UDITEMCFG",MXServer.getMXServer().getSystemUserInfo()); MboSetRemote depClset = MXServer.getMXServer().getMboSet("UDDEPTCLASS",MXServer.getMXServer().getSystemUserInfo()); MboSetRemote uditemCfgSet2 = MXServer.getMXServer().getMboSet("UDITEMCFG",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 fl=getCellString(row, 2);//分类阈值(0备品备件，1战略备件) 仅限这两种 if(!"0".equalsIgnoreCase(fl) && !"1".equalsIgnoreCase(fl)){ result.put("code", "10001"); result.put("msg", "In row "+rowi+" of excel. 分类不是备品备件或战略备件，仅限这两种分类！"); return result.toString(); } String bml=getCellString(row, 4);//部门类 String org=getCellString(row, 6);//业务线 depClset.setWhere("DEPTCLASSNUM='"+bml+"' and ORGID='"+org+"' "); depClset.reset(); if(null==depClset || depClset.count()<=0){ result.put("code", "10001"); result.put("msg", "In row "+rowi+" of excel. 部门类"+bml+"不存在系统中！"); return result.toString(); } } 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 itemnum=getCellString(row, 0); String orgid=getCellString(row, 6); uditemCfgSet2.setWhere("ITEMNUM='"+itemnum+"' and ORGID='"+orgid+"' "); uditemCfgSet2.reset(); if(null==uditemCfgSet2 || uditemCfgSet2.count()<=0){ MboRemote uditemCfgMbo=uditemcfgSet.add(); uditemCfgMbo.setValue("ITEMNUM", itemnum, 2L); uditemCfgMbo.setValue("ORGID", orgid, 2L); uditemCfgMbo.setValue("UDFL", getCellString(row, 2), 2L); uditemCfgMbo.setValue("ISACTIVE", getCellString(row, 3), 2L);//状态 uditemCfgMbo.setValue("DEPTNUM", getCellString(row, 4), 2L);//部门类 uditemCfgMbo.getThisMboSet().save(); }else{ MboRemote udcfgMbo=uditemCfgSet2.getMbo(0); udcfgMbo.setValue("UDFL", getCellString(row, 2), 2L);//分类 udcfgMbo.setValue("ISACTIVE", getCellString(row, 3), 2L);//状态 udcfgMbo.setValue("DEPTNUM", getCellString(row, 4), 2L);//部门类 udcfgMbo.getThisMboSet().save(); } 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; } }