1、在做数据分析-导入的项目中，经常会有将一个excel表格数据导入数据库并通过web端的形式展现出来，示例：excel表格

2、 通过java代码程序实现转化并存入MySQL数据库中，示例：

3、直接贴入代码：controller层

package com.liyh.controller;

import com.liyh.entity.Result;
import com.liyh.service.ExcelService;
import com.liyh.utils.ExcelTool;
import com.liyh.utils.FileUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * 接口
 *
 * @Author: arthur_Yang
 * @Date: 2020/10/23 17:05
 */

@RestController
@RequestMapping("/excel")
public class ExcelController {

    Logger logger = LoggerFactory.getLogger(ExcelController.class);

    @Autowired
    private ExcelService excelService;

    @PostMapping("/import")
    public Result importProject(MultipartFile file) {
        String postfix = ExcelTool.getPostfix(file.getOriginalFilename());

        if (!"xlsx".equals(postfix) && !"xls".equals(postfix)) {
            return Result.error("导入失败，请选择正确的文件格式支持xlsx或xls");
        }
        return excelService.importProject(file);
    }

    @GetMapping("/download")
    public String downloadFile(HttpServletRequest request, HttpServletResponse response) {
        String fileName = "template.xlsx";
        String result = FileUtils.downloadFiles(request, response, fileName);
        if (request == null) {
            return null;
        }
        return result;
    }
}

 4、services实现层

package com.liyh.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.liyh.entity.ProjectItem;
import com.liyh.entity.Result;
import com.liyh.mapper.ExcelMapper;
import com.liyh.service.ExcelService;
import com.liyh.utils.ExcelTool;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @Author: arthur_Yang
 * @Date: 2020/10/23 17:44
 */

@Service
public class ExcelServiceImpl extends ServiceImpl<ExcelMapper, ProjectItem> implements ExcelService {

    private NumberFormat numberFormat = null;

    @Override
    public Result importProject(MultipartFile file) {
        // 解析Excel数据
        Result r = readDataFromExcel(file);
        List list = (List) r.getData();
        List<ProjectItem> items = list;
        if (items == null || items.size() <= 0) {
            return Result.error("没有数据！！！");
        }
        //查询之前是否存在项目清单项
        QueryWrapper wrapper = new QueryWrapper();
        wrapper.eq("is_deleted", 0);
        List<ProjectItem> beforeItems = baseMapper.selectList(wrapper);
        //如果存在，判断两个集合中是否有相同的项目序号
        if (beforeItems != null && beforeItems.size() > 0) {
            List<String> beforeOrderNumber = beforeItems.stream().map(ProjectItem::getDatatime).collect(Collectors.toList());
            List<String> afterOrderNumber = items.stream().map(ProjectItem::getDatatime).collect(Collectors.toList());
            for (String vo : beforeOrderNumber) {
                if (afterOrderNumber.contains(vo)) {
                    return Result.error(vo + "：该项目序号已经存在");
                }
            }
        }
        // 如果没有序号相等，则插入数据表格中的数据，然后重新读取
        for (ProjectItem item : items) {
            // 保存数据
            int insert = baseMapper.insertProjectItem(item.getDatatime(),
                    item.getOrderno(), item.getPno(), item.getPaytypes(),
                    item.getType(), item.getPcode(), item.getPrice(), item.getYh(),
                    item.getPrices(), item.getDevice(), item.getPnos(), item.getTypes(),
                    item.getPaycade(), item.getNumon(), item.getOrdernu());
            if (insert <= 0) {
                return Result.error("导入失败");
            }
        }
        return Result.success("导入成功");
    }

