使用Java完成Excel文件的上传、内容的解析和以及保存操作。重点主要在于使用org.apache.poi包下的Workbook类完成对Excel内容的解析

首先pom文件引入Apache poi，

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>

Apache POI提供API给Java程序对Microsoft Office（Excel、Word、PowerPoint等）格式档案读和写的功能

代码顺序为
controller → service → mapper

controller

接口用于接收前台页面上传的excel文件，并对MultipartFile参数做一些基本的判断

@Controller
@RequestMapping("/upload")
public class UploadExcelFileController {

    private static final Logger LOGGER = LoggerFactory.getLogger(UploadExcelFileController.class);

    @Autowired
    private UploadExcelFileService uploadExcelFileService;

    @PostMapping("/excelFile")
    @ResponseBody
    public ResponseResult uploadExcel(@RequestParam(value = "excelFile") MultipartFile file) {
        LOGGER.debug("开始上传Excel文件");
        ResponseResult result = new ResponseResult();
        try {
            if (file == null) {
                // 文件不能为空
                LOGGER.info("file参数为空！");
                result.setCode(IStatusMessage.SystemStatus.NOT_ALLOWED_EMPTY_FILE.getCode());
                result.setMessage(IStatusMessage.SystemStatus.NOT_ALLOWED_EMPTY_FILE.getMessage());
                return result;
            }

            String fileName = file.getOriginalFilename();
            if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
                // 文件格式不正确
                LOGGER.info("Excel文件格式不正确！");
                result.setCode(IStatusMessage.SystemStatus.FILE_FORMAT_IS_INCORRECT.getCode());
                result.setMessage(IStatusMessage.SystemStatus.FILE_FORMAT_IS_INCORRECT.getMessage());
                return result;
            }

            long size = file.getSize();
            if (StringUtils.isEmpty(fileName) || size == 0) {
                // 文件不能为空
                LOGGER.info("Excel文件内容为空！");
                result.setCode(IStatusMessage.SystemStatus.NOT_ALLOWED_EMPTY_FILE.getCode());
                result.setMessage(IStatusMessage.SystemStatus.NOT_ALLOWED_EMPTY_FILE.getMessage());
                return result;
            }

            result = uploadExcelFileService.uploadExcel(fileName, file, existUser.getId(), result);

            if (result.getMessage().equals("success")) {
                //保存成功
                LOGGER.info("上传Excel文件，文件上传成功！");
                result.setCode(IStatusMessage.SystemStatus.UPLOAD_EXCEL_SUCCESS.getCode());
                result.setMessage(IStatusMessage.SystemStatus.UPLOAD_EXCEL_SUCCESS.getMessage());
            }
        } catch (ServiceException e) {
            e.printStackTrace();
            result.setCode(IStatusMessage.SystemStatus.REQUEST_FAILED.getCode());
            result.setMessage(IStatusMessage.SystemStatus.REQUEST_FAILED.getMessage());
            LOGGER.error("上传Excel文件异常", e);
        }

        return result;
    }

service

定义service接口

public interface UploadExcelFileService{

    ResponseResult uploadExcel(MultipartFile file,ResponseResult result) throws ServiceException;

}

serviceImpl

实现service接口，主要数据的操作全部在serviceImpl类中实现

@Service
public class UploadExcelFileServiceImpl implements UploadExcelFileService {

    private static final Logger LOGGER = LoggerFactory.getLogger(UploadExcelFileServiceImpl.class);

    @Autowired
    private UploadEexcelMapper uploadEexcelMapper;

    @Override
    public ResponseResult uploadExcel(MultipartFile file, ResponseResult result) throws ServiceException {
        try {
            LOGGER.debug("开始处理Excel文件！");
            InputStream inputStream = file.getInputStream();
            Workbook wb;
            if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
                wb = new XSSFWorkbook(inputStream);
            } else {
                wb = new HSSFWorkbook(inputStream);
            }
            Sheet sheet = wb.getSheetAt(0);
            if (sheet == null) {
                result.setMessage("Excel数据为空！");
                return result;
            }

            // 列数
            int column = sheet.getRow(0).getPhysicalNumberOfCells();
            LOGGER.debug("Excel列数：" + column);
            // 此处可判断Excel列数是否符合要求

            // 行数
            int rows = sheet.getLastRowNum();
            LOGGER.debug("Excel行数：" + rows);
            // 此处可判断Excel行数是否符合要求

            List<ExcelDataBean> excelData = new ArrayList<>();
            ExcelDataBean temporary;

            // 循环Excel
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }

