Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

converting A1 style to C1R1 style...

skin111skin111 Posts: 4Member
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?


Comments

  • DrMartenDrMarten Posts: 748Member
    [b][red]This message was edited by DrMarten at 2006-11-29 10:8:11[/red][/b][hr]

    : 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".

    [code]
    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
    [/code]

    Regards,

    Dr M.
  • skin111skin111 Posts: 4Member
    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.
  • DrMartenDrMarten Posts: 748Member
    [b][red]This message was edited by DrMarten at 2006-12-7 1:55:14[/red][/b][hr]
    : 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.
  • skin111skin111 Posts: 4Member
    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

  • PavlinIIPavlinII Posts: 404Member
    : 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:
    [code]
    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[/code]
    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

    [blue][b][italic][size=4]P[/size]avlin [size=4]II[/italic][/size][/b][/blue]

    [purple]Don't take life too seriously anyway you won't escape alive from it![/purple]


  • skin111skin111 Posts: 4Member
    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
    '#*****************************************************************


Sign In or Register to comment.