| View previous topic :: View next topic |
| Author |
Message |
r_vinoya
Super User
Joined: 03 Dec 2003
Posts: 619
Location: Somewhere in the Philippines
|
 Posted: Thu Feb 05, 2004 7:07 pm Post subject: Column Number to String... |
 |
|
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 |
|
 |
Iannz
OOo Advocate
Joined: 14 Feb 2004
Posts: 494
Location: Christchurch, New Zealand
|
| Posted: Sat Feb 14, 2004 8:24 pm Post subject: An alternative column number to string and reverse |
|
|
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 |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 4021
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
pitonyak
Administrator
Joined: 09 Mar 2004
Posts: 3152
Location: Columbus, Ohio, USA
|
| Posted: Mon Jun 14, 2004 7:50 am Post subject: |
|
|
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! 
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
My Document: http://www.pitonyak.org/AndrewMacro.odt
Free Info: http://www.pitonyak.org/oo.php
Most hated bug: http://www.openoffice.org/issues/show_bug.cgi?id=84159 |
|
| Back to top |
|
|
hansm
General User
Joined: 22 Jun 2004
Posts: 19
|
| Posted: Tue Jul 27, 2004 4:36 am Post subject: |
|
|
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 |
|
|
|
|
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
|
