EasyExcel设置自动行宽行高并换行
Xplorist Lv6

EasyExcel设置自动行宽行高并换行

SpringBoot

  • 表格模型
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
package com.urbmn.device.model.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ContentFontStyle;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadFontStyle;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import io.swagger.annotations.ApiModel;
import lombok.Data;

@ApiModel("公交路线站点导出Excel-model")
//@ContentRowHeight(20)
//@HeadRowHeight(30)
//@ColumnWidth(25)
//@ColumnWidth(value = 15)
@HeadStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER, wrapped = BooleanEnum.TRUE)
//@HeadRowHeight(value = 40)
@HeadFontStyle(fontHeightInPoints = 10, fontName = "微软雅黑")
//@ContentRowHeight(value = 13)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER, wrapped = BooleanEnum.TRUE)
@ContentFontStyle(fontHeightInPoints = 10, fontName = "微软雅黑")
@Data
public class ExcelBusLineStation {
@ExcelProperty("序号")
private String orderNum;
//@ColumnWidth(20)
@ExcelProperty("公交路线")
private String busLine;
@ExcelProperty("车辆数")
private String busNum;
//@ExcelProperty("分公司")
//private String branchOffice;
//@ColumnWidth(40)
@ExcelProperty("总站")
private String masterStation;
//@ColumnWidth(255)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.LEFT, verticalAlignment = VerticalAlignmentEnum.CENTER, wrapped = BooleanEnum.TRUE)
@ExcelProperty("经停站")
private String intermediateStop;
}
  • 自定义的Excel格式处理
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
package com.urbmn.device.util.easyExcel;


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 com.alibaba.nacos.client.naming.utils.CollectionUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
public class CustomCellWeightWeightConfig extends AbstractColumnWidthStyleStrategy {
protected Map<Integer, Map<Integer, Integer>> cache = new HashMap<>();

@Override
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);
int columnWidthTemp = columnWidth;
if (columnWidth >= 0) {
if (columnWidth > 256) {
columnWidth = 256;
}

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 * 200);
}

//设置单元格类型
cell.setCellType(CellType.STRING);
// 数据总长度
int length = cell.getStringCellValue().length();
// 换行数
int rows = cell.getStringCellValue().split("\n").length;

int rowNum = rows;
if (columnWidth >= 256) {
int num = columnWidthTemp % 256 == 0 ? columnWidthTemp / 256 : columnWidthTemp / 256 + 1;
rowNum = Math.max(num, rows);
//log.info("rowIndex: {}, columnWidth: {}, length: {}, num: {}, rows: {}, rowNum: {}", cell.getRowIndex(), columnWidthTemp, length, num, rows, rowNum);
//log.info("value: {}", cell.getStringCellValue());
}

// 默认一行高为20
cell.getRow().setHeightInPoints(rowNum * 20);
}
}
}

// 计算长度
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;
}
}
}
}
}
  • 返回给前端byte数组,SpringMVC会自动将byte数组转换成base64格式的字符串
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
@Override
public CommonResult<?> exportBusLineStation(HttpServletRequest request, HttpServletResponse response) throws Exception {
List<ExcelBusLineStation> excelDataList = new ArrayList<>();

// TbBusLineMapper【07】查询所有有效数据
List<TbBusLine> tbBusLines = tbBusLineMapper.listAllValid();
for (int i = 0; i < tbBusLines.size(); i++) {
TbBusLine tbBusLine = tbBusLines.get(i);
String id = tbBusLine.getId();

// 查询车辆数
List<String> busNumList = tbDevicesInfoMapper.listBusNumByLineId(id);

// 查询总站和经停站
TbBusLineStationRel relParam = new TbBusLineStationRel();
relParam.setTbBusLineId(id);
relParam.setUpDownType("up");
// TbBusLineStationRelMapper【02】查询list-by-公交路线id和上下线类型-顺序-by-序号
List<TbBusLineStationRel> lineStations = tbBusLineStationRelMapper.listByBusLineIdUpDownType(relParam);

StringBuilder masterStationSb = new StringBuilder();
StringBuilder intermediateStopSb = new StringBuilder();
int count = 0;
for (int j = 0; j < lineStations.size(); j++) {
TbBusLineStationRel rel = lineStations.get(j);
String tbBusStationId = rel.getTbBusStationId();
TbBusStation tbBusStation = tbBusStationMapper.selectByPrimaryKey(tbBusStationId);
String stationName = tbBusStation.getName();

if (j != 0) {
stationName = " → " + stationName;
}
if (j == 0 || j == lineStations.size() - 1) {
masterStationSb.append(stationName);
}
// 长文本内容添加换行
if (count + stationName.getBytes().length >= 256) {
intermediateStopSb.append("\n");
count = 0;
}
intermediateStopSb.append(stationName);
count += stationName.getBytes().length;
}

ExcelBusLineStation item = new ExcelBusLineStation();
item.setOrderNum("" + (i + 1));
item.setBusLine(tbBusLine.getName());
item.setBusNum("" + busNumList.size());
//item.setBranchOffice(" ");
item.setMasterStation(masterStationSb.toString());
item.setIntermediateStop(intermediateStopSb.toString());
excelDataList.add(item);
}

ByteArrayOutputStream os = new ByteArrayOutputStream();
EasyExcel.write(os, ExcelBusLineStation.class).registerWriteHandler(new CustomCellWeightWeightConfig())
.sheet("1").doWrite(excelDataList);
byte[] bytes = os.toByteArray();
String dateStr = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
String fileOriginName = "公交路线站点汇总" + "-" + dateStr + ".xlsx";
response.setContentType(MediaType.APPLICATION_OCTET_STREAM);
response.setCharacterEncoding("utf-8");
response.addHeader("Content-Disposition", "attachment; filename="
+ new String((URLEncoder.encode(fileOriginName, StandardCharsets.UTF_8.toString())).getBytes(StandardCharsets.UTF_8),
"ISO8859-1"));
return CommonResult.success(bytes);
}

