eCoWorksV3.git - Gitblit

xinyb

2024-09-09 6a63e2f86f981066357bd60d34496c627a51e3a8

提交 \| 用户 \| age
89e607	1	package com.yc.action.excel;
X	2
	3	import com.yc.action.BaseAction;
	4	import com.yc.action.grid.GTGrid;
	5	import com.yc.action.grid.Grid;
	6	import com.yc.action.grid.TreeGridDTO;
	7	import com.yc.action.grid.TreeGridIfc;
9972d7	8	import com.yc.action.tree.Tree_2;
89e607	9	import com.yc.currentThreadInfo.CurrentLocal;
X	10	import com.yc.entity.attachment.AttachmentEntity;
	11	import com.yc.entity.attachment.AttachmentWhereEntity;
	12	import com.yc.exception.CallBackMessage;
	13	import com.yc.factory.FactoryBean;
	14	import com.yc.multiData.SpObserver;
	15	import com.yc.service.excel.ExcelIfc;
	16	import com.yc.service.impl.DBHelper;
	17	import com.yc.service.panel.SqlDBHelperIfc;
9972d7	18	import com.yc.service.tree.TreeJson;
89e607	19	import com.yc.service.upload.AttachmentIfc;
X	20	import com.yc.utils.DefaultSet;
	21	import com.yc.utils.EncodeUtil;
	22	import com.yc.utils.GTJson;
	23	import com.yc.utils.SessionKey;
1417c2	24	import lombok.Data;
89e607	25	import org.apache.commons.collections.map.HashedMap;
X	26	import org.apache.poi.hssf.usermodel.HSSFWorkbook;
	27	import org.apache.poi.ss.usermodel.*;
	28	import org.apache.poi.xssf.usermodel.XSSFWorkbook;
	29	import org.springframework.beans.factory.annotation.Autowired;
	30	import org.springframework.stereotype.Controller;
	31	import org.springframework.web.bind.annotation.RequestMapping;
	32	import org.springframework.web.multipart.MultipartFile;
	33	import org.springframework.web.multipart.MultipartHttpServletRequest;
	34	import org.springframework.web.multipart.MultipartResolver;
	35	import org.springframework.web.multipart.commons.CommonsMultipartResolver;
	36
	37	import javax.servlet.http.HttpServletRequest;
	38	import javax.servlet.http.HttpServletResponse;
	39	import java.text.DateFormat;
