需引入的实体类
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
Excel对应实体类
list需要有一个对应的实体类,可以通过ExcelProperty注解来设置列名,可以通过ExcelIgnore注解来忽略某个属性
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
@Data
public class Vo {
@ExcelProperty("AA")
@ApiModelProperty(value = "AA")
private java.lang.String aa;
@ExcelProperty("BB")
@ApiModelProperty(value = "BB")
private java.lang.String bb;
// 忽略该属性
@ExcelIgnore()
@ApiModelProperty(value = "CC")
private java.lang.String cc;
}
后端接口代码
前端可以通过该接口直接获取xlsx信息,通过HttpServletResponse直接返回数据流
@GetMapping(value = "/exportXlsx")
public Result exportXlsx(HttpServletResponse response) throws IOException{
// 设置响应头
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 设置防止中文名乱码
String filename = URLEncoder.encode("列表信息", "utf-8");
// 获取当前时间
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
String date = simpleDateFormat.format(new Date());
// 文件下载方式(附件下载还是在当前浏览器打开) 给xlsx加上时间戳 便于查看
response.setHeader("Content-disposition", "attachment;filename=" +
filename+"-"+ simpleDateFormat.format(new Date())+ "-" + System.currentTimeMillis() + ".xlsx");
//需要返回的信息的列表
List<Vo> excelList = new ArrayList<>();
// 写入数据到excel 第二个参数必须与excelList对应 registerWriteHandler来自适应宽度
EasyExcel.write(response.getOutputStream(), Vo.class)
.sheet("列表信息").registerWriteHandler(new CustomCellWriteWeightConfig(0))
.doWrite(excelList);
return Result.OK("导出列表信息成功");
}
自适应宽度
自适应设置excel中的的每个列宽
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import java.nio.ByteBuffer;
import java.nio.CharBuffer;
import java.nio.charset.StandardCharsets;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class CustomCellWriteWeightConfig extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 255;
private static final int COLUMN_WIDTH_BASE = 255;
private final Map<Integer, Map<Integer, Double>> cache = new HashMap<>(8);
private Integer relativeRowIndex = -1;
public CustomCellWriteWeightConfig() {
}
public CustomCellWriteWeightConfig(Integer relativeRowIndex) {
//这里是指定从第几行开始自适应。0是第一行,1是第二行,以此类推
this.relativeRowIndex = relativeRowIndex;
}
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
if(this.relativeRowIndex == -1 || relativeRowIndex >= this.relativeRowIndex){
Map<Integer, Double> maxColumnWidthMap = cache.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>(16));
double columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > MAX_COLUMN_WIDTH) {
columnWidth = MAX_COLUMN_WIDTH;
}
Double maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), (int)(maxColumnWidthMap.get(cell.getColumnIndex())*COLUMN_WIDTH_BASE));
}
}
}
}
}
/**
* 计算长度
* @param cellDataList
* @param cell
* @param isHead
* @return
*/
private double dataLength(List<CellData> 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:
return getExcelWidth(cellData.getStringValue());
case BOOLEAN:
return getExcelWidth(cellData.getBooleanValue().toString());
case NUMBER:
return getExcelWidth(cellData.getNumberValue().toString());
default:
return -1;
}
}
}
}
/**
* 调整单元格字符字节宽度,easyExcel默认直接用的UTF-8的byte长度,导致一旦三字节的字符过多就会变得很宽,一字节的字符过多就会不够宽
*/
private double getExcelWidth(String str){
double length = 0.0;
char[] chars = str.toCharArray();
for(char c : chars){
byte[] bytes = this.getUtf8Bytes(c);
if(bytes.length == 1){
length += 1.05;
}
if(bytes.length == 2){
length += 1.5;
}
if(bytes.length == 3){
length += 1.85;
}
if(bytes.length == 4){
length += 2.2;
}
}
return length;
}
private byte[] getUtf8Bytes(char c) {
char[] chars = {c};
CharBuffer charBuffer = CharBuffer.allocate(chars.length);
charBuffer.put(chars);
charBuffer.flip();
ByteBuffer byteBuffer = StandardCharsets.UTF_8.encode(charBuffer);
return byteBuffer.array();
}
}