com.hn.poi.ExcelUtil Maven / Gradle / Ivy
package com.hn.poi;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.lang.UUID;
import cn.hutool.core.util.RandomUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSON;
import cn.hutool.json.JSONUtil;
import cn.hutool.log.Log;
import cn.hutool.log.LogFactory;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.context.AnalysisContext;
import com.hn.upload.FileUploadUtil;
import com.hn.upload.UploadPlatform;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import java.io.*;
import java.lang.reflect.Field;
import java.text.MessageFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* easyExcel 工具类
*
* @author fei
*/
public class ExcelUtil {
private static final Log log = LogFactory.get();
/**
* 读取excel
*
* @param path excel 文件路径
* @param readListener 读监听器,继承这个抽象类,
* 并且实现{@link ReadListener#invoke(Object, AnalysisContext) 读操作}
* {@link ReadListener#doAfterAllAnalysed(AnalysisContext) 读完操作}
*/
public static void read(String path, ExcelReadListener readListener) {
EasyExcel.read(path, readListener).sheet().doRead();
}
/**
* 读取excel
*
* @param is excel 文件流
* @param readListener 读监听器,继承这个抽象类,
* 并且实现{@link ReadListener#invoke(Object, AnalysisContext) 读操作}
* {@link ReadListener#doAfterAllAnalysed(AnalysisContext) 读完操作}
*/
public static void read(InputStream is, ExcelReadListener readListener) {
EasyExcel.read(is, readListener).sheet().doRead();
}
/**
* 读取excel
*
* @param file excel 文件
* @param readListener 读监听器,继承这个抽象类,
* 并且实现{@link ReadListener#invoke(Object, AnalysisContext) 读操作}
* {@link ReadListener#doAfterAllAnalysed(AnalysisContext) 读完操作}
*/
public static void read(File file, ExcelReadListener readListener) {
EasyExcel.read(file, readListener).sheet().doRead();
}
/**
* 读取excel
*
* @param path excel 文件路径
* @param readListener 读监听器,继承这个抽象类,
* 并且实现{@link ReadListener#invoke(Object, AnalysisContext) 读操作}
* {@link ReadListener#doAfterAllAnalysed(AnalysisContext) 读完操作}
* @param sheetNo 工作表序号,第几个工作表,从0开始
*/
public static void read(String path, ExcelReadListener readListener, Integer sheetNo) {
EasyExcel.read(path, readListener).sheet(sheetNo).doRead();
}
/**
* 读取excel
*
* @param is excel 文件流
* @param readListener 读监听器,继承这个抽象类,
* 并且实现{@link ReadListener#invoke(Object, AnalysisContext) 读操作}
* {@link ReadListener#doAfterAllAnalysed(AnalysisContext) 读完操作}
* @param sheetNo 工作表序号,第几个工作表,从0开始
*/
public static void read(InputStream is, ExcelReadListener readListener, Integer sheetNo) {
EasyExcel.read(is, readListener).sheet(sheetNo).doRead();
}
/**
* 读取excel
*
* @param file excel 文件
* @param readListener 读监听器,继承这个抽象类,
* 并且实现{@link ReadListener#invoke(Object, AnalysisContext) 读操作}
* {@link ReadListener#doAfterAllAnalysed(AnalysisContext) 读完操作}
* @param sheetNo 工作表序号,第几个工作表,从0开始
*/
public static void read(File file, ExcelReadListener readListener, Integer sheetNo) {
EasyExcel.read(file, readListener).sheet(sheetNo).doRead();
}
/**
* 读取excel
*
* @param path excel 文件路径
* @param readListener 读监听器,继承这个抽象类,
* 并且实现{@link ReadListener#invoke(Object, AnalysisContext) 读操作}
* {@link ReadListener#doAfterAllAnalysed(AnalysisContext) 读完操作}
* @param sheetName 工作表名
*/
public static void read(String path, ExcelReadListener readListener, String sheetName) {
EasyExcel.read(path, readListener).sheet(sheetName).doRead();
}
/**
* 读取excel
*
* @param is excel 文件流
* @param readListener 读监听器,继承这个抽象类,
* 并且实现{@link ReadListener#invoke(Object, AnalysisContext) 读操作}
* {@link ReadListener#doAfterAllAnalysed(AnalysisContext) 读完操作}
* @param sheetName 工作表名
*/
public static void read(InputStream is, ExcelReadListener readListener, String sheetName) {
EasyExcel.read(is, readListener).sheet(sheetName).doRead();
}
/**
* 读取excel
*
* @param file excel 文件
* @param readListener 读监听器,继承这个抽象类,
* 并且实现{@link ReadListener#invoke(Object, AnalysisContext) 读操作}
* {@link ReadListener#doAfterAllAnalysed(AnalysisContext) 读完操作}
* @param sheetName 工作表名
*/
public static void read(File file, ExcelReadListener readListener, String sheetName) {
EasyExcel.read(file, readListener).sheet(sheetName).doRead();
}
/**
* 读取excel的数据和图片文件
* 同步(内存)读取，不适合读太大的excel文件
* 默认读取第一个sheet
* @param file excel 文件
* @param clazz class
* @param uploadPlatform {@link UploadPlatform}
* @return excel的数据和图片文件
*/
public static List readImg(File file, Class clazz,UploadPlatform uploadPlatform) {
return readImg(file,clazz,0,uploadPlatform,"excelImg");
}
/**
*
* 读取excel的数据和图片文件
* 同步(内存)读取，不适合读太大的excel文件
*
*
* @param file excel 文件
* @param clazz class
* @param sheetNo sheetNo
* @param uploadPlatform {@link UploadPlatform}
* @return excel的数据和图片文件
*/
public static List readImg(File file, Class clazz, Integer sheetNo,UploadPlatform uploadPlatform) {
return readImg(file,clazz,sheetNo,uploadPlatform,"excelImg");
}
/**
*
* 读取excel的数据和图片文件
* 同步(内存)读取，不适合读太大的excel文件
*
*
* @param file excel 文件
* @param clazz class
* @param sheetNo sheetNo
* @param uploadPlatform {@link UploadPlatform}
* @return
*/
public static List readImg(File file, Class clazz, Integer sheetNo, UploadPlatform uploadPlatform,String imgDir) {
// 这里 需要指定读用哪个class去读，然后读取第一个sheet 同步读取会自动finish,放在内存里面的
List list = EasyExcel.read(file).head(clazz).sheet(sheetNo).doReadSync();
Workbook workbook = null;
try {
workbook = createWorkbook(file);
} catch (IOException e) {
throw new ExcelException("createWorkbook fail");
}
Sheet sheet = workbook.getSheetAt(sheetNo);
Map imageInfoMap = getExcelPictures(sheet);
Field[] declaredFields = clazz.getDeclaredFields();
for (int i = 0; i < list.size(); i++) {
T data = list.get(i);
for (Field declaredField : declaredFields) {
ExcelProperty excelProperty = declaredField.getAnnotation(ExcelProperty.class);
if (excelProperty == null) {
continue;
}
//当前字段对应列索引
int index = excelProperty.index();
//自定义图片数据
if (declaredField.isAnnotationPresent(ExcelImageData.class)) {
String indexKey = (i + 1) + "-" + index;
declaredField.setAccessible(true);
PictureData pictureData = imageInfoMap.get(indexKey);
InputStream inputStream = new ByteArrayInputStream(pictureData.getData());
String fileName = UUID.fastUUID() + ".jpg";
if(StrUtil.isNotBlank(imgDir)){
fileName = imgDir + "/" + fileName;
}
FileUploadUtil.uploadFile(uploadPlatform, inputStream, fileName);
try {
declaredField.set(data, fileName);
imageInfoMap.remove(indexKey);
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
}
for (T data : list) {
log.info("读取到数据:{}", JSONUtil.toJsonStr(data));
}
return list;
}
private static Workbook createWorkbook(File file) throws IOException {
String fileName = file.getName();
FileInputStream fileInputStream = null;
try {
fileInputStream = new FileInputStream(file);
} catch (FileNotFoundException e) {
throw new ExcelException("找不到excel文件");
}
if (fileName.toLowerCase().endsWith(".xls")) {
return new HSSFWorkbook(fileInputStream);
} else if (fileName.toLowerCase().endsWith(".xlsx")) {
return new XSSFWorkbook(fileInputStream);
} else {
throw new ExcelException("不支持的Excel类型");
}
}
/**
* 获取sheet中图片及其图片位置信息
*
* @param sheet sheet
* @return 图片及其图片位置信息
*/
public static Map getExcelPictures(Sheet sheet) {
//key格式：行号-列号
Map map = new HashMap<>(sheet.getLastRowNum());
// xls
if (sheet instanceof HSSFSheet) {
HSSFSheet hssfSheet = (HSSFSheet) sheet;
List hssfShapes = hssfSheet.getDrawingPatriarch().getChildren();
for (HSSFShape shape : hssfShapes) {
if (!(shape instanceof HSSFPicture)) {
continue;
}
HSSFPicture picture = (HSSFPicture) shape;
HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
PictureData pictureData = picture.getPictureData();
String key = cAnchor.getRow1() + "-" + cAnchor.getCol1();
map.put(key, pictureData);
}
}
//xlsx
else if (sheet instanceof XSSFSheet) {
XSSFSheet xssfSheet = (XSSFSheet) sheet;
List relations = xssfSheet.getRelations();
for (POIXMLDocumentPart part : relations) {
if (!(part instanceof XSSFDrawing)) {
continue;
}
XSSFDrawing drawing = (XSSFDrawing) part;
List shapes = drawing.getShapes();
for (XSSFShape shape : shapes) {
XSSFPicture picture = (XSSFPicture) shape;
XSSFClientAnchor anchor = picture.getPreferredSize();
CTMarker marker = anchor.getFrom();
String key = marker.getRow() + "-" + marker.getCol();
map.put(key, picture.getPictureData());
}
}
}
return map;
}
}
© 2015 - 2024 Weber Informatics LLC | Privacy Policy