/*********************************************************************
*
* Copyright (C) 2002 Andrew Khan
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
***************************************************************************/
package jxl.biff;
import jxl.common.Logger;
import jxl.biff.formula.ExternalSheet;
/**
* A helper to transform between excel cell references and
* sheet:column:row notation
* Because this function will be called when generating a string
* representation of a formula, the cell reference will merely
* be appened to the string buffer instead of returning a full
* blooded string, for performance reasons
*/
public final class CellReferenceHelper
{
/**
* The logger
*/
private static Logger logger = Logger.getLogger(CellReferenceHelper.class);
/**
* The character which indicates whether a reference is fixed
*/
private static final char fixedInd='$';
/**
* The character which indicates the sheet name terminator
*/
private static final char sheetInd = '!';
/**
* Constructor to prevent instantiation
*/
private CellReferenceHelper()
{
}
/**
* Gets the cell reference
*
* @param column
* @param row
* @param buf
*/
public static void getCellReference(int column, int row, StringBuffer buf)
{
// Put the column letter into the buffer
getColumnReference(column, buf);
// Add the row into the buffer
buf.append(Integer.toString(row+1));
}
/**
* Overloaded method which prepends $ for absolute reference
*
* @param column
* @param colabs TRUE if the column reference is absolute
* @param row
* @param rowabs TRUE if the row reference is absolute
* @param buf
*/
public static void getCellReference(int column, boolean colabs,
int row, boolean rowabs,
StringBuffer buf)
{
if (colabs)
{
buf.append(fixedInd);
}
// Put the column letter into the buffer
getColumnReference(column, buf);
if (rowabs)
{
buf.append(fixedInd);
}
// Add the row into the buffer
buf.append(Integer.toString(row+1));
}
/**
* Gets the column letter corresponding to the 0-based column number
*
* @param column the column number
* @return the letter for that column number
*/
public static String getColumnReference(int column)
{
StringBuffer buf = new StringBuffer();
getColumnReference(column, buf);
return buf.toString();
}
/**
* Gets the column letter corresponding to the 0-based column number
*
* @param column the column number
* @param buf the string buffer in which to write the column letter
*/
public static void getColumnReference(int column, StringBuffer buf)
{
int v = column/26;
int r = column%26;
StringBuffer tmp = new StringBuffer();
while (v != 0)
{
char col = (char) ('A' + r) ;
tmp.append(col);
r = v%26 - 1; // subtract one because only rows >26 preceded by A
v = v/26;
}
char col = (char) ('A' + r) ;
tmp.append(col);
// Insert into the proper string buffer in reverse order
for (int i = tmp.length() - 1; i >= 0; i--)
{
buf.append(tmp.charAt(i));
}
}
/**
* Gets the fully qualified cell reference given the column, row
* external sheet reference etc
*
* @param sheet
* @param column
* @param row
* @param workbook
* @param buf
*/
public static void getCellReference
(int sheet, int column, int row,
ExternalSheet workbook, StringBuffer buf)
{
// Quotes are added by the WorkbookParser
String name = workbook.getExternalSheetName(sheet);
buf.append(StringHelper.replace(name, "\'", "\'\'"));
buf.append(sheetInd);
getCellReference(column, row, buf);
}
/**
* Gets the fully qualified cell reference given the column, row
* external sheet reference etc
*
* @param sheet
* @param column
* @param colabs TRUE if the column is an absolute reference
* @param row
* @param rowabs TRUE if the row is an absolute reference
* @param workbook
* @param buf
*/
public static void getCellReference
(int sheet, int column, boolean colabs,
int row, boolean rowabs,
ExternalSheet workbook, StringBuffer buf)
{
// WorkbookParser now appends quotes and escapes apostrophes
String name = workbook.getExternalSheetName(sheet);
buf.append(name);
buf.append(sheetInd);
getCellReference(column, colabs, row, rowabs, buf);
}
/**
* Gets the fully qualified cell reference given the column, row
* external sheet reference etc
*
* @param sheet
* @param column
* @param row
* @param workbook
* @return the cell reference in the form 'Sheet 1'!A1
*/
public static String getCellReference
(int sheet, int column, int row,
ExternalSheet workbook)
{
StringBuffer sb = new StringBuffer();
getCellReference(sheet, column, row, workbook, sb);
return sb.toString();
}
/**
* Gets the cell reference for the specified column and row
*
* @param column
* @param row
* @return
*/
public static String getCellReference(int column, int row)
{
StringBuffer buf = new StringBuffer();
getCellReference(column, row, buf);
return buf.toString();
}
/**
* Gets the columnn number of the string cell reference
*
* @param s the string to parse
* @return the column portion of the cell reference
*/
public static int getColumn(String s)
{
int colnum = 0;
int numindex = getNumberIndex(s);
String s2 = s.toUpperCase();
int startPos = s.lastIndexOf(sheetInd) + 1;
if (s.charAt(startPos) == fixedInd)
{
startPos++;
}
int endPos = numindex;
if (s.charAt(numindex - 1) == fixedInd)
{
endPos--;
}
for (int i = startPos; i < endPos ; i++)
{
if (i != startPos)
{
colnum = (colnum+1) * 26;
}
colnum += (int) s2.charAt(i) - (int) 'A';
}
return colnum;
}
/**
* Gets the row number of the cell reference
*/
public static int getRow(String s)
{
try
{
return (Integer.parseInt(s.substring(getNumberIndex(s))) - 1);
}
catch (NumberFormatException e)
{
logger.warn(e, e);
return 0xffff;
}
}
/**
* Finds the position where the first number occurs in the string
*/
private static int getNumberIndex(String s)
{
// Find the position of the first number
boolean numberFound = false;
int pos = s.lastIndexOf(sheetInd) + 1;
char c = '\0';
while (!numberFound && pos < s.length() )
{
c = s.charAt(pos);
if (c >= '0' && c <= '9')
{
numberFound = true;
}
else
{
pos++;
}
}
return pos;
}
/**
* Sees if the column component is relative or not
*
* @param s
* @return TRUE if the column is relative, FALSE otherwise
*/
public static boolean isColumnRelative(String s)
{
return s.charAt(0) != fixedInd;
}
/**
* Sees if the row component is relative or not
*
* @param s
* @return TRUE if the row is relative, FALSE otherwise
*/
public static boolean isRowRelative(String s)
{
return s.charAt(getNumberIndex(s) - 1) != fixedInd;
}
/**
* Gets the sheet name from the cell reference string
*
* @param ref
* @return the sheet reference
*/
public static String getSheet(String ref)
{
int sheetPos = ref.lastIndexOf(sheetInd);
if (sheetPos == -1)
{
return "";
}
return ref.substring(0, sheetPos);
}
}
|