Skip to content

More extensive support for XF records; expose cell styles #430

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
andersnm merged 5 commits into from
Oct 31, 2019
Merged

More extensive support for XF records; expose cell styles #430

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
5 commits
Select commit Hold shift + click to select a range
5bb378d
Added GetCellStyle and expose indent, halignment, locked, hidden. Fix…
andersnm Sep 6, 2019
e509311
Fixed reading first workbook stream in xls if multiple are present. F…
andersnm Sep 7, 2019
bc4b6e4
Simplified number formats. Removed mapping to built-in number formats…
andersnm Sep 8, 2019
5665503
Renamed FormatIndex to NumberFormatIndex
andersnm Sep 8, 2019
e807dae
Added font index to cell styles
andersnm Sep 8, 2019
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
86 changes: 86 additions & 0 deletions src/ExcelDataReader/CellStyle.cs
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,86 @@
using System;

namespace ExcelDataReader
{
/// <summary>
/// Horizontal alignment.
/// </summary>
public enum HorizontalAlignment
{
/// <summary>
/// General.
/// </summary>
General,

/// <summary>
/// Left.
/// </summary>
Left,

/// <summary>
/// Centered.
/// </summary>
Centered,

/// <summary>
/// Right.
/// </summary>
Right,

/// <summary>
/// Filled.
/// </summary>
Filled,

/// <summary>
/// Justified.
/// </summary>
Justified,

/// <summary>
/// Centered across selection.
/// </summary>
CenteredAcrossSelection,

/// <summary>
/// Distributed.
/// </summary>
Distributed,
}

/// <summary>
/// Holds style information for a cell.
/// </summary>
public class CellStyle
{
/// <summary>
/// Gets the font index.
/// </summary>
public int FontIndex { get; internal set; }

/// <summary>
/// Gets the number format index.
/// </summary>
public int NumberFormatIndex { get; internal set; }

/// <summary>
/// Gets the indent level.
/// </summary>
public int IndentLevel { get; internal set; }

/// <summary>
/// Gets the horizontal alignment.
/// </summary>
public HorizontalAlignment HorizontalAlignment { get; internal set; }

/// <summary>
/// Gets a value indicating whether the cell is hidden.
/// </summary>
public bool Hidden { get; internal set; }

/// <summary>
/// Gets a value indicating whether the cell is locked.
/// </summary>
public bool Locked { get; internal set; }
}
}
106 changes: 106 additions & 0 deletions src/ExcelDataReader/Core/BinaryFormat/XlsBiffFont.cs
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,106 @@
using System.Text;
using ExcelDataReader.Exceptions;

