批注添加多了会报错

[hbhghong]

你好，这边尝试添加批注，大概1万条数，每行2个批注是正常的。但是写5万就报错了，报错是：
java.nio.charset.MalformedInputException: Input length = 1
请问是什么原因呢

[wangguanquan]

这个异常是因为有异常字符造成的，你先使用如下代码找出是哪些字符影响的，运行后将输出贴出来看一下

List<Comment> commentList = new ArrayList<>();
// TODO 正常放入5万个批注
int i = 0;
try (ExtBufferedWriter writer = new ExtBufferedWriter(Files.newBufferedWriter(Paths.get("1.txt")))) {
    // 循环写5w个comment内容

    for (Comment comment : commentList) {
        if (StringUtil.isNotEmpty(comment.getTitle())) {
            writer.escapeWrite(comment.getTitle());
        }
        if (StringUtil.isNotEmpty(comment.getValue())) {
            writer.escapeWrite(comment.getValue());
        }
        i++;
    }
} catch (MalformedInputException e) {
    Comment errComment = commentList.get(i);
    if (StringUtil.isNotEmpty(errComment.getTitle())) {
        System.out.println("title: " + Base64.getEncoder().encodeToString(errComment.getTitle().getBytes(StandardCharsets.UTF_8)));
    }
    if (StringUtil.isNotEmpty(errComment.getValue())) {
        System.out.println("value: " + Base64.getEncoder().encodeToString(errComment.getValue().getBytes(StandardCharsets.UTF_8)));
    }
} catch (IOException e) {
    e.printStackTrace();
}

[hbhghong]

但是我的批注都是固定的，只有前100条复制了50倍。我还尝试批注内容全部统一字符"A"，但是也是超出一定量出现了问题。而且看日志是执行到了最后一部写入才报错

     protected void resetBlockData() {
         super.resetBlockData();
         Styles styles = workbook.getStyles();
         for (int i = 0, position = rowBlock.position(); i < position; i++) {
             Row row = rowBlock.get(i);
             int rowIndex = row.getIndex();
             Cell[] cells = row.getCells();
             int index = 2;
             Cell cell = cells[index];
             int style = styles.getStyleByIndex(cell.xf);
             style = styles.modifyFill(style, new Fill(Color.RED));
             cell.xf = styles.of(style);
             createComments().addComment(new String(int2Col(index + 1)) + (rowIndex + 1), new Comment("", "A"));
         }
  }

[wangguanquan]

好的，我测试一下，目前批注功能只能说能用未到好用的程序，Comments会收集所有批注最后一次性落盘所以批注过多时会出来OOM

[wangguanquan]

我尝试了一下并没有发现问题，你可以将版本升级到最新的0.5.20再尝试一下，无论是提前设置批注还是按你的代码动态添加都没发现问题

// 提前设置批注

EmptySheet sheet = new EmptySheet();
Comments comments = sheet.createComments();
for (int i = 0; i < 50000; i++) {
    comments.addComment("A" + (i + 1), new Comment("a", "A"));
}
new Workbook().addSheet(sheet).writeTo(defaultTestPath.resolve("5k comments.xlsx"));

// 动态设置批注

new Workbook()
    .addSheet(new MyListSheet<Integer>().setData((i, a) -> i < 50000 ? Arrays.asList(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) : null))
    .writeTo(defaultTestPath.resolve("5k comments.xlsx"));

