<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.7</version>
</dependency>

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.2</version>
</dependency>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>2.0.42</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.18</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.24</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-tomcat</artifactId>
    <scope>provided</scope>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>

@Slf4j
@RestController
@RequestMapping("excel")
public class ExcelController {
 
    @Autowired
    private UserService userService;
 
    //導(dǎo)出
    @GetMapping("/exportExcel")
    public String exportExcel(HttpServletRequest request, HttpServletResponse response){
        String file="D:";
        long startTime = System.currentTimeMillis();
        log.debug("-------------開始插入-------------------");
        userService.exportInspectionPlan(request,response);
        return "ok";
    }
 
 
    //導(dǎo)入
    @PostMapping("/importExcel")
    public void importExcel(MultipartFile multipartFile) throws IOException {
        if (multipartFile.isEmpty()) {
            return;
        }
        // 這里 需要指定讀用哪個class去讀，然后讀取第一個sheet 文件流會自動關(guān)閉
        // 這里每次會讀取3000條數(shù)據(jù) 然后返回過來 直接調(diào)用使用數(shù)據(jù)就行
        EasyExcel.read(multipartFile.getInputStream(), ExportPlanInformationVo.class,
            new PageReadListener<ExportPlanInformationVo>(dataList -> {
                            for (ExportPlanInformationVo user : dataList) {
                                //將導(dǎo)入的數(shù)據(jù)用mybatisPlus一個個添加進數(shù)據(jù)庫
                                System.out.println(user);
                            }
        })).sheet("現(xiàn)場巡視計劃報表").doRead();
    }
    /**
     * 1. 首先分批讀取Excel中的數(shù)據(jù)
     * 這一點EasyExcel有自己的解決方案
     *
     * 2.其次就是DB里插入，怎么去插入這20w條數(shù)據(jù)
     * 當(dāng)然不能一條一條循環(huán)，應(yīng)該批量插入20w條數(shù)據(jù)
     * 同樣不能選擇Mybatis的批量插入，因為效率低
     *
     * 3.使用JDBC+事務(wù)的批量操作將數(shù)據(jù)插入到數(shù)據(jù)庫
     * */
    //批量導(dǎo)入
    @PostMapping("/batchImportExcel")
    public void batchImportExcel(MultipartFile file) throws IOException {
        if (BeanUtil.isEmpty(file)){
            log.debug("傳入的文件不能為空！");
            return ;
        }
        if (!Objects.requireNonNull(file.getOriginalFilename()).endsWith("xls") && !file.getOriginalFilename().endsWith("xlsx")){
            log.debug("請上傳Excel文件！");
            return ;
        }
        CommonExportListenerDto commonExportListenerDto = new CommonExportListenerDto();
        EasyExcel.read(file.getInputStream(),commonExportListenerDto).doReadAll();
    }
}

@Service
@Slf4j
public class UserService {
//
    @Resource
    private IndMapper indMapper;
    //文件導(dǎo)出
    public void exportInspectionPlan(HttpServletRequest request, HttpServletResponse response) {
        //以上需要根據(jù)自己的業(yè)務(wù)去做數(shù)據(jù)處理   這里就不做展示
        try {
            //給文件命名
            String fileName = "ExcelTest";
            // 設(shè)置響應(yīng)頭
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("UTF-8");
            // 設(shè)置防止中文名亂碼
            fileName = URLEncoder.encode(fileName, "utf-8");
            // 文件下載方式(附件下載還是在當(dāng)前瀏覽器打開)
            response.setHeader("Content-disposition", "attachment;filename=" +
                    fileName + ".xlsx");
            //向excel表格寫入數(shù)據(jù)
            EasyExcel.write(response.getOutputStream(), ExportPlanInformationVo.class)
                    .sheet("下方導(dǎo)航")
                    .doWrite(getAll());
/*
            //下載到指定路徑
            String fileUrl = "D://ExcelTest.xlsx";
            //向excel表格寫入數(shù)據(jù)
            EasyExcel.write(fileUrl, ExportPlanInformationVo.class)
                    .sheet("下方導(dǎo)航")
                    .doWrite(getAll());
*/
        } catch (Exception e) {
            log.error("出現(xiàn)錯誤 {}", e);
        }
    }
 
    public List<ExportPlanInformationVo> getAll(){
        //根據(jù)業(yè)務(wù)邏輯獲取數(shù)據(jù)
//        List<ExportPlanInformationVo> all = indMapper.getAll();
        return  indMapper.getAll();
    }
 
}

import java.sql.*;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;
 
public class JDBCUtil {
 
