Bug 65572 - Check for existing Sheet fails for names > 31 characters
Summary: Check for existing Sheet fails for names > 31 characters
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 5.0.0-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2021-09-15 09:52 UTC by Laura Henning
Modified: 2021-10-03 13:29 UTC (History)
0 users

MWE (1.11 KB, text/plain)
2021-09-15 09:52 UTC, Laura Henning

Note You need to log in before you can comment on or make changes to this bug.
Description Laura Henning 2021-09-15 09:52:09 UTC
Created attachment 38033 [details]

When creating a Sheet with a name that exceeds 31 characters, the check if that Sheet already exists in the Workbook fails and an IllegalArgumenException (The workbook already contains a sheet named ...) is thrown. When chopping the name to <=31 characters the exact same code works. See the attached MWE for an example.
Comment 1 PJ Fanning 2021-09-16 21:41:31 UTC
the current XSSFWorkbook has this code

        // YK: Mimic Excel and silently truncate sheet names longer than 31 characters
        if(sheetname.length() > 31) {
            sheetname = sheetname.substring(0, 31);

I'll try removing this
Comment 2 PJ Fanning 2021-09-16 21:46:21 UTC
Actually, I currently only have access to hosted Excel on Sharepoint and it rigidly enforces a 31 char limit on worksheet names - so no, I don't think it is a good idea to remove this in POI.

Maybe what we need is to throw an exception if someone tries to use a 32 or more char sheet name.
Comment 3 PJ Fanning 2021-09-16 22:01:16 UTC
the existing behaviour is clearly documented in the Javadocs.

     * Note that Excel allows sheet names up to 31 chars in length but other applications
     * (such as OpenOffice) allow more. Some versions of Excel crash with names longer than 31 chars,
     * others - truncate such names to 31 character.
     * <p>
     * POI's SpreadsheetAPI silently truncates the input argument to 31 characters.
     * Example:
     * <pre>{@code
     * Sheet sheet = workbook.createSheet("My very long sheet name which is longer than 31 chars"); // will be truncated
     * assert 31 == sheet.getSheetName().length();
     * assert "My very long sheet name which i" == sheet.getSheetName();
     * }</pre>
Comment 4 PJ Fanning 2021-09-16 22:03:29 UTC
Also this:

     * See {@link WorkbookUtil#createSafeSheetName(String nameProposal)}
     *      for a safe way to create valid names
Comment 5 PJ Fanning 2021-10-03 13:29:19 UTC
Closing as invalid