easyexcel导出时修改默认表头及表体样式

最新推荐文章于 2024-06-14 16:42:55 发布

qq_34934815

最新推荐文章于 2024-06-14 16:42:55 发布

阅读量1w

点赞数 5

文章标签： java apache 开发语言

本文链接：https://blog.csdn.net/qq_34934815/article/details/122601147

版权

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
github地址:https://github.com/alibaba/easyexcel
easyexcel的基本使用见官网easyexcel官网，这里不再赘述。

一、问题引入

easyexcel导出的默认样式是这样的，我不喜欢表头的灰色背景，感觉比较丑，而且有时候我们需要修改表头的背景颜色、字号、单元格格式等等。当默认的样式不能满足我们的需求时，就需要我们自行处理对应样式。
在这里插入图片描述

二、导出工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.azxc.rapid.modules.dataAnalysis.entity.CountEntityFarmerCooperatives;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.springblade.core.log.exception.ServiceException;
import org.springblade.core.tool.utils.DateUtil;
import org.springblade.core.tool.utils.StringUtil;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
/*
* 导出excel工具类
* */
public class ExcelUtils {
    public static <T> void exportExcel(List<T> list,String fileName,String sheetName,HttpServletResponse response){
        //设置返回值类型为
        try {
            if(list==null || list.size()==0) {
                throw new ServiceException("无数据可导出！");
            }
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            //编码 防止中文乱码
            fileName = URLEncoder.encode(fileName+DateUtil.time(),"UTF-8");
            response.setHeader("Content-disposition","attachment;filename="+fileName+".xlsx");
            EasyExcel.write(response.getOutputStream(),list.get(0).getClass())
                    .excelType(ExcelTypeEnum.XLSX)
                    .registerWriteHandler(CellStyleUtils.getHorizontalCellStyleStrategy())
                    .registerWriteHandler(new CustomCellWriteHandler())
                   // .registerWriteHandler(new CustomRowWriteHandler())
                    .sheet(sheetName)
                    .doWrite(list);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static <T> void exportExcel(List<T> list, Class<T> clazz,String fileName,String sheetName,HttpServletResponse response){
        //设置返回值类型为
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            //编码 防止中文乱码
            fileName = URLEncoder.encode(fileName+ DateUtil.time(),"UTF-8");
            response.setHeader("Content-disposition","attachment;filename="+fileName+".xlsx");
            EasyExcel.write(response.getOutputStream(),clazz)
                    .excelType(ExcelTypeEnum.XLSX)
                    .registerWriteHandler(CellStyleUtils.getHorizontalCellStyleStrategy())
                    .registerWriteHandler(new CustomCellWriteHandler())
                    //.registerWriteHandler(new CustomRowWriteHandler())
                    .sheet(sheetName)
                    .doWrite(list);
            } catch (IOException e) {
                e.printStackTrace();
            }
    }
    /**
     * @param fileName  文件路径名
     * @param sheetName sheet名
     * @param list      查询出来的数据
     * @param headList  传入的Excel头（例如：姓名，生日）
     * @param fileList  传入需要展示的字段（例如：姓名对应字段是name,生日对应字段是birthday）
     */
    public static void noModelWrite(List<?> list,String fileName, String sheetName,List<String> headList, List<String> fileList,HttpServletResponse response){
        //设置返回值类型为
        try {
            if(list==null || list.size()==0) {
                throw new ServiceException("无数据可导出！");
            }
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            //编码 防止中文乱码
            fileName = URLEncoder.encode(fileName+DateUtil.time(),"UTF-8");
            response.setHeader("Content-disposition","attachment;filename="+fileName+".xlsx");
            EasyExcel.write(response.getOutputStream())
                        .head(head(headList))
                        .excelType(ExcelTypeEnum.XLSX)
                        .registerWriteHandler(CellStyleUtils.getHorizontalCellStyleStrategy())
                        .registerWriteHandler(new CustomCellWriteHandler())
                       // .registerWriteHandler(new CustomRowWriteHandler())
                        .sheet(sheetName)
                        .doWrite(dataList(list,fileList));
            } catch (IOException e) {
                e.printStackTrace();
            }
    }
    /**
     * @param fileName  文件路径名
     * @param sheetName sheet名
     * @param list      查询出来的数据
     * @param headList  传入的Excel头（例如：姓名，生日）
     * @param fileList  传入需要展示的字段（例如：姓名对应字段是name,生日对应字段是birthday）
     */
    public static void noModelWritePlus(List<?> list,String fileName, String sheetName,List<List<String>> headList, List<String> fileList,HttpServletResponse response){
        //设置返回值类型为
        try {
            if(list==null || list.size()==0) {
                throw new ServiceException("无数据可导出！");
            }
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            //编码 防止中文乱码
            fileName = URLEncoder.encode(fileName+DateUtil.time(),"UTF-8");
            response.setHeader("Content-disposition","attachment;filename="+fileName+".xlsx");

            ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream())
                    .head(headList)
                    .excelType(ExcelTypeEnum.XLSX)
                    .registerWriteHandler(CellStyleUtils.getHorizontalCellStyleStrategy())
                    .registerWriteHandler(new CustomCellWriteHandler());
            if(list.size()>0 & list.get(0).getClass()== CountEntityFarmerCooperatives.class){
                excelWriterBuilder.registerWriteHandler(new ExcelFillCellMergeStrategy());
            }
            excelWriterBuilder.sheet(sheetName)
                   .doWrite(dataList(list,fileList));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**
     * 设置Excel头
     * @param headList  Excel头信息
     * @return
     */
    private static List<List<String>> head(List<String> headList) {
        List<List<String>> list = new ArrayList<>();
        for (String value : headList) {
            List<String> head = new ArrayList<>();
            head.add(value);
            list.add(head);
        }
        return list;
    }
    /**
     * 设置表格信息
     * @param dataList  查询出的数据
     * @param fileList  需要显示的字段
     * @return
     */
    private static List<List<?>> dataList(List<?> dataList, List<String> fileList) {
        List<List<?>> list = new ArrayList<>();
        for (Object person : dataList) {
            List<Object> data = new ArrayList<>();
            for (String fieldName : fileList) {
                /**通过反射根据需要显示的字段，获取对应的属性值*/
                data.add(getFieldValue(fieldName, person));
            }
            list.add(data);
        }
        return list;
    }
    /**
     * 根据传入的字段获取对应的get方法，如name,对应的getName方法
     * @param fieldName  字段名
     * @param person    对象
     * @return
     */
    private static Object getFieldValue(String fieldName, Object person) {
        try {
            String firstLetter = fieldName.substring(0, 1).toUpperCase();
            String getter = "get" + firstLetter + fieldName.substring(1);
            Method method = person.getClass().getMethod(getter);
            return method.invoke(person);
        } catch (Exception e) {
            return null;
        }
    }

    public static MultipartFile fileToMultipartFile(File file,String contentType) {
        if (StringUtil.isBlank(contentType)){
            contentType="text/plain";
        }
        FileItem fileItem = createFileItem(file,contentType);
        MultipartFile multipartFile = new CommonsMultipartFile(fileItem);
        return multipartFile;
    }

    private static FileItem createFileItem(File file,String contentType) {
        FileItemFactory factory = new DiskFileItemFactory(16, null);
        FileItem item = factory.createItem("textField", contentType, true, file.getName());
        int bytesRead = 0;
        byte[] buffer = new byte[8192];
        try {
            FileInputStream fis = new FileInputStream(file);
            OutputStream os = item.getOutputStream();
            while ((bytesRead = fis.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return item;
    }
}

三、修改表头及表体样式

EasyExcel链式调用registerWriteHandler(CellStyleUtils.getHorizontalCellStyleStrategy())；
CellStyleUtils相关代码如下：
在这里插入图片描述

    public static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为白色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //边框
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);
        //自动换行
        headWriteCellStyle.setWrapped(true);
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setBold(true);
        headWriteFont.setFontName("宋体");
        headWriteFont.setFontHeightInPoints((short)12);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景绿色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //边框
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        //自动换行
        contentWriteCellStyle.setWrapped(true);
        //文字
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short)12);
        contentWriteFont.setFontName("宋体");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

四、设置列宽

当然可以通过实体上加easyexcel提供注解来实现。我这里是为了统一所有的列宽，所有写了一个处理器，就不需要实体类上加列宽的注解了。
EasyExcel链式调用.registerWriteHandler(new CustomCellWriteHandler())
CustomCellWriteHandler相关代码如下：我把每列宽度都固定成3000，可根据需求修改。
在这里插入图片描述

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;

import java.util.List;

public class CustomCellWriteHandler  extends AbstractColumnWidthStyleStrategy{
    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        // 简单设置
        Sheet sheet = writeSheetHolder.getSheet();
        sheet.setColumnWidth(cell.getColumnIndex(), 3000);
    }
}

qq_34934815

关注

5
点赞
踩
47

收藏

觉得还不错? 一键收藏
2
评论
easyexcel导出时修改默认表头及表体样式

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。github地址:https://github.com/alibaba/easyexceleasyexcel的基本使用见官网easyexcel官网，这里不再赘述。一、问题引入easyexcel导出的默认样式是这样的，我不喜欢表头的灰色背景，感觉比较丑，而且有时候我们需要修改表头的背景颜色、字号、单元格格式等等。当默认的样式不能满足我们的需求时，就需要我们自行处理对应样式。
复制链接

扫一扫