While i was searching for a faster way to export datatable to Excel file, i have found this article ([
^]). Everything was fine, except the getting column letters part. Then i decided to write a small code to achieve this.
Small demo is here ([
^])
First, you need to set MAX_COLUMNS variable:
Excel 2003
MAX_COLUMNS = 256
Excel 2007
MAX_COLUMNS = 16384
Then create an XML file
Dim base As String = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z"
Dim baseArray() As String = base.Split(",")
'temp string to hold column letter
Dim letter As String = vbNullString
'temp letter has:
'1 letter at level 1
'2 letters at level 2
'3 letters at level 3
Dim level As Integer = 1
Dim total As Long = 0
Dim i, j, k As Long
Dim xsettings As New XmlWriterSettings
xsettings.Encoding = Encoding.UTF8
xsettings.Indent = True
xsettings.NewLineOnAttributes = False
xsettings.IndentChars = vbTab
Try
Dim xw As XmlWriter = XmlWriter.Create([file path], xsettings)
xw.WriteStartDocument()
xw.WriteStartElement("columns")
xw.WriteAttributeString("version", lstExcelVersion.Text)
'set column letter based on level
Do
Select Case level
Case 1
For i = 0 To UBound(baseArray)
letter = String.Format("{0}", baseArray(i))
xw.WriteStartElement("column")
xw.WriteAttributeString("index", total + 1)
xw.WriteAttributeString("letter", letter)
xw.WriteEndElement()
total += 1
Next
Case 2
For i = 0 To UBound(baseArray)
For j = 0 To UBound(baseArray)
letter = String.Format("{0}{1}", baseArray(i), baseArray(j))
xw.WriteStartElement("column")
xw.WriteAttributeString("index", total + 1)
xw.WriteAttributeString("letter", letter)
xw.WriteEndElement()
total += 1
If lstExcelVersion.SelectedIndex = 0 And total = MAX_COLUMNS Then Exit Do
Next
Next
Case 3
For i = 0 To UBound(baseArray)
For j = 0 To UBound(baseArray)
For k = 0 To UBound(baseArray)
letter = String.Format("{0}{1}{2}", baseArray(i), baseArray(j), baseArray(k))
xw.WriteStartElement("column")
xw.WriteAttributeString("index", total + 1)
xw.WriteAttributeString("letter", letter)
xw.WriteEndElement()
total += 1
If lstExcelVersion.SelectedIndex = 1 And total = MAX_COLUMNS Then Exit Do
Next
Next
Next
End Select
level += 1
Loop Until level = 4
xw.WriteEndElement()
xw.WriteEndDocument()
xw.Flush()
xw.Close()
MsgBox("Creation of xml file completed")
Catch ex As Exception
Throw ex
End Try
Then get column letter by index/index by column letter
Private Function GetColumn(ByVal by As String, ByVal value As String) As String
Dim returnType As String = vbNullString
Dim returnValue As String = vbNullString
Dim query As String = String.Format("column[@{0}='{1}']", by, value)
Select Case by
Case "index" : returnType = "letter"
Case "letter" : returnType = "index"
End Select
Dim xdoc As New XmlDocument()
xdoc.Load([file path])
'select root of the document
Dim root As XmlElement = xdoc.DocumentElement
'select the column
Dim xn As Xml.XmlNode = root.SelectSingleNode(query)
If Not (xn Is Nothing) Then
returnValue = xn.Attributes(returnType).InnerText
End If
root = Nothing
xdoc = Nothing
Return returnValue
End Function
