Generating Excel with Watermarks using .NET

Introduction

I have a package under development called SpreadsheetExporter that can generate Excel files with watermarks using NPOI or EPPlus. However, since the watermark functionality relies on System.Drawing.Common, and support for System.Drawing.Common has begun to decline since .NET 6, I initially considered restricting this feature to frameworks that support it. Eventually, I abandoned that idea and removed the feature entirely. To avoid losing the reference for future needs, I decided to document the method here.

Generating Excel with Watermarks

Excel does not have a built-in watermark feature, but you can simulate one by setting a full-page transparent background image.

Excel has three view modes: "Normal," "Page Break Preview," and "Page Layout," plus the printing function, making for four scenarios. I have not yet found an effective method for "Page Break Preview," but the others can be configured as follows:

• Setting an image in the Background allows the background image to be displayed in "Normal" and "Page Layout" modes.
• Setting an image in the Header allows the background image to be displayed in "Page Layout" mode and during "Printing."

Generating Full-Page Images

Excel's page setup allows you to configure page orientation and size, which determines the dimensions of the image to be generated.

You can use the following code to retrieve a collection of PaperSize objects that contain width and height records. The default Excel paper sizes can all be found here.

PrinterSettings settings = new PrinterSettings() {
    PrinterName = "Microsoft XPS Document Writer"
};
foreach (System.Drawing.Printing.PaperSize printerPaperSize in settings.PaperSizes) {
    // printerPaperSize.RawKind serial number
    // printerPaperSize.PaperName Paper name like A4
    // printerPaperSize.Width width
    // printerPaperSize.Height height
}

The following is the data for each PaperSize:

RawKind	PaperName	Width	Height
1	Letter	850	1100
2	Letter Small	850	1100
3	Tabloid	1100	1700
4	Ledger	1700	1100
5	Legal	850	1400
6	Statement	550	850
7	Executive	725	1050
8	A3	1169	1654
9	A4	827	1169
10	A4 Small	827	1169
11	A5	583	827
12	B4 (JIS)	1012	1433
13	B5 (JIS)	717	1012
14	Folio	850	1300
15	Quarto	846	1083
16	10×14	1000	1400
17	11×17	1100	1700
18	Note	850	1100
19	Envelope #9	387	887
20	Envelope #10	412	950
21	Envelope #11	450	1037
22	Envelope #12	475	1100
23	Envelope #14	500	1150
24	C size sheet	1700	2200
25	D size sheet	2200	3400
26	E size sheet	3400	4400
27	Envelope DL	433	866
28	Envelope C5	638	902
29	Envelope C3	1276	1803
30	Envelope C4	902	1276
31	Envelope C6	449	638
32	Envelope C65	449	902
33	Envelope B4	984	1390
34	Envelope B5	693	984
35	Envelope B6	693	492
36	Envelope	433	906
37	Envelope Monarch	387	750
38	6 3/4 Envelope	362	650
39	US Std Fanfold	1487	1100
40	German Std Fanfold	850	1200
41	German Legal Fanfold	850	1300
42	B4 (ISO)	984	1390
43	Japanese Postcard	394	583
44	9×11	900	1100
45	10×11	1000	1100
46	15×11	1500	1100
47	Envelope Invite	866	866
50	Letter Extra	950	1200
51	Legal Extra	950	1500
53	A4 Extra	927	1269
54	Letter Transverse	850	1100
55	A4 Transverse	827	1169
56	Letter Extra Transverse	950	1200
57	Super A	894	1402
58	Super B	1201	1917
59	Letter Plus	850	1269
60	A4 Plus	827	1299
61	A5 Transverse	583	827
62	B5 (JIS) Transverse	717	1012
63	A3 Extra	1268	1752
64	A5 Extra	685	925
65	B5 (ISO) Extra	791	1087
66	A2	1654	2339
67	A3 Transverse	1169	1654
68	A3 Extra Transverse	1268	1752

Once you know the PaperSize, you can use the following code to fix the blank areas of the watermark image background. Note that if the PaperSize is landscape, you must swap the width and height parameters.

public Image ResizeImageBackgroundToFullPage(Image watermark, int width, int height){
    if (watermark.Width > width || watermark.Height > height) {
        using (Image image = ZoomOutImage(width, height)) {
            return ResizeImageBackgroundToFullPageInternal(width, height, image);
        }
    }

    return ResizeImageBackgroundToFullPageInternal(width, height, watermark);
}

