[Solved] Column number to letter transformation in Java

Java, C++, C#, Delphi... - Using the UNO bridges
Post Reply
Lookris
Posts: 10
Joined: Mon May 28, 2018 7:14 pm

[Solved] Column number to letter transformation in Java

Post by Lookris »

So, there are a bunch of ways to get column number of a certain cell without actually getting the cell itself: Single- and Complex- References, CellAddress struct and so on. But I can't seem to find a function, or interface to transform this number into letter representation, so that 0 -> A and 99 -> CV (an analogue to POI convertNumToColString). Are there any other ways to do so? The blunt solution will be to create an array of such strings, but it seems counter-productive
Last edited by Lookris on Thu May 31, 2018 4:46 pm, edited 1 time in total.
LibreOffice 6.0 on Linux Mint 18.3 Sylvia
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Column number to letter transformation in Java

Post by RoryOF »

Write a little Java function to do the task - you have been given methods in your other posting.

"Counter-productive" for the Forum is repeatedly answering the same question.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Lookris
Posts: 10
Joined: Mon May 28, 2018 7:14 pm

Re: Column number to letter transformation in Java

Post by Lookris »

Yeah, the answers given works great for the question asked -- when I have a cell object ready. But this is a little different -- I only have a SingleReference, not so easily transformed. Or even worse -- ComplexReference
LibreOffice 6.0 on Linux Mint 18.3 Sylvia
hubert lambert
Posts: 145
Joined: Mon Jun 13, 2016 10:50 am

Re: Column number to letter transformation in Java

Post by hubert lambert »

Hi,
Lookris wrote:But I can't seem to find a function, or interface to transform this number into letter representation, so that 0 -> A and 99 -> CV (an analogue to POI convertNumToColString).
There's none.
Lookris wrote:Are there any other ways to do so?
From the POI source code:

Code: Select all

    /**
     * Takes in a 0-based base-10 column and returns a ALPHA-26
     *  representation.
     * eg {@code convertNumToColString(3)} returns {@code "D"}
     */
    public static String convertNumToColString(int col) {
        // Excel counts column A as the 1st column, we
        //  treat it as the 0th one
        int excelColNum = col + 1;

        StringBuilder colRef = new StringBuilder(2);
        int colRemain = excelColNum;

        while(colRemain > 0) {
            int thisPart = colRemain % 26;
            if(thisPart == 0) { thisPart = 26; }
            colRemain = (colRemain - thisPart) / 26;

            // The letter A is at 65
            char colChar = (char)(thisPart+64);
            colRef.insert(0, colChar);
        }

        return colRef.toString();
    }
Regards.
AOOo 4.1.2 on Win7 | LibreOffice on various Linux systems
Lookris
Posts: 10
Joined: Mon May 28, 2018 7:14 pm

Re: Column number to letter transformation in Java

Post by Lookris »

Oh, this is a great solution! How stupid of me not to check the source for POI function, while mentioning it. I'm deeply ashamed, thank you
LibreOffice 6.0 on Linux Mint 18.3 Sylvia
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Column number to letter transformation in Java

Post by Villeroy »

The following code has been recorded by the MRI extension. I don't write any Java.
Object oInitialTarget is the current spreadsheet.
(1, 1, 5, 6, 0) are arbitrary arguments describing the start column, start row, end column, end row and sheet index of a range.

Code: Select all

import com.sun.star.beans.UnknownPropertyException;
import com.sun.star.beans.XPropertySet;
import com.sun.star.lang.IllegalArgumentException;
import com.sun.star.lang.IndexOutOfBoundsException;
import com.sun.star.lang.WrappedTargetException;
import com.sun.star.sheet.XCellRangesAccess;
import com.sun.star.sheet.XSpreadsheetDocument;
import com.sun.star.sheet.XSpreadsheets;
import com.sun.star.table.XCellRange;
import com.sun.star.uno.AnyConverter;
import com.sun.star.uno.UnoRuntime;
import com.sun.star.uno.XComponentContext;

public static void snippet(XComponentContext xComponentContext, Object oInitialTarget)
{
	try
	{
		XSpreadsheetDocument xSpreadsheetDocument = UnoRuntime.queryInterface(
			XSpreadsheetDocument.class, oInitialTarget);
		XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
		
		XCellRangesAccess xCellRangesAccess = UnoRuntime.queryInterface(
			XCellRangesAccess.class, xSpreadsheets);
		XCellRange xCellRange = xCellRangesAccess.getCellRangeByPosition(1, 1, 5, 6, 0);
		
		XPropertySet xPropSet = UnoRuntime.queryInterface(
			XPropertySet.class, xCellRange);
		String sAbsoluteName = AnyConverter.toString(xPropSet.getPropertyValue("AbsoluteName"));
		
	}
	catch (IllegalArgumentException e1)
	{
		// 
		e1.printStackTrace();
	}
	catch (IndexOutOfBoundsException e2)
	{
		// getCellRangeByPosition
		e2.printStackTrace();
	}
	catch (WrappedTargetException e3)
	{
		// getPropertyValue
		e3.printStackTrace();
	}
	catch (UnknownPropertyException e4)
	{
		// getPropertyValue
		e4.printStackTrace();
	}
}
A sheet column has a name also:

Code: Select all

import com.sun.star.chart.XChartDataArray;
import com.sun.star.container.XIndexAccess;
import com.sun.star.container.XNamed;
import com.sun.star.lang.IndexOutOfBoundsException;
import com.sun.star.lang.WrappedTargetException;
import com.sun.star.sheet.XSpreadsheet;
import com.sun.star.sheet.XSpreadsheetDocument;
import com.sun.star.sheet.XSpreadsheets;
import com.sun.star.table.XCellRange;
import com.sun.star.table.XColumnRowRange;
import com.sun.star.table.XTableColumns;
import com.sun.star.uno.UnoRuntime;
import com.sun.star.uno.XComponentContext;

public static void snippet(XComponentContext xComponentContext, Object oInitialTarget)
{
	try
	{
		XSpreadsheetDocument xSpreadsheetDocument = UnoRuntime.queryInterface(
			XSpreadsheetDocument.class, oInitialTarget);
		XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
		
		XIndexAccess xIndexAccess = UnoRuntime.queryInterface(
			XIndexAccess.class, xSpreadsheets);
		XSpreadsheet xSpreadsheet = UnoRuntime.queryInterface(
			XSpreadsheet.class, xIndexAccess.getByIndex(1));
		
		XChartDataArray xChartDataArray = UnoRuntime.queryInterface(
			XChartDataArray.class, xSpreadsheet);
		
		String[] sColumnDescriptions = xChartDataArray.getColumnDescriptions();
		
		XColumnRowRange xColumnRowRange = UnoRuntime.queryInterface(
			XColumnRowRange.class, xSpreadsheet);
		XTableColumns xTableColumns = xColumnRowRange.getColumns();
		
		XIndexAccess xIndexAccess2 = UnoRuntime.queryInterface(
			XIndexAccess.class, xTableColumns);
		XCellRange xCellRange = UnoRuntime.queryInterface(
			XCellRange.class, xIndexAccess2.getByIndex(13));
		
		XNamed xNamed = UnoRuntime.queryInterface(
			XNamed.class, xCellRange);
		String sName = xNamed.getName();
		
	}
	catch (IndexOutOfBoundsException e1)
	{
		// getByIndex
		e1.printStackTrace();
	}
	catch (WrappedTargetException e2)
	{
		// getByIndex
		e2.printStackTrace();
	}
}
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply