easyexcel
分为实体类导入和非实体类导入
如果是实体导入 需要给实体类加注解：
例如：
@ContentRowHeight(20)
@HeadRowHeight(20)
@ColumnWidth(25)
public class UploadStandardVO {
//表头名称
@ExcelProperty(value = "id")
private String id;
//表头合并 索引下标
@ExcelProperty(index = 19)
private int equipmentOne;
//排除该字段
@ExcelIgnore
private String createUser;
}
---------------------------------------------------------------------------------------
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.event.SyncReadListener;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
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.fastjson.JSONObject;
import com.google.common.collect.Lists;
import com.sundear.baseapi.pojo.bo.AppUserInfo;
import com.sundear.baseapi.pojo.bo.Parameter;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.poifs.filesystem.FileMagic;
import org.apache.poi.ss.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
/**
* 表格处理工具类
*
* @author luc
* @date 2020/8/515:52
*/
@Slf4j
public class EasyExcelUtils {
public static void readExcel(InputStream inStream, Class head, ReadListener listener) {
EasyExcel.read(inStream, head, listener).sheet().doRead();
}
/**
* 导入excel ,生成json
* @param inputStream exece表格的输入流
* @return 返回双重list的集合
*/
public static List<Object> writeWithoutHead(InputStream inputStream) {
SyncReadListener listener = new SyncReadListener();
ExcelReader excelReader = EasyExcelFactory.read(inputStream, null, listener).headRowNumber(0).build();
excelReader.readAll();
List<Object> list = listener.getList();
log.info("读取结果:"+JSONObject.toJSONString(list));
excelReader.finish();
return list;
}
public static boolean isExcelFile(InputStream inputStream) {
boolean result = false;
try {
FileMagic fileMagic = FileMagic.valueOf(inputStream);
if (Objects.equals(fileMagic, FileMagic.OLE2)
|| Objects.equals(fileMagic, FileMagic.OOXML)) {
result = true;
}
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
/**
* 导出excel数据(非实体类导入)
*
* @param response response
* @param sheetName sheet名称
* @param headers 表头数据
* @param data 数据
* @param headParameter 表头格式
* @param dataParameter 表格格式
*/
public static void writeExcel(HttpServletResponse response, String sheetName, List<List<String>> headers, List<List<Object>> data, Parameter headParameter, Parameter dataParameter) throws IOException{
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
Integer num= (int)(Math.random()*1000 + 1);
String fileName = sheetName==null?("表格"+num+".xlsx"):sheetName;
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
//设置格式
HorizontalCellStyleStrategy styleStrategy = getStyleStrategy(dataParameter, headParameter);
sheetName = StringUtils.isBlank(sheetName) ? "data" : sheetName;
ExcelWriterBuilder write = EasyExcel.write(response.getOutputStream()).head(headers);
//添加水印
String waterMark=null;
if(Objects.nonNull(dataParameter)){
waterMark=dataParameter.getWaterMark();
}
if(Objects.nonNull(waterMark)){
write.inMemory(true).registerWriteHandler(new WaterMarkStrategy(waterMark));
}
write
//自动列宽
.registerWriteHandler(new CellWriteHandler())
.registerWriteHandler(styleStrategy)
.sheet(sheetName).doWrite(data);
}
/**
* 导出excel数据(实体类导入)
* 通过配置实体类注解控制表头
*
* @param response
* @param sheetName
* @param data
* @param headParameter
* @param dataParameter
* @param clazz
* @param <T>
* @throws IOException
*/
public static <T> void writeExcelByProperty(HttpServletResponse response, String sheetName, List<T> data, Parameter headParameter, Parameter dataParameter, Class<T> clazz) throws IOException{
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
Integer num= (int)(Math.random()*1000 + 1);
String fileName = sheetName==null?("表格"+num+".xlsx"):sheetName;
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
//设置格式
HorizontalCellStyleStrategy styleStrategy = getStyleStrategy(dataParameter, headParameter);
sheetName = StringUtils.isBlank(sheetName) ? "data" : sheetName;
ExcelWriterBuilder write = EasyExcel.write(response.getOutputStream(),clazz);
//添加水印
String waterMark=null;
if(Objects.nonNull(dataParameter)){
waterMark=dataParameter.getWaterMark();
}
if(Objects.nonNull(waterMark)){
write.inMemory(true).registerWriteHandler(new WaterMarkStrategy(waterMark));
}
write
//自动列宽
.registerWriteHandler(new CellWriteHandler())
.registerWriteHandler(styleStrategy)
.sheet(sheetName).doWrite(data);
}
/**
* excel字体 样式 背景色的设置
* @param dataParameter
* @param headParameter
* @return
*/
public static HorizontalCellStyleStrategy getStyleStrategy(Parameter dataParameter, Parameter headParameter){
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
if(Objects.nonNull(headParameter)){
// 背景默认设置为灰色
headWriteCellStyle.setFillForegroundColor(headParameter.getFillForegroundColor()!=0?headParameter.getFillForegroundColor():IndexedColors.GREY_25_PERCENT.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints(headParameter.getFontHeightInPoints()!=0?headParameter.getFontHeightInPoints():14);
// 字体样式
headWriteFont.setFontName(headParameter.getFontName());
// 是否加粗
headWriteFont.setBold(headParameter.getBold()==null?true:headParameter.getBold());
headWriteCellStyle.setWriteFont(headWriteFont);
//自动换行
headWriteCellStyle.setWrapped(true);
// 水平对齐方式
if(Objects.nonNull(headParameter.getHorizontalAlignment())){
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.valueOf(headParameter.getHorizontalAlignment()));
}
// 垂直对齐方式
if(Objects.nonNull(headParameter.getVerticalAlignment())){
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.valueOf(headParameter.getVerticalAlignment()));
}
//设置边框样式
headWriteCellStyle.setBorderLeft(Objects.nonNull(headParameter.getBorder())?BorderStyle.valueOf(headParameter.getBorder()):BorderStyle.THIN);
headWriteCellStyle.setBorderTop(Objects.nonNull(headParameter.getBorder())?BorderStyle.valueOf(headParameter.getBorder()):BorderStyle.THIN);
headWriteCellStyle.setBorderRight(Objects.nonNull(headParameter.getBorder())?BorderStyle.valueOf(headParameter.getBorder()):BorderStyle.THIN);
headWriteCellStyle.setBorderBottom(Objects.nonNull(headParameter.getBorder())?BorderStyle.valueOf(headParameter.getBorder()):BorderStyle.THIN);
}
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
if(Objects.nonNull(dataParameter)){
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// 背景默认白色
contentWriteCellStyle.setFillForegroundColor(dataParameter.getFillForegroundColor()!=0?dataParameter.getFillForegroundColor():IndexedColors.WHITE.getIndex());
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints(dataParameter.getFontHeightInPoints()!=0?dataParameter.getFontHeightInPoints():12);
// 字体样式
contentWriteFont.setFontName(dataParameter.getFontName());
// 是否加粗
contentWriteFont.setBold(dataParameter.getBold()==null?false:dataParameter.getBold());
contentWriteCellStyle.setWriteFont(contentWriteFont);
//设置边框样式
contentWriteCellStyle.setBorderLeft(Objects.nonNull(dataParameter.getBorder())?BorderStyle.valueOf(dataParameter.getBorder()):BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(Objects.nonNull(dataParameter.getBorder())?BorderStyle.valueOf(dataParameter.getBorder()):BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(Objects.nonNull(dataParameter.getBorder())?BorderStyle.valueOf(dataParameter.getBorder()):BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(Objects.nonNull(dataParameter.getBorder())?BorderStyle.valueOf(dataParameter.getBorder()):BorderStyle.THIN);
// 水平对齐方式
contentWriteCellStyle.setHorizontalAlignment(Objects.nonNull(dataParameter.getHorizontalAlignment())?HorizontalAlignment.valueOf(dataParameter.getHorizontalAlignment()):HorizontalAlignment.CENTER);
//垂直对齐方式
contentWriteCellStyle.setVerticalAlignment(Objects.nonNull(dataParameter.getVerticalAlignment())?VerticalAlignment.valueOf(dataParameter.getVerticalAlignment()):VerticalAlignment.BOTTOM);
}
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
public static void main(String[] args) {
List<Object> result = new ArrayList();
try{
FileInputStream inp = new FileInputStream("D://人口信息.xls");
result = writeWithoutHead(inp);
}catch (Exception e){
e.printStackTrace();
}
List<List<Object>> data = new ArrayList<>();
data.add(Arrays.asList("张三","IT",18,"男"));
data.add(Arrays.asList("小明","工程",25,"女"));
data.add(Arrays.asList("菲菲","设计",23,"女"));
data.add(Arrays.asList("王五","制图",24,"男"));
System.out.println(data);
//头信息
List<List<String>> headList = new ArrayList<>();
//处理表头数据
headList.add(Lists.newArrayList("名字"));
headList.add(Lists.newArrayList("职业"));
headList.add(Lists.newArrayList("年龄"));
headList.add(Lists.newArrayList("性别"));
headList.add(Lists.newArrayList("爱好"));
int size = headList.size();//总列数
int dataSize = data.get(0).size();
/* System.out.println(String.valueOf(size));
System.out.println(String.valueOf(dataSize));*/
try{
String filepath="D://人员调查表.xls";
FileOutputStream outputStream = new FileOutputStream(filepath);
/* writeExcel(outputStream,null,headList,data,null,null);*/
}catch (Exception e){
e.printStackTrace();
}
//方式2
List<AppUserInfo> users = new ArrayList<>();
AppUserInfo info = new AppUserInfo();
info.setUserId("1");
info.setAccount("张三");
info.setPassword("123456");
AppUserInfo info2 = new AppUserInfo();
info2.setUserId("2");
info2.setAccount("小米");
info2.setPassword("84324");
users.add(info);
users.add(info2);
try{
String filepath="D://人员调查表.xls";
/* FileOutputStream outputStream = new FileOutputStream(filepath);
writeExcelByProperty(outputStream,null,users,null,null,AppUserInfo.class);*/
}catch (Exception e){
e.printStackTrace();
}
}
}
----------------------------------------------------------------------------------------------------------
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.RequiredArgsConstructor;
import lombok.SneakyThrows;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRelation;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.font.FontRenderContext;
import java.awt.geom.Rectangle2D;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
/**
* 添加水印配置类
*/
@RequiredArgsConstructor
public class WaterMarkStrategy implements SheetWriteHandler {
private final String WATER_MARK;
public static ByteArrayOutputStream createWaterMark(String content) throws IOException {
int width = 180;
int height = 90;
// 获取bufferedImage对象
BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);
String fontType = "微软雅黑";
int fontStyle = Font.BOLD;
int fontSize = 12;
Font font = new Font(fontType, fontStyle, fontSize);
Graphics2D g2d = image.createGraphics(); // 获取Graphics2d对象
image = g2d.getDeviceConfiguration().createCompatibleImage(width, height, Transparency.TRANSLUCENT);
g2d.dispose();
g2d = image.createGraphics();
//设置字体颜色和透明度，最后一个参数为透明度
g2d.setColor(new Color(236, 88, 104, 80));
// 设置字体
g2d.setStroke(new BasicStroke(1));
// 设置字体类型 加粗 大小
g2d.setFont(font);
//设置倾斜度
g2d.rotate(-0.5, (double) image.getWidth() / 2, (double) image.getHeight() / 2);
FontRenderContext context = g2d.getFontRenderContext();
Rectangle2D bounds = font.getStringBounds(content, context);
double x = (width - bounds.getWidth()) / 2;
double y = (height - bounds.getHeight()) / 2;
double ascent = -bounds.getY();
double baseY = y + ascent;
// 写入水印文字原定高度过小，所以累计写水印，增加高度
g2d.drawString(content, (int) x, (int) baseY);
// 设置透明度
g2d.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER));
// 释放对象
g2d.dispose();
ByteArrayOutputStream os = new ByteArrayOutputStream();
ImageIO.write(image, "png", os);
return os;
}
/**
* 为Excel打上水印工具函数
*
* @param sheet excel sheet
* @param bytes 水印图片字节数组
*/
public static void putWaterRemarkToExcel(XSSFSheet sheet, byte[] bytes) {
XSSFWorkbook workbook = sheet.getWorkbook();
int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
String rID = sheet.addRelation(null, XSSFRelation.IMAGES, workbook.getAllPictures().get(pictureIdx))
.getRelationship().getId();
//设置背景图片----关键代码
sheet.getCTWorksheet().addNewPicture().setId(rID);
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@SneakyThrows
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
try (ByteArrayOutputStream waterMark = createWaterMark(WATER_MARK)) {
XSSFSheet sheet = (XSSFSheet) writeSheetHolder.getSheet();
putWaterRemarkToExcel(sheet, waterMark.toByteArray());
}
}
}
----------------------------------------------------------------------------
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author luchang on 2020/12/18 6:12 下午
* @description 设置excel自动列宽
*/
public class CellWriteHandler extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 255;
private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<Integer, Map<Integer, Integer>>(8);
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (!needSetWidth) {
return;
}
Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap<Integer, Integer>(16);
CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = dataLength(cellDataList, cell, isHead);
if (columnWidth < 0) {
return;
}
if (columnWidth > MAX_COLUMN_WIDTH) {
columnWidth = MAX_COLUMN_WIDTH;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
}
CellData cellData = cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
}
switch (type) {
case STRING:
return cellData.getStringValue().getBytes().length;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}