namespace ExcelDataReader.Core.BinaryFormat
{
/// <summary>
/// The font with index 4 is omitted in all BIFF versions. This means the first four fonts have zero-based indexes, and the fifth font and all following fonts are referenced with one-based indexes.
/// </summary>
internal class XlsBiffFont : XlsBiffRecord
{
private readonly IXlsString _fontName;

internal XlsBiffFont(byte[] bytes, uint offset, int biffVersion)
: base(bytes, offset)
{
if (Id == BIFFRECORDTYPE.FONT_V34)
{
_fontName = new XlsShortByteString(bytes, offset + 4 + 6);
}
else if (Id == BIFFRECORDTYPE.FONT && biffVersion == 2)
{
_fontName = new XlsShortByteString(bytes, offset + 4 + 4);
}
else if (Id == BIFFRECORDTYPE.FONT && biffVersion == 5)
{
_fontName = new XlsShortByteString(bytes, offset + 4 + 14);
}
else if (Id == BIFFRECORDTYPE.FONT && biffVersion == 8)
{
_fontName = new XlsShortUnicodeString(bytes, offset + 4 + 14);
}
else
{
_fontName = new XlsInternalString(string.Empty);
}

if (Id == BIFFRECORDTYPE.FONT && biffVersion >= 5)
{
// Encodings were mapped by correlating this:
// https://docs.microsoft.com/en-us/windows/desktop/intl/code-page-identifiers
// with the FONT record character set table here:
// https://www.openoffice.org/sc/excelfileformat.pdf
var byteStringCharacterSet = ReadByte(12);
switch (byteStringCharacterSet)
{
case 0: // ANSI Latin
case 1: // System default
ByteStringEncoding = EncodingHelper.GetEncoding(1252);
break;
case 77: // Apple roman
ByteStringEncoding = EncodingHelper.GetEncoding(10000);
break;
case 128: // ANSI Japanese Shift-JIS
ByteStringEncoding = EncodingHelper.GetEncoding(932);
break;
case 129: // ANSI Korean (Hangul)
ByteStringEncoding = EncodingHelper.GetEncoding(949);
break;
case 130: // ANSI Korean (Johab)
ByteStringEncoding = EncodingHelper.GetEncoding(1361);
break;
case 134: // ANSI Chinese Simplified GBK
ByteStringEncoding = EncodingHelper.GetEncoding(936);
break;
case 136: // ANSI Chinese Traditional BIG5
ByteStringEncoding = EncodingHelper.GetEncoding(950);
break;
case 161: // ANSI Greek
ByteStringEncoding = EncodingHelper.GetEncoding(1253);
break;
case 162: // ANSI Turkish
ByteStringEncoding = EncodingHelper.GetEncoding(1254);
break;
case 163: // ANSI Vietnamese
ByteStringEncoding = EncodingHelper.GetEncoding(1258);
break;
case 177: // ANSI Hebrew
ByteStringEncoding = EncodingHelper.GetEncoding(1255);
break;
case 178: // ANSI Arabic
ByteStringEncoding = EncodingHelper.GetEncoding(1256);
break;
case 186: // ANSI Baltic
ByteStringEncoding = EncodingHelper.GetEncoding(1257);
break;
case 204: // ANSI Cyrillic
ByteStringEncoding = EncodingHelper.GetEncoding(1251);
break;
case 222: // ANSI Thai
ByteStringEncoding = EncodingHelper.GetEncoding(874);
break;
case 238: // ANSI Latin II
ByteStringEncoding = EncodingHelper.GetEncoding(1250);
break;
case 255: // OEM Latin
ByteStringEncoding = EncodingHelper.GetEncoding(850);
break;
}
}
}

public Encoding ByteStringEncoding { get; }

public string GetFontName(Encoding encoding) => _fontName.GetValue(encoding);
}
}
4 changes: 3 additions & 1 deletion src/ExcelDataReader/Core/BinaryFormat/XlsBiffStream.cs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -254,7 +254,9 @@ public XlsBiffRecord GetRecord(Stream stream)
case BIFFRECORDTYPE.XF_V2:
case BIFFRECORDTYPE.XF_V3:
case BIFFRECORDTYPE.XF_V4:
return new XlsBiffXF(bytes, offset);
return new XlsBiffXF(bytes, offset, biffVersion);
case BIFFRECORDTYPE.FONT:
return new XlsBiffFont(bytes, offset, biffVersion);
case BIFFRECORDTYPE.MERGECELLS:
return new XlsBiffMergeCells(bytes, offset);
case BIFFRECORDTYPE.COLINFO:
Expand Down
85 changes: 82 additions & 3 deletions src/ExcelDataReader/Core/BinaryFormat/XlsBiffXF.cs
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,31 +1,110 @@
using System;
using System.Collections.Generic;
using System.Text;

namespace ExcelDataReader.Core.BinaryFormat
{
[Flags]
internal enum XfUsedAttributes : byte
{
NumberFormat = 0x01,
Font = 0x02,
TextStyle = 0x04,
BorderLines = 0x08,
BackgroundAreaStyle = 0x10,
CellProtection = 0x20,
}

internal class XlsBiffXF : XlsBiffRecord
{
internal XlsBiffXF(byte[] bytes, uint offset)
internal XlsBiffXF(byte[] bytes, uint offset, int biffVersion)
: base(bytes, offset)
{
switch (Id)
{
case BIFFRECORDTYPE.XF_V2:
Font = ReadByte(0);
Format = ReadByte(2) & 0x3F;
IsLocked = (ReadByte(2) & 0x40) != 0;
IsHidden = (ReadByte(2) & 0x80) != 0;
HorizontalAlignment = (HorizontalAlignment)(ReadByte(3) & 0x07);
ParentCellStyleXf = 0xfff;
UsedAttributes =
XfUsedAttributes.NumberFormat | XfUsedAttributes.Font | XfUsedAttributes.TextStyle |
XfUsedAttributes.BorderLines | XfUsedAttributes.BackgroundAreaStyle | XfUsedAttributes.CellProtection;
break;
case BIFFRECORDTYPE.XF_V3:
Font = ReadByte(0);
Format = ReadByte(1);
UsedAttributes = (XfUsedAttributes)(ReadByte(3) >> 2);
IsLocked = (ReadByte(2) & 1) != 0;
IsHidden = (ReadByte(2) & 2) != 0;
IsCellStyleXf = (ReadByte(2) & 4) != 0;
ParentCellStyleXf = ReadUInt16(4) >> 4;
HorizontalAlignment = (HorizontalAlignment)(ReadByte(4) & 0x07);
break;
case BIFFRECORDTYPE.XF_V4:
Font = ReadByte(0);
Format = ReadByte(1);
IsLocked = (ReadByte(2) & 1) != 0;
IsHidden = (ReadByte(2) & 2) != 0;
IsCellStyleXf = (ReadByte(2) & 4) != 0;
ParentCellStyleXf = ReadUInt16(2) >> 4;
UsedAttributes = (XfUsedAttributes)(ReadByte(5) >> 2);
HorizontalAlignment = (HorizontalAlignment)(ReadByte(4) & 0x07);
break;
default:
Font = ReadUInt16(0);
Format = ReadUInt16(2);
IsLocked = (ReadByte(4) & 1) != 0;
IsHidden = (ReadByte(4) & 2) != 0;
IsCellStyleXf = (ReadByte(4) & 4) != 0;
ParentCellStyleXf = ReadUInt16(4) >> 4;
HorizontalAlignment = (HorizontalAlignment)(ReadByte(6) & 0x07);
if (biffVersion < 8)
{
UsedAttributes = (XfUsedAttributes)(ReadByte(7) >> 2);
}
else if (biffVersion == 8)
{
IndentLevel = ReadByte(8) & 0x0F;
UsedAttributes = (XfUsedAttributes)(ReadByte(9) >> 2);
}

break;
}

// Paren 0xfff = do not inherit any cell style XF
if (ParentCellStyleXf == 0xfff)
{
ParentCellStyleXf = -1;
}

// The font with index 4 is omitted in all BIFF versions. This means the first four
// fonts have zero-based indexes, and the fifth font and all following fonts are
// referenced with one-based indexes.
if (Font > 4)
{
Font--;
}
}

public int Font { get; }

public XfUsedAttributes UsedAttributes { get; }

public int Format { get; }

public int ParentCellStyleXf { get; }

public bool IsCellStyleXf { get; }

public bool IsLocked { get; }

public bool IsHidden { get; }

public bool ApplyAlignment { get; }

public int IndentLevel { get; }

public HorizontalAlignment HorizontalAlignment { get; }
}
}
39 changes: 35 additions & 4 deletions src/ExcelDataReader/Core/BinaryFormat/XlsWorkbook.cs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -67,7 +67,7 @@ internal XlsWorkbook(Stream stream, string password, Encoding fallbackEncoding)

