//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); var sheet = workbook.ActiveSheet; object[,] data = new object[,]{ {"Name", "City", "Birthday", "Eye color", "Weight", "Height"}, {"Richard", "New York", new DateTime(1968, 6, 8), "Blue", 67, 165}, {"Nia", "New York", new DateTime(1972, 7, 3), "Brown", 62, 134}, {"Jared", "New York", new DateTime(1964, 3, 2), "Hazel", 72, 180}, {"Natalie", "Washington", new DateTime(1972, 8, 8), "Blue", 66, 163}, {"Damon", "Washington", new DateTime(1986, 2, 2), "Hazel", 76, 176}, {"Angela", "Washington", new DateTime(1993, 2, 15), "Brown", 68, 145} }; sheet.Range["A1:F7"].Value = data; sheet.Range["A:F"].ColumnWidth = 15; sheet.Range["A:F"].Group(); sheet.Range["A:B"].Group(); sheet.Range["D:E"].Group(); //get group information and collapes some group. var groupInfo = sheet.Outline.ColumnGroupInfo; var rowInfo = new Dictionary<int, int>(); foreach (var item in groupInfo) { if (item.Children != null) { foreach (var childItem in item.Children) { if (childItem.StartIndex > 2) { childItem.Collapse(); } if (childItem.Level == 2) { rowInfo.Add(childItem.StartIndex, childItem.EndIndex); } } } } System.Text.StringBuilder builder = new System.Text.StringBuilder(); foreach (var item in rowInfo) { builder.Append("column " + (item.Key + 1) + " to column " + (item.Value + 1) + ", "); } sheet.Range["A15"].Value = "The columns where the group level is 2 are: " + builder.ToString(); sheet.Range["A15"].RowHeight = 25; sheet.Range["A15"].Font.Color = Color.Red; sheet.Range["A15"].Font.Size = 15; // Save to an excel file workbook.Save("GetColumnInfo.xlsx");
' Create a new Workbook Dim workbook As New Workbook Dim sheet = workbook.ActiveSheet Dim data As Object(,) = New Object(,) { {"Name", "City", "Birthday", "Eye color", "Weight", "Height"}, {"Richard", "New York", #1968-6-8#, "Blue", 67, 165}, {"Nia", "New York", #1972-7-3#, "Brown", 62, 134}, {"Jared", "New York", #1964-3-2#, "Hazel", 72, 180}, {"Natalie", "Washington", #1972-8-8#, "Blue", 66, 163}, {"Damon", "Washington", #1986-2-2#, "Hazel", 76, 176}, {"Angela", "Washington", #1993-2-15#, "Brown", 68, 145}} sheet.Range("A1:F7").Value = data sheet.Range("A:F").ColumnWidth = 15 sheet.Range("A:F").Group() sheet.Range("A:B").Group() sheet.Range("D:E").Group() Dim groupInfo = sheet.Outline.ColumnGroupInfo Dim rowInfo = New Dictionary(Of Integer, Integer)() For Each item In groupInfo If item.Children IsNot Nothing Then For Each childItem In item.Children If childItem.StartIndex > 2 Then childItem.Collapse() End If If childItem.Level = 2 Then rowInfo.Add(childItem.StartIndex, childItem.EndIndex) End If Next End If Next Dim builder As New System.Text.StringBuilder For Each item In rowInfo builder.Append("row " & (item.Key + 1) & " to row " & (item.Value + 1) & ", ") Next With sheet.Range!A15 .Value = "The rows where the group level is 2 are: " & builder.ToString() .RowHeight = 25 .Font.Color = Color.Red .Font.Size = 15 End With ' save to an excel file workbook.Save("GetColumnInfo.xlsx")