Microsoft Excel doesn't have a built-in feature to add watermark in Excel sheet. But there are several tricky ways such as adding header image or WordArt to worksheets to simulate the look of a watermark. In this article, I'll show you how to create and insert a header image in Excel to mimic a watermark using Spire.XLS in C#, VB.NET.
Code Snippet:
Step 1: Define a custom function called DrawText() to create an image based the content of a string. The string can be "confidential", "draft", "sample" or any text you want it to be shown as watermark.
private static System.Drawing.Image DrawText(String text, System.Drawing.Font font, Color textColor, Color backColor, double height, double width) { //create a bitmap image with specified width and height Image img = new Bitmap((int)width, (int)height); Graphics drawing = Graphics.FromImage(img); //get the size of text SizeF textSize = drawing.MeasureString(text, font); //set rotation point drawing.TranslateTransform(((int)width - textSize.Width) / 2, ((int)height - textSize.Height) / 2); //rotate text drawing.RotateTransform(-45); //reset translate transform drawing.TranslateTransform(-((int)width - textSize.Width) / 2, -((int)height - textSize.Height) / 2); //paint the background drawing.Clear(backColor); //create a brush for the text Brush textBrush = new SolidBrush(textColor); //draw text on the image at center position drawing.DrawString(text, font, textBrush, ((int)width - textSize.Width)/2 , ((int)height - textSize.Height)/2 ); drawing.Save(); return img; }
Step 2: Initialize a new instance of workbook and load the test file.
Workbook workbook = new Workbook(); workbook.LoadFromFile(@"D:\MyExcel\SurfaceSample.xls");
Step 3: Call DrawText() method to create an image, set the image as left header image. In addition, the header image only shows when the view mode is Layout, so don’t forget to change the view mode as Layout.
Font font = new System.Drawing.Font("arial", 40); String watermark = "Confidential"; foreach (Worksheet sheet in workbook.Worksheets) { //call DrawText() to create an image System.Drawing.Image imgWtrmrk = DrawText(watermark, font, System.Drawing.Color.LightCoral, System.Drawing.Color.White, sheet.PageSetup.PageHeight, sheet.PageSetup.PageWidth); //set image as left header image sheet.PageSetup.LeftHeaderImage = imgWtrmrk; sheet.PageSetup.LeftHeader= "&G"; //the watermark will only appear in this mode, it will disappear if the mode is normal sheet.ViewMode = ViewMode.Layout; }
Step 4: Save and launch the file.
workbook.SaveToFile("result.xlsx",ExcelVersion.Version2010); System.Diagnostics.Process.Start("result.xlsx");
Output:
Full Code:
using Spire.Xls; using System; using System.Drawing; namespace AddWatermark { class Program { static void Main(string[] args) { //initialize a new instance of workbook and load the test file Workbook workbook = new Workbook(); workbook.LoadFromFile(@"D:\MyExcel\SurfaceSample.xls"); //insert iamge in a header to mimic a watermark Font font = new System.Drawing.Font("arial", 40); String watermark = "Confidential"; foreach (Worksheet sheet in workbook.Worksheets) { //call DrawText() to create an image System.Drawing.Image imgWtrmrk = DrawText(watermark, font, System.Drawing.Color.LightCoral, System.Drawing.Color.White, sheet.PageSetup.PageHeight, sheet.PageSetup.PageWidth); //set image as left header image sheet.PageSetup.LeftHeaderImage = imgWtrmrk; sheet.PageSetup.LeftHeader = "&G"; //the watermark will only appear in this mode, it will disappear if the mode is normal sheet.ViewMode = ViewMode.Layout; } workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010); System.Diagnostics.Process.Start("result.xlsx"); } private static System.Drawing.Image DrawText(String text, System.Drawing.Font font, Color textColor, Color backColor, double height, double width) { //create a bitmap image with specified width and height Image img = new Bitmap((int)width, (int)height); Graphics drawing = Graphics.FromImage(img); //get the size of text SizeF textSize = drawing.MeasureString(text, font); //set rotation point drawing.TranslateTransform(((int)width - textSize.Width) / 2, ((int)height - textSize.Height) / 2); //rotate text drawing.RotateTransform(-45); //reset translate transform drawing.TranslateTransform(-((int)width - textSize.Width) / 2, -((int)height - textSize.Height) / 2); //paint the background drawing.Clear(backColor); //create a brush for the text Brush textBrush = new SolidBrush(textColor); //draw text on the image at center position drawing.DrawString(text, font, textBrush, ((int)width - textSize.Width) / 2, ((int)height - textSize.Height) / 2); drawing.Save(); return img; } } }
Imports Spire.Xls Imports System.Drawing Namespace AddWatermark Class Program Private Shared Sub Main(args As String()) 'initialize a new instance of workbook and load the test file Dim workbook As New Workbook() workbook.LoadFromFile("D:\MyExcel\SurfaceSample.xls") 'insert iamge in a header to mimic a watermark Dim font As Font = New System.Drawing.Font("arial", 40) Dim watermark As [String] = "Confidential" For Each sheet As Worksheet In workbook.Worksheets 'call DrawText() to create an image Dim imgWtrmrk As System.Drawing.Image = DrawText(watermark, font, System.Drawing.Color.LightCoral, System.Drawing.Color.White, sheet.PageSetup.PageHeight, sheet.PageSetup.PageWidth) 'set image as left header image sheet.PageSetup.LeftHeaderImage = imgWtrmrk sheet.PageSetup.LeftHeader = "&G" 'the watermark will only appear in this mode, it will disappear if the mode is normal sheet.ViewMode = ViewMode.Layout Next workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010) System.Diagnostics.Process.Start("result.xlsx") End Sub Private Shared Function DrawText(text As [String], font As System.Drawing.Font, textColor As Color, backColor As Color, height As Double, width As Double) As System.Drawing.Image 'create a bitmap image with specified width and height Dim img As Image = New Bitmap(CInt(width), CInt(height)) Dim drawing As Graphics = Graphics.FromImage(img) 'get the size of text Dim textSize As SizeF = drawing.MeasureString(text, font) 'set rotation point drawing.TranslateTransform((CInt(width) - textSize.Width) / 2, (CInt(height) - textSize.Height) / 2) 'rotate text drawing.RotateTransform(-45) 'reset translate transform drawing.TranslateTransform(-(CInt(width) - textSize.Width) / 2, -(CInt(height) - textSize.Height) / 2) 'paint the background drawing.Clear(backColor) 'create a brush for the text Dim textBrush As Brush = New SolidBrush(textColor) 'draw text on the image at center position drawing.DrawString(text, font, textBrush, (CInt(width) - textSize.Width) / 2, (CInt(height) - textSize.Height) / 2) drawing.Save() Return img End Function End Class End Namespace