第一步: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);
}
}