    /**
     * 解析Excel数据
     *
     * @param file 文件
     * @return
     */
    public Result readDataFromExcel(MultipartFile file) {
        POIFSFileSystem pfs = null;
        Workbook workbook = null;
        try {
            // 解析xls和xlsx不兼容问题
            workbook = ExcelTool.getWorkBook(pfs, workbook, file);
        } catch (IOException e) {
            e.printStackTrace();
            return Result.error("模板保存异常。");
        }
        if (workbook == null) {
            return Result.error("请使用模板上传文件");
        }
        // 判断有记录的列数
        if (workbook.getSheetAt(0).getRow(0).getPhysicalNumberOfCells() != 15) {
            return Result.error("请使用类型所对应的模板");
        }
        numberFormat = NumberFormat.getNumberInstance();
        List<ProjectItem> list = new ArrayList<>();
        // 获取表格第一个sheet的内容
        Sheet sheetAt = workbook.getSheetAt(0);
        // 获得sheet总行数
        int lastRowNum = sheetAt.getLastRowNum();
        if (lastRowNum < 1) {
            return Result.error("数据错误");
        }
        // 开始读取,不读取表头所以从第二行开始
        for (int i = 1; i <= lastRowNum; i++) {
            // 获取每一行
            Row row = sheetAt.getRow(i);
            // 行为空不读取
            if (row == null) {
                continue;
            }
            Cell cell = row.getCell(0);
            //列为空不读取
            if (cell == null || StringUtils.isEmpty(convertData(cell))) {
                continue;
            }
            // 创建对象封装行数据
            ProjectItem projectItem = new ProjectItem();
            // 创建一个集合根据下标来确定每个单元格对应对象的什么属性
            List<String> rowList = new ArrayList<>();
            //添加数据
            for (int j = 0; j < 15; j++) {
                Cell cellOne = row.getCell(j);
                try {
                    String item = convertData(cellOne);
                    rowList.add(item);
                } catch (Exception e) {
                    System.out.println("-------------------Err-----------------------");
                    System.out.println(i + "行" + j + "列数据转换出现异常");
                    rowList.add("");
                }
            }
            //规避行数数据后几行为空
            if (rowList.size() < 15) {
                for (int k = 0; k < 15 - rowList.size(); k++) {
                    rowList.add("");
                }
            }
            // 添加数据
            projectItem.setDatatime(rowList.get(0).trim());
            projectItem.setOrderno(rowList.get(1).trim());
            projectItem.setPno(rowList.get(2).trim());
            projectItem.setPaytypes(rowList.get(3).trim());
            projectItem.setType(rowList.get(4).trim());
            projectItem.setPcode(rowList.get(5).trim());
            projectItem.setPrice(rowList.get(6).trim());
            projectItem.setYh(rowList.get(7).trim());
            projectItem.setPrices(rowList.get(8).trim());
            projectItem.setDevice(rowList.get(9).trim());
            projectItem.setPnos(rowList.get(10).trim());
            projectItem.setTypes(rowList.get(11).trim());
            projectItem.setPaycade(rowList.get(12).trim());
            projectItem.setNumon(rowList.get(13).trim());
            projectItem.setOrdernu(rowList.get(14).trim());
            list.add(projectItem);
        }
        return Result.success("解析成功", list);
    }

    /**
     * 表格数据转换
     *
     * @param cell
     * @return
     */
    public String convertData(Cell cell) {
        String str = "";
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                //判断是否是整数
                str = numberFormat.format(cell.getNumericCellValue());
                break;
            case STRING:
                str = cell.getStringCellValue();
                break;
            case FORMULA:
                try {
                    str = String.valueOf(cell.getNumericCellValue());
                } catch (IllegalArgumentException e) {
                    str = String.valueOf(cell.getRichStringCellValue());
                }
                break;
            default:
                str = "";
        }
        return str;
    }
}

5、实体对象层：

package com.liyh.entity;

import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;

import java.io.Serializable;

/**
 * 项目清单表实体类
 *
 * @Author: arthur_Yang
 * @Date: 2020/10/23 17:05
 */
@Data
@TableName("project_item")
public class ProjectItem implements Serializable {

    private static final long serialVersionUID = 1L;
    private Integer id;
    private String datatime;
    private String orderno;
    private String pno;
    private String paytypes;
    private String type;
    private String pcode;
    private String price;
    private String yh;
    private String prices;
    private String device;
    private String pnos;
    private String types;
    private String paycade;
    private String numon;
    private String ordernu;
    private String is_deleted;


}

6、所用到的工具类ExcelTool和FileUtils，可自行封装

package com.liyh.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;

/**
 * 解析Excel文件单元格内容
 *
 * @Author: arthur_Yang
 * @Date: 2020/10/24 17:05
 */
