Handling Long Text in Excel Export: Overcoming the 32767 Character Limit

1. Error Message

java.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)
	at com.alibaba.excel.write.executor.AbstractExcelWriteExecutor.converterAndSet(AbstractExcelWriteExecutor.java:86)
	at com.alibaba.excel.write.executor.ExcelWriteAddExecutor.addJavaObjectToExcel(ExcelWriteAddExecutor.java:174)
	at com.alibaba.excel.write.executor.ExcelWriteAddExecutor.addOneRowOfDataToExcel(ExcelWriteAddExecutor.java:82)
	at com.alibaba.excel.write.executor.ExcelWriteAddExecutor.add(ExcelWriteAddExecutor.java:58)
	at com.alibaba.excel.write.ExcelBuilderImpl.addContent(ExcelBuilderImpl.java:59)
	at com.alibaba.excel.ExcelWriter.write(ExcelWriter.java:70)
	at com.alibaba.excel.ExcelWriter.write(ExcelWriter.java:47)
	at com.alibaba.excel.write.builder.ExcelWriterSheetBuilder.doWrite(ExcelWriterSheetBuilder.java:62)

2. Root Cause

The Apache POI library defines a limit of 32767 characters for cell content in both Excel97 and Excel2007 formats. This restriction is embedded within the SpreadsheetVersion enum:

/**
 * 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);

3. Solution

To resolve the issue, adjust the maximum allowed text length for cell values. This can be done either manually during each export operation or programmatically at application startup using CommandLineRunner. Below is an implementation that modifies the setting upon application boot:

@Component
public class StartupConfig implements CommandLineRunner {
    @Override
    public void run(String... args) throws Exception {
        SpreadsheetVersion version = SpreadsheetVersion.EXCEL2007;
        Field maxLengthField = version.getClass().getDeclaredField("_maxTextLength");
        maxLengthField.setAccessible(true);
        maxLengthField.set(version, Integer.MAX_VALUE);
    }
}