springboot使用easyexcel导出excel（使用converter转换Integer成String）

最新推荐文章于 2024-07-26 14:05:07 发布

temp_44

最新推荐文章于 2024-07-26 14:05:07 发布

阅读量7.7k

点赞数 2

分类专栏：随记

本文链接：https://blog.csdn.net/temp_44/article/details/108142725

版权

随记专栏收录该内容

10 篇文章 0 订阅

订阅专栏

1.引入依赖

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

2.写一个简单的工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSON;
import com.sun.deploy.net.URLEncoder;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 项目名称：test_excel
 * 类 名 称：EasyExcelUtil
 * 类 描 述：TODO
 * 创建时间：2020/8/12 11:35
 * 创 建 人：li
 */
public class EasyExcelUtil {
    public static <T> void writeExcel(HttpServletResponse response, List<T> data, Class tClass,String name){
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode(name + System.currentTimeMillis(), "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            // 这里需要设置不关闭流
            EasyExcel.write(response.getOutputStream(), tClass).autoCloseStream(Boolean.FALSE).sheet(name)
                    .doWrite(data);
        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<String, String>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            try {
                response.getWriter().println(JSON.toJSONString(map));
            } catch (IOException ioException) {
                ioException.printStackTrace();
            }
        }
    }
}

3.编写对应导出的类

public class Route {
    @ExcelProperty(value = "流水号", index = 0)
    private String routeNo;
    @ExcelProperty(value = "是否异常", index = 1,converter = IsExceptionConverter.class)
    private Integer isException;
    @ExcelProperty(value = "异常描述", index = 2)
    private String exceptionReason;
    @ExcelIgnore
    private String msg;

只给出了部分字段，其中
对于不需要表示出的，只需要@ExcelIgnore进行表明即可。
对于需要导出的，@ExcelProperty(value = "列名", index = 0)
如果需要根据值进行转换，需要编写Converter，是否异常，0表示正常，1表示异常。要根据0和1来导出成对应的字符串描述。

4.编写IsExceptionConverter

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.util.NumberUtils;

import java.math.BigDecimal;
import java.text.ParseException;


public class IsExceptionConverter implements Converter<Integer> {
    @Override
    public Class supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
                                     GlobalConfiguration globalConfiguration) throws ParseException {
        return NumberUtils.parseInteger(cellData.getStringValue(), contentProperty);
    }
    @Override
    public CellData convertToExcelData(Integer value, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        //0 正常 1 异常
        if (value == null) {
            return new CellData("");
        } else if (value == 0) {
            return new CellData("正常");
        } else if (value == 1) {
            return new CellData("异常");
        } else {
            return new CellData("状态异常");
        }
    }
}

5.编写Controller代码进行调用

   @RequestMapping("/route")
    public void routeTest(HttpServletRequest request, HttpServletResponse response, QueryRouteParam queryRouteParam){
        List<Route> data = RouteMapper.findRoute(queryRouteParam);
        EasyExcelUtil.writeExcel(response,data,Route.class,"行程");
    }