读取excel的数据导入数据库，并导出有问题的数据

pom.xml引入依赖

<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>4.1.0</version>
</dependency>

测试类

import cn.demo.com.dao.UserMapper;
import cn.demo.com.model.UserModel;
import org.apache.commons.compress.utils.IOUtils;
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.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Pattern;

@RunWith(SpringRunner.class)
@SpringBootTest
public class UserTest {
    @Resource
    private UserMapper userMapper; 

    @Test
    public void user() throws Exception{
        String path2= "F:\\excel\\user";  // 读取的文件夹名称
        String fileName = "";
        File[] allFile = getAllFile(path2);
        for (File file : allFile) {

            FileInputStream input = new FileInputStream(file);
            MultipartFile proFile =new MockMultipartFile("file", file.getName(), "text/plain", IOUtils.toByteArray(input));

             fileName = proFile.getOriginalFilename();
             String fileName1 = fileName.substring(0,fileName.length()-4);
            try {
                byte[] bytes = proFile.getBytes();
                ByteArrayInputStream is = new ByteArrayInputStream(bytes);
                boolean isExcel2003 = true;
                if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
                    isExcel2003 = false;
                }

                Workbook wb = null;
                if (isExcel2003) {
                    wb = new HSSFWorkbook(is);
                } else {
                    wb = new XSSFWorkbook(is);
                }
                // 获取sheet页
                Sheet sheet = wb.getSheetAt(0);

                Row titleRow = sheet.getRow(1);
                Map<String, Integer> titleMap = new HashMap<>();


                    // 获取标题行
                    for (int i = 0; i < titleRow.getPhysicalNumberOfCells(); i++) {
                        titleMap.put(String.valueOf(titleRow.getCell(i)), i);
                    }


                    Integer a1 = titleMap.get("姓名");
                    Integer a2 = titleMap.get("手机号码");
                    Integer a3 = titleMap.get("住址");
                    Integer a4 = titleMap.get("邮箱");

                    // 存放待新增的操作日志
                    List<UserModel> logList = new ArrayList<>();
                    // 存放有问题的的数据
                    List<Map<String, String>> errorList = new ArrayList<>();

                    // excel中的总数量
                    int count = 0;

                    for (int r = 2; r <= sheet.getLastRowNum(); r++) {
                        Row row = sheet.getRow(r);
                        if (row.getPhysicalNumberOfCells() == 0) {
                            break;
                        }

                        // 记录总量
                        count += 1;

                        String name = row.getCell(a1) == null ? null : getCellValue(row.getCell(a1));
                        String phone= row.getCell(a2) == null ? null : getCellValue(row.getCell(a2));
                        String address = row.getCell(a3) == null ? null : getCellValue(row.getCell(a3));
                        String email = row.getCell(a4) == null ? null : getCellValue(row.getCell(a4));

                        // 将有问题的数据全部存入一个map中，后面的判断中若是需要存入ErrorExcel就直接丢进去
                        Map<String, String> copy = new HashMap<>();
                        copy.put("姓名", name);
                        copy.put("手机号码", phone);
                        copy.put("住址", address);
                        copy.put("邮箱", email);

                       if (phone.length() > 11) {
                            copy.put("错误信息","手机号码字数超长");
                            errorList.add(copy);
                            continue;
                        }
                        if (address.length() > 255) {
                            copy.put("错误信息","住址字数过长");
                            errorList.add(copy);
                            continue;
                        }


                        String id = UUID.randomUUID().toString();

                        UserModel record = new UserModel();
                        record.setId(id);
                        record.setType(name);
                        record.setOperationTime(phone);
                        record.setOperator(address);
                        record.setContent(email);
                        logList.add(record);

						// 一条条导入
//                      userMapper.insertSelective(record);
//
//                      System.out.println(name + ":" + phone+ " :导入成功");
                    }

                    // 新增的条数
                    int addCount = logList.size();
                    // 失败的条数
                    int failedCount = errorList.size();

                    if (errorList.size() > 0) {
                        // 将有问题的数据记录存放至ErrorExcel
                        String filePar = "F:\\excel\\usererror\\";
                        String fileDir = filePar + fileName1  + "_ErrorExcel.xlsx";
                        String sheetName = "ErrorSheet";
                        String[] title = {"姓名", "手机号码", "住址", "邮箱","错误信息"};

                        CreateExcelFile.createExcelXlsx(filePar, fileDir, sheetName, title);
                        CreateExcelFile.writeToExcelXls(fileDir, sheetName, errorList);
                    }
                try {
					// 批量导入
//                    if (logList.size() > 0) {
//                        userMapper.insertForeach(logList);
//                    }
                }catch (Exception e){
                    System.out.println( fileName + " :导入失败");
                    e.printStackTrace();
                }

            } catch (Exception e) {
                System.out.println("文件读取失败...");
            }
        }

    }


    public static  File[] getAllFile(String directoryPath) {
        List<String> list = new ArrayList<String>();
        File baseFile = new File(directoryPath);

        File[] files = baseFile.listFiles();

        return files;
    }
	
	public static String getCellValue(Cell cell){
        String cellValue = "";
        String temp1 = "";
        BigDecimal temp2 = null;
        if(cell == null){
            return cellValue;
        }

        CellType ct = cell.getCellType();

        switch (ct) {
            case NUMERIC: // 数字
                short format = cell.getCellStyle().getDataFormat();
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    //System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());
                    if (format == 20 || format == 32) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else if (format == 14 || format == 31 || format == 57 || format == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = org.apache.poi.ss.usermodel.DateUtil
                                .getJavaDate(value);
                        cellValue = sdf.format(date);
                    }else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    try {
                        cellValue = sdf.format(cell.getDateCellValue());// 日期
                    } catch (Exception e) {
                        try {
                            throw new Exception("exception on get date data !".concat(e.toString()));
                        } catch (Exception e1) {
                            e1.printStackTrace();
                        }
                    }finally{
                        sdf = null;
                    }
                }  else {
                    BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                    cellValue = bd.toPlainString();// 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
                }
                break;
            case STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue()+"";;
                break;
            case FORMULA: // 公式
                cellValue = cell.getCellFormula();
                break;
            case BLANK: // 空值
                cellValue = "";
                break;
            case _NONE: // 故障
                cellValue = "ERROR VALUE";
                break;
            default:
                cellValue = "UNKNOW VALUE";
                break;
        }

        return cellValue;
    }
	
	/**
	*创建Excel文件
	*/
	 public static void createExcelXlsx(String filePar,String fileDir, String sheetName, String titleRow[]) {

        File file=new File(filePar);
        if(!file.exists()){//如果文件夹不存在
            file.mkdirs();//创建文件夹
        }

        //创建workbook
        xWorkbook = new XSSFWorkbook();
        //新建文件
        FileOutputStream fileOutputStream = null;
        XSSFRow row = null;
        try {

            CellStyle cellStyle = xWorkbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
            cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);

            //添加Worksheet（不添加sheet时生成的xlsx文件打开时会报错)

            xWorkbook.createSheet(sheetName);
            xWorkbook.getSheet(sheetName).createRow(0);
            //添加表头, 创建第一行
            row = xWorkbook.getSheet(sheetName).createRow(0);
            for (short j = 0; j < titleRow.length; j++) {

                XSSFCell cell = row.createCell(j, CellType.BLANK);
                cell.setCellValue(titleRow[j]);
                cell.setCellStyle(cellStyle);
            }
            fileOutputStream = new FileOutputStream(fileDir);
            xWorkbook.write(fileOutputStream);

        } catch (Exception e) {

            e.printStackTrace();
        } finally {

            if (fileOutputStream != null) {
                try {
                    fileOutputStream.close();
                } catch (IOException e) {

                    e.printStackTrace();
                }
            }
        }
    }
	
	 /**
     * 往excel(xls)中写入(已存在的数据无法写入)
     */

    public static void writeToExcelXls(String fileDir, String sheetName, List<Map<String, String>> mapList) throws Exception {

        //创建workbook
        File file = new File(fileDir);

        try {
            xWorkbook = new XSSFWorkbook(new FileInputStream(file));

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        //文件流
        FileOutputStream fileOutputStream = null;
        XSSFSheet sheet = xWorkbook.getSheet(sheetName);
        // 获取表格的总行数
        int rowCount = sheet.getLastRowNum() + 1; // 需要加一
        //获取表头的列数
        int columnCount = sheet.getRow(0).getLastCellNum();

        try {
            // 获得表头行对象
            XSSFRow titleRow = sheet.getRow(0);
            //创建单元格显示样式
            CellStyle cellStyle = xWorkbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
            cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);


            if (titleRow != null) {
                for (int rowId = 0; rowId < mapList.size(); rowId++) {
                    Map<String, String> map = mapList.get(rowId);
                    XSSFRow newRow = sheet.createRow(rowId + 1);
                    newRow.setHeight((short) (20 * 20));//设置行高  基数为20

                    for (short columnIndex = 0; columnIndex < columnCount; columnIndex++) {  //遍历表头
                        //trim()的方法是删除字符串中首尾的空格
                        String mapKey = titleRow.getCell(columnIndex).toString().trim();
                        XSSFCell cell = newRow.createCell(columnIndex);
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(map.get(mapKey) == null ? null : map.get(mapKey).toString());
                    }
                }
            }

            fileOutputStream = new FileOutputStream(fileDir);
            xWorkbook.write(fileOutputStream);
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (fileOutputStream != null) {
                    fileOutputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }



}