vue项目中实现下载后端返回的excel数据表格
程序员文章站
2024-03-20 16:31:04
...
后端java代码:
**
* 导出失败数据excel
*/
@Override
public void exportFailExcel(BlackListUpload blackListUpload) {
log.info("下载失败导入失败的数据{}", blackListUpload);
String blackListUploadId = blackListUpload.getId();
HashMap<String, Object> queryMap = new HashMap<>();
queryMap.put(BlackListExcel.STATUS.getCode(), BlackListUploadStatus.IMPORT_FAIL);
queryMap.put("blackListUploadId", blackListUploadId);
//查询导入失败数据
ArrayList<HashMap<String, Object>> list = (ArrayList<HashMap<String, Object>>) mongoDataDao.findList(queryMap, MongoCollectionName.EXCEL_IMPORT, HashMap.class);
ArrayList<HashMap<String, Object>> excelList = new ArrayList<>();
//获取导入到excel中数据
for (HashMap<String, Object> map : list) {
HashMap<String, Object> data = (HashMap<String, Object>) map.get("data");
excelList.add(data);
}
try {
//生成excel
ExcelUtil.writeExcelByMaps(response, excelList);
} catch (Exception e) {
log.error("生成excel失败:{}", blackListUpload);
e.printStackTrace();
}
}
/*
* @Description: 导入导出excel工具类
*/
public class ExcelUtil {
/**
* excel文件后缀
*/
public static final String EXCEL_SUFFIX = ".xlsx";
public static <T> void writeExcel(HttpServletResponse response, List<T> dataList, Class<T> cls, String excelName, String sheetName) throws IOException {
Field[] fields = cls.getDeclaredFields();
List<Field> fieldList = Arrays.stream(fields)
.filter(field -> {
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
if (annotation != null && annotation.col() > 0) {
field.setAccessible(true);
return true;
}
return false;
}).sorted(Comparator.comparing(field -> {
int col = 0;
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
if (annotation != null) {
col = annotation.col();
}
return col;
})).collect(Collectors.toList());
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet(sheetName);
AtomicInteger ai = new AtomicInteger();
{
Row row = sheet.createRow(ai.getAndIncrement());
AtomicInteger aj = new AtomicInteger();
//写入头部
fieldList.forEach(field -> {
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
String columnName = "";
if (annotation != null) {
columnName = annotation.value();
}
Cell cell = row.createCell(aj.getAndIncrement());
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
Font font = wb.createFont();
font.setBold(true);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
cell.setCellValue(columnName);
});
}
CellStyle cellStyleDate = wb.createCellStyle();
CreationHelper creationHelper = wb.getCreationHelper();
cellStyleDate.setDataFormat(
creationHelper.createDataFormat().getFormat("yyyy-MM-dd hh:mm:ss")
);
if (CollectionUtils.isNotEmpty(dataList)) {
dataList.forEach(t -> {
Row row1 = sheet.createRow(ai.getAndIncrement());
AtomicInteger aj = new AtomicInteger();
fieldList.forEach(field -> {
Class<?> type = field.getType();
Object value = "";
try {
value = field.get(t);
} catch (Exception e) {
e.printStackTrace();
}
Cell cell = row1.createCell(aj.getAndIncrement());
if (value != null) {
if (type == Date.class) {
cell.setCellStyle(cellStyleDate);
cell.setCellValue(value.toString());
} else {
cell.setCellValue(value.toString());
}
cell.setCellValue(value.toString());
}
});
});
}
//冻结窗格
wb.getSheet(sheetName).createFreezePane(0, 1, 0, 1);
//浏览器下载excel
buildExcelDocument(wb, response);
//生成excel文件
buildExcelFile(excelName + EXCEL_SUFFIX, wb);
}
/**
* 根据map列表生成excel
*
* @param maps
*/
public static void writeExcelByMaps(HttpServletResponse response, List<HashMap<String, Object>> maps) throws IOException {
Workbook wb = new XSSFWorkbook();
String sheetName = "导入失败";
Sheet sheet = wb.createSheet(sheetName);
AtomicInteger rowNum = new AtomicInteger();
if (maps.size() > 0) {
HashMap<String, Object> stringObjectHashMap = maps.get(0);
HashMap<Integer, Object> columnMap = new HashMap<>();
AtomicInteger atomicInteger = new AtomicInteger();
for (Map.Entry<String, Object> entry : stringObjectHashMap.entrySet()) {
int i = atomicInteger.getAndIncrement();
columnMap.put(i, entry.getKey());
}
//写第一行
Row row = sheet.createRow(rowNum.getAndIncrement());
columnMap.entrySet().forEach(integerObjectEntry -> {
Integer columnNum = integerObjectEntry.getKey();
Cell cell = row.createCell(columnNum);
String value = String.valueOf(integerObjectEntry.getValue());
cell.setCellValue(value);
});
//写数据行
maps.forEach(map -> {
Row sheetRow = sheet.createRow(rowNum.getAndIncrement());
columnMap.entrySet().forEach(integerObjectEntry -> {
Integer columnNum = integerObjectEntry.getKey();
String value = String.valueOf(integerObjectEntry.getValue());
Cell cell = sheetRow.createCell(columnNum);
cell.setCellValue(String.valueOf(map.get(value)));
});
});
}
//冻结窗格
wb.getSheet(sheetName).createFreezePane(0, 1, 0, 1);
//浏览器下载excel
buildExcelDocument(wb, response);
//生成excel文件
buildExcelFile("test" + EXCEL_SUFFIX, wb);
}
/**
* 浏览器下载excel
*
* @param wb
* @param response
*/
private static void buildExcelDocument(Workbook wb, HttpServletResponse response) throws IOException {
response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
//response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
response.flushBuffer();
wb.write(response.getOutputStream());
}
/**
* 生成excel文件
*
* @param path 生成excel路径
* @param wb
*/
private static void buildExcelFile(String path, Workbook wb) {
File file = new File(path);
if (file.exists()) {
file.delete();
}
try {
wb.write(new FileOutputStream(file));
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 解析excel
*
* @param file
* @return
*/
public static ArrayList<HashMap<String, Object>> readExcel(File file) {
Workbook workbook = null;
try {
workbook = new XSSFWorkbook(file);
} catch (IOException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
//存放数据
ArrayList<HashMap<String, Object>> dataList = new ArrayList<>();
//获取sheet页数量
int numberOfSheets = workbook.getNumberOfSheets();
for (int z = 0; z < numberOfSheets; z++) {
//取第一个sheet
Sheet sheet = workbook.getSheetAt(z);
int num = sheet.getLastRowNum() - sheet.getFirstRowNum();
//将列名与列标对应
Row firstRow = sheet.getRow(0);
short lastCellNum = firstRow.getLastCellNum();
HashMap<Integer, String> columnNameMap = new HashMap<>();
for (int i = 0; i < lastCellNum; i++) {
String cellValue = firstRow.getCell(i).getStringCellValue();
columnNameMap.put(i, cellValue);
}
//读数据,放入list
for (int i = 0; i < num; i++) {
Row row = sheet.getRow(i + 1);
HashMap<String, Object> map = new HashMap<>();
for (int j = 0; j < lastCellNum; j++) {
map.put(columnNameMap.get(j), String.valueOf(row.getCell(j)));
}
dataList.add(map);
}
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return dataList;
}
}
前端vue代码:
<!-- 绑定事件 -->
<el-form-item class="mb-48">
<el-button type="primary"
@click="downLoadImportResult">下载导入结果</el-button>
<el-button @click="handleClose">返 回</el-button>
</el-form-item>
methods:{
downLoadImportResult() {
let params = { // 请求参数 要下载Excel的id
'id':this.excelId
};
downloadFailExcel(params).then(res => { // 调用接口
console.log(res); // 此处res为bolb类文件对象 Blob(4412) {size: 4412, type: "application/octet-stream"}
var blob = new Blob([res], {type: 'application/vnd.openxmlformats- officedocument.spreadsheetml.sheet;charset=utf-8'}); //type这里表示xlsx类型
var downloadElement = document.createElement('a');
var href = window.URL.createObjectURL(blob); //创建下载的链接
downloadElement.href = href;
downloadElement.download = 'result.xlsx'; //下载后文件名
document.body.appendChild(downloadElement);
downloadElement.click(); //点击下载
document.body.removeChild(downloadElement); //下载完成移除元素
window.URL.revokeObjectURL(href); //释放掉blob对象
}).catch(err => {
this.$message({
message:'下载失败!',
type:'error',
showClose:true
})
})
}
}
export function downloadFailExcel(params) {
return request({
url: '/blackListUpload/downloadFailExcel',
method: 'post',
data:params,
responseType: 'blob', // 设置响应数据类型为 blob
})
}
office 所有后缀对应的 content-type
https://blog.csdn.net/xiaoranzhizhu/article/details/70473734
下一篇: WEB开发之HTTP简述