7ca94d	40	import java.text.DecimalFormat;
89e607	41	import java.text.SimpleDateFormat;
X	42	import java.util.*;
7ca94d	43	import java.util.regex.Pattern;
89e607	44
X	45	@Controller
	46	public class NewExcel extends BaseAction {
	47
	48	@Autowired
	49	ExcelIfc excelIfc;
	50	@Autowired
	51	TreeGridIfc treeGridIfc;
1417c2	52	@Data
X	53	class isNotBlank {
	54	private boolean notBlank;
	55	}
89e607	56	/**
X	57	* 导入Excel数据的请求方法
	58	*
	59	* @param request
	60	* @param response
	61	*/
	62	@RequestMapping("/uploadXlsFile.do")
	63	public void UploadExcelFile(HttpServletRequest request, HttpServletResponse response) {
	64	try {
	65	MultipartResolver resolver = new CommonsMultipartResolver(request.getSession().getServletContext());
	66	MultipartHttpServletRequest multipartRequest = resolver.resolveMultipart(request);
	67	MultipartFile file = multipartRequest.getFile("xlsFile");//xls文件信息
	68	String xlsName = file.getOriginalFilename();
	69	String suffix = xlsName.substring(xlsName.lastIndexOf(".") + 1).toLowerCase();
	70	Workbook workbook = null;
	71	if ("xls".equals(suffix)) {
	72	workbook = new HSSFWorkbook(file.getInputStream());
	73	} else if ("xlsx".equals(suffix)) {
	74	workbook = new XSSFWorkbook(file.getInputStream());
	75	} else {
	76	super.printJson(response, new CallBackMessage().sendErrorMessage("只支持xls或xlsx后缀的文件"));
	77	}
	78	String doccode = multipartRequest.getParameter("doccode");
	79	doccode = (doccode == null ? "" : doccode);
	80	int formid = Integer.parseInt(multipartRequest.getParameter("formid"));
	81	int formtype = Integer.parseInt(multipartRequest.getParameter("formtype"));
	82	if (workbook != null) {
	83	Map<Integer, List<HashMap<String, String>>> map = new HashedMap();
79a15a	84	try {
X	85	String dbId = (String) request.getSession().getAttribute(SessionKey.DATA_BASE_ID);
	86	SpObserver.setDBtoInstance("_" + dbId);
	87	List<Map<String, Object>> info = excelIfc.getInfo(formid);
	88	int type = 0;//0表示设置导入的是主表1表示从表
	89	for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
	90	Sheet sheet = workbook.getSheetAt(i);
	91	if (sheet == null \|\| sheet.getPhysicalNumberOfRows() <= 1) {//第一行是标题，大于1表示有数据
	92	continue;
	93	}
	94	type = (i == 0 ? 1 : 0);//这里的workbook第一个sheet是明细表数据，所以当i==0表示type为1导入的是从表
	95	Map<String, Object> typeMap = getFieldMap(sheet, type, info);
	96	List<HashMap<String, String>> rowli = getRowData(doccode, formid, formtype, sheet, typeMap, request);
	97	map.put(i, rowli);
89e607	98	}
79a15a	99	} finally {
X	100	SpObserver.setDBtoInstance();
89e607	101	}
X	102	List<GTJson> gList = new ArrayList<GTJson>();
77e6d7	103	if (map.get(1) != null && map.get(1).size() > 0) {//主表信息
89e607	104	if (formtype == 1 \|\| formtype == 3) {//特殊窗体的导入不能放在主表信息集合里
X	105	gList.add(getGTJson(map.get(1), null, doccode, formid, formtype));
9972d7	106	} else if (formtype == 2) {//导入2类型窗体的处理 xin 2023-2-21 15:52:32
X	107	request.setAttribute("treeJson", getTreeJson(map.get(1), null, doccode, formid, formtype));
	108	request.setAttribute("formid", formid + "");
330e5b	109	request.setAttribute("isImport","true");//是导入进来的
9972d7	110	Tree_2 tree2 = (Tree_2) FactoryBean.getBean("tree_2");
X	111	tree2.addNode(request, response);
	112	return;
89e607	113	} else {
X	114	for (HashMap<String, String> m : map.get(1)) {
	115	gList.add(getGTJson((map.get(0) != null ? map.get(0) : null), m, doccode, formid, formtype));
	116	}
	117	}
	118	} else if (map.get(0) != null && map.get(0).size() > 0) {//从表信息
	119	gList.add(getGTJson(map.get(0), (map.get(1) != null ? map.get(1).get(0) : null), doccode, formid, formtype));
	120	}
	121	String typeToGrid = getWinType(formtype) + "\|" + ((getWinType(formtype) == 499 \|\| getWinType(formtype) == 497)
	122	? "0" : ((formtype == getWinType(formtype)) ? "0" : "1"));
	123	//保存
	124	String text = saveExcelImport(gList, formid, typeToGrid, request, response);
	125	if ("".equals(text)) {
	126	super.printJson(response, new CallBackMessage().sendSuccessMessage("导入成功！"));
	127	} else {
	128	text = "导入数据在执行保存时错误：" + text + saveExcelJournal(file, formid, formtype, doccode, text, "Fail", request);
	129	super.printJson(response, new CallBackMessage().sendErrorMessage(text));
	130	}
	131	}
	132	} catch (Exception e) {
	133	super.printJson(response, new CallBackMessage().sendErrorMessage(e.getCause() != null ? e.getCause().getMessage() : e.getMessage()));
	134	}
	135	}
	136
	137	/**
	138	* 保存Excel导入数据
	139	*
	140	* @param jsons
	141	* @param request
	142	* @param response
	143	*/
	144	public String saveExcelImport(List<GTJson> jsons, int formid, String type, HttpServletRequest request, HttpServletResponse response) throws Exception {
	145	TreeGridDTO dto = new TreeGridDTO();
	146	Grid grid = null;
	147	try {
79a15a	148	try {
X	149	SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
	150	dto.dbid = (String) request.getSession().getAttribute(SessionKey.DATA_BASE_ID);
	151	grid = treeGridIfc.getGridInfo(formid, type, true, dto);// 获得Grid
	152	} finally {
	153	SpObserver.setDBtoInstance();
	154	}
89e607	155	grid.setImport(true);
X	156	request.setAttribute("_gt_json", jsons);// 赋值
	157	GTGrid gt = (GTGrid) FactoryBean.getBean("GTGrid");
	158	CurrentLocal.setExeclInfo("");
	159	gt.saveFinal(grid, request, response);// 保存执行
	160	return CurrentLocal.getExeclInfo();
	161	} catch (Exception e) {
ccda7c	162	throw e;
89e607	163	}
X	164	}
	165
	166	/**
	167	* 导入日志记录保存
	168	*
	169	* @param file
	170	* @param formid
	171	* @param formtype
	172	* @param doccode
	173	* @param error
	174	* @param state
	175	* @param request
	176	* @return
	177	*/
	178	public String saveExcelJournal(MultipartFile file, int formid, int formtype, String doccode, String error, String state, HttpServletRequest request) {
	179	try {
	180	DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 设置显示格式
	181	String unid = UUID.randomUUID().toString().toUpperCase();// 生成uuid
	182	String usercode = request.getSession().getAttribute(SessionKey.USERCODE).toString();
	183	String username = request.getSession().getAttribute(SessionKey.USERNAME).toString();
	184	AttachmentEntity attachment = new AttachmentEntity();
	185	attachment.setUnid(unid);
	186	attachment.setFormId(formid);
	187	attachment.setOriginalPicture(file.getBytes());//input2byte(new FileInputStream(new File(path)))
	188	attachment.setOriginalFileName(file.getOriginalFilename());//map.get(ExlsParam.BEFORE_NAME.getKey()).toString()
	189	attachment.setAuthorCode(usercode);
	190	attachment.setAuthorName(username);
	191	AttachmentIfc attachmentIfc = (AttachmentIfc) FactoryBean.getBean("AttachmentImpl");
	192	SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
	193	AttachmentWhereEntity whereEntity = attachmentIfc.saveAttachment(attachment, file, "0");
	194	//附件唯一标示 unid
	195	if (whereEntity.getUnid() != null && whereEntity.getSeq() != null) {
	196	unid = whereEntity.getUnid() + ";" + whereEntity.getSeq();
	197	} else {
	198	unid += ";0";
	199	}
	200	int cont = excelIfc.excelRecord(formid, doccode, usercode, username, "In", df.format(new Date()), unid, error, formtype + "", state, "");
	201	if (cont > 0) {
	202	return "【导入日志已记录】";
	203	} else {
	204	return "";
	205	}
	206	} catch (Exception e) {
	207	return "";
	208	} finally {
	209	SpObserver.setDBtoInstance();
	210	}
	211	}
	212
	213	/**
	214	* 把Excel文件遍历后得到的数据内容添加到GTJson对象内
	215	*
	216	* @param detailed 明细数据
	217	* @param panel 主表数据
	218	* @return
	219	*/
	220	public GTJson getGTJson(List<HashMap<String, String>> detailed, HashMap<String, String> panel, String doccode, int formid, int formtype) throws Exception {
	221	GTJson json = new GTJson();
	222	try {
	223	if (panel != null) {
	224	List<HashMap<String, String>> panelRecords = new ArrayList<HashMap<String, String>>();
	225	panelRecords.add(panel);
	226	json.setPanelRecords(panelRecords);
	227	}
	228	json.setInsertedRecords(detailed);
	229	if (!"".equals(doccode.trim())) {
	230	json.setDoccode(EncodeUtil.base64Encode(doccode.trim()));
	231	json.setPanelAction("update");// 添加是修改还插入的sql类型（add或update）。
	232	} else {
	233	json.setPanelAction("add");
	234	}
	235	json.setDisableDuplicateSubmitUUID("excel");//是导入
	236	json.setFormid(formid + "");
	237	json.setgType(formtype + "");
	238	json.setPanelTable(formid + ";" + formtype);
	239	return json;
	240	} catch (Exception e) {
	241	throw new Exception("导入数据信息添加到GTJson时出现错误：" + e);
	242	}
	243	}
	244
