EasyExcel初体验

最新推荐文章于 2024-06-21 11:10:22 发布

qq_27621651

最新推荐文章于 2024-06-21 11:10:22 发布

阅读量275

点赞数

分类专栏：笔记文章标签： excel poi

本文链接：https://blog.csdn.net/qq_27621651/article/details/112308147

版权

笔记专栏收录该内容

4 篇文章 0 订阅

订阅专栏

第一步:pom文件引入easyexcel依赖

<dependency>
	   <groupId>com.alibaba</groupId>
	   <artifactId>easyexcel</artifactId>
	   <version>2.2.6</version>
	</dependency>
	<dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml-schemas</artifactId>
          <version>3.17</version>
    </dependency>
   <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.17</version>
    </dependency>

第二步:接口设计

import javax.servlet.http.HttpServletResponse;
import org.springframework.web.multipart.MultipartFile;

/**
 * <p>
 *  Excel文件处理接口
 * </p>
 * @since 2020-12-16
 */
public interface IExcelService {
	
	/**
	 * 导入excel
	 * @param  file 导入的文件信息
	 * @return 
	 * */
	public  boolean importExcel(MultipartFile file);
	
	/**
	 * 导出excel
	 * @param  keyword 搜索关键字
	 * @param  response
	 * @return 
	 * */
	public  void exportExcel(String keyword,HttpServletResponse response);
	
	/**
	 * 导出模板
	 * @return 
	 * */
	public  void downloadTemplate(HttpServletResponse response);
	
	/**
	 * 模块类型
	 * @return 
	 * */
	public  int menuType();

}

第三步:IExcelService接口实现示例

import com.warmer.kgmaker.consts.MenuConst;
import com.warmer.kgmaker.entity.SyPatent;
import com.warmer.kgmaker.entity.SyReport;
import com.warmer.kgmaker.entity.SyReport;
import com.warmer.kgmaker.excel.listener.DataListener;
import com.warmer.kgmaker.mapper.SyReportMapper;
import com.warmer.kgmaker.service.ICountHotWordService;
import com.warmer.kgmaker.service.IExcelService;
import com.warmer.kgmaker.service.IQueryService;
import com.warmer.kgmaker.service.ISyReportService;
import com.warmer.kgmaker.util.ExcelUtil;
import com.warmer.kgmaker.util.StringUtil;
import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

/**
 * <p>
 * 行业报告 服务实现类
 * </p>
 *
 * @since 2020-12-09
 */
@Service
public class SyReportServiceImpl extends ServiceImpl<SyReportMapper, SyReport> implements ISyReportService,**IExcelService**,IQueryService<SyReport>,ICountHotWordService {
	
	private static final Logger  Logger = LoggerFactory.getLogger(SyReportServiceImpl.class);


	@Override
	public boolean importExcel(MultipartFile file) {
	    try {
		   EasyExcel.read(file.getInputStream(), SyReport.class,new DataListener<SyReport>(this)).sheet().doRead();;
		} catch (IOException e) {
			Logger.error("excel导入异常", e);
			return false;
		}
		return true;
	}

	@Override
	public void exportExcel(String keyword, HttpServletResponse response) {
		 String fileName = "行业报告数据";
		 String sheetName= "行业报告数据";
		 List<SyReport> datas = list(getQueryWrapper4BackStage(keyword));
		 try {
	           ExcelUtil.writeExcel(response, datas, fileName, sheetName, SyReport.class);
	       } catch (Exception e) {
	    	   Logger.error("excel导出异常", e);
	       }
	}

	@Override
	public void downloadTemplate(HttpServletResponse response) {
		 String fileName = "行业报告导入模板";
		 String sheetName= "行业报告导入模板";
		 List<SyReport> datas = new ArrayList<>();
		 try {
			   List<String>  hiddenColumnNames =  new ArrayList<>();
			   hiddenColumnNames.add("序号");
			   hiddenColumnNames.add("点击量");
			   hiddenColumnNames.add("入库时间");
	           ExcelUtil.writeExcel(response, datas, fileName, sheetName, SyReport.class,hiddenColumnNames);
	       } catch (Exception e) {
	    	   Logger.error("excel模板下载异常", e);
	       }
	}

