导入及导入模板

<a id="import_controlNum"  class="btn btn-default"><i class="glyphicon glyphicon-import"></i>导入</a>

//导入
	$("#import_controlNum").click(function(e) {
		var impoortDiaiog=$jzee.dialog({
			title: '导入Excel',
			hiddenBtn:false,
			url : path+'/xmk/control/controlNum_caliber_import.jsp',
			height:400,
			width:500,
			ok: function () {//确定按钮事件
				var url = $("#myFile").val();
				if (!url) {
					toastr.error('请选择即将导入的excel');
					return;
				}
				$("#controlNum_caliber_import_form").form('submit', {
					url : path+'/xmk/caliberControlNumController/importProjectExcel.do',
					success : function(data) {
						var json = eval('(' + data + ')');
						if(json.cade=='0'){
							toastr.error(json.message);
						}else if(json.cade=='1'){
							toastr.success(json.message);
							impoortDiaiog.modal('hide');
							refreshTable();
						}
					}
				});
			}
		});	
	});

 

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@include file="/admin/pages/common/mytags.jsp"%>
<t:base type="webuploader"></t:base> 
<%             
long currtime=System.currentTimeMillis();
String fileBasePath = request.getScheme() + "://"+ request.getServerName() + ":" + request.getServerPort()+ path;          
%>
<!-- BEGIN FORM-->
<form id="controlNum_caliber_import_form" class="form-horizontal" method="post" enctype="multipart/form-data">
	<input type="hidden" id="fileIds" name="fileIds" data-ids="file_fileIds" />
	<input type="hidden" id="file_fileIds" name="file_fileIds"  />
	<section class="content">
		<div class="portlet light">
			<div class="portlet-body form">
			    <div class="form-body">
			    	<div class="form-group">
			            <label class="col-md-3 control-label Validform_label">导入Excel:</label>
			            <div class="col-md-7">
			              <input id="myFile" type="file" name="myFile" class="form-control">
			            </div>
			        </div>
			    </div>
			    <div class="form-group">
		       	 	<label class="col-md-3 control-label">描述:</label>
		        	<div class="col-sm-7">
						<textarea type="text" 
							id="controlNum_cost_property_import_ramark" 
							class="form-control ng-pristine ng-untouched ng-valid" rows="7" disabled
							placeholder="口径编码必填，口径名称必填，业务状态（填写状态数字 一下：3；二下：5）必填，预算单位必填，跟金额有关的必须填数字，可为空。"></textarea>
					</div>
		        </div>
		         <div class="form-group">
		       	 	<label class="col-md-3 control-label">模板下载:</label> 
		        	<div class="col-sm-7">
						<a style="color:red;" id="controlNum_cost_property_import_templatedown">模板下载点击此处</a>
					</div>
		        </div>
			</div>
		</div>
	</section>
</form>
<!-- END FORM-->
<script type="text/javascript">
(function(){
	//导入模板
	$("#controlNum_cost_property_import_templatedown").click(function(){
		window.open(path+'/xmk/caliberControlNumController/downloadOrgImportExcel.do');
	});
})();
</script>

导入代码

CaliberControlNumController

