is there a way or code to convert A1 reference style to C1R1 style in vb.net. or is there a method like ConvertFormula as used in VBA, in vb.NET?

This message was edited by DrMarten at 2006-11-29 10:8:11

---

: is there a way or code to convert A1 reference style to C1R1 style in vb.net. or is there a method like ConvertFormula as used in VBA, in vb.NET?

______________________________________________________________________

Hi,

ZZ is column 702 so it should be enough for you.
I couldn't see one so i created this.>>

It uses a function to convert a string so feel free to use the function in your program.

Function returns a string like.>>

"C28R123" for example for the input of any of these>

"ab123" or "Ab123" or "aB123" or even "AB123".

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim cellRef As String
        'Get Textbox1.text for input.
        cellRef = TextBox1.Text
        'Put result of calculation into Label1.
        Label1.Text = columnRowRef(cellRef)

    End Sub

    Function columnRowRef(ByVal myRef As String) As String

        Dim cellRef, colRowRef, colRef As String
        cellRef = myRef.ToUpper

        Dim len, index, colIndex As Integer
        Dim ref1, ref2 As Integer

        len = cellRef.Length

        'Section that splits the number part off the input to give
        'for example "123" from "BC123"
        For index = 0 To len - 1
            If IsNumeric(cellRef.Substring(index, 1)) = True Then
                colRowRef = "R" & cellRef.Substring(index, len - index)
                Exit For
            End If
        Next

        'Splits the letters off the input to give
        ' for example "BC" from "BC123".
        For index = 0 To len - 1
            If IsNumeric(cellRef.Substring(index, 1)) = True Then
                colRef = cellRef.Remove(index, len - index)
                Exit For
            End If
        Next

        'Works from A through to ZZ only.
        If colRef.Length > 2 Then
            MsgBox("Built to handle A..Z and AA..ZZ only.")
            Exit Function
        End If

        'Section that calculates the column number.
        len = colRef.Length
        Select Case len
            Case 1
                colIndex = Asc(colRef) - 64
                colRef = colIndex.ToString
            Case 2
                ref1 = Asc(colRef.Substring(0, 1)) - 64
                ref2 = Asc(colRef.Substring(1, 1)) - 64
                ref1 = ref1 * 26
                colIndex = ref1 + ref2
                colRef = colIndex.ToString
        End Select

        colRowRef = "C" & colRef & colRowRef
        Return (colRowRef)

    End Function

Regards,

Dr M.

thanks, DrMarten that helped. But do you also have a function or somthing that could convert eg. SUM(A1,B1) or ((A1+B1)/(B2-C2)) or ((A1+B1)-(C1-D2)) to C1R1 style... I know you have to know your current cell or position then either go forward or backward inorder to convert.


Please if you have an idea or a way to do this, please kindly help


thanks.

This message was edited by DrMarten at 2006-12-7 1:55:14

---

: thanks, DrMarten that helped. But do you also have a function or somthing that could convert eg. SUM(A1,B1) or ((A1+B1)/(B2-C2)) or ((A1+B1)-(C1-D2)) to C1R1 style... I know you have to know your current cell or position then either go forward or backward inorder to convert.
:
:
: Please if you have an idea or a way to do this, please kindly help
:
:
: thanks.
_______________________________________________________________________

Hi,


Another challenge eh, great.

If you can put the result of a string into your cell's formula area it will be easier.

By the way is this VBA or another spreadsheet program ( other than Excel ) you are usig C1R1 style in?



Regards,

Dr M.

Hello Dr. Marten,

I am converting xml to excel. Because it will be on a server, I used the free carlos ag excelwriter(cudos to carlos) instead of using excel's objects directly. This is done because of the server does not have excel or office installed (and it will be slow because of the many hits on that server).
so I have be able to successfully create a parser to parse thru the xml the only problem I am having is finding a way to converting the formulas.
if it were vba, I would use the ConvertFormula but my .dll I created is in .NET. and my Parser works very well just cannot get the tags with formula to work because carlos's stuff dosnt support a lot of stuff in excel and it wants C1R1 instead of A1 style(too many of the xmls created dynamically in .asp classic, use A1 style so I cannot really change them). kinda frustrating.


thanks

: is there a way or code to convert A1 reference style to C1R1 style in vb.net. or is there a method like ConvertFormula as used in VBA, in vb.NET?
:
:
:

Hi,
I guess you'll have to implement your own logic for this (if no excel dll is present on your running machine).