public class ExcelTool {
    public static final String EMPTY = "";
    private static final String POINT = ".";

    /**
     * 获得path的后缀名
     *
     * @param path 文件路径
     * @return 路径的后缀名
     */
    public static String getPostfix(String path) {
        if (path == null || EMPTY.equals(path.trim())) {
            return EMPTY;
        }
        if (path.contains(POINT)) {
            return path.substring(path.lastIndexOf(POINT) + 1);
        }
        return EMPTY;
    }

    /**
     * 解析xls和xlsx不兼容问题
     *
     * @param pfs
     * @param workbook
     * @param file
     * @return
     */
    public static Workbook getWorkBook(POIFSFileSystem pfs, Workbook workbook, MultipartFile file) throws IOException {
        String filename = file.getOriginalFilename();
        if (filename.endsWith("xls")) {
            pfs = new POIFSFileSystem(file.getInputStream());
            workbook = new HSSFWorkbook(pfs);
            return workbook;
        } else if (filename.endsWith("xlsx")) {
            try {
                workbook = new XSSFWorkbook(file.getInputStream());
                return workbook;
            } catch (IOException e) {
                return null;
            }
        } else {
            return null;
        }
    }
}

package com.liyh.utils;

import org.springframework.core.io.ClassPathResource;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;

/**
 * @Author: arthur_Yang
 * @Date: 2020/11/4 16:10
 */

public class FileUtils {

    /**
     * 下载文件
     *
     * @param request
     * @param response
     * @param fileName
     * @return
     * @throws IOException
     */
    public static String downloadFiles(HttpServletRequest request, HttpServletResponse response, String fileName) {

        if (StringUtils.isEmpty(fileName)) {
            return "文件名称为空";
        }

        //设置文件路径
        ClassPathResource classPathResource = new ClassPathResource("templates/" + fileName);
        File file = null;
        try {
            file = classPathResource.getFile();
        } catch (IOException e) {
            e.printStackTrace();
            return "文件不存在";
        }

        response.setHeader("content-type", "application/octet-stream");
        // 设置强制下载不打开
        response.setContentType("application/force-download");
        // 设置文件名
        response.addHeader("Content-Disposition", "attachment;fileName=" + fileName);

        byte[] buffer = new byte[1024];
        InputStream 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);
            }
        } 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();
                }
            }
        }
        return "文件下载成功";
    }

    /**
     * 判断文件大小
     *
     * @param file 文件
     * @param size 限制大小
     * @param unit 限制单位（B,K,M,G）
     * @return
     */
    public static boolean checkFileSize(MultipartFile file, int size, String unit) {
        if (file.isEmpty() || StringUtils.isEmpty(size) || StringUtils.isEmpty(unit)) {
            return false;
        }
        long len = file.getSize();
        double fileSize = 0;
        if ("B".equals(unit.toUpperCase())) {
            fileSize = (double) len;
        } else if ("K".equals(unit.toUpperCase())) {
            fileSize = (double) len / 1024;
        } else if ("M".equals(unit.toUpperCase())) {
            fileSize = (double) len / 1048576;
        } else if ("G".equals(unit.toUpperCase())) {
            fileSize = (double) len / 1073741824;
        }
        if (fileSize > size) {
            return false;
        }
        return true;
    }
}

7、pom.xml中的依赖

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <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-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--jdbc 数据库连接-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!-- 引入阿里数据库连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.5</version>
        </dependency>
        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- mybatisPlus 核心库 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>
        <dependency>
            <groupId>net.sourceforge.nekohtml</groupId>
            <artifactId>nekohtml</artifactId>
            <version>1.9.15</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-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.10</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
            </resource>
        </resources>
    </build>

最后贴出完整源码：

package com.liyh.controller;

import com.liyh.entity.Result;
import com.liyh.service.ExcelService;
import com.liyh.utils.ExcelTool;
import com.liyh.utils.FileUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * 接口
 *
 * @Author: arthur_Yang
 * @Date: 2020/10/23 17:05
 */

@RestController
@RequestMapping("/excel")
public class ExcelController {