private Image ZoomOutImage(int pageWidth, int pageHeight) {
    decimal scale = Math.Max((decimal)watermark.Width / pageWidth, (decimal)watermark.Height / pageHeight);
    return new Bitmap(watermark, (int)(watermark.Width / scale), (int)(watermark.Height / scale));
}

private Image ResizeImageBackgroundToFullPageInternal(int pageWidth, int pageHeight, Image image) {
    Image bitmap = new Bitmap(pageWidth, pageHeight);
    using Graphics graphics = Graphics.FromImage(bitmap);
    graphics.Clear(Color.White);
    graphics.DrawImage(image, (pageWidth - image.Width) / 2, (pageHeight - image.Height) / 2);
    graphics.CompositingQuality = CompositingQuality.HighQuality;
    graphics.SmoothingMode = SmoothingMode.HighQuality;
    graphics.Save();

    return bitmap;
}

If you need to generate a text image programmatically, you can use the following code:

public Image DrawText(string text, Font font, Color textColor, Color backColor, int width, int height) {
    // Create a Bitmap object with specified width and height as an image container
    Image img = new Bitmap(width, height);
    using (Graphics drawing = Graphics.FromImage(img)) {

        // Calculate the coordinates of the text within the image container
        SizeF textSize = drawing.MeasureString(text, font, 0, StringFormat.GenericTypographic);
        float x = (width - textSize.Width) / 2;
        float y = (height - textSize.Height) / 2;

        drawing.TranslateTransform(x + (textSize.Width / 2), y + (textSize.Height / 2));
        // Rotate the graphic 45 degrees counter-clockwise
        drawing.RotateTransform(-45);
        drawing.TranslateTransform(-(x + (textSize.Width / 2)), -(y + (textSize.Height / 2)));

        // Clear a rectangle on the image container and fill it with the background color
        drawing.Clear(backColor);

        // Create a solid brush for drawing text
        Brush textBrush = new SolidBrush(textColor);
        drawing.DrawString(text, font, textBrush, x, y);

        drawing.Save();
        return img;
    }
}

TIP

For the logic regarding image rotation, you can refer to "C# Using GDI+ to Implement Text with Center Rotation (Arbitrary Angle)".

Generating Excel with Watermarks using EPPlus

Below is an example of how to generate an Excel file with a watermark using EPPlus, where the watermark type is Image.

sheet.HeaderFooter.OddHeader.InsertPicture(watermark, PictureAlignment.Centered);
sheet.BackgroundImage.Image = watermark;

WARNING

This approach may not be applicable to EPPlus 6 because, due to the System.Drawing.Common support issues in .NET 6 and later, EPPlus 6 removed the dependency on System.Drawing.Common. I have not followed the details, so I am unsure of the adjustment method.

Generating Excel with Watermarks using NPOI (XLSX)

To my knowledge, the current NPOI API cannot directly set Background Images and Header Images, but you can handle this using the lower-level APIs provided by NPOI.

To generate an Excel file with a watermark, you first need to understand the XML structure generated when setting Background and Header images. The following is an excerpt of the relevant XML:

Sheet XML content, where rId1 and rId2 are defined in \xl\worksheets_rels\{Sheet Name}.xml.rels.

<!-- Header Image -->
<headerFooter><oddHeader><![CDATA[&C&G]]></oddHeader></headerFooter><legacyDrawingHF r:id="rId2"/>
<!-- Background Image -->
<picture r:id="rId1"></picture>

{Sheet Name}.xml.rels

<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="../media/image1.png" />
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing" Target="../drawings/vmlDrawing1.vml" /></Relationships>

vmlDrawing1.vml uses o:relid="rId1" to associate the image rId1, which is defined in \xl\drawings\_rels\vmlDrawing1.vml.rels.

<xml xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel">
  <o:shapelayout v:ext="edit">
    <o:idmap v:ext="edit" data="1" />
  </o:shapelayout>
  <v:shapetype id="_x0000_t202" coordsize="21600,21600" o:spt="202" path="m,l,21600r21600,l21600,xe">
    <v:stroke joinstyle="miter" />
    <v:path gradientshapeok="t" o:connecttype="rect" />
  </v:shapetype>
  <v:shape id="CH" type="#_x0000_t75" style="position:absolute;margin-left:0;margin-top:0;width:876.75pt;height:620.25pt;z-index:1">
    <v:imagedata o:relid="rId1" o:title="" />
    <o:lock v:ext="edit" rotation="t" />
  </v:shape>
