Filling Cell Drop down

Aspose.Cells Product Family
1

Hi,

Please suggest if any solution for th below issue

I have data in first row 1 to 256 column and I have data in the second row 2 to column 50,and I have to fill the drop down with two rows of data (A1:AX2),

but if the pass the valfactor formula as A1:AX2 too it is taking the data like first row A1: AX1 and Second row like A2:AX2 ,data from AY1 to IV1 is missing.

please suggest some solution to capture the entire data.

Thanks,

M.Geetha

2
Hi,

I could not understand you completely. Could you elaborate it more and provide sample files and screen shots.
Moreover, it might be MS Excel's behavior. Also, there is no Filling cell drop down feature available in Aspose.Cells, so, there would be no direct way to do it or accordingly and you have to manually set the formulas or copy the formulas accordingly using Cell.Formula property.

By the way, we have setting shared formula feature that my help you a bit to acheive your requiremetns, please see the document for your reference on how it works:
http://www.aspose.com/docs/display/cellsnet/Setting+Shared+Formula


Thank you.



3

Sorry for the inconvenience.

In the snapshot attached there is data Testclient2_490 in HZ5 column from A4 column.But I am able fill the drop down only TestClient2_234 from TestClient2_235 is missing till TestClient2_256 is missing again it start from TestClient2_257 from the next row and end till TestClient2_490.

i.e the second row where it end the first row data also it will end in the same column.

4

Hi,


We request your to kindly provide the following sample files:

1) Your current Excel file as per your screen shot.
2) Your expected Excel file that you need to produce.

Also kindly provide the following:

How could you get your expected Excel file with your desired list of drop down formulas in MS Excel, give us complete process on how could you do this in MS Excel to achieve your desired needs.

We will check it soon.

Thank you.
5

ok Fine Any option to remove blank space in drop down.

Please refer snapshot

My code is like below.

MyBase.WorkBook.Worksheets(0).Cells(irow, gdpicol).Value = groupDefine("Name").ToString()

AsposeCellHelper.Helper.FormatCell(MyBase.WorkBook.Worksheets(0).Cells(irow, gdpicol), AsposeCellHelper.Helper.FormatType.Label_H1, True)

Dim valFactor As Aspose.Cells.Validation = WorkSheet.Validations(WorkSheet.Validations.Add())

valFactor.Type = Aspose.Cells.ValidationType.List

valFactor.Operator = Aspose.Cells.OperatorType.None

valFactor.InCellDropDown = True

valFactor.AreaList.Remove("")

valFactor.ShowError = True

valFactor.AlertStyle = Aspose.Cells.ValidationAlertType.Stop

valFactor.ErrorTitle = "Error"

valFactor.ErrorMessage = "Please select a Group name from the list"

Dim firstCell As Aspose.Cells.Cell

Dim lastCell As Aspose.Cells.Cell

For Each dr As DataRow In columndetail.Rows

If groupDefine("GroupDefinePk") = dr("GroupDefpk") Then

'firstCell = MyBase.WorkBook.Worksheets(0).Cells.Rows(dr("intfirstrow")).GetCellOrNull(dr("intfirstcol"))

firstCell = MyBase.WorkBook.Worksheets(0).Cells(dr("intfirstrow"), dr("intfirstcol"))

' lastCell = MyBase.WorkBook.Worksheets(0).Cells.Rows(dr("intlastrow")).GetCellOrNull(dr("intlastcol"))

lastCell = MyBase.WorkBook.Worksheets(0).Cells(dr("intlastrow"), dr("intlastcol"))

Exit For

End If

Next

valFactor.Formula1 = String.Format("={0}:{1}", firstCell, lastCell)

Dim area As New Aspose.Cells.CellArea

area.StartColumn = gdpicol

area.EndColumn = gdpicol

area.StartRow = irow

area.EndRow = ExcelImport.Helper.MaxRowIndex

valFactor.AreaList.Add(area)

6

Hi,


“Any option to remove blank space in drop down.”
Well, you need to check the source range that you are setting using Validation.Formula1 attribute for your data list validation type, it should not include blank cells/data in the range. You have to adjust your range accordingly by yourself to exclude those blank areas in the source range, otherwise you will get blank spaces in the data validation drop downs as this MS Excel’s behavior.

For reference on Data Validations, please see the topic for your reference:
http://www.aspose.com/docs/display/cellsnet/Data+Filtering+and+Validation

Thank you.


7

Hi,

Thanks for your posting and using Aspose.Cells.

Firstly, for List validation, only single row/column can be set and takes effect. You can confirm this in ms excel manually.

Secondly, as you said, you put the reference as A1:AX2, so the end column is AX and all columns after AX will not be used for the validation. It is same with what you can get when creating the validation in ms excel manually. So it is user’s responsibility to make the reference of formula cover all values he wants to use.

If your issue is different from what we described, please create the validation manually in ms excel and send it to us so we can provide you solution to create it by Cells APIs.