c#/vb.net excel 工作表添加水印-k8凯发天生赢家

本文将介绍如何使用spire.xls组件给excel工作表添加水印。microsoft excel没有直接的方法内置水印在excel工作表中，我们可以通过添加背景图片在视觉上形成独特的水印效果，但是此类背景水印在打印excel文档时水印图案会丢失。同时，我们也可以通过添加页眉页脚功能，将图片或文字加到excel文档内容背后，实现类似水印的效果。但是该类水印只在打印预览或将视图模式为“页面视图”下才能直观可见。

通常情况下，水印分为文本水印和图片水印。spire.xls添加的水印实际上都为图片水印。下面我们将详细介绍使用spire.xls将文本以图片形式添加到excel中， 生成文本水印。

添加背景文本图片水印

static void main(string[] args)
{
    //加载excel文档
    workbook workbook = new workbook();
    workbook.loadfromfile("test.xlsx");
    //设置水印文字和字体
    font font = new font("宋体", 36);
    string watermark = "背景水印";
//设置背景图片为水印
    foreach (worksheet sheet in workbook.worksheets)
    {
        //调用drawtext()方法创建的图片
        image imgwtrmrk = drawtext(watermark, font, color.lightcoral, color.white, sheet.pagesetup.pageheight, sheet.pagesetup.pagewidth);
        
        sheet.pagesetup.backgoundimage = imgwtrmrk as bitmap;                 
        
}
//保存文档并预览
    workbook.savetofile("result.xlsx", excelversion.version2010);
    system.diagnostics.process.start("result.xlsx");
}
private static image drawtext(string text, system.drawing.font font, color textcolor, color backcolor, double height, double width)
{
    //将用来作为水印的文本返回为图片并设置其展示样式
    image img = new bitmap((int)width, (int)height);
    graphics drawing = graphics.fromimage(img);
    sizef textsize = drawing.measurestring(text, font);
    drawing.translatetransform(((int)width - textsize.width) / 2, ((int)height - textsize.height) / 2);
    drawing.rotatetransform(-45);
    drawing.translatetransform(-((int)width - textsize.width) / 2, -((int)height - textsize.height) / 2);
   
    drawing.clear(backcolor);
    brush textbrush = new solidbrush(textcolor);
    drawing.drawstring(text, font, textbrush, ((int)width - textsize.width) / 2, ((int)height - textsize.height) / 2);
    drawing.save();
    return img;
}

private shared sub main(args as string())
  '加载excel文档
  dim workbook as new workbook()
  workbook.loadfromfile("test.xlsx")
  '设置水印文字和字体
  dim font as new font("宋体", 36)
  dim watermark as [string] = "背景水印"
  '设置背景图片为水印
  for each sheet as worksheet in workbook.worksheets
    '调用drawtext()方法创建的图片
    dim imgwtrmrk as image = drawtext(watermark, font, color.lightcoral, color.white, sheet.pagesetup.pageheight, sheet.pagesetup.pagewidth)
    sheet.pagesetup.backgoundimage = trycast(imgwtrmrk, bitmap)
  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 image
  '将用来作为水印的文本返回为图片并设置其展示样式
  dim img as image = new bitmap(cint(width), cint(height))
  dim drawing as graphics = graphics.fromimage(img)
  dim textsize as sizef = drawing.measurestring(text, font)
  drawing.translatetransform((cint(width) - textsize.width) / 2, (cint(height) - textsize.height) / 2)
  drawing.rotatetransform(-45)
  drawing.translatetransform(-(cint(width) - textsize.width) / 2, -(cint(height) - textsize.height) / 2)
  drawing.clear(backcolor)
  dim textbrush as brush = new solidbrush(textcolor)
  drawing.drawstring(text, font, textbrush, (cint(width) - textsize.width) / 2, (cint(height) - textsize.height) / 2)
  drawing.save()
  return img
end function

添加页眉页脚文本图片水印

static void main(string[] args)
{
    //加载excel文档
    workbook workbook = new workbook();
    workbook.loadfromfile("test.xlsx");
    //设置水印文字和字体
    font font = new font("宋体", 36);
string watermark = "内部资料";
//在页眉中插入图片作为模拟水印
    foreach (worksheet sheet in workbook.worksheets)
    {
        //调用drawtext()方法创建的图片
        image imgwtrmrk = drawtext(watermark, font, system.drawing.color.lightcoral, system.drawing.color.white, sheet.pagesetup.pageheight, sheet.pagesetup.pagewidth);
        //插入图片作为leftheaderimage
        sheet.pagesetup.leftheaderimage = imgwtrmrk;
        sheet.pagesetup.leftheader = "&g";
        
//设置视图模式，页眉水印仅在layout模式下直观可见
        sheet.viewmode = viewmode.layout;
}
//保存文档并预览
    workbook.savetofile("result.xlsx", excelversion.version2010);
    system.diagnostics.process.start("result.xlsx");
}
private static image drawtext(string text, system.drawing.font font, color textcolor, color backcolor, double height, double width)
{
    //将用来作为水印的文本返回为图片并设置其展示样式
    image img = new bitmap((int)width, (int)height);
    graphics drawing = graphics.fromimage(img);
    
    sizef textsize = drawing.measurestring(text, font);
    drawing.translatetransform(((int)width - textsize.width) / 2, ((int)height - textsize.height) / 2);
    drawing.rotatetransform(-45);
    drawing.translatetransform(-((int)width - textsize.width) / 2, -((int)height - textsize.height) / 2);
    drawing.clear(backcolor);
    brush textbrush = new solidbrush(textcolor);
    drawing.drawstring(text, font, textbrush, ((int)width - textsize.width) / 2, ((int)height - textsize.height) / 2);
drawing.save();
    return img;
}

private shared sub main(args as string())
  '加载excel文档
  dim workbook as new workbook()
  workbook.loadfromfile("test.xlsx")
  '设置水印文字和字体
  dim font as new font("宋体", 36)
  dim watermark as [string] = "内部资料"
  '在页眉中插入图片作为模拟水印
  for each sheet as worksheet in workbook.worksheets
    '调用drawtext()方法创建的图片
    dim imgwtrmrk as image = drawtext(watermark, font, system.drawing.color.lightcoral, system.drawing.color.white, sheet.pagesetup.pageheight, sheet.pagesetup.pagewidth)
    '插入图片作为leftheaderimage
    sheet.pagesetup.leftheaderimage = imgwtrmrk
    sheet.pagesetup.leftheader = "&g"
    '设置视图模式，页眉水印仅在layout模式下直观可见
    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 image
  '将用来作为水印的文本返回为图片并设置其展示样式
  dim img as image = new bitmap(cint(width), cint(height))
  dim drawing as graphics = graphics.fromimage(img)
  dim textsize as sizef = drawing.measurestring(text, font)
  drawing.translatetransform((cint(width) - textsize.width) / 2, (cint(height) - textsize.height) / 2)
  drawing.rotatetransform(-45)
  drawing.translatetransform(-(cint(width) - textsize.width) / 2, -(cint(height) - textsize.height) / 2)
  drawing.clear(backcolor)
  dim textbrush as brush = new solidbrush(textcolor)
  drawing.drawstring(text, font, textbrush, (cint(width) - textsize.width) / 2, (cint(height) - textsize.height) / 2)
  drawing.save()
  return img
end function