Apache POI:强制单元数据类型为无公式? [英] Apache POI: Force celldata type to be NO formula?

问题描述

添加来自不同来源的纯文本，我注意到 Apache POI (3.17) 自动假定以 = 开头的字符串被解释为公式.这通常很好，但我如何确保在这种特殊情况下，单元格不是一个公式?

Adding pure text from a different source, i noticed Apache POI (3.17) automatically assumes a String starting with = is interpreted as a formula. This is usually fine, but how can i make sure that in this special case, the cell is NOT a formula?

我假设在添加以 = 开头的字符串后，我可以告诉 Apache POI 类型是 CellType.STRING:

I assumed after adding the String starting with =, i can just tell Apache POI the type is CellType.STRING:

cell.setCellType(CellType.STRING);   

但这并没有产生预期的结果，字符串在输出 .xlsx 文件(使用 LibreOffice)中仍作为公式​​读取.目前，我正在检查每个添加的字符串是否以 = 开头并将其替换为 '= 但我更愿意避免所有这些检查.例如我目前如何处理它:

But this does not yield the expected result, the String is still read as a formula in the output .xlsx file (using LibreOffice). Currently, i'm checking if each added String starts with a = and replace it with '= but i would much prefer to avoid all those checks. Example how i currently handle it:

strOut = in_CSV(i,j);
strOut = strOut.startsWith("=") ? "'"+strOut : strOut;

那么有没有更好的方法来确保我的输出不是 Apache POI 中的论坛?

So is there a better way to make sure my output is NOT a forumula in Apache POI?

推荐答案

将以 = 开头的 String 解释为公式由 Excel 或 Calc 完成 之后 打开文件.因此，apache poi 只能在文件中使用与 Excel 用于将以 = 开头的字符串标记为 不是一个公式.此标记称为引号前缀".它看起来像 Excel 编辑器栏中的撇号，但实际上并不是单元格内容的一部分.

The interpreting a String starting with = as a formula is done by Excel or Calc after opening the file. So apache poi can only prevent this if it uses the same markers in the file which Excel would use to mark strings starting with = as not a formula. This mark is called "quote prefix". It looks like an apostrophe in editor bar of Excel but is not really part of the cell content.

因此，简单地在单元格内容前放置一个撇号将是错误的做法.这在 Excel 中导入文本 (CSV) 时有效，因为前导撇号将自动解释为引号前缀.但是在 *.xls 或 *.xlsx 文件中，此自动不会在所有情况下正常工作.因此，我们需要真正将 CellFormat.QuotePrefix 属性 设置为 Excel 在编辑器栏中键入单元格内容的前导撇号后所做的.

So simply putting an apostrophe in front of the cell content will be the wrong approach. This works while importing text (CSV) in Excel because there the leading apostrophe will automatically interpreted as quote prefix. But in a *.xls or *.xlsx file this automatic will not work properly in all cases. So there we need really setting CellFormat.QuotePrefix Property as Excel would do after typing a leading apostrophe to cell content in editor bar.

这可以使用 apache poi 创建一个 CellStyle 已设置 setQuotePrefixed(true).

This can be done using apache poi by creating a CellStyle which has set setQuotePrefixed(true).

这篇关于Apache POI:强制单元数据类型为无公式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！