Java POI: Column Name & Column Index Conversion Utility

1. CellReferenceUtil

package edu.xmu.excel.util;

public class CellReferenceUtil
{

    /**
     * Input Coordination: C<br/>
     * Output Column Number: 2<br/>
     * 
     * @param coordName
     * @return colIndex: 0 based index
     */
    public static int getColIndex(String colName)
    {
        colName = colName.toUpperCase();

        int value = 0;
        for (int i = 0; i < colName.length(); i++)
        {
            int delta = colName.charAt(i) - 64;
            value = value * 26 + delta;
        }
        int colIndex = value - 1;
        return colIndex;
    }

    /**
     * Input column index: 2 <br/>
     * Output column name: C <br/>
     * 
     * @param colIndex
     * @return
     */
    public static String getColName(int colIndex)
    {
        int quotient = (colIndex) / 26;

        if (quotient > 0)
        {
            return getColName(quotient - 1) + (char) ((colIndex % 26) + 65);
        }
        else
        {
            return "" + (char) ((colIndex % 26) + 65);
        }
    }

    /**
     * Input coord: C8<br/>
     * Output Row Number: 7<br/>
     * 
     * @param coordName
     * @return rowIndex starts with 0
     */
    public static int getRowIndex(String rowName)
    {
        int rowIndex = Integer.parseInt(rowName) - 1;
        return rowIndex;
    }

    /**
     * Input rowIndex: 7 <br/>
     * Output rowName: 8 <br/>
     * 
     * @param rowIndex
     * @return
     */
    public static String getRowName(int rowIndex)
    {
        int rowName = rowIndex + 1;
        return String.valueOf(rowName);
    }

    /**
     * Input pos: col = 2, row = 7<br/>
     * Output coord: C8<br/>
     * 
     * @param colIndex
     * @param rowIndex
     * @return
     */
    public static String getCoordName(int colIndex, int rowIndex)
    {
        String colName = getColName(colIndex);
        String rowName = getRowName(rowIndex);

        return colName + rowName;
    }

    /**
     * Input coordName: C8 <br/>
     * Output colIndex: 2 <br/>
     * 
     * @param coordName
     * @return colIndex: Starts with 0
     */
    public static int getColIndexByCoordName(String coordName)
    {
        String[] colAndRowName = splitColAndRow(coordName);
        String colName = colAndRowName[0];
        return getColIndex(colName);
    }

    /**
     * Input coordName: C8 <br/>
     * Output rowIndex: 7 <br/>
     * 
     * @param coordName
     * @return rowIndex: 0 based index
     */
    public static int getRowIndexByCoordName(String coordName)
    {
        String[] colAndRowName = splitColAndRow(coordName);
        String rowName = colAndRowName[1];
        return getRowIndex(rowName);
    }

    /**
     * Input coordName: C8 <br/>
     * Output : String[]{C, 8} <br/>
     * 
     * @param coordName
     * @return
     */
    private static String[] splitColAndRow(String coordName)
    {
        int rowNumStartIndex = 0;
        for (int i = 0; i < coordName.length(); i++)
        {
            char ch = coordName.charAt(i);
            if (Character.isDigit(ch))
            {
                rowNumStartIndex = i;
                break;
            }
        }

        String colName = coordName.substring(0, rowNumStartIndex);
        String rowName = coordName.substring(rowNumStartIndex);

        return new String[]
        { colName, rowName };
    }
}

 

2. Test case:

package edu.xmu.excel.util;

import static org.junit.Assert.assertEquals;

import org.junit.Test;

public class CellReferenceUtilTest
{
    /**
     * Test method for
     * {@link edu.xmu.excel.util.CellReferenceUtil#getColIndex(String)}
     */
    @Test
    public void getColIndexTest()
    {
        int colIndex = CellReferenceUtil.getColIndex("A");
        assertEquals(0, colIndex);

        colIndex = CellReferenceUtil.getColIndex("AA");
        assertEquals(26, colIndex);

        colIndex = CellReferenceUtil.getColIndex("AAA");
        assertEquals(26 * 26 + 26, colIndex);
    }

    /**
     * Test method for
     * {@link edu.xmu.excel.util.CellReferenceUtil#getColName(int)}
     */
    @Test
    public void getColNameTest()
    {
        String colName = CellReferenceUtil.getColName(0);
        assertEquals("A", colName);

        colName = CellReferenceUtil.getColName(26);
        assertEquals("AA", colName);

        colName = CellReferenceUtil.getColName(26 * 26 + 26);
        assertEquals("AAA", colName);
    }

    /**
     * Test method for
     * {@link edu.xmu.excel.util.CellReferenceUtil#getRowIndex(String)}
     */
    @Test
    public void getRowIndexTest()
    {
        int rowIndex = CellReferenceUtil.getRowIndex("8");
        assertEquals(7, rowIndex);

        rowIndex = CellReferenceUtil.getRowIndex("27");
        assertEquals(26, rowIndex);
    }

    /**
     * Test method for
     * {@link edu.xmu.excel.util.CellReferenceUtil#getRowName(int)}
     */
    @Test
    public void getRowNameTest()
    {
        String rowName = CellReferenceUtil.getRowName(7);
        assertEquals("8", rowName);

        rowName = CellReferenceUtil.getRowName(26);
        assertEquals("27", rowName);
    }

    /**
     * Test method for
     * {@link edu.xmu.excel.util.CellReferenceUtil#getColIndexByCoordName(String)}
     */
    @Test
    public void getColIndexByCoordNameTest()
    {
        int colIndex = CellReferenceUtil.getColIndexByCoordName("C8");
        assertEquals(2, colIndex);

        colIndex = CellReferenceUtil.getColIndexByCoordName("AA21");
        assertEquals(26, colIndex);

        colIndex = CellReferenceUtil.getColIndexByCoordName("AAA21");
        assertEquals(26 * 26 + 26, colIndex);
    }

    /**
     * Test method for
     * {@link edu.xmu.excel.util.CellReferenceUtil#getRowIndexByCoordName(String)}
     */
    @Test
    public void getRowIndexByCoordNameTest()
    {
        int rowIndex = CellReferenceUtil.getRowIndexByCoordName("C8");
        assertEquals(7, rowIndex);

        rowIndex = CellReferenceUtil.getRowIndexByCoordName("AA21");
        assertEquals(20, rowIndex);

        rowIndex = CellReferenceUtil.getRowIndexByCoordName("AA222");
        assertEquals(221, rowIndex);
    }
    /**
     * Test method for
     * {@link edu.xmu.excel.util.CellReferenceUtil#getCoordName(int, int)}
     */
    @Test
    public void getCoordNameTest()
    {
        String coordName = CellReferenceUtil.getCoordName(2, 7);
        assertEquals("C8", coordName);

        coordName = CellReferenceUtil.getCoordName(26, 21);
        assertEquals("AA22", coordName);

        coordName = CellReferenceUtil.getCoordName(26 * 26 + 26, 21);
        assertEquals("AAA22", coordName);
    }
}