Gembox Spreadsheet Data Validation

Question

I'm having some trouble applying some Data Validation on an Excel file generated "on-the-fly" that I've been unable to solve. I'm using Gembox Spreadsheet version 3.5 (35.3.40.1025).

The file is being generated using VB.NET and I'm supposed to, for each column, retrieve data from a List of values on another sheet.

The problem is that, although everything seems to be working fine (regarding cell/columns range) the values available on the list are always incrementing, meaning for example that, on cell B1, I have (in this case) both values correct from the source, Cells A1:A2 on the source worksheet.

But on cell B2 (from the main worksheet) the available options are cells A2:A3 (I only have source values on cell A1 and A2). On cell B3 (again on the main worksheet) the source cells are A3:A4 and so forth, when it's supposed to always be using the sources A1:A2.

This is the code I'm using to define this data:

    Private Sub FormatarCells(ByVal lFileColumns As List(Of defs_ficheiro_campo), ByRef excelFile As ExcelFile, ByVal wsDados As ExcelWorksheet)
    Dim wsMain As ExcelWorksheet = excelFile.Worksheets.ElementAt(0)
    Dim oCampoBD As defs_campo_bd
    Dim j As Integer = 0
    Dim val As DataValidation
    Try
        For i As Integer = 0 To lFileColumns.Count - 1
            oCampoBD = New defs_campo_bd
            oCampoBD = defs_campo_bd_mapper.CarregarDefsCampoBDPorId(lFileColumns.ElementAt(i).IdCampoBD)
            If Not String.IsNullOrEmpty(oCampoBD.IdentificadorExportacao) Then
                val = New DataValidation(wsMain, wsMain.Cells.GetSubrangeAbsolute(1, i, LineCount, i).ToString)
                val.Type = DataValidationType.List
                val.Formula1 = "='" & wsDados.Name & "'!" & wsDados.Cells.GetSubrangeAbsolute(0, j, GetLastIndexColumn(wsDados.Columns(j)), j).ToString
                wsMain.DataValidations.Add(val)
                j += 1
            End If
        Next
    Catch ex As Exception
        Throw New Exception(ex.Message)
    End Try
End Sub

From what I can see, all the values are being retrieved correctly. If the Data Validation settings are being applied to a whole column and the source is always the same, why is it that once I get the output file I have the problems mentioned above? Is this a library bug or a code problem?

Best Regards, Celso Santos

### UPDATE ###

I found a way of having the correct data but on a cell-by-cell basis.

    Private Sub FormatarCells(ByVal lFicheiroCampo As List(Of defs_ficheiro_campo), ByRef excelFile As ExcelFile, ByVal wsDados As ExcelWorksheet) ', ByVal rows As Integer)
    Dim wsMain As ExcelWorksheet = excelFile.Worksheets.ElementAt(0)
    'Dim lFicheiroCampo As List(Of defs_ficheiro_campo)
    Dim oCampoBD As defs_campo_bd
    Dim val As DataValidation
    Try
        For row As Integer = 1 To NumeroLinhas ''Por cada linha
            Dim j As Integer = 0
            For col As Integer = 0 To lFicheiroCampo.Count - 1 ''E por cada coluna
                oCampoBD = New defs_campo_bd
                oCampoBD = defs_campo_bd_mapper.CarregarDefsCampoBDPorId(lFicheiroCampo.ElementAt(col).IdCampoBD) ''Obter o campo e respectivos dados
                If Not String.IsNullOrEmpty(oCampoBD.IdentificadorExportacao) Then ''Se tiver validação/origem de dados
                    val = New DataValidation(wsMain, CellRange.RowColumnToPosition(row, col)) ''Obter o ID da célula actual
                    val.Type = DataValidationType.List ''Indicar o tipo de validação (neste caso uma lista/dropdown)
                    val.Formula1 = "='" & wsDados.Name & "'!" & wsDados.Cells.GetSubrangeAbsolute(0, j, GetLastIndexColumn(wsDados.Columns(j)), j).ToString ''Indicar células origem de dados
                    wsMain.DataValidations.Add(val)
                    j += 1
                End If
            Next
        Next
    Catch ex As Exception
        Throw New Exception(ex.Message)
    End Try
End Sub