使用Java过滤excel中sheetName的特殊字符和长度限制

需求

• 在开发中遇到一个文件导出错误，原因是由于XSSFWorkbook workbook = new XSSFWorkbook();生成excel文件的时候没有采取过滤措施，选择的是抛出异常，因此报错，而且由于数据库中存在数据，用户的选项中已经删除了，因此出现了那个时间段内导出的文件一直失败的现象。好在通过查询日志找到准确的错误位置，并且写了方法解决这个情况，现在写文章记录此事件。
• 这个名称在编写的时候存在这种情况
  

具体实现代码

下面展示 代码片.

public class Tmp {
    public static void main(String[] args) {
        Tmp tmp = new Tmp();
        String sheetName = "'Hello*/'\\n'[]**12aldajslslad'a'l'rvdv'";
        String sheetName1 = "";
        System.out.println(sheetName.length());
        //String ans = tmp.validateSheetName(sheetName);
        String ans = validateSheetName(sheetName);
        sheetName1 = validateSheetName(sheetName1);
        //System.out.println(sheetName);
        System.out.println(sheetName1+sheetName1.length());
        System.out.println(ans);
        System.out.println(ans.length());
    }

    public static String validateSheetName(String sheetName) {
        String result = "";
        if (sheetName == null) {
            throw new IllegalArgumentException("sheetName must not be null");
        } else {
            if (sheetName.length() > 31) {
                sheetName = sheetName.substring(0, 31);
            }
            String newStr[] = sheetName.split("");
            int len = sheetName.length();
            if (len >= 1 && len <= 31) {
                int i = 0;
                while (i < len) {
                    if ("*".equals(newStr[i])
                            || "/".equals(newStr[i])
                            || ":".equals(newStr[i])
                            || "?".equals(newStr[i])
                            || "[".equals(newStr[i])
                            || "]".equals(newStr[i])
                            || "\\".equals(newStr[i])
                    ) {
                        newStr[i] = "";
                    }
                    ++i;
                }
                if (sheetName.charAt(0) == '\'') {
                    newStr[0] = "";
                }
                if (sheetName.charAt(len - 1) == '\'') {
                    newStr[len - 1] = "";
                }
            }

            for (int i = 0; i < newStr.length; i++) {
                result += newStr[i];
            }
        }
        return result;
    }

}

正则表达式实现版本具体实现代码

public class Tmp {
    public static void main(String[] args) {
        Tmp tmp = new Tmp();
        String sheetName = "'Hello*/'\\n'[]**12aldajslslad'a'l'rvdv'";
        String sheetName1 = "";
        System.out.println(sheetName.length());
        //String ans = tmp.validateSheetName(sheetName);
        String ans = validateSheetName(sheetName);
        sheetName1 = validateSheetName(sheetName1);
        //System.out.println(sheetName);
        System.out.println(sheetName1+sheetName1.length());
        System.out.println(ans);
        System.out.println(ans.length());
    }

    public static String validateSheetName(String sheetName) {
        if (StringUtils.isBlank(sheetName)) {
            throw new IllegalArgumentException("sheetName must not be null");
        } else {
            if (sheetName.length() > 31) {
                sheetName = sheetName.substring(0, 31);
            }
            sheetName=sheetName.replaceAll("[\\[*\\]*/*\n?*:*\\\\]","");
            sheetName=sheetName.replaceAll("^'*|'*$","");
        }
        return sheetName;
    }

}

• 需要注意的是，正则表达式不可以写成一条，那样将会照成头或尾存在单引号过滤不完全的问题，原因是存在以下这种情况
  String nameTmp2 = "’’'H??:e’ll’o*///’\\n’[][][[\\\\]][][[*’";