将Excel数据内容读取到MySql数据库

前言：通过对上传Excel文件的读取，实现将Excel内容插入到数据库，解决了数据库大批量数据手动添加的过程。
关键词：文件上传，Excel，MySql数据库

一、工具类

此Excel工具类的作用为，读取Excel的内容，传回一个List集合，集合中存储的是Map集合，List的长度为表格的行数（不含表头）；Map中的Key为表头，Value为单元格值；形参path为传入的Excel的绝对路径，参考我上一篇文章：前端文件上传及后端接收（el-upload标签的使用）

使用的依赖：

<!--excel相关-->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>

package com.han.common.util;

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class Excel {

        // 读Excel，返回包含map的集合
    public static List<Map<Object, Object>> getExcel(String path) throws Exception {
        // 读取excel中数据。
        InputStream inputStream = new FileInputStream(path);
        // 工作册对象
        Workbook workbook = WorkbookFactory.create(inputStream);
        // 工作簿对象，第一张表
        Sheet sheet = workbook.getSheetAt(0);
        // 第一行
        Row firstRow = sheet.getRow(0);
        // 列数
        int column = firstRow.getPhysicalNumberOfCells();
        // 获取表头，放入集合
        ArrayList<String> strings = new ArrayList<>();
        for (int i = 0; i < column; i++) {
            Cell cell = firstRow.getCell(i);
            strings.add(i, cell.getStringCellValue());
        }
        // 获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        //数据存入map中
        ArrayList<Map<Object, Object>> list = new ArrayList<>();
        // 遍历每行
        for (int i = 1; i < rows; i++) {
            HashMap<Object, Object> map = new HashMap<>();
            // 获取当前行
            Row row = sheet.getRow(i);
            // 遍历每列
            for (int j = 0; j < column; j++) {
                // 每个单元格
                Cell cell = row.getCell(j);
                // 获取单元格数据类型
                int cellType = cell.getCellType();
                Object value = null;
                if (cellType == Cell.CELL_TYPE_STRING) {
                    // 字符串类型
                    value = cell.getStringCellValue();
                } else if (cellType == Cell.CELL_TYPE_NUMERIC) {// 数字格式
                    if (DateUtil.isCellDateFormatted(cell)) {
                        // 日期类型
                        Date dateCellValue = cell.getDateCellValue();
                        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                        value = dateFormat.format(dateCellValue);
                    } else {
                        double t = cell.getNumericCellValue();
                        if ((long) t == t) {
                            value = (long) t;
                        } else {
                            value = String.valueOf(t);
                        }
                    }
                }
                // map("表头","单元格值");
                map.put(strings.get(j), value);
            }
            list.add(map);
        }
        // list的size表示Excel的行数（排除表头行）
        return list;
    }

}

二、读取Excel

此类也是一个工具类，read()方法的形参为文件夹的绝对路径，File file = new File(path); String[] list = file.list();
通过该路径构造出一个File对象，调用该对象的list()方法获取一个包含该路径下所有文件名的集合，对文件名进行判断，针对不同的文件执行不同的数据库插入语句。这里把Excel文件附上一个（tb_OrderGoods.xlsx）：

表格数据中不要有公式也不要有空值，表头和数据库字段名称保持一致
附上数据库表：

数据库设计：

package com.han.common.util;

import com.han.sale.entity.ExportList;
import com.han.sale.entity.Order;
import com.han.sale.entity.OrderGoods;
import com.han.sale.entity.UserInfo;
import com.han.sale.service.ExportListService;
import com.han.sale.service.OrderGoodsService;
import com.han.sale.service.OrderService;
import com.han.sale.service.UserInfoService;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.io.File;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;

@Component
public class ReadExcel {

    @Resource(name = "orderService")
    private OrderService orderService;

    @Resource(name = "userInfoService")
    private UserInfoService userInfoService;

    @Resource(name = "orderGoodsService")
    private OrderGoodsService orderGoodsService;

    @Resource(name = "exportListService")
    private ExportListService exportListService;

    private String PATH = null;

    // E:\maven\shopping\target\shopping\\upload
    public void read(String path) throws Exception {
        this.PATH = path;
        File file = new File(path);
        String[] list = file.list();
        Objects.requireNonNull(list);
        for (String s : list) {
            switch (s) {
                case "tb_order.xlsx": {
                    ReadOrder();
                    break;
                }
                case "tb_OrderGoods.xlsx": {
                    ReadOrderGoods();
                    break;
                }
                case "tb_userInfo.xlsx": {
                    ReadUserInfo();
                    break;
                }
                case "tb_export.xlsx": {
                    ReadExport();
                    break;
                }
                default: {
                    break;
                }
            }
        }
        // 删文件
        File fileDir = new File(path);
        File[] files = fileDir.listFiles();
        for (File f : files) {
            f.delete();
        }

    }

    public void ReadOrder() throws Exception {
        String Path = this.PATH + "/tb_order.xlsx";
        List<Map<Object, Object>> list = Excel.getExcel(Path);
        for (Map<Object, Object> map : list) {
            System.out.println(map);
        }
        ArrayList<Order> orders = new ArrayList<>();
        for (Map<Object, Object> map : list) {
            long orderID = (Long) map.get("orderID");
            long orderNumber = (Long) map.get("orderNumber");
            String orderDate = (String) map.get("orderDate");
            String orderType = (String) map.get("orderType");
            String orderAction = (String) map.get("orderAction");
            String businessType = (String) map.get("businessType");
            String payType = (String) map.get("payType");
            String transportType = (String) map.get("transportType");
            String orderStatus = (String) map.get("orderStatus");
            Order order = new Order((int) orderID, String.valueOf(orderNumber), orderDate, orderType, orderAction, businessType, payType, transportType, orderStatus);
            orders.add(order);
        }
        for (Order order : orders) {
            int i = orderService.insertSelective(order);
        }

    }