</xml>

The content of vmlDrawing1.vml.rels is as follows:

<?xml version="1.0" encoding="utf-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="../media/image1.png" />
</Relationships>

Originally, knowing this information would be enough to piece together the code for setting the watermark, but you encounter a problem: the built-in XSSFVMLDrawing is used to create Comments, so the generated structure is different from what is required and must be defined manually.

private class VmlRelation : POIXMLRelation {
    private static readonly Lazy<VmlRelation> instance = new(() => {
        return new VmlRelation(
                "application/vnd.openxmlformats-officedocument.vmlDrawing",
                "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing",
                "/xl/drawings/vmlDrawing#.vml",
                typeof(VmlDrawing)
        );
    });

    private VmlRelation(string type, string rel, string defaultName, Type cls) : base(type, rel, defaultName, cls) { }

    public static VmlRelation Instance => instance.Value;
}

private class VmlDrawing : POIXMLDocumentPart {
    public string PictureRelId { get; set; }

    public Image Image { get; set; }

    protected override void Commit() {
        PackagePart part = GetPackagePart();
        Stream @out = part.GetOutputStream();
        Write(@out);
        @out.Close();
    }

    private void Write(Stream stream) {
        // Pixel => Points
        float width = Image.Width * 72 / Image.HorizontalResolution;
        float height = Image.Height * 72 / Image.VerticalResolution;

        using StreamWriter sw = new(stream);
        XmlDocument doc = new();
        doc.LoadXml($@"
<xml xmlns:v=""urn:schemas-microsoft-com:vml"" xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:x=""urn:schemas-microsoft-com:office:excel"">
<o:shapelayout v:ext=""edit"">
<o:idmap v:ext=""edit"" data=""1"" />
</o:shapelayout>
<v:shapetype id=""_x0000_t202"" coordsize=""21600,21600"" o:spt=""202"" path=""m,l,21600r21600,l21600,xe"">
<v:stroke joinstyle=""miter"" />
<v:path gradientshapeok=""t"" o:connecttype=""rect"" />
</v:shapetype>
<v:shape id=""CH"" type=""#_x0000_t75"" style=""position:absolute;margin-left:0;margin-top:0;width:{width}pt;height:{height}pt;z-index:1"">
<v:imagedata o:relid=""{PictureRelId}"" o:title="""" />
<o:lock v:ext=""edit"" rotation=""t"" />
</v:shape>
</xml>");

        doc.Save(stream);
    }
}

Finally, we can use the following code to set the watermark.

MemoryStream imageMs = new MemoryStream();
watermark.Save(imageMs, System.Drawing.Imaging.ImageFormat.Png);

int pictureIdx = workbook.AddPicture(imageMs.ToArray(), PictureType.PNG);
POIXMLDocumentPart docPart = workbook.GetAllPictures()[pictureIdx] as POIXMLDocumentPart;

POIXMLDocumentPart.RelationPart backgroundRelPart = sheet.AddRelation(null, XSSFRelation.IMAGES, docPart);

sheet.GetCTWorksheet().picture = new CT_SheetBackgroundPicture {
    id = backgroundRelPart.Relationship.Id
};

int drawingNumber = (sheet.Workbook as XSSFWorkbook)
    .GetPackagePart()
    .Package
    .GetPartsByContentType(XSSFRelation.VML_DRAWINGS.ContentType).Count + 1;
VmlDrawing drawing = (VmlDrawing)sheet.CreateRelationship(VmlRelation.Instance, XSSFFactory.GetInstance(), drawingNumber);

POIXMLDocumentPart.RelationPart headerRelPart = drawing.AddRelation(null, XSSFRelation.IMAGES, docPart);

drawing.Image = watermark;
drawing.PictureRelId = headerRelPart.Relationship.Id;

sheet.Header.Center = HeaderFooter.PICTURE_FIELD.sequence;
sheet.GetCTWorksheet().legacyDrawingHF = new CT_LegacyDrawing {
    id = sheet.GetRelationId(drawing)
};

Changelog

• 2023-02-24 Initial version of the document created.