jeecg 框架的excel导入含图片（嵌入式，浮动式）

一、啰嗦
二、准备
三、代码
- 1、代码（修改覆写的ExcelImportServer）
- 2、代码（修改覆写的PoiPublicUtil）
- 3、代码（新增类SAXParserHandler）
- 4、代码（PoiPublicUtil文件中加入convertPackagePartToPictureData方法）
四、总结
- 1、ExcelImportServer代码
- 2、PoiPublicUtil代码
- 3、SAXParserHandler代码

看流程可以往下看看，使用代码直接调到四、总结

一、啰嗦

不写公共用的了，就直接写出自己遇到的。
原本以为跟导入一样，就只是一个简单的导出，鬼知道在excel中加入图片有两种方式（嵌入式和浮动式）
然而项目支持浮动式，可是当我使用浮动式插入图片时（没对比没伤害），他直接撑开了，下面这个还好，我还有一个直接占满我整个屏幕跟我壁纸一样了都，要是插入多了。光调节大小就得烦死了。
又得写代码，我一搜，文档没有，框架相关的文章也没有，只能自己写，我这个cv工程师直接崩溃（得亏还有ChatGPT，项目需要，直接拼拼凑凑开始干叭）

二、准备

说是准备其实也没啥，就是啰嗦+1了
目前excel的版本有两种Excel 2003 的.xls和Excel 2007 .xls.xlsx,也不知道说的标准不标准
这里使用的就是Excel 2007这个版本的，因为03版的还没有方法访问内置文件，07的可以进行访问（能访问也就能获取到其中的图片，也可能是我技术不到，反正我得问AI，还是简单来）
具体需要导入的数据如下（反正都差不多，我怕在涉及一些数据，就随便改了改，反正主要是导入图片，不要在意这些细节了）

三、代码

啰里啰嗦的终于要了代码环节了

具体导入在JeecgController类里的importExcel这个方法,复制出来修改为自己的就可以（都是大佬肯定嘎嘎简单）

具体使用的就是这个方法

List<T> list = ExcelImportUtil.importExcel(file.getInputStream(), clazz, params);

点击他会跳转到下面的方法

/*** Excel 导入 数据源IO流,不返回校验结果 导入 字段类型 Integer,Long,Double,Date,String,Boolean* * @param file* @param pojoClass* @param params* @return* @throws Exception*/
public static <T> List<T> importExcel(InputStream inputstream, Class<?> pojoClass, ImportParams params) throws Exception {return new ExcelImportServer().importExcelByIs(inputstream, pojoClass, params).getList();
}

然后再次点击又跳到下面这个方法

/*** Excel 导入 field 字段类型 Integer,Long,Double,Date,String,Boolean* * @param inputstream* @param pojoClass* @param params* @return* @throws Exception*/
public ExcelImportResult importExcelByIs(InputStream inputstream, Class<?> pojoClass, ImportParams params) throws Exception {if (LOGGER.isDebugEnabled()) {LOGGER.debug("Excel import start ,class is {}", pojoClass);}List<T> result = new ArrayList<T>();Workbook book = null;boolean isXSSFWorkbook = false;if (!(inputstream.markSupported())) {inputstream = new PushbackInputStream(inputstream, 8);}//begin-------author:liusq------date:20210129-----for:-------poi3升级到4兼容改造工作【重要敏感修改点】--------//------poi4.x begin----
//		FileMagic fm = FileMagic.valueOf(FileMagic.prepareToCheckMagic(inputstream));
//		if(FileMagic.OLE2 == fm){
//			isXSSFWorkbook=false;
//		}book = WorkbookFactory.create(inputstream);if(book instanceof XSSFWorkbook){isXSSFWorkbook=true;}LOGGER.info("  >>>  poi3升级到4.0兼容改造工作, isXSSFWorkbook = " +isXSSFWorkbook);//end-------author:liusq------date:20210129-----for:-------poi3升级到4兼容改造工作【重要敏感修改点】--------//begin-------author:liusq------date:20210313-----for:-------多sheet导入改造点--------//获取导入文本的sheet数//update-begin-author:taoyan date:20211210 for:https://gitee.com/jeecg/jeecg-boot/issues/I45C32 导入空白sheet报错if(params.getSheetNum()==0){int sheetNum = book.getNumberOfSheets();if(sheetNum>0){params.setSheetNum(sheetNum);}}//update-end-author:taoyan date:20211210 for:https://gitee.com/jeecg/jeecg-boot/issues/I45C32 导入空白sheet报错//end-------author:liusq------date:20210313-----for:-------多sheet导入改造点--------createErrorCellStyle(book);Map<String, PictureData> pictures;//update-begin-author:liusq date:20220609 for:issues/I57UPC excel导入 ImportParams 中没有startSheetIndex参数for (int i = params.getStartSheetIndex(); i < params.getStartSheetIndex()+ params.getSheetNum(); i++) {//update-end-author:liusq date:20220609 for:issues/I57UPC excel导入 ImportParams 中没有startSheetIndex参数if (LOGGER.isDebugEnabled()) {LOGGER.debug(" start to read excel by is ,startTime is {}", System.currentTimeMillis());}if (isXSSFWorkbook) {pictures = PoiPublicUtil.getSheetPictrues07((XSSFSheet) book.getSheetAt(i), (XSSFWorkbook) book);} else {pictures = PoiPublicUtil.getSheetPictrues03((HSSFSheet) book.getSheetAt(i), (HSSFWorkbook) book);}if (LOGGER.isDebugEnabled()) {LOGGER.debug(" end to read excel by is ,endTime is {}", new Date().getTime());}result.addAll(importExcel(result, book.getSheetAt(i), pojoClass, params, pictures));if (LOGGER.isDebugEnabled()) {LOGGER.debug(" end to read excel list by pos ,endTime is {}", new Date().getTime());}}if (params.isNeedSave()) {saveThisExcel(params, pojoClass, isXSSFWorkbook, book);}return new ExcelImportResult(result, verfiyFail, book);
}

1、代码（修改覆写的ExcelImportServer）

上面的importExcelByIs这个方法需要进行一些修改，但因为是源码包，无法进行修改，所以这时候就要把他复制出来，存放相同的地址，然后就完成替换了
包名为package org.jeecgframework.poi.excel.imports;下的ExcelImportServer文件
直接一顿cv完工

然后修改的代码如下（有可以导入嵌入式图片又不能影响之前导入使用）
具体就是：
1、加入了OPCPackage 进行读取07版的excel的包
2、之前运行完book = WorkbookFactory.create(inputstream);会把输入流进行关闭，这里将输入流复制到字节数组ByteArrayOutputStream，然后在获取到OPCPackage 之后手动关闭流

