關於OpenXml SpreadSheet列寬根據內容的Auto-suitability，openxml刪除sheet
因為之前接到的一個需求，讓excel的寬度自動適應。所以最近一直在看Excel相關內容，從結構到.net的兩個類庫OpenXml和Office.Interop.Excel,再到一些具體的使用。
這次學習花費了很多東西，主要陷入了兩個誤區，一個是一開始先入為主的認為Openxml中微軟肯定有解決方案（我以為功能很簡單，而且Interop.Excel是有解決方法的，但是實際上並沒有），另一個在style.xml裡面陷入誤區了（很想當然的認為了自動列寬應該在stylesheet.xml裡面），結果好幾天都是毫無頭緒。好心塞。。。
後來終於找到了可愛的Columns類和Column類，然而。。。仔細閱讀，逐個屬性、方法的檢查之後，居然沒有解決方案，Width屬性要給具體值（然而當初腦補的有個什麼AutoFit()這種名字的方法。。。too naive）。
後來在StackOverflow上找到了半個答案。大概意思就是：BestFit 屬性是信息屬性 (可能由 Excel 優化)。開發者仍然需要為該列提供寬度。這意味著你必須實際計算列寬度，根據單元格的內容。打開 XML SDK 並不會自動做寬度匹配。
根據官方的文檔中BestFit屬性給出最後的解決方案如下：
首先得到數據源中每一列的最大值。
列寬單位：一個列寬單位等於一個常規樣式中一個字符的寬度，excel裡面用像素和寬度來共同描述寬度。
至於常規樣式。。。（工具---選項---常規中的標准字體(當然與字大小也有關)）
上面中間的就是
順便說一下像素和厘米的換算吧 這裡還要引入一個像素精度的概念dpi，如果顯示器的像素精度是96dpi，其實就是96像素每英寸。像素精度是由顯示設備的分辨率來的。下面放個截圖（磅是行高單位） PS：鼠標上的DPI就是這個概念
在常規樣式字體下，列寬度的值是根據0，1，2，...，9這是個字符的平均值或最大值（OpenXml官方文檔上給的公式是最大值，網上有人說平均值，具體還要再查一下）來計算的。 每個單元格有 4 個像素的邊距填充 (每側兩個)，再加上 1 像素填充的網格線。
列寬度 = Truncate([{字符數} * {最大數字寬度像素} + {4+1個像素}]/{最大數字寬度}*256)/256
[示例: 使用宋體字體為例，11號字體大小的最大單個字符寬度是7個像素在96 dpi時。事實上，每個數字在設置為此字體時是相同的寬度。因此，如果值的寬度是 8 個字符寬，列寬的值也就是 Truncate([8*7+5]/7*256)/256 = 8.7109375個字符寬度]
要轉化為列寬度的值在運行時 (以像素為單位表示) 的文件中的寬度值，請使用此公式:
= Truncate (((256 * {列寬度} + Truncate(128 / {最大數字寬度})) / 256) * {最大數字寬度})
[示例: 如上使用同一示例，計算將是 Truncate(((256*8.7109375+Truncate(128/7))/256)*7) = 61 像素。]
若要將像素轉換為字符數，請使用此公式:
= Truncate(({像素數}-5) / {最大數字寬度像素} * 100 + 0.5) / 100
最後給出一個Demo
![]()
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using DocumentFormat.OpenXml;
5 using DocumentFormat.OpenXml.Packaging;
6 using DocumentFormat.OpenXml.Spreadsheet;
7
8 namespace OpenXmlGenerateExcelTest
9 {
10 class Program
11 {
12 static void Main()
13 {
14 CreateSpreadSheet();
15 }
16
17 private static void CreateSpreadSheet()
18 {
19 string fileName = "X:\\01.xlsx";
20 string sheetName = "測試表格sheet—1";
21 using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
22 {
23 spreadSheet.AddWorkbookPart();
24 spreadSheet.WorkbookPart.Workbook = new Workbook();
25
26 WorksheetPart worksheetPart1 = spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
27 worksheetPart1.Worksheet = new Worksheet();
28 SheetData sheetData = new SheetData();
29 ProductData(sheetData);
30 worksheetPart1.Worksheet.AppendChild(AutoFit(sheetData));
31 worksheetPart1.Worksheet.AppendChild(sheetData);
32
33 spreadSheet.WorkbookPart.WorksheetParts.ElementAt(0).Worksheet.Save();
34
35 spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets());
36 spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild(new Sheet()
37 {
38 Id = spreadSheet.WorkbookPart.GetIdOfPart(spreadSheet.WorkbookPart.WorksheetParts.First()),
39 SheetId = 1,
40 Name = sheetName
41 });
42
43 spreadSheet.WorkbookPart.Workbook.Save();
44 }
45 }
46
47 private static void ProductData(SheetData sheetData)
48 {
49 for (uint rowIndex = 1; rowIndex < 5; rowIndex++)
50 {
51 Row row = new Row() { RowIndex = rowIndex };
52 for (char cellIndex = 'A'; cellIndex < 'F'; cellIndex++)
53 {
54 Cell cell = new Cell();
55 string innerText = "12234433433";
56 cell.CellValue = new CellValue(innerText);
57 cell.DataType = new EnumValue<CellValues>(CellValues.String);
58 row.Append(cell);
59 }
60 for (char cellIndex = 'F'; cellIndex < 'K'; cellIndex++)
61 {
62 Cell cell = new Cell();
63 string innerText = "12234";
64 cell.CellValue = new CellValue(innerText);
65 cell.DataType = new EnumValue<CellValues>(CellValues.String);
66 row.Append(cell);
67 }
68 sheetData.Append(row);
69 }
70 }
71
72 private static Columns AutoFit(SheetData sheetData)
73 {
74 var maxColWidth = GetMaxCharacterWidth(sheetData);
75
76 Columns columns = new Columns();
77
78 double maxWidth = 7;
79 foreach (var item in maxColWidth)
80 {
81 /*三種單位寬度公式*/
82 double width = Math.Truncate((item.Value * maxWidth + 5) / maxWidth * 256) / 256;
83 double pixels = Math.Truncate(((256 * width + Math.Truncate(128 / maxWidth)) / 256) * maxWidth);
84 double charWidth = Math.Truncate((pixels - 5) / maxWidth * 100 + 0.5) / 100;
85
86 Column col = new Column() { BestFit = true, Min = (UInt32)(item.Key + 1), Max = (UInt32)(item.Key + 1), CustomWidth = true, Width = (DoubleValue)width };
87 columns.Append(col);
88 }
89 return columns;
90 }
91
92 private static Dictionary<int, int> GetMaxCharacterWidth(SheetData sheetData)
93 {
94 Dictionary<int, int> maxColWidth = new Dictionary<int, int>();
95 var rows = sheetData.Elements<Row>();
96 foreach (var r in rows)
97 {
98 var cells = r.Elements<Cell>().ToArray();
99 for (int i = 0; i < cells.Length; i++)
100 {
101 var cell = cells[i];
102 var cellValue = cell.CellValue == null ? string.Empty : cell.CellValue.InnerText;
103 var cellTextLength = cellValue.Length;
104 if (maxColWidth.ContainsKey(i))
105 {
106 var current = maxColWidth[i];
107 if (cellTextLength > current)
108 {
109 maxColWidth[i] = cellTextLength;
110 }
111 }
112 else
113 {
114 maxColWidth.Add(i, cellTextLength);
115 }
116 }
117 }
118 return maxColWidth;
119 }
120 }
121 }
View Code
