之前写过一篇使用EasyExcel导出excel到项目服务器中,然后在下载页面点击下载之后根据路径进行下载的。在这篇文章中我将用ResponseEntity直接返回excel到前端,记录一下该方法,同时也给大家一个参考。
1、controller层代码(根据前端的需求来)
@GetMapping("/export_excel")
public ResponseEntity exportExcel(@RequestBody List<PrdRequest> prdRequest) {
return prdService.exportExcel(prdRequest);
}
2、service层实现代码
public ResponseEntity exportExcel(List<PrdRequest> prdRequest) {
try {
//根据条件进行查询
List<PrdVo> prdVoList = prdMapper.selectPrd(prdRequest);
//数据写入到字节流
ByteArrayOutputStream bos = new ByteArrayOutputStream();
String sheetName = "产品信息查询导出表";
boolean flag = ExcelUtils.writeExcel(bos, PrdVo.class, prdVoList , sheetName);
//下载文件
String fileName = sheetName + ".xlsx";
//excel导出
if (flag) {
return ExcelUtils.downloadExcel(fileName, bos);
}
} catch (Exception e) {
log.error("产品信息导出异常", e);
}
return null;
}
3、导出结果实体类
@ApiModel(value = "PrdVo", description = "产品信息excel导出实体")
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class PrdVo implements Serializable {
@ExcelProperty(value = "产品名称", index = 0)
private String prdName;
@ExcelProperty(value = "产品编码", index = 1)
private String prdCode;
@ExcelProperty(value = "厂商名称", index = 2)
private String manufacturerName;
。。。其它的字段
}
4、ExcelUtils工具类
@Slf4j
public class ExcelUtils {
/**
* 表头进行自动扩展的导出
*
* @param os 文件输出流
* @param clazz Excel实体映射类
* @param data 导出数据
* @param sheetName sheet名称
* @return
*/
public static Boolean writeExcel(OutputStream os, Class clazz, List<?> data, String sheetName) {
try (BufferedOutputStream bos = new BufferedOutputStream(os)) {
EasyExcel.write(bos, clazz)
.sheet(sheetName)
//使用该方法对表头的宽度进行扩展
.registerWriteHandler(new ColumnWidthStyleStrategy())
.doWrite(data);
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
/**
* ResponseEntity下载文件
*
* @param fileName
* @param byteOutPutStream
*/
public static ResponseEntity<byte[]> downloadExcel(String fileName, ByteArrayOutputStream byteOutPutStream) {
//下载文件
try {
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
// 文件名称
headers.setContentDispositionFormData("attachment",
new String(fileName.getBytes("GBK"), "ISO8859-1"));
ResponseEntity<byte[]> responseEntity = new ResponseEntity<byte[]>(byteOutPutStream.toByteArray(), headers, HttpStatus.OK);
return responseEntity;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
5、ColumnWidthStyleStrategy类,不能使用自带的,因为使用自带的只有第一次导出的时候会格式化
public class ColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 255;
private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<Integer, Map<Integer, Integer>>(8);
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head,
Integer relativeRowIndex, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (!needSetWidth) {
return;
}
Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap<Integer, Integer>(16);
CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = dataLength(cellDataList, cell, isHead);
if (columnWidth < 0) {
return;
}
if (columnWidth > MAX_COLUMN_WIDTH) {
columnWidth = MAX_COLUMN_WIDTH;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes(CommonConstant.UTF_8).length;
}
CellData cellData = cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
}
switch (type) {
case STRING:
return cellData.getStringValue().getBytes(CommonConstant.UTF_8).length;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes(CommonConstant.UTF_8).length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes(CommonConstant.UTF_8).length;
default:
return -1;
}
}
}
mapper文件中进行查询的语句没有放在里面,感觉都很简单,没有必要,需要注意的是第四步的时候使用HttpStatus.OK,因为我之前使用了HttpStatus.CREATED,导出的报表会偶发性的出一点问题。
希望这篇文章能够帮助到需要的人,欢迎大家转载收藏。