This function should look somehow like this:

    Public Function ConvertA2RC(ByVal AForm As String, ByVal CurRow As Integer, ByVal CurCol As Integer) As String
        Dim r, c, Ret As String : r = Nothing : c = Nothing
        If AForm Is Nothing OrElse AForm.Length = 0 OrElse CurRow = 0 OrElse CurCol = 0 Then Return Nothing
        AForm = AForm.ToUpper   'A1, B2, $A1, A$1, $A$1
        For i As Integer = 1 To AForm.Length - 1
            If Char.IsDigit(AForm(i)) Or AForm(i) = "$"c Then r = AForm.Substring(i) : c = AForm.Substring(0, i) : Exit For
        Next
        If r Is Nothing OrElse c Is Nothing Then Throw New Exception("Unsupported A-Form: " & AForm)
        Ret = GenRC(CInt(r.Trim("$"c)), CurRow, r(0) = "$"c, "R"c)
        Ret &= GenRC(ColIndex(c.Trim("$"c)), CurCol, c(0) = "$"c, "C"c)
        Return Ret
    End Function

    Private Function GenRC(ByVal A As Integer, ByVal Cur As Integer, ByVal IsFixed As Boolean, ByVal T As Char) As String
        If IsFixed Then Return T & A
        If A = Cur Then Return T Else Return T & "[" & A - Cur & "]"
    End Function

    Public Function ColIndex(ByVal Col As String) As Integer
        If Col Is Nothing OrElse Col.Length = 0 Then Return 0
        Col = Col.ToUpper
        If Col.Length = 1 Then
            Return AscW(Col(0)) - 64
        Else
            Return (AscW(Col(0)) - 64) * 26 + AscW(Col(1)) - 64
        End If
    End Function

Function ConvertA2RC accepts A1-form address and converts it to R1C1 style.
I did not perform too much tests, but when you have B22:
=B22*$C$23+B$27+$D25
it returns correct answer.
This can convert only basic single addresses. It can not convert ranges (like entire rows, entire columns - 1:1, or A:A etc.. )

Test case:
MsgBox(ConvertA2RC("B22", 23, 2) & "*" & ConvertA2RC("$C$23", 23, 2) & "+" & ConvertA2RC("B$27", 23, 2) & "+" & ConvertA2RC("$D25", 23, 2))


I hope this will inspire you

Pavlin II[/size]

Don't take life too seriously anyway you won't escape alive from it!

Sorry it took a while to post back. I have the complete solution.
I want to say a BIG thank U to PavlinII and Dr Marten.

Here is the solution just incase anyone out there someday needs it.
it will convert any A1 style formula to C1R1.

check it out and make modifications as needed.
thanks,

'#**********************************************************************
Private Function A1StyleToC1R1Style(ByVal A1stringValue As String, ByVal CurRow As Integer, ByVal CurCol As Integer)

Dim charString As String
Dim strLength As Integer
Dim x As Integer
Dim newRCString, tempString As String
Dim newRICI As String

newRCString = Nothing
tempString = Nothing

strLength = A1stringValue.Length
Dim mytemp As String = "-1"

'easy example = sum(A1,B1) or (A1 + B1)
'others include: ((A1 + B1) - ((C1 -D1)/(A2+A1)))

If Not A1stringValue Is Nothing Then
Dim sr As New StringReader(A1stringValue)
Dim y As String

For x = 0 To strLength - 1
y = sr.Read()
charString = ChrW(y)
If (isAlphaOrNumeric(charString) = True) Then
tempString = tempString + charString
Else

If Not tempString Is Nothing Then

If Regex.IsMatch(tempString, "^[a-zA-Z]+[0-9]+$") = True Then
'convert to RICI
newRICI = ConvertA2RC(tempString, CurRow, CurCol)
newRCString = newRCString + newRICI
Else
newRCString = newRCString + tempString
End If

End If
newRCString = newRCString + charString
tempString = Nothing
End If


Next

Return ("=" + newRCString)
Else
Return " "
End If

End Function
'#******************************************************************
Private Function isAlphaOrNumeric(ByVal inputString As String) As Boolean

If Regex.IsMatch(inputString, "[a-zA-Z_0-9]") = True Then

Return True
Else
Return False

End If

End Function
'#******************************************************************
Public Function ConvertA2RC(ByVal AForm As String, ByVal CurRow As Integer, ByVal CurCol As Integer) As String
Dim r, c, Ret As String : r = Nothing : c = Nothing
If AForm Is Nothing OrElse AForm.Length = 0 OrElse CurRow = 0 OrElse CurCol = 0 Then Return Nothing
AForm = AForm.ToUpper 'A1, B2, $A1, A$1, $A$1
Dim b = 0
For i As Integer = 1 To AForm.Length - 1
If Char.IsDigit(AForm, i) Or AForm.Substring(b, i) = "$"c Then r = AForm.Substring(i) : c = AForm.Substring(0, i) : Exit For
b = b + 1
Next
If r Is Nothing OrElse c Is Nothing Then Throw New Exception("Unsupported A-Form: " & AForm)
Ret = GenRC(CInt(r.Trim("$"c)), CurRow, r.Substring(0, 1) = "$"c, "R"c)
Ret &= GenRC(ColIndex(c.Trim("$"c)), CurCol, c.Substring(0, 1) = "$"c, "C"c)
Return Ret
End Function
'#******************************************************************
Private Function GenRC(ByVal A As Integer, ByVal Cur As Integer, ByVal IsFixed As Boolean, ByVal T As Char) As String
If IsFixed Then Return T & A
If A = Cur Then Return T Else Return T & "[" & A - Cur & "]"
End Function
'#*****************************************************************