线程池（五）----多线程校验导入的excel数据并给错误的单元格加标注

怼烎@ 2023-06-24 04:53 7阅读 0赞

一、需求：如这样的模板：

![2019122017142519.png][]

填写用户名和年龄，后台判断长度小于2视为错误数据，现需要把填写正确的数据行录入数据库，把错误行标红并加批注说明错误的原因：

![2019122017162770.png][]

二、代码：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dfdF95X3k_size_16_color_FFFFFF_t_70][]

![20191220171931614.png][]

1、pom:

<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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
      <modelVersion>4.0.0</modelVersion>
      <groupId>com.demo</groupId>
      <artifactId>import-excel</artifactId>
      <version>0.0.1-SNAPSHOT</version>
      <packaging>war</packaging>
    
      <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.4.1.RELEASE</version>
      </parent>
      <dependencies>
        <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
    
        <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml</artifactId>
          <version>3.12</version>
        </dependency>
        <dependency>
          <groupId>org.apache.commons</groupId>
          <artifactId>commons-lang3</artifactId>
          <version>3.4</version>
        </dependency>
        <dependency>
          <groupId>commons-collections</groupId>
          <artifactId>commons-collections</artifactId>
          <version>3.2.1</version>
        </dependency>
    
      </dependencies>
    </project>

2、dto:省略所有get、set方法

（1）

package com.demo.dto;
    
    public class UserDTO {
        private String userName;
        private Integer age;
    }

（2）

package com.demo.dto;
    import java.util.List;
    
    public class ImportUserDTO {
    
        //结果
        private List<UserDTO> userDTOS;
    
    
        //错误信息
        private List<String> errorMessages;
    }

3、thread:采用多线程校验excel数据

package com.demo.thread;
    
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
    
    @Configuration
    public class ThreadPoolTaskExecutorConfig {
        private static int CORE_POOL_SIZE = 5;
        private static int MAX_POOL_SIZE = 1000;
        @Bean(name="threadPoolTaskExecutor")
        public ThreadPoolTaskExecutor serviceJobTaskExecutor(){
            ThreadPoolTaskExecutor poolTaskExecutor = new ThreadPoolTaskExecutor();
            //线程池维护线程的最少数量
            poolTaskExecutor.setCorePoolSize(CORE_POOL_SIZE);
            //线程池维护线程的最大数量
            poolTaskExecutor.setMaxPoolSize(MAX_POOL_SIZE);
            //线程池所使用的缓冲队列
            poolTaskExecutor.setQueueCapacity(200);
            //线程池维护线程所允许的空闲时间
            poolTaskExecutor.setKeepAliveSeconds(30000);
            poolTaskExecutor.setWaitForTasksToCompleteOnShutdown(true);
            System.out.println(poolTaskExecutor);
            return poolTaskExecutor;
        }
    }

4、util：

（1）

package com.demo.util;
    
    import org.apache.commons.lang3.StringUtils;
    
    import java.text.DateFormat;
    import java.text.ParseException;
    import java.text.SimpleDateFormat;
    import java.util.*;
    
    public class DateUtil {
    
      
        public static final String YMDHMSXXX = "yyyy-MM-dd'T'HH:mm:ssXXX";
    
        public static final String YMDHMSX = "yyyyMMddHHmmssSSS";
        /**
         * yyyy-MM-dd HH:mm:ss
         */
        public static final String YMDHMS = "yyyy-MM-dd HH:mm:ss";
    
        public static final String DATE_PATTERN_DAY = "yyyy-MM-dd";
    
        /**
         * yyyy/MM/dd HH:mm:ss
         */
        public static final String YMDHMS_SLASH = "yyyy/MM/dd HH:mm:ss";
        /**
         * dd-MM-yyyy
         */
        public static final String DMY = "dd-MM-yyyy";
        /**
         * MM-dd-yyyy
         */
        public static final String MDY = "MM-dd-yyyy";
        /**
         * dd-MM-yyyy HH:mm
         */
        public static final String DMYHM = "dd-MM-yyyy HH:mm";
    
        /**
         * yyyy.MM.dd
         */
        public static final String YYYYMMDD_POINT = "yyyy.MM.dd";
    
        /**
         * yyyy/MM/dd
         */
        public static final String YYYYMMDD_SLASH = "yyyy/MM/dd";
    
        /**
         *  时间起始时间 00:00:00
         */
        public static final String START_TIME_STR = " 00:00:00";
    
        /**
         *  时间结束时间 23:59:59
         */
        public static final String END_TIME_STR = " 23:59:59";
    
    
        public static final String YMD = "yyyy-MM-dd";
        public static final String YMDP = "yyyy.MM.dd";
    
        public static final String YYYYMMDD = "yyyyMMdd";
    
        public static final String YYYYMM = "yyyyMM";
    
        /**
         * yyyyMMddHHmmss
         */
        public static final String YYYYMMDD_NOSEPARATOR = "yyyyMMddHHmmss";
    
        public static String format(Date date, String formatPattern) {
            if (date == null) {
                return "";
            }
            SimpleDateFormat f = new SimpleDateFormat(formatPattern);
            return f.format(date);
        }
    
    }

（2）

package com.demo.util;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    
    import java.text.DecimalFormat;
    import java.util.Date;
    
    public class ExcelUtil {
    
        /**
         *
         * @description: 取单元格中的内容，包含时间格式
        　*/
        public static String getSheetCellTimeValue(Row row, int cellNo) {
            Cell cell = row.getCell(cellNo);
            DecimalFormat df = new DecimalFormat("0");
            String name = "";
            if (cell != null) {
                if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                    String cellText = "";
                    if(HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date dateTemp = cell.getDateCellValue();
                        cellText = DateUtil.format(dateTemp, DateUtil.YYYYMMDD_SLASH);
                        name = String.valueOf(cellText);
                    } else {
                        cellText = df.format(cell.getNumericCellValue());
                        name = String.valueOf(cellText);
                        if (null != name && name.indexOf(".") > 0) {
                            name = name.substring(0, name.indexOf("."));
                        }
                    }
                } else {
                    name = cell.getStringCellValue().trim();
                }
            } else {
                name = "";
            }
            return name == null?"":name.trim();
        }
    }

5、task:

