Working with worksheet headers and footers

Extracting information about all headers and footers in an Excel document

You can extract information about all the headers and footers in an Excel document as shown in the below code sample.

advanced_usage.add_watermarks_to_spreadsheets.SpreadsheetGetHeaderFooterInformation

SpreadsheetLoadOptions loadOptions = new SpreadsheetLoadOptions();                                               
// Constants.InSpreadsheetXlsx is an absolute or relative path to your document. Ex: "C:\\Docs\\spreadsheet.xlsx"
Watermarker watermarker = new Watermarker(Constants.InSpreadsheetXlsx, loadOptions);                             
                                                                                                                 
SpreadsheetContent content = watermarker.getContent(SpreadsheetContent.class);                                   
                                                                                                                 
for (SpreadsheetWorksheet worksheet : content.getWorksheets())                                                   
{                                                                                                                
    for (SpreadsheetHeaderFooter headerFooter : worksheet.getHeadersFooters())                                   
    {                                                                                                            
        System.out.println(headerFooter.getHeaderFooterType());                                                  
        for (SpreadsheetHeaderFooterSection section : headerFooter.getSections())                                
        {                                                                                                        
            System.out.println(section.getSectionType());                                                        
            if (section.getImage() != null)                                                                      
            {                                                                                                    
                System.out.println(section.getImage().getWidth());                                               
                System.out.println(section.getImage().getHeight());                                              
                System.out.println(section.getImage().getBytes().length);                                        
            }                                                                                                    
                                                                                                                 
            System.out.println(section.getScript());                                                             
        }                                                                                                        
    }                                                                                                            
}                                                                                                                
                                                                                                                 
watermarker.close();                                                                                             

You can also clear a particular header and footer using GroupDocs.Watermark as shown in the below code sample.

advanced_usage.add_watermarks_to_spreadsheets.SpreadsheetClearHeaderFooter

SpreadsheetLoadOptions loadOptions = new SpreadsheetLoadOptions();                                              
// Constants.InSpreadsheetXlsx is an absolute or relative path to your document. Ex: @"C:\Docs\spreadsheet.xlsx"
Watermarker watermarker = new Watermarker(Constants.InSpreadsheetXlsx, loadOptions);                            
                                                                                                                
SpreadsheetContent content = watermarker.getContent(SpreadsheetContent.class);                                  
                                                                                                                
SpreadsheetHeaderFooterSectionCollection sections = content                                                     
        .getWorksheets().get_Item(0)                                                                            
        .getHeadersFooters().getByOfficeHeaderFooterType(OfficeHeaderFooterType.HeaderPrimary)                  
        .getSections();                                                                                         
for (SpreadsheetHeaderFooterSection section : sections)                                                         
{                                                                                                               
    section.setScript(null);                                                                                    
    section.setImage(null);                                                                                     
}                                                                                                               
                                                                                                                
watermarker.save(Constants.OutSpreadsheetXlsx);                                                                 
                                                                                                                
watermarker.close();                                                                                            

Using GroupDocs.Watermark, you can also clear a particular section of header and footer as shown in the sample code below.

advanced_usage.add_watermarks_to_spreadsheets.SpreadsheetClearSectionOfHeaderFooter

SpreadsheetLoadOptions loadOptions = new SpreadsheetLoadOptions();                                               
// Constants.InSpreadsheetXlsx is an absolute or relative path to your document. Ex: "C:\\Docs\\spreadsheet.xlsx"
Watermarker watermarker = new Watermarker(Constants.InSpreadsheetXlsx, loadOptions);                             
                                                                                                                 
SpreadsheetContent content = watermarker.getContent(SpreadsheetContent.class);                                   
                                                                                                                 
SpreadsheetHeaderFooterSection section = content                                                                 
        .getWorksheets().get_Item(0)                                                                             
        .getHeadersFooters().getByOfficeHeaderFooterType(OfficeHeaderFooterType.HeaderEven)                      
        .getSections().getBySpreadsheetHeaderFooterSectionType(SpreadsheetHeaderFooterSectionType.Left);         
section.setImage(null);                                                                                          
section.setScript(null);                                                                                         
                                                                                                                 
watermarker.save(Constants.OutSpreadsheetXlsx);                                                                  
                                                                                                                 
watermarker.close();                                                                                             

GroupDocs.Watermark enables you to add watermark to images inside any header and footer. You can use below code sample to achieve this.

advanced_usage.add_watermarks_to_spreadsheets.SpreadsheetAddWatermarkToImagesInHeaderFooter

SpreadsheetLoadOptions loadOptions = new SpreadsheetLoadOptions();                                               
// Constants.InSpreadsheetXlsx is an absolute or relative path to your document. Ex: "C:\\Docs\\spreadsheet.xlsx"
Watermarker watermarker = new Watermarker(Constants.InSpreadsheetXlsx, loadOptions);                             
                                                                                                                 
// Initialize image or text watermark                                                                            
TextWatermark watermark = new TextWatermark("Protected image", new Font("Arial", 8));                            
watermark.setHorizontalAlignment(HorizontalAlignment.Center);                                                    
watermark.setVerticalAlignment(VerticalAlignment.Center);                                                        
watermark.setRotateAngle(45);                                                                                    
watermark.setSizingType(SizingType.ScaleToParentDimensions);                                                     
watermark.setScaleFactor(1);                                                                                     
                                                                                                                 
SpreadsheetContent content = watermarker.getContent(SpreadsheetContent.class);                                   
for (SpreadsheetWorksheet worksheet : content.getWorksheets())                                                   
{                                                                                                                
    for (SpreadsheetHeaderFooter headerFooter : worksheet.getHeadersFooters())                                   
    {                                                                                                            
        for (SpreadsheetHeaderFooterSection section : headerFooter.getSections())                                
        {                                                                                                        
            if (section.getImage() != null)                                                                      
            {                                                                                                    
                // Add watermark to the image                                                                    
                section.getImage().add(watermark);                                                               
            }                                                                                                    
        }                                                                                                        
    }                                                                                                            
}                                                                                                                
                                                                                                                 
watermarker.save(Constants.OutSpreadsheetXlsx);                                                                  
                                                                                                                 
watermarker.close();                                                                                             

More resources

GitHub examples

You may easily run the code above and see the feature in action in our GitHub examples:

Free online document watermarking App

Along with full featured Java library we provide simple, but powerful free Apps.

You are welcome to add watermark to PDF, DOC, DOCX, PPT, PPTX, XLS, XLSX, Emails and more with our free online Free Online Document Watermarking App.