eCoWorksV3.git - Gitblit

fs-danaus

2023-06-16 b2156e90e361afd9746eb9c491cb2f235c65de98

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