XF: Extended Format (E0h)

The XF record stores formatting properties. There are two different XF records, one for cell records and another for style records. The fStyle bit is true if the XF is a style XF. The ixfe of a cell record (BLANK, LABEL, NUMBER, RK, and so on) points to a cell XF record, and the ixfe of a STYLE record points to a style XF record. Note that in previous BIFF versions, the record number for the XF record was 43h.

Prior to BIFF5, all number format information was included in FORMAT records in the BIFF file. Beginning with BIFF5, many of the built-in number formats were moved to an internal table and are no longer saved with the file as FORMAT records. You still use the ifmt to associate the built-in number formats with an XF record. However, the internal number formats are no longer visible in the BIFF file.

The following table lists all the number formats that are now maintained internally. Note that 17h through 24h are reserved for international versions and are undocumented at this time.

Index to internal format (ifmt)

Format string

00h

General

01h

0

02h

0.00

03h

#,##0


Index to internal format (ifmt)

Format string

04h

#,##0.00

05h

($#,##0_);($#,##0)

06h

($#,##0_);[Red]($#,##0)

07h

($#,##0.00_);($#,##0.00)

08h

($#,##0.00_);[Red]($#,##0.00)

09h

0%

0ah

0.00%

0bh

0.00E+00

0ch

# ?/?

0dh

# ??/??

0eh

m/d/yy

0fh

d-mmm-yy

10h

d-mmm

11h

mmm-yy

12h

h:mm AM/PM

13h

h:mm:ss AM/PM

14h

h:mm

15h

h:mm:ss

16h

m/d/yy h:mm

25h

(#,##0_);(#,##0)

26h

(#,##0_);[Red](#,##0)

27h

(#,##0.00_);(#,##0.00)

28h

(#,##0.00_);[Red](#,##0.00)

29h

_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)

2ah

_($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)

2bh

_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)

2ch

_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

2dh

mm:ss

2eh

[h]:mm:ss

2fh

mm:ss.0

30h

##0.0E+0

31h

@


A BIFF file can contain as many XF records as are necessary to describe the different cell formats and styles in a workbook. The XF records are written in a table in the workbook stream, and the index to the XF record table is a 0-based number called ixfe.

The workbook stream must contain a minimum XF table consisting of 15 style XF records and one cell XF record (ixfe=0 through ixfe=15). The first XF record (ixfe=0) is the XF record for the Normal style. The next 14 records (ixfe=1 through ixfe=14) are XF records that correspond to outline styles RowLevel_1, ColLevel_1, RowLevel_2, ColLevel_2, and so on. The last record (ixfe=15) is the default cell XF for the workbook.

Following these XF records are five additional style XF records (not strictly required) that correspond to the Comma, Comma [0], Currency, Currency [0], and Percent styles.

Cell XF Record — BIFF8

Record Data

Offset

Bits

Mask

Name

Contents

4

15 – 0

FFFFh

ifnt

Index to the FONT record.

6

15 – 0

FFFFh

ifmt

Index to the FORMAT record.

8

0

0001h

fLocked

= 1 if the cell is locked.

1

0002h

fHidden

= 1 if the cell is hidden.

2

0004h

fStyle

= 0 for cell XF.
= 1 for style XF.

3

0008h

f123Prefix

If the Transition Navigation Keys option is off (Options dialog box, Transition tab), f123Prefix = 1 indicates that a leading apostrophe (single quotation mark) is being used to coerce the cell's contents to a simple string.

If the Transition Navigation Keys option is on, f123Prefix = 1 indicates that the cell formula begins with one of the four Lotus 1-2-3 alignment prefix characters:
' left
" right
^ centered
\ fill

15 – 4

FFF0h

ixfParent

Index to the XF record of the parent style. Every cell XF must have a parent style XF, which is usually ixfeNormal = 0.

10

2 – 0

0007h

alc

Alignment:
0 = general
1 = left
2 = center
3 = right
4 = fill
5 = justify
6 = center across selection

3

0008h

fWrap

= 1 wrap text in cell.

6 – 4

0070h

alcV

Vertical alignment:
0 = top
1 = center
2 = bottom
3 = justify

7

0080h

fJustLast

(Used only in Far East versions of Microsoft Excel).

15 – 8

FF00h

trot

Rotation, in degrees; 0 – 90dec is up 0 – 90 deg., 91 – 180dec is down 1 – 90 deg, and 255dec is vertical.

12

3 – 0

000Fh

cIndent

Indent value (Format Cells dialog box, Alignment tab).

4

0010h

fShrinkToFit

= 1 if Shrink To Fit option is on (Format Cells dialog box, Alignment tab).

5

0020h

fMergeCell

= 1 if Merge Cells option is on (Format Cells dialog box, Alignment tab).

7 – 6

00C0h

iReadingOrder

Reading direction (Far East versions only):
0 = Context
1 = Left-to-right
2 = Right-to-left

9 – 8

0300h

(Reserved)

10

0400h

fAtrNum

= 1 if the ifmt is not equal to the ifmt of the parent style XF.

11

0800h

fAtrFnt

= 1 if the ifnt is not equal to the ifnt of the parent style XF.

12

1000h

fAtrAlc

= 1 if either the alc or the fWrap field is not equal to the corresponding field of the parent style XF.

13

2000h

fAtrBdr

= 1 if any border line field (dgTop, and so on) is not equal to the corresponding field of the parent style XF.

14

4000h

fAtrPat

= 1 if any pattern field (fls, icvFore, icvBack) is not equal to the corresponding field of the parent style XF.

15

8000h

fAtrProt

= 1 if either the fLocked field or the fHidden field is not equal to the corresponding field of the parent style XF.

14

3 – 0

000Fh

dgLeft

Border line style (see the following table).

7 – 4

00F0h

dgRight

Border line style (see the following table).

11 – 8

0F00h

dgTop

Border line style (see the following table).

15 – 12

F000h

dgBottom

Border line style (see the following table).

16

6 – 0

007Fh

icvLeft

Index to the color palette for the left border color.

13 – 7

3F80h

icvRight

Index to the color palette for the right border color.

15 – 14

C000h

grbitDiag

1=diag down, 2=diag up, 3=both.

18

6 – 0

0000007Fh

icvTop

Index to the color palette for the top border color.

13 – 7

00003F80h

icvBottom

Index to the color palette for the bottom border color.


Offset

Bits

Mask

Name

Contents

20 – 14

001FC000h

icvDiag

for diagonal borders.

24 – 21

01E00000h

dgDiag

Border line style (see the following table).

25

03800000h

(Reserved)

31 – 26

FC000000h

fls

Fill pattern.

22

6 – 0

007Fh

icvFore

Index to the color palette for the foreground color of the fill pattern.

13 – 7

3F80h

icvBack

Index to the color palette for the background color of the fill pattern.

14

4000h

fSxButton

= 1 if the XF record is attached to a PivotTable button.

15

8000h

(Reserved)


Border Line Styles (BIFF8)

The border line style fields — dgTop, dgLeft, and so on — correspond to the options in the Format Cells dialog box, Border tab, as shown in the following table.

dg* value

Border line style

0h

None

1h

Thin

2h

Medium

3h

Dashed

4h

Dotted

5h

Thick

6h

Double

7h

Hair

8h

Medium dashed

9h

Dash-dot

Ah

Medium dash-dot

Bh

Dash-dot-dot

Ch

Medium dash-dot-dot

Dh

Slanted dash-dot


Style XF Record — BIFF8

The style XF record is identical to the cell XF record, except that some of the fields have slightly different meanings.

Offset

Bits

Mask

Name

Contents

4

15 – 0

FFFFh

ifnt

Index to the FONT record.

6

15 – 0

FFFFh

ifmt

Index to the FORMAT record.

8

0

0001h

fLocked

= 1 if the cell is locked.

1

0002h

fHidden

= 1 if the cell is hidden.

2

0004h

fStyle

= 0 for cell XF.
= 1 for style XF.

3

0008h

f123Prefix

This bit is always = 0 for style XF.

15 – 4

FFF0h

ixfParent

For style XF records, this field equals FFFh (ixfNULL).

10

2 – 0

0007h

alc

Alignment:
0 = general
1 = left
2 = center
3 = right
4 = fill
5 = justify
6 = center across selection

3

0008h

fWrap

= 1 wrap text in cell.

6 – 4

0070h

alcV

Vertical alignment:
0 = top
1 = center
2 = bottom
3 = justify

7

0080h

fJustLast

(Used only in Far East versions of Microsoft Excel.)

15 – 8

FF00h

trot

Rotation, in degrees; 0 – 90dec is up 0 – 90 deg., 91 – 180dec is down 1 – 90 deg, and 255dec is vertical.

12

3 – 0

000Fh

cIndent

Indent value (Format Cells dialog box, Alignment tab).

4

0010h

fShrinkToFit

= 1 if Shrink To Fit option is on (Format Cells dialog box, Alignment tab).

5

0020h

fMergeCell

= 1 if Merge Cells option is on (Format Cells dialog box, Alignment tab).

7 – 6

00C0h

iReadingOrder

Reading direction (Far East versions only).

9 – 8

0300h

(Reserved)

10

0400h

fAtrNum

= 1 if the ifmt is not equal to the ifmt of the parent style XF.

11

0800h

fAtrFnt

= 1 if the ifnt is not equal to the ifnt of the parent style XF.

12

1000h

fAtrAlc

= 1 if either the alc or the fWrap field is not equal to the corresponding field of the parent style XF.

13

2000h

fAtrBdr

= 1 if any border line field (dgTop, and so on) is not equal to the corresponding field of the parent style XF.

14

4000h

fAtrPat

= 1 if any pattern field (fls, icvFore, icvBack) is not equal to the corresponding field of the parent style XF.

15

8000h

fAtrProt

= 1 if either the fLocked field or the fHidden field is not equal to the corresponding field of the parent style XF.

14

3 – 0

000Fh

dgLeft

Border line style (see the following table).

7 – 4

00F0h

dgRight

Border line style (see the following table).

11 – 8

0F00h

dgTop

Border line style (see the following table).

15 – 12

F000h

dgBottom

Border line style (see the following table).

16

6 – 0

007Fh

icvLeft

Index to the color palette for the left border color.

13 – 7

3F80h

icvRight

Index to the color palette for the right border color.

15 – 14

C000h

grbitDiag

1=diag down, 2=diag up, 3=both.

18

6 – 0

0000007Fh

icvTop

Index to the color palette for the top border color.

13 – 7

00003F80h

icvBottom

Index to the color palette for the bottom border color.

20 – 14

001FC000h

icvDiag

for diagonal borders.

24 – 21

01E00000h

dgDiag

Border line style (see the following table).

25

03800000h

(Reserved)

31 – 26

FC000000h

fls

Fill pattern.

22

6 – 0

007Fh

icvFore

Index to the color palette for the foreground color of the fill pattern.

13 – 7

3F80h

icvBack

Index to the color palette for the background color of the fill pattern.

14

4000h

fSxButton

This bit always = 0 for style XF.

15

8000h

(Reserved)


Border Line Styles (BIFF8)

The border line style fields — dgTop, dgLeft, and so on — correspond to the options in the Format Cells dialog box, Border tab, as shown in the following table.

dg* value

Border line style

0h

None

1h

Thin

2h

Medium

3h

Dashed

4h

Dotted

5h

Thick

6h

Double

7h

Hair

8h

Medium dashed

9h

Dash-dot

Ah

Medium dash-dot

Bh

Dash-dot-dot

Ch

Medium dash-dot-dot

Dh

Slanted dash-dot


Cell XF Record — BIFF7 and earlier

Record Data

Offset

Bits

Mask

Name

Contents

4

15 – 0

FFFFh

ifnt

Index to the FONT record.

6

15 – 0

FFFFh

ifmt

Index to the FORMAT record.

8

0

0001h

fLocked

= 1 if the cell is locked.

1

0002h

fHidden

= 1 if the cell is hidden.

2

0004h

fStyle

= 0 for cell XF.
= 1 for style XF.

3

0008h

f123Prefix

If the Transition Navigation Keys option is off (Options dialog box, Transition tab), f123Prefix = 1 indicates that a leading apostrophe (single quotation mark) is being used to coerce the cell's contents to a simple string.

If the Transition Navigation Keys option is on, f123Prefix = 1 indicates that the cell formula begins with one of the four Lotus 1-2-3 alignment prefix characters:
' left
" right
^ centered
\ fill

15 – 4

FFF0h

ixfParent

Index to the XF record of the parent style. Every cell XF must have a parent style XF, which is usually ixfeNormal = 0.

10

2 – 0

0007h

alc

Alignment:
0 = general
1 = left
2 = center
3 = right
4 = fill
5 = justify
6 = center across selection

3

0008h

fWrap

= 1 wrap text in cell.

10

6 – 4

0070h

alcV

Vertical alignment:
0 = top
1 = center
2 = bottom
3 = justify

7

0080h

fJustLast

(Used only in Far East versions of Microsoft Excel.)

9 – 8

0300h

ori

Orientation of text in cell:
= 0 no rotation.
= 1 text appears top-to-bottom; letters are upright.
= 2 text is rotated 90 degrees counterclockwise.
= 3 text is rotated 90 degrees clockwise.

10

0400h

fAtrNum

= 1 if the ifmt is not equal to the ifmt of the parent style XF.

11

0800h

fAtrFnt

= 1 if the ifnt is not equal to the ifnt of the parent style XF.

12

1000h

fAtrAlc

= 1 if either the alc or the fWrap field is not equal to the corresponding field of the parent style XF.

13

2000h

fAtrBdr

= 1 if any border line field (dgTop, and so on) is not equal to the corresponding field of the parent style XF.

14

4000h

fAtrPat

= 1 if any pattern field (fls, icvFore, icvBack) is not equal to the corresponding field of the parent style XF.

15

8000h

fAtrProt

= 1 if either the fLocked field or the fHidden field is not equal to the corresponding field of the parent style XF.

12

6 – 0

007Fh

icvFore

Index to the color palette for the foreground color of the fill pattern.

12 – 7

1F80h

icvBack

Index to the color palette for the background color of the fill pattern.

13

2000h

fSxButton

= 1 if the XF record is attached to a PivotTable button.

15 – 14

C000h

(Reserved)

14

5 – 0

003Fh

fls

Fill pattern.

8 – 6

01C0h

dgBottom

Border line style (see the following table).

15 – 9

FE00h

icvBottom

Index to the color palette for the bottom border color.

16

2 – 0

0007h

dgTop

Border line style (see the following table).

5 – 3

0038h

dgLeft

Border line style (see the following table).

8 – 6

01C0h

dgRight

Border line style (see the following table).

15 – 9

FE00h

icvTop

Index to the color palette for the top border color.

18

6 – 0

007Fh

icvLeft

Index to the color palette for the left border color.

13 – 7

3F80h

icvRight

Index to the color palette for the right border color.

15 – 14

C000h

(Reserved)


Border Line Styles (BIFF7)

The border line style fields — dgTop, dgLeft, and so on — correspond to the options in the Format Cells dialog box, Border tab, as shown in the following table.

dg* value

Border line style

0h

None

1h

Thin

2h

Medium

3h

Dashed

4h

Dotted

5h

Thick

6h

Double

7h

Hair


Style XF Record — BIFF7 and earlier

The style XF record is identical to the cell XF record, except that some of the fields have slightly different meanings.

Record Data

Offset

Bits

Mask

Name

Contents

4

15–0

FFFFh

ifnt

Index to the FONT record.

6

15–0

FFFFh

ifmt

Index to the FORMAT record.

8

0

0001h

fLocked

= 1 if the cell is locked.

1

0002h

fHidden

= 1 if the cell is hidden.

2

0004h

fStyle

= 0 for cell XF.
= 1 for style XF.

3

0008h

f123Prefix

This bit is always = 0 for style XF.

15–4

FFF0h

ixfParent

For style XF records, this field equals FFFh (ixfNULL).

10

2–0

0007h

alc

Alignment:
0 = general
1 = left
2 = center
3 = right
4 = fill
5 = justify
6 = center across selection

3

0008h

fWrap

= 1 wrap text in cell.

6–4

0070h

alcV

Vertical alignment:
0 = top
1 = center
2 = bottom
3 = justify

7

0080h

fJustLast

(Used only in Far East versions of Microsoft Excel.)

9–8

0300h

ori

Orientation of text in cell:
= 0 no rotation.
= 1 text appears top-to-bottom; letters are upright.
= 2 text is rotated 90 degrees counterclockwise.
= 3 text is rotated 90 degrees clockwise.

10

0400h

fAtrNum

= 0 if the style includes Number (Style dialog box).

11

0800h

fAtrFnt

= 0 if the style includes Font (Style dialog box).

12

1000h

fAtrAlc

= 0 if the style includes Alignment (Style dialog box).

13

2000h

fAtrBdr

= 0 if the style includes Border (Style dialog box).

14

4000h

fAtrPat

= 0 if the style includes Patterns (shading) (Style dialog box).

15

8000h

fAtrProt

= 0 if the style includes Protection (cell protection) (Style dialog box).

12

6–0

007Fh

icvFore

Index to the color palette for the foreground color of the fill pattern.

12–7

1F80h

icvBack

Index to the color palette for the background color of the fill pattern.

13

2000h

fSxButton

This bit always = 0 for style XF.

15–14

C000h

(Reserved)

14

5–0

003Fh

fls

Fill pattern.

8–6

01C0h

dgBottom

Border line style (see the following table).

15–9

FE00h

icvBottom

Index to the color palette for the bottom border color.

16

2–0

0007h

dgTop

Border line style (see the following table).

5–3

0038h

dgLeft

Border line style (see the following table).

8–6

01C0h

dgRight

Border line style (see the following table).

15–9

FE00h

icvTop

Index to the color palette for the top border color.

18

6–0

007Fh

icvLeft

Index to the color palette for the left border color.

13–7

3F80h

icvRight

Index to the color palette for the right border color.

15–14

C000h

(reserved)


Border Line Styles (BIFF7)

The border line style fields — dgTop, dgLeft, and so on — correspond to the options in the Format Cells dialog box, Border tab, as shown in the following table.

dg* value

Border line style

0h

None

1h

Thin

2h

Medium

3h

Dashed

4h

Dotted

5h

Thick

6h

Double

7h

Hair