[Bug] 使用模板写入，新文件，新模板，新数据，但是会有公式进入

[wxdChinese]

Search before asking

• I searched in the issues and found nothing similar.

Fesod version

1.3.0

JDK version

17

Operating system

windows11

Steps To Reproduce

public static importExcel(MultipartFile file) {
FastExcelUploadDataListener<QuestionExcel> listener = new FastExcelUploadDataListener<>();
List<QuestionFailedExcel> errorList = new ArrayList<>();
File importTempFile = null;
try {
importTempFile = File.createTempFile(UUID.randomUUID().toString(), ".xlsx");
            file.transferTo(importTempFile);
            FastExcel.read(importTempFile, QuestionExcel.class, listener)
                    .ignoreEmptyRow(false)
                    .sheet()
                    .doRead();
List<QuestionExcel> list = listener.getList();
errorList = new ArrayList<>(list);
} catch (Exception e) {
            log.error("===========", e);
        } finally {
            if (importTempFile != null) {
                importTempFile.delete();
            }
        }
File tempFile = null;
            try {
                // 创建输出文件
                tempFile = File.createTempFile(UUID.randomUUID().toString(), ".xlsx");
                // 加载模板
                try (InputStream failedTemplate = QuestionUtil.class.getResourceAsStream("/template/excel/exam/questionFailedTemplate.xlsx");) {

                    // 读取模板并写入数据（忽略注解中的表头）
                    FastExcel.write(tempFile)
                            .withTemplate(failedTemplate) // 使用模板
                            .sheet()// 默认第一个 sheet
                            .head(QuestionFailedExcel.class)
                            .needHead(false)
                            .doWrite(errorList);          // 写入数据列表

                } catch (Exception e) {
                    log.error("写入错误信息失败", e);
                    throw new ServiceException("写入错误信息失败");
                }

}

Current Behavior

FastExcel.write出来的文件会有FastExcel.read中的公式，如wps的嵌入图片

Expected Behavior

FastExcel.write中全是新的东西，不应该有read中的其他隐性的东西。

Anything else?

No response

Are you willing to submit a PR?

• I'm willing to submit a PR!

[wxdChinese]

                // 读取模板并写入数据（忽略注解中的表头）
                FastExcel.write(tempFile)
                        .filedCacheLocation(CacheLocationEnum.NONE)
                        .withTemplate(failedTemplate) // 使用模板
                        .sheet()// 默认第一个 sheet
                        .head(QuestionFailedExcel.class)
                        .needHead(false)
                        .doWrite(errorList);          // 写入数据列表

filedCacheLocation这个属性也无效

[bengbengbalabalabeng]

To help pinpoint the issue more accurately, could you please provide the following files or a minimal reproducible example?

• QuestionFailedExcel and QuestionExcel
• questionFailedTemplate.xlsx
• importTempFile.xlsx

[wxdChinese]

为了更准确地找出问题所在，请您提供以下文件或一个最小可复现示例？

• QuestionFailedExcel 和 QuestionExcel
• questionFailedTemplate.xlsx
• importTempFile.xlsx

@Data
public class QuestionExcel {
    @ExcelProperty(value = "题目", index = 0)
    @Size(max = 500, message = "题目长度不能超过{max}")
    @NotBlank(message = "题目不能为空")
    private String question;
    @ExcelProperty(value = "题目图片", index = 1)
    private String questionImg;
    @ExcelProperty(value = "类型", index = 2)
    @NotBlank(message = "类型不能为空")
    private String type;
    @ExcelProperty(value = "难易度", index = 3)
    @NotBlank(message = "难易度不能为空")
    private String difficulty;
    @ExcelProperty(value = "选项A（正确）", index = 4)
    @Size(max = 500, message = "选项A（正确）长度不能超过{max}")
    private String optionA;
    @ExcelProperty(value = "选项B（错误）", index = 5)
    @Size(max = 500, message = "选项B（错误）长度不能超过{max}")
    private String optionB;
    @ExcelProperty(value = "选项C", index = 6)
    @Size(max = 500, message = "选项C长度不能超过{max}")
    private String optionC;
    @ExcelProperty(value = "选项D", index = 7)
    @Size(max = 500, message = "选项D长度不能超过{max}")
    private String optionD;
    @ExcelProperty(value = "选项E", index = 8)
    @Size(max = 500, message = "选项E长度不能超过{max}")
    private String optionE;
    @ExcelProperty(value = "选项F", index = 9)
    @Size(max = 500, message = "选项F长度不能超过{max}")
    private String optionF;
    @ExcelProperty(value = "选项G", index = 10)
    @Size(max = 500, message = "选项G长度不能超过{max}")
    private String optionG;
    @ExcelProperty(value = "选项H", index = 11)
    @Size(max = 500, message = "选项H长度不能超过{max}")
    private String optionH;
    @ExcelProperty(value = "选项I", index = 12)
    @Size(max = 500, message = "选项I长度不能超过{max}")
    private String optionI;
    @ExcelProperty(value = "选项J", index = 13)
    @Size(max = 500, message = "选项J长度不能超过{max}")
    private String optionJ;
    @ExcelProperty(value = "选项K", index = 14)
    @Size(max = 500, message = "选项K长度不能超过{max}")
    private String optionK;
    @ExcelProperty(value = "选项L", index = 15)
    @Size(max = 500, message = "选项L长度不能超过{max}")
    private String optionL;
    @ExcelProperty(value = "选项M", index = 16)
    @Size(max = 500, message = "选项M长度不能超过{max}")
    private String optionM;
    @ExcelProperty(value = "选项N", index = 17)
    @Size(max = 500, message = "选项N长度不能超过{max}")
    private String optionN;
    @ExcelProperty(value = "选项O", index = 18)
    @Size(max = 500, message = "选项O长度不能超过{max}")
    private String optionO;
    @ExcelProperty(value = "选项P", index = 19)
    @Size(max = 500, message = "选项P长度不能超过{max}")
    private String optionP;
    @ExcelProperty(value = "选项Q", index = 20)
    @Size(max = 500, message = "选项Q长度不能超过{max}")
    private String optionQ;
    @ExcelProperty(value = "选项R", index = 21)
    @Size(max = 500, message = "选项R长度不能超过{max}")
    private String optionR;
    @ExcelProperty(value = "选项S", index = 22)
    @Size(max = 500, message = "选项S长度不能超过{max}")
    private String optionS;
    @ExcelProperty(value = "选项T", index = 23)
    @Size(max = 500, message = "选项T长度不能超过{max}")
    private String optionT;
    @ExcelProperty(value = "解析", index = 24)
    @Size(max = 500, message = "解析长度不能超过{max}")
    private String parse;
    @ExcelProperty(value = "答案", index = 25)
    @NotBlank(message = "答案不能为空")
    @Size(max = 20, message = "答案长度不能超过{max}")
    @Pattern(regexp = "^(?!.*([A-T]).*\\1)[A-T]{1,20}$", message = "答案格式不正确")
    private String answer;
    @ExcelProperty(value = "精选", index = 26)
    @NotBlank(message = "精选不能为空")
    private String featured;
}

@Data
public class QuestionFailedExcel extends QuestionExcel {
    @ExcelProperty("错误信息")
    private String failedMessage;
}

questionFailedTemplate.xlsx

questionTemplate.xlsx

[bengbengbalabalabeng]

Based on my current understanding:

Fesod has not yet implemented optimized handling for image cell types during reading. As a result, the value read from such cells is the image reference formula as a plain string (e.g., =DISPIMG("ID_*****", 1)).

In your example, during the conversion from QuestionExcel to QuestionFailedExcel,
the questionImg field still retains the original formula string. Therefore, the exported questionFailedTemplate will continue to contain this formula (in my opinion, this behavior is unrelated to filedCacheLocation).

If you want the final exported Excel file to display the original image instead of the formula, the only current approach is to manually extract the image using Apache POI during the reading phase,
and then reinsert the image during the writing phase using StringImageConverter.

If anything seems incorrect or if you have additional findings, feel free to add more details.

[wxdChinese]

根据我目前的理解：

Fesod 尚未实现对图像单元格类型在读取过程中的优化处理。因此，从这些单元格读取的值是图像引用公式的纯字符串（例如，=DISPIMG("ID_*****", 1)）。

QuestionExcel在你的例子中，从到 的转换过程中QuestionFailedExcel， 该questionImg字段仍然保留着原始公式字符串。因此，导出的数据questionFailedTemplate将继续包含此公式（在我看来，这种行为与 无关filedCacheLocation）。

如果您希望最终导出的 Excel 文件显示原始图像而不是公式，目前唯一的方法是在读取阶段使用 Apache POI 手动提取图像， 然后在写入阶段使用StringImageConverter.

如果发现任何错误或有其他发现，请随时补充细节。

我打断点查看并没有看见如（=DISPIMG("ID_*****", 1)）这样或其他的字符串公式，对应的字段上的值是null

[bengbengbalabalabeng]

Thanks for the feedback! I tested both embedded and floating images in WPS, but I wasn’t able to reproduce the issue on my side. Could you provide a minimal reproducible example (including the template file and sample data) or a screenshot of the behavior?

[wxdChinese]

感谢您的反馈！我在 WPS 中测试了嵌入式图片和浮动图片，但无法重现您遇到的问题。您能否提供一个最小可复现示例（包括模板文件和示例数据）或问题截图？

这是read的文件
questionTemplate.xlsx
这是write出来的文件

导入失败数据_1767750406079.xlsx

[wxdChinese]

感谢您的反馈！我在 WPS 中测试了嵌入式图片和浮动图片，但无法解决您遇到的问题。您能否提供一个最小的可复现示例（包括模板文件和样本数据）或问题截图？

这是读的文件 questionTemplate.xlsx 这是写出来的文件

导入失败数据_1767750406079.xlsx

我不是要读取图片，现在的问题是，wps的嵌入图片，我在写入的时候已经是新文件，新模板、新数据了，但是确还是有嵌入图片的公式，我打断点对应的数据又是null，这让我很困扰，总不能让我用Apache POI 重新再读取一次再删除公式把？

[bengbengbalabalabeng]

Below is a minimal reproducible example for reading the questionTemplate.xlsx file you provided.

Environment:

• Java version: 17
• FastExcel: 1.3.0

View full code

Model

@EqualsAndHashCode
@Data
public class QuestionExcel {

    @ExcelProperty(value = "题目", index = 0)
    private String question;

    @ExcelProperty(value = "类型", index = 1)
    private String type;

    @ExcelProperty(value = "难易度", index = 2)
    private String difficulty;

    @ExcelProperty(value = "选项A（正确）", index = 3)
    private String optionA;

    @ExcelProperty(value = "选项B（错误）", index = 4)
    private String optionB;

    @ExcelProperty(value = "选项D", index = 5)
    private String optionD;

    @ExcelProperty(value = "选项C", index = 6)
    private String optionC;

    @ExcelProperty(value = "选项E", index = 7)
    private String optionE;

    @ExcelProperty(value = "选项F", index = 8)
    private String optionF;

    @ExcelProperty(value = "选项G", index = 9)
    private String optionG;

    @ExcelProperty(value = "选项H", index = 10)
    private String optionH;

    @ExcelProperty(value = "选项I", index = 11)
    private String optionI;

    @ExcelProperty(value = "选项J", index = 12)
    private String optionJ;

    @ExcelProperty(value = "选项K", index = 13)
    private String optionK;

    @ExcelProperty(value = "选项L", index = 14)
    private String optionL;

    @ExcelProperty(value = "选项M", index = 15)
    private String optionM;

    @ExcelProperty(value = "选项N", index = 16)
    private String optionN;

    @ExcelProperty(value = "选项O", index = 17)
    private String optionO;

    @ExcelProperty(value = "选项P", index = 18)
    private String optionP;

    @ExcelProperty(value = "选项Q", index = 19)
    private String optionQ;

    @ExcelProperty(value = "选项R", index = 20)
    private String optionR;

    @ExcelProperty(value = "选项S", index = 21)
    private String optionS;

    @ExcelProperty(value = "选项T", index = 22)
    private String optionT;

    @ExcelProperty(value = "解析", index = 23)
    private String parse;

    @ExcelProperty(value = "正确答案", index = 24)
    private String answer;

    @ExcelProperty(value = "分数", index = 25)
    private String score;
}

Demo

class Issues773Demo {

    private static final String QA_TEMPLATE = "questionTemplate.xlsx";

    public static void main(String[] args) {
        FastExcelUploadDataListener listener = new FastExcelUploadDataListener();

        FastExcel.read(QA_TEMPLATE, QuestionExcel.class, listener)
                .ignoreEmptyRow(false)
                .sheet()
                .doRead();
        List<QuestionExcel> list = listener.getList();

        System.out.println("In Row-5, OptionC should contain an embedded image formula string: " + list.get(3).getOptionC());
        System.out.println("In Row-5, OptionE should be null (floating image): " + list.get(3).getOptionE());
        System.out.println("In Row-7, OptionE should be null (floating image): " + list.get(5).getOptionA());
        System.out.println("In Row-8, OptionA should contain an embedded image formula string: " + list.get(6).getOptionA());
        System.out.println("In Row-8, OptionD should be null (floating image): " + list.get(6).getOptionD());
        System.out.println("In Row-11, OptionA should be null (floating image): " + list.get(9).getOptionA());
        System.out.println("In Row-26, OptionA should contain an embedded image formula string: " + list.get(24).getOptionA());
        System.out.println("In Row-26, OptionD should contain an embedded image formula string: " + list.get(24).getOptionD());
    }

    private static class FastExcelUploadDataListener extends AnalysisEventListener<QuestionExcel> {
        private final List<QuestionExcel> list = new ArrayList<>();

        @Override
        public void invoke(QuestionExcel data, AnalysisContext context) {
            list.add(data);
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {}

        public List<QuestionExcel> getList() {
            return list;
        }
    }

}

Output:

• Embedded images are read as formula strings.
• Floating images result in NULL.

In this minimal example, the issue “all image fields are null” cannot be reproduced.

我打断点查看并没有看见如（=DISPIMG("ID_*****", 1)）这样或其他的字符串公式，对应的字段上的值是null

[wxdChinese]

谢谢，是我数错了行数，我将关闭这个问题，个人失误占用了你的时间非常抱歉