	@Override
	public int menuType() {
		return MenuConst.MENU_REPORT;    //此处就是一个数字，自定义就行
	}

导入详解

使用到的对象：

主要看看 @ExcelProperty(value=“序号”,index=0)的使用，这玩意可以设置表头名称和对应的索引位置

另外那个字段不需要导入导出，可以在字段上就上 @ExcelIgnore注解~~


 
 package com.warmer.kgmaker.entity;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import com.fasterxml.jackson.annotation.JsonFormat;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;

import java.time.LocalDateTime;
import java.io.Serializable;

/**
 * <p>
 * 行业报告
 * </p>
 * @since 2020-12-09
 */
@ContentRowHeight(30)      
@HeadRowHeight(15)
@ColumnWidth(15)
@ApiModel("行业报告对象信息")
public class SyReport extends Model<SyReport> {

    private static final long serialVersionUID = 1L;

    /**
     * id
     */
    @ExcelProperty(value="序号",index=0)
    @TableId(value = "id", type = IdType.AUTO)
    @ApiModelProperty(value="序号")
    private Integer id;

    /**
     * 所属行业
     */
    @ExcelProperty(value="所属行业")
    @ApiModelProperty("所属行业")
    private String industry;

    /**
     * 二级行业
     */
    @ExcelProperty(value="二级行业")
    @ApiModelProperty("二级行业")
    private String secIndustry;

    /**
     * 报告名称
     */
    @ExcelProperty(value="报告名称")
    @ApiModelProperty("报告名称")
    private String name;

    /**
     * 报告简介
     */
    @ExcelProperty(value="报告简介")
    @ApiModelProperty("报告简介")
    private String introduction;

    /**
     * 报告目录
     */
    @ExcelProperty(value="报告目录")
    @ApiModelProperty("报告目录")
    private String catalog;

    /**
     * 发布时间
     */
    @ExcelProperty(value="发布时间")
    @ApiModelProperty("发布时间")
    private String reporttime;

    /**
     * 链接
     */
    @ColumnWidth(100)
    @ApiModelProperty("链接")
    @ExcelProperty(value="链接")
    private String url;

    /**
     * 点击量
     */
    @ExcelProperty(value="点击量")
    @ApiModelProperty("点击量")
    private Long clicks;

    /**
     * 入库时间
     */
    @ExcelProperty(value="入库时间")
    @ApiModelProperty("入库时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime inputdate;

   /**
    * get、set省略
    **/
}

导入使用到的方法：

EasyExcel 提供的read方法可以直接从excel中读取数据，重点是数据监听 DataListener，

这个可以对每行数据做处理的

public boolean importExcel(MultipartFile file) {
	    try {
		   EasyExcel.read(file.getInputStream(), SyReport.class,new DataListener<SyReport>(this)).sheet().doRead();;
		} catch (IOException e) {
			Logger.error("excel导入异常", e);
			return false;
		}
		return true;
	}

下面的DataListener是一个通用的导入数据监听，主要是对带有excel图片的数据做处理及分批入库

package com.warmer.kgmaker.excel.listener;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.extension.service.IService;

/**
 * 模板的读取类
 * @param <T>
 * @since 2020-12-22
 */
public class DataListener<T> extends AnalysisEventListener<T> {
	