    Logger logger = LoggerFactory.getLogger(ExcelController.class);

    @Autowired
    private ExcelService excelService;

    @PostMapping("/import")
    public Result importProject(MultipartFile file) {
        String postfix = ExcelTool.getPostfix(file.getOriginalFilename());

        if (!"xlsx".equals(postfix) && !"xls".equals(postfix)) {
            return Result.error("导入失败，请选择正确的文件格式支持xlsx或xls");
        }
        return excelService.importProject(file);
    }

    @GetMapping("/download")
    public String downloadFile(HttpServletRequest request, HttpServletResponse response) {
        String fileName = "template.xlsx";
        String result = FileUtils.downloadFiles(request, response, fileName);
        if (request == null) {
            return null;
        }
        return result;
    }
}

package com.liyh.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.liyh.entity.ProjectItem;
import com.liyh.entity.Result;
import com.liyh.mapper.ExcelMapper;
import com.liyh.service.ExcelService;
import com.liyh.utils.ExcelTool;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @Author: arthur_Yang
 * @Date: 2020/10/23 17:44
 */

@Service
public class ExcelServiceImpl extends ServiceImpl<ExcelMapper, ProjectItem> implements ExcelService {

    private NumberFormat numberFormat = null;

    @Override
    public Result importProject(MultipartFile file) {
        // 解析Excel数据
        Result r = readDataFromExcel(file);
        List list = (List) r.getData();
        List<ProjectItem> items = list;
        if (items == null || items.size() <= 0) {
            return Result.error("没有数据！！！");
        }
        //查询之前是否存在项目清单项
        QueryWrapper wrapper = new QueryWrapper();
        wrapper.eq("is_deleted", 0);
        List<ProjectItem> beforeItems = baseMapper.selectList(wrapper);
        //如果存在，判断两个集合中是否有相同的项目序号
        if (beforeItems != null && beforeItems.size() > 0) {
            List<String> beforeOrderNumber = beforeItems.stream().map(ProjectItem::getDatatime).collect(Collectors.toList());
            List<String> afterOrderNumber = items.stream().map(ProjectItem::getDatatime).collect(Collectors.toList());
            for (String vo : beforeOrderNumber) {
                if (afterOrderNumber.contains(vo)) {
                    return Result.error(vo + "：该项目序号已经存在");
                }
            }
        }
        // 如果没有序号相等，则插入数据表格中的数据，然后重新读取
        for (ProjectItem item : items) {
            // 保存数据
            int insert = baseMapper.insertProjectItem(item.getDatatime(),
                    item.getOrderno(), item.getPno(), item.getPaytypes(),
                    item.getType(), item.getPcode(), item.getPrice(), item.getYh(),
                    item.getPrices(), item.getDevice(), item.getPnos(), item.getTypes(),
                    item.getPaycade(), item.getNumon(), item.getOrdernu());
            if (insert <= 0) {
                return Result.error("导入失败");
            }
        }
        return Result.success("导入成功");
    }