public static class MyListSheet<T> extends ListSheet<T> {
    @Override
    protected void resetBlockData() {
    super.resetBlockData();
    Styles styles = workbook.getStyles();
    for (int i = 0, position = rowBlock.position(); i < position; i++) {
        org.ttzero.excel.entity.Row row = rowBlock.get(i);
        int rowIndex = row.getIndex();
        Cell[] cells = row.getCells();
        int index = 0;
        Cell cell = cells[index];
        int style = styles.getStyleByIndex(cell.xf);
        style = styles.modifyFill(style, new Fill(Color.RED));
        cell.xf = styles.of(style);
        createComments().addComment(new String(int2Col(index + 1)) + (rowIndex + 1), new Comment(null, Integer.toString(row.index)));
    }
}

[hbhghong]

刚更新最新包，写入批注”A“不会报错
但是：
第3列批注是：信托登记系统产品编码应唯一;该信托项目未填报《业务分类信息》、《产品特征》、《联系人信息》、《信托费用信息》、《信托规模项目》、《特色领域规模》;长度不能小于21位
第5列批注是：必填
这样插入1万条测试不会报错，5万条会。然后用你给到的代码测试

List<Comment> commentList = new ArrayList<>();
        for (int i = 0; i < 50000; i++) {
            Comment comment = new Comment();
            comment.setValue("信托登记系统产品编码应唯一;该信托项目未填报《业务分类信息》、《产品特征》、《联系人信息》、《信托费用信息》、《信托规模项目》、《特色领域规模》;长度不能小于21位");
            Comment comment2 = new Comment();
            comment2.setValue("必填");
            commentList.add(comment);
            commentList.add(comment2);
        }
        // TODO 正常放入5万个批注
        int i = 0;
        try (ExtBufferedWriter writer = new ExtBufferedWriter(Files.newBufferedWriter(Paths.get("D:\\1.txt")))) {
            // 循环写5w个comment内容

            for (Comment comment : commentList) {
                if (StringUtil.isNotEmpty(comment.getTitle())) {
                    writer.escapeWrite(comment.getTitle());
                }
                if (StringUtil.isNotEmpty(comment.getValue())) {
                    writer.escapeWrite(comment.getValue());
                }
                i++;
            }
        } catch (MalformedInputException e) {
            Comment errComment = commentList.get(i);
            if (StringUtil.isNotEmpty(errComment.getTitle())) {
                System.out.println("title: " + Base64.getEncoder().encodeToString(errComment.getTitle().getBytes(StandardCharsets.UTF_8)));
            }
            if (StringUtil.isNotEmpty(errComment.getValue())) {
                System.out.println("value: " + Base64.getEncoder().encodeToString(errComment.getValue().getBytes(StandardCharsets.UTF_8)));
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

这样也没报错
我用的是模板写入

[wangguanquan]

感谢你的反馈，已发现问题，Comments类173行writer.write(100 + i);改为writer.writeInt(100 + i);，有空的话可以fork本项目将修改提交PR，临时的你可以使用如下代码替换原本的Comments类来解决问题。

new Workbook().addSheet(new TemplateSheet(Paths.get(".template测试.xlsx")) {
    @Override
    public Comments createComments() {
        if (comments == null) {
            comments = new MyComments(); // 使用自定义Comments
        }
        return comments;
    }
}).writeTo(Paths.get("大量批注测试.xlsx"));

// 自定义Comments修复异常字符
publi class MyComments extends Comments {
    @Override
    protected void vml(Path root) throws IOException {
        Path parent = root.resolve("drawings");
        if (!Files.exists(parent)) {
            FileUtil.mkdir(parent);
        }

        try (ExtBufferedWriter writer = new ExtBufferedWriter(
            Files.newBufferedWriter(parent.resolve("vmlDrawing" + id + Const.Suffix.VML)))) {
            writer.write("<xml xmlns:v=\"urn:schemas-microsoft-com:vml\"");
            writer.write(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
            writer.write(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
            writer.write(" <o:shapelayout v:ext=\"edit\">");
            writer.write("  <o:idmap v:ext=\"edit\" data=\"1\"/>");
            writer.write(" </o:shapelayout>");
            writer.write(" <v:shapetype id=\"_x0000_t202\" coordsize=\"21600,21600\" o:spt=\"202\"");
            writer.write("  path=\"m,l,21600r21600,l21600,xe\">");
            writer.write("  <v:stroke joinstyle=\"miter\"/>");
            writer.write("  <v:path gradientshapeok=\"t\" o:connecttype=\"rect\"/>");
            writer.write(" </v:shapetype>");
            int i = 1;
            for (C c : commentList) {
                long cr = ExcelReader.coordinateToLong(c.ref);
                writer.write(" <v:shape id=\"_x0000_s");writer.writeInt(100 + i); // <-- 修改点
                writer.write("\" type=\"#_x0000_t202\" style='width:" + (c.width != null ? c.width : 100.8D) + "pt;height:" + (c.height != null ? c.height : 60.6D) + " pt;z-index:");
                writer.writeInt(i++);
                writer.write(";  visibility:hidden' fillcolor=\"#ffffe1\" o:insetmode=\"auto\">");
                writer.write("  <v:fill color2=\"#ffffe1\"/>");
                writer.write("  <v:shadow on=\"t\" color=\"black\" obscured=\"t\"/>");
                writer.write("  <v:path o:connecttype=\"none\"/>");
                writer.write("  <v:textbox style='mso-direction-alt:auto'>");
                writer.write("   <div style='text-align:left'></div>");
                writer.write("  </v:textbox>");
                writer.write("  <x:ClientData ObjectType=\"Note\">");
                writer.write("   <x:MoveWithCells/>");
                writer.write("   <x:SizeWithCells/>");
                writer.write("   <x:Anchor/>");
                writer.write("   <x:AutoFill>False</x:AutoFill>");
                writer.write("   <x:Row>");writer.write((cr >> 16) - 1);writer.write("</x:Row>");
                writer.write("   <x:Column>");writer.write((cr & 0x7FFF) - 1);writer.write("</x:Column>");
                writer.write("  </x:ClientData>");
                writer.write(" </v:shape>");
            }
            writer.write("</xml>");
        }
    }
}

[hbhghong]

用了自定义comments后，批注没有写入了

[wangguanquan]

试试这样，先调用createComments创建Comments。WorksheetWriter有点问题如果未提前创建Comments则不会绑定批注与工作表的关系

TemplateSheet sheet = new TemplateSheet(Paths.get(".template测试.xlsx")) {
    @Override
    public Comments createComments() {
        if (comments == null) {
            comments = new MyComments(); // 使用自定义Comments
        }
        return comments;
    }

    @Override
    protected void resetBlockData() {
        // TODO 原有逻辑
    }
};
// 预先创建Comments
sheet.createComments();

new Workbook().addSheet(sheet).writeTo(Paths.get("大量批注测试.xlsx"));

[wangguanquan]

拉取 feature/comments_optimize 分支并打包，此分支已修复上面的问题