                temporary = new ExcelDataBean();

                // 用户名
                if (row.getCell(0) != null) {
                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                    String userName = row.getCell(0).getStringCellValue();
                    if (userName == null || userName.isEmpty()) {
                        result.setMessage("Excel中用户名称为必填项，不能为空，请填写后再进行上传！");
                        return result;
                    }
                    temporary.setUserName(userName);
                }

                // 手机号
                if (row.getCell(1) != null) {
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    String mobile = row.getCell(1).getStringCellValue();
                    if (mobile == null || mobile.isEmpty()) {
                        result.setMessage("Excel中用户手机号为必填项，不能为空，请填写后再进行上传！");
                        return result;
                    }
                    temporary.setMobile(mobile);
                }

                // QQ
                if (row.getCell(2) != null) {
                    row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                    String qq = row.getCell(2).getStringCellValue();
                    if (qq == null || qq.isEmpty()) {
                        result.setMessage("Excel中用户QQ为必填项，不能为空，请填写后再进行上传！");
                        return result;
                    }
                    temporary.setQq(qq);
                }
                //添加进list
                excelData.add(temporary);
            }

            // 此处省略其他操作处理
            // 此处省略其他操作处理

            // 做插入处理
            if (excelData.size() > 0) {
                // 将Excel数据插入数据库
                int i = uploadEexcelMapper.insertExcelData(excelData);
                if (i == excelData.size()) {
                    // 数据全部插入成功
                    result.setMessage("success");
                }
            }
            return result;
        } catch (IOException e) {
            e.printStackTrace();
            LOGGER.error(">>>>>>>>>>>>>保存Excel数据到数据库【UploadExcelFileServiceImpl.uploadExcel()】执行异常:" + e);
            throw new ServiceException(e.getMessage());
        }
        result.setMessage("数据保存失败，请稍候重试！");
        return result;
    }

mapper接口

@Mapper
public interface UploadEexcelMapper{

    int insertExcelData(List<ExcelDataBean> item);

}

mapper.xml，使用foreach将数据保存至数据库

    <!-- 将excel数据批量插入数据库 -->
    <insert id="insertExcelData" parameterType="java.util.List">
        INSERT INTO user_from_excel (username,mobile,qq)
        VALUES
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.username},
            #{item.mobile},
            #{item.qq}
            )
        </foreach>
    </insert>

html页面主要是一个input选择框，和一个确认上传按钮

<input id="fileSelect" type="file" name="excelFile"
           accept=".xls,.xlsx" style="margin-top: 6px"/>

<a class="layui-btn" id="uploadExcel" onclick="uploadExcel()">开始上传</a>

js，使用ajax请求调用接口上传excel文件到后台

/**
 * 上传Excel文件
 */
