easypoi导出文件
easypoi教程网址:http://easypoi.mydoc.io/,里面的例子很生动形象,形容的恰到好处。
一、创建导出文件的实体类
我用到的有两种方法,一种是直接新建一个class,class里面写的属性加上@Excel()注解,代表导出表中的一列,在注解中可以设置列名列宽等属性;另一种是动态创建导出文件的实体类,用ExcelExportEntity类去创建列,eg:new ExcelExportEntity(“组织机构”,“organization”, 24),具体使用方式,看手册即可
二、获取列的值
值可以用HashMap用key,value的形式存放,key就是在创建列时的取得key,比如上面的new ExcelExportEntity(“组织机构”, “organization”,24)中的key就是organization,在值的map中key就是organization,value就是放这个organization的值;
在创建列时,可以指定这一列的宽度上面中的例子指定的宽度是24,也可以在创建完workbook对象后,通过sheet设置自动宽度
Sheet sheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
for (int i = 0; i < 68; i++)
sheet.autoSizeColumn(i);
三、创建workbook对象
参数需要三个,①exportParams是导出的参数设置,②entity位置是实体类的class对象,或者动态创建的导出类,③是表格中的值
ExportParams exportParams = new ExportParams();
exportParams.setType(ExcelType.XSSF);
exportParams.setSheetName("重点指标");
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, entity, entityValue);
// 如果上面已经指定了列宽,这里在设置自动适应宽度,上面设置的列宽会失效(根据实际情况自己决定使用哪种方式)
Sheet sheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
for (int i = 0; i < 68; i++)
sheet.autoSizeColumn(i);
文件流写入响应体中
response.reset();
response.setCharacterEncoding("ISO8859-1");
response.setHeader("Connection", "close");
response.setHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
String fileName2 = "".concat(new String("文件名".getBytes("gb2312"), "ISO8859-1")).concat(".xlsx");
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName2, "UTF-8"));
workbook.write(response.getOutputStream());
response.getOutputStream().flush();
response.getOutputStream().close();
response.flushBuffer();
记得关流 记得关流
加粗文本 加粗文本
标记文本
删除文本
引用文本
H2O is是液体。
210 运算结果是 1024.
下面是全部的代码
String taskId = params.get("taskId").toString();
List<Map<String,Object>> propertyModels = (ArrayList<Map<String,Object>>) params.get("propertyModels");
List<Map<String,String>> organizationList = performanceTaskRepository.findOrganizationIdByTaskId(taskId);
Map<String, Map<String,Object>> organizationKPI = new HashMap<>();
for (Map<String, String> organization : organizationList) {
Map<String, Object> result = new HashMap<>();
result.put("organization",organization.get("organization_name"));
organizationKPI.put(organization.get("organization_id"), result);
}
List<String> propertyModelIdList = new ArrayList<>();
for (Map<String, Object> propertyModel : propertyModels) {
String nodeKey = propertyModel.get("nodeKey").toString();
String propertyUniqueName = propertyModel.get("propertyUniqueName").toString();
String itemRowKey = propertyModel.get("itemRowKey").toString();
String propertyModelId = propertyModel.get("id").toString();
propertyModelIdList.add(propertyModelId);
List<ItemProperty> properties = itemPropertyDao.findByTaskIdAndItemIdAndNodeKeyAndPropertyUniqueName(
taskId, nodeKey, propertyUniqueName, itemRowKey);
properties.forEach(property -> {
Map<String,Object> map = organizationKPI.get(property.getOrganizationId());
map.put(property.getPropertyUniqueName(), property.getResultValue());
organizationKPI.put(property.getOrganizationId(), map);
});
}
List<ExcelExportEntity> entity = new ArrayList<>();
entity.add(new ExcelExportEntity("组织机构","organization", 24));
List<ItemPropertiesModel> itemPropertiesModelList = itemPropertiesModelDao.findByPropertyModelIdIn(propertyModelIdList);
itemPropertiesModelList.forEach(itemPropertiesModel -> {
ExcelExportEntity excelEntity = new ExcelExportEntity(itemPropertiesModel.getPropertyViewName(), itemPropertiesModel.getPropertyUniqueName());
excelEntity.setWidth(26);
entity.add(excelEntity);
});
List<Map<String, Object>> entityValue = new ArrayList<>();
for (Map.Entry<String, Map<String, Object>> entry : organizationKPI.entrySet()) {
entityValue.add(entry.getValue());
}
ExportParams exportParams = new ExportParams();
exportParams.setType(ExcelType.XSSF);
exportParams.setSheetName("重点指标");
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, entity, entityValue);
try {
response.reset();
response.setCharacterEncoding("ISO8859-1");
response.setHeader("Connection", "close");
response.setHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
String fileName2 = "".concat(new String("重点指标".getBytes("gb2312"), "ISO8859-1")).concat(".xlsx");
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName2, "UTF-8"));
workbook.write(response.getOutputStream());
response.getOutputStream().flush();
response.getOutputStream().close();
response.flushBuffer();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
前端代码
vue前端获取转换下载方式
exportKPI({ taskId: this.$store.state.currentTaskId, propertyModels: propertyArr }).then(res => {
const blobUrl = window.URL.createObjectURL(res.data)
const elink = document.createElement('elink')
elink.style.display = 'none'
elink.download = name
elink.href = blobUrl
elink.click()
window.URL.revokeObjectURL(blobUrl)
document.body.removeChild(elink)
const h = this.$createElement
this.$message({
message: h('p', null, [
h('span', null, '重要指标文件导出 '),
h('i', { style: 'color: teal' }, '成功')
])
})
this.kpiLoading = false
this.dialogExportKPIVisible = false
this.KPIData = []
}).catch(err => {
console.error(err)
const h = this.$createElement
this.$message({
message: h('p', null, [
h('span', null, '重要指标文件导出 '),
h('i', { style: 'color: red' }, '失败')
])
})
}).finally(() => {
this.kpiLoading = false
})