    private static String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false";
    private static String username = "root";
    private static String password = "196713";
    private static String driverName = "com.mysql.jdbc.Driver";
 
    /**
     * 獲取連接對象
     *
     * @return 連接對象
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            // 1. 注冊驅(qū)動
            Class.forName(driverName);
            // 2. 獲取連接對象
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return conn;
    }
 
 
    /**
     * 釋放資源
     *
     * @param connection 連接對象
     * @param statement  預(yù)編譯執(zhí)行對象
     * @param resultSet  結(jié)果集
     */
    public static void releaseResources(Connection connection, PreparedStatement statement, ResultSet resultSet) {
        // 釋放資源
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
 
    public static void insertBatch(List<Map<Object, Object>> dataList, String sql) {
        Connection conn = null;
        PreparedStatement pstm = null;
        try {
            conn = getConnection();
            //如果需要開啟事務(wù)此處需要將自動提交關(guān)閉
            // conn.setAutoCommit(false);
            //預(yù)編譯sql
            pstm = (PreparedStatement) conn.prepareStatement(sql);
            for (Map<Object, Object> map : dataList) {
                //此處類型判斷不完整后續(xù)可以借鑒jdk自行封裝攔截器
                for (int i = 1; i <= map.size(); i++) {
                    Object o = map.get(i-1);
                    if (BeanUtil.isEmpty(o)) {
                        pstm.setString(i, null);
                        continue;
                    }
                    if (o instanceof String) {
                        pstm.setString(i, o.toString());
                        continue;
                    }
                    if (o instanceof Integer) {
                        pstm.setInt(i, Integer.parseInt(o.toString()));
                        continue;
                    }
                    if (o instanceof LocalDateTime) {
                        pstm.setDate(i, new Date(System.currentTimeMillis()));
                        continue;
                    }
                    if (o instanceof Boolean) {
                        pstm.setBoolean(i, Boolean.parseBoolean(o.toString()));
                    }
                }
                //添加到同一個批處理中
                pstm.addBatch();
            }
            //執(zhí)行批處理
            pstm.executeBatch();
            //如果需要開啟事務(wù)此處需要手動提交事務(wù)
            //conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
 
}

@Data
@Slf4j
public class CommonExportListenerDto extends AnalysisEventListener<Map<Object, Object>> {
 
    /**
     * 表頭數(shù)據(jù)（存儲所有的表頭數(shù)據(jù)）
     */
    private List<Map<Integer, String>> headList = new ArrayList<>();
 
    /*
     * 數(shù)據(jù)體
     */
    private List<Map<Object, Object>> dataList = new ArrayList<>();
 
    /**
     * 存儲全部表頭數(shù)據(jù)
     * @param headMap
     * @param context
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        headList.add(headMap);
    }
 
    /**
     * 每一條數(shù)據(jù)解析都會來調(diào)用
     * @param data
     * @param context
     */
    @Override
    public void invoke(Map<Object, Object> data, AnalysisContext context) {
        dataList.add(data);
        if (dataList.size() >= 3) {
            saveData();
            // 存儲完成清理 list
            dataList = ListUtils.newArrayListWithExpectedSize(2000);
        }
    }
 
    /**
     * 所有數(shù)據(jù)解析完成之后的操作
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
    }
 
    private void saveData() {
        log.info("{}條數(shù)據(jù)，開始存儲數(shù)據(jù)庫！", dataList.size());
        //批量導(dǎo)入
        JDBCUtil.insertBatch(dataList,"INSERT INTO ind (a,b,c,d,e) VALUES(?,?,?,?,?);");
        log.info("存儲數(shù)據(jù)庫成功！");
    }
}

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@HeadRowHeight(value = 30) // 頭部行高
@ContentRowHeight(value = 25) // 內(nèi)容行高
@ColumnWidth(value = 20) // 列寬
@HeadFontStyle(fontName = "宋體", fontHeightInPoints = 11)
public class ExportPlanInformationVo  implements Serializable {
 
// 1. 如果不想某個字段在excel中出現(xiàn)  可以加  @ExcelIgnore注解
    @ExcelProperty(value = "a")
    private String a;
 
 
//    @Dict(code = "inspectionType", fieldName = "inspectionTypeName")
    @ExcelProperty(value = "b")
    private String b;
 
    @ExcelProperty(value = "c")
    private String c;
    @ExcelProperty(value = "d")
    private String d;
    @ExcelProperty(value = "c")
    private String e;
 
 
}

@Mapper
public interface IndMapper {
 
    @Select("select * from ind")
    List<ExportPlanInformationVo> getAll();
 
}