1.首先,导入easyPOI的Maven依赖,可以去Maven仓库去找。
2.下面是我实际工作中写的代码:
(1)controller层:
@RequestMapping("/importExcel")
@ResponseBody
public String importExcel(@RequestParam(value="file") MultipartFile file,HttpServletRequest request) {
String fffw=request.getParameter("fffw_");
String fffwName=request.getParameter("fffwName_");
MultipartRequest multipartRequest=(MultipartRequest) request;
MultipartFile excelFile=multipartRequest.getFile("file");
ImportParams params=new ImportParams();
int fail=0;
//params.setTitleRows(1);
params.setHeadRows(1);
params.setNeedVerfiy(true);
List<BirthInfoImportExcelPojo> list=null;
int success=0;
try {
list=ExcelImportUtil.importExcel(excelFile.getInputStream(), BirthInfoImportExcelPojo.class, params);
fail=indpservice.insertBirthInfo(list,fffw,fffwName);
success=list.size();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//System.out.println(JSON.toJSONString(list));
return "生日卡数据导入成功"+success+"条,导入失败"+fail+"条";
}
(2)service层:对数据进行处理后 插入数据库
@Override
public int insertBirthInfo(List<BirthInfoImportExcelPojo> list,String fffw,String fffwName) {
// TODO Auto-generated method stub
int fail=0;
ShiroUser user = (ShiroUser) SecurityUtils.getSubject().getPrincipal();
if(list!=null&& list.size()>0){
Iterator<BirthInfoImportExcelPojo> it = list.iterator();
while(it.hasNext()){
BirthInfoImportExcelPojo bir=it.next();
StringBuilder sb=new StringBuilder();
sb.append("select lpkzh from FD_CPASHsrkgl where lpkzh='"+bir.getLpkzh()+"'");
List<Map<String,String>> lis=indpDao.getDdataList(sb.toString());
if(lis.size()>0 || bir.getLpkzh()==null){
fail++;
it.remove();
}
}
for(int j=0;j<list.size();j++){
BirthInfoImportExcelPojo bir=list.get(j);
CpaBirInfoEntity birInfo = new CpaBirInfoEntity();
birInfo.setId(UUID.randomUUID().toString());
birInfo.setCjr_(user.getUserId());
birInfo.setCjrname_(user.getName());
birInfo.setData_dept_code(user.getDeptCode());
birInfo.setData_org_id(user.getOrgid());
birInfo.setC_uid(user.getUserId());
birInfo.setDr("N");
birInfo.setData_user_id(user.getUserId());
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
birInfo.setTs(formatter.format(new Date()));
birInfo.setSfyfp("CPASsf002");
birInfo.setCjrq(formatter.format(new Date()));
birInfo.setLpkzh(bir.getLpkzh());
birInfo.setThmm(bir.getLpkpwd());
birInfo.setFffw_(fffw);
birInfo.setFffwname_(fffwName);
StringBuilder sb = new StringBuilder();
sb.append(
" insert into bpm_ru_biz_info(ID_,SOL_ID_,TABLE_NAME_,ARCHIVE_STATE_,CREATE_USER_ID_,STATE_,DR_,BIZ_TYPE_,DATA_USER_ID,TS_) ");
sb.append(" values('" + birInfo.getId() + "','263867fd-7d9b-484d-a17e-ca6d1bf15410','生日卡管理','0','"
+ user.getUserId() + "','0','N','100110121004','"+user.getUserId()+"','"+formatter.format(new Date())+"')");
indpDao.save(birInfo);
indpDao.saveBirInfo(sb.toString());
}
}
if(list.size()>0&& list!=null){
StringBuilder updateSql=new StringBuilder();
updateSql.append("update FD_srkfsfw set srkzs=srkzs+'"+list.size()+"',kys=kys+'"+list.size()+"' where id='"+fffw+"'");
indpDao.saveBirInfo(updateSql.toString());
}
return fail;
}
(3)Entity: 注解需要对应Excel中的信息。
public class BirthInfoImportExcelPojo {
/**
* 礼品卡账号
*/
@Excel(name = "礼品卡账号")
private String lpkzh;
/**
* 账号密码
*/
@Excel(name = "密码")
private String lpkpwd;
/**
* 礼品卡发放范围
*/
@Excel(name = "发放范围")
private String lpkfffw;
public String getLpkzh() {
return lpkzh;
}
public void setLpkzh(String lpkzh) {
this.lpkzh = lpkzh;
}
public String getLpkpwd() {
return lpkpwd;
}
public void setLpkpwd(String lpkpwd) {
this.lpkpwd = lpkpwd;
}
public String getLpkfffw() {
return lpkfffw;
}
public void setLpkfffw(String lpkfffw) {
this.lpkfffw = lpkfffw;
}
@Override
public String toString() {
return "BirthInfoImportExcelPojo [lpkzh=" + lpkzh + ", lpkpwd=" + lpkpwd + ", lpkfffw=" + lpkfffw + "]";
}
}
(4)前台代码:
function saveImport() {
//限制文件为excel文件
var file = $("#file").val();
var fw=$("#fffwName_").val();
if (file.indexOf(".xls") < 0) {
layerMsg("'提示', '仅支持xls和xlsx格式,请重新选择!'");
return;
}else if(fw==""){
layerMsg("'提示', '请选择发放范围'");
return ;
}
var modal = $('#mylaModal');
modal.modal('hide');
var formData = new FormData();
formData.append("file", document.getElementById("file").files[0]);
formData.append("fffw_",document.getElementById("fffw_").value);
formData.append("fffwName_",document.getElementById("fffwName_").value);
var index = layer.load(0, {shade: [0.1,'#fff']}); //0代表加载的风格,支持0-2
$.ajax({
url : 'indpController/importExcel',
type : "POST",
data : formData,
dataType: 'text',
contentType : false,
processData : false,
async : true,
success : function(data) {
layerAlert(data);
$("#search_").click();
layer.close(index);
$("#bizInfoList").bootstrapTable('destroy');
loadTableCfg();
memorySelected();
initBtns();
}
});
}
记录作为java实习生的点滴