Java读取Excel文件数据并存入实体类

-----------------------------------------------------话不多说，直接干货！----------------------------------------------

第一步：

引入HuTool依赖：

<!--Hutool Java工具包-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.4.5</version>
        </dependency>

第二步：

创建实体类：（此处的注解可以不用，手动生成get/set方法也行）

package kr.wms.domain.model;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class CmCustomSalesForecast {

    private Long submitTime;

    private String region;

    private String customer;

    private String customerBaseTable;

    private String erpCode;

    private String materialCodeCompany;

    private String specificationAndModel;

    private String salesForecast;

    private String month;

    private String materialCode;

    private String creator;

    private String createPerson;

    private String code;

    private Long createTime;
}

第三步：

在controller层创建接口，通过

MultipartFile 上传文件

！！！注意：此处的

JsonResult可以随意替换成你需要的返回值，这是我这边代码封装的返回值

package kr.wms.web;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import kr.wms.domain.service.CmCustomStockService;
import kr.wms.domain.service.StockOutService;
import lombok.extern.slf4j.Slf4j;
import microsvr.core.utility.result.JsonResult;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import java.util.Map;


@PostMapping(value = "importTheEstimateTable")
    @ApiOperation(value = "PC-导入表")
    public JsonResult importTheEstimateTable(@RequestPart MultipartFile file) {
        try {
            //PC-导入表
            return stockOutService.importTheEstimateTable(file);
        } catch (Exception e) {
            e.printStackTrace();
            return JsonResult.operationError("导入失败！"+e.getMessage());
        }
    }

第四步：

创建service接口

/**
     * 导入预估表
     * @param file
     * @return
     */
    JsonResult importTheEstimateTable(MultipartFile file) throws Exception;

第五步：

创建实现层：

/**
     * 导入表
     * @param file
     * @return
     */
    @Override
    public JsonResult importTheEstimateTable(MultipartFile file) throws Exception {
        if (file != null) {
            ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
            List<List<Object>> lists = reader.read();
            //CmCustomSalesForecast为你需要的实体类
            CmCustomSalesForecast newForecast = new CmCustomSalesForecast();
            List<CmCustomSalesForecast> cmCustomSalesForecastList = Lists.newArrayList();
            SimpleDateFormat formatTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            SimpleDateFormat formatDay = new SimpleDateFormat("yyyy-MM-dd");
            Calendar cal = Calendar.getInstance();
            int i = 1;
            int row = 1;
            if (lists != null && lists.size() > 0) {
                //遍历文件每行数据
                for (List<Object> list : lists) {
                    if (row ==1){
                        row ++;
                        continue;
                    }
                    //遍历文件每列数据
                    for (Object excel : list) {
                        if (i == 1) {
                            String time = String.valueOf(excel);
                            if (time.contains(":")) {
                                cal.setTime(formatTime.parse(time));
                                newForecast.setSubmitTime(cal.getTimeInMillis());
                            } else if (time.contains("/")) {
                                cal.setTime(formatDay.parse(time));
                                newForecast.setSubmitTime(cal.getTimeInMillis());
                            }
                            i++;
                            continue;
                        }
                        if (i == 2) {
                            newForecast.setRegion(String.valueOf(excel));
                            i++;
                            continue;
                        }
                        if (i == 3) {
                            newForecast.setCustomer(String.valueOf(excel));
                            i++;
                            continue;
                        }
                        if (i == 4) {
                            newForecast.setCustomerBaseTable(String.valueOf(excel));
                            i++;
                            continue;
                        }
                        if (i == 5) {
                            newForecast.setErpCode(String.valueOf(excel));
                            i++;
                            continue;
                        }
                        if (i == 6) {
                            newForecast.setMaterialCodeCompany(String.valueOf(excel));
                            i++;
                            continue;
                        }
                        if (i == 7) {
                            newForecast.setSpecificationAndModel(String.valueOf(excel));
                            i++;
                            continue;
                        }
                        if (i == 8) {
                            newForecast.setSalesForecast(String.valueOf(excel));
                            i++;
                            continue;
                        }
                        if (i == 9) {
                            newForecast.setMonth(String.valueOf(excel));
                            cmCustomSalesForecastList.add(newForecast);
                            //重置列循环
                            i = 1;
                            newForecast = new CmCustomSalesForecast();
                        }
                    }
                }

                if (cmCustomSalesForecastList.size() > 0) {
                    for (CmCustomSalesForecast forecast : cmCustomSalesForecastList) {
                        String region = forecast.getRegion();
                        String customerBaseTable = forecast.getCustomerBaseTable();
                        String materialCodeCompany = forecast.getMaterialCodeCompany();
                        String month = forecast.getMonth();
                        List<CmCustomSalesForecast> oldForecastList = cmCustomSalesForecastMapper.selectByCustomer(region, customerBaseTable, materialCodeCompany, month,null);
                        if (oldForecastList.size()==0) {
                            forecast.setCode(System.currentTimeMillis() + "");
                            forecast.setCreator(Common.getCurrentUserName());
                            forecast.setCreatePerson(Common.getCurrentUserId() + "");
                            forecast.setCreateTime(Common.getTimeInMillis());
                            int insertForecast = cmCustomSalesForecastMapper.insert(forecast);
                            if (insertForecast == 0) {
                                throw new Exception("新增失败！");
                            }
                        } else {
                            CmCustomSalesForecast oldForecast = oldForecastList.get(0);
                            oldForecast.setSubmitTime(forecast.getSubmitTime());
                            oldForecast.setRegion(forecast.getRegion());
                            oldForecast.setCustomer(forecast.getCustomer());
                            oldForecast.setCustomerBaseTable(forecast.getCustomerBaseTable());
                            oldForecast.setErpCode(forecast.getErpCode());
                            oldForecast.setMaterialCodeCompany(forecast.getMaterialCodeCompany());
                            oldForecast.setSpecificationAndModel(forecast.getSpecificationAndModel());
                            oldForecast.setSalesForecast(forecast.getSalesForecast());
                            oldForecast.setMonth(forecast.getMonth());
                            int updateForecast = cmCustomSalesForecastMapper.updateByPrimaryKey(oldForecast);
                            if (updateForecast == 0) {
                                throw new Exception("修改失败！");
                            }
                        }
                    }
                }
            } else {
                throw new Exception("解析EXCEL文件失败！");
            }
            return JsonResult.operationSuccessful("导入成功！");
        } else {
            return JsonResult.operationError("参数不能为空！");
        }
    }

注意：

关键的一步便是使用HuTool的

ExcelUtil.getReader()

这个方法，通过读取输入流获取excel里面的文件内容，读出来以后便可实现数据的获取！