一、添加EasyExcel依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version>
</dependency>二、后端代码示例
controller：
@GetMapping("/download")
public void download(HttpServletResponse response) throws IOException {
String dataFormat = new SimpleDateFormat("yyyyMMdd").format(new Date());
//xlsx格式:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet xls格式:application/vnd.ms-excelExport
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("满意度调查信息导出表" + dataFormat, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel
.write(response.getOutputStream(), ScorePsnExcelDTO.class)
.registerWriteHandler(EasyExcelUtils.getStyle())//引用样式
.registerWriteHandler(new CustomCellWriteWidthConfig())//自适应列宽
.registerWriteHandler(new CustomCellWriteHeightConfig())//自适应行高
.sheet("调查表")
.doWrite(data());//业务数据
}DTO（模板数据）：
package cn.hsa.pss.pw.web.thirdinterface.excelExport.dto;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
/**
* 医疗机构评价 -评价人DTO
*
* @Author：
* @Date：2024-01-23 16:46
* @Description：
*/
@Data
public class ScorePsnExcelDTO {
@ExcelProperty(value = "序号", index = 0)
private Integer no;
//定点统筹区（参保人统筹区
@ExcelProperty(value = "定点统筹区", index = 1)
private String areaCode;
//医药机构编码
@ExcelProperty(value = "医药机构编码", index = 2)
private String medInsCode;
//医药机构名称
@ExcelProperty(value = "医药机构名称", index = 3)
private String medInsName;
//医药机构类型
@ExcelProperty(value = "医药机构类型", index = 4)
private String medInsType;
//医疗类别
@ExcelProperty(value = "医疗类别", index = 5)
private String medType;
//就医人次
@ExcelProperty(value = "就医人次", index = 6)
private Integer medNum;
//参与调查人次
@ExcelProperty(value = "参与调查人次", index = 7)
private Integer scoreNum;
//很不满意
@ExcelProperty(value = {"评价分布", "很不满意"}, index = 8)
private Integer scoreOne;
//不满意
@ExcelProperty(value = {"评价分布", "不满意"}, index = 9)
private Integer scoreTwo;
//一般
@ExcelProperty(value = {"评价分布", "一般"}, index = 10)
private Integer scoreThree;
//比较满意
@ExcelProperty(value = {"评价分布", "比较满意"}, index = 11)
private Integer scoreFour;
//很满意
@ExcelProperty(value = {"评价分布", "很满意"}, index = 12)
private Integer scoreFive;
//参与调查率
@ExcelProperty(value = "参与调查率", index = 13)
private Double scoreRate;
//满意度
@ExcelProperty(value = "满意度", index = 14)
private String goodRate;
}
关键点1：响应头设置
//如果前端接收xlsx格式，则
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//如果前端接收xls格式，则
response.setContentType("application/vnd.ms-excelExport");
关键点2：ScorePsnExcelDTO
1、 @ExcelProperty(value = "序号", index = 0)
value对应的导出excel的列名，index代表顺序
2、如果涉及到单元格合并，可以这么写：
@ExcelProperty(value = {"评价分布", "很不满意"}, index = 8)
@ExcelProperty(value = {"评价分布", "一般"}, index = 10)
效果如下：
关键点3：
要使用get方法
自适应行高：
package cn.hsa.pss.pw.web.thirdinterface.excelExport.config;
import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import java.util.Iterator;
/**
* 自适应行高
*
* @Author：
* @Date：2024-02-01 14:00
* @Description：
*/
public class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy {
/**
* 默认高度
*/
private static final Integer DEFAULT_HEIGHT = 300;
@Override
protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
}
@Override
protected void setContentColumnHeight(Row row, int relativeRowIndex) {
Iterator<Cell> cellIterator = row.cellIterator();
if (!cellIterator.hasNext()) {
return;
}
// 默认为 1行高度
int maxHeight = 1;
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (cell.getCellTypeEnum() == CellType.STRING) {
String value = cell.getStringCellValue();
int len = value.length();
int num = 0;
if (len > 50) {
num = len % 50 > 0 ? len / 50 : len / 2 - 1;
}
if (num > 0) {
for (int i = 0; i < num; i++) {
value = value.substring(0, (i + 1) * 50 + i) + "\n" + value.substring((i + 1) * 50 + i, len + i);
}
}
if (value.contains("\n")) {
int length = value.split("\n").length;
maxHeight = Math.max(maxHeight, length) + 1;
}
}
}
row.setHeight((short) ((maxHeight) * DEFAULT_HEIGHT));
}
}
自适应列宽：
package cn.hsa.pss.pw.web.thirdinterface.excelExport.config;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 自适应列宽
*
* @Author：
* @Date：2024-02-01 13:38
* @Description：
*/
public class CustomCellWriteWidthConfig extends AbstractColumnWidthStyleStrategy {
private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
// 单元格文本长度大于60换行
if (columnWidth >= 0) {
if (columnWidth > 60) {
columnWidth = 60;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
Sheet sheet = writeSheetHolder.getSheet();
sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
}
/**
* 计算长度
*
* @param cellDataList
* @param cell
* @param isHead
* @return
*/
private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData<?> cellData = cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch (type) {
case STRING:
// 换行符（数据需要提前解析好）
int index = cellData.getStringValue().indexOf("\n");
return index != -1 ?
cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}
字体样式工具类：
package cn.hsa.pss.pw.web.thirdinterface.excelExport.utils;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import java.util.ArrayList;
/**
* 设置excel工具类
*
* @Author：wangguangxing
* @Date：2024-02-01 14:18
* @Description：
*/
public class EasyExcelUtils {
public static HorizontalCellStyleStrategy getStyle() {
//自定义表头样式 浅橙色 居中
WriteCellStyle headCellStyle = new WriteCellStyle();
headCellStyle.setFillForegroundColor(IndexedColors.TAN.getIndex()); //表头颜色
headCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //文本居中
//字体
WriteFont writeFont = new WriteFont();
writeFont.setFontName("微软雅黑"); //字体
writeFont.setFontHeightInPoints((short) 10); //字体大小
headCellStyle.setWriteFont(writeFont);
// 自动换行
headCellStyle.setWrapped(true);
//内容样式
WriteCellStyle contentCellStyle = new WriteCellStyle();
contentCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //文本居中
contentCellStyle.setWriteFont(writeFont);
//设置边框
contentCellStyle.setBorderLeft(BorderStyle.THIN); //左边框线
contentCellStyle.setBorderTop(BorderStyle.THIN); //顶部框线
contentCellStyle.setBorderRight(BorderStyle.THIN); //右边框线
contentCellStyle.setBorderBottom(BorderStyle.THIN); //底部框线
ArrayList<WriteCellStyle> contentCells = new ArrayList<>();
contentCells.add(contentCellStyle);
//样式策略
HorizontalCellStyleStrategy handler = new HorizontalCellStyleStrategy();
handler.setHeadWriteCellStyle(headCellStyle); //表头样式
handler.setContentWriteCellStyleList(contentCells); //内容样式
return new HorizontalCellStyleStrategy(headCellStyle, contentCells);
}
}
三、前端代码示例
1、
exportFile() {
this.downLoading = true
exportScoreList().then((res) => {
this.downLoading = false
const str = res.headers["content-disposition"]
const fileName = decodeURI(str.substr(str.indexOf("%")))
this.downloadFile(res.data, fileName)
}).catch((err) => {
this.downLoading = false
})
}
2、
downloadFile(res, fileName) {
let blob = new Blob([res], {type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'})
if (!fileName) {
fileName = res.headers['content-disposition'].split('filename=').pop();
}
if ('msSaveOrOpenBlob' in navigator) {
window.navigator.msSaveOrOpenBlob(blob, fileName);
} else {
const elink = document.createElement('a');
elink.download = fileName;
elink.style.display = 'none';
elink.href = window.URL.createObjectURL(blob);
document.body.appendChild(elink);
elink.setAttribute('href', elink.href)
elink.click();
document.body.removeChild(elink);
window.URL.revokeObjectURL(elink.href);
}
}
3、
export function exportScoreList() {
return axios({
url: `${path}/excel/download`,
method: "get",
responseType: "blob"
});
}
关键点1：
responseType: "blob" method: "get",
关键点2：
let blob = new Blob([res], {type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'})
要与后台响应头类型对应上。
