[Solved] Column number to letter transformation in Java

Java, C++, C#, Delphi, ??? - Using the UNO bridges

[Solved] Column number to letter transformation in Java

Postby Lookris » Wed May 30, 2018 12:39 pm

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
Lookris
 
Posts: 10
Joined: Mon May 28, 2018 7:14 pm

Re: Column number to letter transformation in Java

Postby RoryOF » Wed May 30, 2018 2:52 pm

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.5 on Xubuntu 18.04 (mostly 64 bit version) and infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 27488
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Column number to letter transformation in Java

Postby Lookris » Wed May 30, 2018 3:09 pm

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
Lookris
 
Posts: 10
Joined: Mon May 28, 2018 7:14 pm

Re: Column number to letter transformation in Java

Postby hubert lambert » Thu May 31, 2018 12:15 pm

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   Expand viewCollapse view
    /**
     * 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
hubert lambert
 
Posts: 100
Joined: Mon Jun 13, 2016 10:50 am

Re: Column number to letter transformation in Java

Postby Lookris » Thu May 31, 2018 12:45 pm

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
Lookris
 
Posts: 10
Joined: Mon May 28, 2018 7:14 pm

Re: Column number to letter transformation in Java

Postby Villeroy » Thu May 31, 2018 3:39 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25852
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to External Programs

Who is online

Users browsing this forum: No registered users and 1 guest