Implement Excel Import/Export in RuoYi’s Front‑Back‑End Separation Using ElementUI and SpringBoot

The article demonstrates a complete implementation of Excel import and export in the RuoYi management system’s front‑back‑end separation version, using Vue with ElementUI on the front end and SpringBoot with POI on the back end.

Excel Import – Frontend

A dialog is added to the page using <el-dialog> with :visible.sync="upload.open" to control visibility. Inside the dialog, an <el-upload> component is configured:

<el-dialog :title="upload.title" :visible.sync="upload.open" width="400px" append-to-body>
  <el-upload
    ref="upload"
    :limit="1"
    accept=".xlsx, .xls"
    :headers="upload.headers"
    :action="upload.url"
    :disabled="upload.isUploading"
    :on-progress="handleFileUploadProgress"
    :on-success="handleFileSuccess"
    :auto-upload="false"
    :data="{updateSupport:upload.updateSupport}"
    drag>
    <i class="el-icon-upload"></i>
    <div class="el-upload__text">将文件拖到此处，或 <em>点击上传</em></div>
    <div class="el-upload__tip" slot="tip">
      <el-checkbox v-model="upload.updateSupport" />是否更新已经存在的下井次数设置数据
      <el-link type="info" style="font-size:12px" @click="downloadTemplate('xjszTemplate.xlsx')">下载模板</el-link>
    </div>
    <div class="el-upload__tip" slot="tip" style="color:red">提示：仅允许导入“xls”或“xlsx”格式文件！是否全勤中：1代表全勤，0代表固定次数，不得有空值！！</div>
  </el-upload>
  <div slot="footer" class="dialog-footer">
    <el-button type="primary" @click="submitFileForm">确定</el-button>
    <el-button @click="upload.open = false">取消</el-button>
  </div>
</el-dialog>

The component’s data model includes fields for visibility, title, upload status, token headers (using getToken() from @/utils/auth), the upload URL, and an updateSupport flag bound to a checkbox.

Upload progress is handled by handleFileUploadProgress, which sets upload.isUploading = true. On success, handleFileSuccess closes the dialog, resets the uploading flag, clears the file list, shows an alert with the server response, and refreshes the data list.

The "Confirm" button triggers submitFileForm, which calls this.$refs.upload.submit() to send the file to the backend.

Excel Import – Backend

The SpringBoot controller defines an endpoint @RequestMapping("/importData") that receives a MultipartFile and a boolean updateSupport parameter:

@RequestMapping("/importData")
@ResponseBody
@ApiOperation("导入下井次数设置数据")
public AjaxResult importData(@RequestParam MultipartFile file, @RequestParam boolean updateSupport) throws Exception {
    ExcelUtil<KqXjcssz> util = new ExcelUtil<KqXjcssz>(KqXjcssz.class);
    List<KqXjcssz> xjcsszList = util.importExcel(file.getInputStream());
    for (KqXjcssz xjcssz : xjcsszList) {
        if (xjcssz.getGh() == null) {
            return AjaxResult.error("存在为空的工号数据");
        }
        xjcssz.setSzrq(new Date());
        xjcssz.setSzr(SecurityUtils.getUsername());
        Integer count = kqXjcsszService.isExistByGh(xjcssz.getGh());
        if (count > 0) {
            if (updateSupport) {
                kqXjcsszService.updateKqXjcssz(xjcssz);
            }
        } else {
            kqXjcsszService.insertKqXjcssz(xjcssz);
        }
    }
    return AjaxResult.success("导入成功");
}

The method uses RuoYi’s ExcelUtil to parse the uploaded file into a list of entity objects, validates required fields, sets audit information, checks for existing records, and either updates or inserts based on the updateSupport flag.

Excel Export – Frontend

An export button is added with type="warning" and an @click="handleExport" handler:

<el-button type="warning" icon="el-icon-download" size="mini" @click="handleExport" v-hasPermi="['kqgl:bcgl:export']">导出</el-button>

The handler confirms the action, then calls exportBcgl(queryParams) from an imported API module, and finally triggers a file download:

handleExport() {
  const queryParams = this.queryParams;
  this.$confirm("是否确认导出所有数据项?", "警告", { confirmButtonText: "确定", cancelButtonText: "取消", type: "warning" })
    .then(() => exportBcgl(queryParams))
    .then(response => { this.download(response.msg); })
    .catch(() => {});
}

The API function sends a GET request to /kqgl/bcgl/export with the query parameters:

export function exportBcgl(query) {
  return request({ url: '/kqgl/bcgl/export', method: 'get', params: query });
}

Excel Export – Backend

The corresponding SpringBoot controller method uses ExcelUtil to write the data list to an Excel file:

@GetMapping("/export")
public AjaxResult export(KqBcgl kqBcgl) {
    List<KqBcgl> list = kqBcglService.getBcListByNameToExport(kqBcgl);
    ExcelUtil<KqBcgl> util = new ExcelUtil<KqBcgl>(KqBcgl.class);
    return util.exportExcel(list, "bcgl");
}

Entity fields are annotated with @Excel(name = "列名") to control column titles, types, widths, and other export properties. The article also shows how to join dictionary tables in the MyBatis query to replace code values with readable labels before export.

Overall, the guide provides a step‑by‑step walkthrough of the full stack required to import and export Excel files in a RuoYi‑based application, covering UI components, token handling, request configuration, server‑side parsing, validation, conditional updates, and annotation‑driven export.