关于OpenXmlSpreadSheet列宽根据内容的Auto-suitability

  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                     else113                     {114                         maxColWidth.Add(i, cellTextLength);115                     }116                 }117             }118             return maxColWidth;119         }120     }121 }