    private static final Logger LOGGER = LoggerFactory.getLogger(DataListener.class);
    /**
     * 每隔1000条存储数据库，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 1000;
    
    List<T> list = new ArrayList<T>();
    
    private IService<T> service;
    
    
    private  Map<String, String> picturePositionUrlMap ;
    
    private boolean isExitsImage;
    
	public DataListener(IService<T> service,Map<String, String> picturePositionUrlMap) {
		super();
		this.service = service;
		this.picturePositionUrlMap = picturePositionUrlMap;
		this.isExitsImage = true;
	}
	
	public DataListener(IService<T> service) {
		super();
		this.service = service;
		this.isExitsImage = false;
	}
	
	
	@Override
    public void invoke(T data, AnalysisContext context) {
	    if(isExitsImage){
	    	setPictureProperty(data, context);
	    }
		list.add(data);
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
    }
	
	/**
	 * 设置图片地址
	 * @param  data 数据
	 * @param context 上下文
	 * @return
	 * */
	@SuppressWarnings("deprecation")
	private void setPictureProperty(T data, AnalysisContext context){
		try {
			Field[] fields  = data.getClass().getDeclaredFields();
			int rowNum = context.getCurrentRowNum();
			for (Field field : fields) {
				ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
				if(null == excelProperty){
					continue;
				}
				String[] name = excelProperty.value();
				String  picturePositionKey = rowNum +","+name[0];
				if(!picturePositionUrlMap.containsKey(picturePositionKey)){
					continue;
				}
				field.setAccessible(true);
				Method  picrureUrlMethod = (Method) data.getClass().getMethod("set" + getMethodName(field.getName()),String.class);  
				picrureUrlMethod.invoke(data, picturePositionUrlMap.get(picturePositionKey));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} 
	}
	
    private static String getMethodName(String fildeName) throws Exception{  
       byte[] items = fildeName.getBytes();  
       items[0] = (byte) ((char) items[0] - 'a' + 'A');  
       return new String(items);  
   }
	

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        LOGGER.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        service.saveBatch(list);
        LOGGER.info("存储数据库成功！");
    }
}

下面这个是处理excel图片的辅助类：主要是读取图片，保存到本地、然后返回以行业-列命为key，

图片本地路径为value的map集合，（2003的读取图片可以读取到、2007的读取不到，不知道是不是读取的姿势不对）

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.IOException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFPictureData;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

/**
 * <p>
 * excel图片工具类
 * </p>
 * @since 2020-12-23
 */
public class ExcelImageUtil {
	