/**
 * 导入
 * @param request
 * @param response
 * @return
 * @throws Exception
 */
	@RequestMapping(value="/importProjectExcel",produces= "application/json; charset=utf-8")
	@ResponseBody
	public String importProjectExcel(HttpServletRequest request,HttpServletResponse response)throws Exception{
 		JSONObject jsonObject=new JSONObject();
		String ysnd = sysParamFacade.getParamValue("当前年度");
		String jzee = sysParamFacade.getParamValue("基础数据库名");
		//获取资金来源的列表
		List<Map<String, Object>> dataInfos = sysBaseDataSelecctFacade.getZjlyList("T_CALIBER_CONTROLNUM","CaliberControlNumEntity");
		List<String> englishField = new ArrayList<String>();
		englishField.add("CALIBERNO");//口径编码
		englishField.add("CALIBERNAME");//口径名称
		englishField.add("CALIBERKZJE");//总控制金额
		englishField.add("KZREMARK");//控制说明
		englishField.add("BZ");//备注
		englishField.add("YWZT");//业务状态
		englishField.add("YSDW");//预算单位
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		InputStream fis = null;
		MultipartHttpServletRequest mulRequest = (MultipartHttpServletRequest) request;
		mulRequest.setCharacterEncoding("UTF-8");
		Map<String, MultipartFile> fileMap = mulRequest.getFileMap();
        Pattern pattern = Pattern.compile("^(([1-9]{1}\\d*)|([0]{1}))(\\.(\\d){0,2})?$");//金额规范化
		for (Entry<String, MultipartFile> entry : fileMap.entrySet()) {
			MultipartFile mulFile = entry.getValue();
			fis = mulFile.getInputStream();
			Workbook workbook = WorkbookFactory.create(fis);
			Sheet sheet = workbook.getSheetAt(0);
			//取第一行字段名
			Row firstRow = sheet.getRow(0);
			int firstCellNum = firstRow.getLastCellNum();//数据列数
			for (int i = 0; i < firstCellNum; i++) {
				for(Map<String, Object> m:dataInfos){
					if(firstRow.getCell(i).getStringCellValue().equals(m.get("showName"))){
						englishField.add(m.get("pym").toString());
					}
					
				}
			}
			//判断模板是否正确
			if(firstCellNum!=englishField.size()){
				jsonObject.put("cade", "0");
				jsonObject.put("message","模板错误，请导入正确的模板！");
				Gson gson = new Gson();
				return gson.toJson(jsonObject);
			}
			//内容行数
			int rowNum= sheet.getLastRowNum();
			//需要导入的数据
			for (int i = 1; i <= rowNum; i++) {
				Row row = sheet.getRow(i);
				//判断是否有填写必填项以及填写错误项
				Cell cell0=row.getCell(0);//口径编码行	数字
				Cell cell1=row.getCell(1);//口径名称行
				Cell cell2=row.getCell(2);//总控制金额行	数字
				Cell cell3=row.getCell(3);//控制说明行
				Cell cell4=row.getCell(4);//备注行
				Cell cell5=row.getCell(5);//业务状态行	数字
				Cell cell6=row.getCell(6);//预算单位行
				
				if(cell0!=null){
					cell0.setCellType(Cell.CELL_TYPE_STRING);
				}
				if(cell1!=null){
					cell1.setCellType(Cell.CELL_TYPE_STRING);
				}
				if(cell2!=null){
					cell2.setCellType(Cell.CELL_TYPE_STRING);
				}
				if(cell3!=null){
					cell3.setCellType(Cell.CELL_TYPE_STRING);
				}
				if(cell4!=null){
					cell4.setCellType(Cell.CELL_TYPE_STRING);
				}
				if(cell5!=null){
					cell5.setCellType(Cell.CELL_TYPE_STRING);
				}
				if(cell6!=null){
					cell6.setCellType(Cell.CELL_TYPE_STRING);
				}

				//单位判断
				if(cell0==null||cell0.toString().equals("")||cell1==null||cell1.toString().equals("")){
					jsonObject.put("cade", "0");
					jsonObject.put("message","第"+(i+1)+"行存在单位信息不完整,请填写完整在重新导入！");
					Gson gson = new Gson();
					return gson.toJson(jsonObject);
				}
				List<CaliberEntity> unit = caliberFacade.findListBySql("select * from T_CALIBER where SHOW_NO=? and NAME=? and YSND=?",cell0.toString(),cell1.toString(),ysnd);
				if(unit==null||unit.size()==0){
					jsonObject.put("cade", "0");
					jsonObject.put("message","系统不存在   ["+cell0.toString()+"]"+cell1.toString()+" 这个单位！");
					Gson gson = new Gson();
					return gson.toJson(jsonObject);
				}
				
				//总控制金额
				if(cell2!=null && !cell2.toString().equals("") && !cell2.toString().equals("null")){
					String str=cell2.toString().replace(",", "");
					boolean cell2s = pattern.matcher(str.toString()).matches();
					if(!cell2s){
						jsonObject.put("cade", "0");
						jsonObject.put("message","第"+(i+1)+"行控制金额不符合金额规范，请修改！");
						Gson gson = new Gson();
						return gson.toJson(jsonObject);
					}
				}
				
				//业务状态
				if(cell5==null||cell5.toString().equals("")||cell5.toString().equals("null")){
					jsonObject.put("cade", "0");
					jsonObject.put("message","第"+(i+1)+"行业务状态未填写,请填写完整在重新导入！");
					Gson gson = new Gson();
					return gson.toJson(jsonObject);
				}else if(!cell5.toString().equals("3")&&!cell5.toString().equals("5")){
					jsonObject.put("cade", "0");
					jsonObject.put("message","第"+(i+1)+"行业务状态不符合规格，请按照规格填写业务状态！（填写状态数字    一下：3   二下：5）");
					Gson gson = new Gson();
					return gson.toJson(jsonObject);
				}
				
				//预算单位
				if(cell6==null||cell6.toString().equals("")||cell6.toString().equals("null")){
					jsonObject.put("cade", "0");
					jsonObject.put("message","第"+(i+1)+"行预算单位未填写,请填写完整在重新导入！");
					Gson gson = new Gson();
					return gson.toJson(jsonObject);
				}
				List<String> ysdwIdList = Arrays.asList(cell6.toString().split(","));
				for (String ysdw : ysdwIdList) {
					//根据ID获取预算单位列表
					List<SysOrgEntity> sysOrgList = sysOrgFacade.findListBySql("select * from "+jzee+".sys_org where IN_CODE=?  and YEAR=?",ysdw,ysnd);
					if(sysOrgList==null||sysOrgList.size()==0){
						jsonObject.put("cade", "0");
						jsonObject.put("message","第"+(i+1)+"行预算单位口径编码 ["+ysdw+" ]不存在  ！");
						Gson gson = new Gson();
						return gson.toJson(jsonObject);
					}
				}
				
				
				Map<String, Object> map = new HashMap<String, Object>();
				for (int j = 0; j < firstCellNum; j++) {
					Cell cell = row.getCell(j);
					map.put(englishField.get(j), cell==null?null:cell.toString());
					if(j!=0&&j!=1&&j!=2&&j!=3&&j!=4&&j!=5&&j!=6){
						if(cell!=null&&!cell.toString().equals("")){
							String cells=cell.toString().replace(",", "");
							boolean matches = pattern.matcher(cells).matches();
							if(!matches){
								jsonObject.put("cade", "0");
								jsonObject.put("message","第"+(i+1)+"行 第"+(j+1)+"列 不符合金额规范，请修改！");
								Gson gson = new Gson();
								return gson.toJson(jsonObject);
							}
						}
					}
				}
				list.add(map);
			}
		}
		fis.close();
		
		SimpleDateFormat sFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//时间日期
		String time = sFormat.format(new Date());//时间日期
		String userName=this.getUserName();//获取当前登录的用户名
		return  caliberControlNumFacade.importProjectExcel(list,time,userName,ysnd,jzee);
	}

 CaliberControlNumService

