package cn.autoform.excel.impl;
|
|
import cn.autoform.bean.ExcelLog;
|
import cn.autoform.db.entity.FormBaseEntity;
|
import cn.autoform.db.entity.FormFieldEntity;
|
import cn.autoform.db.exten.AllFormData;
|
import cn.autoform.db.exten.ServicesFormData;
|
import cn.autoform.factory.FormFactory;
|
import cn.autoform.fw.exception.RowOverLengthException;
|
import cn.autoform.util.form.entity.FormDataParam;
|
import cn.autoform.util.form.paramap.FormDataEditMap;
|
import cn.autoform.util.thread.Keys;
|
import cn.autoform.util.thread.ThreadData;
|
import cn.autoform.util.tool.JSONTool;
|
import cn.autoform.web.client.FormClient;
|
import cn.autoform.web.interceptor.spring.InitParamInterceptor;
|
import cn.autoform.web.service.datamanagement.DataManagementService;
|
import cn.autoform.web.service.formbase.FormBaseService;
|
import jxl.Sheet;
|
import jxl.Workbook;
|
import jxl.read.biff.BiffException;
|
import jxl.write.WritableFont;
|
import jxl.write.WritableSheet;
|
import jxl.write.WritableWorkbook;
|
import lombok.Data;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.context.annotation.Scope;
|
import org.springframework.stereotype.Component;
|
import org.springframework.web.multipart.MultipartFile;
|
|
import javax.annotation.Resource;
|
import javax.servlet.http.HttpServletRequest;
|
import javax.servlet.http.HttpServletResponse;
|
import java.beans.Transient;
|
import java.io.IOException;
|
import java.io.OutputStream;
|
import java.net.URLEncoder;
|
import java.util.*;
|
import java.util.concurrent.TimeoutException;
|
|
@Component
|
@Scope("prototype")
|
public class ImpExcelThread{
|
// implements Runnable
|
private Map<String,Object> cookie = new HashMap<>();
|
|
private static final String TAGATTRIBUTE = "tag_Attribute";
|
|
|
@Autowired
|
private ExcelServiceImpl excelService;
|
|
@Autowired
|
private ExcelImpl excel;
|
|
|
@Autowired
|
private DataManagementService dataManagementService = null;
|
|
@Resource
|
private FormDataEditMap formDataEditMap;
|
|
@Autowired
|
private FormClient formClient;
|
|
@Autowired
|
private FormFactory formFactory;
|
|
@Autowired
|
private FormBaseService formBaseService = null;
|
|
/**
|
* 表格主页面
|
*/
|
private static final String MAIN = "main";
|
|
/**
|
* 主数据缓存
|
*/
|
|
/**
|
* 当前所有子表单别名
|
*/
|
Map<String,String> subs = new HashMap<>();
|
|
/**
|
*
|
*/
|
|
|
|
//判断是导入还是导出,true 为导入，false 为导出
|
private Boolean flag;
|
|
private String formId;
|
|
private String tenantID;
|
|
private String userName;
|
|
private MultipartFile file;
|
|
private HttpServletRequest request;
|
private HttpServletResponse response;
|
|
// @Override
|
public void run(){
|
//添加cookie
|
System.out.println("-->添加cookie");
|
this.cookie.keySet().stream().forEach(o->ThreadData.set(InitParamInterceptor.COOKIE_KEY.get(o),this.cookie.get(o)));
|
System.out.println("进入线程--->");
|
imExcel();
|
}
|
|
/**
|
* 导入
|
*/
|
@Transient
|
public void imExcel() {
|
System.out.println("当前公司id为"+ThreadData.get(Keys.COMPANY_ID));
|
System.out.println("开始导入");
|
//添加操作log
|
Integer id = null;
|
Workbook workbook = null;
|
try {
|
id = this.addLog();
|
System.out.println("添加id为:"+id);
|
//最终添加字段
|
List<FormDataParam> formList = new ArrayList<>();
|
//查询表单字段,比对格式用
|
List<FormFieldEntity> rsList = getFormFields();
|
//控件别名对应属性
|
Map<String,FieldInfo> aliasing = getFieldInfo(JSONTool.toObj(rsList.get(0).getFieldset(),Map.class));
|
|
|
//创建关联工作簿
|
workbook = Workbook.getWorkbook(file.getInputStream());
|
//取得主表页面
|
Sheet sheet = workbook.getSheet(MAIN);
|
//循环主表格行
|
System.out.println("当前行列"+sheet.getRows()+"--"+sheet.getColumns());
|
for(int c=1; c< sheet.getRows(); c++){
|
FormDataParam formDataParam = new FormDataParam(this.formId,this.tenantID,this.userName);
|
Map<String,Object> data = new HashMap<>();
|
formDataParam.setData(data);
|
//获取标题
|
FieldInfo f = null;
|
for(int r=0; r<sheet.getColumns(); r++){
|
String title = sheet.getCell(r, 0).getContents();
|
System.out.println("当前行:"+title);
|
f = aliasing.get(title);
|
f.setNum(Objects.toString(c));
|
//当前添加主表数据
|
String d = sheet.getCell(r, c).getContents();
|
String val = getValue(d,f,rsList);
|
data.put(f.getAlias(),val);
|
}
|
//添加当前行子表数据
|
for(String p:subs.keySet()){
|
try {
|
data.put(subs.get(p),getSubList(p,workbook,f,aliasing,rsList));
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
formList.add(formDataParam);
|
}
|
System.out.print("最终导入数据:---->"+ JSONTool.toJson(formList));
|
List<ServicesFormData> dataList = new ArrayList<>();
|
formList.stream().forEach(j->dataList.add(new AllFormData(formDataEditMap.apply(j)).getFormData()));
|
//处理数据，转换为费用云可用数据
|
System.out.println("处理后数据:"+ JSONTool.toJson(dataList));
|
Integer res = formClient.impDatas(dataList);
|
if(res==1) {
|
updateAddLog(id, 2, "操作成功");
|
}else{
|
updateAddLog(id,3,"业务系统添加数据异常");
|
}
|
} catch (IOException e) {
|
System.out.print("导入失败");
|
updateAddLog(id,3,e.getMessage());
|
e.printStackTrace();
|
} catch (BiffException e) {
|
System.out.print("导入失败");
|
updateAddLog(id,3,e.getMessage());
|
e.printStackTrace();
|
} catch (Exception e) {
|
System.out.print("导入失败");
|
updateAddLog(id,3,e.getMessage());
|
e.printStackTrace();
|
}finally {
|
if(workbook != null) {
|
workbook.close();
|
}
|
}
|
|
|
}
|
|
|
/**
|
* 获取所有表单字段
|
* @return
|
*/
|
public List<FormFieldEntity> getFormFields(){
|
return dataManagementService.queryFormFields(formId, tenantID);
|
}
|
|
/**
|
* 添加操作纪录
|
* @param
|
*/
|
public Integer addLog() throws TimeoutException {
|
ExcelLog ex = new ExcelLog(formId,tenantID);
|
ex.setFormName(file.getOriginalFilename());
|
ex.setType(this.flag?"10":"20");
|
ex.setTenantId(ThreadData.get(Keys.TENANT_ID));
|
return formClient.addExcelLog(ex);
|
}
|
|
/**
|
* 取出子表单数据
|
*/
|
public List<Map<String,Object>> getSubList(String sheetPage,Workbook workbook,FieldInfo f,Map<String,FieldInfo> aliasing,List<FormFieldEntity> rsList) throws Exception {
|
List<Map<String,Object>> subs = new ArrayList<>();
|
System.out.print("当前子表单页:"+sheetPage);
|
//获取子表单页
|
Sheet sheet = workbook.getSheet(sheetPage);
|
for(int c=1; c< sheet.getRows(); c++){
|
//获取编号标题
|
String d = sheet.getCell(0, c).getContents();
|
Map<String,Object> data = new HashMap<>();
|
for (int r = 1; r < sheet.getColumns(); r++) {
|
if(Objects.equals(d,f.getNum())) {
|
String title = sheet.getCell(r, 0).getContents();
|
System.out.print("当前子表单行名称:"+title);
|
FieldInfo fc = aliasing.get(title);
|
//当前添加主表数据
|
String val = getValue(sheet.getCell(r, c).getContents(),fc,rsList);
|
data.put(fc.getAlias(),val);
|
}else{
|
break;
|
}
|
}
|
subs.add(data);
|
}
|
return subs;
|
}
|
|
/**
|
* 修改log状态
|
* @param id
|
* @param state
|
*/
|
public void updateAddLog(Integer id,Integer state,String msg) {
|
ExcelLog ex = new ExcelLog();
|
ex.setId(id);
|
ex.setState(state);
|
ex.setOverTime(new Date());
|
ex.setMag(msg);
|
try {
|
formClient.updateExcelLog(ex);
|
} catch (TimeoutException e) {
|
e.printStackTrace();
|
}
|
}
|
|
/**
|
* 获取控件属性
|
* @param
|
*/
|
public Map<String,FieldInfo> getFieldInfo(Map<String,Object> formList){
|
Map<String,FieldInfo> m = new HashMap<>();
|
formList.keySet().stream().forEach(o->{
|
FieldInfo fi = new FieldInfo();
|
Map<String,Object> item = JSONTool.toObj(JSONTool.toJson(formList.get(o)),Map.class);
|
fi.setTag_Type(Objects.toString(item.get("tag_Type")));
|
fi.setColumnOrderNum(Integer.parseInt(Objects.toString(item.get("columnOrderNum"),"0")));
|
fi.setParentsubFormNum(Integer.parseInt(Objects.toString(item.get("parentsubFormNum"),"0")));
|
Map<String,Object> attr = JSONTool.toObj(JSONTool.toJson(item.get(TAGATTRIBUTE)),Map.class);
|
fi.setAlias(Objects.toString(attr.get("alias")));
|
fi.setTitle(Objects.toString(attr.get("title")));
|
fi.setMustcheck(Boolean.parseBoolean(Objects.toString(attr.get("mustcheck"),"false")));
|
m.put(Objects.toString(attr.get("title")),fi);
|
if(Objects.equals(Objects.toString(item.get("tag_Type")),"subform")){
|
this.subs.put(Objects.toString(attr.get("title")),Objects.toString(attr.get("alias")));
|
}
|
});
|
System.out.println("最终表单属性:"+ JSONTool.toJson(m));
|
System.out.println("所有子表单:"+ JSONTool.toJson(this.subs));
|
return m;
|
}
|
|
/**
|
* 控件校验
|
*/
|
public String getValue(String value,FieldInfo fieldInfo,List<FormFieldEntity> rsList) throws Exception {
|
//首先校验必填
|
if(fieldInfo.getMustcheck()){
|
if(value ==null || Objects.equals("",value)){
|
throw new RowOverLengthException(fieldInfo.getTitle()+"列-值必填");
|
}
|
}
|
//按控件类型校验
|
if(fieldInfo.getTag_Type().equals("number")){
|
if(!excel.isNumeric(value)){
|
throw new RowOverLengthException(fieldInfo.getTitle()+"列-值为:"+value);
|
}else{
|
return value;
|
}
|
}else if(fieldInfo.getTag_Type().equals("calendar")){
|
if(!excel.isValidDate(value)){
|
throw new RowOverLengthException(fieldInfo.getTitle()+"列-值为:"+value);
|
}else{
|
return "20"+value;
|
}
|
//获取excel的下拉列表和树型的值
|
}else if(fieldInfo.getTag_Type().equals("dropdownlist") || fieldInfo.getTag_Type().equals("tree")){
|
//调用获取Code 的方法
|
String val = excel.getCode(rsList,value,fieldInfo.getColumnOrderNum(),fieldInfo.getTag_Type(),this.tenantID);
|
if(val!=null && !Objects.equals(val,"")){
|
return val;
|
}else{
|
throw new RowOverLengthException(fieldInfo.getTitle()+"列-值为:"+value+"->主数据缺失！");
|
}
|
}else if(fieldInfo.getTag_Type().equals("auto")){
|
return "自动生成";
|
}else{
|
return value;
|
}
|
}
|
|
|
@Data
|
class FieldInfo{
|
|
//是否必填
|
private Boolean mustcheck;
|
//控件类型
|
private String tag_Type;
|
//控件别名
|
private String alias;
|
//数据源类型
|
private String datasourcetype = null;
|
//控件顺序序号
|
private Integer columnOrderNum;
|
//是否是子表单0为子表单，不为0则对应子表单顺序号
|
private Integer parentsubFormNum;
|
//标题
|
private String title;
|
//子表单关联id
|
private String num;
|
}
|
|
public void setFlag(Boolean flag) {
|
this.flag = flag;
|
}
|
|
public String getFormId() {
|
return formId;
|
}
|
|
public void setFormId(String formId) {
|
this.formId = formId;
|
}
|
|
public String getTenantID() {
|
return tenantID;
|
}
|
|
public void setTenantID(String tenantID) {
|
this.tenantID = tenantID;
|
}
|
|
public String getUserName() {
|
return userName;
|
}
|
|
public void setUserName(String userName) {
|
this.userName = userName;
|
}
|
|
public MultipartFile getFile() {
|
return file;
|
}
|
|
public void setFile(MultipartFile file) {
|
this.file = file;
|
}
|
|
public HttpServletRequest getRequest() {
|
return request;
|
}
|
|
public void setRequest(HttpServletRequest request) {
|
this.request = request;
|
}
|
|
public HttpServletResponse getResponse() {
|
return response;
|
}
|
|
public void setResponse(HttpServletResponse response) {
|
this.response = response;
|
}
|
|
|
public Map<String, Object> getCookie() {
|
return cookie;
|
}
|
|
public void setCookie(Map<String, Object> cookie) {
|
this.cookie = cookie;
|
}
|
|
|
|
}
|
