引入阿里easyExcel依赖

                           com.alibaba            easyexcel            2.2.6                                                org.ehcache                    ehcache

自定义的阿里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> 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> 去传入                .doWrite(detail(list));    }    private static List<List> detail(List<Map> mapList) {        List<List> list = new ArrayList();        for (Map map : mapList) {            List objectList = new ArrayList();            Set<Map.Entry> entrySet = map.entrySet();            for (Map.Entry entry :entrySet){                objectList.add(entry.getValue());            }            list.add(objectList);        }        return list;    }    /**动态头传入*/    public static List<List> head(String[] header) {        List head0 = null;        List<List> 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> list = nmgtkmanagemapper.querytkinfobystbhs(stbhs);            // 最后一位传参为电子章地址 若需要可传            EasyExcelUtils.exportDetailLeave(list,"表格的大标题",heads,out,null);
相关文章
Copyright ©  maxssl.com 版权所有 浙ICP备2022011180号