环境准备:导入Maven依赖
<!-- hutool -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>${hutool.version}</version>
</dependency>
<!--******** 处理excel文件使用poi包 start********-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.ooxml.version}</version>
</dependency>
<!--******** 处理excel文件使用poi包 end********-->
Excel 自定义列名模版下载
Excel 自定义列名模版下载效果图
后台代码
public static BaseResponse downloadTemplate(HttpServletResponse response) {
try {
String outFileName = "客户端账号管理" + System.currentTimeMillis() + ".xlsx";
String ENCODING = "UTF-8";
ExcelWriter writer = ExcelUtil.getWriter(true);
// 创建一个Workbook
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建一个sheet页
HSSFSheet sheet = workbook.createSheet("sheet1");
// 创建第一行
HSSFRow row = sheet.createRow(0);
// 创建单元格
HSSFCell cell1 = row.createCell(0);
HSSFCell cell2 = row.createCell(1);
HSSFCell cell3 = row.createCell(2);
HSSFCell cell4 = row.createCell(3);
// 设置表头
cell1.setCellValue("姓名");
cell2.setCellValue("性别");
cell3.setCellValue("年龄");
cell4.setCellValue("爱好");
// 获取我们的输出流
final OutputStream output = response.getOutputStream();
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + outFileName + ";filename*=utf-8''"
+ URLEncoder.encode(outFileName, ENCODING));
workbook.write(output);
writer.flush(output, true);
writer.close();
// 这里可以自行关闭资源或者写一个关闭资源的工具类
IoUtil.close(output);
return null;
} catch (Exception e) {
e.printStackTrace();
return BaseResponse.returnFault("用户导出失败!");
}
}
Element-UI前端编写
Element-UI 官网地址:Element - The world's most popular Vue UI framework
//模版下载
downloadTemplate() {
this.$axios({
method: 'post',
url: '/cdanm/client/downloadTemplate/',
//data: params,
responseType: 'blob',//定义接口响应的格式,很重要
headers: {
'Content-Type': 'application/x-www-form-urlencoded;charset=UTF-8',
}
}).then(response => {
if (response.status == 200) {
var blob = response.data;
this.downloadFile('客户端账户管理Excel.xlsx', blob) //直接通过blob数据下载
} else {
self.$message({
type: 'error',
message: "导出失败!"
});
}
}).catch(err => {
self.$message({
type: 'error',
message: "导出失败!"
});
})
},
Excel 数据导出
效果图
后台代码编写
Controller层编写
/**
* 客户端账号管理数据 Excel下载
*/
@RequestMapping("/getUserTemplate/{userId}")
public BaseResponse getClientTemplateDown(@PathVariable("userId") String userId, HttpServletResponse response) {
try {
List<UsersDto> list = UserService.selectUsersByUserId(userId);
ExcelUtils.downLoadFile(list, response);
//注:return null,是为了避免多次response,关闭了socke继续请求问题
return null;
} catch (Exception e) {
e.printStackTrace();
return BaseResponse.returnFault(e.getMessage());
}
}
工具类 ExcelUtils 方法 downLoadFile编写
/**
* Excel模版下载
*
* @param response
*/
public static BaseResponse downLoadFile(List<ClientsDto> clientsList, HttpServletResponse response) {
String outFileName = "客户端账号管理" + System.currentTimeMillis() + ".xlsx";
String ENCODING = "UTF-8";
ExcelWriter writer = ExcelUtil.getWriter(true);
// 1、通过工具类创建writer并且进行别名
assembleWriter(writer);
//2、封装Excel模版下载的数据
List<ExcelInfo> excelInfoList = getExcelInfoList(clientsList);
//3、准备将对象写入我们的 List
writer.write(excelInfoList, true);
try {
// 获取我们的输出流
final OutputStream output = response.getOutputStream();
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + outFileName + ";filename*=utf-8''"
+ URLEncoder.encode(outFileName, ENCODING));
writer.flush(output, true);
writer.close();
// 这里可以自行关闭资源或者写一个关闭资源的工具类
IoUtil.close(output);
return null;
} catch (Exception e) {
e.printStackTrace();
return BaseResponse.returnFault("用户导出失败!");
}
}
private static void assembleWriter(ExcelWriter writer) {
writer.addHeaderAlias("name", "名称");
writer.addHeaderAlias("userName", "姓名");
writer.addHeaderAlias("address", "所在地");
writer.addHeaderAlias("loginName", "账号");
writer.addHeaderAlias("password", "密码");
writer.addHeaderAlias("clientStatus", "账号状态");
}
/**
* 封装模版下载的数据
*/
private static List<ExcelInfo> getExcelInfoList(List<UsersDto> usersList) {
List<ExcelInfo> list = new ArrayList<>();
ExcelInfo info = null;
for (UsersDto dto : usersList) {
info = new ExcelInfo();
info.setName(dto.getName());
info.setUserName(dto.getUserName());
info.setAddress(dto.getAddress());
info.setLoginName(dto.getLoginName());
info.setPassword(dto.getPassword());
info.setClientStatus(dto.getClientStatus());
list.add(info);
}
return list;
}
/**
* @author by LMGD
* @date 2021-10-20 16:14
* @description 客户端账号管理数据下载模版
*/
public class ExcelInfo {
private String name;
private String userName;
/**
* 所在地
*/
private String address;
/**
* 账号id
*/
private String loginName;
/**
* 密码
*/
private String password;
/**
* 账号状态
*/
private String clientStatus;
//省略get/set
}
Element-Ui 组件前端编写
exportClients() {
if (this.queryParams.userId== null || this.queryParams.userId== '') {
this.$message({
message: '选择【所属用户】才可导出!!',
type: 'warning'
})
return;
}
const self = this;
const params = JSON.parse(JSON.stringify(this.queryParams.userId));
this.$axios({
method: 'post',
url: '/client/getUserTemplate/' + this.queryParams.userId,
//data: params,
responseType: 'blob',//定义接口响应的格式,很重要
headers: {
'Content-Type': 'application/x-www-form-urlencoded;charset=UTF-8',
}
}).then(response => {
if (response.status == 200) {
var blob = response.data;
this.downloadFile('excel.xlsx', blob) //直接通过blob数据下载
} else {
self.$message({
type: 'error',
message: "导出失败!"
});
}
}).catch(err => {
self.$message({
type: 'error',
message: "导出失败!"
});
})
},
downloadFile(fileName, blob) {
let aLink = document.createElement('a');
var blob = blob; //new Blob([content]);
let evt = document.createEvent("HTMLEvents");
evt.initEvent("click", true, true);//initEvent 不加后两个参数在FF下会报错 事件类型,是否冒泡,是否阻止浏览器的默认行为
aLink.download = fileName;
aLink.href = URL.createObjectURL(blob);
aLink.dispatchEvent(new MouseEvent('click', { bubbles: true, cancelable: true, view: window }));//兼容火狐
},
Excel 模版批量导入
Controller层编写
/***
* 客户端账号管理信息模版上传
*/
@RequestMapping("/uploadClientTemplate")
public BaseResponse uploadClientTemplate(@RequestParam("file") MultipartFile file, @RequestParam("projectId") String projectId) {
try {
if (StringUtils.isEmpty(projectId)) {
return BaseResponse.returnFault("选择【所属项目】才可导入");
}
//往数据库插入数据
BaseResponse baseResponse = ExcelUtils.importFile(file);
List<Client> list = (List<Client>) baseResponse.getResponse().getData();
clientService.saveClientList(list, projectId);
return BaseResponse.returnSuccess();
} catch (Exception e) {
e.printStackTrace();
return BaseResponse.returnFault(e.getMessage());
}
}
工具类 ExcelUtils 方法 importFile 编写
/**
* Excel文件导入
*
* @param file
* @throws Exception
*/
public static BaseResponse importFile(MultipartFile file) {
try {
String fileName = file.getOriginalFilename();
String message = null;
// 上传文件为空
if (StringUtils.isEmpty(fileName)) {
message = "没有导入文件";
return BaseResponse.returnFault(message);
}
//上传文件大小为1000条数据
if (file.getSize() > 1024 * 1024 * 10) {
message = "上传失败: 文件大小不能超过10M!";
return BaseResponse.returnFault(message);
}
// 上传文件名格式不正确
if (fileName.lastIndexOf(".") != -1 && !".xlsx".equals(fileName.substring(fileName.lastIndexOf(".")))) {
message = "文件名格式不正确, 请使用后缀名为.xlsx的文件";
return BaseResponse.returnFault(message);
}
InputStream inputStream = file.getInputStream();
ExcelReader excelReader = ExcelUtil.getReader(inputStream, "sheet1");
assembleReader(excelReader);
//转成集合,用于存储到数据库
List<Client> populationList = excelReader.readAll(Client.class);
return BaseResponse.returnSuccess(populationList);
} catch (Exception e) {
return BaseResponse.returnFault(e.getMessage());
}
}
private static void assembleReader(ExcelReader reader) {
reader.addHeaderAlias("监测点代码", "pointId");
reader.addHeaderAlias("监测点名称", "pointName");
reader.addHeaderAlias("调查员名称", "clientName");
reader.addHeaderAlias("所在地", "address");
reader.addHeaderAlias("账户名称", "loginName");
reader.addHeaderAlias("账户状态", "clientStatus");
}
@TableName("T_CLIENT")
public class Client {
/**
* 客户端id
*/
private String clientId;
/**
* 项目id
*/
private String projectId;
private String pointCode;
private String clientName;
private String address;
private String loginName;
private String password;
private String clientStatus;
//省略get/set 方法
}
前端编写
importClients(file) {
let fd = new FormData()
fd.append('file', file)
fd.append('projectId', this.queryParams.projectId)
uploadClientTemplate(fd).then(res => {
if (res.response.messageHeader.code == 0) {
this.searchClientsList()
this.$message({
type: 'success',
message: res.response.messageHeader.desc
});
}
})
return true;
},
<script>
import {uploadClientTemplate } from '@/api/api'
</script>
api.js 里面写的接口
// 客户端账户管理-导入
export function uploadClientTemplate(data) {
return request({
url: '/client/uploadClientTemplate',
method: 'post',
data
})
}