出现问题:
使用jxls工具的transformXLS方法报错
System.out.println("---------------准备导出-------------------");
XLSTransformer former = new XLSTransformer();
InputStream is = this.getClass().getClassLoader().getResourceAsStream(STUDENT_HEALTH_TEMPLATE_FILE_NAME);
HSSFWorkbook workBook = (HSSFWorkbook) former.transformXLS(is, branchMeg.getColumns());
报错日志如下
Caused by: java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Cell.getCellType()I
at net.sf.jxls.parser.CellParser.parseCellFormula(CellParser.java:68) ~[jxls-core-1.0.6.jar!/:?]
at net.sf.jxls.util.SheetHelper.findFormulasInRow(SheetHelper.java:69) ~[jxls-core-1.0.6.jar!/:?]
at net.sf.jxls.util.SheetHelper.findFormulas(SheetHelper.java:29) ~[jxls-core-1.0.6.jar!/:?]
at net.sf.jxls.util.SheetHelper.findFormulas(SheetHelper.java:21) ~[jxls-core-1.0.6.jar!/:?]
问题背景
使用jxls工具类用来模板导出excel文件
原因分析:
随着需求不断升级,项目中也使用了poi并且poi的版本升到了4.1.2,但是jxls的版本没有升级,也主要是因为jxls-core的版本维护停在了1.0.6,追踪jxls的源码不难发现,使用了poi的Cell的CELL_TYPE_STRING属性,而我这个版本的poi已经去掉了这个属性,导致找不到而报错
解决方案
重写jxls这个类 :net.sf.jxls.parser.CellParser.parseCellFormula
将org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING改成org.apache.poi.ss.usermodel.CellType.STRING即可,如图
另外还有一些方法需要重写
net.sf.jxls.util 包下的Util类
这里摘取某一部分
moveCell方法
private static void moveCell(org.apache.poi.ss.usermodel.Cell srcCell, org.apache.poi.ss.usermodel.Cell destCell) {
destCell.setCellStyle(srcCell.getCellStyle());
switch (srcCell.getCellType()) {
case STRING:
destCell.setCellValue(srcCell.getRichStringCellValue());
break;
case NUMERIC:
destCell.setCellValue(srcCell.getNumericCellValue());
break;
case BLANK:
destCell.setCellType(BLANK);
break;
case BOOLEAN:
destCell.setCellValue(srcCell.getBooleanCellValue());
break;
case ERROR:
destCell.setCellErrorValue(srcCell.getErrorCellValue());
break;
case FORMULA:
break;
default:
break;
}
srcCell.setCellType(BLANK);
}
copyCell方法
public static void copyCell(org.apache.poi.ss.usermodel.Cell oldCell, org.apache.poi.ss.usermodel.Cell newCell,
boolean copyStyle) {
if (copyStyle) {
newCell.setCellStyle(oldCell.getCellStyle());
copyConditionalFormat(oldCell, newCell);
}
switch (oldCell.getCellType()) {
case STRING:
newCell.setCellValue(oldCell.getRichStringCellValue());
break;
case NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case BLANK:
newCell.setCellType(BLANK);
break;
case BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
default:
break;
}
}
copyCell方法
public static void copyCell(org.apache.poi.ss.usermodel.Cell oldCell, org.apache.poi.ss.usermodel.Cell newCell,
boolean copyStyle, String expressionToReplace,
String expressionReplacement) {
if (copyStyle) {
newCell.setCellStyle(oldCell.getCellStyle());
copyConditionalFormat(oldCell, newCell);
}
switch (oldCell.getCellType()) {
case STRING:
String oldValue = oldCell.getRichStringCellValue().getString();
String newValue = replaceExpressions(oldValue, expressionToReplace, expressionReplacement);
newCell.setCellValue(newCell.getSheet().getWorkbook().getCreationHelper().createRichTextString(newValue));
break;
case NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case BLANK:
newCell.setCellType(BLANK);
break;
case BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
default:
break;
}
}
getOrCreateCell方法
public static Cell getOrCreateCell(Sheet poiSheet, Integer rowNum, Integer cellNum) {
org.apache.poi.ss.usermodel.Row row = poiSheet.getRow(rowNum.intValue());
if (row == null) {
row = poiSheet.createRow(rowNum.intValue());
}
Cell cell = row.getCell(cellNum.intValue(), Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
return cell;
}
还有
net.sf.jxls.parser.Cell
net.sf.jxls.transformer.CellTransformer
net.sf.jxls.transformer.XLSTransformer
都是改CellType就不一一举例了
引用一下这篇大佬的文章
https://blog.csdn.net/sxc112400/article/details/136541186
或者下载文章绑定的资源,内部是一个jxls重写包,不用自己重写代码,直接下载源码下来放进项目里即可使用