8
问题描述
使用easyexcel最新版本3.2.1，读取xlsx文件，报警告“Currently excel cannot get style information, but it doesn't affect the data analysis.You can try to save the file with office again or ignore the current error.”， 读取后dataFormatData是null，对于excel中内容是日期的单元格（示例2023/5/5），无法获得其单元格的dataFormatData，从而无法将读取到的数字转换成日期字符串。
读取的代码 DynamicReadListener dynamicReadListener = DynamicReadListener.init();
EasyExcel.read(file.getInputStream(), dynamicReadListener)
.readDefaultReturn(ReadDefaultReturnEnum.READ_CELL_DATA)
.extraRead(CellExtraTypeEnum.COMMENT)
.extraRead(CellExtraTypeEnum.MERGE)
.extraRead(CellExtraTypeEnum.HYPERLINK)
.sheet()
.doRead(); @Slf4j
public class DynamicReadListener extends AnalysisEventListener<Map<Integer, ReadCellData<?>>> {
private Map<Integer, String> headMap = new HashMap<>();
private final List<Map<String, String>> dataList = new ArrayList<>();
/**
* 实例化 Listener
* @return Listener
*/
public static DynamicReadListener init() {
return new DynamicReadListener();
}
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
this.headMap = headMap;
log.info("解析得到的表头数据：{}", headMap);
}
@Override
public void invoke(Map<Integer, ReadCellData<?>> data, AnalysisContext context) {
ReadRowHolder readRowHolder = context.readRowHolder();
Map<Integer, Cell> cellMap = readRowHolder.getCellMap();
for (int i = 0; i < cellMap.size(); i++) {
Cell cell = cellMap.get(i);
}
Map<Integer, String> integerStringMap = headMap;
HashMap<String, String> map = new HashMap<>();
for (int i = 0; i < integerStringMap.size(); i++) {
ReadCellData<?> cellData = data.get(i);
String s;
if (cellData != null && cellData.getType() == CellDataTypeEnum.NUMBER) {
s = NumberToTextConverter.toText(cellData.getNumberValue().doubleValue());
if (cellData.getDataFormatData().getFormat().equals("yyyy/m/d")) {
s = DateUtils.format(HSSFDateUtil.getJavaDate(cellData.getNumberValue().doubleValue(),
false, null), "yyyy-MM-dd");
}
} else {
s = cellData == null ? "" : cellData.toString();
}
map.put(integerStringMap.get(i), s);
}
dataList.add(map);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
public List<Map<String, String>> getDataList() {
return dataList;
}
} 2023-05-05 17:18:19.626 WARN 35144 --- [nio-8888-exec-1] c.a.excel.analysis.v07.XlsxSaxAnalyser : Currently excel cannot get style information, but it doesn't affect the data analysis.You can try to save the file with office again or ignore the current error.
java.lang.RuntimeException: not supported setting property http://xml.org/sax/properties/lexical-handler
at org.apache.xmlbeans.impl.store.Locale$SaxLoader.<init>(Locale.java:3397) ~[xmlbeans-3.1.0.jar:na]
at org.apache.xmlbeans.impl.store.Locale$XmlReaderSaxLoader.<init>(Locale.java:3087) ~[xmlbeans-3.1.0.jar:na]
at org.apache.xmlbeans.impl.store.Locale.getSaxLoader(Locale.java:3072) ~[xmlbeans-3.1.0.jar:na]
at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1272) ~[xmlbeans-3.1.0.jar:na]
at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1259) ~[xmlbeans-3.1.0.jar:na]
at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.parse(SchemaTypeLoaderBase.java:345) ~[xmlbeans-3.1.0.jar:na]
at org.openxmlformats.schemas.spreadsheetml.x2006.main.StyleSheetDocument$Factory.parse(Unknown Source) ~[poi-ooxml-schemas-4.1.2.jar:4.1.2]
at org.apache.poi.xssf.model.StylesTable.readFrom(StylesTable.java:190) ~[poi-ooxml-4.1.2.jar:4.1.2]
at org.apache.poi.xssf.model.StylesTable.<init>(StylesTable.java:138) ~[poi-ooxml-4.1.2.jar:4.1.2]
at org.apache.poi.xssf.eventusermodel.XSSFReader.getStylesTable(XSSFReader.java:127) ~[poi-ooxml-4.1.2.jar:4.1.2]
at com.alibaba.excel.analysis.v07.XlsxSaxAnalyser.setStylesTable(XlsxSaxAnalyser.java:149) ~[easyexcel-core-3.2.1.jar:na]
at com.alibaba.excel.analysis.v07.XlsxSaxAnalyser.<init>(XlsxSaxAnalyser.java:106) ~[easyexcel-core-3.2.1.jar:na]
at com.alibaba.excel.analysis.ExcelAnalyserImpl.choiceExcelExecutor(ExcelAnalyserImpl.java:103) ~[easyexcel-core-3.2.1.jar:na]
at com.alibaba.excel.analysis.ExcelAnalyserImpl.<init>(ExcelAnalyserImpl.java:55) ~[easyexcel-core-3.2.1.jar:na]
at com.alibaba.excel.ExcelReader.<init>(ExcelReader.java:30) ~[easyexcel-core-3.2.1.jar:na]
at com.alibaba.excel.read.builder.ExcelReaderBuilder.build(ExcelReaderBuilder.java:214) ~[easyexcel-core-3.2.1.jar:na]
at com.alibaba.excel.read.builder.ExcelReaderBuilder.sheet(ExcelReaderBuilder.java:251) ~[easyexcel-core-3.2.1.jar:na]
at com.alibaba.excel.read.builder.ExcelReaderBuilder.sheet(ExcelReaderBuilder.java:239) ~[easyexcel-core-3.2.1.jar:na]
at com.dgr.test.domain.app.device.service.impl.DgrDeviceServiceImpl.importDevice(DgrDeviceServiceImpl.java:586) ~[main/:na]
at com.dgr.test.domain.app.device.service.impl.DgrDeviceServiceImpl$$FastClassBySpringCGLIB$$9eee1748.invoke(<generated>) ~[main/:na]
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.3.5.jar:5.3.5]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779) ~[spring-aop-5.3.5.jar:5.3.5]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.5.jar:5.3.5]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.5.jar:5.3.5]
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-5.3.5.jar:5.3.5]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) ~[spring-tx-5.3.5.jar:5.3.5]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-5.3.5.jar:5.3.5]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.5.jar:5.3.5]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.5.jar:5.3.5]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692) ~[spring-aop-5.3.5.jar:5.3.5]
at com.dgr.test.domain.app.device.service.impl.DgrDeviceServiceImpl$$EnhancerBySpringCGLIB$$66031afe.importDevice(<generated>) ~[main/:na]
at com.dgr.test.domain.adapter.device.controller.DeviceController.importDevice(DeviceController.java:95) ~[main/:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:197) ~[spring-web-5.3.5.jar:5.3.5]
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:141) ~[spring-web-5.3.5.jar:5.3.5]
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106) ~[spring-webmvc-5.3.5.jar:5.3.5]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:894) ~[spring-webmvc-5.3.5.jar:5.3.5]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808) ~[spring-webmvc-5.3.5.jar:5.3.5]
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.3.5.jar:5.3.5]
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1060) ~[spring-webmvc-5.3.5.jar:5.3.5]
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:962) ~[spring-webmvc-5.3.5.jar:5.3.5]
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.3.5.jar:5.3.5]
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909) ~[spring-webmvc-5.3.5.jar:5.3.5]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:652) ~[tomcat-embed-core-9.0.44.jar:4.0.FR]
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.3.5.jar:5.3.5]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:733) ~[tomcat-embed-core-9.0.44.jar:4.0.FR]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.44.jar:9.0.44]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.3.5.jar:5.3.5]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.3.5.jar:5.3.5]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.3.5.jar:5.3.5]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.3.5.jar:5.3.5]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:93) ~[spring-boot-actuator-2.4.4.jar:2.4.4]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.3.5.jar:5.3.5]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.3.5.jar:5.3.5]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.3.5.jar:5.3.5]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:893) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1707) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[na:na]
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[na:na]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-9.0.44.jar:9.0.44]
at java.base/java.lang.Thread.run(Thread.java:834) ~[na:na]
Caused by: org.xml.sax.SAXNotSupportedException: not supported setting property http://xml.org/sax/properties/lexical-handler
at org.gjt.xpp.sax2.Driver.setProperty(Driver.java:204) ~[pull-parser-2.jar:na]
at org.apache.xmlbeans.impl.store.Locale$SaxLoader.<init>(Locale.java:3388) ~[xmlbeans-3.1.0.jar:na]
... 85 common frames omitted