推荐篇：原来阿里也对excel情有独钟

最新推荐文章于 2024-08-26 15:43:49 发布

沉默木头人

最新推荐文章于 2024-08-26 15:43:49 发布

阅读量6.8k

点赞数

分类专栏： java 文章标签： excel poi

本文链接：https://blog.csdn.net/qq_44322555/article/details/109195968

版权

java 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

最近发现alibaba包下有一个excel导入导出工具类，非常好用，对于普通的场景及业务逻辑用起来非常方便。

记录一下；

位置：

该工具类在 com.alibaba包下。

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.6</version> 
</dependency>

特性：

该工具类对外提供了：读取和下载excel方法。

package com.gu.alibabaexcel.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Objects;

/**
 * @author Mr.Gu
 * @date 2020/10/21 9:16
 * @function : excel工具类
 **/
public class ExcelUtil<T> {

    /**
     * 读取excel数据
     * @param excel excel文件
     * @param cls 映射的实体类
     */
    public static <T> List<T> readExcel(MultipartFile excel, Class cls) throws IOException {
        return readExcel(excel,cls,1);
    }

    /**
     * 读取excel数据
     * @param excel excel文件
     * @param cls 映射的实体类
     * @param headRowNumber 从第几行开始读  0代表从第一行开始 1为第二行
     */
    public static <T> List<T> readExcel(MultipartFile excel,Class cls,int headRowNumber) throws IOException {
        return EasyExcelFactory.read(excel.getInputStream())
                .sheet()
                .headRowNumber(headRowNumber)
                .head(cls)
                .doReadSync();
    }

    /**
     * 下载excel
     */
    public static void download(HttpServletResponse response, Class cls, List data, String fileName, String sheetName) throws Exception {
        download(response, cls, data, fileName, sheetName,null);
    }

    public static void download(HttpServletResponse response, Class cls, List data, String fileName, String sheetName, WriteHandler handler) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");

        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        ExcelWriterSheetBuilder builder = EasyExcel.write(response.getOutputStream(), cls)
                .excelType(ExcelTypeEnum.XLSX)
                .sheet(sheetName)
                .registerWriteHandler(horizontalCellStyleStrategy);
        if(Objects.nonNull(handler)){
            builder.registerWriteHandler(handler);
        }
        builder.doWrite(data);
    }
}

使用方式：


	/**
     * 导出excel
     * @param response
     */
    @GetMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response) {
        List<User> userList = new ArrayList<>();
        userList.add(new User(1,"张三","男",20,new BigDecimal(99.5)));
        userList.add(new User(2,"李四","女",30,new BigDecimal(89.5)));
        userList.add(new User(3,"王五","男",40,new BigDecimal(79.5)));
        try {
            ExcelUtil.download(response,User.class,userList,"学生信息","Sheet1");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


	/**
     * 导入excel
     * @param file
     * @return
     */
    @PostMapping("/import")
    public String importExcel(@RequestBody MultipartFile file) {
        if (file != null) {
            try {
                List<User> objectList = ExcelUtil.readExcel(file, User.class,0);
                System.out.println(objectList);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return "成功";
    }

注：导入和导出时使用到的User实体类而不是普通的实体类，需要添加该包下的注解，才可在导入或导出的时候和excel中的列映射；
如果实体类中存在许多字段而导出时有些字段不需要就需要在该字段上添加@ExcelIgnore注解；easyExcel就不会使用该字段；如果该字段不使用并且也没有添加easyExcel的任何注解，那么导出时easyExcel也会将该字段导出到excel表格中；


import com.alibaba.excel.annotation.ExcelProperty;

import java.math.BigDecimal;

/**
 * @author Mr.Gu
 * @date 2020/10/21 9:20
 * @function :
 **/
public class User {

    @ExcelProperty(value = "编号",index = 0)
    private Integer number;

    @ExcelProperty(value = "姓名",index = 1)
    private String name;

    @ExcelProperty(value = "性别",index = 2)
    private String sex;

    @ExcelProperty(value = "年龄",index = 3)
    private Integer age;

    @ExcelProperty(value = "分数",index = 4)
    private BigDecimal score;

    //getter/setter方法

最后，使用该工具类导出的excel如下：
在这里插入图片描述
非常简单，好用的一个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.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelCellWriteHandler extends AbstractColumnWidthStyleStrategy {

    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

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

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 255) {
                    columnWidth = 255;
                }

                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    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 = 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;
                }
            }
        }
    }

}