62,634
社区成员




layer.confirm('确定导出到Excel?', {
btn : [ '确定', '取消' ]
}, function() {
$.ajax({
type : "POST",
url : "/require/export2Excel",
responseType : Blob,
data : {
requireName : $('#requireName').val(),
requireId : $('#requireId').val(),
functionName : $('#functionName').val(),
functionModule : $('#functionModule').val(),
functionType : $('#functionType').val(),
assignState : $('#assignState').val(),
submitState : $('#submitState').val(),
},success: (wb) => {//wb为我后端的workbook,我写成respong时excel文件直接没数据,写成这个的时候有数据,这儿可能有问题
var blob = new Blob([wb]); //创建一个blob对象
var a = document.createElement('a'); //创建一个<a></a>标签
a.href = URL.createObjectURL(blob); // response is a blob
a.download = "功能需求表.xlsx";
a.style.display = 'none';
document.body.appendChild(a);
a.click();
a.remove();
layer.msg("导出成功!");
}
})
});
@PostMapping("export2Excel")
@ResponseBody
public void export2Excel(@RequestParam Map<String, Object> params, HttpServletResponse response){
Query query = new Query(params,"");
List<FunctionRequireDO> list = requireService.list(query);
ExcelUtils.writeExcel(response,list,FunctionRequireDO.class);
}
public static <T> void writeExcel(HttpServletResponse response, List<T> dataList, Class<T> cls){
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("Sheet1");
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(CellStyle.SOLID_FOREGROUND);
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
cell.setCellValue(columnName);
});
}
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.setCellValue(value.toString());
} else {
cell.setCellValue(value.toString());
}
cell.setCellValue(value.toString());
}
});
});
}
//冻结窗格
wb.getSheet("Sheet1").createFreezePane(0, 1, 0, 1);
//浏览器下载excel
buildExcelDocument("功能需求表.xlsx",wb,response);
//生成excel文件
// buildExcelFile("C:/Users/Administrator/Desktop/"+fileName+".xlsx",wb);
}
/**
* 浏览器下载excel
* @param fileName
* @param wb
* @param response
*/
private static void buildExcelDocument(String fileName, Workbook wb, HttpServletResponse response){
try {
//application/octet-stream
response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
response.setHeader("content-type", "application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(fileName, "utf-8"));
response.flushBuffer();
wb.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
}
@GetMapping("/export")
@ApiOperation(value = "导出",httpMethod = "GET")
public void test3( FeedbackListParam param, HttpServletResponse response) {
ExcelWriter writer = ExcelUtil.getWriter();
try {
/**
*/
String head = "导出数据";
List<List<Object>> rows = new LinkedList<>();
//填充rows
List<String> rowHead = CollUtil.newArrayList("id", "用户id", "用户昵称");
writer.writeHeadRow(rowHead);
writer.write(rows);
//设置宽度自适应
writer.setColumnWidth(-1, 22);
//response为HttpServletResponse对象
response.setContentType("application/vnd.ms-excel;charset=utf-8");
//test.xls是弹出下载对话框的文件名,不能为中文,中文请自行编码
response.setHeader("Content-Disposition", "attachment;filename=" + new String((head).getBytes("UTF-8"), "ISO-8859-1") + ".xls");
ServletOutputStream out = response.getOutputStream();
//out为OutputStream,需要写出到的目标流
writer.flush(out);
} catch (Exception e) {
log.error("导出异常",e);
e.printStackTrace();
} finally {
// 关闭writer,释放内存
writer.close();
}
}