本次通过Springboot与vue实现了下载Excel功能。 在生成Excel文件时使用了apache的poi,
使用前须在pom中引入poi的jar包依赖:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
前台代码:
downloadCE(downloadFlg) {
this.form.uid = this.cboxList;
axios.defaults.responseType = "blob";
let downloadUri = '';
// 下载csv格式文件
if (downloadFlg == '0') {
downloadUri = '/api/downloadUser';
// 下载Excel文件
} else {
downloadUri = '/api/downloadUserExcel';
}
axios.post(
downloadUri,
this.form
).then(res => {
let contentDisposition = res.headers['content-disposition'];
// 将URL参数数值转成汉字
let fileName = decodeURI(contentDisposition.substring(contentDisposition.indexOf('=') + 1));
// 处理IE以及IE内核的浏览器兼容问题
if (window.navigator && window.navigator.msSaveOrOpenBlob) {
navigator.msSaveBlob(res.data, fileName);
} else {
// 处理google firefox浏览器兼容问题
let url = window.URL.createObjectURL(new Blob([res.data]));
let a = document.createElement('a');
a.style.display = 'none';
a.href = url;
a.setAttribute('download', fileName);
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
window.URL.revokeObjectURL(url);
}
后台代码:
@PostMapping("/downloadUserExcel")
public void downloadUserExcel(HttpServletResponse response
, @RequestBody UserForm userForm) throws IOException {
List<String> uidList = userForm.getUid();
List<UserModel> searchDownloadUser = new ArrayList<>();
if (!uidList.isEmpty()) {
searchDownloadUser = userService.searchDownloadUser(uidList);
createExcel(response, searchDownloadUser);
}
}
private void createExcel(HttpServletResponse response
, List<UserModel> searchDownloadUser) throws IOException {
// 文件名的生成
LocalDateTime ldf = LocalDateTime.now();
DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyyMMdd_HHmm");
String fileName = "用户信息" + dtf.format(ldf) + ".xlsx";
XSSFWorkbook work = new XSSFWorkbook();
XSSFSheet createSheet = work.createSheet("用户信息");
// 表头
String[] excelTitle = new String[] {"用户ID", "用户姓名", "用户年龄", "用户薪资", "所在部门"};
XSSFRow createRow = createSheet.createRow(0);
// 标题样式
CellStyle cellStyleTitle = work.createCellStyle();
cellStyleTitle.setAlignment(HorizontalAlignment.CENTER);
cellStyleTitle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyleTitle.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
// 使设置的颜色生效,一定要加上这句否则设置颜色不生效
cellStyleTitle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 内容样式
CellStyle cellStyleContext = work.createCellStyle();
cellStyleContext.setAlignment(HorizontalAlignment.CENTER);
cellStyleContext.setVerticalAlignment(VerticalAlignment.CENTER);
// 写入表头
for (int i = 0; i < excelTitle.length; i++) {
// 设置列宽
createSheet.setColumnWidth(i, 256 * 15);
XSSFCell createCell = createRow.createCell(i);
createCell.setCellStyle(cellStyleTitle);
XSSFRichTextString richText = new XSSFRichTextString(excelTitle[i]);
createCell.setCellValue(richText);
}
// 写入内容
for (int i = 0; i < searchDownloadUser.size(); i++) {
XSSFRow createRows = createSheet.createRow(i + 1);
String[] context = new String[]
{searchDownloadUser.get(i).getId()
, searchDownloadUser.get(i).getName()
, String.valueOf(searchDownloadUser.get(i).getAge())
, String.valueOf(searchDownloadUser.get(i).getSalary())
, searchDownloadUser.get(i).getDepartNm()};
for (int j = 0; j < context.length; j++) {
XSSFCell createCell = createRows.createCell(j);
createCell.setCellStyle(cellStyleContext);
XSSFRichTextString richText = new XSSFRichTextString(context[j]);
createCell.setCellValue(richText);
}
}
exprotExcel(response, work, fileName);
}
private void exprotExcel(HttpServletResponse response
, XSSFWorkbook work, String fileName) throws IOException {
// 设置响应头
response.setHeader("content-type", "application/octet-stream");
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
ServletOutputStream outputStream = response.getOutputStream();
try {
// 将文件输出
work.write(outputStream);
} finally {
outputStream.close();
}
}
最终的实现效果:
下载的效果: