lotus-noteslotus-dominolotusscript

Multi-values needs to be exported to excel to different rows using lotus script


I was trying to export the Multi-valued field but couldn't able to achieve it. i tried Ubound to export but the scenario is I need the 1st value in 1st row and 2nd value in 2nd row vice versa. Please help me to solve this. I'm using excel application to export the data.

Thanks in advance.

ForAll b In fieldList 
If UBound(doc.getitemvalue(b)) <1 Then 

worksheet.cells(i,j).value =doc.getitemvalue(CStr(b)) 

Else 

'Join(doc.getitemvalue(CStr(b)),Chr(10)) 

worksheet.cells(i,j).value =Join(doc.getitemvalue(CStr(b)),Chr(10)) 

End If 
End Forall

Solution

  • Lets assume the following document:

    Fieldname Value 0 Value 1 Value 2
    FirstField Value0-First
    SecondField Value0-Second Value1-Second Value2-Second
    ThirdField Value0-Third Value1-Third

    Solution 1: If you want the result to look like this

    FirstField SecondField ThirdField
    Value0-First Value0-Second Value0-Third
    Value1-Second Value1-Third
    Value2-Second

    Then you use the following code:

    cellY = 2
    cellX = 1
    Forall fieldName in fieldNameList
      For y = 0 to ubound( doc.GetitemValue( fieldName ) )
        worksheet.cells(cellY + y, cellX).value = doc.GetitemValue( fieldName )(y)
      Next
      cellX = cellX + 1
    End Forall
    

    Solution 2: If you want the result to look like this

    Fieldname Value 0 Value 1 Value 2
    FirstField Value0-First
    SecondField Value0-Second Value1-Second Value2-Second
    ThirdField Value0-Third Value1-Third

    Then you use the following code:

    cellY = 1
    cellX = 2
    Forall fieldName in fieldNameList
      For x = 0 to ubound( doc.GetitemValue( fieldName ) )
        worksheet.cells(cellY, cellX+x).value = doc.GetitemValue( fieldName )(x)
      Next
      cellY = cellY + 1
    End Forall
    

    Solution 3: If you want the result to look like this

    All fields
    FirstField
    Value0-First
    SecondField
    Value0-Second
    Value1-Second
    Value2-Second
    ThirdField
    Value0-Third
    Value1-Third

    Then you use the following code:

    cellY = 1
    cellX = 1
    Forall fieldName in fieldNameList
      worksheet.cells(cellY, cellX).value = fieldName
      For y = 0 to ubound( doc.GetitemValue( fieldName ) )
        cellY = cellY + 1
        worksheet.cells(cellY, cellX).value = doc.GetitemValue( fieldName )(y)
      Next
      cellY = cellY + 1
    End Forall