package com.yc.action.excel;
|
|
import com.yc.action.BaseAction;
|
import com.yc.action.grid.GTGrid;
|
import com.yc.action.grid.Grid;
|
import com.yc.action.grid.TreeGridDTO;
|
import com.yc.action.grid.TreeGridIfc;
|
import com.yc.action.tree.Tree_2;
|
import com.yc.currentThreadInfo.CurrentLocal;
|
import com.yc.entity.attachment.AttachmentEntity;
|
import com.yc.entity.attachment.AttachmentWhereEntity;
|
import com.yc.exception.CallBackMessage;
|
import com.yc.factory.FactoryBean;
|
import com.yc.multiData.SpObserver;
|
import com.yc.service.excel.ExcelIfc;
|
import com.yc.service.impl.DBHelper;
|
import com.yc.service.panel.SqlDBHelperIfc;
|
import com.yc.service.tree.TreeJson;
|
import com.yc.service.upload.AttachmentIfc;
|
import com.yc.utils.DefaultSet;
|
import com.yc.utils.EncodeUtil;
|
import com.yc.utils.GTJson;
|
import com.yc.utils.SessionKey;
|
import lombok.Data;
|
import org.apache.commons.collections.map.HashedMap;
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Controller;
|
import org.springframework.web.bind.annotation.RequestMapping;
|
import org.springframework.web.multipart.MultipartFile;
|
import org.springframework.web.multipart.MultipartHttpServletRequest;
|
import org.springframework.web.multipart.MultipartResolver;
|
import org.springframework.web.multipart.commons.CommonsMultipartResolver;
|
|
import javax.servlet.http.HttpServletRequest;
|
import javax.servlet.http.HttpServletResponse;
|
import java.text.DateFormat;
|
import java.text.DecimalFormat;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
import java.util.regex.Pattern;
|
|
@Controller
|
public class NewExcel extends BaseAction {
|
|
@Autowired
|
ExcelIfc excelIfc;
|
@Autowired
|
TreeGridIfc treeGridIfc;
|
@Data
|
class isNotBlank {
|
private boolean notBlank;
|
}
|
/**
|
* 导入Excel数据的请求方法
|
*
|
* @param request
|
* @param response
|
*/
|
@RequestMapping("/uploadXlsFile.do")
|
public void UploadExcelFile(HttpServletRequest request, HttpServletResponse response) {
|
try {
|
MultipartResolver resolver = new CommonsMultipartResolver(request.getSession().getServletContext());
|
MultipartHttpServletRequest multipartRequest = resolver.resolveMultipart(request);
|
MultipartFile file = multipartRequest.getFile("xlsFile");//xls文件信息
|
String xlsName = file.getOriginalFilename();
|
String suffix = xlsName.substring(xlsName.lastIndexOf(".") + 1).toLowerCase();
|
Workbook workbook = null;
|
if ("xls".equals(suffix)) {
|
workbook = new HSSFWorkbook(file.getInputStream());
|
} else if ("xlsx".equals(suffix)) {
|
workbook = new XSSFWorkbook(file.getInputStream());
|
} else {
|
super.printJson(response, new CallBackMessage().sendErrorMessage("只支持xls或xlsx后缀的文件"));
|
}
|
String doccode = multipartRequest.getParameter("doccode");
|
doccode = (doccode == null ? "" : doccode);
|
int formid = Integer.parseInt(multipartRequest.getParameter("formid"));
|
int formtype = Integer.parseInt(multipartRequest.getParameter("formtype"));
|
if (workbook != null) {
|
Map<Integer, List<HashMap<String, String>>> map = new HashedMap();
|
try {
|
String dbId = (String) request.getSession().getAttribute(SessionKey.DATA_BASE_ID);
|
SpObserver.setDBtoInstance("_" + dbId);
|
List<Map<String, Object>> info = excelIfc.getInfo(formid);
|
int type = 0;//0表示设置导入的是主表1表示从表
|
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
|
Sheet sheet = workbook.getSheetAt(i);
|
if (sheet == null || sheet.getPhysicalNumberOfRows() <= 1) {//第一行是标题，大于1表示有数据
|
continue;
|
}
|
type = (i == 0 ? 1 : 0);//这里的workbook第一个sheet是明细表数据，所以当i==0表示type为1导入的是从表
|
Map<String, Object> typeMap = getFieldMap(sheet, type, info);
|
List<HashMap<String, String>> rowli = getRowData(doccode, formid, formtype, sheet, typeMap, request);
|
map.put(i, rowli);
|
}
|
} finally {
|
SpObserver.setDBtoInstance();
|
}
|
List<GTJson> gList = new ArrayList<GTJson>();
|
if (map.get(1) != null && map.get(1).size() > 0) {//主表信息
|
if (formtype == 1 || formtype == 3) {//特殊窗体的导入不能放在主表信息集合里
|
gList.add(getGTJson(map.get(1), null, doccode, formid, formtype));
|
} else if (formtype == 2) {//导入2类型窗体的处理 xin 2023-2-21 15:52:32
|
request.setAttribute("treeJson", getTreeJson(map.get(1), null, doccode, formid, formtype));
|
request.setAttribute("formid", formid + "");
|
request.setAttribute("isImport","true");//是导入进来的
|
Tree_2 tree2 = (Tree_2) FactoryBean.getBean("tree_2");
|
tree2.addNode(request, response);
|
return;
|
} else {
|
for (HashMap<String, String> m : map.get(1)) {
|
gList.add(getGTJson((map.get(0) != null ? map.get(0) : null), m, doccode, formid, formtype));
|
}
|
}
|
} else if (map.get(0) != null && map.get(0).size() > 0) {//从表信息
|
gList.add(getGTJson(map.get(0), (map.get(1) != null ? map.get(1).get(0) : null), doccode, formid, formtype));
|
}
|
String typeToGrid = getWinType(formtype) + "|" + ((getWinType(formtype) == 499 || getWinType(formtype) == 497)
|
? "0" : ((formtype == getWinType(formtype)) ? "0" : "1"));
|
//保存
|
String text = saveExcelImport(gList, formid, typeToGrid, request, response);
|
if ("".equals(text)) {
|
super.printJson(response, new CallBackMessage().sendSuccessMessage("导入成功！"));
|
} else {
|
text = "导入数据在执行保存时错误：" + text + saveExcelJournal(file, formid, formtype, doccode, text, "Fail", request);
|
super.printJson(response, new CallBackMessage().sendErrorMessage(text));
|
}
|
}
|
} catch (Exception e) {
|
super.printJson(response, new CallBackMessage().sendErrorMessage(e.getCause() != null ? e.getCause().getMessage() : e.getMessage()));
|
}
|
}
|
|
/**
|
* 保存Excel导入数据
|
*
|
* @param jsons
|
* @param request
|
* @param response
|
*/
|
public String saveExcelImport(List<GTJson> jsons, int formid, String type, HttpServletRequest request, HttpServletResponse response) throws Exception {
|
TreeGridDTO dto = new TreeGridDTO();
|
Grid grid = null;
|
try {
|
try {
|
SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
|
dto.dbid = (String) request.getSession().getAttribute(SessionKey.DATA_BASE_ID);
|
grid = treeGridIfc.getGridInfo(formid, type, true, dto);// 获得Grid
|
} finally {
|
SpObserver.setDBtoInstance();
|
}
|
grid.setImport(true);
|
request.setAttribute("_gt_json", jsons);// 赋值
|
GTGrid gt = (GTGrid) FactoryBean.getBean("GTGrid");
|
CurrentLocal.setExeclInfo("");
|
gt.saveFinal(grid, request, response);// 保存执行
|
return CurrentLocal.getExeclInfo();
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
/**
|
* 导入日志记录保存
|
*
|
* @param file
|
* @param formid
|
* @param formtype
|
* @param doccode
|
* @param error
|
* @param state
|
* @param request
|
* @return
|
*/
|
public String saveExcelJournal(MultipartFile file, int formid, int formtype, String doccode, String error, String state, HttpServletRequest request) {
|
try {
|
DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 设置显示格式
|
String unid = UUID.randomUUID().toString().toUpperCase();// 生成uuid
|
String usercode = request.getSession().getAttribute(SessionKey.USERCODE).toString();
|
String username = request.getSession().getAttribute(SessionKey.USERNAME).toString();
|
AttachmentEntity attachment = new AttachmentEntity();
|
attachment.setUnid(unid);
|
attachment.setFormId(formid);
|
attachment.setOriginalPicture(file.getBytes());//input2byte(new FileInputStream(new File(path)))
|
attachment.setOriginalFileName(file.getOriginalFilename());//map.get(ExlsParam.BEFORE_NAME.getKey()).toString()
|
attachment.setAuthorCode(usercode);
|
attachment.setAuthorName(username);
|
AttachmentIfc attachmentIfc = (AttachmentIfc) FactoryBean.getBean("AttachmentImpl");
|
SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
|
AttachmentWhereEntity whereEntity = attachmentIfc.saveAttachment(attachment, file, "0");
|
//附件唯一标示 unid
|
if (whereEntity.getUnid() != null && whereEntity.getSeq() != null) {
|
unid = whereEntity.getUnid() + ";" + whereEntity.getSeq();
|
} else {
|
unid += ";0";
|
}
|
int cont = excelIfc.excelRecord(formid, doccode, usercode, username, "In", df.format(new Date()), unid, error, formtype + "", state, "");
|
if (cont > 0) {
|
return "【导入日志已记录】";
|
} else {
|
return "";
|
}
|
} catch (Exception e) {
|
return "";
|
} finally {
|
SpObserver.setDBtoInstance();
|
}
|
}
|
|
/**
|
* 把Excel文件遍历后得到的数据内容添加到GTJson对象内
|
*
|
* @param detailed 明细数据
|
* @param panel 主表数据
|
* @return
|
*/
|
public GTJson getGTJson(List<HashMap<String, String>> detailed, HashMap<String, String> panel, String doccode, int formid, int formtype) throws Exception {
|
GTJson json = new GTJson();
|
try {
|
if (panel != null) {
|
List<HashMap<String, String>> panelRecords = new ArrayList<HashMap<String, String>>();
|
panelRecords.add(panel);
|
json.setPanelRecords(panelRecords);
|
}
|
json.setInsertedRecords(detailed);
|
if (!"".equals(doccode.trim())) {
|
json.setDoccode(EncodeUtil.base64Encode(doccode.trim()));
|
json.setPanelAction("update");// 添加是修改还插入的sql类型（add或update）。
|
} else {
|
json.setPanelAction("add");
|
}
|
json.setDisableDuplicateSubmitUUID("excel");//是导入
|
json.setFormid(formid + "");
|
json.setgType(formtype + "");
|
json.setPanelTable(formid + ";" + formtype);
|
return json;
|
} catch (Exception e) {
|
throw new Exception("导入数据信息添加到GTJson时出现错误：" + e);
|
}
|
}
|
|
public TreeJson getTreeJson(List<HashMap<String, String>> detailed, HashMap<String, String> panel, String doccode, int formid, int formtype) throws Exception {
|
TreeJson json = new TreeJson();
|
try {
|
json.setInsertedRecords(detailed);
|
List<HashMap<String,String>> list=new ArrayList<>();
|
json.setUpdatedRecords(list);
|
json.setUpdatedRecords(list);
|
json.setDeleteRecords(list);
|
json.setDragRecords(list);
|
return json;
|
} catch (Exception e) {
|
throw new Exception("导入数据信息添加到GTJson时出现错误：" + e);
|
}
|
}
|
|
/**
|
* 遍历Excel文件的每行每列
|
*
|
* @param sheet
|
* @param fieldmap
|
* @return
|
* @throws Exception
|
*/
|
public List<HashMap<String, String>> getRowData(String doccode, int formid, int formtype, Sheet sheet, Map<String, Object> fieldmap, HttpServletRequest request) throws Exception {
|
int errRow=0;
|
try {
|
List<HashMap<String, String>> list = new ArrayList<>();
|
List<String> imp = (List<String>) fieldmap.get("Import");
|
int CellNum = 0;//总列数
|
boolean isNullCell = true;//是空行
|
//获取的是物理行数，也就是不包括那些空行（隔行）的情况
|
for (int r = 0; r < sheet.getPhysicalNumberOfRows(); r++) {
|
errRow++;
|
Row row = sheet.getRow(r);//获取行的信息
|
if(row==null){
|
continue;
|
}
|
if (r == 0) {//第0行必定是标题行
|
CellNum = row.getPhysicalNumberOfCells();//0元素是标题，标题有多少列那么数据也要有多少列对应。获取不为空的列个数。
|
continue;
|
}
|
if (row.getPhysicalNumberOfCells() != CellNum) {//导入数据的行信息列和标题行不相等有可能是空行或不能识别是空行情况
|
isNullCell = true;//是空行
|
for (Cell cell : row) {
|
if (cell.getCellType() != CellType.BLANK) {//列值类型如果不是CellType.BLANK那就不是空行
|
isNullCell = false;
|
break;
|
}
|
}
|
if (isNullCell) {
|
continue;
|
}
|
}
|
Map<String, Object> map = new HashMap<>();
|
map.putAll((Map<? extends String, ?>) fieldmap.get("InitValue"));
|
isNotBlank blank=new isNotBlank(); //整行都不是空值（有一列有值就不算空行）
|
for (int c = 0; c < CellNum; c++) {
|
map.put(imp.get(c), getCellVlaue(row.getCell(c),blank));
|
}
|
if(blank.notBlank){//不是空行就进入
|
list.add(getDynamicValue(doccode, formid, formtype, map, request));//处理有会话值或动态值后添加到list内
|
}
|
}
|
return list;
|
} catch (Exception e) {
|
throw new Exception("导入数据信息在读取Excel的第"+errRow+"行时出现错误：" + e);
|
}
|
}
|
|
/**
|
* 处理数据的值有会话(@cccode)或动态(如：!select ****)的情况
|
*
|
* @param doccode
|
* @param formid
|
* @param formtype
|
* @param map
|
* @param request
|
* @return
|
* @throws Exception
|
*/
|
private HashMap<String, String> getDynamicValue(String doccode, int formid, int formtype, Map<String, Object> map, HttpServletRequest request) throws Exception {
|
try {
|
String value = "";
|
HashMap<String, String> valueMap = new HashMap<>();
|
// SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
|
SqlDBHelperIfc sqlDBHelperIfc = (SqlDBHelperIfc) FactoryBean.getBean("SqlDBHelper");
|
for (String k : map.keySet()) {
|
value = (String) map.get(k);
|
if (value.indexOf("@") != -1) {
|
value = DBHelper.getValRepShi(formid, value, request.getSession(), null, false);
|
}
|
if (value.indexOf("!") != -1) {
|
List<Map<String, Object>> list = sqlDBHelperIfc.getHashMapObj(value.replaceAll("!", ""));
|
if (list.size() > 0) {
|
for (String key : list.get(0).keySet()) {
|
value = (String) list.get(0).get(key);//取值
|
}
|
} else {
|
value = "";
|
}
|
}
|
if(k.toLowerCase().equals("formid") && "".equals(value)){
|
value=formid+"";
|
}
|
if (k.toLowerCase().equals("doccode") && !"".equals(doccode)) {
|
value = doccode;
|
}
|
String v = DefaultSet.getDefaultValue(formid + "-" + formtype + "-" + k, request.getSession());
|
if (v != null && !"".equals(v.trim())) {
|
value = v;
|
}
|
valueMap.put(k.trim().toLowerCase(), value);//把导入字段去空格和转成小写，然后重新赋值
|
}
|
return valueMap;
|
} catch (Exception e) {
|
throw new Exception("会话值处理或动态值处理错误：" + e.getCause()!=null?e.getCause().getMessage():e.getMessage());
|
} finally {
|
// SpObserver.setDBtoInstance();
|
}
|
}
|
|
/**
|
* 获取系统导入设置的信息和初始值
|
*
|
* @param sheet
|
* @param type
|
* @param info
|
* @return
|
* @throws Exception
|
*/
|
public Map<String, Object> getFieldMap(Sheet sheet, int type, List<Map<String, Object>> info) throws Exception {
|
try {
|
Map<String, Object> map = new HashedMap();
|
if (info != null && info.size() > 0) {
|
List<String> list = new ArrayList<>();
|
Map<String, Object> value = new HashedMap();
|
for (int i = 0; i < info.size(); i++) {
|
if (DBHelper.getValueInt(info.get(i), "headflag") == type) {
|
//设置了需要导入信息的字段
|
if (DBHelper.getValueInt(info.get(i), "isImport") == 1) {
|
list.add(DBHelper.getValue(info.get(i), "fieldid"));
|
}
|
//所以对于type的字段初始值
|
value.put(DBHelper.getValue(info.get(i), "fieldid"), DBHelper.getValue(info.get(i), "InitValue"));
|
}
|
}
|
if (sheet.getRow(0).getPhysicalNumberOfCells() != list.size()) {
|
throw new Exception("此功能界面设置导入的" + (type == 1 ? "明细数据" : "主数据") + "字段数(" + list.size() + "个)，" +
|
"Excel文件内的" + (type == 1 ? "明细数据" : "主数据") + "标题列数" +
|
"(" + sheet.getRow(0).getPhysicalNumberOfCells() + "个)，匹配失败！" +
|
"请下载最新的导入模板或检查Excel文件是否为该功能号的导入文件。");
|
}
|
map.put("Import", list);
|
map.put("InitValue", value);
|
}
|
return map;
|
} catch (Exception e) {
|
throw new Exception(e);
|
}
|
}
|
|
/**
|
* 获取Excel文件每行每列的值
|
*
|
* @param cell
|
* @return
|
*/
|
private String getCellVlaue(Cell cell,isNotBlank blank) {
|
//判断是否为null或空串
|
if (cell == null || cell.toString().trim().equals("")) {
|
return "";
|
}
|
blank.notBlank=true;
|
String cellValue = "";
|
// 以下是判断数据的类型
|
switch (cell.getCellType()) {
|
case NUMERIC:// 数字
|
if (DateUtil.isCellDateFormatted(cell)) {// 判断是否为日期类型
|
Date date = cell.getDateCellValue();
|
DateFormat formater = new SimpleDateFormat(
|
"yyyy-MM-dd HH:mm");
|
cellValue = formater.format(date);
|
} else {
|
cell.setCellType(CellType.STRING);//数字格式，添加这个是为了防止出现小位数.如：156会变成156.0这样导致不一致
|
cellValue = cell.toString();//cell.getNumericCellValue() + "";
|
if (isENum(cellValue)) {//是否为科学计算法值 xin 2021-7-15 11:07:53
|
DecimalFormat df = new DecimalFormat("0");
|
cellValue = df.format(cell.getNumericCellValue());
|
}
|
}
|
break;
|
case STRING: // 字符串
|
cellValue = cell.getStringCellValue();
|
break;
|
case BOOLEAN: // Boolean
|
cellValue = cell.getBooleanCellValue() + "";
|
break;
|
case FORMULA: // 公式
|
try {
|
cellValue = String.valueOf(cell.getNumericCellValue());
|
} catch (IllegalStateException e) {
|
cellValue = String.valueOf(cell.getRichStringCellValue());
|
}
|
break;
|
case BLANK: // 空值
|
cellValue = "";
|
break;
|
case ERROR: // 故障
|
cellValue = "非法字符";
|
break;
|
default:
|
cellValue = "未知类型";
|
break;
|
}
|
return cellValue;
|
}
|
|
/**
|
* 是否为科学计数法值
|
* @return
|
*/
|
private boolean isENum(String input){
|
Pattern pattern = Pattern.compile("(-?\\d+\\.?\\d*)[Ee]{1}[\\+-]?[0-9]*");
|
return pattern.matcher(input).matches();
|
}
|
|
/**
|
* @param formtype 窗体类型
|
* @return
|
*/
|
public int getWinType(int formtype) {
|
int wintype = formtype;
|
switch (formtype) {
|
case 5:
|
wintype = 9;
|
break;
|
case 16:
|
wintype = 17;
|
break;
|
case 8:
|
wintype = 15;
|
break;
|
case 498:
|
wintype = 499;
|
break;
|
case 496:
|
wintype = 497;
|
break;
|
default:
|
break;
|
}
|
return wintype;
|
}
|
}
|