	/**
	 * 读取并保存图片
	 * @param  file excel文件
	 * @param  path  图片存储路径
	 * @return
	 * */
	 public static Map<String, String> loadPictures(MultipartFile file,String path,String storagePrefix){
		 Map<String, String> pictureMap = new HashMap<>();
		 Workbook workbook = null;
		 String[] fileSplit = file.getOriginalFilename().split("\\.");
		 String fileFormat = fileSplit[fileSplit.length-1];
        try {
			if (ExcelFormatEnum.XLS.getValue().equals(fileFormat)) {
			    workbook = new HSSFWorkbook(file.getInputStream());
			} else if (ExcelFormatEnum.XLSX.getValue().equals(fileFormat)) {
			    workbook = new XSSFWorkbook(file.getInputStream());
			} else {
			    return pictureMap;
			}
			//读取excel所有图片
			if (ExcelFormatEnum.XLS.getValue().equals(fileFormat)) {
			    getPicturesXLS(workbook,path,pictureMap,storagePrefix);
			} else {
			    getPicturesXLSX(workbook,path,pictureMap,storagePrefix);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		return pictureMap;
	 }
	 
	/**
     * 获取Excel2003的图片
     * @param workbook
     */
    @SuppressWarnings("unchecked")
    private static void getPicturesXLS(Workbook workbook,String path,Map<String, String> pictureMap,String storagePrefix) {
		List<HSSFPictureData> pictures = (List<HSSFPictureData>) workbook.getAllPictures();
        HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(0);
        HSSFRow header = sheet.getRow(0);
        Iterator<Cell>  iterator =  header.iterator();
        Map<Integer,String> columnIndex2NameMap = new HashMap<Integer,String>(); 
        while(iterator.hasNext()){
        	Cell  cell = iterator.next();
        	columnIndex2NameMap.put(cell.getColumnIndex(), cell.getStringCellValue());
        }
        if (pictures.size() != 0) {
            for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
                HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
                if (shape instanceof HSSFPicture) {
                    HSSFPicture pic = (HSSFPicture) shape;
                    int pictureIndex = pic.getPictureIndex() - 1;
                    HSSFPictureData picData = pictures.get(pictureIndex);
                    String key = anchor.getRow1()+","+ columnIndex2NameMap.get(Integer.valueOf(anchor.getCol1()+""));
                    pictureMap.put(key, printImg(picData,path,storagePrefix));
                }
            }
        }
    }
 
    /**
     * 获取Excel2007的图片
     * @param workbook
     */
    private static void getPicturesXLSX(Workbook workbook,String path,Map<String, String> pictureMap,String storagePrefix) {
        XSSFSheet xssfSheet = (XSSFSheet) workbook.getSheetAt(0);
        List<XSSFShape> list = xssfSheet.getDrawingPatriarch().getShapes();
        for (XSSFShape shape : list) {
            if (shape instanceof XSSFPicture) {
                XSSFPicture picture = (XSSFPicture) shape;
                XSSFClientAnchor cAnchor = (XSSFClientAnchor) picture.getAnchor();
                XSSFPictureData pdata = picture.getPictureData();
                String key = cAnchor.getRow1() + "," + cAnchor.getCol1(); // 行号-列号
                pictureMap.put(key, printImg(pdata,path,storagePrefix));
                
            }
        }
    }
    
    /**
     * 保存图片并返回存储地址
     * @param pic
     * @return
     */
    public static String printImg(PictureData pic,String path,String storagePrefix) {
        try {
        	File file = new File(path);
        	if(!file.exists()){
        		file.mkdirs();
        	}
            String ext = pic.suggestFileExtension(); //图片格式
            String filename = UUID.randomUUID().toString() + "." + ext;
            byte[] data = pic.getData();
            FileUtils.copyInputStreamToFile(new ByteArrayInputStream(data), new File(path, filename));
            return storagePrefix+filename;
        } catch (Exception e) {
            return "";
        }
    }

    /**
     * 枚举excel格式
     */
    public enum ExcelFormatEnum {
        XLS(0, "xls"),
        XLSX(1, "xlsx");
 
        private Integer key;
        private String value;
 
        ExcelFormatEnum(Integer key, String value) {
            this.key = key;
            this.value = value;
        }
 
        public Integer getKey() {
            return key;
        }
 
        public String getValue() {
            return value;
        }
    }

}

导出详解

@Override
	public void exportExcel(String keyword, HttpServletResponse response) {
		 String fileName = "行业报告数据";
		 String sheetName= "行业报告数据";
		 List<SyReport> datas = list(getQueryWrapper4BackStage(keyword));
		 try {
	           ExcelUtil.writeExcel(response, datas, fileName, sheetName, SyReport.class);
	       } catch (Exception e) {
	    	   Logger.error("excel导出异常", e);
	       }
	}

主要是设置样式和数写入excel

package com.warmer.kgmaker.util;

import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.warmer.kgmaker.excel.handler.CustomizeCellStyleStrategy;

/**
 * <p>
 * excel工具类
 * </p>
 * @since 2020-12-23
 */
public class ExcelUtil {
	
    /**
     * 导出
     * @param response  响应信息
     * @param data   数据
     * @param fileName  文件名
     * @param sheetName   sheet名称
     * @param clazz    数据所对应的类的class
     * @param customizeWriteHandler 自定义写处理
     * @param hiddenColumnNames 隐藏列名称集合
     * @throws Exception 
     */
    public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class<?> clazz,SheetWriteHandler customizeWriteHandler,List<String> hiddenColumnNames) throws Exception {
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        headWriteCellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        CustomizeCellStyleStrategy customizeCellStyleStrategy = new CustomizeCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle,hiddenColumnNames);
        ExcelWriterSheetBuilder excelWriterSheetBuilder =  EasyExcel.write(getOutputStream(fileName, response,ExcelTypeEnum.XLS.getValue()), clazz).excelType(ExcelTypeEnum.XLS).sheet(sheetName);
    	excelWriterSheetBuilder.registerWriteHandler(customizeCellStyleStrategy);
        if(customizeWriteHandler!=null){
        	excelWriterSheetBuilder.registerWriteHandler(customizeWriteHandler);
        }
        excelWriterSheetBuilder.doWrite(data);
    }
    
