Hutool工具实现数据基本的导入和导出（excel）

Hutool官方文档：Hutool参考文档

 vue3+Element-Plus+springboot+mysql

一、所需依赖包

 <!--   hutool工具     -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.16</version>
        </dependency>
        <!--excel-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

二、导入

1.前端 

参考： Upload 上传 | Element Plus

<el-upload style="display: inline-block;" action="http://localhost:8081/user/import" :show-file-list="false" >
        <el-button type="primary" class="ml-5" >导入
          <el-icon>
            <Bottom />
          </el-icon>
        </el-button>
</el-upload>

2.后端

@ApiOperation("用户信息导入")
    @PostMapping("/import")
    public boolean importInfo(@RequestPart("file")MultipartFile userFile) throws IOException {
        final InputStream inputStream = userFile.getInputStream();
       
        final ExcelReader reader = ExcelUtil.getReader(inputStream);

        reader.addHeaderAlias("id", "id");

        reader.addHeaderAlias("用户名", "username");
        reader.addHeaderAlias("密码", "password");
        reader.addHeaderAlias("昵称", "nickname");
        reader.addHeaderAlias("邮箱", "email");
        reader.addHeaderAlias("电话", "phone");
        reader.addHeaderAlias("地址", "address");
        reader.addHeaderAlias("创建时间", "createTime");
        final List<User> users = reader.readAll(User.class);
//这里我数据库id设的自增，所以这边设为null
        users.forEach(s->s.setId(null));

        System.out.println(users);
        return userService.saveBatch(users);
    }

三、导出

1.前端

 <el-button type="primary" class="ml-5" @click="userExport()">导出
        <el-icon>
          <Top />
        </el-icon>
 </el-button>



<script setup>
import { Menu as Top } from '@element-plus/icons-vue'
import { exportUser} from '../api/user'
//导出
const userExport = () => {
  exportUser()
}

</script>


//用户信息导出js
export const exportUser = async () => {
    try {
        const response = await request({
            url: 'user/export',
            method: 'get',
            responseType: 'blob',
        });
        const url = window.URL.createObjectURL(new Blob([response.data]));
        const link = document.createElement('a');
        link.href = url;
        link.setAttribute('download', '用户信息.xlsx');
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
    } catch (error) {
        console.error('导出用户信息失败', error);
    }
};

2.后端

@ApiOperation("用户信息导出")
    @GetMapping("/export")
    public void export(HttpServletResponse httpServletResponse) throws IOException {
        final List<User> userList = userMapper.selectList(new QueryWrapper<>());

        ExcelWriter excelWriter = ExcelUtil.getWriter(true);

        excelWriter.addHeaderAlias("id", "id");
//将数据模型中的username字段映射为Excel表格中的用户名列。
        excelWriter.addHeaderAlias("username", "用户名");
        excelWriter.addHeaderAlias("password", "密码");
        excelWriter.addHeaderAlias("nickname", "昵称");
        excelWriter.addHeaderAlias("email", "邮箱");
        excelWriter.addHeaderAlias("phone", "电话");
        excelWriter.addHeaderAlias("address", "地址");
        excelWriter.addHeaderAlias("createTime", "创建时间");


        /*
        写出数据，本方法只是将数据写入Workbook中的Sheet，并不写出到文件
        写出的起始行为当前行号，可使用getCurrentRow()方法调用，根据写出的的行数，当前行号自动增加
        样式为默认样式，可使用getCellStyle()方法调用后自定义默认样式
        data - 数据
        isWriteKeyAsHead - 是否强制写出标题行（Map或Bean）
         */
        //只保留别名的数据
        excelWriter.setOnlyAlias(true);
        excelWriter.write(userList, true);

        httpServletResponse.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset:utf-8");
        String filename = URLEncoder.encode("用户信息.xlsx", "UTF-8");
        httpServletResponse.setHeader("Content-Disposition", "attachment; filename=" + filename);

        final ServletOutputStream outputStream = httpServletResponse.getOutputStream();
        /*
        将Excel Workbook刷出到输出流
            Parameters:
            out - 输出流
            isCloseOut - 是否关闭输出流
         */
        excelWriter.flush(outputStream,true);
        outputStream.close();
        // 刷新缓冲区，确保数据发送到客户端
        httpServletResponse.flushBuffer();
        excelWriter.close();

    }