openXML写Excel列组合

重要代码：

 1  DOS.SheetData sheetData = new DOS.SheetData();
 2 
 3 
 4                 DOS.Columns columns = new DOS.Columns();
 5                 DOS.Column column = null;
 6                 uint min = 1;
 7 
 8                 for (int ind = 1; ind <= 26; ind += 5)
 9                 {
10                     if (ind < 5)
11                         continue;
12                     column = columns.AppendChild<DOS.Column>(new DOS.Column());
13 
14 
15                     if (ind - 5 <= 0)
16                     {
17                         min = 1;
18                     }
19                     else
20                     {
21                         min = (uint)ind - 4;
22                     }
23                     column.Min = new DOX.UInt32Value(min);
24                     uint max = (uint)ind - 1;
25                     if (max <= 0)
26                         max = 1;
27                     column.Max = new DOX.UInt32Value(max);
28                     column.OutlineLevel = 1;
29                 }
30 
31                 DOS.Worksheet worksheet = worksheetPart.Worksheet = new DOS.Worksheet(columns,sheetData);

如果存在多个列组合，列组合之间的列不能连续，否则认为是一个列组合

完整代码：

 1        /// <summary>
 2         /// 列组
 3         /// </summary>
 4         /// <param name="fileName"></param>
 5         /// <param name="sheetName"></param>
 6         public static void CreateColumnGroup(string fileName,string sheetName)
 7         {
 8             using (pkg.SpreadsheetDocument document = pkg.SpreadsheetDocument.Create(fileName, DOX.SpreadsheetDocumentType.Workbook))
 9             {
10                 pkg.WorkbookPart workbookPart = document.AddWorkbookPart();
11                 DOS.Workbook workbook = workbookPart.Workbook = new DOS.Workbook();
12 
13                 pkg.SharedStringTablePart sharedStringTablePart = workbookPart.AddNewPart<pkg.SharedStringTablePart>();
14 
15                 pkg.WorksheetPart worksheetPart = workbookPart.AddNewPart<pkg.WorksheetPart>();
16                 DOS.SheetData sheetData = new DOS.SheetData();
17 
18 
19                 DOS.Columns columns = new DOS.Columns();
20                 DOS.Column column = null;
21                 uint min = 1;
22 
23                 for (int ind = 1; ind <= 26; ind += 5)
24                 {
25                     if (ind < 5)
26                         continue;
27                     column = columns.AppendChild<DOS.Column>(new DOS.Column());
28 
29 
30                     if (ind - 5 <= 0)
31                     {
32                         min = 1;
33                     }
34                     else
35                     {
36                         min = (uint)ind - 4;
37                     }
38                     column.Min = new DOX.UInt32Value(min);
39                     uint max = (uint)ind - 1;
40                     if (max <= 0)
41                         max = 1;
42                     column.Max = new DOX.UInt32Value(max);
43                     column.OutlineLevel = 1;
44                 }
45 
46                 DOS.Worksheet worksheet = worksheetPart.Worksheet = new DOS.Worksheet(columns,sheetData);
47 
48                 DOS.Sheets sheets = document.WorkbookPart.Workbook.AppendChild<DOS.Sheets>(new DOS.Sheets());
49 
50                 DOS.Sheet sheet = new DOS.Sheet()
51                 {
52                     Id = workbookPart.GetIdOfPart(worksheetPart),
53                     SheetId = 1,
54                     Name = sheetName
55                 };
56                 sheets.Append(sheet);
57 
58 
59 
60 
61                 DOS.Row row;
62 
63                 for (uint rowIndex = 1; rowIndex < 100; rowIndex++)
64                 {
65                     row = new DOS.Row()
66                     {
67                         RowIndex = new DOX.UInt32Value(rowIndex)
68                     };
69 
70                     sheetData.Append(row);
71 
72                     Random random = null;
73 
74                     DOS.Cell cell = null;
75                     for(int colindex=0;colindex<=25;colindex++)
76                     {
77                         random = new Random((int)DateTime.Now.Millisecond * (int)rowIndex);
78                         string text = random.Next(1, 2000).ToString();
79 
80                         int textIndex = InsertSharedStringItem(text, sharedStringTablePart);
81 
82                         cell = new DOS.Cell()
83                         {
84                             CellReference = colindex.ToExcelName() + rowIndex.ToString(),
85                             CellValue = new DOS.CellValue(textIndex.ToString())
86                         };
87                         cell.DataType = new DOX.EnumValue<DOS.CellValues>(DOS.CellValues.SharedString);
88                         row.AppendChild(cell);
89                     }
90 
91                 }
92 
93                 worksheet.Save();
94 
95             }
96         }

 

posted on 2018-09-10 11:36  天之梅子  阅读(275)  评论(0)  编辑  收藏  举报