java实现Excel导入实战篇（迭代二）
java实现Excel导入实战篇（迭代二）
挑战者
发布于 2018-06-29 17:36:39
发布于 2018-06-29 17:36:39
代码可运行
运行总次数：0
代码可运行
一、实现页面导入excel
目录：1.配置文件 2.页面展示 3.后台代码 4.图片粘贴演示1.配置文件
<!-- 文件上传-->
码 
<bean id="multipartResolver"
class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<!--1024*200即最大支持上传200kB,如果有需求可以调大，依次类推。-->
<property name="maxUploadSize" value="204800"/>
<!--resolveLazily属性启用是为了推迟文件解析，以便在UploadAction 中捕获文件大小异常-->
<property name="resolveLazily" value="true"/>
</bean> 2.页面展示
2.1html代码
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="renderer" content="webkit" />
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<!-- default header name is X-CSRF-TOKEN -->
<title>Excel导入</title>
<script charset="UTF-8" src="https://static.wuage.com/common/lib/jquery.min.js"></script>
</head>
<style>
.dn{
display: none;
}
.box{
padding-left: 20px;
}
.loadingBlack{
position: fixed;
left: 0;
top: 0;
width: 100%;
height: 100%;
z-index: 1;
background: rgba(0,0,0,0.2);
filter:alpha(opacity=20);
}
.text{
line-height: 26px;
color: #333;
font-size: 14px;
font-family: "simsun";
}
/*.box {
text-align: center;
}*/
.submitB {
margin-left: -72px;
font-size: 12px;
color: blue;
}
.text span {
color: #C81623;
font-weight: 900;
}
</style>
<body>
<div class="box">
<div class="text">
<span>注意事项:</span><br>
<p>1、通过此功能将外部渠道的信息导入到CLM系统。</p>
<p>2、导入的Excel不能隐藏列(如果有隐藏列会导致导入数据出现问题)。</p>
<p>3、目前只支持excel(以.xlsx或.xls结尾)文件导入，请下载Excel模板并严格按模板要求整理信息数据。<a href="/upload/importExcelTemplate.xlsx">excel模板下载</a></p>
<p>注释：如果需要增加列，请联系开发人员。</p>
</div>
<div>
</div>
<form name = "frm" action="/clm/clmCustomer/excelImport" method="post" enctype="multipart/form-data">
<input id="jsLink" name="excelFile" type="file" value="浏览">
<input type="button" style="width:80px;height:25px;" id="submitB" class="submitB" value="导入excel"/><br/>
</form>
</div>
</body>
<script type="text/javascript">
$(function (){
$('#submitB').click(function(){
if($("input[type='file']").val() == ""){
alert("请选择导入的文件！");
}else{
frm.submit();
}
});
});
</script>
</html>
图片.png
图片.png
图片.png
3.后台代码SpringMvc
@RequestMapping(value = "/clmCustomer/excelImportView", method = { RequestMethod.GET })
public String list(HttpServletRequest request, Model model, HttpServletResponse response) {
return "/clmpool/excelImportView";//导出页面
}
/**
* 导入excel数据。
*
* @return
*/
@RequestMapping(value = "/clmCustomer/excelImport", method = RequestMethod.POST)
@ResponseBody
@Transactional
public ModelResult<Map<String, Object>> excelImportData(@RequestParam("excelFile") MultipartFile multipartFile,
HttpServletRequest request, HttpServletResponse response) {
ModelResult<Map<String, Object>> result = new ModelResult<Map<String, Object>>();
List<String> list = null;
List<ExcelImportModel> excelImportModelList = Lists.newArrayList();
// 所有问题数据返回
Map<String, Object> problemMap = Maps.newConcurrentMap();
List<ExcelImportModel> problemList = Lists.newArrayList();
int count = 1;
try {
if (!multipartFile.isEmpty()) {
// ====================== 这个就是导入的excel返回的JSON数据============================start
list = ExcelImportUtil.exportListFromExcel(multipartFile, 0);
LOG.info("ExcelImportUtil return listData:{}", JSON.toJSONString(list));
System.out.println(JSON.toJSONString(list));
// ====================== 这个就是导入的excel返回的JSON数据============================end
// =======================解析json进行业务 处理=========================================start
for (String string : list) {
count++;
String[] split = string.split("=");
System.out.println(split.length);
ExcelImportModel importModel = new ExcelImportModel();
importModel.setCompanyName(split[1]);
importModel.setContactPerson(split[2] + split[3].replace("未填写", ""));
importModel.setMobile(split[4]);
StringBuffer remark = new StringBuffer();
if ((split.length > 6) && StringUtils.isNotBlank(split[5])) {
if (StringUtils.isNotBlank(split[5].replace("\\N", ""))) {
remark.append("座机：" + split[5].replace("\\N", ""));
}
}
if ((split.length > 7) && StringUtils.isNotBlank(split[6])) {
if (StringUtils.isNotBlank(split[6].replace("\\N", ""))) {
remark.append(";邮箱：" + split[6].replace("\\N", ""));
}
}
if ((split.length > 8) && StringUtils.isNotBlank(split[7])) {
if (StringUtils.isNotBlank(split[7].replace("\\N", ""))) {
remark.append(";传真：" + split[7].replace("\\N", ""));
}
}
if ((split.length > 9) && StringUtils.isNotBlank(split[8])) {
if (StringUtils.isNotBlank(split[8].replace("\\N", ""))) {
remark.append(";公司介绍：" + split[8].replace("\\N", ""));
}
}
if (StringUtils.isNotBlank(remark.toString())) {
importModel.setRemark(remark.toString());
}
if ((split.length > 10) && StringUtils.isNotBlank(split[9])) {
importModel.setBusinessProvince(split[9].replace("省", ""));
}
if ((split.length > 11) && StringUtils.isNotBlank(split[10])) {
importModel.setBusinessCity(split[10].replace("市", ""));
}
if ((split.length > 12) && StringUtils.isNotBlank(split[11])) {
importModel.setBusinessAddress(split[11]);
}
if ((split.length > 13) && StringUtils.isNotBlank(split[12])) {
importModel.setMainProd("|"
+ split[12].trim().replace(",", "|").replace("，", "|").replace(" ", "")
+ "|");
}
excelImportModelList.add(importModel);
}
// =======================解析json进行业务 处理=========================================end
LOG.info("excelImportModelList:{}", JSON.toJSONString(excelImportModelList));
// 标签（来源）id
Long tagId = getTagId();
if (tagId > 0) {
for (ExcelImportModel excelImportModel : excelImportModelList) {
// 校验手机号
Map<String, String> checkPhoneNumberBelongs = PhoneNumberBelongsUtil.checkPhoneNumberBelongs(excelImportModel.getMobile());
if (CollectionUtils.isEmpty(checkPhoneNumberBelongs)
|| StringUtils.isEmpty(excelImportModel.getCompanyName())
|| StringUtils.isEmpty(excelImportModel.getCompanyName().replace("\\N", ""))) {
ExcelImportModel newexcelImportModel = new ExcelImportModel();
newexcelImportModel.setCompanyName(excelImportModel.getCompanyName());
newexcelImportModel.setMobile(excelImportModel.getMobile());
problemList.add(newexcelImportModel);
continue;
}
// 1.根据手机号判断是否有会员或者是否已经注册客户。
if (StringUtils.isNotBlank(excelImportModel.getMobile())) {
ClmPoolParam clmPoolParam = new ClmPoolParam();
clmPoolParam.setMobile(excelImportModel.getMobile());
ModelListResult<ClmPoolModel> queryClmPoolModel = clmPoolQueryService.query(clmPoolParam);
if (queryClmPoolModel != null && CollectionUtils.isNotEmpty(queryClmPoolModel.getModel())) {
continue;
}
} else {
// 如果手机号为null，调过该条数据
ExcelImportModel newexcelImportModel = new ExcelImportModel();
newexcelImportModel.setCompanyName(excelImportModel.getCompanyName());
newexcelImportModel.setMobile(excelImportModel.getMobile());
problemList.add(newexcelImportModel);
continue;
}
// 2.判断是否区域为null，如果不为null，则根据区域判断分池。
short poolStatus = 0;
if (StringUtils.isNotBlank(excelImportModel.getBusinessProvince())) {
poolStatus = ClmBaseMessageUtil.getPoolStatusByBelongsArea(excelImportModel.getBusinessProvince());
if (poolStatus > 0) {
insertClmPool(excelImportModel, tagId, poolStatus,
excelImportModel.getBusinessProvince(),
excelImportModel.getBusinessCity());
continue;
}
}
// 3.根据手机号识别区域和补充信息。
poolStatus = ClmBaseMessageUtil.getPoolStatusByMobile(excelImportModel.getMobile());
if (poolStatus > 0) {
insertClmPool(excelImportModel, tagId, poolStatus, excelImportModel.getBusinessProvince(),
excelImportModel.getBusinessCity());
} else {
ExcelImportModel newexcelImportModel = new ExcelImportModel();
newexcelImportModel.setCompanyName(excelImportModel.getCompanyName());
newexcelImportModel.setMobile(excelImportModel.getMobile());
problemList.add(newexcelImportModel);
}
}
}
}
// 总池中不存在则创建
problemMap.put("problemData", JSON.toJSONString(problemList));
problemMap.put("problemCount", problemList.size());
problemMap.put("totalCount", excelImportModelList.size());
result.setModel(problemMap);
LOG.info("problemData:{},problemCount:{}", JSON.toJSONString(problemList), problemList.size());
} catch (Exception e) {
System.out.println("import excel error 第" + count + "数据有问题");
LOG.info("import excel error :{},count:{}", e, count);
result.setErrorMessage(ResultCode.INSERT_10100.getCode(),
"import excel error please check import excel data problem!第" + count + "数据有问题");
}
return result;
}4.动态图片操作演示
导入Excel图.gif
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2017.08.17 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
腾讯云开发者
