I am using the OpenXml SDK to generate a spreadsheet. I currently use about 5 different cell formats which I define in a style sheet. This works fine, assuming that I am creating a completely new document.
The style sheet looks something like this:
<?xml version="1.0" encoding="utf-8"?>
<x:styleSheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<x:fonts>
<x:font>
<x:sz val="11" />
</x:font>
<x:font>
<x:sz val="18" />
</x:font>
<x:font>
<x:i />
<x:sz val="11" />
</x:font>
</x:fonts>
<x:fills>
<x:fill>
<x:patternFill patternType="none" />
</x:fill>
<x:fill>
<x:patternFill patternType="gray125" />
</x:fill>
<x:fill>
<x:patternFill patternType="solid">
<x:fgColor rgb="C0C0C0" />
</x:patternFill>
</x:fill>
<x:fill>
<x:patternFill patternType="solid">
<x:fgColor rgb="DCDCDC" />
</x:patternFill>
</x:fill>
</x:fills>
<x:borders>
<x:border />
<x:border>
<x:left style="thin" />
<x:right style="thin" />
<x:top />
<x:bottom />
<x:diagonal />
</x:border>
</x:borders>
<x:cellXfs>
<x:xf />
<x:xf fontId="1" fillId="2" borderId="1">
<x:alignment horizontal="left" vertical="top" wrapText="1" />
</x:xf>
<x:xf fontId="0" fillId="0" borderId="1">
<x:alignment horizontal="left" vertical="top" wrapText="1" />
</x:xf>
<x:xf>
<x:alignment horizontal="left" vertical="top" wrapText="1" />
</x:xf>
<x:xf fontId="2" fillId="0" borderId="1">
<x:alignment horizontal="left" vertical="top" wrapText="1" />
</x:xf>
<x:xf fontId="1" fillId="3" borderId="1">
<x:alignment horizontal="left" vertical="top" wrapText="1" />
</x:xf>
</x:cellXfs>
</x:styleSheet>
I would like to support a second scenario, in which my program adds a new worksheet into a pre-existing spreadsheet.
This might be:
What would be a best strategy for creating styles in an existing spreadsheet?
Ideally, I would want to detect whether the required styles are already present and if so reuse them. Detecting a style based solely on its definition would be messy code. It would be much more practical if I could assign a unique name to xf elements which I define.
I know that I can define named styles in the cellStyles node, which refer to items in the cellStyleXfs collection, something like:
<x:cellStyleXfs>
<x:xf />
<x:xf fontId="1" fillId="2" borderId="1">
<x:alignment horizontal="left" vertical="top" wrapText="1" />
</x:xf>
<x:xf fontId="0" fillId="0" borderId="1">
<x:alignment horizontal="left" vertical="top" wrapText="1" />
</x:xf>
<x:xf>
<x:alignment horizontal="left" vertical="top" wrapText="1" />
</x:xf>
<x:xf fontId="2" fillId="0" borderId="1">
<x:alignment horizontal="left" vertical="top" wrapText="1" />
</x:xf>
<x:xf fontId="1" fillId="3" borderId="1">
<x:alignment horizontal="left" vertical="top" wrapText="1" />
</x:xf>
</x:cellStyleXfs>
<x:cellStyles>
<x:cellStyle name="Standard" xfId="0" />
<x:cellStyle name="ML_Header" xfId="1" />
<x:cellStyle name="ML_LanguageText" xfId="2" />
<x:cellStyle name="ML_DefaultLeftAligned" xfId="3" />
<x:cellStyle name="ML_CommentText" xfId="4" />
<x:cellStyle name="ML_SubHeader" xfId="5" />
</x:cellStyles>
However, I can't figure out the relationship between items in the cellStyleXfs collection and the callXfs collection.
In addition, Excel behaves like it owns the cellStyles and cellStyleXfs nodes. If I edit the spreadsheet in excel, the deinitions are changed and some of them are removed.
At present, this does not look like a practical solution.
Is there any way that I can assign a name to an xf node in the cellXfs element? That would make life easier.
An alternative approach would be to define new styles (and fonts and fills and borders) every time I generate a worksheet. Done repeatedly, that would make the file larger every time.
What would be the best strategy?
I have a strategy which is certainly not perfect, but which is good enough for my purpose.
I decided
As a minimum requirement, I wanted the function to at least find the styles which it had generated itself.
If my application exports to the same excel file 100 times, I don't want to generate the identical styles 100 times, making the excel file a little bigger every time.
The set of properties which I specify is incomplete. For example, I do not specify the font family. If there was a style which matched all of my properties, but used a different font, I would still use this style. That is OK for me, but in another application it might be unacceptable.
For historic reasons, my code is in VB, but it could easily be ported to C#.
This is a slightly reduced version of my code. There are 19 different styles, which are initialised by the function CreateStyleSheet(). If the style sheet does not exist at all, then it creates it, before checking for the individual styles.
The main function which searches for or creates a style is FindCellFormat. Its 15 parameters define the set or properties, which it checks for.
For 8 of the styles there is a helper function, FindCellFormatForStatus, but this is not particularly relevant.
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
Friend Class clsOpenXmlUtil
'CellFormat IDs
Public Property CellFormat_ColumnHeader As Integer
Public Property CellFormat_LanguageText As Integer
Public Property CellFormat_DefaultLeftAligned As Integer
Public Property CellFormat_CommentText As Integer
Public Property CellFormat_ColumnSubHeader As Integer
Public Property CellFormat_ColumnHeaderSmall As Integer
Public Property CellFormat_Location_UI As Integer
Public Property CellFormat_Location_Code As Integer
Public Property CellFormat_Location_Resource As Integer
Public Property CellFormat_MultipleUsageTrue As Integer
Public Property CellFormat_ComponentHeader As Integer
Public Property CellFormat_Status_Unknown As Integer
Public Property CellFormat_Status_OriginalText As Integer
Public Property CellFormat_Status_Edited As Integer
Public Property CellFormat_Status_GlobalDatabase As Integer
Public Property CellFormat_Status_ExcelImport As Integer
Public Property CellFormat_Status_OnlineTranslation As Integer
Public Property CellFormat_Status_ImportedLocalization As Integer
Public Property CellFormat_Status_OutOfDate As Integer
Public Sub CreateStyleSheet ( ExlBookPart As WorkbookPart )
If ExlBookPart.GetPartsCountOfType(Of WorkbookStylesPart) = 0 Then
Dim ss = New Stylesheet()
Dim fontCollection = New Fonts()
Dim fillCollection = New Fills()
Dim borderCollection = New Borders()
Dim formatCollection = New CellFormats()
'This block only defines the default styles.
'The styles which we actually use are defined in a second step.
fontCollection.Append ( New Font With { .FontSize = New FontSize With { .Val = 11 } } )
fillCollection.Append ( New Fill With { .PatternFill = New PatternFill With { .PatternType = PatternValues.None } } )
fillCollection.Append ( New Fill With { .PatternFill = New PatternFill With { .PatternType = PatternValues.Gray125 } } )
borderCollection.Append ( New Border() )
formatCollection.Append ( New CellFormat() )
ss.Append ( fontCollection )
ss.Append ( fillCollection )
ss.Append ( borderCollection )
ss.Append ( formatCollection )
Dim ExlStyleSheetPart = ExlBookPart.AddNewPart(Of WorkbookStylesPart)
ExlStyleSheetPart.Stylesheet = ss
End If
Dim ExlStyleSheet As Stylesheet = ExlBookPart.WorkbookStylesPart.Stylesheet
'Check for cell formats with specific attributes. Create the cell formats if necessary.
CellFormat_ColumnHeader = FindCellFormat ( ExlStyleSheet, 18, False, False, "C0C0C0", "", BorderStyleValues.Thin, BorderStyleValues.Thin, BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, HorizontalAlignmentValues.Left, VerticalAlignmentValues.Top, True, False )
CellFormat_LanguageText = FindCellFormat ( ExlStyleSheet, 11, False, False, "", "", BorderStyleValues.Thin, BorderStyleValues.Thin, BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, HorizontalAlignmentValues.Left, VerticalAlignmentValues.Top, True, True )
CellFormat_DefaultLeftAligned = FindCellFormat ( ExlStyleSheet, 11, False, False, "", "", BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, HorizontalAlignmentValues.Left, VerticalAlignmentValues.Top, True, False )
CellFormat_CommentText = FindCellFormat ( ExlStyleSheet, 11, True, False, "", "", BorderStyleValues.Thin, BorderStyleValues.Thin, BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, HorizontalAlignmentValues.Left, VerticalAlignmentValues.Top, True, True )
CellFormat_ColumnSubHeader = FindCellFormat ( ExlStyleSheet, 18, False, False, "DCDCDC", "", BorderStyleValues.Thin, BorderStyleValues.Thin, BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, HorizontalAlignmentValues.Left, VerticalAlignmentValues.Top, True, False )
CellFormat_ColumnHeaderSmall = FindCellFormat ( ExlStyleSheet, 11, False, False, "C0C0C0", "", BorderStyleValues.Thin, BorderStyleValues.Thin, BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, HorizontalAlignmentValues.Left, VerticalAlignmentValues.Top, True, False )
CellFormat_Location_UI = FindCellFormat ( ExlStyleSheet, 11, False, False, "CCFFCC", "", BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, HorizontalAlignmentValues.Left, VerticalAlignmentValues.Top, True, False )
CellFormat_Location_Code = FindCellFormat ( ExlStyleSheet, 11, False, False, "FFFF99", "", BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, HorizontalAlignmentValues.Left, VerticalAlignmentValues.Top, True, False )
CellFormat_Location_Resource = FindCellFormat ( ExlStyleSheet, 11, False, False, "CC99FF", "", BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, HorizontalAlignmentValues.Left, VerticalAlignmentValues.Top, True, False )
CellFormat_MultipleUsageTrue = FindCellFormat ( ExlStyleSheet, 11, False, False, "CCFFFF", "", BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.None, HorizontalAlignmentValues.Left, VerticalAlignmentValues.Top, True, False )
CellFormat_ComponentHeader = FindCellFormat ( ExlStyleSheet, 18, False, False, "FFFF99", "", BorderStyleValues.None, BorderStyleValues.None, BorderStyleValues.Thin, BorderStyleValues.Thin, BorderStyleValues.None, HorizontalAlignmentValues.Left, VerticalAlignmentValues.Top, True, False )
CellFormat_Status_Unknown = FindCellFormatForStatus ( ExlStyleSheet, GeneralSettings.ExcelBackground_Unknown, GeneralSettings.ExcelForeground_Unknown )
CellFormat_Status_OriginalText = FindCellFormatForStatus ( ExlStyleSheet, GeneralSettings.ExcelBackground_OriginalText, GeneralSettings.ExcelForeground_OriginalText )
CellFormat_Status_Edited = FindCellFormatForStatus ( ExlStyleSheet, GeneralSettings.ExcelBackground_Edited, GeneralSettings.ExcelForeground_Edited )
CellFormat_Status_GlobalDatabase = FindCellFormatForStatus ( ExlStyleSheet, GeneralSettings.ExcelBackground_GlobalDatabase, GeneralSettings.ExcelForeground_GlobalDatabase )
CellFormat_Status_ExcelImport = FindCellFormatForStatus ( ExlStyleSheet, GeneralSettings.ExcelBackground_ExcelImport, GeneralSettings.ExcelForeground_ExcelImport )
CellFormat_Status_OnlineTranslation = FindCellFormatForStatus ( ExlStyleSheet, GeneralSettings.ExcelBackground_OnlineTranslation, GeneralSettings.ExcelForeground_OnlineTranslation )
CellFormat_Status_ImportedLocalization = FindCellFormatForStatus ( ExlStyleSheet, GeneralSettings.ExcelBackground_ImportedLocalization, GeneralSettings.ExcelForeground_ImportedLocalization )
CellFormat_Status_OutOfDate = FindCellFormatForStatus ( ExlStyleSheet, GeneralSettings.ExcelBackground_OutOfDate, GeneralSettings.ExcelForeground_OutOfDate )
'So far as I can tell, this is not necessary.
'ExlStyleSheet.Save()
End Sub
Public Function FindCellFormatForStatus ( ExlStyleSheet As Stylesheet,
FillColour As System.Drawing.Color,
FontColour As System.Drawing.Color ) As Integer
Dim FillColourString = $"{FillColour.R:X2}{FillColour.G:X2}{FillColour.B:X2}"
Dim FontColourString = $"{FontColour.R:X2}{FontColour.G:X2}{FontColour.B:X2}"
Return FindCellFormat ( ExlStyleSheet,
11,
False,
False,
FillColourString,
FontColourString,
BorderStyleValues.Thin,
BorderStyleValues.Thin,
BorderStyleValues.None,
BorderStyleValues.None,
BorderStyleValues.None,
HorizontalAlignmentValues.Left,
VerticalAlignmentValues.Top,
True,
True)
End Function
Public Function FindCellFormat ( ExlStyleSheet As Stylesheet,
FontSize As Integer,
FontItalic As Boolean,
FontBold As Boolean,
FillColour As String,
FontColour As String,
LeftBorderStyle As BorderStyleValues,
RightBorderStyle As BorderStyleValues,
TopBorderStyle As BorderStyleValues,
BottomBorderStyle As BorderStyleValues,
DiagonalBorderStyle As BorderStyleValues,
HorizontalAlignment As HorizontalAlignmentValues,
VerticalAlignment As VerticalAlignmentValues,
WrapText As Boolean,
Unlocked As Boolean ) As Integer
Dim formatCollection As CellFormats = ExlStyleSheet.GetFirstChild(Of CellFormats)
Dim fontCollection As Fonts = ExlStyleSheet.GetFirstChild(Of Fonts)
Dim fillCollection As Fills = ExlStyleSheet.GetFirstChild(Of Fills)
Dim borderCollection As Borders = ExlStyleSheet.GetFirstChild(Of Borders)
Dim FormatId As Integer = -1
If formatCollection IsNot Nothing _
AndAlso fontCollection IsNot Nothing _
AndAlso fillCollection IsNot Nothing _
AndAlso borderCollection IsNot Nothing _
Then
Dim Count As Integer = formatCollection.Elements.Count
For i As Integer = 0 To Count-1
Try
Dim format As CellFormat = formatCollection.ElementAt(i)
'I have decided to skip cell formats without related Font, Fill or Border objects.
If format.FontId Is Nothing Then Continue For
If format.FillId Is Nothing Then Continue For
If format.BorderId Is Nothing Then Continue For
'These should now succeed.
Dim Font As Font = fontCollection.ElementAt(format.FontId.Value)
Dim Fill As Fill = fillCollection.ElementAt(format.FillId.Value)
Dim Border As Border = borderCollection.ElementAt(format.BorderId.Value)
'Read some values from the format, where we must check for null.
'As far as I can tell, the italic is usually indicated by the presence of an Italic object.
'Italic.Value is usually null. If Italic.Value is not null, then I guess we have to consider its value.
'The same applies to Bold.
Dim IsItalic = Font.Italic IsNot Nothing
Dim IsBold = Font.Bold IsNot Nothing
Dim IsFontSize = If ( Font?.FontSize.Val.Value, -1 )
If IsItalic AndAlso Font.Italic.Val IsNot Nothing Then
IsItalic = Font.Italic.Val.Value
End If
If IsBold AndAlso Font.Bold.Val IsNot Nothing Then
IsBold = Font.Bold.Val.Value
End If
Dim IsLeftBorderStyle = if ( Border.LeftBorder?.Style Is Nothing, BorderStyleValues.None, Border.LeftBorder.Style.Value )
Dim IsRightBorderStyle = if ( Border.RightBorder?.Style Is Nothing, BorderStyleValues.None, Border.RightBorder.Style.Value )
Dim IsTopBorderStyle = if ( Border.TopBorder?.Style Is Nothing, BorderStyleValues.None, Border.TopBorder.Style.Value )
Dim IsBottomBorderStyle = if ( Border.BottomBorder?.Style Is Nothing, BorderStyleValues.None, Border.BottomBorder.Style.Value )
Dim IsDiagonalBorderStyle = if ( Border.DiagonalBorder?.Style Is Nothing, BorderStyleValues.None, Border.DiagonalBorder.Style.Value )
Dim IsFillColour = ""
If Fill.PatternFill.PatternType.Value = PatternValues.Solid Then
'I believe that I have seen both 8 and 6 character values.
'Tentatively only compare 6 characters
IsFillColour = If ( Fill.PatternFill?.ForegroundColor?.Rgb?.Value, "" )
If IsFillColour.Length = 8 Then
IsFillColour = IsFillColour.Substring(2)
End If
End If
Dim IsFontColour = If ( Font.Color?.Rgb?.Value, "" )
If IsFontColour.Length = 8 Then
IsFontColour = IsFontColour.Substring(2)
End If
'Unlocked is only meaningful if protection is applies to the worksheet.
'In this case, all cells are locked unless they are specifically unlocked.
'As far as I can tell this requires the attribute applyProtection="1" and the child node <protection locked="0">.
'See also the YouTube video https://www.youtube.com/watch?time_continue=20&v=KN2Q0vWMd8k
Dim IsUnlocked = if ( format.ApplyProtection?.Value, False ) AndAlso Not If ( format.Protection?.Locked?.Value, True )
'Always check for null
Dim IsHorizontalAlignment = If ( format.Alignment?.Horizontal?.Value, HorizontalAlignmentValues.General )
Dim IsVerticalAlignment = If ( format.Alignment?.Vertical?.Value, VerticalAlignmentValues.Bottom )
Dim IsWrapText = If ( format.Alignment?.WrapText?.Value, False )
'Finally start comparing stuff
If Font.FontSize.Val.Value <> FontSize Then Continue For
If IsItalic <> FontItalic Then Continue For
If IsBold <> FontBold Then Continue For
If IsFillColour <> FillColour Then Continue For
If IsFontColour <> FontColour Then Continue For
If IsLeftBorderStyle <> LeftBorderStyle Then Continue For
If IsRightBorderStyle <> RightBorderStyle Then Continue For
If IsTopBorderStyle <> TopBorderStyle Then Continue For
If IsBottomBorderStyle <> BottomBorderStyle Then Continue For
If IsDiagonalBorderStyle <> DiagonalBorderStyle Then Continue For
If IsHorizontalAlignment <> HorizontalAlignment Then Continue For
If IsVerticalAlignment <> VerticalAlignment Then Continue For
If IsWrapText <> WrapText Then Continue For
If IsUnlocked <> Unlocked Then Continue For
'If we get this far, we will consider it to be a match.
FormatId = i
Exit For
Catch ex As Exception
'We'll take that as a no shall we?
Continue For
End Try
Next
If FormatId = -1 Then
'We did not find the format, so create a new one.
'The new IDs will be the collection size BEFORE adding a new element.
Dim FontId = fontCollection.Elements.Count
Dim FillId = fillCollection.Elements.Count
Dim BorderId = borderCollection.Elements.Count
FormatId = formatCollection.Elements.Count
Dim TextColour As Color = Nothing
If FontColour.HasContent() Then
TextColour = New Color With { .Rgb = New HexBinaryValue ( FontColour ) }
End If
fontCollection.Append ( New Font With { .FontSize = New FontSize With { .Val = FontSize },
.Italic = New Italic With { .Val = FontItalic },
.Bold = New Bold With { .Val = FontBold },
.Color = TextColour } )
borderCollection.Append ( New Border With { .LeftBorder = New LeftBorder With { .Style = LeftBorderStyle },
.RightBorder = New RightBorder With { .Style = RightBorderStyle },
.TopBorder = New TopBorder With { .Style = TopBorderStyle },
.BottomBorder = New BottomBorder With { .Style = BottomBorderStyle },
.DiagonalBorder = New DiagonalBorder With { .Style = DiagonalBorderStyle } } )
If String.IsNullOrEmpty(FillColour) Then
'Use default fill
FillId = 0
Else
Dim fgColour = New ForegroundColor With { .Rgb = New HexBinaryValue ( FillColour ) }
fillCollection.Append ( New Fill With { .PatternFill = New PatternFill ( fgColour ) With { .PatternType = PatternValues.Solid } } )
End If
Dim align = New Alignment With { .Horizontal = HorizontalAlignment, .Vertical = VerticalAlignment, .WrapText = WrapText }
Dim format = New CellFormat ( align ) With { .FontId=FontId, .FillId=FillId, .BorderId=BorderId }
formatCollection.Append ( format )
If Unlocked Then
format.ApplyProtection = True
format.Protection = New Protection With { .Locked = False }
End If
End If
End If
Return FormatId
End Function
End Class