    /**
     * 导出
     * @param response  响应信息
     * @param data   数据
     * @param fileName  文件名
     * @param sheetName   sheet名称
     * @param clazz    数据所对应的类的class
     * @throws Exception 
     */
    public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class<?> clazz) throws Exception {
    	writeExcel(response, data, fileName, sheetName, clazz,null,null);
    }
    
    
    /**
     * 导出
     * @param response  响应信息
     * @param data   数据
     * @param fileName  文件名
     * @param sheetName   sheet名称
     * @param clazz    数据所对应的类的class
     * @param customizeWriteHandler  自定义写入处理
     * @throws Exception 
     */
    public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class<?> clazz,SheetWriteHandler customizeWriteHandler) throws Exception {
    	writeExcel(response, data, fileName, sheetName, clazz,customizeWriteHandler,null);
    }
    
    /**
     * 导出
     * @param response  响应信息
     * @param data   数据
     * @param fileName  文件名
     * @param sheetName   sheet名称
     * @param clazz    数据所对应的类的class
     * @param hiddenColumnNames 隐藏列名称集合
     * @throws Exception 
     */
    public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class<?> clazz,List<String> hiddenColumnNames) throws Exception {
    	writeExcel(response, data, fileName, sheetName, clazz,null,hiddenColumnNames);
    }
    
    /**
     * 设置响应信息
     * @param response  响应信息
     * @param fileName   文件名
     * @param suffix 文件后缀
     * @return
     */
    public static OutputStream getOutputStream(String fileName, HttpServletResponse response,String suffix) throws Exception {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + suffix);
        return response.getOutputStream();
    }

}

这个是自定义单元格样式写入处理器，我是把默认的处理类拷贝了一份，然后加入了自己的逻辑

，主要是为了隐藏某些列，因为下载模板的时候某些列是不需要展示的

package com.warmer.kgmaker.excel.handler;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.AbstractCellStyleStrategy;

/**
 * 自定义单元格样式写入处理器
 */
public class CustomizeCellStyleStrategy extends AbstractCellStyleStrategy {

	private WriteCellStyle headWriteCellStyle;
    private List<WriteCellStyle> contentWriteCellStyleList;

    private CellStyle headCellStyle;
    private List<CellStyle> contentCellStyleList;
    
    private  List<String> hiddenFieldNames;
    
    private Workbook workbook;

    public CustomizeCellStyleStrategy(WriteCellStyle headWriteCellStyle,
        List<WriteCellStyle> contentWriteCellStyleList) {
        this.headWriteCellStyle = headWriteCellStyle;
        this.contentWriteCellStyleList = contentWriteCellStyleList;
    }

    public CustomizeCellStyleStrategy(WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle, List<String> hiddenFieldNames) {
        this.headWriteCellStyle = headWriteCellStyle;
        contentWriteCellStyleList = new ArrayList<WriteCellStyle>();
        contentWriteCellStyleList.add(contentWriteCellStyle);
        this.hiddenFieldNames = hiddenFieldNames;
    }

    @Override
    protected void initCellStyle(Workbook workbook) {
        if (headWriteCellStyle != null) {
            headCellStyle = StyleUtil.buildHeadCellStyle(workbook, headWriteCellStyle);
        }
        if (contentWriteCellStyleList != null && !contentWriteCellStyleList.isEmpty()) {
            contentCellStyleList = new ArrayList<CellStyle>();
            for (WriteCellStyle writeCellStyle : contentWriteCellStyleList) {
                contentCellStyleList.add(StyleUtil.buildContentCellStyle(workbook, writeCellStyle));
            }
        }
        this.workbook = workbook;
    }