String importProjectExcel(List<Map<String, Object>> list,String sFormat,String userName,String ysnd,String jzee);

 CaliberControlNumServiceImpl

@Override
	public String importProjectExcel(List<Map<String, Object>> list,String sFormat,String userName,String ysnd,String jzee) {
		JSONObject jsonObject=new JSONObject();
		//获取资金来源的列表
		List<Map<String, Object>> dataInfos = sysBaseDataSelecctFacade.getZjlyList("T_CALIBER_CONTROLNUM","CaliberControlNumEntity");
		for(Map<String, Object> m:list){
			List<String> ysdwIdList = Arrays.asList(m.get("YSDW").toString().split(","));
			for (String ysdw : ysdwIdList) {
				//项目口径
				List<Map<String, Object>> caliberEntity = caliberFacade.findListMapBySql("select * from T_CALIBER where SHOW_NO=? and NAME=? and YSND=?",m.get("CALIBERNO"), m.get("CALIBERNAME"),ysnd);
				//组织机构
				List<Map<String, Object>> orgEntity = sysOrgFacade.findListMapBySql("select * from "+jzee+".sys_org where IN_CODE=?  and YEAR=?",ysdw,ysnd);
				List<Map<String, Object>> cpControlNumList=findListMapBySql("SELECT * FROM T_CALIBER_CONTROLNUM WHERE CALIBERNO=? AND CALIBERNAME=? AND UNITNUMBER=? AND YWZT=?",m.get("CALIBERNO").toString(),m.get("CALIBERNAME").toString(),ysdw,m.get("YWZT").toString());
				if (cpControlNumList!=null && cpControlNumList.size()>0) {
					for (Map<String, Object> map : cpControlNumList) {
						executeSql("DELETE FROM T_CALIBER_CONTROLNUM where ID=?",map.get("ID").toString());
					}
				}
				//口径控制数添加
				String id = UUID.randomUUID().toString();//自动生成id
				String insertSql1="INSERT INTO T_CALIBER_CONTROLNUM (";
				String insertSql2=" VALUES (";
				insertSql1+="ID,";
				insertSql2+="'"+id+"',";
				insertSql1+="UNITID,";
				insertSql2+="'"+orgEntity.get(0).get("ID")+"',";
				insertSql1+="UNITNUMBER,";
				insertSql2+="'"+orgEntity.get(0).get("IN_CODE")+"',";
				insertSql1+="UNITNAME,";
				insertSql2+="'"+orgEntity.get(0).get("NAME")+"',";
				insertSql1+="CALIBERID,";
				insertSql2+="'"+caliberEntity.get(0).get("ID")+"',";
				insertSql1+="CALIBERNO,";
				insertSql2+="'"+caliberEntity.get(0).get("SHOW_NO")+"',";
				insertSql1+="CALIBERNAME,";
				insertSql2+="'"+caliberEntity.get(0).get("NAME")+"',";
				insertSql1+="LAST_UPDATE_USER,";
				insertSql2+="'"+userName+"',";
				insertSql1+="LAST_UPDATE_TIME,";
				insertSql2+="'"+sFormat+"',";
				insertSql1+="YSND,";
				insertSql2+="'"+ysnd+"',";
				insertSql1+="YWZT,";
				insertSql2+="'"+m.get("YWZT")+"',";
				if(m.get("CALIBERKZJE")!=null && !m.get("CALIBERKZJE").equals("null") && !m.get("CALIBERKZJE").equals("")){
					insertSql1+="CALIBERKZJE,";
					insertSql2+="'"+m.get("CALIBERKZJE").toString().replace(",", "")+"',";
				}
				for(Map<String, Object> map:dataInfos){	
					if(m.get(map.get("pym"))!=null&&!m.get(map.get("pym")).equals("")&&!m.get(map.get("pym")).equals("null")){
						insertSql1+=map.get("pym")+",";
						insertSql2+="'"+new BigDecimal(m.get(map.get("pym")).toString().replace(",", ""))+"',";
					}
				}
				if(m.get("KZREMARK")!=null && !m.get("KZREMARK").equals("null") && !m.get("KZREMARK").equals("")){
					insertSql1+="KZREMARK,";
					insertSql2+="'"+m.get("KZREMARK")+"',";
				}
				if(m.get("BZ")!=null && !m.get("BZ").equals("null") && !m.get("BZ").equals("")){
					insertSql1+="BZ,";
					insertSql2+="'"+m.get("BZ")+"',";
				}
				insertSql1=insertSql1.substring(0, insertSql1.length()-1);
				insertSql2=insertSql2.substring(0, insertSql2.length()-1);
				insertSql1+=")";
				insertSql2+=")";
				System.out.println(insertSql1+insertSql2);
				executeSql(insertSql1+insertSql2);
			}
		}
		jsonObject.put("cade", "1");
		jsonObject.put("message","导入成功");
		Gson gson = new Gson();
		return gson.toJson(jsonObject);
	}

