关于poi生成excl 读取JSON文件获取城市 报错的情况

[刘青峰]

java.lang.IllegalArgumentException: Invalid name: '涓滆帪锟?': name must be letter, digit, period, or underscore
at org.apache.poi.hssf.usermodel.HSSFName.validateName(HSSFName.java:206)
at org.apache.poi.hssf.usermodel.HSSFName.setNameName(HSSFName.java:132)
at com.kakarote.crm9.erp.crm.controller.CrmCustomerController.downloadExcel(CrmCustomerController.java:746)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.jfinal.aop.Invocation.invoke(Invocation.java:97)
at com.kakarote.crm9.erp.crm.common.CrmInterceptor.intercept(CrmInterceptor.java:94)
at com.jfinal.aop.Invocation.invoke(Invocation.java:91)

    public void downloadExcel() {
        List<Record> recordList = adminFieldService.queryAddField(CrmEnum.CRM_CUSTOMER);
        recordList.removeIf(record -> "file".equals(record.getStr("formType")) || "checkbox".equals(record.getStr("formType")) || "user".equals(record.getStr("formType")) || "structure".equals(record.getStr("formType")));
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet hidden = wb.createSheet("hidden");//1.创建隐藏的sheet页。
        HSSFSheet sheet = wb.createSheet("客户导入表");
        sheet.setForceFormulaRecalculation(true);
        sheet.setDefaultRowHeight((short)400);
        CellStyle textStyle = wb.createCellStyle();
        DataFormat format = wb.createDataFormat();
        textStyle.setDataFormat(format.getFormat("@"));
        for (int i = 0; i < recordList.size() + 2; i++) {
            sheet.setDefaultColumnStyle(i,textStyle);
            sheet.setColumnWidth(i,20*256);
        }
        HSSFRow titleRow = sheet.createRow(0);
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font font = wb.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 16);
        cellStyle.setFont(font);
        titleRow.createCell(0).setCellValue("客户导入模板(*)为必填项");
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        titleRow.getCell(0).setCellStyle(cellStyle);
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, recordList.size() + 1);
        sheet.addMergedRegion(region);
        try {
            HSSFRow row = sheet.createRow(1);
            for (int i = 0; i < recordList.size(); i++) {
                Record record = recordList.get(i);
                //省市区需特殊处理
                if ("map_address".equals(record.getStr("field_name"))) {
                    HSSFCell cell1 = row.createCell(i);
                    cell1.setCellValue("省");
                    HSSFCell cell2 = row.createCell(i + 1);
                    cell2.setCellValue("市");
                    HSSFCell cell3 = row.createCell(i + 2);
                    cell3.setCellValue("区");
                    HSSFSheet hideSheet = wb.createSheet("address");
                    wb.setSheetHidden(wb.getSheetIndex(hideSheet), true);
                    int rowId = 0;
                    // 设置第一行，存省的信息
                    Row provinceRow = hideSheet.createRow(rowId++);
                    provinceRow.createCell(0).setCellValue("省列表");
                    List<String> provinceList = Db.query("select city_name from 72crm_crm_area where parent_id = ?", 100000);
                    for (int x = 0; x < provinceList.size(); x++) {
                        Cell provinceCell = provinceRow.createCell(x + 1);
                        provinceCell.setCellValue(provinceList.get(x));
                    }
                    // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
                    Map<String, List<String>> areaMap = CrmExcelUtil.getAreaMap();
                    for (String key : areaMap.keySet()) {
                        List<String> son = areaMap.get(key);
                        Row subRow = hideSheet.createRow(rowId++);
                        subRow.createCell(0).setCellValue(key);
                        for (int x = 0; x < son.size(); x++) {
                            Cell cell = subRow.createCell(x + 1);
                            cell.setCellValue(son.get(x));
                        }
                        // 添加名称管理器
                        String range = CrmExcelUtil.getRange(1, rowId, son.size());
                        Name name = wb.createName();
                        // key不可重复
                        System.out.println("6 - zxm1 = "+new String(key.getBytes("UTF-8"),"GBK"));
                        System.out.println("6 - zxm2= "+new String(key.getBytes("GBK"),"UTF-8"));
                         System.out.println("6 - zxm3 = "+ new String(key.getBytes("ISO8859_1"),"UTF-8"));
                         key=new String(key.getBytes("GBK"),"UTF-8").toString();
                        name.setNameName(key); //key编码不对导致错误
                        String formula = "address!" + range;
                        name.setRefersToFormula(formula);
                    }
                    // 省级下拉框
                    CellRangeAddressList provRangeAddressList = new CellRangeAddressList(2, Integer.MAX_VALUE, i, i);
                    String[] arr = provinceList.toArray(new String[]{});
                    DVConstraint provConstraint = DVConstraint.createExplicitListConstraint(arr);
                    HSSFDataValidation provinceDataValidation = new HSSFDataValidation(provRangeAddressList, provConstraint);
                    provinceDataValidation.createErrorBox("error", "请选择正确的省份");
                    sheet.addValidationData(provinceDataValidation);
                    //市 区下拉框
                    for (int x = 2; x < 10000; x++) {
                        CrmExcelUtil.setDataValidation(CrmExcelUtil.getCorrespondingLabel(i + 1), sheet, x, i + 1);
                        CrmExcelUtil.setDataValidation(CrmExcelUtil.getCorrespondingLabel(i + 2), sheet, x, i + 2);
                    }
                } else {
                    HSSFCell cell = row.createCell(i);
                    if (record.getInt("is_null") == 1) {
                        cell.setCellValue(record.getStr("name") + "(*)");
                    } else {
                        cell.setCellValue(record.getStr("name"));
                    }
                    String[] setting = record.get("setting");
                    if (setting != null && setting.length != 0) {
                        CellRangeAddressList regions = new CellRangeAddressList(2, Integer.MAX_VALUE, i, i);
                        DVConstraint constraint = DVConstraint.createExplicitListConstraint(setting);
                        HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
                        sheet.addValidationData(dataValidation);
                    }
                }
            }
            HttpServletResponse response = getResponse();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("UTF-8");
            //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
            response.setHeader("Content-Disposition", "attachment;filename=customer_import.xls");
            wb.write(response.getOutputStream());

        } catch (Exception e) {
            Log.getLog(getClass()).error("error", e);
        } finally {
            try {
                wb.close();
            } catch (IOException ex) {
                ex.printStackTrace();
            }

        }
        renderNull();
    }

