使用Java完成Excel文件的上傳、內(nèi)容的解析和以及保存操作。重點(diǎn)主要在于使用org.apache.poi包下的Workbook類完成對Excel內(nèi)容的解析

首先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程序?qū)icrosoft Office（Excel橄妆、Word、PowerPoint等）格式檔案讀和寫的功能

代碼順序?yàn)?br> controller → service → mapper

controller

接口用于接收前臺頁面上傳的excel文件，并對MultipartFile參數(shù)做一些基本的判斷

@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參數(shù)為空匆笤！");
                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文件內(nèi)容為空谱邪！");
                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

實(shí)現(xiàn)service接口惦银，主要數(shù)據(jù)的操作全部在serviceImpl類中實(shí)現(xiàn)

@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數(shù)據(jù)為空！");
                return result;
            }

            // 列數(shù)
            int column = sheet.getRow(0).getPhysicalNumberOfCells();
            LOGGER.debug("Excel列數(shù)：" + column);
            // 此處可判斷Excel列數(shù)是否符合要求

            // 行數(shù)
            int rows = sheet.getLastRowNum();
            LOGGER.debug("Excel行數(shù)：" + rows);
            // 此處可判斷Excel行數(shù)是否符合要求

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

            // 循環(huán)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中用戶名稱為必填項(xiàng)扯俱，不能為空书蚪，請?zhí)顚懞笤龠M(jìn)行上傳！");
                        return result;
                    }
                    temporary.setUserName(userName);
                }

                // 手機(jī)號
                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中用戶手機(jī)號為必填項(xiàng)迅栅，不能為空殊校，請?zhí)顚懞笤龠M(jìn)行上傳！");
                        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為必填項(xiàng)读存，不能為空为流，請?zhí)顚懞笤龠M(jìn)行上傳！");
                        return result;
                    }
                    temporary.setQq(qq);
                }
                //添加進(jìn)list
                excelData.add(temporary);
            }

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

            // 做插入處理
            if (excelData.size() > 0) {
                // 將Excel數(shù)據(jù)插入數(shù)據(jù)庫
                int i = uploadEexcelMapper.insertExcelData(excelData);
                if (i == excelData.size()) {
                    // 數(shù)據(jù)全部插入成功
                    result.setMessage("success");
                }
            }
            return result;
        } catch (IOException e) {
            e.printStackTrace();
            LOGGER.error(">>>>>>>>>>>>>保存Excel數(shù)據(jù)到數(shù)據(jù)庫【UploadExcelFileServiceImpl.uploadExcel()】執(zhí)行異常:" + e);
            throw new ServiceException(e.getMessage());
        }
        result.setMessage("數(shù)據(jù)保存失敗让簿，請稍候重試敬察！");
        return result;
    }

mapper接口

@Mapper
public interface UploadEexcelMapper{

    int insertExcelData(List<ExcelDataBean> item);

}

mapper.xml，使用foreach將數(shù)據(jù)保存至數(shù)據(jù)庫

    <!-- 將excel數(shù)據(jù)批量插入數(shù)據(jù)庫 -->
    <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選擇框拜英，和一個確認(rèn)上傳按鈕

<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請求調(diào)用接口上傳excel文件到后臺

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

    //判斷是否已經(jīng)選擇了文件
    var selectFile = $("#fileSelect").val();
    if (selectFile == null || selectFile == '') {
        layer.alert("請先選擇文件！");
        return;
    }

    layer.confirm('上傳前請先確認(rèn)文件內(nèi)容是否填寫完整居凶，確認(rèn)上傳此文件嗎虫给？', {
        icon: 3, move: false, title: '提示', closeBtn: 0,
        btn: ['確認(rèn)', '返回']
    }, function () {
        let loading = layer.load(2);
        //多次點(diǎn)擊只執(zhí)行一次
        var oneClick = 1;
        if (oneClick == 1) {
            //執(zhí)行上傳文件操作
            $.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("非法參數(shù)！", {
                            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) {
                                    //只關(guān)閉提醒彈窗
                                    layer.close(index);
                                }
                            });
                        }
                    }
                },
                error: function () {
                    layer.alert("操作請求錯誤侠碧，請您稍后再試", function () {
                        layer.closeAll();
                    });
                },
                complete: function () {
                    // 請求完成后
                    oneClick = 1;
                    layer.close(loading);
                }
            });
        }

    });

}

大概整個流程就這樣……

其它使用到的類

實(shí)體類ExcelDataBean ?? 只簡寫了用戶名抹估、手機(jī)號和QQ三個參數(shù)

public class ExcelDataBean {

    // 用戶名
    private String userName;

    // 手機(jī)號
    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 + '\'' +
                '}';
    }
}

請求響應(yīng)結(jié)果封裝類ResponseResult，有code弄兜、message和obj三個參數(shù)

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

    private String code;
    private String message;
    private Object obj;
    
    public ResponseResult() {
        // 默認(rèn)是返回成功的
        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 + '}';
    }
}

自定義服務(wù)層接口異常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);
    }

}

響應(yīng)狀態(tài)信息類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;
        }
    }
}