    @Override
    protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        if (headCellStyle == null) {
            return;
        }
        if(CollectionUtils.isNotEmpty(hiddenFieldNames)){
        	for (String fieldName : hiddenFieldNames) {
               	if(cell.getStringCellValue().equals(fieldName)){
               		Sheet sheet = workbook.getSheetAt(0);
               		sheet.setColumnHidden(cell.getColumnIndex(), true);
               	}
       		}
        }
        cell.setCellStyle(headCellStyle);
    }

    @Override
    protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        if (contentCellStyleList == null || contentCellStyleList.isEmpty()) {
            return;
        }
        cell.setCellStyle(contentCellStyleList.get(relativeRowIndex % contentCellStyleList.size()));
    }
	

}

导出时，需要将是图片路径的属性，读取图片写入到excel,可以实现easyexcel提供的Converter,实现自动的转换

这个是转换的用法，@ExcelProperty 的converter值设为自定义转换类的class

@ExcelProperty(value = "专利附图",converter=StringImageConverter.class)
 private String picture;

---------------------------------------分割线

package com.warmer.kgmaker.excel.converter;

import java.io.File;
import java.io.IOException;
import java.util.Properties;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.util.FileUtils;
import com.warmer.kgmaker.util.PropertiesUtil;

/**
 * String and image converter
 *
 * @author Jiaju Zhuang
 */
public class StringImageConverter implements Converter<String> {
	
	
    @Override
    public Class supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.IMAGE;
    }

    @Override
    public String convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
        GlobalConfiguration globalConfiguration) {
        throw new UnsupportedOperationException("Cannot convert images to string");
    }

    @Override
    public CellData convertToExcelData(String value, ExcelContentProperty contentProperty,
        GlobalConfiguration globalConfiguration) throws IOException {
    	File file = new File(PropertiesUtil.getProperties4UploadPath()+value);
    	if(file.exists()){
            return new CellData(FileUtils.readFileToByteArray(file));
    	}
		return new CellData(value);
    }
}

导出的列，如果一些列是需要下拉框的，可以自定义SheetWriteHandler ，然后注册下就行了

 ExcelWriterSheetBuilder excelWriterSheetBuilder =  EasyExcel.write(getOutputStream(fileName, response,ExcelTypeEnum.XLS.getValue()), clazz).excelType(ExcelTypeEnum.XLS).sheet(sheetName);
 excelWriterSheetBuilder.registerWriteHandler(customizeCellStyleStrategy);
   if(customizeWriteHandler!=null){
        	excelWriterSheetBuilder.registerWriteHandler(customizeWriteHandler);
       }

-------------------------------------------分割线----------------------------------------

package com.warmer.kgmaker.excel.handler;


import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;


/***
 * excel中生成下拉框 选项的处理类
 * */
public class SpinnerWriteHandler implements SheetWriteHandler {
 
	/**
	 * key: 为字段的所在列的index
	 * value： 下拉框所需数据      
	 * 例子 {1=['男','女']}
	 * */
	private Map<Integer,List<String>> mapDropDown;  
	
	public SpinnerWriteHandler(Map<Integer, List<String>> mapDropDown) {
		super();
		this.mapDropDown = mapDropDown;
	}

	@Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
 
    }
 
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();
        for (Map.Entry<Integer, List<String>> entry : mapDropDown.entrySet()) {
            CellRangeAddressList addressList = new CellRangeAddressList(1, 1000, entry.getKey(), entry.getKey());
            List<String> options = entry.getValue();
            String[] strings = new String[options.size()];
            DataValidationConstraint constraint = helper.createExplicitListConstraint(options.toArray(strings));
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
            /***处理Excel兼容性问题**/
            if (dataValidation instanceof XSSFDataValidation) {
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.setShowErrorBox(true);
            } else {
                dataValidation.setSuppressDropDownArrow(false);
            }
            sheet.addValidationData(dataValidation);
        }
    }
}

