Skip to content

Escape character in the formula #546

New issue
New issue
Closed
Closed
Escape character in the formula#546
Labels
needs more infoThis issue can't reproduce, need more info
@jimwei

Description

@jimwei
jimwei
opened on Dec 25, 2019

我在第一个sheet中第一列设置formula, formula的内容是第二个sheet开始的其他sheet中的cell内容
比如, formula是"'Sheet2'!G31",
设置完后, 我读取了一下, 是正确的, 但是保存后, 通过excel 打开发现一些cell设置的formula不正确,
比如, 第三, 四行的formula的内容和第二行设置的formula变成一样的了.

Activity

xuri

xuri commented on Dec 25, 2019

@xuri
xuri
on Dec 25, 2019
Member

Hi @jimwei, please follow by issue template provide code and steps to reproduce the issue.

xuri
added
needs more infoThis issue can't reproduce, need more info
on Dec 25, 2019
jimwei

jimwei commented on Dec 27, 2019

@jimwei
jimwei
on Dec 27, 2019 · edited by jimwei
Author

我跟踪了下代码, formula中无法正确显示单引号的原因如下:

  1. excel中对sheet的命名有一定的规范要求, 比如,包含空格, 或者以数字开始或结束, 在formula中会用单引号引起来, 就像formula是"'1-1第一季度统计'!G31",
  2. excelize在保存时, 会调用xml.marshal方法(sheet.go, line108), 这会对单引号进行转义,会将单引号转为"&GetRows returns empty list for non-empty sheet #39;", 所以, 保存后, excel无法打开,
    方案:
  3. 需要将xlsxF结构中的Content字段的xml tag定义为innerxml(xmlWorksheet.go, line378), 如下
    Content string xml:",innerxml"
  4. 这样处理后, 保存, excel就可以正常打开了,
jimwei
closed this as completedon Dec 27, 2019
xuri
added a commit that references this issue on Dec 28, 2019

Fix #547 and #546, add default overlay element for the chart

8b960ee
xuri
changed the title [-]formula设置错误[/-] [+]Escape character in the formula[/+] on Dec 28, 2019
xuri

xuri commented on Feb 15, 2020

@xuri
xuri
on Feb 15, 2020 · edited by xuri
Member

Hi @jimwei, I rolled back changes to the formula content data type definition in recent commit 52f1eee. Ref: #578. I have tested the formulas with escape characters in the XML and it works well. Could you tell me which version of Office Excel you are using?

nullfy
added a commit that references this issue on Oct 23, 2020

Fix qax-os#547 and qax-os#546, add default overlay element for the chart

f8b5810
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    needs more infoThis issue can't reproduce, need more info

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @jimwei@xuri

        Issue actions
          Escape character in the formula · Issue #546 · qax-os/excelize