To write into excel

Hi Team,

I have an output of as follows in this excel,

But I need to get in the following format,

Can anyone let me know, what logic can I use here.

1 Like


Simple solution steps are :slight_smile:

  1. Read the data and store into data table.
  2. Use for each row and iterate through all the data inside
  3. Use If condition to skip the null values
  4. Create a int variable as count and another int variable as column
  5. Use column to define the particular column to write data using the code below, means increment the column value by 1 everytime and you will get the string form of the column and count is to give the number of row. It has to be incremented everytime by 1. This is the tricky part you need to do…

and the code to convert number to alphabet (Column to write data)

     For c As Integer = 1 To CapcUsedColumnsRange
        Dim str As String = Nothing

        If c <= 26 Then
            str = Convert.ToChar(64 + c)
        ElseIf c > 26 And c <= 52 Then
            str = Convert.ToChar(65) + Convert.ToChar(64 + c - 26)
        ElseIf c > 52 Then
            str = Convert.ToChar(66) + Convert.ToChar(64 + c - 52)
        End If