9972d7	245	public TreeJson getTreeJson(List<HashMap<String, String>> detailed, HashMap<String, String> panel, String doccode, int formid, int formtype) throws Exception {
X	246	TreeJson json = new TreeJson();
	247	try {
	248	json.setInsertedRecords(detailed);
	249	List<HashMap<String,String>> list=new ArrayList<>();
	250	json.setUpdatedRecords(list);
	251	json.setUpdatedRecords(list);
	252	json.setDeleteRecords(list);
	253	json.setDragRecords(list);
	254	return json;
	255	} catch (Exception e) {
	256	throw new Exception("导入数据信息添加到GTJson时出现错误：" + e);
	257	}
	258	}
	259
89e607	260	/**
X	261	* 遍历Excel文件的每行每列
	262	*
	263	* @param sheet
	264	* @param fieldmap
	265	* @return
	266	* @throws Exception
	267	*/
	268	public List<HashMap<String, String>> getRowData(String doccode, int formid, int formtype, Sheet sheet, Map<String, Object> fieldmap, HttpServletRequest request) throws Exception {
15616b	269	int errRow=0;
89e607	270	try {
X	271	List<HashMap<String, String>> list = new ArrayList<>();
	272	List<String> imp = (List<String>) fieldmap.get("Import");
	273	int CellNum = 0;//总列数
	274	boolean isNullCell = true;//是空行
	275	//获取的是物理行数，也就是不包括那些空行（隔行）的情况
	276	for (int r = 0; r < sheet.getPhysicalNumberOfRows(); r++) {
15616b	277	errRow++;
89e607	278	Row row = sheet.getRow(r);//获取行的信息
79a15a	279	if(row==null){
X	280	continue;
	281	}
89e607	282	if (r == 0) {//第0行必定是标题行
X	283	CellNum = row.getPhysicalNumberOfCells();//0元素是标题，标题有多少列那么数据也要有多少列对应。获取不为空的列个数。
	284	continue;
	285	}
	286	if (row.getPhysicalNumberOfCells() != CellNum) {//导入数据的行信息列和标题行不相等有可能是空行或不能识别是空行情况
	287	isNullCell = true;//是空行
	288	for (Cell cell : row) {
	289	if (cell.getCellType() != CellType.BLANK) {//列值类型如果不是CellType.BLANK那就不是空行
	290	isNullCell = false;
	291	break;
	292	}
	293	}
	294	if (isNullCell) {
	295	continue;
	296	}
	297	}
	298	Map<String, Object> map = new HashMap<>();
	299	map.putAll((Map<? extends String, ?>) fieldmap.get("InitValue"));
1417c2	300	isNotBlank blank=new isNotBlank(); //整行都不是空值（有一列有值就不算空行）
89e607	301	for (int c = 0; c < CellNum; c++) {
1417c2	302	map.put(imp.get(c), getCellVlaue(row.getCell(c),blank));
89e607	303	}
1417c2	304	if(blank.notBlank){//不是空行就进入
X	305	list.add(getDynamicValue(doccode, formid, formtype, map, request));//处理有会话值或动态值后添加到list内
	306	}
89e607	307	}
X	308	return list;
	309	} catch (Exception e) {
15616b	310	throw new Exception("导入数据信息在读取Excel的第"+errRow+"行时出现错误：" + e);
89e607	311	}
X	312	}
	313
	314	/**
	315	* 处理数据的值有会话(@cccode)或动态(如：!select ****)的情况
	316	*
	317	* @param doccode
	318	* @param formid
	319	* @param formtype
	320	* @param map
	321	* @param request
	322	* @return
	323	* @throws Exception
	324	*/
	325	private HashMap<String, String> getDynamicValue(String doccode, int formid, int formtype, Map<String, Object> map, HttpServletRequest request) throws Exception {
	326	try {
	327	String value = "";
	328	HashMap<String, String> valueMap = new HashMap<>();
79a15a	329	// SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
89e607	330	SqlDBHelperIfc sqlDBHelperIfc = (SqlDBHelperIfc) FactoryBean.getBean("SqlDBHelper");
X	331	for (String k : map.keySet()) {
	332	value = (String) map.get(k);
	333	if (value.indexOf("@") != -1) {
	334	value = DBHelper.getValRepShi(formid, value, request.getSession(), null, false);
	335	}
	336	if (value.indexOf("!") != -1) {
	337	List<Map<String, Object>> list = sqlDBHelperIfc.getHashMapObj(value.replaceAll("!", ""));
790334	338	if (list.size() > 0) {
X	339	for (String key : list.get(0).keySet()) {
	340	value = (String) list.get(0).get(key);//取值
	341	}
	342	} else {
	343	value = "";
89e607	344	}
X	345	}
d0268d	346	if(k.toLowerCase().equals("formid") && "".equals(value)){
X	347	value=formid+"";
	348	}
89e607	349	if (k.toLowerCase().equals("doccode") && !"".equals(doccode)) {
X	350	value = doccode;
	351	}
	352	String v = DefaultSet.getDefaultValue(formid + "-" + formtype + "-" + k, request.getSession());
	353	if (v != null && !"".equals(v.trim())) {
	354	value = v;
	355	}
	356	valueMap.put(k.trim().toLowerCase(), value);//把导入字段去空格和转成小写，然后重新赋值
	357	}
	358	return valueMap;
	359	} catch (Exception e) {
15616b	360	throw new Exception("会话值处理或动态值处理错误：" + e.getCause()!=null?e.getCause().getMessage():e.getMessage());
89e607	361	} finally {
79a15a	362	// SpObserver.setDBtoInstance();
89e607	363	}
X	364	}
	365
	366	/**
	367	* 获取系统导入设置的信息和初始值
	368	*
	369	* @param sheet
	370	* @param type
	371	* @param info
	372	* @return
	373	* @throws Exception
	374	*/
	375	public Map<String, Object> getFieldMap(Sheet sheet, int type, List<Map<String, Object>> info) throws Exception {
	376	try {
	377	Map<String, Object> map = new HashedMap();
	378	if (info != null && info.size() > 0) {
	379	List<String> list = new ArrayList<>();
	380	Map<String, Object> value = new HashedMap();
	381	for (int i = 0; i < info.size(); i++) {
	382	if (DBHelper.getValueInt(info.get(i), "headflag") == type) {
	383	//设置了需要导入信息的字段
	384	if (DBHelper.getValueInt(info.get(i), "isImport") == 1) {
	385	list.add(DBHelper.getValue(info.get(i), "fieldid"));
	386	}
	387	//所以对于type的字段初始值
	388	value.put(DBHelper.getValue(info.get(i), "fieldid"), DBHelper.getValue(info.get(i), "InitValue"));
	389	}
	390	}
	391	if (sheet.getRow(0).getPhysicalNumberOfCells() != list.size()) {
	392	throw new Exception("此功能界面设置导入的" + (type == 1 ? "明细数据" : "主数据") + "字段数(" + list.size() + "个)，" +
	393	"Excel文件内的" + (type == 1 ? "明细数据" : "主数据") + "标题列数" +
	394	"(" + sheet.getRow(0).getPhysicalNumberOfCells() + "个)，匹配失败！" +
	395	"请下载最新的导入模板或检查Excel文件是否为该功能号的导入文件。");
	396	}
	397	map.put("Import", list);
	398	map.put("InitValue", value);
	399	}
	400	return map;
	401	} catch (Exception e) {
	402	throw new Exception(e);
	403	}
	404	}
	405
	406	/**
	407	* 获取Excel文件每行每列的值
	408	*
	409	* @param cell
	410	* @return
	411	*/
