**前言:**这是本人工作中的一个SpringBoot项目,有关通过excel模板(或者zip里面有excel)导入数据,先分享给大家,后面还会发布POI导出普通excel、模板excel、单个压缩文件含多个excel、单个excel含多个sheet、模板word。为了让大家能够完全看懂,我尽量把每一步代码都写上,没看懂的骚年们可以留言,我看到必回。
首先添加依赖:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
1、Controller:
@RestController
@RequestMapping("/law/collection/")
@Api(value = "模板导入", tags = {"模板导入接口"})
public class ExcelController {
@Autowired
IExcelService excelService;
@ApiOperation(value = "上传一个附件", notes = "通过表单方式上传一个文件")
@PostMapping("excel/upload")
public ResultBean upload(
@ApiParam(name = "file", value = "附件实体类", required = true)
MultipartFile file,
@ApiParam(name = "itemType", value = "行政案件类型(1:行政检查;2:行政处罚;3:行政强制;4:行政许可;5:行政征收;6:行政征用)", required = true)
Integer itemType,
HttpServletRequest request,
String userCode) throws Exception {
return excelService.upload(file, itemType, userCode);
}
}
2、Service:
public interface IExcelService {
/**
* 导入六大类数据
*
* @param file
* @throws IOException
*/
ResultBean upload(MultipartFile file, Integer itemType, String userCode) throws Exception;
}
3、ExcelServiceImpl:
@Service
@Slf4j
@Transactional
public class ExcelServiceImpl implements IExcelService {
//此处自行根据自己的业务添加
@Autowired
InsService insService;
@Override
public ResultBean upload(MultipartFile file, Integer itemType, String userCode) throws Exception {
List<String> errorInfos = new ArrayList();
log.info("ContentType>>>>>>>>>>>>>>>>>>>>>>>>>>>" + file.getContentType() + ", OriginalFilename>>>>>>>>>>>>>>>>>>>>>>>>>>>" + file.getOriginalFilename());
String fileType = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1);
log.info("导入的文件类型为>>>>>>>>>>>>>>>>>>>>>>>>>>>" + fileType.toLowerCase());
if ("zip".equals(fileType.toLowerCase())) {
String filePath = attachmentConfig.fileRootPath() + "Formwork" + File.separator + "temp" + File.separator + IdUtil.simpleUUID();
File fileZip = FileUtil.newFile(filePath + File.separator + IdUtil.simpleUUID() + ".zip");
FileUtil.touch(fileZip);
file.transferTo(fileZip);
File unzipZip = ZipUtil.unzip(fileZip, FileUtil.file(filePath), Charset.forName("GBK"));
try {
List<File> files = FileUtil.loopFiles(unzipZip);
File excelFile = null;
Map<String, File> fileMap = new HashMap<>();
for (File tempFile : files) {
String[] type = tempFile.getName().split("[.]");
if ("xlsx".equals(type[1].toLowerCase())) {
excelFile = tempFile;
System.out.println(excelFile.getName());
} else if ("pdf".equals(type[1].toLowerCase())) {
fileMap.put(type[0], tempFile);
}
}
ExcelUtil.read07BySax(excelFile, 0, checkRowHandler(errorInfos, itemType));
if (!errorInfos.isEmpty()) {
return ResultBean.fail("错误信息", errorInfos);
}
ExcelUtil.read07BySax(excelFile, 0, insertRowHandler(itemType, userCode, fileMap));
} catch (Exception e) {
throw new Exception(e);
} finally {
System.gc();
FileUtil.del(filePath);
}
} else if (AttachmentUtil.checkExcel(file)) {
ExcelUtil.read07BySax(file.getInputStream(), 0, checkRowHandler(errorInfos, itemType));
if (!errorInfos.isEmpty()) {
return ResultBean.fail("错误信息", errorInfos);
}
ExcelUtil.read07BySax(file.getInputStream(), 0, insertRowHandler(itemType, userCode, new HashMap<>()));
}
return ResultBean.ok("导入成功!");
}
/**
* 处理行数据
*
* @param errorInfos
* @return
*/
private RowHandler checkRowHandler(List<String> errorInfos, Integer itemType) {
return new RowHandler() {
@Override
public void handle(int sheetIndex, int rowIndex, List<Object> rowlist) {
try {
if (rowIndex >= 4) {//这里的4是因为模板中的第五行才是填的数据,excel从0行开始算起,后面附上模板,供大家参照
if (checkListNotEmpty(rowlist)) {
switch (itemType) {
// 行政检查
case 1:
insErrorHandler(errorInfos, rowIndex, rowlist);
break;
case 2:
punishErrorHandler(errorInfos, rowIndex, rowlist);
break;
case 3:
enforceErrorHandler(errorInfos, rowIndex, rowlist);
break;
case 4:
allowErrorHandler(errorInfos, rowIndex, rowlist);
break;
case 5:
levyErrorHandler(errorInfos, rowIndex, rowlist);
break;
case 6:
requisitionErrorHandler(errorInfos, rowIndex, rowlist);
break;
}
}
}
} catch (Exception e) {
log.error("异常", e);
throw new RuntimeException(e);
}
}
};
}
//这里只写上其中一类的导入示例,先根据自己业务判断excel值的有效性(不必太在意里面的实现,只要知道这步是校验数据有效性即可)
/**
* 1行政检查错误信息
*
* @param rowIndex
* @param rowlist
* @return
*/
private List<String> insErrorHandler(List<String> errorInfos, int rowIndex, List<Object> rowlist) {
// 公共错误信息
publicErrorHandler(errorInfos, rowIndex, rowlist);
// 职权来源
if (StringUtils.isNotEmpty(toString(rowlist.get(17)))) {
AuthoritySrc authoritySrc = EnumUtil.descOf(AuthoritySrc.class, toString(rowlist.get(17)));
if (authoritySrc == null) {
errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行(错误码:" + RandomUtil.randomNumbers(4) + "),职权来源类型有误!");
}
if ("依委托".equals(rowlist.get(17))) {
if (StringUtils.isEmpty(toString(rowlist.get(18)))) {
errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行(错误码:" + RandomUtil.randomNumbers(4) + "),委托单位为空!");
} else if (getDept(toString(rowlist.get(18))) == null) {
errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行(错误码:" + RandomUtil.randomNumbers(4) + "),委托单位填写错误!");
}
}
} else {
errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行(错误码:" + RandomUtil.randomNumbers(4) + "),职权来源为空!");
}
if (StringUtils.isEmpty(toString(rowlist.get(12)))) {
errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行(错误码:" + RandomUtil.randomNumbers(4) + "),检查类别为空!");
}else {
CheckCat checkCat = EnumUtil.descOf(CheckCat.class, toString(rowlist.get(12)));
if (checkCat == null) {
errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行(错误码:" + RandomUtil.randomNumbers(4) + "),检查类别类型有误!");
}
}
if (StringUtils.isEmpty(toString(Convert.toDate(rowlist.get(13))))) {
errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行(错误码:" + RandomUtil.randomNumbers(4) + "),检查日期为空!");
} else {
//String format = DateUtil.format (DateUtil.parseDate (Convert.toStr (rowlist.get (13))), "yyyy/MM/dd");
DateTime dateTime = DateUtil.parseDate(Convert.toStr(rowlist.get(13)));
String format = DateUtil.format(dateTime, "yyyy/MM/dd");
String checkYear = DateUtil.format(dateTime, "yyyy");
if (checkYear.equals("1900") || !checkDate(format)) {
errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行(错误码:" + RandomUtil.randomNumbers(4) + "),检查日期格式有误(正确示例:2019/01/01)!");
}
}
if (StringUtils.isEmpty(toString(rowlist.get(14)))) {
errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行(错误码:" + RandomUtil.randomNumbers(4) + "),检查内容为空!");
}
if (StringUtils.isEmpty(toString(rowlist.get(15)))) {
errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行(错误码:" + RandomUtil.randomNumbers(4) + "),检查结果为空!");
}
if (StringUtils.isEmpty(toString(rowlist.get(16)))) {
errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行(错误码:" + RandomUtil.randomNumbers(4) + "),检查决定为空!");
}else {
CheckDecision checkDecision = EnumUtil.descOf(CheckDecision.class, toString(rowlist.get(16)));
if (checkDecision == null) {
errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行(错误码:" + RandomUtil.randomNumbers(4) + "),检查决定类型有误!");
}
}
return errorInfos;
}
//校验完成通过后,就是具体的插入数据的操作了
/**
* 1行政检查插入数据库
*
* @param rowlist
*/
@SuppressWarnings("Duplicates")
private void insValueHandler(List<Object> rowlist, Integer itemType, String userCode, Map<String, File> fileMap) throws Exception {
InsModel insModel = new InsModel();
insModel.setUserCode(userCode);
// 行政相对人名称、类别
insModel.setAdmCounterName(toString(rowlist.get(1)));
insModel.setAdmCounterCategory(EnumUtil.descOf(AdmCounterCategory.class, toString(rowlist.get(2))));
// 自然人
if (AdmCounterCategory.NATURAL_PERSON.getCode().equals(insModel.getAdmCounterCategory().getCode())) {
insModel.setNaturalPersonModel(setNature(rowlist, itemType));
} else {
insModel.setLegalPersonModel(setLegal(rowlist, itemType));
}
// 案件名称、决定书(通知书)名称、决定书(通知书)文号
insModel.setCaseName(toString(rowlist.get(9)));
insModel.setDecisionName(toString(rowlist.get(10)));
insModel.setDecisionNum(toString(rowlist.get(11)));
// 职权来源
insModel.setAuthoritySrc(EnumUtil.descOf(AuthoritySrc.class, toString(rowlist.get(17))));
if (AuthoritySrc.ENTRUSTED.getCode().equals(insModel.getAuthoritySrc().getCode())) {
DeptMainModel dept = getDept(toString(rowlist.get(18)));
insModel.setEntrustDept(dept.getId());
}
// 检查类型、日期
insModel.setCheckCat(EnumUtil.descOf(CheckCat.class, toString(rowlist.get(12))));
insModel.setCheckDate(Convert.toDate(rowlist.get(13)));
// 检查内容、结果、决定
insModel.setCheckContent(toString(rowlist.get(14)));
insModel.setCheckResult(toString(rowlist.get(15)));
insModel.setCheckDecision(EnumUtil.descOf(CheckDecision.class, toString(rowlist.get(16))));
insModel.setStatus(EnumUtil.codeOf(Status.class, "1"));
insModel.setRemark(toString(rowlist.get(19)));
ResultBean<String> insert = insService.insert(insModel);//这里就是插入数据库的操作,具体代码就不写上了
if (ResultCode.BADREQUEST.equals(insert.getStatus())) {
log.error("异常", insModel.getCaseName() + insert.getDesc());
throw new RuntimeException(insModel.getCaseName() + insert.getDesc());
}
//插入决定书附件
String fileName = toString(rowlist.get(20));
String apprAdmFileId = null;
if (StringUtils.isNotEmpty(fileName)) {
File pdfFile = fileMap.get(fileName);
apprAdmFileId = saveFile(pdfFile, userCode, "1", insModel.getId());
}
excelDao.update("law_collection.appr_adm_ins", insModel.getId(), apprAdmFileId);
//插入操作痕迹
markService.insertMark(Mark.init(userCode, MarkTypeEnum.IMPORT, insModel.getId()));
}
//下面的是使用到的私有方法(不必在意具体实现,根据各自实际业务而定)
/**
* 公共错误信息
*
* @param rowIndex
* @param rowlist
* @return
*/
private List<String> publicErrorHandler(List<String> errorInfos, int rowIndex, List<Object> rowlist) {
if (StringUtils.isEmpty(toString(rowlist.get(1)))) {
errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行(错误码:" + RandomUtil.randomNumbers(4) + "),行政相对人名称为空!");
}
// 行政相对人类别
AdmCounterCategory admCounterCategory = EnumUtil.descOf(AdmCounterCategory.class, toString(rowlist.get(2)));
if (admCounterCategory == null) {
errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行(错误码:" + RandomUtil.randomNumbers(4) + "),行政相对人类别有误!");
}else {
if (AdmCounterCategory.ORGANIZATION.getCode().equals(admCounterCategory.getCode()) || AdmCounterCategory.BUSINESSMEN.getCode().equals(admCounterCategory.getCode())) {
if (StringUtils.isEmpty(toString(rowlist.get(4)))) {
errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行(错误码:" + RandomUtil.randomNumbers(4) + "),法定代表人为空!");
}
if (StringUtils.isNotEmpty(toString(rowlist.get(5)))) {
LegalCardType legalCardType = EnumUtil.descOf(LegalCardType.class, toString(rowlist.get(5)));
if (legalCardType == null) {
errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行(错误码:" + RandomUtil.randomNumbers(4) + "),法定代表人证件类型有误!");
}
}
}else {
if (StringUtils.isNotEmpty(toString(rowlist.get(7)))) {
LegalCardType legalCardType = EnumUtil.descOf(LegalCardType.class, toString(rowlist.get(7)));
if (legalCardType == null) {
errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行(错误码:" + RandomUtil.randomNumbers(4) + "),自然人证件类型有误!");
}
}
}
}
if (StringUtils.isEmpty(toString(rowlist.get(9)))) {
errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行(错误码:" + RandomUtil.randomNumbers(4) + "),案件名称为空!");
}
return errorInfos;
}
private Boolean checkListNotEmpty(List<Object> list) {
boolean flag = false;
for (int i = 1; i < list.size(); i++) {
if (StrUtil.isNotEmpty(Convert.toStr(list.get(i)))) {
flag = true;
break;
}
}
return flag;
}
/**
* 字符串判断
*
* @param obj
* @return
*/
private static String toString(Object obj) {
return null == obj ? "" : obj.toString();
}
/**
* 获取委托单位数据
*
* @param unitFullName
*/
private DeptMainModel getDept(String unitFullName) {
return deptMainDao.get(unitFullName);
}
/**
* 自然人设置
*
* @param rowlist
* @param itemType
* @return
*/
private NaturalPersonModel setNature(List<Object> rowlist, Integer itemType) {
NaturalPersonModel naturalPersonModel = new NaturalPersonModel();
// 自然人证件号码、证件类型
naturalPersonModel.setCardNumber(toString(rowlist.get(8)));
naturalPersonModel.setCardType(EnumUtil.descOf(LegalCardType.class, toString(rowlist.get(7))));
// 行政案件类型
naturalPersonModel.setAdmCaseType(EnumUtil.codeOf(AdmCaseType.class, toString(itemType)));
return naturalPersonModel;
}
/**
* 法人及其他人、个体工商户的设置
*
* @param rowlist
* @param itemType
* @return
*/
private LegalPersonModel setLegal(List<Object> rowlist, Integer itemType) {
LegalPersonModel legalPersonModel = new LegalPersonModel();
// 统一社会信用代码
legalPersonModel.setCreditCode(toString(rowlist.get(3)));
// 法定代表人、证件类型、证件号码
legalPersonModel.setLegalRep(toString(rowlist.get(4)));
legalPersonModel.setLegalCardType(EnumUtil.descOf(LegalCardType.class, toString(rowlist.get(5))));
legalPersonModel.setLegalCardNumber(toString(rowlist.get(6)));
// 行政案件类型
legalPersonModel.setAdmCaseType(EnumUtil.codeOf(AdmCaseType.class, toString(itemType)));
return legalPersonModel;
}
public static boolean checkDate(String str) {
SimpleDateFormat sd = new SimpleDateFormat("yyyy/MM/dd");//括号内为日期格式,y代表年份,M代表年份中的月份(为避免与小时中的分钟数m冲突,此处用M),d代表月份中的天数
try {
sd.setLenient(false);//此处指定日期/时间解析是否不严格,在true是不严格,false时为严格
sd.parse(str);//从给定字符串的开始解析文本,以生成一个日期
} catch (Exception e) {
return false;
}
return true;
}
}
页面效果:
点击上传即可!
附上模板zip的结构图(本人暂时没发现CSDN可以上传zip文件供大家下载),或者excel,其实zip里面也是含excel,只不过我们的业务中还要有附件pdf,没有附件的可以不管,直接上传excel。
有pdf附件的就放在附件的文件夹里,excel放在数据的文件夹里。
下面展示模板的内容:
一行不够展示,分两张截取图,后面的:
到这里就完成了POI导入excel或zip文件,最后提醒小伙伴们自己测试时要根据自己项目的实际业务出发,避免卡在我的某一处业务代码上。好了,有问题记得留言哦。