package com.demo.task;
    
    import com.demo.dto.ImportUserDTO;
    import com.demo.dto.UserDTO;
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.*;
    
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.concurrent.Callable;
    
    public class UserTask implements Callable<ImportUserDTO> {
    
        //待处理的数据
        private List<List<String>> datas;
    
        //行起始下标
        private Integer rowIndex;
    
        private Workbook workbook;
    
        private Sheet sheet;
    
    
        public UserTask(List<List<String>> datas, int rowIndex, Workbook workbook,Sheet sheet) {
            this.datas = datas;
            this.rowIndex = rowIndex;
            this.workbook = workbook;
            this.sheet = sheet;
            //第一行为标题
            this.rowIndex  = rowIndex+1;
        }
    
        @Override
        public ImportUserDTO call() throws Exception {
    
            ImportUserDTO importUserDTO = new ImportUserDTO();
            List<String> errorMsgList = new ArrayList<>();
            List<UserDTO> userDTOS = new ArrayList<>();
            //总行数
            int rowNum = datas.size();
            //列下标，因为要在出错的单元格标注
            Map<String, Integer> colMap = new HashMap<>();
    
            for(int i=0;i<rowNum;i++){
                try{
                    //列下标
                    int colIndex = 0;
                    colMap.put("name",colIndex);
                    String name = datas.get(i).get(colIndex++);
                    colMap.put("age",colIndex);
                    String age = datas.get(i).get(colIndex++);
    
                    //校验
                    UserDTO userDTO = new UserDTO();
                    if (StringUtils.isBlank(name) || name.length()<2) {
                        handleErrorCell((rowIndex+i),errorMsgList, colMap.get("name"), "第" + (rowIndex + i) + "行数据:" + "姓名长度不对！");
                    }
                    userDTO.setUserName(name);
                    if (StringUtils.isBlank(age) || age.length()<2) {
                        handleErrorCell((rowIndex+i),errorMsgList, colMap.get("age"), "第" + (rowIndex + i) + "行数据:" + "年龄长度不对！");
                    }
                    userDTO.setAge(Integer.valueOf(age));
                    userDTOS.add(userDTO);
                }catch (Exception e){
                    //遇到错误行，跳出此次循环
                    continue;
                }
            }
            importUserDTO.setErrorMessages(errorMsgList);
            importUserDTO.setUserDTOS(userDTOS);
            return importUserDTO;
        }
    
        /**
         *
         * @param rowIndex 行下标
         * @param errorMsgList
         * @param colIndex 列下标
         * @param errorMsg
         */
        private void handleErrorCell(int rowIndex, List<String> errorMsgList, Integer colIndex, String errorMsg) throws Exception {
            errorMsgList.add(errorMsg);
            //设置错误字体为红色
            CellStyle style = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setFontHeightInPoints((short) 11);
            font.setFontName("微软雅黑");
            font.setColor(Font.COLOR_RED);
            //style.setAlignment(HorizontalAlignment.CENTER);
            style.setFont(font);
            Cell deliveryTimeCell = sheet.getRow(rowIndex).getCell(colIndex);
            deliveryTimeCell.setCellStyle(style);
            //添加批注
            XSSFDrawing p = ((XSSFSheet)sheet).createDrawingPatriarch();
            XSSFCell cell = (XSSFCell) sheet.getRow(rowIndex).getCell(colIndex);
            if(cell == null){
                //如果你获取的单元格是空进行创建新的cell，再追加批注，不然null指针
                XSSFRow r = (XSSFRow) sheet.getRow(rowIndex);
                cell = r.createCell(colIndex);
            }
            cell.setCellStyle(style);
            XSSFComment comment = p.createCellComment(new XSSFClientAnchor(0, 0, 0, 0,
                    (colIndex), rowIndex, colIndex+2, rowIndex+2));
            XSSFRichTextString rtf = new XSSFRichTextString(errorMsg);
            XSSFFont commentFormatter = (XSSFFont) workbook.createFont();
            rtf.applyFont(commentFormatter);
            comment.setString(rtf);
            cell.setCellComment(comment);
    
            throw new Exception();
        }
    }

6、service：

