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:
[C#]
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;
}
[VB.NET]
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(Math.Truncate(width)), CInt(Math.Truncate(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(Math.Truncate(width)) - textSize.Width) \ 2, (CInt(Math.Truncate(height)) - textSize.Height) \ 2)
'rotate text
drawing.RotateTransform(-45)
'reset translate transform
drawing.TranslateTransform(-(CInt(Math.Truncate(width)) - textSize.Width) \ 2, -(CInt(Math.Truncate(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(Math.Truncate(width)) - textSize.Width) \ 2, (CInt(Math.Truncate(height)) - textSize.Height) \ 2)
drawing.Save()
Return img
End Function
