About Auto-suitability of OpenXml SpreadSheet column width based on content, openxml deletes sheet

About Auto-suitability of OpenXml SpreadSheet column width based on content, openxml deletes sheet
Because of a previous requirement, the excel width is automatically adapted. So recently I have been reading Excel-related content, from the structure to the. net two class libraries OpenXml and Office. Interop. Excel, and then to some specific use. I spent a lot of money on this study and got into two misunderstandings. One is that Microsoft must have a solution in Openxml (I think the function is very simple, and Interop. excel has a solution, but it does not actually), the other is in style. xml is in misunderstanding (I think of course the automatic column width should be in stylesheet. in xml. Good stuff... Then I finally found the cute Columns Class and Column class,... After carefully reading and checking attributes and Methods one by one, there is no solution, and the Width attribute should be given a specific value (however, what was previously called AutoFit () this method of name... Too naive ). Then I found half an answer on StackOverflow. BestFit attributes are information attributes (may be optimized by Excel ). The developer still needs to provide the width for this column. This means that you must calculate the column width based on the cell content. Opening the xml sdk does not automatically perform width matching. According to the BestFit attribute in the official document, the final solution is as follows: first, obtain the maximum value of each column in the data source. Column width unit: the unit of a column width is equal to the width of a character in a regular style. In excel, pixels and width are used to describe the width. For general style... (Tool-Option-Standard font in general (also related to the word size, of course )) the middle of the above is the conversion of pixels and centimeters by the way. Here we also introduce the concept of dpi for Pixel Precision. If the pixel precision of the monitor is 96 dpi, it is actually 96 pixels per inch. The pixel precision is determined by the resolution of the display device. Put the following (LBS is the Row Height unit) PS: the DPI on the mouse is the concept in the regular style font, the column width value is based on 0, 1, 2 ,..., 9. This is the average or maximum value of a character (the formula given in the OpenXml official document is the maximum value. Some people on the Internet say that the average value should be checked again. Each cell has a padding of 4 pixels (two at each side), plus a grid line filled with 1 pixel. Column width = Truncate ([{Character Count} * {maximum digit width pixel} + {4 + 1 pixel}]/{maximum digit width} * 256)/256 [Example: take the font as an example. The maximum width of a single character in the font 11 is 7 pixels at 96 dpi. In fact, each number is set to the same width for this font. Therefore, if the value width is 8 characters wide, the value of the column width is Truncate ([8*7 + 5]/7*256) /256 = 8.7109375 characters width] use this formula to specify the width of the value to be converted to the column width in the runtime (in pixels) file: = Truncate (256 * {column width} + Truncate (128/{maximum digit width})/256) * {maximum digit width}) [Example: in the same example as above, the calculation will be Truncate (256*8.7109375 + Truncate (128/7)/256) * 7) = 61 pixels.] To convert pixels to characters, use this formula: = Truncate ({number of characters}-5)/{maximum number width pixel} * 100 + 0.5) /100 finally provides a 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 = "test table 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. addNewPar T <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 spre AdSheet. 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 C Ell (); 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 Columns = new Columns (); 77 78 double maxWidth = 7; 79 Each (var item in maxColWidth) 80 {81/* Three unit width formulas */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 return maxColWidth; 118} 119} 120}View Code