解决java POI导入Excel超时问题

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

由于要导入大量数据，后台会耗费很长时间，导致超时。

本项目前端request.js中设定的超时时间为150s.

const service = axios.create({

    baseURL: baseUrl,

    withCredentials: true,

    timeout: 150000

});

我们的做法是：

前端导入Excel向后台发出请求时，后台立即返回信息“后台正在导入，请稍等！”，向redis中存入键isFinished的值为字符串“0”，并且开启一个线程来完成插入大量数据到数据库的工作，当插入完成则将redis中isFinished的值改为字符串“1”。前端收到“后台正在输入，请稍等！”后，此时导入按钮处于禁用状态并且处于加载状态。通过window.setInterval定时轮询，每隔30秒去后台查询ifFinished值，如果为字符串“1”表示上传完毕，此时在弹框“上传完毕！”，此时导入按钮禁用状态和加载状态都取消,并且刷新列表。

前端代码：

<div style="margin-bottom: 10px">

        <el-form :inline="true">

          <el-form-item label="">

            <el-button type="success" icon="el-icon-download" @click="downloadTemplate">下载模板</el-button>

            <el-button :loading="importLoading" type="primary" icon="el-icon-upload" @click="handleImport">导入

            </el-button>

            <el-upload ref="importUpload" :auto-upload="false" :show-file-list="false" :on-change="handleUploadChange"

              :disabled="importDisabled" style="display: inline" action="#" class="upload-demo">

              <el-button id="uploadButton" style="display: none" slot="trigger" :loading="importLoading" size="small"

                type="primary" icon="el-icon-upload">导入</el-button>

            </el-upload>

          </el-form-item>

          <el-form-item label="">

            <el-button type="warning" icon="el-icon-lightning" @click="exportExcel">导出</el-button>

          </el-form-item>

        </el-form>

      </div>

handleUploadChange(file) {
  if (file.name.lastIndexOf('.') < 0) {
    this.$message.error('上传文件只能是xls、xlsx格式!')
    return
  }
  const testMsg = file.name.substring(file.name.lastIndexOf('.') + 1).toLowerCase()
  const extensionXLS = testMsg == 'xls'
  const extensionXLSX = testMsg == 'xlsx'
  if (!extensionXLS && !extensionXLSX) {
    this.$message.error('上传文件只能是xls、xlsx格式!')
    return
  }
  const isLt2M = file.size / 1024 / 1024 < 2
  if (!isLt2M) {
    this.$message.error('上传文件不能超过 2MB!')
    return
  }
  this.importLoading = true
  this.importDisabled = true
  const data = new FormData()
  data.append('file', file.raw)
  medicineListApi.importExcel(data).then(response => {
    if (response.status == true) {
      this.open2(response.msg)
      this.getList()
    } else {
      this.open2(response.msg)
      this.importLoading = false
      this.importDisabled = false
    }
    window.setInterval(() => {
      setTimeout(this.getStatus(), 0)
    }, 30*1000)
  }).catch(() => {
    this.open2('抱歉，导入失败')
    this.importLoading = false
    this.importDisabled = false
  })

},
open2(str) {
  this.$notify({
    title: '提示',
    message: str,
    duration: 0
  })
},

// 请求后台获取是否导入完成的状态
getStatus(){
  medicineListApi.getStatus().then(response => {
    if (response.data == "1") {
      this.open2("上传完毕！")
      this.importLoading = false
      this.importDisabled = false
      this.getList()
    }
  })
}

项目中我们经常需要实现轮询-每隔几秒请求一次接口刷新数据，一般都会使用setInterval，但要注意单纯使用它会导致页面卡死，解释：setInterval不会清除定时器队列，每重复执行1次都会导致定时器叠加，最终卡死你的网页。但是setTimeout是自带清除定时器的

解决办法：

window.setInterval(() => {

  setTimeout(fun, 0)

}, 30000)

setTimeout() 方法用于在指定的毫秒数后调用函数或计算表达式。如果你只想重复执行可以使用 setInterval() 方法。setTimeout()只执行一次，而setInterval可以多次调用。

medicineList.js代码：

import request from "./request";

const baseUrl = "/medicineList"

export const medicineListApi = {

  /**

   * 导入

   * @param data

   */

  importExcel(data) {

    return request({

      url: baseUrl + '/import',

      method: 'post',

      data: data,

      headers: {

        'Content-Type': 'multipart/form-data'

      }

    })

  },

  getStatus() {

    return request({

      url: baseUrl + "/getStatus",

      method: "GET"

    });

  },

};

后台代码：

controller：

@RestController

@RequestMapping("/medicineList")

@Slf4j

public class MedicineListController extends BaseController {

    @Autowired

    private MedicineListService medicineListService;

    /**

     * 导入药品信息

     *

     * @param file

     * @return

     * @throws Exception

     */

    @PostMapping("/import")

    public JSONObject importNodeInfo(MultipartFile file) throws Exception {

        return medicineListService.importNodeInfo(file.getInputStream());

    }

