Using Range object to display dropdownlist


#1

I have an Aspose.Excel.Range object. How can use this Range object as the source of a column in my woksheet?. In fact I need to create a list control using the range object.

Thanks in advance

Prince P Thomas

prinspthomas@hotmail.com


#2

You can try the autofilter feature. Please check http://www.aspose.com/Wiki/default.aspx/Aspose.Excel/DataFilteringAndValidation.html for reference.


#3

Thanks for your reply.

When I tried AutoFilter like

myExcel.Worksheets(0).AutoFilter.Range = "C1:C1"

I got a dropdown list cell C1. But how to set the options in the list?. Also I need the options dynamic.

Following is the code I used to create the Range

Dim sheet As Worksheet

sheet = myExcel.Worksheets(1)

sheet.Cells.ImportDataTable(getDateTable, False, 0, 0)

' where getDateTable is a function which returns a Datatable [see below]

sheet.Name = "Dates"

Dim myRange As Range = myExcel.Worksheets(1).Cells.CreateRange("A1", "A7")

myRange.Name = "Days"

'------------ ------------ --------------- -------------

Private Function getDateTable() As DataTable

Dim dt As New DataTable

Dim dcName As New DataColumn("Day", GetType(String))

dt.Columns.Add(dcName)

Dim dr As DataRow

Dim arrDays() As String

arrDays = New String() {"Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"}

For Each strDay As String In arrDays

dr = dt.NewRow()

dr("Day") = strDay

dt.Rows.Add(dr)

Next

Return dt

End Function

'------ ------------ ---------- -----------

I want the items in the range I created above as the options in a listcontrol in my worksheet. Is it possible?

Thanks

prins