阿里EasyExcel快速导出demo

引入阿里easyExcel依赖

1.       
2.         <dependency>
3.             <groupId>com.alibaba</groupId>
4.             <artifactId>easyexcel</artifactId>
5.             <version>2.2.6</version>
6.             <exclusions>
7.                 <exclusion>
8.                     <groupId>org.ehcache</groupId>
9.                     <artifactId>ehcache</artifactId>
10.                 </exclusion>
11.             </exclusions>
12.         </dependency>

复制代码

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

1. import com.alibaba.excel.write.handler.SheetWriteHandler;
2. import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
3. import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
4. import com.jerry.util.ExcelUtils;
5. import org.apache.commons.lang.StringUtils;
6. import org.apache.commons.logging.Log;
7. import org.apache.commons.logging.LogFactory;
8. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
9. import org.apache.poi.ss.usermodel.*;
10. import org.apache.poi.ss.util.CellRangeAddress;
12. import javax.imageio.ImageIO;
13. import java.awt.image.BufferedImage;
14. import java.io.ByteArrayOutputStream;
15. import java.io.IOException;
16. import java.net.URL;
19. public class SheetWriteHandlerUtil implements SheetWriteHandler {
21.     private String title;
22.     private String[] header;
23.     private String imageurl;
24.     private String sheetName;
25.     private final Log log = LogFactory.getLog(getClass());
27.     public SheetWriteHandlerUtil(String title, String[] header, String imageurl, String sheetName) {
28.         this.title = title;
29.         this.header = header;
30.         this.imageurl = imageurl;
31.         this.sheetName = sheetName;
32.     }
33.     public SheetWriteHandlerUtil(String sheetName) {
34.         this.sheetName = sheetName;
35.     }
38.     @Override
39.     public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
41.     }
43.     @Override
44.     public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
45.         Workbook workbook = writeWorkbookHolder.getWorkbook();
46.         Sheet sheet = workbook.getSheetAt(0);
47.         if (StringUtils.isNotEmpty(sheetName)){
48.             writeWorkbookHolder.getCachedWorkbook().setSheetName(0, sheetName);
49.         }
50.         if (StringUtils.isNotEmpty(title)){
51.             //设置标题
52.             Row row1 = sheet.createRow(0);
53.             row1.setHeight((short) 800);
54.             Cell cell = row1.createCell(0);
55.             //设置单元格内容
56.             cell.setCellValue(title);
57.             CellStyle cellStyle = workbook.createCellStyle();
58.             cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
59.             cellStyle.setAlignment(HorizontalAlignment.LEFT);
60.             Font font = workbook.createFont();
61.             font.setBold(true);
62.             font.setFontHeight((short) 400);
63.             cellStyle.setFont(font);
64.             cell.setCellStyle(cellStyle);
65.         }
66.         if (header != null){
67.             // 第一行大标题占位设置
68.             sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, header.length-1));
69.         }
70.         if(StringUtils.isNotEmpty(imageurl)){
71.             try {
72.                 imagewrite(writeWorkbookHolder,writeSheetHolder,imageurl);
73.             } catch (IOException e) {
74.                 e.printStackTrace();
75.                 log.error("easyexcel拦截器图片流处理出错"+ e.getMessage());
76.             }
77.         }
79.     }
80.     public void imagewrite(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder,String imageurl) throws IOException {
81.         Workbook workbook = writeWorkbookHolder.getWorkbook();
82.         Sheet sheet = workbook.getSheetAt(0);
83.         try (ByteArrayOutputStream picOut = new ByteArrayOutputStream()) {
84.             //读图片并写入流
85.             BufferedImage bufferedImage = ImageIO.read(new URL(imageurl));
86.             ImageIO.write(bufferedImage, "png", picOut);
87.             ExcelUtils.addPictureToSheet(sheet, 3, 3, 0,0,workbook.addPicture(picOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG),1.3,6.25);
88.         } catch (Exception e) {
89.             log.debug("", e);
90.         }
92.     }
93. }

复制代码

自定义的EasyExcelUtils方法类

