How to Write Formula into excel Binary file?

Question

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.

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

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.

Thanks in advance.

Answer 1

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.

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.