如何将公式写入excel二进制文件？

I have found one open source project with the basic excel functionality, write cell, write int to cell, change color, font and stuff. One thing is missing, and its add Formula, and I was unable to implement it, so if any one know how to do it it would be great.

我找到了一个开源项目,它具有基本的excel功能,写入单元格,将int写入单元格,更改颜色,字体和内容。有一件事是缺失的,它的添加公式,我无法实现它,所以如果任何人知道如何做它将是伟大的。

BIFF Formats:

internal sealed class BIFF
    {
        public const ushort DefaultColor = 0x7fff;

        public const ushort BOFRecord = 0x0209;
        public const ushort EOFRecord = 0x0A;

        public const ushort FontRecord = 0x0231;
        public const ushort FormatRecord = 0x001E;
        public const ushort LabelRecord = 0x0204;
        public const ushort WindowProtectRecord = 0x0019;
        public const ushort XFRecord = 0x0243;
        public const ushort HeaderRecord = 0x0014;
        public const ushort FooterRecord = 0x0015;
        public const ushort ExtendedRecord = 0x0243;
        public const ushort StyleRecord = 0x0293;
        public const ushort CodepageRecord = 0x0042;
        public const ushort NumberRecord = 0x0203;
        public const ushort ColumnInfoRecord = 0x007D;       

    }

example method for writing int and string

用于编写int和string的示例方法

private void WriteStringCell(BinaryWriter writer, CellInfo cell)
        {
            string value;
            if (cell.Value is string)
                value = (string)cell.Value;
            else
                value = cell.Value.ToString();
            if (value.Length > 255)
                value = value.Substring(0, 255);
            ushort[] clData = { BIFF.LabelRecord, 0, 0, 0, 0, 0 };
            byte[] plainText = Encoding.GetEncoding(CodePage).GetBytes(value);
            int iLen = plainText.Length;
            clData[1] = (ushort)(8 + iLen);
            clData[2] = (ushort)cell.Row;
            clData[3] = (ushort)cell.Column;
            clData[4] = (ushort)cell.FXIndex;
            clData[5] = (ushort)iLen;
            WriteUshortArray(writer, clData);
            writer.Write(plainText);
        }
private void WriteNumberCell(BinaryWriter writer, CellInfo cell)
        {
            double dValue = Convert.ToDouble(cell.Value);
            ushort[] clData = { BIFF.NumberRecord, 14, (ushort)cell.Row, (ushort)cell.Column, (ushort)cell.FXIndex };
            WriteUshortArray(writer, clData);
            writer.Write(dValue);
        }

you can download source code from link provided. So what I want is to have a method WriteFromulaCell that will write formula into excel. No success at all.

您可以从提供的链接下载源代码。所以我想要的是一个方法WriteFromulaCell,它将公式写入excel。根本没有成功。

Thanks in advance.

提前致谢。

3 个解决方案

#1

Try using NPOI, it has capability of adding formula, it's open source and free, or there is a also another open source xls project: excellibrary. Both project claiming full BIFF implementation.

尝试使用NPOI,它有添加公式的能力,它的开源和免费,或者还有另一个开源xls项目:excellibrary。这两个项目均声称完整的BIFF实施。

#2

Formulas in BIFF are represented as binary streams of tokens. If what you're trying to do is create a WriteFormulaCell() that can take a string like "=SUM(A3:Q47)" and turn it into a valid FORMULA record, that's a couple of months of work, or a few years of work, depending. Also, the bytecodes they used for the tokens changed a few times-- every version of BIFF had SOME change or other to the way they encoded this stuff.

BIFF中的公式表示为令牌的二进制流。如果您要做的是创建一个WriteFormulaCell(),它可以采用像“= SUM(A3:Q47)”这样的字符串并将其转换为有效的FORMULA记录,那就是几个月的工作,或者几年工作,取决于。此外,他们用于令牌的字节码改变了几次 - 每个版本的BIFF都有一些变化或者其他编码这些东西的方式。

If what you're trying to do is insert a specific, simple formula, in a specific place, fire up Excel, type the formula into the right spot on an empty spreadsheet, save as Excel 97, and go look at the BIFF file. You'll find a FORMULA record with the correctly-parsed token stream, which you can copy and insert into your own file. I'm ASSUMING that you're aware of the whole relative reference/absolute reference thing. References to other sheets will be by index, not by name. Defined names won't work at all with this trick. Other limitations may apply, your mileage may vary, no warranty implied, etc.

如果你要做的是在特定的地方插入一个特定的简单公式,启动Excel,在空电子表格的正确位置键入公式,保存为Excel 97,然后查看BIFF文件。您将找到具有正确解析的令牌流的FORMULA记录,您可以将其复制并插入到您自己的文件中。我认为你知道整个相对参考/绝对参考的东西。对其他工作表的引用将是索引,而不是名称。使用这个技巧,定义的名称根本不起作用。其他限制可能适用,您的里程可能会有所不同,不提供任何保证等。

#3

IIRC, an Excel "binary" file is really a ZIP file with XML code (at least this is true for the newer Excel formats). I have previously used Microsoft's library to read those files.

IIRC,一个Excel“二进制”文件实际上是一个带有XML代码的ZIP文件(至少对于较新的Excel格式也是如此)。我以前使用Microsoft的库来读取这些文件。

#1