function uploadExcel() {

    //判断是否已经选择了文件
    var selectFile = $("#fileSelect").val();
    if (selectFile == null || selectFile == '') {
        layer.alert("请先选择文件！");
        return;
    }

    layer.confirm('上传前请先确认文件内容是否填写完整，确认上传此文件吗？', {
        icon: 3, move: false, title: '提示', closeBtn: 0,
        btn: ['确认', '返回']
    }, function () {
        let loading = layer.load(2);
        //多次点击只执行一次
        var oneClick = 1;
        if (oneClick == 1) {
            //执行上传文件操作
            $.ajax({
                type: "POST",
                data: new FormData($('#uploadForm')[0]),
                async: false,
                processData: false,
                contentType: false,
                url: rootUrl + "/upload/excelFile",
                beforeSend: function () {
                    oneClick++;
                },
                success: function (datas) {
                    if (datas.code == null || typeof(datas.code) === 'undefined') {
                        layer.alert("非法参数！", {
                            time: 0,
                            closeBtn: 0,
                            btn: ["确定"],
                            yes: function () {
                                layer.closeAll();
                                // 去列表页面
                                window.location.href = "/";
                            }
                        });
                    } else {
                        if (datas.code == 1104) {
                            //文件上传成功
                            layer.alert(datas.message, {
                                time: 0,
                                closeBtn: 0,
                                btn: ["确定"],
                                yes: function () {
                                    layer.closeAll();
                                    // 去列表页面
                                    window.location.href = "/";
                                }
                            });
                        } else {
                            //上传失败
                            layer.alert(datas.message, {
                                time: 0,
                                closeBtn: 0,
                                btn: ["确定"],
                                yes: function (index) {
                                    //只关闭提醒弹窗
                                    layer.close(index);
                                }
                            });
                        }
                    }
                },
                error: function () {
                    layer.alert("操作请求错误，请您稍后再试", function () {
                        layer.closeAll();
                    });
                },
                complete: function () {
                    // 请求完成后
                    oneClick = 1;
                    layer.close(loading);
                }
            });
        }

    });

}

大概整个流程就这样……

---

---

其它使用到的类

实体类ExcelDataBean 👇 只简写了用户名、手机号和QQ三个参数

public class ExcelDataBean {

    // 用户名
    private String userName;

    // 手机号
    private String mobile;

    // QQ号
    private String qq;

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getMobile() {
        return mobile;
    }

    public void setMobile(String mobile) {
        this.mobile = mobile;
    }

    public String getQq() {
        return qq;
    }

    public void setQq(String qq) {
        this.qq = qq;
    }

    @Override
    public String toString() {
        return "ExcelDataBean{" +
                "userName='" + userName + '\'' +
                ", mobile='" + mobile + '\'' +
                ", qq='" + qq + '\'' +
                '}';
    }
}

请求响应结果封装类ResponseResult，有code、message和obj三个参数

public class ResponseResult implements Serializable {
    
    private static final long serialVersionUID = 728506566686199394L;

    private String code;
    private String message;
    private Object obj;
    
    public ResponseResult() {
        // 默认是返回成功的
        this.code = IStatusMessage.SystemStatus.SUCCESS.getCode();
        this.message = IStatusMessage.SystemStatus.SUCCESS.getMessage();
    }
    
    public ResponseResult(IStatusMessage statusMessage){
        this.code = statusMessage.getCode();
        this.message = statusMessage.getMessage();
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getMessage() {
        return message;
    }

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

    public Object getObj() {
        return obj;
    }

    public void setObj(Object obj) {
        this.obj = obj;
    }

    @Override public String toString() {
        return "ResponseResult{" + "code='" + code + '\'' + ", message='"
                + message + '\'' + ", obj=" + obj + '}';
    }
}

自定义服务层接口异常ServiceException

public class ServiceException extends Exception {

    private static final long serialVersionUID = -8265701868248066795L;

    public ServiceException() {
        super();
    }

    public ServiceException(String message) {
        super(message);
    }

    public ServiceException(Throwable throwable) {
        super(throwable);
    }

    public ServiceException(String message, Throwable throwable) {
        super(message, throwable);
    }

}

响应状态信息类IStatusMessage

public interface IStatusMessage {

    String getCode();

    String getMessage();

    public enum SystemStatus implements IStatusMessage {
        NOT_ALLOWED_EMPTY_FILE("1112", "上传文件不能为空"),
        FILE_FORMAT_IS_INCORRECT("1103", "上传文件格式不正确"),
        NOT_ALLOWED_EMPTY_FILE("1112", "上传文件不能为空"),
        UPLOAD_EXCEL_SUCCESS("1104", "文件上传成功"),
        REQUEST_FAILED("1101", "请求失败，请您稍后再试"),
        ;

        private String code;
        private String message;

        private SystemStatus(String code, String message) {
            this.code = code;
            this.message = message;
        }

        public String getCode() {
            return this.code;
        }

        public String getMessage() {
            return this.message;
        }
    }
}