springboot实现excel的导入导出(搬运)used
https://blog.csdn.net/qq_35859844/article/details/88365095
ExcelUtil工具类
package com.hainei.common.utils; import java.io.*; import java.lang.reflect.Field; import java.text.DateFormat; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.*; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.formula.functions.T; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; /** * @Author wq * @Date 2020/4/7 * @Time 14:11 *@Description xls、xlsx Excel导入导出 * @return **/ public class ExcelUtil { /** * 导出多个sheet的excel * @param name * @param mapList * @param response * @param <T> */ public static <T> void exportMultisheetExcel(String name, List<Map> mapList, HttpServletResponse response) { BufferedOutputStream bos = null; try { String fileName = name + ".xlsx"; bos = getBufferedOutputStream(fileName, response); doExport(mapList, bos); } catch (Exception e) { e.printStackTrace(); } finally { try { if (bos != null) { bos.close(); } } catch (IOException e) { e.printStackTrace(); } } } /** * 从excel中读内容 * @param filePath * @param sheetIndex * @return */ //public static List<Map<String, String>> readExcel(String filePath, Integer sheetIndex) { public static List<Map<String, String>> readExcel(MultipartFile filePath, Integer sheetIndex) throws IOException { List<Map<String, String>> dataList = new ArrayList<>(); //将文件转成workbook类型 Workbook wb = buildWorkbook(filePath); //Workbook wb = ExcelUtil.createWorkBook(filePath); if (wb != null) { Sheet sheet = wb.getSheetAt(sheetIndex); int maxRownum = sheet.getPhysicalNumberOfRows(); Row firstRow = sheet.getRow(0); int maxColnum = firstRow.getPhysicalNumberOfCells(); String columns[] = new String[maxColnum]; for (int i = 0; i < maxRownum; i++) { Map<String, String> map = null; if (i > 0) { map = new LinkedHashMap<>(); firstRow = sheet.getRow(i); } if (firstRow != null) { String cellData = null; for (int j = 0; j < maxColnum; j++) { cellData = (String) ExcelUtil.getCellFormatValue(firstRow.getCell(j)); if (i == 0) { columns[j] = cellData; } else { map.put(columns[j], cellData); } } } else { break; } if (i > 0) { dataList.add(map); } } } return dataList; } private static BufferedOutputStream getBufferedOutputStream(String fileName, HttpServletResponse response) throws Exception { response.setContentType("application/x-msdownload"); response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1")); return new BufferedOutputStream(response.getOutputStream()); } private static <T> void doExport(List<Map> mapList, OutputStream outputStream) { int maxBuff = 100; // 创建excel工作文本，100表示默认允许保存在内存中的行数 SXSSFWorkbook wb = new SXSSFWorkbook(maxBuff); try { for (int i = 0; i < mapList.size(); i++) { Map map = mapList.get(i); String[] headers = (String[]) map.get("headers"); Collection<T> dataList = (Collection<T>) map.get("dataList"); String fileName = (String) map.get("fileName"); createSheet(wb, null, headers, dataList, fileName, maxBuff); } if (outputStream != null) { wb.write(outputStream); } } catch (Exception ex) { ex.printStackTrace(); } } private static <T> void createSheet(SXSSFWorkbook wb, String[] exportFields, String[] headers, Collection<T> dataList, String fileName, int maxBuff) throws NoSuchFieldException, IllegalAccessException, IOException { Sheet sh = wb.createSheet(fileName); CellStyle style = wb.createCellStyle(); CellStyle style2 = wb.createCellStyle(); //创建表头 Font font = wb.createFont(); font.setFontName("微软雅黑"); font.setFontHeightInPoints((short) 11);//设置字体大小 style.setFont(font);//选择需要用到的字体格式 style.setFillForegroundColor(HSSFColor.YELLOW.index);// 设置背景色 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 style2.setFont(font);//选择需要用到的字体格式 style2.setFillForegroundColor(HSSFColor.WHITE.index);// 设置背景色 style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中 style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平向下居中 style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 style2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 style2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 Row headerRow = sh.createRow(0); //表头 int headerSize = headers.length; for (int cellnum = 0; cellnum < headerSize; cellnum++) { Cell cell = headerRow.createCell(cellnum); cell.setCellStyle(style); sh.setColumnWidth(cellnum, 4000); cell.setCellValue(headers[cellnum]); } int rownum = 0; Iterator<T> iterator = dataList.iterator(); while (iterator.hasNext()) { T data = iterator.next(); Row row = sh.createRow(rownum + 1); Field[] fields = getExportFields(data.getClass(), exportFields); for (int cellnum = 0; cellnum < headerSize; cellnum++) { Cell cell = row.createCell(cellnum); cell.setCellStyle(style2); Field field = fields[cellnum]; setData(field, data, field.getName(), cell); } rownum = sh.getLastRowNum(); // 大数据量时将之前的数据保存到硬盘 if (rownum % maxBuff == 0) { ((SXSSFSheet) sh).flushRows(maxBuff); // 超过100行后将之前的数据刷新到硬盘 } } } private static <T> void doExport(String[] headers, String[] exportFields, Collection<T> dataList, String fileName, OutputStream outputStream) { int maxBuff = 100; // 创建excel工作文本，100表示默认允许保存在内存中的行数 SXSSFWorkbook wb = new SXSSFWorkbook(maxBuff); try { createSheet(wb, exportFields, headers, dataList, fileName, maxBuff); if (outputStream != null) { wb.write(outputStream); } } catch (Exception ex) { ex.printStackTrace(); } } /** * 获取单条数据的属性 * * @param object * @param property * @param <T> * @return * @throws NoSuchFieldException * @throws IllegalAccessException */ private static <T> Field getDataField(T object, String property) throws NoSuchFieldException, IllegalAccessException { Field dataField; if (property.contains(".")) { String p = property.substring(0, property.indexOf(".")); dataField = object.getClass().getDeclaredField(p); return dataField; } else { dataField = object.getClass().getDeclaredField(property); } return dataField; } private static Field[] getExportFields(Class<?> targetClass, String[] exportFieldNames) { Field[] fields = null; if (exportFieldNames == null || exportFieldNames.length < 1) { fields = targetClass.getDeclaredFields(); } else { fields = new Field[exportFieldNames.length]; for (int i = 0; i < exportFieldNames.length; i++) { try { fields[i] = targetClass.getDeclaredField(exportFieldNames[i]); } catch (Exception e) { try { fields[i] = targetClass.getSuperclass().getDeclaredField(exportFieldNames[i]); } catch (Exception e1) { throw new IllegalArgumentException("无法获取导出字段", e); } } } } return fields; } /** * 根据属性设置对应的属性值 * * @param dataField 属性 * @param object 数据对象 * @param property 表头的属性映射 * @param cell 单元格 * @param <T> * @return * @throws IllegalAccessException * @throws NoSuchFieldException */ private static <T> void setData(Field dataField, T object, String property, Cell cell) throws IllegalAccessException, NoSuchFieldException { dataField.setAccessible(true); //允许访问private属性 Object val = dataField.get(object); //获取属性值 Sheet sh = cell.getSheet(); //获取excel工作区 CellStyle style = cell.getCellStyle(); //获取单元格样式 int cellnum = cell.getColumnIndex(); if (val != null) { if (dataField.getType().toString().endsWith("String")) { cell.setCellValue((String) val); } else if (dataField.getType().toString().endsWith("Integer") || dataField.getType().toString().endsWith("int")) { cell.setCellValue((Integer) val); } else if (dataField.getType().toString().endsWith("Long") || dataField.getType().toString().endsWith("long")) { cell.setCellValue(val.toString()); } else if (dataField.getType().toString().endsWith("Double") || dataField.getType().toString().endsWith("double")) { cell.setCellValue((Double) val); } else if (dataField.getType().toString().endsWith("Date")) { DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); cell.setCellValue(format.format((Date) val)); } else if (dataField.getType().toString().endsWith("List")) { List list1 = (List) val; int size = list1.size(); for (int i = 0; i < size; i++) { //加1是因为要去掉点号 int start = property.indexOf(dataField.getName()) + dataField.getName().length() + 1; String tempProperty = property.substring(start, property.length()); Field field = getDataField(list1.get(i), tempProperty); Cell tempCell = cell; if (i > 0) { int rowNum = cell.getRowIndex() + i; Row row = sh.getRow(rowNum); if (row == null) {//另起一行 row = sh.createRow(rowNum); //合并之前的空白单元格（在这里需要在header中按照顺序把list类型的字段放到最后，方便显示和合并单元格） for (int j = 0; j < cell.getColumnIndex(); j++) { sh.addMergedRegion(new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex() + size - 1, j, j)); Cell c = row.createCell(j); c.setCellStyle(style); } } tempCell = row.createCell(cellnum); tempCell.setCellStyle(style); } //递归传参到单元格并获取偏移量（这里获取到的偏移量都是第二层后list的偏移量） setData(field, list1.get(i), tempProperty, tempCell); } } else { if (property.contains(".")) { String p = property.substring(property.indexOf(".") + 1, property.length()); Field field = getDataField(val, p); setData(field, val, p, cell); } else { cell.setCellValue(val.toString()); } } } } private static Workbook createWorkBook(String filePath) { Workbook wb = null; if (filePath == null) { return null; } String extString = filePath.substring(filePath.lastIndexOf(".")); InputStream is = null; try { is = new FileInputStream(filePath); if (".xls".equals(extString)) { return wb = new HSSFWorkbook(is); } else if (".xlsx".equals(extString)) { return wb = new XSSFWorkbook(is); } else { return wb; } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return wb; } /** * 将字段转为相应的格式 * @param cell * @return */ private static Object getCellFormatValue(Cell cell) { Object cellValue = null; if (cell != null) { //判断cell类型 switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: { DecimalFormat df=new DecimalFormat("0"); String a = df.format(cell.getNumericCellValue()); // String s = String.valueOf(cell.getNumericCellValue()); //String substring = s.substring(0, s.lastIndexOf(".")); cellValue =a; break; } case Cell.CELL_TYPE_FORMULA: { if (DateUtil.isCellDateFormatted(cell)) { cellValue = cell.getDateCellValue();////转换为日期格式YYYY-mm-dd } else { cellValue = String.valueOf(cell.getNumericCellValue()); //数字 } break; } case Cell.CELL_TYPE_STRING: { cellValue = cell.getRichStringCellValue().getString(); break; } default: cellValue = ""; } } else { cellValue = ""; } return cellValue; } //类型转换 private static Workbook buildWorkbook(MultipartFile file) throws IOException { String filename = file.getOriginalFilename(); if (filename.endsWith(".xls")) { return new HSSFWorkbook(file.getInputStream()); } else if (filename.endsWith(".xlsx")) { return new XSSFWorkbook(file.getInputStream()); } else { throw new IOException("unknown file format: " + filename); } } }
测试一下
/** * @Author: guandezhi * @Date: 2019/3/9 11:18 */ @Slf4j @RestController @RequestMapping("/excel") public class ExcelController { @RequestMapping(value = "/exportExcel") public String exportExcel(HttpServletResponse response) throws Exception { String[] headers = {"姓名", "性别", "年龄", "学校", "班级"}; String fileName = "学生表"; List<Student> studentList = new ArrayList<>(); Student student = new Student(); student.setStudentName("guandezhi"); student.setGrade("三年二班"); student.setAge(20); student.setSchool("XX大学"); student.setSex("男"); studentList.add(student); Map<String, Object> studentMap = new HashMap(); studentMap.put("headers", headers); studentMap.put("dataList", studentList); studentMap.put("fileName", fileName); List<Map> mapList = new ArrayList(); mapList.add(studentMap); ExcelUtil.exportMultisheetExcel(fileName, mapList, response); return "success"; } @RequestMapping(value = "/readExcel") public String readExcel() throws Exception { String filePath = "F:\\student.xls"; List<Map<String, String>> mapList = ExcelUtil.readExcel(filePath, 0); log.info("mapList:" + mapList); return "success"; } }
其中的student类
/** * @Author: guandezhi * @Date: 2019/3/7 9:53 */ @Data public class Student { private String studentName; private String sex; private Integer age; private String school; private String grade; }
pom.xml
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.2.6.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.zhetang</groupId> <artifactId>exceldemo</artifactId> <version>0.0.1-SNAPSHOT</version> <name>exceldemo</name> <description>Demo project for Spring Boot</description> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> <version>2.2.6.RELEASE</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> <exclusions> <exclusion> <groupId>org.junit.vintage</groupId> <artifactId>junit-vintage-engine</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.13</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.13</version> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>commons-collections</groupId> <artifactId>commons-collections</artifactId> <version>3.2.2</version> </dependency> <dependency> <groupId>org.codehaus.jackson</groupId> <artifactId>jackson-core-asl</artifactId> <version>1.9.13</version> </dependency> <dependency> <groupId>org.codehaus.jackson</groupId> <artifactId>jackson-mapper-asl</artifactId> <version>1.9.13</version> </dependency> <!-- Jackson --> <dependency> <groupId>com.fasterxml.jackson.datatype</groupId> <artifactId>jackson-datatype-guava</artifactId> <version>2.5.3</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.28</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.12</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-web</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-annotation</artifactId> <version>3.2.0</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
排放点导入到数据库
package com.hainei.service.impl.ldar; import com.github.pagehelper.PageHelper; import com.hainei.common.enums.YesOrNo; import com.hainei.common.exception.LdarException; import com.hainei.common.exception.code.LdarResponseCode; import com.hainei.common.utils.ExcelUtil; import com.hainei.common.utils.PageUtil; import com.hainei.common.utils.PageVO; import com.hainei.mapper.ldar.*; import com.hainei.pojo.bo.ldar.DrainBO; import com.hainei.pojo.bo.ldar.DrainRepairBO; import com.hainei.pojo.bo.ldar.DrainValueBO; import com.hainei.pojo.model.ldar.*; import com.hainei.service.ldar.DrainService; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.BeanUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import org.springframework.web.multipart.MultipartFile; import tk.mybatis.mapper.entity.Example; import java.util.*; /** * Created with IntelliJ IDEA. * User：wq * Date：2020/4/2 * Time: 16:55 * Description: 排放点 */ @Service @Slf4j public class DrainServiceImpl implements DrainService { @Autowired private LdarDrainMapper ldarDrainMapper; @Autowired private LdarEquipmentMapper ldarEquipmentMapper; @Override public void saveDrain(DrainBO drainBO) { LdarDrain entity = new LdarDrain(); BeanUtils.copyProperties(drainBO,entity); entity.setId(UUID.randomUUID().toString().replace("-","")); entity.setGmtCreatedOn(new Date()); Example example = new Example(LdarDrain.class); Example.Criteria criteria = example.createCriteria(); criteria.andEqualTo("name",entity.getName()); List<LdarDrain> ldarDrains = ldarDrainMapper.selectByExample(example); if (ldarDrains.size()>0){ throw new LdarException(LdarResponseCode.DATA_ALREADY_EXIST); }else{ ldarDrainMapper.insertSelective(entity); } } @Override public void updateDrain(DrainBO drainBO) { LdarDrain entity = new LdarDrain(); BeanUtils.copyProperties(drainBO,entity); entity.setGmtCreatedOn(new Date()); ldarDrainMapper.updateByPrimaryKeySelective(entity); } @Override public void deleteDrain(List<String> ids) { LdarDrain ldarDrain = new LdarDrain(); ldarDrain.setIsDeleted(YesOrNo.YES.type); Example example = new Example(LdarDrain.class); Example.Criteria criteria = example.createCriteria(); criteria.andIn("id",ids); ldarDrainMapper.updateByExampleSelective(ldarDrain,example); } @Override public PageVO<LdarDrain> listDrain(Integer pageNum, Integer pageSize) { PageHelper.startPage(pageNum,pageSize); Example example = new Example(LdarDrain.class); example.orderBy("gmtCreatedOn").desc(); Example.Criteria criteria = example.createCriteria(); criteria.andEqualTo("isDeleted",YesOrNo.NO.type); List<LdarDrain> ldarDrains = ldarDrainMapper.selectByExample(example); PageVO<LdarDrain> pageVO = PageUtil.getPageVO(ldarDrains); return pageVO; } @Override public LdarDrain getById(String id) { LdarDrain ldarDrain = ldarDrainMapper.selectByPrimaryKey(id); return ldarDrain; } @Override public PageVO<LdarDrain> getByEquipmentId(Integer pageNum, Integer pageSize, String id) { PageHelper.startPage(pageNum,pageSize); List<LdarDrain> byEquipment = ldarDrainMapper.getByEquipment(id); PageVO<LdarDrain> pageVO = PageUtil.getPageVO(byEquipment); return pageVO; } @Override public void updateRecord(DrainValueBO drainValueBO) { ldarDrainMapper.updateRecord(drainValueBO); } @Override public void updateRepair(DrainRepairBO drainRepairBO) { ldarDrainMapper.updateRepair(drainRepairBO); } @Transactional @Override public void importExecl(MultipartFile filePath)throws Exception { List<Map<String, String>> mapList = ExcelUtil.readExcel(filePath, 0); log.info("mapList:" + mapList); for (Map<String, String> map : mapList) { if (map.get("所在生产区") != null && map.get("所在装置") !=""&& map.get("所在设备") != null&& map.get("所属公司") != null&& map.get("排放点名称") != null) { Example example = new Example(LdarEquipment.class); Example.Criteria criteria = example.createCriteria(); criteria.andEqualTo("name",map.get("所在设备")); criteria.andEqualTo("firm",map.get("所属公司")); criteria.andEqualTo("produceArea",map.get("所在生产区")); criteria.andEqualTo("device",map.get("所在装置")); List<LdarEquipment> ldarEquipments = ldarEquipmentMapper.selectByExample(example); if(ldarEquipments.size()>0){ for (LdarEquipment equipment: ldarEquipments) { if(equipment.getName().equals(map.get("所在设备"))){ Example example1 = new Example(LdarDrain.class); Example.Criteria criteria1= example1.createCriteria(); criteria1.andEqualTo("equipmentName",map.get("所在设备")); criteria1.andEqualTo("firm",map.get("所属公司")); criteria1.andEqualTo("produceArea",map.get("所在生产区")); criteria1.andEqualTo("deviceName",map.get("所在装置")); criteria1.andEqualTo("name",map.get("排放点名称")); List<LdarDrain> ldarDrains = ldarDrainMapper.selectByExample(example1); if(ldarDrains.size()>0){ break; }else{ String id = UUID.randomUUID().toString().replace("-", ""); LdarDrain entity = new LdarDrain(id,map.get("排放点名称"),map.get("所属公司"), map.get("所在生产区"),map.get("所在装置"),map.get("所在设备"),map.get("排放点类型"), map.get("介质"),map.get("其他信息"),new Date()); ldarDrainMapper.insertSelective(entity); } } } }else{ throw new LdarException(LdarResponseCode.ADD_ERROR); } } else { throw new LdarException(LdarResponseCode.DATA_ERROR); /* Example example = new Example(LdarProduce.class); Example.Criteria criteria = example.createCriteria(); criteria.andEqualTo("produceName",map.get("所在生产区")); criteria.andEqualTo("firm",map.get("所属公司")); if(ldarProduceMapper.selectByExample(example)!=null){ }else{ LdarProduce ldarProduce = new LdarProduce(); ldarProduceMapper.insertSelective() }*/ } } } }
下载
package com.hainei.service.impl.ldar; import com.hainei.common.exception.BusinessException; import com.hainei.common.exception.LdarException; import com.hainei.common.exception.code.LdarResponseCode; import com.hainei.common.utils.CommonUtils; import com.hainei.common.utils.DataResult; import com.hainei.common.utils.ExcelUtil; import com.hainei.mapper.ldar.*; import com.hainei.pojo.model.ldar.LdarDevice; import com.hainei.pojo.model.ldar.LdarDrain; import com.hainei.pojo.model.ldar.LdarEquipment; import com.hainei.pojo.model.ldar.LdarProduce; import com.hainei.service.ldar.ExcelService; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.web.multipart.MultipartFile; import tk.mybatis.mapper.entity.Example; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.*; import java.util.Date; import java.util.List; import java.util.Map; import java.util.UUID; /** * Created with IntelliJ IDEA. * User：wq * Date：2020/4/8 * Time: 17:00 * Description: No Description */ @Service @Slf4j public class ExcelServiceImpl implements ExcelService { @Autowired private LdarDrainMapper ldarDrainMapper; @Autowired private LdarFirmMapper ldarFirmMapper; @Autowired private LdarProduceMapper ldarProduceMapper; @Autowired private LdarDeviceMapper ldarDeviceMapper; @Autowired private LdarEquipmentMapper ldarEquipmentMapper; @Override public DataResult download(HttpServletRequest request, HttpServletResponse response) throws UnsupportedEncodingException { String fileName = "模板.zip"; if (fileName != null){ // String realPath = "E:\\海内demo集\\海内\\模板\\少量数据"; String realPath = "E:\\海内demo集\\海内"; File file = new File(realPath,fileName); fileName = new String(file.getName().getBytes("utf-8")); String suffixNmae = fileName.substring(fileName.lastIndexOf(".")); String name = CommonUtils.generateUUID().toString(); fileName = name + suffixNmae; if (file.exists()){ response.setContentType("application/force-download"); response.addHeader("Content-Disposition","attachment;fileName="+fileName); byte[] buffer = new byte[1024]; FileInputStream fis = null; BufferedInputStream bis = null; try{ fis = new FileInputStream(file); bis = new BufferedInputStream(fis); OutputStream os = response.getOutputStream(); int i = bis.read(buffer); while(i != -1){ os.write(buffer,0,i); i = bis.read(buffer); } System.out.println("success"); }catch (Exception e){ e.printStackTrace(); }finally { if (bis != null){ try{ bis.close(); }catch (IOException e){ e.printStackTrace(); } } if (fis != null){ try{ fis.close(); }catch (IOException e){ e.printStackTrace(); } } } } } System.out.println(fileName); return DataResult.success(fileName); } @Override public void importExeclProduce(MultipartFile filePath) throws Exception { List<Map<String, String>> mapList = ExcelUtil.readExcel(filePath, 0); log.info("mapList:" + mapList); for (Map<String, String> map : mapList) { if (map.get("生产区名称") != null && map.get("所属公司") !=""&& map.get("生产区状态") != null ) { Example example = new Example(LdarProduce.class); Example.Criteria criteria = example.createCriteria(); criteria.andEqualTo("produceName",map.get("生产区名称")); criteria.andEqualTo("firm",map.get("所属公司")); List<LdarProduce> ldarProduces = ldarProduceMapper.selectByExample(example); if(ldarProduces.size()>0){ for (LdarProduce produce: ldarProduces){ if(produce.getProduceName().equals(map.get("生产区名称"))) { Example example1 = new Example(LdarProduce.class); Example.Criteria criteria1 = example1.createCriteria(); criteria1.andEqualTo("produceName", map.get("生产区名称")); criteria1.andEqualTo("firm", map.get("所属公司")); List<LdarProduce> ldarProduces1 = ldarProduceMapper.selectByExample(example1); if (ldarProduces1.size() > 0) { break; } else { String id = UUID.randomUUID().toString().replace("-", ""); LdarProduce entity = new LdarProduce(id, map.get("生产区名称"), map.get("所属公司"), map.get("生产区状态"), map.get("管理者"), map.get("联系方式"), map.get("其他信息")); entity.setGmtCreatedOn(new Date()); ldarProduceMapper.insertSelective(entity); } } } }else{ String id = UUID.randomUUID().toString().replace("-", ""); LdarProduce entity = new LdarProduce(id,map.get("生产区名称"),map.get("所属公司"), map.get("生产区状态"),map.get("管理者"),map.get("联系方式"),map.get("其他信息")); entity.setGmtCreatedOn(new Date()); ldarProduceMapper.insertSelective(entity); } } else { //throw new Exception("关键字段为空"); throw new LdarException(LdarResponseCode.DATA_ERROR); } } } @Override public void importExeclDevice(MultipartFile filePath) throws Exception { List<Map<String, String>> mapList = ExcelUtil.readExcel(filePath, 0); log.info("mapList:" + mapList); for (Map<String, String> map : mapList) { if (map.get("装置状态") != null && map.get("所属公司") !="" && map.get("所在生产区") != null && map.get("装置名称")!=null) { Example example = new Example(LdarDevice.class); Example.Criteria criteria = example.createCriteria(); criteria.andEqualTo("name",map.get("装置名称")); criteria.andEqualTo("firm",map.get("所属公司")); criteria.andEqualTo("produceArea",map.get("所在生产区")); List<LdarDevice> ldarDevices = ldarDeviceMapper.selectByExample(example); if(ldarDevices.size()>0) { for (LdarDevice device : ldarDevices){ if (device.getName().equals(map.get("装置名称"))) { Example example1 = new Example(LdarDevice.class); Example.Criteria criteria1 = example1.createCriteria(); criteria1.andEqualTo("produceArea", map.get("所在生产区")); criteria1.andEqualTo("name", map.get("装置名称")); criteria1.andEqualTo("firm", map.get("所属公司")); List<LdarDevice> ldarDevices1 = ldarDeviceMapper.selectByExample(example1); if (ldarDevices1.size() > 0) { break; } else { String id = UUID.randomUUID().toString().replace("-", ""); LdarDevice entity = new LdarDevice(id, map.get("装置名称"), map.get("所属公司"), map.get("所在生产区"), map.get("装置状态"), map.get("管理者"), map.get("其他信息")); entity.setGmtCreatedOn(new Date()); ldarDeviceMapper.insertSelective(entity); } } } }else{ String id = UUID.randomUUID().toString().replace("-", ""); LdarDevice entity = new LdarDevice(id,map.get("装置名称"),map.get("所属公司"), map.get("所在生产区"),map.get("装置状态"),map.get("管理者"),map.get("其他信息")); entity.setGmtCreatedOn(new Date()); ldarDeviceMapper.insertSelective(entity); } } else { throw new LdarException(LdarResponseCode.DATA_ERROR); } } } @Override public void importExeclEquipment(MultipartFile filePath) throws Exception { List<Map<String, String>> mapList = ExcelUtil.readExcel(filePath, 0); log.info("mapList:" + mapList); for (Map<String, String> map : mapList) { if (map.get("设备名称") != null && map.get("所属公司") != "" && map.get("所在生产区") != null && map.get("所在装置")!=null&&map.get("设备状态")!=null) { Example example = new Example(LdarEquipment.class); Example.Criteria criteria = example.createCriteria(); criteria.andEqualTo("name",map.get("设备名称")); criteria.andEqualTo("firm",map.get("所属公司")); criteria.andEqualTo("produceArea",map.get("所在生产区")); criteria.andEqualTo("device",map.get("所在装置")); List<LdarEquipment> ldarEquipments = ldarEquipmentMapper.selectByExample(example); if(ldarEquipments.size()>0) { break; } else { String id = UUID.randomUUID().toString().replace("-", ""); LdarEquipment entity = new LdarEquipment(id, map.get("设备名称"), map.get("设备编码"), map.get("所属公司"), map.get("所在生产区"), map.get("所在装置"),map.get("设备状态"),map.get("管理者"), map.get("其他信息")); entity.setGmtCreatedOn(new Date()); ldarEquipmentMapper.insertSelective(entity); } }else { //throw new Exception("关键字段为空"); throw new LdarException(LdarResponseCode.DATA_ERROR); } } } }
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Springboot+Poi实现Excel的导入导出
个人学习笔记，记录日常学习，便于查阅及加深，仅为方便个人使用。
浙公网安备 33010602011771号