    @GetMapping("/getStatus")

    public JSONObject getStatus() {

        return medicineListService.getStatus();

    }

}

service接口：

public interface MedicineListService extends IService<DrugData> {

    JSONObject importNodeInfo(InputStream inputStream) throws Exception;

    JSONObject getStatus();

}

service实现类：

@Service

public class MedicineListServiceImpl extends ServiceImpl<MedicineListMapper,DrugData> implements MedicineListService {

    @Resource

    private MedicineListMapper medicineListMapper;

    private static Logger logger = LoggerFactory.getLogger(MedicineListService.class);

    @Autowired

    private StringRedisTemplate redisTemplate;

    public JSONObject importNodeInfo(InputStream in) throws Exception {

        redisTemplate.opsForValue().set("isFinished","0",60*60l,TimeUnit.SECONDS);

        JSONObject json = new JSONObject();

        json.put("msg", "后台正在导入，请稍等！");

        json.put("status", true);

        new Thread() {

            @Override

            public void run() {

                try {

                    //   根据类型进行分流导入

                    String str0 = "";

                    String str = "";

                    String fstr = "";

                    int operCount = 0;

                    XSSFWorkbook workbook = new XSSFWorkbook(in);

                    XSSFSheet sheet = workbook.getSheetAt(0);

                    int totalColumnNum = sheet.getRow(0).getLastCellNum();

                    logger.info("导入代码信息excel文件的总列数:" + totalColumnNum);

                    System.out.println(totalColumnNum);

                    int lastRowNum = sheet.getLastRowNum();

                    logger.info("导入节点信息excel文件的总行数:" + lastRowNum);

                    System.out.println(sheet.getLastRowNum());

                    for (int num = 0; num <= lastRowNum; num++) {

                        XSSFRow row = sheet.getRow(num);

                        if(row == null) {

                            str0 = "存在空数据行，行号：" + (num + 1) + "，导入失败！";

                            break;

                        }

                        int hcount = num + 1;

                        if (num == 0) {

                            if (null != String.valueOf(row.getCell(0)) && String.valueOf(row.getCell(0)).equals("药品编码")) {

                                continue;

                            } else {

                                json.put("msg", "导入的模板名称出错，请确认");

                                json.put("status", false);

                                json.put("data", operCount);

                            }

                        }

                        DrugData drugData = new DrugData();

                        String drugNo = String.valueOf(row.getCell(0));

                        if(StringUtils.isNotBlank(drugNo) && !"null".equalsIgnoreCase(drugNo)) {

                            drugData.setDrugno(drugNo);// 药品编码

                        }

                        String drugName = String.valueOf(row.getCell(1));

                        if(StringUtils.isNotBlank(drugName) && !"null".equalsIgnoreCase(drugName)) {

                            drugData.setDrugname(drugName);//药品名称

                        }

                        String indiction = String.valueOf(row.getCell(2));

                        if(StringUtils.isNotBlank(indiction) && !"null".equalsIgnoreCase(indiction)) {

                            drugData.setIndiction(indiction); //适应症

                        }

                        try {

                            QueryWrapper<DrugData> wrapper = new QueryWrapper<>();

                            if(StringUtils.isNotBlank(drugData.getDrugno())){

                                wrapper.eq("drugno",drugData.getDrugno());

                            }

                            List<DrugData> drugDataList = medicineListMapper.selectList(wrapper);

                            if (null != drugDataList && drugDataList.size() > 0) {

                                drugData.setId(drugDataList.get(0).getId());

                                medicineListMapper.updateById(drugData);

                            } else {

                                medicineListMapper.insert(drugData);

                            }

                        } catch (Exception e) {

                            logger.error(e.getMessage());

                            str = str + "第【" + hcount + "】行，";

                            continue;

                        }

                        operCount++;

                    }

                    if (StringUtils.isNotBlank(str)) {

                        str = "其中-->" + str + "导入失败！";

                    }

                    if (StringUtils.isNotBlank(fstr)) {

                        fstr = "==投量-->" + fstr + "附表导入失败！";

                    }

                    redisTemplate.opsForValue().set("isFinished","1");

                    json.put("msg", "操作成功" + str0 + str + fstr);

                    json.put("status", true);

                    json.put("data", operCount);

                } catch (Exception e) {

                    logger.error(e.getMessage());

                } finally {

                    try {

                        in.close();

                    } catch (IOException e) {

                        logger.error(e.getMessage());

                    }

                }

            }

        }.start();

        return json;

    }

    @Override

    public JSONObject getStatus() {

        String isFinished = redisTemplate.opsForValue().get("isFinished");

        JSONObject result = new JSONObject();

        if (StringUtils.isNotBlank(isFinished)) {

            result.put("msg", "获取成功");

            result.put("status", true);

            result.put("data",isFinished);

        } else {

            result.put("msg", "获取失败");

            result.put("status", false);

        }

        redisTemplate.delete("isFinished");

        return result;

    }

}