Java调用easyExcel 几行代码实现导出单个sheet

Java调用easyExcel 几行代码实现导出单个sheet

一. EasyExcel简介

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。 github地址:https://github.com/alibaba/easyexcel

$阿里巴巴团队开发$

官方文档地址

https://alibaba-easyexcel.github.io/index.html
https://www.yuque.com/easyexcel

二. 代码实现

1. 新建springboot项目(大家都会，我简单上几张图，创建时没有选择任何依赖)

$这里我把多余没用的文件删除了$

2. 导入maven依赖

我导入了这三个依赖

        <!-- easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>
        <!-- web项目 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- lombok-->
        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>

3.实体类编写

1.私有属性编写

@Data
@AllArgsConstructor
public class UserEntity {
    private String name;
    private String age;
    private String sex;
    private String password;
    private String address;
}

2.excel注解编写

在每一个属性上面添加 @ExcelProperty 注解 value为excel的第一行表头的名称 index索引是 位置

看图

4. ExcelUtils编写

直接上代码，注释都加了，固定写法

    /**
     * 单个sheet
     * @param response HttpServletResponse
     * @param list 数据 里面存放实体类
     */
    public static void write(HttpServletResponse response, List<UserEntity> list)throws Exception  {
        //获取response的输出流
        ExcelWriter writer = EasyExcel.write(response.getOutputStream()).build();
        // .writerSheet里面放入两个参数 1. sheet编号（从0开始）  2. sheet名字 
        // .head里面放入 实体类的class 
        WriteSheet sheet = EasyExcel.writerSheet(0, "用户列表").head(UserEntity.class).build();
        //写入
        writer.write(list,sheet);
        writer.finish();
    }

5. Controller层编写

@Controller
public class DownloadExcel {

    @RequestMapping("downloadexcel")
    public void easyexcel(HttpServletResponse response){
        List<UserEntity> list = new ArrayList();
        list.add(new UserEntity("小明","18","男","123","济南"));
        list.add(new UserEntity("小红","19","女","456","广州"));
        list.add(new UserEntity("小王","20","男","789","上海"));
        list.add(new UserEntity("小强","21","男","qaz","北京"));
        list.add(new UserEntity("小兰","22","女","wsx","深圳"));
        list.add(new UserEntity("小花","23","女","edc","郑州"));
        String filename = "用户信息表.xlsx";
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf8");
            //  URLEncoder 防止中文名乱码
            response.setHeader("Content-Disposition", "attachment;filename*= UTF-8''"+ URLEncoder.encode(filename,"UTF-8"));
            ExcelUtil.write(response,list);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

6. 测试

启动springboot，tomcat默认端口8080，然后再输入我们的路径 /downloadexcel
http://localhost:8080/downloadexcel
$在这里我说一下千万不要用ajxa请求，如果实现点击下载，就直接用js跳转（window.location=url）就行$

下载成功

打开看看：

好了最后分享一下 增加width的工具类：

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.util.HashMap;
import java.util.List;
import java.util.Map;

public class AutoWidth extends AbstractColumnWidthStyleStrategy {

    private static final int MAX_COLUMN_WIDTH = 255;
    //因为在自动列宽的过程中，有些设置地方让列宽显得紧凑，所以做出了个判断
    private static final int COLUMN_WIDTH = 10;
    private  Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);

    public AutoWidth() {
    }

    @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) {
            Map<Integer, Integer> maxColumnWidthMap = (Map)CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap(16);
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > MAX_COLUMN_WIDTH) {
                    columnWidth = MAX_COLUMN_WIDTH;
                }else {
                    if(columnWidth<COLUMN_WIDTH){
                        columnWidth =columnWidth*2;
                    }
                }
                Integer maxColumnWidth = (Integer)((Map)maxColumnWidthMap).get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    ((Map)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().length;
        } else {
            CellData cellData = (CellData)cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch(type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

使用方法：

在这里调用

最后在测试一下：

把controller模拟的数据变得长一点

然后重启 地址栏输入 http://localhost:8080/downloadexcel

下载成功 直接看数据：

好了本片文章就到这里