package com.webclient.beans.uditemsafe; 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-20 */ public class ItemsafeImportDataBean 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(); this.app.getDataBean("results_showlist").reloadTable(); this.app.getDataBean("results_showlist").refreshTable(); 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 uditemsafeSet = MXServer.getMXServer().getMboSet("UDITEMSAFE",MXServer.getMXServer().getSystemUserInfo()); MboRemote uditemsafeMbo=null; for (int i = beginRowIndex; i <= totalRows; i++) { // System.out.println("当前插入行：：" + i + "/" + totalRows); Row row = sheet.getRow(i); // 判断何时结束循环 if (row == null || null==row.getCell(2)) { break; } rowi=row.getRowNum()+1; try{ uditemsafeSet.setWhere("ITEMNUM='"+getCellString(row, 2)+"' AND DEPTNUM='"+getCellString(row, 3)+"' AND ORGID='"+getCellString(row, 0)+"' AND SITEID='"+getCellString(row, 1)+"'"); uditemsafeSet.reset(); if(!uditemsafeSet.isEmpty() && uditemsafeSet.count()>0){ uditemsafeMbo=uditemsafeSet.getMbo(0); System.out.println("uditemsafeMbo; "+uditemsafeMbo); uditemsafeMbo.setValue("ORGID", getCellString(row, 0), 2L); uditemsafeMbo.setValue("SITEID", getCellString(row, 1), 2L); uditemsafeMbo.setValue("ITEMNUM", getCellString(row, 2), 2L); uditemsafeMbo.setValue("DEPTNUM", getCellString(row, 3), 2L); uditemsafeMbo.setValue("LOWQT", getCellString(row, 4), 2L); uditemsafeMbo.setValue("UPQT", getCellString(row, 5), 2L); uditemsafeMbo.setValue("SAFEQT", getCellString(row, 6), 2L); uditemsafeMbo.setValue("ISENABLE", 0, 2L); uditemsafeMbo.getThisMboSet().save(); uditemsafeMbo.getThisMboSet().close(); result.put("code", "10000"); result.put("msg", "success"); }else{ uditemsafeMbo=uditemsafeSet.add(); uditemsafeMbo.setValue("ORGID", getCellString(row, 0), 2L); uditemsafeMbo.setValue("SITEID", getCellString(row, 1), 2L); uditemsafeMbo.setValue("ITEMNUM", getCellString(row, 2), 2L); uditemsafeMbo.setValue("DEPTNUM", getCellString(row, 3), 2L); uditemsafeMbo.setValue("LOWQT", getCellString(row, 4), 2L); uditemsafeMbo.setValue("UPQT", getCellString(row, 5), 2L); uditemsafeMbo.setValue("SAFEQT", getCellString(row, 6), 2L); uditemsafeMbo.setValue("ISENABLE", 0, 2L); uditemsafeMbo.getThisMboSet().save(); uditemsafeMbo.getThisMboSet().close(); result.put("code", "10000"); result.put("msg", "success"); } }catch (Exception e){ result.put("code", "10001"); result.put("msg", "In row "+rowi+" of excel."+e.getMessage()); } } }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; } }