赞
踩
需引入的实体类
- <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();
- }
-
- }

Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。