    /**
     * 解析Excel数据
     *
     * @param file 文件
     * @return
     */
    public Result readDataFromExcel(MultipartFile file) {
        POIFSFileSystem pfs = null;
        Workbook workbook = null;
        try {
            // 解析xls和xlsx不兼容问题
            workbook = ExcelTool.getWorkBook(pfs, workbook, file);
        } catch (IOException e) {
            e.printStackTrace();
            return Result.error("模板保存异常。");
        }
        if (workbook == null) {
            return Result.error("请使用模板上传文件");
        }
        // 判断有记录的列数
        if (workbook.getSheetAt(0).getRow(0).getPhysicalNumberOfCells() != 15) {
            return Result.error("请使用类型所对应的模板");
        }
        numberFormat = NumberFormat.getNumberInstance();
        List<ProjectItem> list = new ArrayList<>();
        // 获取表格第一个sheet的内容
        Sheet sheetAt = workbook.getSheetAt(0);
        // 获得sheet总行数
        int lastRowNum = sheetAt.getLastRowNum();
        if (lastRowNum < 1) {
            return Result.error("数据错误");
        }
        // 开始读取,不读取表头所以从第二行开始
        for (int i = 1; i <= lastRowNum; i++) {
            // 获取每一行
            Row row = sheetAt.getRow(i);
            // 行为空不读取
            if (row == null) {
                continue;
            }
            Cell cell = row.getCell(0);
            //列为空不读取
            if (cell == null || StringUtils.isEmpty(convertData(cell))) {
                continue;
            }
            // 创建对象封装行数据
            ProjectItem projectItem = new ProjectItem();
            // 创建一个集合根据下标来确定每个单元格对应对象的什么属性
            List<String> rowList = new ArrayList<>();
            //添加数据
            for (int j = 0; j < 15; j++) {
                Cell cellOne = row.getCell(j);
                try {
                    String item = convertData(cellOne);
                    rowList.add(item);
                } catch (Exception e) {
                    System.out.println("-------------------Err-----------------------");
                    System.out.println(i + "行" + j + "列数据转换出现异常");
                    rowList.add("");
                }
            }
            //规避行数数据后几行为空
            if (rowList.size() < 15) {
                for (int k = 0; k < 15 - rowList.size(); k++) {
                    rowList.add("");
                }
            }
            // 添加数据
            projectItem.setDatatime(rowList.get(0).trim());
            projectItem.setOrderno(rowList.get(1).trim());
            projectItem.setPno(rowList.get(2).trim());
            projectItem.setPaytypes(rowList.get(3).trim());
            projectItem.setType(rowList.get(4).trim());
            projectItem.setPcode(rowList.get(5).trim());
            projectItem.setPrice(rowList.get(6).trim());
            projectItem.setYh(rowList.get(7).trim());
            projectItem.setPrices(rowList.get(8).trim());
            projectItem.setDevice(rowList.get(9).trim());
            projectItem.setPnos(rowList.get(10).trim());
            projectItem.setTypes(rowList.get(11).trim());
            projectItem.setPaycade(rowList.get(12).trim());
            projectItem.setNumon(rowList.get(13).trim());
            projectItem.setOrdernu(rowList.get(14).trim());
            list.add(projectItem);
        }
        return Result.success("解析成功", list);
    }

    /**
     * 表格数据转换
     *
     * @param cell
     * @return
     */
    public String convertData(Cell cell) {
        String str = "";
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                //判断是否是整数
                str = numberFormat.format(cell.getNumericCellValue());
                break;
            case STRING:
                str = cell.getStringCellValue();
                break;
            case FORMULA:
                try {
                    str = String.valueOf(cell.getNumericCellValue());
                } catch (IllegalArgumentException e) {
                    str = String.valueOf(cell.getRichStringCellValue());
                }
                break;
            default:
                str = "";
        }
        return str;
    }
}

package com.liyh.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.liyh.entity.ProjectItem;
import com.liyh.entity.Result;
import org.springframework.web.multipart.MultipartFile;

/**
 * @Author: arthur_Yang
 * @Date: 2020/10/23 17:44
 */
public interface ExcelService extends IService<ProjectItem> {

    Result importProject(MultipartFile file);
}

package com.liyh.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.liyh.entity.ProjectItem;
import org.apache.ibatis.annotations.Param;

/**
 * @Author: arthur_Yang
 * @Date: 2020/10/23 17:46
 */
public interface ExcelMapper extends BaseMapper<ProjectItem> {

    int insertProjectItem(@Param("datatime") String datatime,
                          @Param("orderno") String orderno,
                          @Param("pno") String pno,
                          @Param("paytypes") String paytypes,
                          @Param("type") String type,
                          @Param("pcode") String pcode,
                          @Param("price") String price,
                          @Param("yh") String yh,
                          @Param("prices") String prices,
                          @Param("device") String device,
                          @Param("pnos") String pnos,
                          @Param("types") String types,
                          @Param("paycade") String paycade,
                          @Param("numon") String numon,
                          @Param("ordernu") String ordernu);
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.liyh.mapper.ExcelMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="projectItemResultMap" type="com.liyh.entity.ProjectItem">
        <id column="id" property="id"/>
        <result column="datatime" property="datatime"/>
        <result column="orderno" property="orderno"/>
        <result column="pno" property="pno"/>
        <result column="paytypes" property="paytypes"/>
        <result column="type" property="type"/>
        <result column="pcode" property="pcode"/>
        <result column="price" property="price"/>
        <result column="yh" property="yh"/>
        <result column="prices" property="prices"/>
        <result column="device" property="device"/>
        <result column="pnos" property="pnos"/>
        <result column="types" property="types"/>
        <result column="paycade" property="paycade"/>
        <result column="numon" property="numon"/>
        <result column="ordernu" property="paycade"/>
        <result column="is_deleted" property="isDeleted"/>
    </resultMap>