package com.demo.service.serviceImpl;
    
    import com.demo.dto.ImportUserDTO;
    import com.demo.dto.UserDTO;
    import com.demo.service.ImportService;
    import com.demo.task.UserTask;
    import com.demo.util.ExcelUtil;
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
    import org.springframework.stereotype.Service;
    import org.apache.commons.collections.CollectionUtils;
    
    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.UUID;
    import java.util.concurrent.Future;
    
    @Service("importService")
    public class ImportServiceImpl implements ImportService {
    
        @Autowired
        private ThreadPoolTaskExecutor threadPoolTaskExecutor;
    
        /**
         * 根据模板读取正确数据保存到user表，并把错误数据标红加批注
         *   姓名  年龄
         * @param is
         * @param fileName
         */
        @Override
        public void saveUser(InputStream is, String fileName) throws IOException {
            //所有数据
            List<List<String>> datas = new ArrayList<>();
            boolean isExcel2007 = fileName.matches("^.+\\.(?i)(xlsx)$");
            Workbook wb = null;
            if (!isExcel2007) {
                // 当excel是2003时,创建excel2003
                wb = new HSSFWorkbook(is);
            } else {
                // 当excel是2007时,创建excel2007
                wb = new XSSFWorkbook(is);
            }
            //只读取第一个页脚的数据
            Sheet sheet = wb.getSheetAt(0);
            //行数
            int rows = sheet.getLastRowNum();
            //第一行为标题，从第二行开始获取
            for (int i=2; i <= rows; i++) {
                List<String> dataOfRow = new ArrayList<>();
                Row rowData = sheet.getRow(i);
                //循环列
                for (int j = 0; j<2; j++) {
                    String cellData = ExcelUtil.getSheetCellTimeValue(rowData, j);
                    dataOfRow.add(StringUtils.isBlank(cellData) ? "" : cellData);
                }
                datas.add(dataOfRow);
            }
            //解析数据datas
            List<Future> futures = new ArrayList<>();
            for (int i = 0; i < datas.size(); i += 100) {
                int startIndex = i;
                int endIndex = startIndex + 100 > datas.size() ? datas.size() : startIndex + 100;
                UserTask task = new UserTask(datas.subList(startIndex, endIndex),i+1,wb,sheet);
                Future<ImportUserDTO> future = threadPoolTaskExecutor.submit(task);
                futures.add(future);
            }
            //收集数据
            List<UserDTO> userDTOS = new ArrayList<>();
            List<String> errorMsgList = new ArrayList<>();
            try{
                for(Future future:futures){
                    ImportUserDTO importUserDTO = (ImportUserDTO) future.get();
                    userDTOS.addAll(importUserDTO.getUserDTOS());
                    errorMsgList.addAll(importUserDTO.getErrorMessages());
                }
            }catch (Exception e){
    
            }
            //插入正确填写的数据
            System.out.println("正确数据："+userDTOS.size());
            //输出错误模板
            if(CollectionUtils.isNotEmpty(errorMsgList)){
                String errorFilePath = "E:"+File.separator + "error" + File.separator
                        +UUID.randomUUID().toString()+".xlsx";
                File fileDir = new File("E:"+File.separator + "error");
                if (!fileDir.exists()) {
                    fileDir.mkdirs();
                }
                File errorFile = new File(errorFilePath);
                if (errorFile.exists()) {
                    errorFile.delete();
                }
                FileOutputStream out = new FileOutputStream(errorFilePath);
                wb.write(out);
                out.close();
              }
        }
    }

7、rest：

package com.demo.rest;
    
    import com.demo.service.ImportService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.bind.annotation.RestController;
    import org.springframework.web.multipart.MultipartFile;
    
    import java.io.IOException;
    import java.io.InputStream;
    
    @RequestMapping("/import")
    @RestController
    public class ImportController {
    
        @Autowired
        private ImportService importService;
    
        /**
         * 保存excel的信息到数据库
         * @param file
         */
        @RequestMapping("/import")
        public void importRest(@RequestParam("excelFile") MultipartFile file){
            try {
                InputStream is = file.getInputStream();
                String fileName = file.getOriginalFilename();
                importService.saveUser(is,fileName);
            } catch (IOException e) {
                e.printStackTrace();
            }
    
        }
    }

8、启动类：

package com.demo;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    @SpringBootApplication
    public class Application {
        public static void main(String args[]){
            SpringApplication.run(Application.class,args);
        }
    }

9、application.properties配置文件：

server.port=9999

测试：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dfdF95X3k_size_16_color_FFFFFF_t_70 1][]

请求后E盘error目录下会生成一个错误的excel文件，打卡即和开头所述的一样。

[2019122017142519.png]: https://img-blog.csdnimg.cn/2019122017142519.png
[2019122017162770.png]: https://img-blog.csdnimg.cn/2019122017162770.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dfdF95X3k_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191220171816491.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dfdF95X3k=,size_16,color_FFFFFF,t_70
[20191220171931614.png]: https://img-blog.csdnimg.cn/20191220171931614.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dfdF95X3k_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191220172650665.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dfdF95X3k=,size_16,color_FFFFFF,t_70