基于springboot、vue、element、apache poi导出勾选中的信息excel表格。

记录自己的前后端分离项目中excel导出。

因为是前后端分离的项目，所以这里要注意解决跨域的问题！！！解决跨域问题参考其他博主，自己写的练手项目大佬轻喷。

1、数据库代码

-- 创建表
CREATE TABLE `tb_pcinfo` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(15) NOT NULL COMMENT '姓名',
  `age` tinyint(3) unsigned NOT NULL COMMENT '年龄',
  `gender` char(1) NOT NULL DEFAULT '1' COMMENT '性别 1 男 2 女',
  `officer_id` int(10) unsigned NOT NULL COMMENT '警员编号',
  `level` varchar(10) NOT NULL DEFAULT '警员' COMMENT '警衔',
  `region_name` varchar(20) NOT NULL COMMENT '所属警署',
  `dept_name` varchar(10) NOT NULL COMMENT '部门名称',
  `dept_id` int(10) unsigned NOT NULL COMMENT '部门编号',
  `update_time` datetime NOT NULL COMMENT '信息更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `officer_id` (`officer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8 COMMENT='警员信息表';

-- 导入数据
INSERT INTO tb_pcinfo (`name`,age,gender,officer_id,`level`,region_name,dept_name,dept_id,update_time) VALUES ('张薇',27,2,433871,'8','总区警署','公共关系科',6,NOW()),
('王国华',25,1,15941,'11','新界南警署','PTU',7,NOW()),
('李华',32,1,847331,'10','新界南警署','商业罪案调查科',8,NOW()),
('刘颖',27,2,776533,'11','铜锣湾警署','警校',3,NOW()),
('尹华',26,1,334121,'11','赤柱分区警署 ','毒品调查科',4,NOW()),
('林果香',45,1,11453,'1','总区警署','毒品调查科',4,NOW()),
('朴天林',39,1,15741,'6','西九龙交通部','处理及意外调查科',2,NOW()),
('赵天德',37,1,333021,'5','青衣警署','刑事情报科',5,NOW()),
('李丽',37,2,123321,'7','田心警署','公共关系科',6,NOW()),
('王涵',48,1,337021,'6','青衣警署','警校',3,NOW()),
('彭宏',28,1,431021,'8','青衣警署','PTU',7,NOW()),
('石米高',36,1,973371,'6','尖沙咀警署','飞虎队',1,NOW());

2、前端代码

<template>
    <div>
        <el-button type="info" icon="el-icon-upload2" @click="exportExcel()">
            导出
        </el-button>
    </div>
    
    <!-- 警员信息表单 -->
          <el-table
            id="outTable"
            :data="tableData"
            border
            style="width: auto"
            text-align="center"
            @selection-change="handleSelectionChange"
            v-model="multipleSelection"
            :row-key="getRowKeys"
          >
            <el-table-column
              type="selection"
              width="55"
              :reserve-selection="true"
            >
            </el-table-column>
            <el-table-column fixed prop="name" label="姓名" width="80">
            </el-table-column>
            <el-table-column prop="age" label="年龄" width="80">
            </el-table-column>
            <el-table-column prop="gender" label="性别" width="80">
              <template slot-scope="scope">
                <span style="margin-right: 10px" v-if="scope.row.gender == 1">
                  男</span
                >
                <span style="margin-right: 10px" v-if="scope.row.gender == 2"
                  >女</span
                >
              </template> </el-table-column
            >编辑<el-table-column prop="officerId" label="警员编号" width="80">
            </el-table-column>
            <el-table-column prop="level" label="警员职级" width="80">
            </el-table-column>
            <el-table-column prop="regionName" label="所属警署" width="150">
            </el-table-column>
            <el-table-column prop="deptName" label="所属部门" width="150">
            </el-table-column>
            <el-table-column prop="deptId" label="部门编号" width="80">
            </el-table-column>
            <el-table-column prop="updateTime" label="更新日期" width="150">
            </el-table-column>
          </el-table>
</template>


<script>
import {
  exportPcInfo,
} from "@/api/pcinof.js";//这里写自己的js路径，我的是这个根据自己的来


