相关内容:
1,jdbcTemplate 批量导入
2,js原始产生form 动态产生文件选择框
3,反射机制调用set方法
![](https://i-blog.csdnimg.cn/blog_migrate/e57da3ed0fb872715ec0510011170b86.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/544c2faaac58266387037f818a6b5691.gif)
<div class="l-bar-separator"></div>
<div class="group"><a class="link add" href="javascript:void(0);" id="importExcel"><span></span>导入excel</a></div>
![](https://i-blog.csdnimg.cn/blog_migrate/166926cf5721abc109e86c32a121537b.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/b0e89fffe2e5bc1eb8c9b3545e941fa8.gif)
<script type="text/javascript">
$(function() {
//导入基础数据
$("#importExcel").click(function() {
var url = __ctx+ '/ytdyjcompare/Ytdyjykzzb/ytdyjykzzb/importExcel.ht';
importExcel(url);//导入数据
});
});
/**
* 产生导入对话框,并打开
* @param actionUrl 请求的Url
*/
function importExcel(actionUrl) {
var f = document.createElement("form");
var i = document.createElement("input");
f.name = 'myFrom';
f.action = actionUrl;
f.method = "post";
i.type = "file";
i.name = "xmlFile";
$(i).css("display", "none");
$(i).attr("onchange", "importData('" + actionUrl + "')");
//$(i).attr("accept", ".xls");
$(i).attr("accept", [ '.xls', '.xlsx' ]);
$(f).attr("enctype", "multipart/form-data");
f.appendChild(i);
document.body.appendChild(f);
i.click();
}
/**
*提交表单
*/
function importData(actionUrl) {
//同步提交
//$("form[name='myFrom']").submit();
//异步提交
var nmiframe = document.forms['myFrom'];
var formData = new FormData(nmiframe);
$.ajax({
url : actionUrl,
type : 'POST',
data : formData,
async : false,
cache : false,
contentType : false,
processData : false,
success : function(returndata) {
//alert(1);
alert(returndata);
window.location.reload(true);//重新加载页面
},
error : function(returndata) {// alert(2);
alert(returndata);
}
});
}
</script>
![](https://i-blog.csdnimg.cn/blog_migrate/db074f1b1e34154a3b9fe987ae5ef67a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/9d1fac35ebbc0a1fc30384aa5d3917e1.gif)
/**
* @param request
* @param response
* @throws Exception
* 执行导入操作
*/
@ResponseBody
@RequestMapping("importExcel")
public void importExcel(MultipartHttpServletRequest request, HttpServletResponse response) throws Exception {
String rq = request.getParameter("rq");
System.out.println("can you see me");
// SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
MultipartFile fileLoad = request.getFile("xmlFile");
ResultMessage resultMessage = null;
String result = "导入数据成功";
try {
String fileType = fileLoad.getOriginalFilename().substring(fileLoad.getOriginalFilename().lastIndexOf(".") + 1, fileLoad.getOriginalFilename().length());
Workbook wb = null;
if (fileType.equals("xls")) {
wb = new HSSFWorkbook(fileLoad.getInputStream());
} else if (fileType.equals("xlsx")) {
wb = new XSSFWorkbook(fileLoad.getInputStream());
} else {
throw new Exception("读取的不是excel文件");
}
this.importExcelHandle(request, wb);
if (result.contains("成功")) {
resultMessage = new ResultMessage(ResultMessage.Success, "导入成功!");
} else {
resultMessage = new ResultMessage(ResultMessage.Fail, result);
}
writeResultMessage(response.getWriter(), resultMessage);
} catch (Exception ex) {
ex.printStackTrace();
resultMessage = new ResultMessage(ResultMessage.Fail, result);
resultMessage.setMessage("导入失败--" + ex.getMessage());
response.getWriter().print(resultMessage);
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/236c1ea707588cf6125365b4613de4ca.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/9bfa40edaee16f292b71cb24fbdec90f.gif)
/*
* 具体导入操作
*/
public void importExcelHandle(MultipartHttpServletRequest request, Workbook wb) throws Exception {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
// 读取了模板内所有sheet内容
HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(0);
HSSFCell cell = null;
// 读取excel,保存与map中
// Map<String, String> excelMap = new HashMap<String, String>();
// 获取最大行
// int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
int physicalNumberOfRows = sheet.getLastRowNum();
Map<String, String> titleVarMap = this.getTitleVarMap();
Class<?> cls = Class.forName("com.ytd.ytdyjcompare.model.Ytdyjykzzb.Ytdyjykzzb");
Method[] ms = cls.getMethods();
List<Ytdyjykzzb> list = new ArrayList<Ytdyjykzzb>();
// 遍历每一行
for (int i = 0; i < physicalNumberOfRows; i++) {
Object o = cls.newInstance();
// 遍历每一列
for (int j = 0; j < titleVarMap.size(); j++) {
cell = sheet.getRow(i + 1).getCell(j);
cell.setCellType(Cell.CELL_TYPE_STRING);
String colVal = cell.getStringCellValue();
// 查找对应实体类属性
String key = titleVarMap.get("var" + j);
if (StringUtil.isNotEmpty(key)) {
// 遍历类的所有方法名
for (int k = 0; k < ms.length; k++) {
// excel标题与类属性相同的时候
String setType = ms[k].getReturnType().getName();
Method method = null;
// 获取get方法
String tmp = "get" + firstLetterName(key);
if (tmp.equals(ms[k].getName()) && StringUtil.isNotEmpty(colVal)) {
if ("java.lang.boolean".equals(setType)) {
method = cls.getMethod("set" + firstLetterName(key), Boolean.class);
method.invoke(o, colVal);
} else if ("java.lang.String".equals(setType)) {
method = cls.getMethod("set" + firstLetterName(key), String.class);
method.invoke(o, colVal);
} else if ("java.lang.Long".equals(setType)) {
method = cls.getMethod("set" + firstLetterName(key), Long.class);
method.invoke(o, Long.parseLong(colVal));
} else if ("int".equals(setType)) {
method = cls.getMethod("set" + firstLetterName(key), Integer.class);
method.invoke(o, Integer.parseInt(colVal));
} else if ("java.util.Date".equals(setType)) {
method = cls.getMethod("set" + firstLetterName(key), Date.class);
method.invoke(o, sdf.parse(colVal));
} else if ("java.lang.Double".equals(setType)) {
method = cls.getMethod("set" + firstLetterName(key), Double.class);
method.invoke(o, Double.parseDouble(colVal));
} else if ("java.lang.Float".equals(setType)) {
method = cls.getMethod("set" + firstLetterName(key), Float.class);
method.invoke(o, Float.parseFloat(colVal));
}
}
}
}
}
Ytdyjykzzb e = (Ytdyjykzzb) o;
Long id = UniqueIdUtil.genId();
e.setId(id);
SysUser sysUser = ContextUtil.getCurrentUser();
e.setDrr(sysUser.getFullname());
e.setDrsj(new Date());
e.setSfydb("否");
list.add(e);
}
this.insertData(list);
}
![](https://i-blog.csdnimg.cn/blog_migrate/21552ca35de3e5693683424f9adf3473.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/90d39c86036779c57a1b7cf27ec5837a.gif)
public void insertData(List<Ytdyjykzzb> list) {
String sql = "INSERT INTO W_YTDYJYKZZB (ID,F_SJDM,F_CZM,F_SJDH,F_DRR,F_DRSJ,F_SFYDB) values (?, ?, ?, ?,?,to_date(?,'yyyy-MM-dd HH24:mi:ss'),?)";
final List<Ytdyjykzzb> list2 = list;
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
public int getBatchSize() {
return list2.size();
}
public void setValues(PreparedStatement ps, int index) throws SQLException {
SimpleDateFormat sdfTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
int j = 1;
Ytdyjykzzb e = list2.get(index);
System.out.print(e);
ps.setObject(j++, e.getId());
ps.setObject(j++, e.getSjdm());
ps.setObject(j++, e.getCzm());
ps.setObject(j++, e.getSjdh());
ps.setObject(j++, e.getDrr());
ps.setObject(j++,e.getDrsj()==null?null:sdfTime.format(e.getDrsj()));
ps.setObject(j++, e.getSfydb());
}
});
}
/**
* @return list添加的顺序就是Excel标题的顺序
*/
public Map<String, String> getTitleVar() {
List<String> title = new ArrayList<String>();
title.add("数据点名");
title.add("厂站名");
title.add("数据点号");
// 有序的保存与map中
Map<String, String> titleMap = new HashMap<String, String>();
for (int i = 0; i < title.size(); i++) {
titleMap.put("var" + i, title.get(i));
}
return titleMap;
}
/**
* @return list添加的顺序就是Excel标题的顺序
*/
public Map<String, String> getTitleVarMap() {
List<String> title = new ArrayList<String>();
title.add("sjdm");
title.add("czm");
title.add("sjdh");
// 有序的保存与map中
Map<String, String> titleVarMap = new HashMap<String, String>();
for (int i = 0; i < title.size(); i++) {
titleVarMap.put("var" + i, title.get(i));
}
return titleVarMap;
}
/**
* @param name
* @return
* String //首字母大写
*
*/
public static String firstLetterName(String name) {
name = name.substring(0, 1).toUpperCase() + name.substring(1);
return name;
}