Page 1 of 1
[Solved] Column number to letter transformation in Java
Posted: Wed May 30, 2018 12:39 pm
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
Re: Column number to letter transformation in Java
Posted: Wed May 30, 2018 2:52 pm
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.
Re: Column number to letter transformation in Java
Posted: Wed May 30, 2018 3:09 pm
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
Re: Column number to letter transformation in Java
Posted: Thu May 31, 2018 12:15 pm
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.
Re: Column number to letter transformation in Java
Posted: Thu May 31, 2018 12:45 pm
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
Re: Column number to letter transformation in Java
Posted: Thu May 31, 2018 3:39 pm
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();
}
}