通过easyexcel导出数据到excel表格

这篇文章简单介绍一下怎么通过easyexcel做数据的导出，使用之前easyui构建的歌曲列表crud应用，添加一个导出按钮，点击的时候直接连接后端接口地址，在后端的接口完成数据的导出功能。

前端页面完整代码

let editingId;
let requestUrl;
let base = "http://localhost:8083";
let pageList = [20, 50, 100, 500, 1000];// 定义一个json对象保存歌曲数据
let data = {};function addHandler() {requestUrl = "/song/insert";$.post(base + requestUrl, {name: "*****",singer: "*****",note: "*****"}, function () {$("#song_list").datagrid("reload");}, "json");
}function editHandler() {let datagrid = $("#song_list");let row = datagrid.datagrid("getSelected");if (editingId != null && editingId != "") {datagrid.datagrid("selectRow", editingId);} else {if (row) {// 获取行索引，这个索引从0开始let rowIndex = datagrid.datagrid("getRowIndex", row);editingId = rowIndex;requestUrl = "/song/updateById";datagrid.datagrid("beginEdit", rowIndex);}}
}function saveHandler() {if (editingId) {// 只有结束编辑才能获取到最新的值$("#song_list").datagrid("endEdit", editingId);$.post(base + requestUrl, data, function (res) {$.messager.show({title: '系统消息',timeout: 5000,showType: 'slide',msg: res.message,});editingId = "";}, "json");}
}function cancelHandler() {// editingId != null条件防止刷新页面带来的问题if (editingId != null && editingId !== "") {$("#song_list").datagrid("cancelEdit", editingId);editingId = "";}
}function exportHandler() {location.href = base + "/song/export";
}function deleteHandler() {let rowData = $("#song_list").datagrid("getSelected");if (rowData) {$.messager.confirm("提示", "删除后数据无法恢复，是否确认删除？", function(bool) {if (bool) {$.get(base + "/song/deleteById/" + rowData.id, {}, function(res) {$.messager.show({title: '系统消息',timeout: 5000,showType: 'slide',msg: res.message,});$("#song_list").datagrid("reload");}, "json");}});} else {$.messager.alert("请选择要删除的数据！", "warning");}
}$(document).ready(function() {let datagrid = $("#song_list").datagrid({url: base + "/song/selectByPage",title: "歌曲列表",height: 810,striped: true,fitColumns: true,singleSelect: true,pagination: true,remoteFilter: true,clientPaging: false,pageSize: pageList[0],pageList: pageList,loadFilter: function(res) {if (res.code == 200) {return res.data;} else {return null;}},onAfterEdit: function (rowIndex, rowData, changes) { // 结束行内编辑事件data = {id: rowData.id,name: changes.name ? changes.name : rowData.name,note: changes.note ? changes.note : rowData.note,singer: changes.singer ? changes.singer : rowData.singer};},toolbar: [{iconCls: 'icon-add',text: '添加',handler: function() {addHandler();}}, '-', {iconCls: 'icon-edit',text: '修改',handler: function() {editHandler();},}, "-", {iconCls: "icon-save",text: "保存",handler: function() {saveHandler();}}, "-", {iconCls: "icon-cancel",text: "取消",handler: function() {cancelHandler();}}, '-', {iconCls: 'icon-ok',text: '导出',handler: function() {exportHandler();}}, '-', {iconCls: 'icon-delete',text: '删除',handler: function() {deleteHandler();},}],columns: [[{field: 'id', title: 'id', width: 200},{field: 'name', title: 'name', width: 200, editor: "textbox"},{field: 'singer', title: 'singer', width: 200, editor: "textbox"},{field: 'note', title: 'note', width: 200, editor: "textbox"},{field: 'lastUpdateTime', title: 'lastUpdateTime', width: 200},]]});datagrid.datagrid('enableFilter', [{field: 'name',type: 'textbox',op: ['equal', 'contains']}, {field: 'singer',type: 'textbox',op: ['equal', 'contains'],}, {field: 'note',type: 'textbox',op: ['equal', 'contains']}]);});

添加依赖

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.3.2</version>
</dependency>

修改实体类，添加列注解

package com.example.springboot.entity;import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;import java.io.Serializable;
import java.time.LocalDateTime;/*** 歌曲* @author heyunlin* @version 1.0*/
@Data
@TableName("song")
public class Song implements Serializable {private static final long serialVersionUID = 18L;@ExcelIgnore@TableId(type = IdType.INPUT)private String id;/*** 歌曲名*/@ExcelProperty("歌曲名")private String name;/*** 歌手*/@ExcelProperty("歌手")private String singer;/*** 描述信息*/@ExcelProperty("描述信息")private String note;/*** 最后一次修改时间*/@TableField("last_update_time")@ExcelProperty("最后一次修改时间")@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")private LocalDateTime lastUpdateTime;
}

参考官网的案例代码，完成后端controller接口具体代码实现

package com.example.springboot.service.impl;import com.alibaba.excel.EasyExcel;
import com.example.springboot.entity.Song;
import com.example.springboot.mapper.SongMapper;
import com.example.springboot.restful.JsonResult;
import com.example.springboot.service.SongService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;/*** @author heyunlin* @version 1.0*/
@Service
public class SongServiceImpl implements SongService {private final SongMapper songMapper;@Autowiredpublic SongServiceImpl(SongMapper songMapper) {this.songMapper = songMapper;}// 其他代码...@Overridepublic void export(HttpServletResponse response) {String fileName = "song.xlsx";response.setCharacterEncoding("utf-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName);response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");try {List<Song> songs = songMapper.selectList(null);EasyExcel.write(response.getOutputStream(), Song.class).sheet("歌曲列表").doWrite(songs);} catch (Exception e) {e.printStackTrace();response.reset();response.setContentType("application/json;charset=utf-8");JsonResult<Void> jsonResult = JsonResult.success("数据导出异常");try {response.getWriter().write(jsonResult.toString());} catch (IOException ioException) {ioException.printStackTrace();}}}}

代码已经同步到后端项目的springbooot-crud1.0分支，可按需获取~

springboot+mybatis实现简单的增删查改案例项目https://gitee.com/he-yunlin/springboot-crud.git