ansaurus

Question

BIFF5 Excel - Pointing to XF and FONT records from LABEL record

Answer 1

+1 A:

For bold, the Font record needs a value of 0x02BC at offset 6. For future reference, in case you want to add further font styling subsequently, italic requires a bitmask of 0x0002 at offset 2, while strikethrough requires a bitmask of 0x0008 at offset 2. Offset 4 points to the colour index, if you want to change font colour, while offset 8 identifies superscript/subscript, and offset 10 identifies underline type. Offsets 11, 12 and 14 identify the font family, character set and size of the font name, followed by the font name itself.

You can find full details of all the options at http://msdn.microsoft.com/en-us/library/cc313154(v=office.12).aspx

EDIT

Looking at your XF record, you're writing it as a BIFF5 record rather than the later version BIFF8. The link that I referenced with the worked example is actually a BIFF8 example.

BIFF8 would be:- header = pack("vv", 0x00E0, 0x0014); data = pack("vvvCCCCVVv", fontIndex, numberFormatIndex, 0xFFF5, alignment, rotation, identation+shrinkToFit+textDirection, attributes, border1, border2, fg_and_bg_colours);

BIFF5 is:- header = pack("vv", 0x00E0, 0x0010); data = pack("vvvvvvvv", fontIndex, numberFormatIndex, 0xFFF5, alignment, fg_and_bg_colours, fillPattern, border1, border2);

I'll confess that I'm more used to working with the BIFF8 format; but reading through the details of the format for BIFF5, I can't see any obvious problems with the xf that you're writing. I do know that in PHPExcel we explicitly write the 16 Font records for the pre-defineds before writing the new user-defined font records, and then always set the XF records pointing to the user-defined fonts.

Mark Baker 2010-09-14 19:19:31

Hi Mark, Thanks for your response! If you look at the code I supplied you'll see that although the first FONT record has a normal font weight (0x0190), the following FONT records are in fact using 0x02BC in offset 6. The LABEL points to the XF record which points to one of the FONT records using the 0x02BC byte. In other words, LABEL -> XF -> FONT, and the FONT record it points to is already in fact bold. Yet it still is not changing the font to bold. What am I missing?

Alex 2010-09-14 19:26:46

Rereading the specification document, Font records 0-15 are reserved for Normal style, Row outline levels 1-7, Column outline levels 1-7, and the Default cell format. User-defined font records should be index 16 and up.

Mark Baker 2010-09-14 21:16:55

According to the Excel Binary File Format Specifications PDF from Microsoft's website, Offset 8, in the LABEL record is an index to the XF record. Offset 4 in the XF record is an index to the FONT record, so if this is the case, I'm not understanding why it doesn't work as I've implemented it. Regardless, I have tried adding 16 (the total required XF records) to the FONT index number, as you suggested, and it still does not work. From my understanding, the XF index and FONT index are two different values. The LABEL record should point to the XF record, which in turn points to the FONT record.

Alex 2010-09-14 21:22:47

The principle you're outlining Cell -> XF -> Font is quite correct... the problem is probably somewhere in the detail of the values you're writing to the XF record, as you believe. there's a worked example at this url http://msdn.microsoft.com/en-us/library/dd943793(v=office.12).aspx that might help.

Mark Baker 2010-09-14 21:54:31

Ok, I have made a few changes to my XFs, and I'm fairly certain they are correct. I am also fairly certain all my indexes are correct. Am I missing some records that are required to make this functionality work? currently I am using the following records: BOF (0x10 - Worksheet), WINDOW1, 1904, FONT(x7), XF(x17), LABEL, and EOF. Everytime I try to add STYLE, EXTERNSHEET or BOUNDSHEET it corrupts the Excel file, so these are the only records I have currently. Am I missing a necessary record to make XF/FONT work properly?

Alex 2010-09-14 23:29:14

I'd assumed your code was extracts from the write rather than the whole script. XF and Font records appear in the General workbook stream, while each worksheet is written in its own stream, so you have a BOF at the beginning of the file, an EOF after the style XF and font records, followed by the individual worksheet streams

Mark Baker 2010-09-15 00:09:31

Thanks Mark, you're right. I updated my original post. Once I did that I could see in a different editor that the styling was being applied correctly, however now Excel kicks it back as "file is corrupt" I can only assume there is a problem with my BOUNDSHEET record, or I am missing records.. Now my current output is BOF (0x05), WINDOW1, 1904, FONT(x7), XF(x17), BOUNDSHEET, EOF, BOF (0x10), LABELs, EOF

Alex 2010-09-15 00:18:59

ansaurus

tags:

views:

answers:

BIFF5 Excel - Pointing to XF and FONT records from LABEL record

related questions