引入阿里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);