# 使用 .NET 產生帶有浮水印的 Excel
[](https://hackmd.io/3xVaIkvfQGex8nPzol-tVA)
## 前言
我有一個開發中的套件 `SpreadsheetExporter` 可以使用 NPOI 或 EPPlus 產生帶有浮水印的 Excel，但是由於浮水印功能會使用到 `System.Drawing.Common`，但是隨著 .NET 6 對於`System.Drawing.Common`的支援度開始下降。本來想改為僅提供有支援 `System.Drawing.Common` 的 Framework 才提供這個功能，但後來我放棄了這個想法，直接刪除相關功能。為了避免後續需要時沒有參考，我決定把方法作為筆記。
## 產生帶有浮水印的 Excel
Excel 並沒有內建的浮水印功能，但是可以透過設定滿版的透明背景圖片來模擬出浮水印的效果。
Excel 有三種檢視模式，分別為「標準模式」、「分頁模式」和「整頁模式」，再加上列印，有四種狀況，目前「分頁模式」，我還沒有找到有效的方法，其他可用以下方式設定：
* 在 Background 設定 Image 可以在「標準模式」和「整頁模式」顯示背景圖片。
* 在 Header 設定 Image 可以在「整頁模式」和「列印」顯示背景圖片。
## 產生滿版圖片
Excel 的版面配置，可以設定頁面方向和頁面大小，這會決定要產生的圖片大小。
可以藉由下面的程式碼取得有紀錄長寬的 PaperSize 物件集合，Excel 預設的紙張大小皆可在此找到對應的設定。
```csharp
PrinterSettings settings = new PrinterSettings()
{
PrinterName = "Microsoft XPS Document Writer"
};
foreach (System.Drawing.Printing.PaperSize printerPaperSize in settings.PaperSizes)
{
// printerPaperSize.RawKind 流水號
// printerPaperSize.PaperName A4 之類的 PaperName
// printerPaperSize.Width 寬
// printerPaperSize.Height 高
}
```
以下為各個 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 |
知道 PaperSize 大小，就可用以下程式碼修正浮水印圖片背景的空白部分，需注意如果 PaperSize 為橫向，傳入`width` 和 `height` 要對調。
```csharp
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;
}
```
如果需要用程式產生文字圖片，可用以下程式碼：
```csharp
public Image DrawText(string text, Font font, Color textColor, Color backColor, int width, int height) {
// 建立一個指定寬度和高度的 Bitmap 物件作為圖像容器 //創立一個指定寬度和高度的位圖圖象
Image img = new Bitmap(width, height);
using (Graphics drawing = Graphics.FromImage(img)) {
// 計算文字在圖像容器中的座標
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));
// 將圖形逆時針旋轉 45 度
drawing.RotateTransform(-45);
drawing.TranslateTransform(-(x + (textSize.Width / 2)), -(y + (textSize.Height / 2)));
// 在圖像容器上清除一個矩形，以背景顏色填充
drawing.Clear(backColor);
// 建立一個實心筆刷，用於繪製文字
Brush textBrush = new SolidBrush(textColor);
drawing.DrawString(text, font, textBrush, x, y);
drawing.Save();
return img;
}
}
```
:::info
有關圖片旋轉的邏輯，可以參考[C# 使用 GDI+ 實現新增中心旋轉(任意角度)的文字](https://www.itread01.com/article/1523253671.html)。
:::
## 使用 EPPlus 產生帶有浮水印的 Excel
下面是使用 EPPlus 產生帶有浮水印的 Excel 的範例程式碼，其中 `watermark` 型別為 `Image`。
```csharp
sheet.HeaderFooter.OddHeader.InsertPicture(watermark, PictureAlignment.Centered);
sheet.BackgroundImage.Image = watermark;
```
:::warning
此作法可能不適用於 EPPlus 6，因為 `System.Drawing.Common` 在 .NET 6 以後的支援問題，EPPlus 6 刪除 `System.Drawing.Common` 的依賴關係，但我並無關注詳細內容，所以不確定調整方式。
:::
## 使用 NPOI 產生帶有浮水印的 Excel(XLSX)
以我的認知，目前 NPOI 的 API 並不能直接設定 Background Image 和 Header Image，但可以使用 NPOI 提供的較底層的 API 來進行處理。
若要產生帶有浮水印的 Excel，需先了解設定 Background 和 Header 的 Image 會產生怎樣的 XML 結構。以下為相關 XML 節錄：
Sheet 的 XML 相關內容，其中`rId1` 和 `rId2`定義在 `\xl\worksheets_rels\{Sheet Name}.xml.rels`。
```xml
<!-- Header Image -->
<headerFooter><oddHeader><![CDATA[&C&G]]></oddHeader></headerFooter><legacyDrawingHF r:id="rId2"/>
<!-- Backround Image -->
<picture r:id="rId1"></picture>
```
{Sheet Name}.xml.rels
```xml
<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 會用 `o:relid="rId1"` 關聯圖片 `rId1` 的定義位置在 `\xl\drawings\_rels\vmlDrawing1.vml.rels` 裡。
```xml
<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>
```
vmlDrawing1.vml.rels 內容如下：
```xml
<?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>
```
原本知道這些資訊就可以用來拼湊設定浮水印的程式碼，但是這時會遇到一個問題，內建的 `XSSFVMLDrawing` 是用來建立 `Comment` 的，因此產生的結構和所需的不同，需要自行定義。
```csharp
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);
}
}
```
最後我們可以使用以下程式碼設定浮水印。
```csharp
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)
};
```
###### tags: `.NET` `Excel` `NPOI` `EPPlus`
Sign in with Wallet
Connect another wallet