模板下载同导出差不多，就不说了

第四步：控制层设计

package com.warmer.kgmaker.controller.backstage;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.warmer.kgmaker.enums.MenuEnum;
import com.warmer.kgmaker.service.IExcelService;
import com.warmer.kgmaker.util.CasicHttpResponse;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import springfox.documentation.annotations.ApiIgnore;

/**
 * <p>
 * excel 控制器
 * </p>
 * @author dukeke
 * @since 2020-12-22
 */
@Controller
@RequestMapping("/backstage/excel")
@Api(tags ="后台excel操作统一接口")
public class ExcelController {
	
	
	private final static  Map<Integer,IExcelService>  excelServiceMap =  new HashMap<Integer,IExcelService>();
	
	private ExcelController(List<IExcelService> excelServices){
		for (IExcelService excelService : excelServices) {
			excelServiceMap.put(excelService.menuType(), excelService);
		}
	}
	
	/**
	 * 导入excel
	 * @param  menuType  模块类型 
	 * @param  file 导入的文件信息
	 * @return
	 * */ 
	@ApiOperation("excel导入接口")
	@ApiImplicitParams({
		@ApiImplicitParam(name="menuType",dataType="Integer",value="模块类型 企业:1 科技成果:2 专利:3 产业园区:4 行业报告:5 期刊论文:6 投资机构:7 新闻:8 通知通告:13 友情链接:9 行业:10 日志:11 用户:12 "),
		@ApiImplicitParam(name="file",allowMultiple=true,value="excel文件",dataType = "__file",paramType="form")})
	@PostMapping("/import")	
	public  @ResponseBody CasicHttpResponse<Boolean> importExcel(@RequestParam("menuType") int  menuType,@RequestParam(value="file") MultipartFile file){
		return CasicHttpResponse.success(excelServiceMap.get(menuType).importExcel(file));
	}

	/**
	 * 导出excel
	 * @param  menuType 模块类型
	 * @param  keyword  搜索关键字
	 * @return
	 * */ 
	@ApiOperation("excel导出接口")
	@ApiImplicitParams({
		@ApiImplicitParam(name="menuType",dataType="Integer",value="模块类型  企业:1 科技成果:2 专利:3 产业园区:4 行业报告:5 期刊论文:6 投资机构:7 新闻:8 通知通告:13友情链接:9 行业:10 日志:11 用户:12 "),
		@ApiImplicitParam(name="keyword",value="搜索关键字")})
	@GetMapping("/export")	
	public  void exportExcel(@RequestParam("menuType") int  menuType,
			@RequestParam(value = "keyword",required=false) String  keyword,
			@ApiIgnore HttpServletResponse response){
		    excelServiceMap.get(menuType).exportExcel(keyword,response);
	}
	
	/**
	 * 模板下载
	 * @param  menuType 模块类型
	 * @return
	 * */ 
	@ApiOperation("excel模板下载接口")
	@ApiImplicitParam(name="menuType",dataType="Integer",value="模块类型   企业:1 科技成果:2 专利:3 产业园区:4 行业报告:5 期刊论文:6 投资机构:7 新闻:8通知通告:13 友情链接:9 行业:10 日志:11 用户:12 ")
	@GetMapping("/download/template")	
	public  void downloadTemplate(@RequestParam("menuType")  int  menuType,@ApiIgnore HttpServletResponse response){
		 excelServiceMap.get(menuType).downloadTemplate(response);
	}
}

Over

qq_27621651

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
EasyExcel初体验

第一步:pom文件引入easyexcel依赖<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.6</version> </dependency> <dependency> <groupId>org.apach
复制链接

扫一扫

专栏目录