导入模板代码

/**
 * 导入模板
 * @param response
 */
	@RequestMapping("/downloadOrgImportExcel")
	@ResponseBody
	public void downloadOrgImportExcel(HttpServletResponse response){
		//获取资金来源的列表
		List<Map<String, Object>> dataInfos = sysBaseDataSelecctFacade.getZjlyList("T_CALIBER_CONTROLNUM","CaliberControlNumEntity");
		List<String> header=new ArrayList<String>();
		header.add("口径编码");
		header.add("口径名称");
		header.add("总控制金额");
		header.add("控制说明");
		header.add("备注");
		header.add("业务状态");
		header.add("预算单位");
		for(Map<String, Object> map:dataInfos){
			header.add(map.get("showName").toString());
		}
		try {
			HSSFWorkbook hSSfWorkbook = new HSSFWorkbook();//工作簿
			HSSFSheet hSSFSheet = hSSfWorkbook.createSheet();//表
			int hssfRowIndex=0; 
			int hssfCellIndex=header.size();
			HSSFRow hssfRowTwo=hSSFSheet.createRow((short)hssfRowIndex);
			hssfRowTwo.setHeightInPoints(15);
			for (int i = 0; i < hssfCellIndex; i++) {
				HSSFCell cellTwo=hssfRowTwo.createCell(i);
				cellTwo.setCellValue(header.get(i));
				this.getCellTitleStyle(hSSfWorkbook,cellTwo);
			}
			
			OutputStream out = response.getOutputStream();
			response.addHeader("Content-Disposition", "attachment;filename="+ java.net.URLEncoder.encode("口径控制数导入模板"+".xls", "UTF-8"));
			response.setContentType("application/octet-stream");
			hSSfWorkbook.write(out);
			out.flush();
			out.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void getCellTitleStyle(HSSFWorkbook hssfWorkbook,HSSFCell cell){
		HSSFFont font = hssfWorkbook.createFont();
		
		font.setFontName("黑体");
		font.setFontHeightInPoints((short) 12);//设置字体大小
		HSSFCellStyle style=cell.getCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  	  	style.setFont(font);
	}