报错的源头就是这个获取key这里

// 添加名称管理器
String range = CrmExcelUtil.getRange(1, rowId, son.size());
Name name = wb.createName();
name.setNameName(key); //key编码不对导致错误
String formula = "address!" + range;
name.setRefersToFormula(formula);

key不管我怎么转编码还是乱码
不知道什么情况导致 请各位大佬帮忙看看

[你看见了吗°]

你要考虑下,乱码时哪里来的,数据库获取出来就乱码,或者在业务端操作时变成乱码,还是前端传送过来是乱码,知道哪里变成乱码,在处理会好点

[刘青峰]

引用 1 楼 来自底层程序猿的仰望 的回复:

你要考虑下,乱码时哪里来的,数据库获取出来就乱码,或者在业务端操作时变成乱码,还是前端传送过来是乱码,知道哪里变成乱码,在处理会好点

JSON是中文，读取过来就变成乱码了 key=new String(key.getBytes("GBK"),"UTF-8").toString(); 这里转换成中文还是乱码

[刘青峰]

System.out.println(Charset.defaultCharset()); cn.hutool.json.JSONObject readJSONObject= JSONUtil.readJSONObject(new File(PathKit.getRootClassPath()+"/config/area.json"), Charset.forName("UTF-8")); return readJSONObject.toBean(Map.class);