    public void ReadOrderGoods() throws Exception {
        String Path = this.PATH + "/tb_OrderGoods.xlsx";
        List<Map<Object, Object>> list = Excel.getExcel(Path);
        ArrayList<OrderGoods> orderGoods = new ArrayList<>();
        for (Map<Object, Object> map : list) {
            long goodsID = (Long) map.get("goodsID");
            long orderNum = (Long) map.get("orderNum");
            long goodsCode = (Long) map.get("goodsCode");
            String IMEI = (String) map.get("IMEI");
            String goodsName = (String) map.get("goodsName");
            long goodsPrice = (Long) map.get("goodsPrice");
            long goodsNum = (Long) map.get("goodsNum");
            long moneySum = (Long) map.get("moneySum");
            long goodsStatus = (Long) map.get("goodsStatus");
            String goodsGrand = (String) map.get("goodsGrand");
            String goodsColor = (String) map.get("goodsColor");
            String goodsSupplier = (String) map.get("goodsSupplier");
            String goodsModel = (String) map.get("goodsModel");
            OrderGoods goods = new OrderGoods((int) goodsID, String.valueOf(orderNum), String.valueOf(goodsCode), IMEI, goodsName,
                    (double) goodsPrice,
                    (int) goodsNum,
                    (double) moneySum,
                    String.valueOf(goodsStatus),
                    goodsGrand, goodsColor,
                    goodsSupplier, goodsModel);
            System.out.println(goods);
            orderGoods.add(goods);
        }
        for (OrderGoods orderGood : orderGoods) {
            int i = orderGoodsService.insertSelective(orderGood);
        }
    }

    public void ReadUserInfo() throws Exception {
        String Path = this.PATH + "/tb_userInfo.xlsx";
        List<Map<Object, Object>> list = Excel.getExcel(Path);
        ArrayList<UserInfo> userInfos = new ArrayList<>();
        for (Map<Object, Object> map : list) {
            long userID = (Long) map.get("userID");
            long orderNum = (Long) map.get("orderNum");
            String userName = (String) map.get("userName");
            String memberID = (String) map.get("memberID");
            long userTel = (Long) map.get("userTel");
            long userPhone = (Long) map.get("userPhone");
            long userPostcode = (Long) map.get("userPostcode");
            String userAddress = (String) map.get("userAddress");
            String transportAddress = (String) map.get("transportAddress");
            String transportType = (String) map.get("transportType");
            long transportFee = (Long) map.get("transportFee");
            String payType = (String) map.get("payType");
            String receiveDate = (String) map.get("receiveDate");
            String userEmail = (String) map.get("userEmail");
            String remitter = (String) map.get("remitter");
            UserInfo userInfo = new UserInfo((int) userID, String.valueOf(orderNum), userName, memberID, String.valueOf(userTel),
                    String.valueOf(userPhone), String.valueOf(userPostcode), userAddress, transportAddress, transportType,
                    (double) transportFee, payType, receiveDate, userEmail, remitter);
            System.out.println(userInfo);
            userInfos.add(userInfo);
        }
        for (UserInfo userInfo : userInfos) {
            int i = userInfoService.insertSelective(userInfo);
        }
    }

    public void ReadExport() throws Exception {
        String Path = this.PATH + "/tb_export.xlsx";
        List<Map<Object, Object>> list = Excel.getExcel(Path);
        ArrayList<ExportList> exportLists = new ArrayList<>();
        for (Map<Object, Object> map : list) {
            long exportID = (Long) map.get("exportID");
            long orderNumber = (Long) map.get("orderNumber");
            long goodsCode = (Long) map.get("goodsCode");
            String imei = (String) map.get("IMEI");
            long goodsStatus = (Long) map.get("goodsStatus");
            ExportList exportList = new ExportList((int) exportID, String.valueOf(orderNumber), String.valueOf(goodsCode), imei, String.valueOf(goodsStatus), null, null);
            exportLists.add(exportList);
        }
        for (ExportList exportList : exportLists) {
            int i = exportListService.insertSelective(exportList);
        }
    }

}

三、调用

private static String UPLOAD_PATH;

    @Resource
    private ReadExcel readExcel;

    @PostMapping("upload")
    public ResponseEntity upload(@RequestParam MultipartFile file, HttpServletRequest request, HttpServletResponse response) throws Exception {
        //        tb_export.xlsx
        String filename = file.getOriginalFilename();
        //        E:\maven\shopping\target\shopping\\upload\
        String upload = request.getServletContext().getRealPath("upload/");

        File fileDir = new File(upload);
        File[] files = fileDir.listFiles();
        for (File f : files) {
            f.delete();
        }

        String path = upload + filename;
        File filePath = new File(path);
        ExportListHandler.UPLOAD_PATH = path;
        BufferedOutputStream outputStream = new BufferedOutputStream(new FileOutputStream(filePath));
        outputStream.write(file.getBytes());
        outputStream.flush();
        outputStream.close();
        //遍历读取Excel
        readExcel.read(upload);
        return null;
    }

代码具体解释参考文头的链接