public XlsBiffSimpleValueRecord Backup { get; set; }

public List<XlsBiffRecord> Fonts { get; } = new List<XlsBiffRecord>();
public List<XlsBiffFont> Fonts { get; } = new List<XlsBiffFont>();

public List<XlsBiffBoundSheet> Sheets { get; } = new List<XlsBiffBoundSheet>();

Expand Down Expand Up @@ -138,6 +138,37 @@ public IEnumerable<XlsWorksheet> ReadWorksheets()
}
}

internal void AddXf(XlsBiffXF xf)
{
// Ignore used flags in Cell XFs
var applyFont = !xf.IsCellStyleXf || (xf.UsedAttributes & XfUsedAttributes.Font) != 0;
var applyNumberFormat = !xf.IsCellStyleXf || (xf.UsedAttributes & XfUsedAttributes.NumberFormat) != 0;
var applyAlignment = !xf.IsCellStyleXf || (xf.UsedAttributes & XfUsedAttributes.TextStyle) != 0;
var applyProtection = !xf.IsCellStyleXf || (xf.UsedAttributes & XfUsedAttributes.CellProtection) != 0;
var extendedFormat = new ExtendedFormat()
{
FontIndex = xf.Font,
NumberFormatIndex = xf.Format,
Locked = xf.IsLocked,
Hidden = xf.IsHidden,
HorizontalAlignment = xf.HorizontalAlignment,
IndentLevel = xf.IndentLevel,
ParentCellStyleXf = xf.ParentCellStyleXf,
ApplyFont = applyFont,
ApplyNumberFormat = applyNumberFormat,
ApplyTextAlignment = applyAlignment,
ApplyProtection = applyProtection,
};

// The workbook holds two kinds of XF records: Cell XFs, and Cell Style XFs.
// In the binary XLS format, both kinds of XF records are saved in a single list,
// whereas the XLSX format has two separate lists - like the CommonWorkbook internals.
// The Cell XFs hold indexes into the Cell Style XF list, so adding the XF in both lists
// here to keep the indexes the same.
ExtendedFormats.Add(extendedFormat);
CellStyleExtendedFormats.Add(extendedFormat);
}

private void ReadWorkbookGlobals(XlsBiffStream biffStream)
{
XlsBiffRecord rec;
Expand Down Expand Up @@ -173,7 +204,7 @@ private void ReadWorkbookGlobals(XlsBiffStream biffStream)
break;
case BIFFRECORDTYPE.FONT:
case BIFFRECORDTYPE.FONT_V34:
Fonts.Add(rec);
Fonts.Add((XlsBiffFont)rec);
break;
case BIFFRECORDTYPE.FORMAT_V23:
{
Expand All @@ -193,7 +224,7 @@ private void ReadWorkbookGlobals(XlsBiffStream biffStream)
case BIFFRECORDTYPE.XF_V4:
case BIFFRECORDTYPE.XF_V3:
case BIFFRECORDTYPE.XF_V2:
AddExtendedFormat(GetExtendedFormatCount(), ((XlsBiffXF)rec).Format, true);
AddXf((XlsBiffXF)rec);
break;
case BIFFRECORDTYPE.SST:
SST = (XlsBiffSST)rec;
Expand Down Expand Up @@ -224,4 +255,4 @@ private void ReadWorkbookGlobals(XlsBiffStream biffStream)
}
}
}
}
}
Loading