export default {
    
    data() {
    return {
        //表中数据
      tableData: [],

       //多选框选中数据
      multipleSelection: [],
        }
    },

  methods: {
    //下载excel文件
    fileDownload(blobData) {
      const blob = new Blob([blobData], {
        type: "application/vnd.openxmlformats-officeedocument.sheet;charset=utf-8",
      }); //处理二进制文件，指定字符集
      const link = document.createElement("a");
      link.href = window.URL.createObjectURL(blob); //将下载连接赋值到a标签href属性上
      link.download = "pcInfo.xlsx"; //所要下载的文件名，与后端响应的名字相对应
      link.click(); //执行下载
    },

    //导入导出方法
    exportExcel() {
      exportPcInfo(this.multipleSelection)
        .then((result) => {
          if (result.data.code != 0) {
            this.$alert("导出成功");
            this.fileDownload(result.data);
          }
        })
        .catch((err) => {
          err.$alert("fialed");
        });
    },
    }
};
</script>

 这个是js中的代码

//导出警员信息
export function exportPcInfo(multipleSelection)
{
    return request({
        url: 'pcInfo/exportExcel',//后端路径
        method: 'post',
        data: multipleSelection,
        responseType: 'blob'//这个一定要写不要要乱码
    })
}

3、后端代码

3.1、controller代码

@RestController
@RequestMapping("/pcInfo")
public class PcController
{
    @Autowired
    private PcService pcService;

    //导出警员信息excel
    @PostMapping("exportExcel")
    public void exportPcInfo(HttpServletResponse response, @RequestBody List<PcInfo> selectData) throws IOException {
        pcService.exportPcInfoData(response, selectData);
    }

}

3.2、service层代码

@Service
public interface PcService {
    //导出pcInfo的excel
    //void exportPcInfoData(HttpServletResponse response);
    public void exportPcInfoData(HttpServletResponse response, List<PcInfo> selectData);
}

@Service
@Slf4j
public class PcServiceImpl implements PcService
{
    //导出PcInfo的excel
    @Override
    //public void exportPcInfoData(HttpServletResponse response)
    public void exportPcInfoData(HttpServletResponse response, List<PcInfo> selectData) {
        try {
            // 创建Excel工作簿
            Workbook excel = new XSSFWorkbook();
            Sheet sheet = excel.createSheet("Sheet1");

            // 创建表头
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("姓名");
            headerRow.createCell(1).setCellValue("年龄");
            headerRow.createCell(2).setCellValue("性别");
            headerRow.createCell(3).setCellValue("警员编号");
            headerRow.createCell(4).setCellValue("警员职级");
            headerRow.createCell(5).setCellValue("所属警署");
            headerRow.createCell(6).setCellValue("所属部门");
            headerRow.createCell(7).setCellValue("部门编号");

            // 获取单元格，这里也可以用foreach
            for (int i = 0; i < selectData.size(); i++) {
                Row row = sheet.createRow(i + 1);

                row.createCell(0).setCellValue(selectData.get(i).getName());
                row.createCell(1).setCellValue(selectData.get(i).getAge());
                row.createCell(2).setCellValue(selectData.get(i).getGender());
                row.createCell(3).setCellValue(selectData.get(i).getOfficerId());
                row.createCell(4).setCellValue(selectData.get(i).getLevel());
                row.createCell(5).setCellValue(selectData.get(i).getRegionName());
                row.createCell(6).setCellValue(selectData.get(i).getDeptName());
                row.createCell(7).setCellValue(selectData.get(i).getDeptId());
            }

            // 设置响应头
            response.setContentType("application/vnd.openxmlformats-officeedocument.sheet;charset=utf-8");
            String fileName = URLEncoder.encode("pcInfo", "UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");//注意这里的下载名字 pcInfo.xlsx一定要和前端下载的一样，可以自己修改自己的

            //excel.write(response.getOutputStream());这里可以一步写完
            ServletOutputStream out = response.getOutputStream();
            excel.write(out);
            //关闭资源
            out.flush();
            out.close();
            excel.close();

            System.out.println("导出成功");
        } catch (IOException e) {
            System.out.println("IOException：" + e.getMessage());
        }
    }
    
}

4、实现效果