阿里EasyExcel快速导出demo

Java资料网推荐阿里EasyExcel快速导出demo这篇文章给大家，欢迎收藏Java资料网享受知识的乐趣

引入阿里easyExcel依赖

       <!-- easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
            <exclusions>
                <exclusion>
                    <groupId>org.ehcache</groupId>
                    <artifactId>ehcache</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

自定义的阿里easyexcel拦截器方法

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.jerry.util.ExcelUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.net.URL;


public class SheetWriteHandlerUtil implements SheetWriteHandler {

    private String title;
    private String[] header;
    private String imageurl;
    private String sheetName;
    private final Log log = LogFactory.getLog(getClass());

    public SheetWriteHandlerUtil(String title, String[] header, String imageurl, String sheetName) {
        this.title = title;
        this.header = header;
        this.imageurl = imageurl;
        this.sheetName = sheetName;
    }
    public SheetWriteHandlerUtil(String sheetName) {
        this.sheetName = sheetName;
    }


    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);
        if (StringUtils.isNotEmpty(sheetName)){
            writeWorkbookHolder.getCachedWorkbook().setSheetName(0, sheetName);
        }
        if (StringUtils.isNotEmpty(title)){
            //设置标题
            Row row1 = sheet.createRow(0);
            row1.setHeight((short) 800);
            Cell cell = row1.createCell(0);
            //设置单元格内容
            cell.setCellValue(title);
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
            Font font = workbook.createFont();
            font.setBold(true);
            font.setFontHeight((short) 400);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
        }
        if (header != null){
            // 第一行大标题占位设置
            sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, header.length-1));
        }
        if(StringUtils.isNotEmpty(imageurl)){
            try {
                imagewrite(writeWorkbookHolder,writeSheetHolder,imageurl);
            } catch (IOException e) {
                e.printStackTrace();
                log.error("easyexcel拦截器图片流处理出错"+ e.getMessage());
            }
        }

    }
    public void imagewrite(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder,String imageurl) throws IOException {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);
        try (ByteArrayOutputStream picOut = new ByteArrayOutputStream()) {
            //读图片并写入流
            BufferedImage bufferedImage = ImageIO.read(new URL(imageurl));
            ImageIO.write(bufferedImage, "png", picOut);
            ExcelUtils.addPictureToSheet(sheet, 3, 3, 0,0,workbook.addPicture(picOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG),1.3,6.25);
        } catch (Exception e) {
            log.debug("", e);
        }

    }
}

自定义的EasyExcelUtils方法类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.*;

import java.io.ByteArrayOutputStream;
import java.util.*;

/**
 * @author wangchaofan-n
 */
public class EasyExcelUtils {

    private final Log log = LogFactory.getLog(getClass());

    /**
     *
     * @param list  数据
     * @param title 标题
     * @param header 动态列
     */
    public static void exportDetailLeave(List<Map<String,Object>> list, String title, String[] header,ByteArrayOutputStream out,String imageurl) {
        // 标题样式
        WriteCellStyle headWriteCellStyle = getHeadStyle();
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, new WriteCellStyle());
        EasyExcel.write(out)
                // 第一行大标题样式设置
                .registerWriteHandler(new SheetWriteHandlerUtil(title,header, imageurl, null))
                //设置默认样式及写入头信息开始的行数
                .useDefaultStyle(true).relativeHeadRowIndex(1)
                // 表头、内容样式设置
                .registerWriteHandler(horizontalCellStyleStrategy)
                // 统一列宽,如需设置自动列宽则new LongestMatchColumnWidthStyleStrategy()
                //.registerWriteHandler(new SimpleColumnWidthStyleStrategy(25))
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .sheet(title)
                // 这里放入动态头
                .head(head(header))
                // 当然这里数据也可以用 List<List<String>> 去传入
                .doWrite(detail(list));

    }

    private static List<List<Object>> detail(List<Map<String, Object>> mapList) {
        List<List<Object>> list = new ArrayList<>();
        for (Map<String, Object> map : mapList) {
            List<Object> objectList = new ArrayList<>();
            Set<Map.Entry<String,Object>> entrySet = map.entrySet();
            for (Map.Entry<String,Object> entry :entrySet){
                objectList.add(entry.getValue());
            }
            list.add(objectList);
        }
        return list;
    }


    /**动态头传入*/
    public static List<List<String>> head(String[] header) {
        List<String> head0 = null;
        List<List<String>> list = new LinkedList<>();
        for (String h : header) {
            head0 = new LinkedList<>();
            head0.add(h);
            list.add(head0);
        }
        return list;
    }


    public static WriteCellStyle getHeadStyle(){
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("黑体");//设置字体名字
        headWriteFont.setFontHeightInPoints((short)15);//设置字体大小
        headWriteFont.setBold(true);//字体加粗
        headWriteCellStyle.setWriteFont(headWriteFont); //在样式用应用设置的字体;
        // 样式
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;
        headWriteCellStyle.setBottomBorderColor((short) 0);//设置底边框颜色;
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);  //设置左边框;
        headWriteCellStyle.setLeftBorderColor((short) 0);//设置左边框颜色;
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;
        headWriteCellStyle.setRightBorderColor((short) 0);//设置右边框颜色;
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;
        headWriteCellStyle.setTopBorderColor((short) 0); //设置顶边框颜色;
        headWriteCellStyle.setWrapped(true);  //设置自动换行;
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//设置水平对齐的样式为居中对齐;
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);  //设置垂直对齐的样式为居中对齐;
        //headWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适

        return headWriteCellStyle;
    }




}

调用示例

            ByteArrayOutputStream out = new ByteArrayOutputStream()
            // 此处填写表的列名
            String[] heads = new String[]{"列名1","学习","题干","选项","答案","解析"};
            // 此处为查询数据库语句
            List<Map<String,Object>> list = nmgtkmanagemapper.querytkinfobystbhs(stbhs);
            // 最后一位传参为电子章地址 若需要可传
            EasyExcelUtils.exportDetailLeave(list,"表格的大标题",heads,out,null);

文章来源:https://www.cnblogs.com/ruosu0909/p/17638076.html
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：jacktools123@163.com进行投诉反馈，一经查实，立即删除！