service:
/** * * <p>Title: importexcel</p> * <p>Description: 批量导入</p> * @author duwb * @param multipartFile * @return * @throws Exception */ @Syslog(desc = "批量导入",level = 1L,model = "产品服务") @Transactional(rollbackFor = RuntimeException.class) public Result importexcel(MultipartFile multipartFile) throws Exception { //缓存产品 List<Product> list =new ArrayList<Product>(); //缓存ES List<EsEntity> eslist =new ArrayList<>(); //缓存名字去重后数据 Set<String> distnicNameSet =new HashSet<>(); for (Map<String,Object> map : PoiUtil.importExcel(multipartFile, Map.class)) { Product product = new Product(); product.setProductName(map.get("产品名称").toString()); String[] arr = map.get("产品类型").toString().split("/"); if("软件类".equals(arr[0])){ product.setProductType(1); String softId = dictionariesService.getIdByTableNameAndName("soft_type", "name", arr[1]); product.setChildrenProductType(softId); }else if("硬件类".equals(arr[0])){ product.setProductType(2); String devTypeId = dictionariesService.getIdByTableNameAndName("situation_dev_type", "name", arr[1]); product.setChildrenProductType(devTypeId); }else{ return Result.error("产品类型错误!"); } product.setProductDesc(map.get("产品描述").toString()); product.setProductCompany(map.get("产品生产商").toString()); product.setProductVersion(map.get("产品版本号").toString()); distnicNameSet.add(product.getProductName()); list.add(product); //存储ES EsEntity esEntity = new EsEntity(); esEntity.setName(product.getProductName()); esEntity.setColFrom("0"); esEntity.setLppId(Long.valueOf(product.getId())); esEntity.setTableName("product"); StringBuilder content = new StringBuilder(); content.append(product.getProductName()).append("|").append(product.getProductDesc()).append("|").append(product.getProductCompany()); esEntity.setContent(content.toString()); eslist.add(esEntity); } //判断是否有重复名称的数据导入 Boolean res =distnicNameSet.size()==list.size()?true:false; if(res==false){ return Result.error("批量导入失败!有存在同名的产品!"); } int hundredSize = 100; int thousandSize = 1000; if(list.size() < hundredSize){ productExtMapper.batchInsert(list); }else if(hundredSize <= list.size() && list.size() <= thousandSize ){ List<List<Product>> list1 = PoiUtil.pagingList(list,hundredSize); for(List<Product> list2 : list1){ productExtMapper.batchInsert(list2); } }else if(list.size() > thousandSize){ List<List<Product>> list1 = PoiUtil.pagingList(list,thousandSize); for(List<Product> list2 : list1){ productExtMapper.batchInsert(list2); } } //批量保存Es esUtil.batchSave(eslist); return Result.ok("批量导入成功!"); } /** * * <p>Title: modelDownload</p> * <p>Description:模板下载</p> * @author duwb * @param response * @return * @throws Exception */ @Syslog(desc = "模板下载",level = 1L,model = "产品服务") public Result modelDownload(HttpServletResponse response) throws Exception { try { List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>(); entity.add(new ExcelExportEntity("产品名称", "productName")); entity.add(new ExcelExportEntity("产品类型", "productType")); entity.add(new ExcelExportEntity("产品描述", "productDesc")); entity.add(new ExcelExportEntity("产品生产商", "productCompany")); entity.add(new ExcelExportEntity("产品版本号", "productVersion")); List<Map<String,Object>> list = new ArrayList<Map<String,Object>>(); Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("产品信息", "产品信息表", ExcelType.XSSF), entity, list); PoiUtil.downLoadExcel("产品模板", response, workbook); } catch (Exception e) { return Result.error("下载产品模板失败!"); } return Result.ok("下载产品模板成功!"); }
productExtMapper
@Repository @Mapper public interface ProductExtMapper { /** * 功能描述: 批量加入 * @param item * @return: int */ int batchInsert(List<Product> item); }
productExtendMapper.xml:
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.sinux.dao.custome.ProductExtMapper"> <insert id="batchInsert"> insert into product (id,product_name,product_type,product_desc,product_company,product_version,children_product_type) values <foreach collection="list" item="item" separator=","> (#{item.id,jdbcType=VARCHAR},#{item.productName,jdbcType=VARCHAR},#{item.productType,jdbcType=BIGINT}, #{item.productDesc,jdbcType=VARCHAR}, #{item.productCompany,jdbcType=VARCHAR},#{item.productVersion,jdbcType=VARCHAR},#{item.childrenProductType,jdbcType=VARCHAR}) </foreach> </insert> </mapper>
easyPoi通过字段定义模板的下载:
/** * * <p>Title: modelDownload</p> * <p>Description:模板下载</p> * @param response * @return * @throws Exception */ @Syslog(desc = "模板下载",level = 1L,model = "产品服务") public Result modelDownload(HttpServletResponse response) throws Exception { try { List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>(); entity.add(new ExcelExportEntity("产品名称", "productName")); entity.add(new ExcelExportEntity("产品类型", "productType")); entity.add(new ExcelExportEntity("产品描述", "productDesc")); entity.add(new ExcelExportEntity("产品生产商", "productCompany")); entity.add(new ExcelExportEntity("产品版本号", "productVersion")); List<Map<String,Object>> list = new ArrayList<Map<String,Object>>(); Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("产品信息", "产品信息表", ExcelType.XSSF), entity, list); PoiUtil.downLoadExcel("产品模板", response, workbook); } catch (Exception e) { return Result.error("下载产品模板失败!"); } return Result.ok("下载产品模板成功!"); }
模板如下
PoiUtil工具类
/** * @ClassName PoiUtil * @Description * @Version V1.0 **/ @Component @Slf4j public class PoiUtil { /** * @title exportExcel * @param list * @param title * @param sheetName * @param pojoClass * @param fileName * @param isCreateHeader * @param response * @description * @return void * @throws */ public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException { ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF); exportParams.setCreateHeadRows(isCreateHeader); defaultExport(list, pojoClass, fileName, response, exportParams); } /** * @title exportExcel * @param list * @param title * @param sheetName * @param pojoClass * @param fileName * @param response * @description * @return void * @throws */ public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException { defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF)); } /** * @title exportExcel * @param list * @param pojoClass * @param fileName * @param exportParams * @param response * @description * @return void * @throws */ public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException { defaultExport(list, pojoClass, fileName, response, exportParams); } /** * @title exportExcel * @param list * @param fileName * @param response * @description * @return void * @throws */ public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException { defaultExport(list, fileName, response); } /** * @title defaultExport * @param list * @param pojoClass * @param fileName * @param response * @param exportParams * @description * @return void * @throws */ private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException { Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list); downLoadExcel(fileName, response, workbook); } /** * @title defaultExport * @param list * @param fileName * @param response * @description * @return void * @throws */ private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException { Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF); downLoadExcel(fileName, response, workbook); } /** * @title downLoadExcel * @param fileName * @param response * @param workbook * @description * @return void * @throws */ public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException { try { response.setCharacterEncoding("UTF-8"); response.setHeader("content-Type", "application/vnd.ms-excel;charset=UTF-8"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8")); workbook.write(response.getOutputStream()); }catch (Exception e) { throw new IOException(e.getMessage()); } } /** * @title importExcel * @param filePath * @param titleRows * @param headerRows * @param pojoClass * @description * @return java.util.List<T> * @throws */ public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException { if (StringUtils.isBlank(filePath)) { return null; } ImportParams params = new ImportParams(); params.setTitleRows(titleRows); params.setHeadRows(headerRows); params.setNeedSave(true); params.setSaveUrl("/excel/"); try { return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params); } catch (NoSuchElementException e) { throw new IOException("模板不能为空"); } catch (Exception e) { throw new IOException(e.getMessage()); } } /** * @title importExcel * @param file * @param pojoClass * @description * @return java.util.List<T> * @throws */ public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException { return importExcel(file, 1, 1, pojoClass); } /** * @title importExcel * @param file * @param titleRows * @param headerRows * @param pojoClass * @description * @return java.util.List<T> * @throws */ public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException { return importExcel(file, titleRows, headerRows, false, pojoClass); } /** * @title importExcel * @param file * @param titleRows * @param headerRows * @param needVerfiy * @param pojoClass * @description * @return java.util.List<T> * @throws */ public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException { if (file == null) { return null; } try { return importExcel(file.getInputStream(), titleRows, headerRows, needVerfiy, pojoClass); } catch (Exception e) { throw new IOException(e.getMessage()); } } /** * @title importExcel * @param inputStream * @param titleRows * @param headerRows * @param needVerfiy * @param pojoClass * @description * @return java.util.List<T> * @throws */ public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException { if (inputStream == null) { return null; } ImportParams params = new ImportParams(); params.setTitleRows(titleRows); params.setHeadRows(headerRows); params.setSaveUrl("/excel/"); params.setNeedSave(true); // params.setNeedVerfiy(needVerfiy); try { return ExcelImportUtil.importExcel(inputStream, pojoClass, params); } catch (NoSuchElementException e) { throw new IOException("excel文件不能为空"); } catch (Exception e) { throw new IOException(e.getMessage()); } } /** * @title helpDownload * @param response * @param modInfoService * @param type * @param title * @param sheetName * @param fileName * @description 辅助模板下载方法 * @return void * @throws */ public static void helpDownload(HttpServletResponse response, ModInfoService modInfoService, String type, String title, String sheetName, String fileName) throws IOException { Query query = new Query(); query.put("type",type); List<JSONObject> cloumns = modInfoService.getExcludeAutoCloumnModelAttr(query); List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>(); //构造对象等同于@Excel for (JSONObject jsonObject : cloumns) { if ("id".equals(jsonObject.getString("prop"))) { continue; } entity.add(new ExcelExportEntity(jsonObject.getString("label"), jsonObject.getString("prop"))); } List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); //生成EXCEL模板 Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(title, sheetName, ExcelType.XSSF), entity, list); PoiUtil.downLoadExcel(fileName, response, workbook); } /** * @title pagingList * @param list * @param pageSize * @description 辅助批量导入方法 * @return java.util.List<java.util.List<T>> * @throws */ public static <T> List<List<T>> pagingList(List<T> list, int pageSize) { int length = list.size(); int num = (length + pageSize - 1) / pageSize; List<List<T>> newList = new ArrayList<>(); for (int i = 0; i < num; i++) { int fromIndex = i * pageSize; int toIndex = (i + 1) * pageSize < length ? (i + 1) * pageSize : length; newList.add(list.subList(fromIndex, toIndex)); } return newList; } public <T> List<T> getSame(ArrayList<T> arrayListTemp,ArrayList<T> arrayListTempOne){ ArrayList<T> arrayListResult=new ArrayList<>(); for(T t:arrayListTemp){ for(T tOne:arrayListTempOne){ if(tOne.equals(t)){ arrayListResult.add(tOne); } } } return arrayListResult; } /** * @title ExcelTypeEnum * @param * @description EXCEL类型枚举 * @return * @throws */ enum ExcelTypeEnum { /*** * EXCEL枚举 */ XLS("xls"), XLSX("xlsx"); private String value; ExcelTypeEnum(String value) { this.value = value; } public String getValue() { return value; } public void setValue(String value) { this.value = value; } } }
spingboot集成easypoi实现excel批量导入功能
最新推荐文章于 2024-04-19 17:21:09 发布