This project has moved. For the latest updates, please go here.

Modfying existing header and footer of a loaded document

Oct 22, 2012 at 12:33 AM

Hi,

First of all, great work on ClosedXML it's been a life saver.

Now, on to the second thing. I'm not sure if it's just me, or i can't seem to find a proper in-library solution for my problem, but it seems i'm not able to modify an existing header at all, And from the looks of the code, it seems that isn't possible at all. Header and footer data is loaded with internal SetInnerText method inside XLHeaderFooter class, and when saving this innertext overrides all the data i set in the Central, Left and Right properties. I checked the latest source (i'm not using it) and that part is still pretty much the same. 

Suggestion: open up a SetInnerText method. I did it and it solved my problem. It still overrides the Central, Left and Right, but i'm not using them. XLHeaderFooter class and IXLHeaderFooter interface could use a little tweaking i i can provide it if you like.  Cheers!

Coordinator
Oct 22, 2012 at 2:58 AM

Use the .GetText method, then clear the item and then put the modified text in.

For convenience I added .Clear to headers/footers.

The problem is that Excel's header's and footer's are nasty. For every header/footer you have left/right/center plus odd/even/first/all pages. .

Oct 22, 2012 at 10:21 AM

Ok, now it's definitely doable:), but for my use case, a set inner text is still much easier. I use closedxml for excel driven templates, and basically i replace a placeholder with appropriate content (for instance %PartnerName% with Microsoft). With the Clear method, from what i gather, i should first gettext and parse it up t to appropriate areas (Center, LEft, Right) and then replace tokens, since i can't put text directly into header. 

2 possible solutions on library level would be: do that parsing internally and put texts in appropriate places during loading or, again, open up a setinnertext method:). Since i do realize how nasty headers and footers are, i think that safer option is opening that setinnertext method, it's much more bullet proof.

Nov 23, 2012 at 10:40 AM

Could you help me to find error in this code as I am unable to modify footer::

Private Function updateXlsxUsingXML(ByVal filename As String, ByVal footer As String) As String
        Dim workbook = Nothing
        Try
            workbook = New XLWorkbook(filename)
            For Each ws As IXLWorksheet In workbook.Worksheets
                ws.PageSetup.Footer.Right.AddText(footer, XLHFOccurrence.OddPages)
            Next
            workbook.Save()
            UpdateTable(filename, footer)
            Return True
        Catch ex As Exception
            writeLogFile.WriteLine(thisDay.ToString + " Exception " + ex.StackTrace)
            workbook.Save()
            Return False
        End Try
    End Function

Thanks