    <insert id="insertProjectItem">
        insert into project_item (datatime,orderno,pno,paytypes,type,pcode,price,yh,prices,device,pnos,types,paycade,numon,ordernu)
        values(#{datatime},#{orderno},#{pno},#{paytypes},#{type},#{pcode},#{price},#{yh},#{prices},#{device},#{pnos},#{types},#{paycade},#{numon},#{ordernu})
    </insert>

</mapper>

package com.liyh.entity;

import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;

import java.io.Serializable;

/**
 * 项目清单表实体类
 *
 * @Author: arthur_Yang
 * @Date: 2020/10/23 17:05
 */
@Data
@TableName("project_item")
public class ProjectItem implements Serializable {

    private static final long serialVersionUID = 1L;
    private Integer id;
    private String datatime;
    private String orderno;
    private String pno;
    private String paytypes;
    private String type;
    private String pcode;
    private String price;
    private String yh;
    private String prices;
    private String device;
    private String pnos;
    private String types;
    private String paycade;
    private String numon;
    private String ordernu;
    private String is_deleted;


}

package com.liyh.entity;

import java.io.Serializable;
import java.util.List;

/**
 * 封装返回结果
 *
 * @Author: arthur_Yang
 * @Date: 2020/10/23 17:33
 */
public class Result implements Serializable {
    /**
     * 执行结果，true为执行成功 false为执行失败
     */

    private boolean flag;
    /**
     * 返回结果信息，主要用于页面提示信息
     */
    private String message;
    /**
     * 返回数据
     */
    private Object data;
    /**
     * LIST集合对象
     */
    private List list;

    public static Result success(String message) {
        return new Result(true, message);
    }

    public static Result success(String message, Object data) {
        return new Result(true, message, data);
    }

    public static Result error(String message) {
        return new Result(false, message);
    }

    public Result(boolean flag, String message) {
        super();
        this.flag = flag;
        this.message = message;
    }

    public Result(boolean flag, String message, Object data) {
        this.flag = flag;
        this.message = message;
        this.data = data;
    }

    public Result(List<ProjectItem> list) {
        this.list = list;
    }

    public boolean isFlag() {
        return flag;
    }

    public void setFlag(boolean flag) {
        this.flag = flag;
    }

    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }

    public Object getData() {
        return data;
    }

    public void setData(Object data) {
        this.data = data;
    }

    public List getList() {
        return list;
    }

    public void setList(List list) {
        this.list = list;
    }
}

# 配置端口
server:
  port: 8086

spring:
  # 配置数据源
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password: Tswork@

  thymeleaf:
    mode: LEGACYHTML5
    # 取消模板文件缓存
    cache: false

  #设定thymeleaf文件路径 默认为src/main/resources/templates
  freemarker:
    template-loader-path: classpath:/templates

  #设定静态文件路径，js,css等
  mvc:
    static-path-pattern: /static/**

  servlet:
    multipart:
      # 设置单个文件大小
      max-file-size: 200MB
      # 设置单次请求文件的总大小
      max-request-size: 200MB

# mybatis-plus相关配置
mybatis-plus:
  # xml扫描，多个目录用逗号或者分号分隔（告诉 Mapper 所对应的 XML 文件位置）
  mapper-locations: classpath*:com/liyh/mapper/xml/*.xml
  configuration:
    # 是否开启自动驼峰命名规则映射:从数据库列名到Java属性驼峰命名的类似映射
    map-underscore-to-camel-case: true

#打印sql，保存到文件
logging:
  level:
    com.liyh.mapper: debug

以上涉及字段及工具类可根据需求自行调整！！！！