OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Column Number to String...

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Code Snippets
View previous topic :: View next topic  
Author Message
r_vinoya
Super User
Super User


Joined: 03 Dec 2003
Posts: 619
Location: Somewhere in the Philippines

PostPosted: Thu Feb 05, 2004 7:07 pm    Post subject: Column Number to String... Reply with quote

I found a code (from AndrewMacro.sxw...Chapter6.6) that converts column number to string...
But it have some bugs ( 0, 1, 2, ... , 11, 12, 13, 14, 15 converts to A, B, C, ... ,L, M, BN, BO)...

I made modification of the macro:
Code:
Function columnnumtostr (ByVal colnum As long) As String
Rem This Code converts column index number to letter (ex. 1 => A, 18 => R)
   dim r$
   if colnum>26 then
      b = int(colnum/26)
      a = colnum MOD 26
      if a = 0 then
         a = 26
         b = b-1
      endif
      r$ = chr(64 + b) & chr(64+a)
   else
      r$ = chr (64 + colnum)
   endif
   
   columnnumtostr = r$
End Function


Hope this help...
Thanks for the original macro that get me started.
_________________
# : - )
Back to top
View user's profile Send private message
Iannz
OOo Advocate
OOo Advocate


Joined: 14 Feb 2004
Posts: 494
Location: Christchurch, New Zealand

PostPosted: Sat Feb 14, 2004 8:24 pm    Post subject: An alternative column number to string and reverse Reply with quote

In these routines column numbers start at zero thus A <-> 0 B <-> 1 etc

Code:
function fnColStr2Num(byval sCol as string) as long
dim i as integer, nCol as integer

sCol=ucase(sCol)
nCol = 0
for i = 1 to len(sCol)
   nCol=nCol*26 + asc(mid(sCol,i,1)) - 64
next
fnColStr2Num = nCol - 1
end function


function fnColNum2Str(byval nCol as long) as string
dim sCol as string

sCol=""
while nCol >= 0
   sCol =  chr(65 + (nCol mod 26)) & sCol
   nCol = int(nCol / 26) - 1
wend
fnColNum2Str = sCol
end function

_________________
Cheers, Ian

http://wiki.services.openoffice.org/wiki/Extensions_development_basic a wiki about writing OpenOffice.org extensions.
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Thu Jun 10, 2004 6:37 am    Post subject: Reply with quote

Just adding some cross references....

Column Number to Column Name and vice versa
http://www.oooforum.org/forum/viewtopic.php?p=23013#23013
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
pitonyak
Administrator
Administrator


Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

PostPosted: Mon Jun 14, 2004 7:50 am    Post subject: Reply with quote

Hey r_vinoya, next time you find a bug in a macro in my document, let me know so that the fix can be shown there as well... Added bonus, I will credit the fix to you! Smile

I found this bug while writing my book so I also updated that section in my macro document. My current code is as follows:
Code:
'Given a cell, extract the normal looking address of a cell
'First, the name of the containing sheet is extracted.
'Second, the column number is obtained and turned into a letter
'Lastly, the row is obtained. Rows start at 0 but are displayed as 1
Function PrintableAddressOfCell(the_cell As Object) As String
  PrintableAddressOfCell = "Unknown"
  If Not IsNull(the_cell) Then
    PrintableAddressOfCell = the_cell.getSpreadSheet().getName + ":" + _
      ColumnNumberToString(the_cell.CellAddress.Column) + (the_cell.CellAddress.Row+1)
  End If
End Function

' Columns are numbered starting at 0 where 0 corresponds to A
' They run as A-Z,AA-AZ,BA-BZ,...,IV
' This is esentially a question of how do you convert a Base 10 number to
' a base 26 number.
' Note that the_column is passed by value!
Function ColumnNumberToString(ByVal the_column As Long) As String
  Dim s$
  'Save this so I do NOT modify the parameter.
  'This was an icky bug that took me a while to find
  Do while the_column >= 0
    s$ = Chr(65 +  the_column MOD 26) + s$
    the_column =  the_column \ 26 - 1
  Loop
  ColumnNumberToString = s$
End Function

Notice that you can use integer division out of the box and it works great! If you really want the address of a cell, however, a new service was introducted in OOo 1.1.1. I document this in both my macro document and my book (the book contains the more complete information). The short version is that the one of the properties returns a different value depending on the currently active sheet.
Code:
  oConv = ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
  oConv.Address = oActiveCell.getCellAddress
  Print oConv.UserInterfaceRepresentation
  print oConv.PersistentRepresentation

_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php
Back to top
View user's profile Send private message Send e-mail Visit poster's website AIM Address
hansm
General User
General User


Joined: 22 Jun 2004
Posts: 19

PostPosted: Tue Jul 27, 2004 4:36 am    Post subject: Reply with quote

How about this one:
Code:

Function ColumnNumberToString(ByVal the_column As Long) As String
  oDoc = ThisComponent
  oSheet = oDoc.Sheets( 0)
  ColumnNumberToString = oNSheet.getcolumns().ElementNames( the_column)
End Function

It uses the internal index->columnname conversion of the sheet.
Back to top
View user's profile Send private message
NisSAM
Newbie
Newbie


Joined: 24 Sep 2010
Posts: 3

PostPosted: Sat Sep 25, 2010 10:11 am    Post subject: Reply with quote

I prefer to use my own function just because it works faster.

But !!!

I always check at start of the application that for an arbitrary column number I've the same result from my own function and getName.

After that I am sure that my quick function works OK.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Code Snippets All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group