POI-Excel导入导出开发文档

POI-Excel导入导出开发文档

此处导入导出功能为集成Jeecg-boot开源项目中的功能，在此基础上移植到项目中。

1、在pom.xml中引入添加依赖

		<dependency>
			<groupId>org.jeecgframework</groupId>
			<artifactId>autopoi-web</artifactId>
			<version>1.0.1</version>
		</dependency>

2、导出Controller

    /**
     * excel 导出
     */
    @RequestMapping("/export")
    @RequiresPermissions("generator:testuser:export")
    public void export(@RequestParam Map<String, Object> params) throws IOException {

        response.setHeader("Content-Disposition", "attachment;filename=" + "testUser.xls");
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);

        PageUtils page = testUserService.queryPage(params);
        String userName = ((SysUserEntity)SecurityUtils.getSubject().getPrincipal()).getUsername();
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("testUser", userName, "导出信息"), TestUserEntity.class, page.getList());
        OutputStream output = response.getOutputStream();
        BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
        bufferedOutPut.flush();
        workbook.write(bufferedOutPut);
        bufferedOutPut.close();
        workbook.write(output);
        output.flush();
        output.close();

    }

2.1 获取当前用户名

  String userName = ((SysUserEntity)SecurityUtils.getSubject().getPrincipal()).getUsername();

2.2 获取导出的Excel文档对象

 Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("testUser", userName, "导出信息"), TestUserEntity.class, page.getList());

ExportParams参数介绍

/**
title：Excel标题名称，可以设置为类名称，表示导出的信息是什么
secondTitle：二级标题名称，可以设置为当前用户名称，表示是谁导出的信息
sheetName：工作表名称
*/
public ExportParams(String title, String secondTitle, String sheetName) {
		this.title = title;
		this.secondTitle = secondTitle;
		this.sheetName = sheetName;
	}

3、导出

    /**
     * excel 导入
     */
    @RequestMapping(value="/import",method=RequestMethod.POST)
    @RequiresPermissions("generator:testuser:import")
    public R impotr(HttpServletRequest request) throws Exception {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
            MultipartFile file = entity.getValue();// 获取上传文件对象
            ImportParams params = new ImportParams();
            params.setTitleRows(2);
            params.setHeadRows(1);
            params.setNeedSave(false);
            try {
                List<TestUserEntity> list = ExcelImportUtil.importExcel(file.getInputStream(), TestUserEntity.class, params);
                    testUserService.saveBatch(list);
            } catch (Exception e) {

            } finally {
                try {
                    file.getInputStream().close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return R.ok();
    }

3.1 参数ImportParams

 ImportParams params = new ImportParams();
			params.setHeadRows(1);   //标明head在第一行   
            params.setTitleRows(2);  //标明title在第二行   
            params.setNeedSave(false);  //导入的Excel文件不保存在后台，如果为true则保存

3.2 从Excel文件流中解析数据

List<TestUserEntity> list = ExcelImportUtil.importExcel(file.getInputStream(), TestUserEntity.class, params);

4、VUE前端导出

    // 下载文件
      download (response) {
        if (!response) {
          return
        }
        let url = window.URL.createObjectURL(new Blob([response.data]))
        let link = document.createElement('a')
        link.style.display = 'none'
        link.href = url
        link.setAttribute('download', 'TestUser.xls')

        document.body.appendChild(link)
        link.click()
      },
 
 // 导出
      exportExcel () {
        this.$http({
          url: this.$http.adornUrl('/generator/testuser/export'),
          method: 'get',
          params: this.$http.adornParams({
            'page': this.pageIndex,
            'limit': this.pageSize,
            'key': this.dataForm.key,
            'column': this.dataForm.column
          }),
          responseType: 'blob'
        }).then(response => {
          this.download(response)
        }).catch(error => console.log(error))
      },

5、VUE前端导入

5.1 需要有文件上传组件

file-upload.vue,把该文件放入到当前页面路径下

<template>
  <el-dialog
    title="上传文件"
    :close-on-click-modal="false"
    @close="closeHandle"
    :visible.sync="visible">
    <el-upload
      drag
      :action="url"
      :before-upload="beforeUploadHandle"
      :on-success="successHandle"
      multiple
      :file-list="fileList"
      style="text-align: center;">
      <i class="el-icon-upload"></i>
      <div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>
      <div class="el-upload__tip" slot="tip">只支持xls格式！</div>
    </el-upload>
  </el-dialog>
</template>

<script>
  export default {
    data () {
      return {
        visible: false,
        url: '',
        num: 0,
        successNum: 0,
        fileList: []
      }
    },
    methods: {
      init (upFileUrl) {
        this.url = this.$http.adornUrl(`${upFileUrl}?token=${this.$cookie.get('token')}`)
        this.visible = true
      },
      // 上传之前
      beforeUploadHandle (file) {
        console.log(file.type)
        if (file.type !== 'application/vnd.ms-excel') {
          this.$message.error('只支持xls格式！')
          return false
        }
        this.num++
      },
      // 上传成功
      successHandle (response, file, fileList) {
        this.fileList = fileList
        this.successNum++
        if (response && response.code === 0) {
          if (this.num === this.successNum) {
            this.$confirm('操作成功, 是否继续操作?', '提示', {
              confirmButtonText: '确定',
              cancelButtonText: '取消',
              type: 'warning'
            }).catch(() => {
              this.visible = false
            })
          }
        } else {
          this.$message.error(response.msg)
        }
      },
      // 弹窗关闭时
      closeHandle () {
        this.fileList = []
        this.$emit('refreshDataList')
      }
    }
  }
</script>

5.2 引入上传组件

import Upload from './file-upload'

 components: {
      AddOrUpdate,
      Upload
    },