1.jsp
<link rel="stylesheet" href="../../ext-3.2.1/ux/fileuploadfield/css/fileuploadfield.css" />
<script type="text/javascript" src="../../ext-3.2.1/ux/fileuploadfield/FileUploadField.js"></script>
2.js
var downloadBtn = new Ext.Button({
text:"导入模版下载",
iconCls:'excel',
handler:function(){
var serverPath1 = location.protocol+"//"+location.host+"/xx/downLoad/metersInputModel_1.xls";
var serverPath2 = location.protocol+"//"+location.host+"/xx/downLoad/metersInputModel_2.xls";
Ext.Msg.show({
title:'点击以下链接下载',
msg: "<a href="+serverPath1+" target='_blank'>1.基表导入模版下载</a><br><a href="+serverPath2+" target='_blank'>2.修正仪导入模版下载</a><br>若点击无法下载，请右击上述文字，选择“目标另存为”",
buttons: Ext.Msg.OK,
width:300
});
}
});
var importXls = function(){
var fileName=fileForm.form.findField('file').getValue();
if(fileName.length<1){
Ext.Msg.alert(message.tip,'请选择要导入的Excel文件!');
return;
}
if(fileName.substring(fileName.lastIndexOf('.')+1)!='xls'){
Ext.Msg.alert(message.tip,'仅支持导入Excel文件!');
return;
}
if(Ext.isEmpty(btnTypeModel.getValue())){
Ext.Msg.alert(message.tip,'请务必选择导入数据类型！');
return;
}
fileForm.form.submit({
url:path+'/MetersInput/importData.json',
params:{type:btnTypeModel.getValue()},
method:'post',
waitMsg:'正在导入,请稍候...',
success:function(form,action){
Ext.Msg.alert(message.tip, action.result.msg);//action.result.msg
//刷新界面
if(action.result.success){
padStore.reload();
}
fileForm.form.reset();
},
failure:function(form,action){
Ext.Msg.alert(message.tip, action.result.msg);
fileForm.form.reset();
}
});
};
var btnTypeModel = new Ext.form.ComboBox({
mode : 'local',
triggerAction : 'all',
name : 'tablestatus',
hiddenName : 'tablestatus',
fieldLabel : '类型',
valueField : 'id',
displayField : 'text',
anchor : '100%',
width:100,
editable : false,
allowBlank : false,
mode : 'local',
store : new Ext.data.SimpleStore({
fields : [ 'id', 'text' ],
data : [ [ '1', '类型1' ],
[ '2', '类型2' ]
]
})
});
var btnImport = new Ext.Button({
text:'导 入 ',
iconCls:'import',
name : 'import',
handler : function(){importXls();}
});
var fileTopbar = new Ext.Toolbar({
margins:'0 0 0 0',
bodyStyle:'0px 0px 0px 0px',
items:[
'-','类型:',btnTypeModel,
'-',btnImport,
'-',downloadBtn,
'-']
});
//批量上传附件
var fileForm = new Ext.form.FormPanel({
bodyStyle : 'padding:0px 5px 0px 5px',
labelAlign : 'right',
labelWidth: 50,
layout : 'column',
fileUpload:true,
enctype:'multipart/form-data',
frame : true,
items : [{
columnWidth:0.35,
layout:'form',
bodyStyle : 'padding:2px 0px 0px 0px',
items:[{
xtype:'fileuploadfield',
buttonText:' 浏 览 ',
fieldLabel:'文件位置',
height:20,
name:'file',
anchor:'100%'}]
},{
columnWidth:0.65,
bodyStyle : 'padding:0px 0px 0px 0px',
layout:'form',
items:[fileTopbar]
}]
});
var northPanel = new Ext.Panel({
layout:'fit',
region:'north',
height:40,
border:false,
items:[fileForm]
});
3.java
@RequestMapping(value="/importData",method=RequestMethod.POST)
public void importData(@RequestParam("file") MultipartFile file,
HttpServletResponse response,String type,ModelMap modelMap) throws IOException {
try{
boolean success = Boolean.FALSE;
String msg = null;
if(!file.isEmpty()){
InputStream inputStream = file.getInputStream();
msg = this.getXlsData(type,inputStream); //读取Excel并保存数据
inputStream.close();
}
//判断是否成功, 和返回值
if("failure".equals(msg) || msg==null){
msg = "导入失败！";
}else if(msg.indexOf("success")!=-1){
success = Boolean.TRUE;
msg = msg.replace("success,", "");
}else if("success".equals(msg)){
success = Boolean.TRUE;
msg = "导入成功！";
}
response.setContentType("text/html;charset=utf-8");
response.getWriter().write("{success:"+success+",msg:'"+msg+"'}");
}catch(Exception e){
modelMap.put("success", Boolean.FALSE);
modelMap.put("msg", e.getMessage());
e.printStackTrace();
response.getWriter().write("{success:false,msg:'导入失败！'}");
}
}
public String getXlsData(String type, InputStream inputStream) {
int colNum = 0, rowNum = 0, rowCount = 0;
String result = "failure";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
List<MetersInputBean> list = new ArrayList<MetersInputBean>();
try{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(inputStream);
// 第一个工作表
HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);
// 创建行对象
HSSFRow tempRow = null;
// 获得总行数
rowCount = hssfsheet.getLastRowNum();
if(rowCount > 0) {
if("1".equals(type)){//基表
//定义接收变量
String tablestatus, mtrid, mtrvendor, brand, mtrtype, memometer, mtrspc, diameter,
maximum, minimum, productiondate, nextvaliddate, projectname, memo;
StringBuffer errorMsg = new StringBuffer();
MetersInputBean mode = null;
for (int i = 1; i < rowCount + 1; i++) {
rowNum = i; //获取行数
colNum = 0; //重置列数
errorMsg.delete(0, errorMsg.length()); //清空错误信息, 再次使用
// 构造一个空对象
mode = new MetersInputBean();
tempRow = hssfsheet.getRow(i);
//判断是否还有数据
if (tempRow == null || tempRow.getCell(0) == null) {
continue;
}
tablestatus = PoiCell.getCellValue(tempRow.getCell(colNum++)); //材料名称
if(tablestatus.length()<=0) throw new Exception("类型不能为空！");
mtrid = PoiCell.getCellValue(tempRow.getCell(colNum++)); //材料名称
if(mtrid.length()<=0) throw new Exception("表号不能为空！");
mtrvendor = PoiCell.getCellValue(tempRow.getCell(colNum++)); //单价
brand = PoiCell.getCellValue(tempRow.getCell(colNum++)); //计量单位
mtrtype = PoiCell.getCellValue(tempRow.getCell(colNum++));
memometer = PoiCell.getCellValue(tempRow.getCell(colNum++));
mtrspc = PoiCell.getCellValue(tempRow.getCell(colNum++));
diameter = PoiCell.getCellValue(tempRow.getCell(colNum++));
maximum= PoiCell.getCellValue(tempRow.getCell(colNum++));
minimum= PoiCell.getCellValue(tempRow.getCell(colNum++));
productiondate= PoiCell.getCellValue(tempRow.getCell(colNum++));
nextvaliddate= PoiCell.getCellValue(tempRow.getCell(colNum++));
projectname= PoiCell.getCellValue(tempRow.getCell(colNum++));
memo= PoiCell.getCellValue(tempRow.getCell(colNum++));
//设值
mode.setTablestatus("1");
mode.setMtrid(mtrid);
mode.setMtrvendor(mtrvendor);
mode.setBrand(brand);
mode.setMtrtype(mtrtype);
mode.setMtrspc(mtrspc);
mode.setDiameter(diameter);
mode.setMemometer(memometer);
mode.setMaximum(Double.parseDouble(maximum));
mode.setMinimum(Double.parseDouble(minimum));
mode.setProductiondate(sdf.parse(productiondate));
mode.setNextvaliddate(sdf.parse(nextvaliddate));
mode.setProjectname(projectname);
mode.setMemo(memo);
list.add(mode);
}
}else if("2".equals(type)){//修正仪
//定义接收变量
String tablestatus, mtrid, mtrvendor, brand, aammtrtype, memometer,productiondate, nextvaliddate, projectname, memo;
StringBuffer errorMsg = new StringBuffer();
MetersInputBean mode = null;
for (int i = 1; i < rowCount + 1; i++) {
rowNum = i; //获取行数
colNum = 0; //重置列数
errorMsg.delete(0, errorMsg.length()); //清空错误信息, 再次使用
// 构造一个空对象
mode = new MetersInputBean();
tempRow = hssfsheet.getRow(i);
//判断是否还有数据
if (tempRow == null || tempRow.getCell(0) == null) {
continue;
}
tablestatus = PoiCell.getCellValue(tempRow.getCell(colNum++)); //材料名称
if(tablestatus.length()<=0) throw new Exception("类型不能为空！");
mtrid = PoiCell.getCellValue(tempRow.getCell(colNum++)); //材料名称
if(mtrid.length()<=0) throw new Exception("表号不能为空！");
mtrvendor = PoiCell.getCellValue(tempRow.getCell(colNum++));
brand = PoiCell.getCellValue(tempRow.getCell(colNum++));
aammtrtype = PoiCell.getCellValue(tempRow.getCell(colNum++));
memometer = PoiCell.getCellValue(tempRow.getCell(colNum++));
productiondate= PoiCell.getCellValue(tempRow.getCell(colNum++));
nextvaliddate= PoiCell.getCellValue(tempRow.getCell(colNum++));
projectname= PoiCell.getCellValue(tempRow.getCell(colNum++));
memo= PoiCell.getCellValue(tempRow.getCell(colNum++));
mode.setTablestatus("2");
mode.setMtrid(mtrid);
mode.setMtrvendor(mtrvendor);
mode.setBrand(brand);
mode.setMemometer(memometer);
mode.setAammtrtype(aammtrtype);
mode.setProductiondate(sdf.parse(productiondate));
mode.setNextvaliddate(sdf.parse(nextvaliddate));
mode.setProjectname(projectname);
mode.setMemo(memo);
list.add(mode);
}
}
}
//保存导入信息
if(list.size() > 0){
// //判断表号是否存在
for(MetersInputBean code:list){
String sql = "select * from mms_meter where mtrid ='"+code.getMtrid()+"'";
int rows = this.metersInputService.getRowCount(sql);
if(rows>0) return "表号或修正仪编号["+code.getMtrid()+"]已存在，请核实！";
}
try{
result = this.metersInputService.insertXlsData(list);
}catch (Exception e) {
return "数据导入失败,请检查文件数据是否无误！";
}
}else{
return "Excel空或有误, 没有读取到数据！";
}
return result;
}catch(Exception e){
list.clear();
e.printStackTrace();
//判断导入数据是否有误
if(rowNum <= rowCount) result = "第"+rowNum+"行"+colNum+"列数据有误, 请核实！<br><font color='red'>错误信息: "+e.getMessage()+"</font>";
return result;
}
}
@Transactional
public String insertXlsData(List<MetersInputBean> list) throws Exception{
SimpleDateFormat sdf_hms = new SimpleDateFormat("yyyyMMdd");
DecimalFormat bigDf = new DecimalFormat("000000");
String Changetime = sdf_hms.format(new Date());
int rows = 0;
//代码转换
String sql = "select * From sys_code where parentid in('1.2.16','1.2.67','1.2.44','1.2.32')";
List<SysCode> sysList = this.jdbcTemplate.query(sql, sysCodeRowMapper);
Map<String ,String> map= new HashMap<String, String>();
for(SysCode code:sysList){
map.put(code.getValue(),code.getId());
}
int index = 0;
for(MetersInputBean code:list){
//转码
code.setMtrvendor(map.get(code.getMtrvendor()));
code.setMemometer(map.get(code.getMemometer()));
code.setDiameter(map.get(code.getDiameter()));
String Changetime_Value = Changetime + String.valueOf(bigDf.format(++index));
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
String inSql = "insert into mms_meter(id,mtrid,brand,mtrvendor,mtrtype,mtrspc,"
+ "diameter,projectname,maximum,minimum,isic,mtrstatus,tablestatus,changetime,memo,memometer,mtrposition";
if(code.getProductiondate()!=null){
inSql+=",productiondate";
}
if(code.getNextvaliddate()!=null){
inSql+=",nextvaliddate";
}
inSql+=" ) "
+ "values (mms_meter_seq.nextval,'" + (code.getMtrid()==null?"":code.getMtrid()) + "','"
+ (code.getBrand()==null?"":code.getBrand()) + "','"
+ (code.getMtrvendor()==null?"":code.getMtrvendor()) + "'," + "'"
+ (code.getMtrtype()==null?"":code.getMtrtype()) + "','"
+ (code.getMtrspc()==null?"":code.getMtrspc()) + "','"
+ (code.getDiameter()==null?"":code.getDiameter()) + "','"
+ (code.getProjectname()==null?"":code.getProjectname()) + "','"
+ code.getMaximum() + "','" + code.getMinimum() + "','"
+ (code.getIsic()==null?"":code.getIsic()) + "','1.2.32.2','"
+ code.getTablestatus()+ "','"+ Changetime_Value +"','"
+ (code.getMemo()==null?"":code.getMemo())+"','"
+ (code.getMemometer()==null?"":code.getMemometer())+"','1.4.10.3'";
if(code.getProductiondate()!=null){
inSql += ",to_date('" + df.format(code.getProductiondate()) + "','yyyy-mm-dd')";
}
if(code.getNextvaliddate()!=null){
inSql += ",to_date('" + df.format(code.getNextvaliddate()) + "','yyyy-mm-dd')";
}
inSql+=")";
rows += this.jdbcTemplate.update(inSql);
}
return "success,总共"+list.size()+"行, 成功导入"+rows+"行！";
}
浙公网安备 33010602011771号