Search code examples
vb.netexport-to-excel

Count columnName startwith specific value

ldtData.Columns.IndexOf("1-")

Is this correct if I want to select the columnName start from '1-' , '2-' , '3-' .. '12-' ?

By the way, how can I count that column starting with '1-' , '2-' , '3-' .. '12-' ?

enter image description here

Image above show SQL result. '1-' for Jan, '2-' for Feb. What is the key/method should I use to count and differentiate columnName.

Solution

  • I get the solution.

    Dim liTotal(ldtData.Columns.Count - 1) As Integer
Dim cnt1, cnt2, cnt3, cnt4, cnt5, cnt6, cnt7, cnt8, cnt9, cnt10, cnt11, cnt12 As Integer
'Count row by month to set as colspan
For col As Integer = 4 To ldtData.Columns.Count - 1
If ldtData.Columns(col).ColumnName.Substring(0, 1) = "1" Then
cnt1 = cnt1 + 1
ElseIf ldtData.Columns(col).ColumnName.Substring(0, 1) = "2" Then
cnt2 = cnt2 + 1
ElseIf ldtData.Columns(col).ColumnName.Substring(0, 1) = "3" Then
cnt3 = cnt3 + 1
ElseIf ldtData.Columns(col).ColumnName.Substring(0, 1) = "4" Then
cnt4 = cnt4 + 1
ElseIf ldtData.Columns(col).ColumnName.Substring(0, 1) = "5" Then
cnt5 = cnt5 + 1
ElseIf ldtData.Columns(col).ColumnName.Substring(0, 1) = "6" Then
cnt6 = cnt6 + 1
ElseIf ldtData.Columns(col).ColumnName.Substring(0, 1) = "7" Then
cnt7 = cnt7 + 1
ElseIf ldtData.Columns(col).ColumnName.Substring(0, 1) = "8" Then
cnt8 = cnt8 + 1
ElseIf ldtData.Columns(col).ColumnName.Substring(0, 1) = "9" Then
cnt9 = cnt9 + 1
ElseIf ldtData.Columns(col).ColumnName.Substring(0, 2) = "10" Then
cnt10 = cnt10 + 1
ElseIf ldtData.Columns(col).ColumnName.Substring(0, 2) = "11" Then
cnt11 = cnt11 + 1
ElseIf ldtData.Columns(col).ColumnName.Substring(0, 2) = "12" Then
cnt12 = cnt12 + 1
End If
Next

    The way is I declare all count of month (cnt1,cnt2, ...). Then, I do loop and count for each month, which is if the data found "1" then it will count as cnt1(Jan) in my table.