HTML

  • index.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>test</title>
<!-- <link rel="stylesheet" href="./css/xadmin.css"> -->
</head>
<body>
<div id="app">
<h1>我的第一个 JavaScript 程序</h1>
<p id="demo">这是一个段落</p>

<input type="file" v-on:change="upload($event)">

<button v-on:click="download()">下载</button>

<img v-bind:src="imgSrc">

<button v-on:click="downloadExcel()">下载Excel</button>

<button v-on:click="exportBusLineStation()">导出公交站点Excel</button>
</div>
</body>
<!-- <script type="text/javascript" src="./lib/axios.0.27.2.min.js"></script> -->
<!-- <script type="text/javascript" src="./lib/vue.min.js"></script> -->
<script src="https://cdn.jsdelivr.net/npm/axios/dist/axios.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/vue@2.7.14/dist/vue.js"></script>
<script type="text/javascript" src="./js/index.js"></script>
</html>

JavaScript

  • index.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
let app = new Vue({
el: '#app',
data: {
//imgSrc: '/api/file/download',
imgSrc: '',
},
created: function () {

},
methods: {
upload: function (event) {
var _self = this;
var file = event.target.files[0];
var param = new FormData();
param.append('file', file);
param.append('fileType', '3');

axios({
method: 'post',
url: '/api/file/upload',
headers: {'Content-Type':'multipart/form-data'},
data: param
}).then(function(response) {
var data = response.data;
console.log(response);
});
},
download: function () {
axios({
method: 'post',
url: '/api/file/download',
data: {
id: '855C4C501F6F4BEAABAC3ACCAA9ADC8F'
}
}).then(response => {
let contentDisposition = response.headers['content-disposition'];
let fileName = contentDisposition.substring(contentDisposition.indexOf('=') + 1);
let data = this.dataURLtoBlob(response.data.data);
let url = window.URL.createObjectURL(new Blob([data]));
let link = document.createElement('a');
link.href = url; // <a>下载文件
this.imgSrc = url; // <img>显示图片
link.setAttribute('download', decodeURIComponent(fileName));
document.body.appendChild(link);
link.click();
}).catch(error => {
alert("文件下载失败");
console.log(error);
});
},
dataURLtoBlob: function (dataurl) {
try {
let arr = dataurl.split(',');
let mime = arr[0].match(/:(.*?);/)[1];
let bstr = atob(arr[1]);
let n = bstr.length;
let u8arr = new Uint8Array(n);

while (n--) {
u8arr[n] = bstr.charCodeAt(n);
}
return new Blob([u8arr], { type: mime });
} catch {
let arr = dataurl.split(',');
let bstr = atob(arr[0]);
let n = bstr.length;
let u8arr = new Uint8Array(n);

while (n--) {
u8arr[n] = bstr.charCodeAt(n);
}
return new Blob([u8arr]);
}
},
downloadExcel: function () {
axios({
method: 'post',
url: '/api/tbDevicesInfo/exportLostDevice',
data: {
"pageNumber": 1,
"pageSize": 10,
"queryBy": {
"day": 3
}
}
}).then(response => {
let contentDisposition = response.headers['content-disposition'];
let fileName = contentDisposition.substring(contentDisposition.indexOf('=') + 1);
let data = this.dataURLtoBlob(response.data.data);
let url = window.URL.createObjectURL(new Blob([data]));
let link = document.createElement('a');
link.href = url; // <a>下载文件
this.imgSrc = url; // <img>显示图片
link.setAttribute('download', decodeURIComponent(fileName));
document.body.appendChild(link);
link.click();
}).catch(error => {
alert("文件下载失败");
console.log(error);
});
},
exportBusLineStation: function () {
axios({
method: 'post',
url: '/api/device/baseData/exportBusLineStation',
data: {}
}).then(response => {
let contentDisposition = response.headers['content-disposition'];
let fileName = contentDisposition.substring(contentDisposition.indexOf('=') + 1);
let data = this.dataURLtoBlob(response.data.data);
let url = window.URL.createObjectURL(new Blob([data]));
let link = document.createElement('a');
link.href = url; // <a>下载文件
//this.imgSrc = url; // <img>显示图片
link.setAttribute('download', decodeURIComponent(fileName));
document.body.appendChild(link);
link.click();
}).catch(error => {
alert("文件下载失败");
console.log(error);
});
},
},
});
 评论