Excel水印

起因：

出于数据安全与保护目的，需要对站点所有导出的excel文件自动加上水印

解决办法：

• 拦截器
  使用拦截器统一处理，对已有代码基本上做到零侵入，不影响现有逻辑
• 使用第三组件添加水印
• 注意事项
  • 搜索Excel加水印，请勿以watermark作为关键字，准确关键字应为BackgroundImage
  • 第三方组件存在收费与免费两种版本。部分收费组件可以免费使用，但对excel数据条数有限制。
  • 第三方组件普遍要求Excel为xlsx格式
  • .net自带excel组件需要本机安装office
  • 画图文字字体尽量不使用"微软雅黑"-收费。建议使用【宋体】simsun

---

拦截器

/// <summary>
    /// Excel水印拦截
    /// </summary>
    public class ExcelWaterMarkAttribute : ActionFilterAttribute
    {
       /// <summary>
        /// Excel水印
        /// </summary>
        /// <param name="filterContext"></param>
        public override void OnActionExecuted(ActionExecutedContext filterContext)
        {
            if (filterContext.Result is FileStreamResult)
            {
                var ret = (FileStreamResult)filterContext.Result;

                if (ret != null && ret.ContentType.Equals("application/ms-excel", StringComparison.OrdinalIgnoreCase))
                {
                    var excelName = ret.FileDownloadName;
                 //TODO 水印处理
                }
            }
            base.OnActionExecuted(filterContext);
        }
 }
 //在controller中加上上述标签即可

水印组件

• NPOI
  • 免费
  • 强烈推荐
  • 水印为NPOI后期增加方法，请注意引用版本。支持Excel格式为.xlsx

    /// <summary>
        /// Excel添加水印
        /// </summary>
        /// <param name="inputfilepath">Excel路径</param>
        /// <param name="waterMarkName">水印内容</param>
        /// <param name="outputfilepath">水印后文件路径</param>
        public static void setWatermark(string inputfilepath, string waterMarkName, string outputfilepath)
        {
            try
            {
                FileStream fs = new FileStream(outputfilepath,FileMode.Create);

                XSSFWorkbook workbook = new XSSFWorkbook(inputfilepath);
                //根据水印内容手动绘图
                Image image = DrawText(waterMarkName, font, Color.LightGray, System.Drawing.Color.White, 200, 200);
                // 导出到字节流B
                MemoryStream os = new MemoryStream();
                image.Save(os, System.Drawing.Imaging.ImageFormat.Png);
               // image.Save("D:\\Download\\1\\q.png");
                int pictureIdx = workbook.AddPicture(os.ToArray(), PictureType.PNG);
                POIXMLDocumentPart poixmlDocumentPart = (POIXMLDocumentPart)workbook.GetAllPictures()[pictureIdx];
                for (int i = 0; i < workbook.NumberOfSheets; i++)
                {
                //获取每个Sheet表
                    XSSFSheet sheetWB = (XSSFSheet)workbook.GetSheetAt(i);
                    PackagePartName ppn = poixmlDocumentPart.GetPackagePart().PartName;
                    String relType = XSSFRelation.IMAGES.Relation;
                    PackageRelationship pr = sheetWB.GetPackagePart().AddRelationship(ppn, TargetMode.Internal, relType, null);
              
                    sheetWB.GetCTWorksheet().picture = new NPOI.OpenXmlFormats.Spreadsheet.CT_SheetBackgroundPicture() {
                        id = pr.Id
                    };
                }
                workbook.Write(fs);
            }
            catch (Exception e)
            {
               
            }
        }
//手动绘图
     private byte[] 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.Clear(backColor);
            //创建文本刷
            Brush textBrush = new SolidBrush(textColor);

            var ypos = (Math.Sqrt(Math.Pow(width, 2) + Math.Pow(height, 2)) - textSize.Width) / 2;
            drawing.TranslateTransform(0, (float)ypos);
            drawing.RotateTransform(30);//控制倾斜角度
            drawing.DrawString(text, font, textBrush, 0, 0);
            drawing.ResetTransform();
            drawing.Save();
            var stream = new MemoryStream();
            img.Save(stream, ImageFormat.Png);
            return stream.ToArray();

        }

• Aspose.Cells
  • 收费

Workbook workbook = new Workbook(new MemoryStream(bytes));
                var font = new System.Drawing.Font("微软雅黑", 16);
                var img= DrawText(watermark, font, Color.LightGray, Color.White, 200, 200);
                for (int i = 0; i < workbook.Worksheets.Count; i++)
                {
                    workbook.Worksheets[i].BackgroundImage = img;
                }
 workbook.Save(file,SaveFormat.Xlsx);

• GroupDocs
  • 收费
  • 不推荐。免费使用时有版权文字