excel 导出 The maximum length of cell contents (text) is 32767 characters Excel单元格最大存储长度32767个字符，超长会报

标签：return int excel 32767 报错 ._ 长度 public

excel 导出 The maximum length of cell contents (text) is 32767 characters

导出excel功能，报错。错误日志提示：:The maximum length of cell contents (text) is 32767 characters

调查后，poi会有单元格最大长度校验超过32767会报错。

需求调研：
调研发现，excel和csv文件本身存在单元格有32767的最大字符限制。

解决方案：
解决办法是程序不报错，但是生成excel或者csv 会自动截取单元格最大32767 characters。

1、通过java反射机制，设置单元格最大校验限制为Integer.MAX_VALUE(2147483647)。
导出生成excel文件之前调用设置单元格最大限制方法。

public void resetCellMaxTextLength() {
        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();
            }
        }
    }

2、修改源码，修改源码文件枚举类，同样也是设置最大校验限制为Integer.MAX_VALUE

package org.apache.poi.ss;
import org.apache.poi.ss.util.CellReference;
/**
 * @description ：Excel枚举
 */
public enum SpreadsheetVersion {
    /**
     * excel 枚举
     **/
    EXCEL97(65536, 256, 30, 3, 4000, 32767),
    EXCEL2007(1048576, 16384, 255, 2147483647, 64000, Integer.MAX_VALUE);
    private final int _maxRows;
    private final int _maxColumns;
    private final int _maxFunctionArgs;
    private final int _maxCondFormats;
    private final int _maxCellStyles;
    private final int _maxTextLength;
<span class="hljs-keyword">private</span> SpreadsheetVersion(int maxRows, int maxColumns, int maxFunctionArgs, int maxCondFormats, int maxCellStyles, int maxText) {
    <span class="hljs-keyword">this</span>._maxRows = maxRows;
    <span class="hljs-keyword">this</span>._maxColumns = maxColumns;
    <span class="hljs-keyword">this</span>._maxFunctionArgs = maxFunctionArgs;
    <span class="hljs-keyword">this</span>._maxCondFormats = maxCondFormats;
    <span class="hljs-keyword">this</span>._maxCellStyles = maxCellStyles;
    <span class="hljs-keyword">this</span>._maxTextLength = maxText;
}

<span class="hljs-keyword">public</span> int getMaxRows() {
    <span class="hljs-keyword">return</span> <span class="hljs-keyword">this</span>._maxRows;
}

<span class="hljs-keyword">public</span> int getLastRowIndex() {
    <span class="hljs-keyword">return</span> <span class="hljs-keyword">this</span>._maxRows - <span class="hljs-number">1</span>;
}

<span class="hljs-keyword">public</span> int getMaxColumns() {
    <span class="hljs-keyword">return</span> <span class="hljs-keyword">this</span>._maxColumns;
}

<span class="hljs-keyword">public</span> int getLastColumnIndex() {
    <span class="hljs-keyword">return</span> <span class="hljs-keyword">this</span>._maxColumns - <span class="hljs-number">1</span>;
}

<span class="hljs-keyword">public</span> int getMaxFunctionArgs() {
    <span class="hljs-keyword">return</span> <span class="hljs-keyword">this</span>._maxFunctionArgs;
}

<span class="hljs-keyword">public</span> int getMaxConditionalFormats() {
    <span class="hljs-keyword">return</span> <span class="hljs-keyword">this</span>._maxCondFormats;
}

<span class="hljs-keyword">public</span> int getMaxCellStyles() {
    <span class="hljs-keyword">return</span> <span class="hljs-keyword">this</span>._maxCellStyles;
}

<span class="hljs-keyword">public</span> String getLastColumnName() {
    <span class="hljs-keyword">return</span> CellReference.convertNumToColString(<span class="hljs-keyword">this</span>.getLastColumnIndex());
}

<span class="hljs-keyword">public</span> int getMaxTextLength() {
    <span class="hljs-keyword">return</span> <span class="hljs-keyword">this</span>._maxTextLength;
}

}

32767 是 2的15次方减1. 即可容纳不超过2的15次方的字符. 但请注意在单元格中只可显示1024个字符. 可在编辑栏中显示全部的32767个字符.
以前2003每个单元格只可容纳255个字符. 即256-1个. 此后版本的excel做了改进. 才能容纳更多的字符.

原文链接：https://www.cnblogs.com/stubborn-dude/p/17552839.html

标签：return,int,excel,32767,报错,._,长度,public
From： https://www.cnblogs.com/sunny3158/p/17758747.html