/*** Excel 导入 field 字段类型 Integer,Long,Double,Date,String,Boolean** @param inputstream* @param pojoClass* @param params* @return* @throws Exception*/public ExcelImportResult importExcelByIs(InputStream inputstream, Class<?> pojoClass, ImportParams params) throws Exception {if (LOGGER.isDebugEnabled()) {LOGGER.debug("Excel import start ,class is {}", pojoClass);}List<T> result = new ArrayList<T>();Workbook book = null;OPCPackage opc = null;boolean isXSSFWorkbook = false;if (!(inputstream.markSupported())) {inputstream = new PushbackInputStream(inputstream, 8);}//begin-------author:liusq------date:20210129-----for:-------poi3升级到4兼容改造工作【重要敏感修改点】--------//------poi4.x begin----
//		FileMagic fm = FileMagic.valueOf(FileMagic.prepareToCheckMagic(inputstream));
//		if(FileMagic.OLE2 == fm){
//			isXSSFWorkbook=false;
//		}// 将输入流内容复制到字节数组ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();byte[] buffer = new byte[1024];int bytesRead;while ((bytesRead = inputstream.read(buffer)) != -1) {byteArrayOutputStream.write(buffer, 0, bytesRead);}byte[] workbookData = byteArrayOutputStream.toByteArray();try (ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(workbookData)) {// 创建Workbook，不会直接关闭原始输入流book = WorkbookFactory.create(byteArrayInputStream);if (book instanceof XSSFWorkbook) {isXSSFWorkbook = true;opc = PackageHelper.open(new ByteArrayInputStream(workbookData));}} finally {try {//之前操作自动关闭了输入流，由于输入流内容复制到字节数组没有进行关闭，所以这里进行关闭inputstream.close();} catch (IOException e) {LOGGER.info("Error closing input stream: " + e.getMessage());}}
//        book = WorkbookFactory.create(nonClosingInputStream);
//        if (book instanceof XSSFWorkbook) {
//            isXSSFWorkbook = true;
//            opc = PackageHelper.open(inputstream);
//        }LOGGER.info("  >>>  poi3升级到4.0兼容改造工作, isXSSFWorkbook = " + isXSSFWorkbook);//end-------author:liusq------date:20210129-----for:-------poi3升级到4兼容改造工作【重要敏感修改点】--------//begin-------author:liusq------date:20210313-----for:-------多sheet导入改造点--------//获取导入文本的sheet数//update-begin-author:taoyan date:20211210 for:https://gitee.com/jeecg/jeecg-boot/issues/I45C32 导入空白sheet报错if (params.getSheetNum() == 0) {int sheetNum = book.getNumberOfSheets();if (sheetNum > 0) {params.setSheetNum(sheetNum);}}//update-end-author:taoyan date:20211210 for:https://gitee.com/jeecg/jeecg-boot/issues/I45C32 导入空白sheet报错//end-------author:liusq------date:20210313-----for:-------多sheet导入改造点--------createErrorCellStyle(book);Map<String, PictureData> pictures;//update-begin-author:liusq date:20220609 for:issues/I57UPC excel导入 ImportParams 中没有startSheetIndex参数for (int i = params.getStartSheetIndex(); i < params.getStartSheetIndex()+ params.getSheetNum(); i++) {//update-end-author:liusq date:20220609 for:issues/I57UPC excel导入 ImportParams 中没有startSheetIndex参数if (LOGGER.isDebugEnabled()) {LOGGER.debug(" start to read excel by is ,startTime is {}", System.currentTimeMillis());}//03版xls因为文件限制暂时无法识别内嵌图片//07版xlsx 此处以进行适配内嵌图片，内嵌和浮动自动识别返回if (isXSSFWorkbook) {pictures = PoiPublicUtil.getAllPictures(opc,(XSSFSheet) book.getSheetAt(i), (XSSFWorkbook) book);
//                pictures = PoiPublicUtil.getSheetPictrues07((XSSFSheet) book.getSheetAt(i), (XSSFWorkbook) book);} else {pictures = PoiPublicUtil.getSheetPictrues03((HSSFSheet) book.getSheetAt(i), (HSSFWorkbook) book);}if (LOGGER.isDebugEnabled()) {LOGGER.debug(" end to read excel by is ,endTime is {}", new Date().getTime());}result.addAll(importExcel(result, book.getSheetAt(i), pojoClass, params, pictures));if (LOGGER.isDebugEnabled()) {LOGGER.debug(" end to read excel list by pos ,endTime is {}", new Date().getTime());}}if (params.isNeedSave()) {saveThisExcel(params, pojoClass, isXSSFWorkbook, book);}return new ExcelImportResult(result, verfiyFail, book);}

2、代码（修改覆写的PoiPublicUtil）

上述代码在这里使用方法进行识别图片，具体加入的方法是getAllPictures(OPCPackage opc, XSSFSheet sheet, XSSFWorkbook workbook)

//03版xls因为文件限制暂时无法识别内嵌图片//07版xlsx 此处以进行适配内嵌图片，内嵌和浮动自动识别返回if (isXSSFWorkbook) {pictures = PoiPublicUtil.getAllPictures(opc,(XSSFSheet) book.getSheetAt(i), (XSSFWorkbook) book);
//                pictures = PoiPublicUtil.getSheetPictrues07((XSSFSheet) book.getSheetAt(i), (XSSFWorkbook) book);} else {pictures = PoiPublicUtil.getSheetPictrues03((HSSFSheet) book.getSheetAt(i), (HSSFWorkbook) book);}

点击会getSheetPictrues03或getSheetPictrues07也调到源码路径
包名package org.jeecgframework.poi.util;下的PoiPublicUtil文件
这里模仿上面也复制出来一份

加入以下方法，浮动式调用的值自带的，嵌入式使用了一个手写的方法

public static Map<String, PictureData> getAllPictures(OPCPackage opc, XSSFSheet sheet, XSSFWorkbook workbook) throws Exception {Map<String, PictureData> sheetIndexPicMap = new HashMap<>();// 处理嵌入式图片List<PackagePart> parts = opc.getParts();sheetIndexPicMap.putAll(getEmbedPictures(parts));// 处理浮动式图片sheetIndexPicMap.putAll(getSheetPictrues07(sheet, workbook));return sheetIndexPicMap;}

getEmbedPictures方法

/*** 获取嵌入式图片* @param parts* @return* @throws JDOMException* @throws IOException* @throws ParserConfigurationException*/private static Map<String, PictureData> getEmbedPictures(List<PackagePart> parts) throws JDOMException, IOException, ParserConfigurationException, SAXException {Map<String, Set<String>> mapImg = new HashMap<>();Map<String, String> mapImgPath = new HashMap<>();Map<Integer, List<String>> dataMap = new HashMap<>();for (PackagePart part : parts) {
//            System.out.println(part.getPartName());PackagePartName partName = part.getPartName();String name = partName.getName();if ("/xl/cellimages.xml".equals(name)) {SAXBuilder builder = new SAXBuilder();// 获取文档Document doc = builder.build(part.getInputStream());// 获取根节点Element root = doc.getRootElement();List<Element> cellImageList = root.getChildren();for (Element imgEle : cellImageList) {Element xdrPic = imgEle.getChildren().get(0);Element xdrNvPicPr = xdrPic.getChildren().get(0);Element xdrBlipFill = xdrPic.getChildren().get(1);Element aBlip = xdrBlipFill.getChildren().get(0);Attribute attr = aBlip.getAttributes().get(0);String imgId = xdrNvPicPr.getChildren().get(0).getAttributeValue("name");String id = attr.getValue();if (mapImg.containsKey(id)) {mapImg.get(id).add(imgId);} else {Set<String> set = new HashSet<>();set.add(imgId);mapImg.put(id, set);}}}if ("/xl/_rels/cellimages.xml.rels".equals(name)) {SAXBuilder builder = new SAXBuilder();// 获取文档Document doc = builder.build(part.getInputStream());// 获取根节点Element root = doc.getRootElement();List<Element> relationshipList = root.getChildren();for (Element relationship : relationshipList) {String id = relationship.getAttributeValue("Id");String target = relationship.getAttributeValue("Target");mapImgPath.put(id, target);}}if (name.contains("/xl/worksheets/sheet")) {// 获取文档String sheetNoStr = name.replace("/xl/worksheets/sheet", "").replace(".xml", "");Integer sheetNo = Integer.valueOf(sheetNoStr) - 1;// 步骤1：创建SAXParserFactory实例SAXParserFactory factory = SAXParserFactory.newInstance();// 步骤2：创建SAXParser实例SAXParser saxParser = factory.newSAXParser();SAXParserHandler saxParserHandler = new SAXParserHandler();saxParser.parse(part.getInputStream(), saxParserHandler);List<String> rows = saxParserHandler.getRows();dataMap.put(sheetNo, rows);}}Map<String, String> imgMap = new HashMap<>();for (String id : mapImg.keySet()) {Set<String> imgIds = mapImg.get(id);String path = mapImgPath.get(id);for (String imgId : imgIds) {imgMap.put(imgId, path);}}for (Integer key : dataMap.keySet()) {List<String> rows = dataMap.get(key);for (int i = 0; i < rows.size(); i++) {String imgId = rows.get(i);String imgId_index = imgId.substring(0, imgId.indexOf(":"));String imgId_url = imgId.substring(imgId.indexOf(":")+1);if (imgMap.containsKey(imgId_url)) {rows.set(i, imgId_index+":"+imgMap.get(imgId_url));}}}Map<String, PictureData> map = new HashMap<>();for (Integer key : dataMap.keySet()) {List<String> pathList = dataMap.get(key);for (int i = 0; i < pathList.size(); i++) {String path = pathList.get(i);String path_index = path.substring(0, path.indexOf(":"));String path_url = path.substring(path.indexOf(":")+1);if (StringUtils.isNotEmpty(path_url)) {for (PackagePart part : parts) {PackagePartName partName = part.getPartName();String name = partName.getName();if (name.contains(path_url)) {//进行转换PictureData pictureData = convertPackagePartToPictureData(part,new XSSFWorkbook());map.put(path_index, pictureData);break;}}}}}return map;}

上面的方法
1、调用了一个xml解析的自定义类SAXParserHandler
2、调用了一个PackagePart转换为PictureData的方法convertPackagePartToPictureData

3、代码（新增类SAXParserHandler）

实体类SAXParserHandler，我跟上面一样也这样建了

package org.jeecgframework.poi.xml;import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;import java.util.ArrayList;
import java.util.List;//xml解析
public class SAXParserHandler extends DefaultHandler {String value = null;List<String> rows = new ArrayList<>();int rowIndex = 0;int currentColumn = 0;public List<String> getRows() {return rows;}//用来标识解析开始@Overridepublic void startDocument() throws SAXException {super.startDocument();}//用来标识解析结束@Overridepublic void endDocument() throws SAXException {super.endDocument();}//解析xml元素@Overridepublic void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {super.startElement(uri, localName, qName, attributes);if (qName.equals("row")) {value = "";// 遇到新的行时，重置列号为0currentColumn = 0;} else if (qName.equals("c")) {// 获取列号String columnRef = attributes.getValue("r");if (columnRef != null) {currentColumn = parseColumnIndex(columnRef.replaceAll("[^A-Z]", ""));}}}@Overridepublic void endElement(String uri, String localName, String qName) throws SAXException {super.endElement(uri, localName, qName);if (qName.equals("c")) {if (value != null && value.contains("DISPIMG")) {value = value.substring(value.lastIndexOf("DISPIMG(")).replace("DISPIMG(\"", "");value = value.substring(0, value.indexOf("\""));
//                rows.add(rowIndex, value);rows.add(rowIndex + "_" + currentColumn + ":" + value);} else {
//                rows.add(rowIndex, null);rows.add(rowIndex + "_" + currentColumn + ":null");}value = "";}else if (qName.equals("row")) {rowIndex++;}}@Overridepublic void characters(char[] ch, int start, int length) throws SAXException {super.characters(ch, start, length);value += new String(ch, start, length);}// 解析列号private int parseColumnIndex(String columnRef) {int result = 0;for (int i = 0; i < columnRef.length(); i++) {result = result * 26 + (columnRef.charAt(i) - 'A' + 1);}return result - 1;}
}

4、代码（PoiPublicUtil文件中加入convertPackagePartToPictureData方法）

具体加入的方法convertPackagePartToPictureData

/*** 将PackagePart转换为PictureData* @param packagePart* @param workbook* @return*/public static PictureData convertPackagePartToPictureData(PackagePart packagePart, XSSFWorkbook workbook) {if (packagePart == null) {throw new IllegalArgumentException("PackagePart cannot be null");}if (workbook == null) {throw new IllegalArgumentException("Workbook cannot be null");}try (InputStream inputStream = packagePart.getInputStream()) {// 从PackagePart读取数据byte[] data = IOUtils.toByteArray(inputStream);// 确定内容，创建PictureTypeString contentType = packagePart.getContentType();// 默认PNGint pictureType = XSSFWorkbook.PICTURE_TYPE_PNG;if (contentType.equals("image/jpeg")) {pictureType = XSSFWorkbook.PICTURE_TYPE_JPEG;} else if (contentType.equals("image/png")) {pictureType = XSSFWorkbook.PICTURE_TYPE_PNG;}// 将图片数据添加到工作簿int pictureIndex = workbook.addPicture(data, pictureType);return workbook.getAllPictures().get(pictureIndex);} catch (IOException e) {e.printStackTrace();return null;}}

四、总结

具体使用的文件为下面四个
在这里插入图片描述

1、ExcelImportServer代码

/*** Copyright 2013-2015 JEECG (jeecgos@163.com)* <p>* Licensed under the Apache License, Version 2.0 (the "License");* you may not use this file except in compliance with the License.* You may obtain a copy of the License at* <p>* http://www.apache.org/licenses/LICENSE-2.0* <p>* Unless required by applicable law or agreed to in writing, software* distributed under the License is distributed on an "AS IS" BASIS,* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.* See the License for the specific language governing permissions and* limitations under the License.*/
package org.jeecgframework.poi.excel.imports;import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ooxml.util.PackageHelper;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecgframework.core.util.ApplicationContextUtil;
import org.jeecgframework.poi.excel.annotation.ExcelTarget;
import org.jeecgframework.poi.excel.entity.ImportParams;
import org.jeecgframework.poi.excel.entity.params.ExcelCollectionParams;
import org.jeecgframework.poi.excel.entity.params.ExcelImportEntity;
import org.jeecgframework.poi.excel.entity.result.ExcelImportResult;
import org.jeecgframework.poi.excel.entity.result.ExcelVerifyHanlderResult;
import org.jeecgframework.poi.excel.imports.base.ImportBaseService;
import org.jeecgframework.poi.excel.imports.base.ImportFileServiceI;
import org.jeecgframework.poi.excel.imports.verifys.VerifyHandlerServer;
import org.jeecgframework.poi.exception.excel.ExcelImportException;
import org.jeecgframework.poi.exception.excel.enums.ExcelImportEnum;
import org.jeecgframework.poi.util.ExcelUtil;
import org.jeecgframework.poi.util.PoiPublicUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;/*** Excel 导入服务** @author JEECG* @date 2014年6月26日 下午9:20:51*/
@SuppressWarnings({"rawtypes", "unchecked", "hiding"})
public class ExcelImportServer extends ImportBaseService {private final static Logger LOGGER = LoggerFactory.getLogger(ExcelImportServer.class);private CellValueServer cellValueServer;private VerifyHandlerServer verifyHandlerServer;private boolean verfiyFail = false;/*** 异常数据styler*/private CellStyle errorCellStyle;public ExcelImportServer() {this.cellValueServer = new CellValueServer();this.verifyHandlerServer = new VerifyHandlerServer();}/**** 向List里面继续添加元素** @param object* @param param* @param row* @param titlemap* @param targetId* @param pictures* @param params*/private void addListContinue(Object object, ExcelCollectionParams param, Row row, Map<Integer, String> titlemap, String targetId, Map<String, PictureData> pictures, ImportParams params) throws Exception {Collection collection = (Collection) PoiPublicUtil.getMethod(param.getName(), object.getClass()).invoke(object, new Object[]{});Object entity = PoiPublicUtil.createObject(param.getType(), targetId);String picId;boolean isUsed = false;// 是否需要加上这个对象for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {Cell cell = row.getCell(i);String titleString = (String) titlemap.get(i);if (param.getExcelParams().containsKey(titleString)) {if (param.getExcelParams().get(titleString).getType() == 2) {picId = row.getRowNum() + "_" + i;saveImage(object, picId, param.getExcelParams(), titleString, pictures, params);} else {saveFieldValue(params, entity, cell, param.getExcelParams(), titleString, row);}isUsed = true;}}if (isUsed) {collection.add(entity);}}/*** 获取key的值,针对不同类型获取不同的值** @Author JEECG* @date 2013-11-21* @param cell* @return*/private String getKeyValue(Cell cell) {if (cell == null) {return null;}Object obj = null;switch (cell.getCellTypeEnum()) {case STRING:obj = cell.getStringCellValue();break;case BOOLEAN:obj = cell.getBooleanCellValue();break;case NUMERIC:obj = cell.getNumericCellValue();break;case FORMULA:obj = cell.getCellFormula();break;}return obj == null ? null : obj.toString().trim();}/*** 获取保存的真实路径** @param excelImportEntity* @param object* @return* @throws Exception*/private String getSaveUrl(ExcelImportEntity excelImportEntity, Object object) throws Exception {String url = "";if (excelImportEntity.getSaveUrl().equals("upload")) {if (excelImportEntity.getMethods() != null && excelImportEntity.getMethods().size() > 0) {object = getFieldBySomeMethod(excelImportEntity.getMethods(), object);}url = object.getClass().getName().split("\\.")[object.getClass().getName().split("\\.").length - 1];return excelImportEntity.getSaveUrl() + "/" + url.substring(0, url.lastIndexOf("Entity"));}return excelImportEntity.getSaveUrl();}//update-begin--Author:xuelin  Date:20171205 for：TASK #2098 【excel问题】 Online 一对多导入失败--------------------private <T> List<T> importExcel(Collection<T> result, Sheet sheet, Class<?> pojoClass, ImportParams params, Map<String, PictureData> pictures) throws Exception {List collection = new ArrayList();Map<String, ExcelImportEntity> excelParams = new HashMap<String, ExcelImportEntity>();List<ExcelCollectionParams> excelCollection = new ArrayList<ExcelCollectionParams>();String targetId = null;if (!Map.class.equals(pojoClass)) {Field fileds[] = PoiPublicUtil.getClassFields(pojoClass);ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);if (etarget != null) {targetId = etarget.value();}getAllExcelField(targetId, fileds, excelParams, excelCollection, pojoClass, null);}ignoreHeaderHandler(excelParams, params);Iterator<Row> rows = sheet.rowIterator();Map<Integer, String> titlemap = getTitleMap(sheet, rows, params, excelCollection);//update-begin-author:liusq date:20220310 for:[issues/I4PU45]@excel里面新增属性fixedIndexSet<String> keys = excelParams.keySet();for (String key : keys) {if (key.startsWith("FIXED_")) {String[] arr = key.split("_");titlemap.put(Integer.parseInt(arr[1]), key);}}//update-end-author:liusq date:20220310 for:[issues/I4PU45]@excel里面新增属性fixedIndexSet<Integer> columnIndexSet = titlemap.keySet();Integer maxColumnIndex = Collections.max(columnIndexSet);Integer minColumnIndex = Collections.min(columnIndexSet);Row row = null;//跳过表头和标题行for (int j = 0; j < params.getTitleRows() + params.getHeadRows(); j++) {row = rows.next();}Object object = null;String picId;while (rows.hasNext() && (row == null || sheet.getLastRowNum() - row.getRowNum() > params.getLastOfInvalidRow())) {row = rows.next();//update-begin--Author:xuelin  Date:20171017 for：TASK #2373 【bug】表改造问题，导致 3.7.1批量导入用户bug-导入不成功--------------------// 判断是集合元素还是不是集合元素,如果是就继续加入这个集合,不是就创建新的对象//update-begin--Author:xuelin  Date:20171206 for：TASK #2451 【excel导出bug】online 一对多导入成功， 但是现在代码生成后的一对多online导入有问题了Cell keyIndexCell = row.getCell(params.getKeyIndex());if (excelCollection.size() > 0 && StringUtils.isEmpty(getKeyValue(keyIndexCell)) && object != null && !Map.class.equals(pojoClass)) {//update-end--Author:xuelin  Date:20171206 for：TASK #2451 【excel导出bug】online 一对多导入成功， 但是现在代码生成后的一对多online导入有问题了for (ExcelCollectionParams param : excelCollection) {addListContinue(object, param, row, titlemap, targetId, pictures, params);}} else {object = PoiPublicUtil.createObject(pojoClass, targetId);try {//update-begin-author:taoyan date:20200303 for:导入图片int firstCellNum = row.getFirstCellNum();if (firstCellNum > minColumnIndex) {firstCellNum = minColumnIndex;}int lastCellNum = row.getLastCellNum();if (lastCellNum < maxColumnIndex + 1) {lastCellNum = maxColumnIndex + 1;}for (int i = firstCellNum, le = lastCellNum; i < le; i++) {Cell cell = row.getCell(i);String titleString = (String) titlemap.get(i);if (excelParams.containsKey(titleString) || Map.class.equals(pojoClass)) {if (excelParams.get(titleString) != null && excelParams.get(titleString).getType() == 2) {picId = row.getRowNum() + "_" + i;saveImage(object, picId, excelParams, titleString, pictures, params);} else {if (params.getImageList() != null && params.getImageList().contains(titleString)) {if (pictures != null) {picId = row.getRowNum() + "_" + i;PictureData image = pictures.get(picId);if (image != null) {byte[] data = image.getData();params.getDataHanlder().setMapValue((Map) object, titleString, data);}}} else {saveFieldValue(params, object, cell, excelParams, titleString, row);}//update-end-author:taoyan date:20200303 for:导入图片}}}for (ExcelCollectionParams param : excelCollection) {addListContinue(object, param, row, titlemap, targetId, pictures, params);}//update-begin-author:taoyan date:20210526 for:autopoi导入excel 如果单元格被设置边框，即使没有内容也会被当做是一条数据导入 #2484if (isNotNullObject(pojoClass, object)) {collection.add(object);}//update-end-author:taoyan date:20210526 for:autopoi导入excel 如果单元格被设置边框，即使没有内容也会被当做是一条数据导入 #2484} catch (ExcelImportException e) {if (!e.getType().equals(ExcelImportEnum.VERIFY_ERROR)) {throw new ExcelImportException(e.getType(), e);}}}//update-end--Author:xuelin  Date:20171017 for：TASK #2373 【bug】表改造问题，导致 3.7.1批量导入用户bug-导入不成功--------------------}return collection;}/*** 判断当前对象不是空* @param pojoClass* @param object* @return*/private boolean isNotNullObject(Class pojoClass, Object object) {try {Method method = pojoClass.getMethod("isNullObject");if (method != null) {Object flag = method.invoke(object);if (flag != null && true == Boolean.parseBoolean(flag.toString())) {return false;}}} catch (NoSuchMethodException e) {LOGGER.debug("未定义方法 isNullObject");} catch (IllegalAccessException e) {LOGGER.warn("没有权限访问该方法 isNullObject");} catch (InvocationTargetException e) {LOGGER.warn("方法调用失败 isNullObject");}return true;}/*** 获取忽略的表头信息* @param excelParams* @param params*/private void ignoreHeaderHandler(Map<String, ExcelImportEntity> excelParams, ImportParams params) {List<String> ignoreList = new ArrayList<>();for (String key : excelParams.keySet()) {String temp = excelParams.get(key).getGroupName();if (temp != null && temp.length() > 0) {ignoreList.add(temp);}}params.setIgnoreHeaderList(ignoreList);}/*** 获取表格字段列名对应信息** @param rows* @param params* @param excelCollection* @return*/private Map<Integer, String> getTitleMap(Sheet sheet, Iterator<Row> rows, ImportParams params, List<ExcelCollectionParams> excelCollection) throws Exception {Map<Integer, String> titlemap = new HashMap<Integer, String>();Iterator<Cell> cellTitle = null;String collectionName = null;ExcelCollectionParams collectionParams = null;Row headRow = null;int headBegin = params.getTitleRows();//update_begin-author:taoyan date:2020622 for：当文件行数小于代码里设置的TitleRows时headRow一直为空就会出现死循环int allRowNum = sheet.getPhysicalNumberOfRows();//找到首行表头，每个sheet都必须至少有一行表头while (headRow == null && headBegin < allRowNum) {headRow = sheet.getRow(headBegin++);}if (headRow == null) {throw new Exception("不识别该文件");}//update-end-author:taoyan date:2020622 for：当文件行数小于代码里设置的TitleRows时headRow一直为空就会出现死循环//设置表头行数if (ExcelUtil.isMergedRegion(sheet, headRow.getRowNum(), 0)) {params.setHeadRows(2);} else {params.setHeadRows(1);}cellTitle = headRow.cellIterator();while (cellTitle.hasNext()) {Cell cell = cellTitle.next();String value = getKeyValue(cell);if (StringUtils.isNotEmpty(value)) {titlemap.put(cell.getColumnIndex(), value);//加入表头列表}}//多行表头for (int j = headBegin; j < headBegin + params.getHeadRows() - 1; j++) {headRow = sheet.getRow(j);cellTitle = headRow.cellIterator();while (cellTitle.hasNext()) {Cell cell = cellTitle.next();String value = getKeyValue(cell);if (StringUtils.isNotEmpty(value)) {int columnIndex = cell.getColumnIndex();//当前cell的上一行是否为合并单元格if (ExcelUtil.isMergedRegion(sheet, cell.getRowIndex() - 1, columnIndex)) {collectionName = ExcelUtil.getMergedRegionValue(sheet, cell.getRowIndex() - 1, columnIndex);if (params.isIgnoreHeader(collectionName)) {titlemap.put(cell.getColumnIndex(), value);} else {titlemap.put(cell.getColumnIndex(), collectionName + "_" + value);}} else {//update-begin-author:taoyan date:20220112 for: JT640 【online】导入 无论一对一还是一对多 如果子表只有一个字段 则子表无数据// 上一行不是合并的情况下另有一种特殊的场景： 如果当前单元格和上面的单元格同一列 即子表字段只有一个 所以标题没有出现跨列String prefixTitle = titlemap.get(cell.getColumnIndex());if (prefixTitle != null && !"".equals(prefixTitle)) {titlemap.put(cell.getColumnIndex(), prefixTitle + "_" + value);} else {titlemap.put(cell.getColumnIndex(), value);}//update-end-author:taoyan date:20220112 for: JT640 【online】导入 无论一对一还是一对多 如果子表只有一个字段 则子表无数据}/*int i = cell.getColumnIndex();// 用以支持重名导入if (titlemap.containsKey(i)) {collectionName = titlemap.get(i);collectionParams = getCollectionParams(excelCollection, collectionName);titlemap.put(i, collectionName + "_" + value);} else if (StringUtils.isNotEmpty(collectionName) && collectionParams.getExcelParams().containsKey(collectionName + "_" + value)) {titlemap.put(i, collectionName + "_" + value);} else {collectionName = null;collectionParams = null;}if (StringUtils.isEmpty(collectionName)) {titlemap.put(i, value);}*/}}}return titlemap;}//update-end--Author:xuelin  Date:20171205 for：TASK #2098 【excel问题】 Online 一对多导入失败--------------------/*** 获取这个名称对应的集合信息** @param excelCollection* @param collectionName* @return*/private ExcelCollectionParams getCollectionParams(List<ExcelCollectionParams> excelCollection, String collectionName) {for (ExcelCollectionParams excelCollectionParams : excelCollection) {if (collectionName.equals(excelCollectionParams.getExcelName())) {return excelCollectionParams;}}return null;}/*** Excel 导入 field 字段类型 Integer,Long,Double,Date,String,Boolean** @param inputstream* @param pojoClass* @param params* @return* @throws Exception*/public ExcelImportResult importExcelByIs(InputStream inputstream, Class<?> pojoClass, ImportParams params) throws Exception {if (LOGGER.isDebugEnabled()) {LOGGER.debug("Excel import start ,class is {}", pojoClass);}List<T> result = new ArrayList<T>();Workbook book = null;OPCPackage opc = null;boolean isXSSFWorkbook = false;if (!(inputstream.markSupported())) {inputstream = new PushbackInputStream(inputstream, 8);}//begin-------author:liusq------date:20210129-----for:-------poi3升级到4兼容改造工作【重要敏感修改点】--------//------poi4.x begin----
//		FileMagic fm = FileMagic.valueOf(FileMagic.prepareToCheckMagic(inputstream));
//		if(FileMagic.OLE2 == fm){
//			isXSSFWorkbook=false;
//		}// 将输入流内容复制到字节数组ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();byte[] buffer = new byte[1024];int bytesRead;while ((bytesRead = inputstream.read(buffer)) != -1) {byteArrayOutputStream.write(buffer, 0, bytesRead);}byte[] workbookData = byteArrayOutputStream.toByteArray();try (ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(workbookData)) {// 创建Workbook，不会直接关闭原始输入流book = WorkbookFactory.create(byteArrayInputStream);if (book instanceof XSSFWorkbook) {isXSSFWorkbook = true;opc = PackageHelper.open(new ByteArrayInputStream(workbookData));}} finally {try {//之前操作自动关闭了输入流，由于输入流内容复制到字节数组没有进行关闭，所以这里进行关闭inputstream.close();} catch (IOException e) {LOGGER.info("Error closing input stream: " + e.getMessage());}}
//        book = WorkbookFactory.create(nonClosingInputStream);
//        if (book instanceof XSSFWorkbook) {
//            isXSSFWorkbook = true;
//            opc = PackageHelper.open(inputstream);
//        }LOGGER.info("  >>>  poi3升级到4.0兼容改造工作, isXSSFWorkbook = " + isXSSFWorkbook);//end-------author:liusq------date:20210129-----for:-------poi3升级到4兼容改造工作【重要敏感修改点】--------//begin-------author:liusq------date:20210313-----for:-------多sheet导入改造点--------//获取导入文本的sheet数//update-begin-author:taoyan date:20211210 for:https://gitee.com/jeecg/jeecg-boot/issues/I45C32 导入空白sheet报错if (params.getSheetNum() == 0) {int sheetNum = book.getNumberOfSheets();if (sheetNum > 0) {params.setSheetNum(sheetNum);}}//update-end-author:taoyan date:20211210 for:https://gitee.com/jeecg/jeecg-boot/issues/I45C32 导入空白sheet报错//end-------author:liusq------date:20210313-----for:-------多sheet导入改造点--------createErrorCellStyle(book);Map<String, PictureData> pictures;//update-begin-author:liusq date:20220609 for:issues/I57UPC excel导入 ImportParams 中没有startSheetIndex参数for (int i = params.getStartSheetIndex(); i < params.getStartSheetIndex()+ params.getSheetNum(); i++) {//update-end-author:liusq date:20220609 for:issues/I57UPC excel导入 ImportParams 中没有startSheetIndex参数if (LOGGER.isDebugEnabled()) {LOGGER.debug(" start to read excel by is ,startTime is {}", System.currentTimeMillis());}//03版xls因为文件限制暂时无法识别内嵌图片//07版xlsx 此处以进行适配内嵌图片，内嵌和浮动自动识别返回if (isXSSFWorkbook) {pictures = PoiPublicUtil.getAllPictures(opc,(XSSFSheet) book.getSheetAt(i), (XSSFWorkbook) book);
//                pictures = PoiPublicUtil.getSheetPictrues07((XSSFSheet) book.getSheetAt(i), (XSSFWorkbook) book);} else {pictures = PoiPublicUtil.getSheetPictrues03((HSSFSheet) book.getSheetAt(i), (HSSFWorkbook) book);}if (LOGGER.isDebugEnabled()) {LOGGER.debug(" end to read excel by is ,endTime is {}", new Date().getTime());}result.addAll(importExcel(result, book.getSheetAt(i), pojoClass, params, pictures));if (LOGGER.isDebugEnabled()) {LOGGER.debug(" end to read excel list by pos ,endTime is {}", new Date().getTime());}}if (params.isNeedSave()) {saveThisExcel(params, pojoClass, isXSSFWorkbook, book);}return new ExcelImportResult(result, verfiyFail, book);}/**** @param is* @return* @throws IOException*/public static byte[] getBytes(InputStream is) throws IOException {ByteArrayOutputStream buffer = new ByteArrayOutputStream();int len;byte[] data = new byte[100000];while ((len = is.read(data, 0, data.length)) != -1) {buffer.write(data, 0, len);}buffer.flush();return buffer.toByteArray();}/*** 保存字段值(获取值,校验值,追加错误信息)** @param params* @param object* @param cell* @param excelParams* @param titleString* @param row* @throws Exception*/private void saveFieldValue(ImportParams params, Object object, Cell cell, Map<String, ExcelImportEntity> excelParams, String titleString, Row row) throws Exception {Object value = cellValueServer.getValue(params.getDataHanlder(), object, cell, excelParams, titleString);if (object instanceof Map) {if (params.getDataHanlder() != null) {params.getDataHanlder().setMapValue((Map) object, titleString, value);} else {((Map) object).put(titleString, value);}} else {ExcelVerifyHanlderResult verifyResult = verifyHandlerServer.verifyData(object, value, titleString, excelParams.get(titleString).getVerify(), params.getVerifyHanlder());if (verifyResult.isSuccess()) {setValues(excelParams.get(titleString), object, value);} else {Cell errorCell = row.createCell(row.getLastCellNum());errorCell.setCellValue(verifyResult.getMsg());errorCell.setCellStyle(errorCellStyle);verfiyFail = true;throw new ExcelImportException(ExcelImportEnum.VERIFY_ERROR);}}}/**** @param object* @param picId* @param excelParams* @param titleString* @param pictures* @param params* @throws Exception*/private void saveImage(Object object, String picId, Map<String, ExcelImportEntity> excelParams, String titleString, Map<String, PictureData> pictures, ImportParams params) throws Exception {if (pictures == null || pictures.get(picId) == null) {return;}PictureData image = pictures.get(picId);byte[] data = image.getData();String fileName = "pic" + Math.round(Math.random() * 100000000000L);fileName += "." + PoiPublicUtil.getFileExtendName(data);//update-beign-author:taoyan date:20200302 for:【多任务】online 专项集中问题 LOWCOD-159int saveType = excelParams.get(titleString).getSaveType();if (saveType == 1) {String path = PoiPublicUtil.getWebRootPath(getSaveUrl(excelParams.get(titleString), object));File savefile = new File(path);if (!savefile.exists()) {savefile.mkdirs();}savefile = new File(path + "/" + fileName);FileOutputStream fos = new FileOutputStream(savefile);fos.write(data);fos.close();setValues(excelParams.get(titleString), object, getSaveUrl(excelParams.get(titleString), object) + "/" + fileName);} else if (saveType == 2) {setValues(excelParams.get(titleString), object, data);} else {ImportFileServiceI importFileService = null;try {importFileService = ApplicationContextUtil.getContext().getBean(ImportFileServiceI.class);} catch (Exception e) {System.err.println(e.getMessage());}if (importFileService != null) {String dbPath = importFileService.doUpload(data);setValues(excelParams.get(titleString), object, dbPath);}}//update-end-author:taoyan date:20200302 for:【多任务】online 专项集中问题 LOWCOD-159}private void createErrorCellStyle(Workbook workbook) {errorCellStyle = workbook.createCellStyle();Font font = workbook.createFont();font.setColor(Font.COLOR_RED);errorCellStyle.setFont(font);}}

2、PoiPublicUtil代码

/*** Copyright 2013-2015 JEECG (jeecgos@163.com)**  Licensed under the Apache License, Version 2.0 (the "License");*  you may not use this file except in compliance with the License.*  You may obtain a copy of the License at**     http://www.apache.org/licenses/LICENSE-2.0**  Unless required by applicable law or agreed to in writing, software* distributed under the License is distributed on an "AS IS" BASIS,* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.* See the License for the specific language governing permissions and* limitations under the License.*/
package org.jeecgframework.poi.util;import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URISyntaxException;
import java.util.*;import javax.imageio.ImageIO;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;import org.apache.commons.lang3.StringUtils;
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.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackagePart;
import org.apache.poi.openxml4j.opc.PackagePartName;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.jdom2.Attribute;
import org.jdom2.Document;
import org.jdom2.Element;
import org.jdom2.JDOMException;
import org.jdom2.input.SAXBuilder;
import org.jeecgframework.poi.excel.annotation.Excel;
import org.jeecgframework.poi.excel.annotation.ExcelCollection;
import org.jeecgframework.poi.excel.annotation.ExcelEntity;
import org.jeecgframework.poi.excel.annotation.ExcelIgnore;
import org.jeecgframework.poi.excel.entity.vo.PoiBaseConstants;
import org.jeecgframework.poi.word.entity.WordImageEntity;
import org.jeecgframework.poi.word.entity.params.ExcelListEntity;
import org.jeecgframework.poi.xml.SAXParserHandler;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ClassUtils;
import org.xml.sax.SAXException;/*** AutoPoi 的公共基础类** @author JEECG* @date 2015年4月5日 上午12:59:22*/
public final class PoiPublicUtil {private static final Logger LOGGER = LoggerFactory.getLogger(PoiPublicUtil.class);private PoiPublicUtil() {}@SuppressWarnings({ "unchecked" })public static <K, V> Map<K, V> mapFor(Object... mapping) {Map<K, V> map = new HashMap<K, V>();for (int i = 0; i < mapping.length; i += 2) {map.put((K) mapping[i], (V) mapping[i + 1]);}return map;}/*** 彻底创建一个对象** @param clazz* @return*/public static Object createObject(Class<?> clazz, String targetId) {Object obj = null;Method setMethod;try {if (clazz.equals(Map.class)) {return new HashMap<String, Object>();}obj = clazz.newInstance();Field[] fields = getClassFields(clazz);for (Field field : fields) {if (isNotUserExcelUserThis(null, field, targetId)) {continue;}if (isCollection(field.getType())) {ExcelCollection collection = field.getAnnotation(ExcelCollection.class);setMethod = getMethod(field.getName(), clazz, field.getType());setMethod.invoke(obj, collection.type().newInstance());} else if (!isJavaClass(field)) {setMethod = getMethod(field.getName(), clazz, field.getType());setMethod.invoke(obj, createObject(field.getType(), targetId));}}} catch (Exception e) {LOGGER.error(e.getMessage(), e);throw new RuntimeException("创建对象异常");}return obj;}/*** 获取class的 包括父类的** @param clazz* @return*/public static Field[] getClassFields(Class<?> clazz) {List<Field> list = new ArrayList<Field>();Field[] fields;do {fields = clazz.getDeclaredFields();for (int i = 0; i < fields.length; i++) {list.add(fields[i]);}clazz = clazz.getSuperclass();} while (clazz != Object.class && clazz != null);return list.toArray(fields);}/*** @param photoByte* @return*/public static String getFileExtendName(byte[] photoByte) {String strFileExtendName = "JPG";if ((photoByte[0] == 71) && (photoByte[1] == 73) && (photoByte[2] == 70) && (photoByte[3] == 56) && ((photoByte[4] == 55) || (photoByte[4] == 57)) && (photoByte[5] == 97)) {strFileExtendName = "GIF";} else if ((photoByte[6] == 74) && (photoByte[7] == 70) && (photoByte[8] == 73) && (photoByte[9] == 70)) {strFileExtendName = "JPG";} else if ((photoByte[0] == 66) && (photoByte[1] == 77)) {strFileExtendName = "BMP";} else if ((photoByte[1] == 80) && (photoByte[2] == 78) && (photoByte[3] == 71)) {strFileExtendName = "PNG";}return strFileExtendName;}/*** 获取GET方法** @param name* @param pojoClass* @return* @throws Exception*/public static Method getMethod(String name, Class<?> pojoClass) throws Exception {StringBuffer getMethodName = new StringBuffer(PoiBaseConstants.GET);getMethodName.append(name.substring(0, 1).toUpperCase());getMethodName.append(name.substring(1));Method method = null;try {method = pojoClass.getMethod(getMethodName.toString(), new Class[] {});} catch (Exception e) {method = pojoClass.getMethod(getMethodName.toString().replace(PoiBaseConstants.GET, PoiBaseConstants.IS), new Class[] {});}return method;}/*** 获取SET方法** @param name* @param pojoClass* @param type* @return* @throws Exception*/public static Method getMethod(String name, Class<?> pojoClass, Class<?> type) throws Exception {StringBuffer getMethodName = new StringBuffer(PoiBaseConstants.SET);getMethodName.append(name.substring(0, 1).toUpperCase());getMethodName.append(name.substring(1));return pojoClass.getMethod(getMethodName.toString(), new Class[] { type });}//update-begin-author:taoyan date:20180615 for:TASK #2798 导入扩展方法，支持自定义导入字段转换规则/*** 获取get方法 通过EXCEL注解exportConvert判断是否支持值的转换* @param name* @param pojoClass* @param convert* @return* @throws Exception*/public static Method getMethod(String name, Class<?> pojoClass,boolean convert) throws Exception {StringBuffer getMethodName = new StringBuffer();if(convert){getMethodName.append(PoiBaseConstants.CONVERT);}getMethodName.append(PoiBaseConstants.GET);getMethodName.append(name.substring(0, 1).toUpperCase());getMethodName.append(name.substring(1));Method method = null;try {method = pojoClass.getMethod(getMethodName.toString(), new Class[] {});} catch (Exception e) {method = pojoClass.getMethod(getMethodName.toString().replace(PoiBaseConstants.GET, PoiBaseConstants.IS), new Class[] {});}return method;}/*** 获取set方法  通过EXCEL注解importConvert判断是否支持值的转换* @param name* @param pojoClass* @param type* @param convert* @return* @throws Exception*/public static Method getMethod(String name, Class<?> pojoClass, Class<?> type,boolean convert) throws Exception {StringBuffer setMethodName = new StringBuffer();if(convert){setMethodName.append(PoiBaseConstants.CONVERT);}setMethodName.append(PoiBaseConstants.SET);setMethodName.append(name.substring(0, 1).toUpperCase());setMethodName.append(name.substring(1));return pojoClass.getMethod(setMethodName.toString(), new Class[] { type });}//update-end-author:taoyan date:20180615 for:TASK #2798 导入扩展方法，支持自定义导入字段转换规则/*** 获取Excel2003图片** @param sheet*            当前sheet对象* @param workbook*            工作簿对象* @return Map key:图片单元格索引（1_1）String，value:图片流PictureData*/public static Map<String, PictureData> getSheetPictrues03(HSSFSheet sheet, HSSFWorkbook workbook) {Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();List<HSSFPictureData> pictures = workbook.getAllPictures();if (!pictures.isEmpty()) {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 picIndex = String.valueOf(anchor.getRow1()) + "_" + String.valueOf(anchor.getCol1());sheetIndexPicMap.put(picIndex, picData);}}return sheetIndexPicMap;} else {return null;}}/*** 获取Excel2007图片** @param sheet*            当前sheet对象* @param workbook*            工作簿对象* @return Map key:图片单元格索引（1_1）String，value:图片流PictureData*/public static Map<String, PictureData> getSheetPictrues07(XSSFSheet sheet, XSSFWorkbook workbook) {Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();for (POIXMLDocumentPart dr : sheet.getRelations()) {if (dr instanceof XSSFDrawing) {XSSFDrawing drawing = (XSSFDrawing) dr;List<XSSFShape> shapes = drawing.getShapes();for (XSSFShape shape : shapes) {XSSFPicture pic = (XSSFPicture) shape;XSSFClientAnchor anchor = pic.getPreferredSize();CTMarker ctMarker = anchor.getFrom();String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol();sheetIndexPicMap.put(picIndex, pic.getPictureData());}}}return sheetIndexPicMap;}public static Map<String, PictureData> getAllPictures(OPCPackage opc, XSSFSheet sheet, XSSFWorkbook workbook) throws Exception {Map<String, PictureData> sheetIndexPicMap = new HashMap<>();// 处理嵌入式图片List<PackagePart> parts = opc.getParts();sheetIndexPicMap.putAll(getEmbedPictures(parts));// 处理浮动式图片sheetIndexPicMap.putAll(getSheetPictrues07(sheet, workbook));return sheetIndexPicMap;}public static String getWebRootPath(String filePath) {try {String path = null;try {path = PoiPublicUtil.class.getClassLoader().getResource("").toURI().getPath();} catch (URISyntaxException e) {//e.printStackTrace();//update-begin-author:taoyan date:20211116 for: JAR包分离 发布出空指针 https://gitee.com/jeecg/jeecg-boot/issues/I4CMHK}catch (NullPointerException e) {path =  PoiPublicUtil.class.getProtectionDomain().getCodeSource().getLocation().getPath();}//update-end-author:taoyan date:20211116 for: JAR包分离 发布出空指针 https://gitee.com/jeecg/jeecg-boot/issues/I4CMHK//update-begin--Author:zhangdaihao  Date:20190424 for：解决springboot 启动模式，上传路径获取为空问题---------------------if (path == null || path == "") {//解决springboot 启动模式，上传路径获取为空问题path = ClassUtils.getDefaultClassLoader().getResource("").getPath();}//update-end--Author:zhangdaihao  Date:20190424 for：解决springboot 启动模式，上传路径获取为空问题----------------------LOGGER.debug("--- getWebRootPath ----filePath--- " + path);path = path.replace("WEB-INF/classes/", "");path = path.replace("file:/", "");LOGGER.debug("--- path---  " + path);LOGGER.debug("--- filePath---  " + filePath);return path + filePath;} catch (Exception e) {throw new RuntimeException(e);}}/*** 判断是不是集合的实现类** @param clazz* @return*/public static boolean isCollection(Class<?> clazz) {return Collection.class.isAssignableFrom(clazz);}/*** 是不是java基础类** @param field* @return*/public static boolean isJavaClass(Field field) {Class<?> fieldType = field.getType();boolean isBaseClass = false;if (fieldType.isArray()) {isBaseClass = false;} else if (fieldType.isPrimitive() || fieldType.getPackage() == null || fieldType.getPackage().getName().equals("java.lang") || fieldType.getPackage().getName().equals("java.math") || fieldType.getPackage().getName().equals("java.sql") || fieldType.getPackage().getName().equals("java.util")) {isBaseClass = true;}return isBaseClass;}/*** 判断是否不要在这个excel操作中** @param* @param field* @param targetId* @return*/public static boolean isNotUserExcelUserThis(List<String> exclusionsList, Field field, String targetId) {boolean boo = true;if (field.getAnnotation(ExcelIgnore.class) != null) {boo = true;} else if (boo && field.getAnnotation(ExcelCollection.class) != null && isUseInThis(field.getAnnotation(ExcelCollection.class).name(), targetId) && (exclusionsList == null || !exclusionsList.contains(field.getAnnotation(ExcelCollection.class).name()))) {boo = false;} else if (boo && field.getAnnotation(Excel.class) != null && isUseInThis(field.getAnnotation(Excel.class).name(), targetId) && (exclusionsList == null || !exclusionsList.contains(field.getAnnotation(Excel.class).name()))) {boo = false;} else if (boo && field.getAnnotation(ExcelEntity.class) != null && isUseInThis(field.getAnnotation(ExcelEntity.class).name(), targetId) && (exclusionsList == null || !exclusionsList.contains(field.getAnnotation(ExcelEntity.class).name()))) {boo = false;}return boo;}/*** 判断是不是使用** @param exportName* @param targetId* @return*/private static boolean isUseInThis(String exportName, String targetId) {return targetId == null || exportName.equals("") || exportName.indexOf("_") < 0 || exportName.indexOf(targetId) != -1;}private static Integer getImageType(String type) {if (type.equalsIgnoreCase("JPG") || type.equalsIgnoreCase("JPEG")) {return XWPFDocument.PICTURE_TYPE_JPEG;}if (type.equalsIgnoreCase("GIF")) {return XWPFDocument.PICTURE_TYPE_GIF;}if (type.equalsIgnoreCase("BMP")) {return XWPFDocument.PICTURE_TYPE_GIF;}if (type.equalsIgnoreCase("PNG")) {return XWPFDocument.PICTURE_TYPE_PNG;}return XWPFDocument.PICTURE_TYPE_JPEG;}/*** 返回流和图片类型** @Author JEECG* @date 2013-11-20* @param entity* @return (byte[]) isAndType[0],(Integer)isAndType[1]* @throws Exception*/public static Object[] getIsAndType(WordImageEntity entity) throws Exception {Object[] result = new Object[2];String type;if (entity.getType().equals(WordImageEntity.URL)) {ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();BufferedImage bufferImg;String path = Thread.currentThread().getContextClassLoader().getResource("").toURI().getPath() + entity.getUrl();path = path.replace("WEB-INF/classes/", "");path = path.replace("file:/", "");bufferImg = ImageIO.read(new File(path));//update-begin-author:taoYan date:20211203 for: Excel 导出图片的文件带小数点符号 导出报错 https://gitee.com/jeecg/jeecg-boot/issues/I4JNHRImageIO.write(bufferImg, entity.getUrl().substring(entity.getUrl().lastIndexOf(".") + 1, entity.getUrl().length()), byteArrayOut);//update-end-author:taoYan date:20211203 for: Excel 导出图片的文件带小数点符号 导出报错 https://gitee.com/jeecg/jeecg-boot/issues/I4JNHRresult[0] = byteArrayOut.toByteArray();type = entity.getUrl().split("/.")[entity.getUrl().split("/.").length - 1];} else {result[0] = entity.getData();type = PoiPublicUtil.getFileExtendName(entity.getData());}result[1] = getImageType(type);return result;}/*** 获取参数值** @param params* @param map* @return*/@SuppressWarnings("rawtypes")public static Object getParamsValue(String params, Object object) throws Exception {if (params.indexOf(".") != -1) {String[] paramsArr = params.split("\\.");return getValueDoWhile(object, paramsArr, 0);}if (object instanceof Map) {return ((Map) object).get(params);}return getMethod(params, object.getClass()).invoke(object, new Object[] {});}/*** 解析数据** @Author JEECG* @date 2013-11-16* @return*/public static Object getRealValue(String currentText, Map<String, Object> map) throws Exception {String params = "";while (currentText.indexOf("{{") != -1) {params = currentText.substring(currentText.indexOf("{{") + 2, currentText.indexOf("}}"));Object obj = getParamsValue(params.trim(), map);// 判断图片或者是集合// update-begin-author:taoyan date:20210914 for:autopoi模板导出，赋值的方法建议增加空判断或抛出异常说明。 /issues/3005if(obj==null){obj = "";}// update-end-author:taoyan date:20210914 for:autopoi模板导出，赋值的方法建议增加空判断或抛出异常说明。/issues/3005if (obj instanceof WordImageEntity || obj instanceof List || obj instanceof ExcelListEntity) {return obj;} else {currentText = currentText.replace("{{" + params + "}}", obj.toString());}}return currentText;}/*** 通过遍历过去对象值** @param object* @param paramsArr* @param index* @return* @throws Exception*/@SuppressWarnings("rawtypes")public static Object getValueDoWhile(Object object, String[] paramsArr, int index) throws Exception {if (object == null) {return "";}if (object instanceof WordImageEntity) {return object;}if (object instanceof Map) {object = ((Map) object).get(paramsArr[index]);} else {object = getMethod(paramsArr[index], object.getClass()).invoke(object, new Object[] {});}return (index == paramsArr.length - 1) ? (object == null ? "" : object) : getValueDoWhile(object, paramsArr, ++index);}/*** double to String 防止科学计数法** @param value* @return*/public static String doubleToString(Double value) {String temp = value.toString();if (temp.contains("E")) {BigDecimal bigDecimal = new BigDecimal(temp);temp = bigDecimal.toPlainString();}//---update-begin-----autor:scott------date:20191016-------for:excel导入数字类型，去掉后缀.0------return ExcelUtil.remove0Suffix(temp);//---update-end-----autor:scott------date:20191016-------for:excel导入数字类型，去掉后缀.0------}/*** 判断是否是数值类型* @param xclass* @return*/public static boolean isNumber(String xclass){if(xclass==null){return false;}String temp = xclass.toLowerCase();if(temp.indexOf("int")>=0 || temp.indexOf("double")>=0 || temp.indexOf("decimal")>=0){return true;}return false;}//update-begin---author:liusq  Date:20211217  for：[LOWCOD-2521]【autopoi】大数据导出方法【全局】----/*** 统一 key的获取规则* @param key* @param targetId* @date  2022年1月4号* @return*/public static String getValueByTargetId(String key, String targetId, String defalut) {if (StringUtils.isEmpty(targetId) || key.indexOf("_") < 0) {return key;}String[] arr = key.split(",");String[] tempArr;for (String str : arr) {tempArr = str.split("_");if (tempArr == null || tempArr.length < 2) {return defalut;}if (targetId.equals(tempArr[1])) {return tempArr[0];}}return defalut;}//update-end---author:liusq  Date:20211217  for：[LOWCOD-2521]【autopoi】大数据导出方法【全局】----/*** 将PackagePart转换为PictureData* @param packagePart* @param workbook* @return*/public static PictureData convertPackagePartToPictureData(PackagePart packagePart, XSSFWorkbook workbook) {if (packagePart == null) {throw new IllegalArgumentException("PackagePart cannot be null");}if (workbook == null) {throw new IllegalArgumentException("Workbook cannot be null");}try (InputStream inputStream = packagePart.getInputStream()) {// 从PackagePart读取数据byte[] data = IOUtils.toByteArray(inputStream);// 确定内容，创建PictureTypeString contentType = packagePart.getContentType();// 默认PNGint pictureType = XSSFWorkbook.PICTURE_TYPE_PNG;if (contentType.equals("image/jpeg")) {pictureType = XSSFWorkbook.PICTURE_TYPE_JPEG;} else if (contentType.equals("image/png")) {pictureType = XSSFWorkbook.PICTURE_TYPE_PNG;}// 将图片数据添加到工作簿int pictureIndex = workbook.addPicture(data, pictureType);return workbook.getAllPictures().get(pictureIndex);} catch (IOException e) {e.printStackTrace();return null;}}/*** 获取嵌入式图片* @param parts* @return* @throws JDOMException* @throws IOException* @throws ParserConfigurationException*/private static Map<String, PictureData> getEmbedPictures(List<PackagePart> parts) throws JDOMException, IOException, ParserConfigurationException, SAXException {Map<String, Set<String>> mapImg = new HashMap<>();Map<String, String> mapImgPath = new HashMap<>();Map<Integer, List<String>> dataMap = new HashMap<>();for (PackagePart part : parts) {
//            System.out.println(part.getPartName());PackagePartName partName = part.getPartName();String name = partName.getName();if ("/xl/cellimages.xml".equals(name)) {SAXBuilder builder = new SAXBuilder();// 获取文档Document doc = builder.build(part.getInputStream());// 获取根节点Element root = doc.getRootElement();List<Element> cellImageList = root.getChildren();for (Element imgEle : cellImageList) {Element xdrPic = imgEle.getChildren().get(0);Element xdrNvPicPr = xdrPic.getChildren().get(0);Element xdrBlipFill = xdrPic.getChildren().get(1);Element aBlip = xdrBlipFill.getChildren().get(0);Attribute attr = aBlip.getAttributes().get(0);String imgId = xdrNvPicPr.getChildren().get(0).getAttributeValue("name");String id = attr.getValue();if (mapImg.containsKey(id)) {mapImg.get(id).add(imgId);} else {Set<String> set = new HashSet<>();set.add(imgId);mapImg.put(id, set);}}}if ("/xl/_rels/cellimages.xml.rels".equals(name)) {SAXBuilder builder = new SAXBuilder();// 获取文档Document doc = builder.build(part.getInputStream());// 获取根节点Element root = doc.getRootElement();List<Element> relationshipList = root.getChildren();for (Element relationship : relationshipList) {String id = relationship.getAttributeValue("Id");String target = relationship.getAttributeValue("Target");mapImgPath.put(id, target);}}if (name.contains("/xl/worksheets/sheet")) {// 获取文档String sheetNoStr = name.replace("/xl/worksheets/sheet", "").replace(".xml", "");Integer sheetNo = Integer.valueOf(sheetNoStr) - 1;// 步骤1：创建SAXParserFactory实例SAXParserFactory factory = SAXParserFactory.newInstance();// 步骤2：创建SAXParser实例SAXParser saxParser = factory.newSAXParser();SAXParserHandler saxParserHandler = new SAXParserHandler();saxParser.parse(part.getInputStream(), saxParserHandler);List<String> rows = saxParserHandler.getRows();dataMap.put(sheetNo, rows);}}Map<String, String> imgMap = new HashMap<>();for (String id : mapImg.keySet()) {Set<String> imgIds = mapImg.get(id);String path = mapImgPath.get(id);for (String imgId : imgIds) {imgMap.put(imgId, path);}}for (Integer key : dataMap.keySet()) {List<String> rows = dataMap.get(key);for (int i = 0; i < rows.size(); i++) {String imgId = rows.get(i);String imgId_index = imgId.substring(0, imgId.indexOf(":"));String imgId_url = imgId.substring(imgId.indexOf(":")+1);if (imgMap.containsKey(imgId_url)) {rows.set(i, imgId_index+":"+imgMap.get(imgId_url));}}}Map<String, PictureData> map = new HashMap<>();for (Integer key : dataMap.keySet()) {List<String> pathList = dataMap.get(key);for (int i = 0; i < pathList.size(); i++) {String path = pathList.get(i);String path_index = path.substring(0, path.indexOf(":"));String path_url = path.substring(path.indexOf(":")+1);if (StringUtils.isNotEmpty(path_url)) {for (PackagePart part : parts) {PackagePartName partName = part.getPartName();String name = partName.getName();if (name.contains(path_url)) {//进行转换PictureData pictureData = convertPackagePartToPictureData(part,new XSSFWorkbook());map.put(path_index, pictureData);break;}}}}}return map;}}

3、SAXParserHandler代码

package org.jeecgframework.poi.xml;import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;import java.util.ArrayList;
import java.util.List;//xml解析
public class SAXParserHandler extends DefaultHandler {String value = null;List<String> rows = new ArrayList<>();int rowIndex = 0;int currentColumn = 0;public List<String> getRows() {return rows;}//用来标识解析开始@Overridepublic void startDocument() throws SAXException {super.startDocument();}//用来标识解析结束@Overridepublic void endDocument() throws SAXException {super.endDocument();}//解析xml元素@Overridepublic void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {super.startElement(uri, localName, qName, attributes);if (qName.equals("row")) {value = "";// 遇到新的行时，重置列号为0currentColumn = 0;} else if (qName.equals("c")) {// 获取列号String columnRef = attributes.getValue("r");if (columnRef != null) {currentColumn = parseColumnIndex(columnRef.replaceAll("[^A-Z]", ""));}}}@Overridepublic void endElement(String uri, String localName, String qName) throws SAXException {super.endElement(uri, localName, qName);if (qName.equals("c")) {if (value != null && value.contains("DISPIMG")) {value = value.substring(value.lastIndexOf("DISPIMG(")).replace("DISPIMG(\"", "");value = value.substring(0, value.indexOf("\""));
//                rows.add(rowIndex, value);rows.add(rowIndex + "_" + currentColumn + ":" + value);} else {
//                rows.add(rowIndex, null);rows.add(rowIndex + "_" + currentColumn + ":null");}value = "";}else if (qName.equals("row")) {rowIndex++;}}@Overridepublic void characters(char[] ch, int start, int length) throws SAXException {super.characters(ch, start, length);value += new String(ch, start, length);}// 解析列号private int parseColumnIndex(String columnRef) {int result = 0;for (int i = 0; i < columnRef.length(); i++) {result = result * 26 + (columnRef.charAt(i) - 'A' + 1);}return result - 1;}
}