1417c2	412	private String getCellVlaue(Cell cell,isNotBlank blank) {
89e607	413	//判断是否为null或空串
X	414	if (cell == null \|\| cell.toString().trim().equals("")) {
	415	return "";
	416	}
1417c2	417	blank.notBlank=true;
89e607	418	String cellValue = "";
X	419	// 以下是判断数据的类型
	420	switch (cell.getCellType()) {
	421	case NUMERIC:// 数字
	422	if (DateUtil.isCellDateFormatted(cell)) {// 判断是否为日期类型
	423	Date date = cell.getDateCellValue();
	424	DateFormat formater = new SimpleDateFormat(
	425	"yyyy-MM-dd HH:mm");
	426	cellValue = formater.format(date);
	427	} else {
2be456	428	cell.setCellType(CellType.STRING);//数字格式，添加这个是为了防止出现小位数.如：156会变成156.0这样导致不一致
X	429	cellValue = cell.toString();//cell.getNumericCellValue() + "";
7ca94d	430	if (isENum(cellValue)) {//是否为科学计算法值 xin 2021-7-15 11:07:53
X	431	DecimalFormat df = new DecimalFormat("0");
	432	cellValue = df.format(cell.getNumericCellValue());
	433	}
89e607	434	}
X	435	break;
	436	case STRING: // 字符串
	437	cellValue = cell.getStringCellValue();
	438	break;
	439	case BOOLEAN: // Boolean
	440	cellValue = cell.getBooleanCellValue() + "";
	441	break;
	442	case FORMULA: // 公式
	443	try {
	444	cellValue = String.valueOf(cell.getNumericCellValue());
	445	} catch (IllegalStateException e) {
	446	cellValue = String.valueOf(cell.getRichStringCellValue());
	447	}
	448	break;
	449	case BLANK: // 空值
	450	cellValue = "";
	451	break;
	452	case ERROR: // 故障
	453	cellValue = "非法字符";
	454	break;
	455	default:
	456	cellValue = "未知类型";
	457	break;
	458	}
	459	return cellValue;
	460	}
	461
	462	/**
7ca94d	463	* 是否为科学计数法值
X	464	* @return
	465	*/
	466	private boolean isENum(String input){
	467	Pattern pattern = Pattern.compile("(-?\\d+\\.?\\d)[Ee]{1}[\\+-]?[0-9]");
	468	return pattern.matcher(input).matches();
	469	}
	470
	471	/**
89e607	472	* @param formtype 窗体类型
X	473	* @return
	474	*/
	475	public int getWinType(int formtype) {
	476	int wintype = formtype;
	477	switch (formtype) {
	478	case 5:
	479	wintype = 9;
	480	break;
	481	case 16:
	482	wintype = 17;
	483	break;
	484	case 8:
	485	wintype = 15;
	486	break;
	487	case 498:
	488	wintype = 499;
	489	break;
	490	case 496:
	491	wintype = 497;
	492	break;
	493	default:
	494	break;
	495	}
	496	return wintype;
	497	}
	498	}