The maximum length of cell contents (text) is 32,767 charactersExcel导出单元格长度超长-Toy模板网

这篇具有很好参考价值的文章主要介绍了The maximum length of cell contents (text) is 32,767 charactersExcel导出单元格长度超长。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

一、问题描述

导出excel接口报错，错误信息如下：

ava.lang.IllegalArgumentException: The maximum length of cell contents (text) is 32767 characters

at org.apache.poi.ss.usermodel.CellBase.checkLength(CellBase.java:309)

at org.apache.poi.ss.usermodel.CellBase.setCellValue(CellBase.java:290)

二、定位问题

从错误信息查看源码，定位到 CellBase 类 checkLength 方法 309 行

private void checkLength(String value) {
  // value值超过了 32767
  if(value.length() > getSpreadsheetVersion().getMaxTextLength()){
    final String message = String.format(Locale.ROOT,
            "The maximum length of cell contents (text) is %d characters",
            getSpreadsheetVersion().getMaxTextLength());
    throw new IllegalArgumentException(message);
  }
}

getSpreadsheetVersion().getMaxTextLength() 枚举源码类

public enum SpreadsheetVersion {
    /**
     * Excel97 format aka BIFF8
     * <ul>
     * <li>The total number of available rows is 64k (2^16)</li>
     * <li>The total number of available columns is 256 (2^8)</li>
     * <li>The maximum number of arguments to a function is 30</li>
     * <li>Number of conditional format conditions on a cell is 3</li>
     * <li>Number of cell styles is 4000</li>
     * <li>Length of text cell contents is 32767</li>
     * </ul>
     */
    EXCEL97(0x10000, 0x0100, 30, 3, 4000, 32767),
 
    /**
     * Excel2007
     *
     * <ul>
     * <li>The total number of available rows is 1M (2^20)</li>
     * <li>The total number of available columns is 16K (2^14)</li>
     * <li>The maximum number of arguments to a function is 255</li>
     * <li>Number of conditional format conditions on a cell is unlimited
     * (actually limited by available memory in Excel)</li>
     * <li>Number of cell styles is 64000</li>
     * <li>Length of text cell contents is 32767</li>
     * <ul>
     */
    EXCEL2007(0x100000, 0x4000, 255, Integer.MAX_VALUE, 64000, 32767);
 
    private final int _maxRows;
    private final int _maxColumns;
    private final int _maxFunctionArgs;
    private final int _maxCondFormats;
    private final int _maxCellStyles;
    private final int _maxTextLength;
 
    private SpreadsheetVersion(int maxRows, int maxColumns, int maxFunctionArgs, int maxCondFormats, int maxCellStyles, int maxText) {
        _maxRows = maxRows;
        _maxColumns = maxColumns;
        _maxFunctionArgs = maxFunctionArgs;
        _maxCondFormats = maxCondFormats;
        _maxCellStyles = maxCellStyles;
        _maxTextLength = maxText;
    }
 
    /**
     * @return the maximum number of usable rows in each spreadsheet
     */
    public int getMaxRows() {
        return _maxRows;
    }
 
    /**
     * @return the last (maximum) valid row index, equals to <code> getMaxRows() - 1 </code>
     */
    public int getLastRowIndex() {
        return _maxRows - 1;
    }
 
    /**
     * @return the maximum number of usable columns in each spreadsheet
     */
    public int getMaxColumns() {
        return _maxColumns;
    }
 
    /**
     * @return the last (maximum) valid column index, equals to <code> getMaxColumns() - 1 </code>
     */
    public int getLastColumnIndex() {
        return _maxColumns - 1;
    }
 
    /**
     * @return the maximum number arguments that can be passed to a multi-arg function (e.g. COUNTIF)
     */
    public int getMaxFunctionArgs() {
        return _maxFunctionArgs;
    }
 
    /**
     * @return the maximum number of conditional format conditions on a cell
     */
    public int getMaxConditionalFormats() {
        return _maxCondFormats;
    }
 
    /**
     * @return the maximum number of cell styles per spreadsheet
     */
    public int getMaxCellStyles() {
        return _maxCellStyles;
    }
 
    /**
     *
     * @return the last valid column index in a ALPHA-26 representation
     *  (<code>IV</code> or <code>XFD</code>).
     */
    public String getLastColumnName() {
        return CellReference.convertNumToColString(getLastColumnIndex());
    }
 
    /**
     * @return the maximum length of a text cell
     */
    public int getMaxTextLength() {
        return _maxTextLength;
    }
}

三、解决方案

1. 通过反射修改

通过，反射去修改 SpreadsheetVersion 的 _maxTextLength 变量即可。

( 注：这里仅仅只是为了突破cell的内容限制而作修改，理论上，修改 final 变量肯定是不合理的 )

/**
  * 初始化 cell 内容长度
  * 	   cell 原本内容长度限制 32767  现修改为Integer.MAX_VALUE
*/
public static void initCellMaxTextLength() {
		SpreadsheetVersion excel2007 = SpreadsheetVersion.EXCEL2007;
		if (Integer.MAX_VALUE != excel2007.getMaxTextLength()) {
			Field field;
			try {
				field = excel2007.getClass().getDeclaredField("_maxTextLength");
				field.setAccessible(true);
			    field.set(excel2007,Integer.MAX_VALUE);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
 
// 导出数据前调用
initCellMaxTextLength();

2. 在自己的项目下新建SpreadsheetVersion类，包名要和源码的保持一致

the maximum length of cell contents (text) is 32,767 characters,java,java,开发语言

然后修改EXCEL2007

EXCEL2007(0x100000, 0x4000, 255, Integer.MAX_VALUE, 64000, Integer.MAX_VALUE);

文章来源地址https://www.toymoban.com/news/detail-588238.html

到了这里，关于The maximum length of cell contents (text) is 32,767 charactersExcel导出单元格长度超长的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！