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.currentThreadInfo.CurrentLocal;
|
import com.yc.entity.XlsImportParameterEntity;
|
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.sdk.weixincp.util.UploadFiles;
|
import com.yc.service.BaseService;
|
import com.yc.service.commons.SqlQueryIfc;
|
import com.yc.service.dao.c_sys_cr_pageDao;
|
import com.yc.service.excel.ExcelIfc;
|
import com.yc.service.impl.DBHelper;
|
import com.yc.service.impl.EnvHelper;
|
import com.yc.service.upload.AttachmentIfc;
|
import com.yc.utils.GTJson;
|
import com.yc.utils.ImageUtils;
|
import com.yc.utils.SessionKey;
|
import com.yc.ycenum.ExlsParam;
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
|
import org.apache.poi.ss.usermodel.Row;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.ss.usermodel.Workbook;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.json.JSONException;
|
import org.json.JSONObject;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.dao.DataAccessException;
|
import org.springframework.stereotype.Controller;
|
import org.springframework.web.bind.annotation.RequestMapping;
|
|
import javax.servlet.http.HttpServletRequest;
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.*;
|
import java.text.DateFormat;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
|
@Controller("com.yc.action.excel.Excel")
|
@RequestMapping("/excel.do")
|
public class Excel extends BaseAction {
|
@Autowired
|
ExcelIfc excelIfc;
|
@Autowired
|
c_sys_cr_pageDao cParent;
|
@Autowired
|
SqlQueryIfc sqlQueryIfc;
|
@Autowired
|
TreeGridIfc treeGridIfc;
|
|
public String sqlext = "";
|
|
public String getSqlext() {
|
return sqlext;
|
}
|
|
public void setSqlext(String sqlext) {
|
this.sqlext = sqlext;
|
}
|
|
/**
|
* 数据范围检查后按确定
|
*/
|
private static final String OK = "ok";
|
/**
|
* 数据范围检查后按取消
|
*/
|
private static final String QU_XIAO = "quexiao";
|
|
@RequestMapping(params = "e=down")
|
public void excelDown(int formId, HttpServletRequest request, HttpServletResponse response) throws IOException {
|
String path = EnvHelper.getPath() + File.separator + "excel" + File.separator + formId + ".xls";
|
OutputStream out = response.getOutputStream();
|
InputStream inputStream = null;
|
BufferedInputStream br = null;
|
try {
|
cParent.createFolderAndFile(path);
|
com.yc.excel.Excel excel_manage = new com.yc.excel.Excel();
|
List<Map<String, Object>> list = sqlQueryIfc
|
.queryForList("select menuname,formtype from _sysmenu where formid=?", formId);
|
StringBuffer nameBuffer = new StringBuffer();
|
if (list != null && list.size() > 0) {
|
Map<String, Object> gformInfo = list.get(0);
|
nameBuffer.append(gformInfo.get("menuname")).append("--").append(formId).append("_")
|
.append(gformInfo.get("formtype"));
|
} else {
|
nameBuffer.append(formId);
|
}
|
excel_manage.writeXls(excelIfc.getImportInfo(formId), path, null, null);
|
inputStream = new FileInputStream(new File(path));
|
response.reset();
|
response.setContentType("application/vnd.ms-excel");
|
response.setHeader("Content-Disposition", "attachment; filename="
|
+ new String(nameBuffer.toString().getBytes("gb2312"), "ISO_8859_1") + ".xls");
|
response.setContentLength(inputStream.available());
|
br = new BufferedInputStream(inputStream);
|
byte[] buf = new byte[1024];
|
int len = 0;
|
while ((len = br.read(buf)) > 0) {
|
out.write(buf, 0, len);
|
}
|
} catch (Exception e) {
|
|
} finally {
|
out.flush();
|
br.close();
|
out.close();
|
inputStream.close();
|
cParent.delete(path);
|
}
|
}
|
|
@RequestMapping(params = "m=import")
|
public void importFromExcelFile(HttpServletRequest request, HttpServletResponse response) throws IOException {
|
Map<String, Object> map = new HashMap<String, Object>();
|
// 从页面返回时的参数与map的key是一样的，因为在下面将map转换出去的
|
map.put(ExlsParam.BEFORE_NAME.getKey(), request.getParameter(ExlsParam.BEFORE_NAME.getKey()));
|
map.put(ExlsParam.END_NAME.getKey(), request.getParameter(ExlsParam.END_NAME.getKey()));
|
map.put(ExlsParam.FORM_ID.getKey(), request.getParameter(ExlsParam.FORM_ID.getKey()));
|
map.put(ExlsParam.FORM_TYPE.getKey(), request.getParameter(ExlsParam.FORM_TYPE.getKey()));
|
// map.put(ExlsParam.EXCEL_TITLE.getKey(),
|
// (request.getParameter(ExlsParam.EXCEL_TITLE.getKey()) != null) ?
|
// request.getParameter(ExlsParam.EXCEL_TITLE.getKey()) : 0);
|
map.put(OK, request.getParameter("ok"));// 已经做了检查
|
map.put(QU_XIAO, request.getParameter("quexiao"));// 已经做了检查
|
map.put(ExlsParam.PARENT_ID.getKey(), request.getParameter(ExlsParam.PARENT_ID.getKey()));
|
map.put(ExlsParam.TITLE_ROWS.getKey(), request.getParameter(ExlsParam.TITLE_ROWS.getKey()));
|
map.put(ExlsParam.ROW_NOT_INFO.getKey(), request.getParameter(ExlsParam.ROW_NOT_INFO.getKey()));
|
map.put(ExlsParam.COL_NO.getKey(), request.getParameter(ExlsParam.COL_NO.getKey()));
|
map.put(ExlsParam.ONLY_INFO.getKey(), request.getParameter(ExlsParam.ONLY_INFO.getKey()));
|
map.put(ExlsParam.DETIAL_TABLE.getKey(), request.getParameter(ExlsParam.DETIAL_TABLE.getKey()));
|
map.put(ExlsParam.DOCCODE.getKey(), request.getParameter(ExlsParam.DOCCODE.getKey()));
|
// importExcel(request, response, map,"","");
|
}
|
|
/**
|
* 这个方法是xls导入数据的最新方法调用 xin 2021-1-5 10:11:47
|
* @param request
|
* @param response
|
* @throws IOException
|
*/
|
@RequestMapping(params = "m=xlsdata")
|
public void XlsDataImport(XlsImportParameterEntity xlsEntity,HttpServletRequest request, HttpServletResponse response) throws IOException {
|
try {
|
//大改版 2019-10-30 16:23:21
|
if (xlsEntity.getState() == 0) {// 0表示映射Excel表
|
//映射方法
|
JSONObject json=excelMapping(xlsEntity,request);
|
super.printJson(response, json.toString());
|
return;
|
}
|
//上传后的文件名称
|
xlsEntity.setEndName(xlsEntity.getFile1().getOriginalFilename());
|
excelImport(xlsEntity,request,response);
|
return;
|
} catch (Exception e) {
|
super.printJson(response, new CallBackMessage().sendErrorMessage(e.getMessage()));
|
}
|
}
|
|
/**
|
* 直接保存
|
*
|
* @param request
|
* @param response
|
* @param formid
|
* @param type
|
* @param gJsonList
|
* @throws UnsupportedEncodingException
|
* @throws DataAccessException
|
* @throws JSONException
|
*/
|
@SuppressWarnings("unused")
|
public void saveTo(HttpServletRequest request, HttpServletResponse response, int formid, String type,
|
List<GTJson> gJsonList) throws Exception {
|
TreeGridDTO dto = new TreeGridDTO();
|
Grid grid = null;
|
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
|
grid.setImport(true);
|
if (request == null) {
|
grid.setCallback(true);
|
grid.setTaobaoData(gJsonList);
|
} else {
|
request.setAttribute("_gt_json", gJsonList);// 赋值
|
GTGrid gt = (GTGrid) FactoryBean.getBean("GTGrid");
|
gt.saveFinal(grid, request, response);// 保存执行
|
}
|
} catch (Exception e) {
|
throw e;
|
} finally {
|
SpObserver.setDBtoInstance();
|
}
|
}
|
|
/**
|
*
|
* @param formtype
|
* 窗体类型
|
* @return
|
*/
|
private 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;
|
}
|
|
/**
|
* 映射导入字段页面
|
*
|
* @param request
|
* @return
|
* @throws Exception
|
*/
|
public JSONObject excelMapping(XlsImportParameterEntity xlsEntity,HttpServletRequest request)
|
throws Exception {
|
// 获取最后一个.的位置
|
String xlsName=xlsEntity.getFile1().getOriginalFilename();
|
String houzhui = xlsName.substring(xlsName.lastIndexOf(".")).toLowerCase();
|
Workbook workbook = null;// Excel文件
|
Sheet sheet = null;// Excel里的sheet
|
Row row = null;// 行
|
try {
|
if (houzhui.equals(".xls")) {
|
workbook = new HSSFWorkbook(xlsEntity.getFile1().getInputStream());
|
} else if (houzhui.equals(".xlsx")) {
|
workbook = new XSSFWorkbook(xlsEntity.getFile1().getInputStream());
|
} else {
|
throw new Exception("只支持xls或xlsx后缀的文件");
|
}
|
// 获取系统设置的导入字段信息
|
SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
|
ExcelIfc Ifc = (ExcelIfc) FactoryBean.getBean("com.yc.service.excel.ExcelImpl");
|
List<Map<String, Object>> list = Ifc.getImportInfo(xlsEntity.getFormid());
|
Map<Integer, List<Map<String, Object>>> ltMap = getSetupArray(list);
|
int excelNum = workbook.getNumberOfSheets();// Excel的sheet数
|
Map<String, Object> map = new HashMap<>();
|
Map<String, Object> mapInfo = new HashMap<>();
|
for (int i = 0; i < excelNum; i++) {// 遍历每一个sheet
|
sheet = workbook.getSheetAt(i);// 获取每一个sheet的信息
|
if (sheet.getLastRowNum() == -1) {// 是否是空行
|
continue;
|
}
|
row = sheet.getRow(0);// 获取Excel表每一个sheet的第一行标题内容（只取第一行内容）
|
if (row.getLastCellNum() == -1) {// 是否是空列
|
continue;
|
}
|
|
if (i == 0) {// 第一页固定为明细表数据
|
if (xlsEntity.getIsDetailed() == 1) {
|
map.put("xls1", getExcelJson(row));
|
} else {
|
switch (xlsEntity.getFormtype()) {
|
case 1:
|
case 3:
|
if (excelNum == 2) {
|
i++;
|
sheet = workbook.getSheetAt(i);
|
row = sheet.getRow(0);
|
map.put("xls1", getExcelJson(row));
|
}
|
break;
|
default:
|
break;
|
}
|
}
|
mapInfo.put("xls1-g", ltMap.get(0));
|
} else if (i == 1) {// 第二页固定为主表数据
|
map.put("xls2", getExcelJson(row));
|
mapInfo.put("xls2-g", ltMap.get(1));
|
} else {
|
|
}
|
}
|
Map<String, Object> resMap = new HashMap<>();
|
resMap.put("state", 1);
|
resMap.put("xls", map);
|
resMap.put("gform", mapInfo);
|
// Map 转成 JSONObject 返回
|
return new JSONObject(resMap);
|
} catch (Exception e) {
|
throw e;//向上抛出错误
|
} finally {
|
workbook.close();
|
SpObserver.setDBtoInstance();
|
}
|
}
|
/**
|
* 组装系统导入里的字段
|
*
|
*/
|
public Map<String, Object> getExcelJson(Row row) {
|
Map<String, Object> excel=new HashMap<>();
|
try {
|
//遍历第一行的每一个列数信息
|
for (int i = 0; i < row.getLastCellNum(); i++) {
|
try {
|
//每一个列的内容
|
excel.put("-"+i, row.getCell(i));
|
} catch (ArrayIndexOutOfBoundsException e) {
|
continue;
|
}
|
}
|
return excel;
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
/**
|
* 组装系统导入设置的字段
|
*
|
*/
|
public Map<Integer, List<Map<String, Object>>> getSetupArray(List<Map<String, Object>> list) {
|
List<Map<String, Object>> l1 = new ArrayList<>();
|
List<Map<String, Object>> l2 = new ArrayList<>();
|
try {
|
Map<String, Object> c=new HashMap<>();
|
c.put("id", " ");
|
c.put("text", " ");
|
l1.add(c);
|
l2.add(c);
|
for (Map<String, Object> map : list) {
|
Map<String, Object> m=new HashMap<>();
|
if (!DBHelper.getValue(map, "FieldID").equals("rowid")) {
|
m.put("id", DBHelper.getValue(map, "FieldID"));
|
m.put("text", DBHelper.getValue(map, "fieldname"));
|
if (DBHelper.getValueInt(map, "HeadFlag") == 1) {// 明细数据
|
l1.add(m);
|
}else if (DBHelper.getValueInt(map, "HeadFlag") == 0) {// 主数据
|
l2.add(m);
|
}
|
}
|
}
|
Map<Integer, List<Map<String, Object>>> sMap=new HashMap<>();
|
sMap.put(0, l1);
|
sMap.put(1, l2);
|
return sMap;
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
/**
|
* 执行导入组装保存
|
* @param request
|
* @param response
|
* @return
|
* @throws Exception
|
*/
|
public void excelImport(XlsImportParameterEntity xlsenEntity,HttpServletRequest request,HttpServletResponse response) throws Exception {
|
String information="";//返回信息
|
String dbId = (String) request.getSession().getAttribute(SessionKey.DATA_BASE_ID);
|
String cltCode = (String) request.getSession().getAttribute((SessionKey.USERCODE));
|
try {
|
|
// InputStream iStream = new FileInputStream(path); // 加载excel文件
|
com.yc.excel.Excel excel = new com.yc.excel.Excel();
|
// int formid = Integer.parseInt(map.get(ExlsParam.FORM_ID.getKey()).toString());
|
// int type = Integer.parseInt(map.get(ExlsParam.FORM_TYPE.getKey()).toString());
|
xlsenEntity.setTypeDuiyin(getWinType(xlsenEntity.getFormtype()));
|
// add map 对应参数
|
// map.put(ExlsParam.TYPE_DUIYING.getKey(), typeDuiyin);
|
List<GTJson> gList = null;
|
|
try {
|
SpObserver.setDBtoInstance("_" + dbId);
|
// 获得设置信息 根据主从表加序号排序
|
List<Map<String, Object>> sort = excelIfc.getInfo(xlsenEntity.getFormid());
|
// 配置
|
gList = excel.getExcelJson(request, sort, xlsenEntity);
|
} catch (Exception e) {
|
throw e;
|
} finally {
|
SpObserver.setDBtoInstance();
|
}
|
String typeToGrid = xlsenEntity.getTypeDuiyin() + "|" + ((xlsenEntity.getTypeDuiyin() == 499 || xlsenEntity.getTypeDuiyin() == 497) ? "0" : ((xlsenEntity.getFormtype() == xlsenEntity.getTypeDuiyin()) ? "0" : "1"));
|
// if (map.get(OK) != null) {// 已经检查 确定操作
|
// //cParent.delete(path);
|
// saveTo(request, response, formid, typeToGrid, gList);
|
// String temp = CurrentLocal.getExeclInfo();
|
// throw new Exception(temp);
|
// } else {// 做检查处理
|
// String hasGrid = "0";
|
switch (xlsenEntity.getFormtype()) {
|
case 16:
|
// hasGrid = "0";
|
// type = 17;
|
xlsenEntity.setFormtype(17);
|
break;
|
case 498:
|
case 496:
|
// type = 17;
|
// hasGrid = "many";
|
xlsenEntity.setFormtype(17);
|
break;
|
default:
|
// hasGrid = "1";
|
break;
|
}
|
saveTo(request, response, xlsenEntity.getFormid(), typeToGrid, gList);// 保存
|
|
String resultAll = CurrentLocal.getExeclInfo();// 在保存时候出现的错误信息
|
String ActionStatus="";//导入状态
|
if (!resultAll.equals("")) {// 是否有错误信息，有则页面提示错误信息内容，没有说明保存成功。
|
information="【导入失败】:"+resultAll;
|
ActionStatus="Fail";
|
}else{
|
information="【导入成功】";
|
ActionStatus="Success";
|
}
|
|
// 导入的数据信息
|
// 导入的标识 In
|
// 导入数据审计记录
|
try {
|
DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 设置显示格式
|
String unid = UUID.randomUUID().toString().toUpperCase();// 生成uuid
|
AttachmentEntity attachment = new AttachmentEntity();
|
attachment.setUnid(unid);
|
attachment.setFormId(xlsenEntity.getFormid());
|
attachment.setOriginalPicture(xlsenEntity.getFile1().getBytes());//input2byte(new FileInputStream(new File(path)))
|
attachment.setOriginalFileName(xlsenEntity.getFile1().getOriginalFilename());//map.get(ExlsParam.BEFORE_NAME.getKey()).toString()
|
attachment.setAuthorCode(cltCode);
|
attachment.setAuthorName((String) request.getSession().getAttribute((SessionKey.USERNAME)));
|
|
// 保存导入导出的文件信息
|
SpObserver.setDBtoInstance("_" + dbId);
|
AttachmentIfc attachmentIfc = (AttachmentIfc) FactoryBean.getBean("AttachmentImpl");
|
// 保存导入的数据到指定数据库里面并且返回一个对象
|
AttachmentWhereEntity whereEntity=attachmentIfc.saveAttachment(attachment, xlsenEntity.getFile1(),"0");
|
//附件唯一标示 unid
|
if(whereEntity.getUnid()!=null && whereEntity.getSeq()!=null){
|
unid=whereEntity.getUnid()+";"+whereEntity.getSeq();
|
}
|
// 数据导入导出审计记录
|
int cont = excelIfc.excelRecord(xlsenEntity.getFormid(), xlsenEntity.getDoccode(),
|
request.getSession().getAttribute(SessionKey.USERCODE).toString(),
|
request.getSession().getAttribute(SessionKey.USERNAME).toString(), "In",
|
df.format(new Date()), unid, resultAll, String.valueOf(xlsenEntity.getFormtype()), ActionStatus, "");
|
if (cont>0) {
|
information +="导入日志记录成功！";
|
}else{
|
information +="导入日志记录失败！";
|
}
|
} catch (Exception e) {
|
information +="（在记录导入日志时出现代码错误："+e+")";
|
} finally {
|
SpObserver.setDBtoInstance();
|
}
|
super.printJson(response, new CallBackMessage().sendSuccessMessage(information));
|
return;
|
// }
|
} catch (Exception e) {
|
throw e;
|
} finally {
|
}
|
}
|
|
/**
|
* 处理广陶附件，从本地更新附件
|
* @param request
|
* @param response
|
*/
|
@RequestMapping(params = "m=image")
|
public void ImportAttachment(HttpServletRequest request, HttpServletResponse response){
|
try{
|
BaseService baseService= (BaseService) FactoryBean.getBean("BaseService");
|
AttachmentIfc attachmentIfc= (AttachmentIfc) FactoryBean.getBean("AttachmentImpl");
|
//取出数据
|
List<Map<String, Object>> list=baseService.getSimpleJdbcTemplate().queryForList("select a.doccode,a.EnterCode,a.EnterName,b.fujian,b.rowid from t62042h a left join t62042d b on a.DocCode=b.doccode where isnull(b.fujian,'')<>'' ");
|
SpObserver.setDBtoInstance("_"+request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
|
byte[] orgBinaryimages=null;//源文件
|
byte[] smallBinaryimages=null;//缩略图
|
int num=0;
|
for(Map<String, Object> map:list){
|
Integer seq=null;
|
if((map.get("fujian")+"").indexOf(";")>0) {continue;}
|
String fileName = "e:/"+map.get("fujian");
|
// if(true){
|
// ImageUtils.scaleByConfig(fileName, fileName, request);
|
// }
|
|
//源文件二进制
|
InputStream photoStream = new FileInputStream(fileName);
|
orgBinaryimages=input2byte(photoStream);
|
|
//处理缩略图
|
String smallImagePath = ImageUtils.buildSmallPicReName(fileName,
|
180,180);
|
|
//缩略图二进制
|
InputStream photoStream1 = new FileInputStream(smallImagePath);
|
smallBinaryimages=input2byte(photoStream1);
|
String uuid=UUID.randomUUID().toString().toUpperCase(); //生成uuid
|
File file=new File(fileName);
|
AttachmentEntity attachmentEntity = new AttachmentEntity() ;
|
attachmentEntity.setUnid(uuid);
|
attachmentEntity.setSeq(seq);
|
attachmentEntity.setFormId(62042);
|
attachmentEntity.setDoccode(map.get("doccode")+"");
|
attachmentEntity.setFieldId("fujian");
|
attachmentEntity.setRowId(null);
|
attachmentEntity.setPhysicalFile(null);
|
attachmentEntity.setPhysicalPath(null);
|
attachmentEntity.setOriginalFileName(file.getName());
|
attachmentEntity.setFileSize(file.length()/1024);
|
attachmentEntity.setFileType("jpg");
|
attachmentEntity.setAuthorCode(map.get("EnterCode")+"");
|
attachmentEntity.setAuthorName(map.get("EnterName")+"");
|
UploadFiles uploadFiles = new UploadFiles() ;
|
uploadFiles.setOrgBinaryimages(orgBinaryimages);
|
uploadFiles.setSmallBinaryimages(smallBinaryimages);
|
AttachmentWhereEntity attachmentWhereEntity = attachmentIfc.saveAttachment(uploadFiles, attachmentEntity,"1") ;
|
//更新t62042d fujian字段为uuid;seq。
|
baseService.getSimpleJdbcTemplate().update(" update a set a.fujian=? from t62042d a where a.doccode=? and a.rowid=?",uuid+";"+attachmentWhereEntity.getSeq(),map.get("doccode"),map.get("rowid"));
|
num++;
|
System.out.println(num+">>"+map.get("doccode")+";"+map.get("rowid"));
|
}
|
this.print(response,"一共处理完成-"+num);
|
}catch (Exception e){
|
this.print(response,e.getMessage());
|
}finally {
|
SpObserver.setDBtoInstance();
|
}
|
}
|
}
|