1. import com.alibaba.excel.EasyExcel;
2. import com.alibaba.excel.write.metadata.style.WriteCellStyle;
3. import com.alibaba.excel.write.metadata.style.WriteFont;
4. import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
5. import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
6. import org.apache.commons.logging.Log;
7. import org.apache.commons.logging.LogFactory;
8. import org.apache.poi.ss.usermodel.*;
10. import java.io.ByteArrayOutputStream;
11. import java.util.*;
13. /**
14. * @author wangchaofan-n
15. */
16. public class EasyExcelUtils {
18.     private final Log log = LogFactory.getLog(getClass());
20.     /**
21.      *
22.      * @param list  数据
23.      * @param title 标题
24.      * @param header 动态列
25.      */
26.     public static void exportDetailLeave(List<Map<String,Object>> list, String title, String[] header,ByteArrayOutputStream out,String imageurl) {
27.         // 标题样式
28.         WriteCellStyle headWriteCellStyle = getHeadStyle();
29.         // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
30.         HorizontalCellStyleStrategy horizontalCellStyleStrategy =
31.                 new HorizontalCellStyleStrategy(headWriteCellStyle, new WriteCellStyle());
32.         EasyExcel.write(out)
33.                 // 第一行大标题样式设置
34.                 .registerWriteHandler(new SheetWriteHandlerUtil(title,header, imageurl, null))
35.                 //设置默认样式及写入头信息开始的行数
36.                 .useDefaultStyle(true).relativeHeadRowIndex(1)
37.                 // 表头、内容样式设置
38.                 .registerWriteHandler(horizontalCellStyleStrategy)
39.                 // 统一列宽,如需设置自动列宽则new LongestMatchColumnWidthStyleStrategy()
40.                 //.registerWriteHandler(new SimpleColumnWidthStyleStrategy(25))
41.                 .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
42.                 .sheet(title)
43.                 // 这里放入动态头
44.                 .head(head(header))
45.                 // 当然这里数据也可以用 List<List<String>> 去传入
46.                 .doWrite(detail(list));
48.     }
50.     private static List<List<Object>> detail(List<Map<String, Object>> mapList) {
51.         List<List<Object>> list = new ArrayList<>();
52.         for (Map<String, Object> map : mapList) {
53.             List<Object> objectList = new ArrayList<>();
54.             Set<Map.Entry<String,Object>> entrySet = map.entrySet();
55.             for (Map.Entry<String,Object> entry :entrySet){
56.                 objectList.add(entry.getValue());
57.             }
58.             list.add(objectList);
59.         }
60.         return list;
61.     }
64.     /**动态头传入*/
65.     public static List<List<String>> head(String[] header) {
66.         List<String> head0 = null;
67.         List<List<String>> list = new LinkedList<>();
68.         for (String h : header) {
69.             head0 = new LinkedList<>();
70.             head0.add(h);
71.             list.add(head0);
72.         }
73.         return list;
74.     }
77.     public static WriteCellStyle getHeadStyle(){
78.         // 头的策略
79.         WriteCellStyle headWriteCellStyle = new WriteCellStyle();
80.         // 背景颜色
81.         headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
82.         headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
83.         // 字体
84.         WriteFont headWriteFont = new WriteFont();
85.         headWriteFont.setFontName("黑体");//设置字体名字
86.         headWriteFont.setFontHeightInPoints((short)15);//设置字体大小
87.         headWriteFont.setBold(true);//字体加粗
88.         headWriteCellStyle.setWriteFont(headWriteFont); //在样式用应用设置的字体;
89.         // 样式
90.         headWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;
91.         headWriteCellStyle.setBottomBorderColor((short) 0);//设置底边框颜色;
92.         headWriteCellStyle.setBorderLeft(BorderStyle.THIN);  //设置左边框;
93.         headWriteCellStyle.setLeftBorderColor((short) 0);//设置左边框颜色;
94.         headWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;
95.         headWriteCellStyle.setRightBorderColor((short) 0);//设置右边框颜色;
96.         headWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;
97.         headWriteCellStyle.setTopBorderColor((short) 0); //设置顶边框颜色;
98.         headWriteCellStyle.setWrapped(true);  //设置自动换行;
99.         headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//设置水平对齐的样式为居中对齐;
100.         headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);  //设置垂直对齐的样式为居中对齐;
101.         //headWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适
103.         return headWriteCellStyle;
104.     }
109. }

复制代码

调用示例

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

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！