[Solved] Column number to letter transformation in Java

[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

[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.

[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

[hubert lambert]

Hi,

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.

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.

[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

[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();
	}
}