/**
*
* @param formulRef
* @param rowRef
* @param colRef
* @param row
* @param col
* @return
*/
public static String getFormula(String formulRef, int rowRef, int colRef, int row, int col)
{
Type type = gettType(formulRef);
Point formul = getCoordonne(formulRef);
if (type.Equals(Type.CELLREF))
{
return(SIGN_EQUAL + formulRef);
}
else if (type.Equals(Type.CELL))
{
Point ecart = new Point(col - colRef, row - rowRef);
if ((ecart.X + formul.X) > 0 && (ecart.Y + formul.Y) > 0)
{
return(SIGN_EQUAL + DataFormater.getColumnName((int)ecart.X + (int)formul.X) + ((int)ecart.Y + (int)formul.Y));
}
else
{
return(ERROR_CELL);
}
}
else if (type.Equals(Type.COLREF))
{
Point ecart = new Point(0, row - rowRef);
if ((ecart.X + formul.X) > 0 && (ecart.Y + formul.Y) > 0)
{
return(SIGN_EQUAL + CELL_REF_SEPARATOR + DataFormater.getColumnName((int)ecart.X + (int)formul.X) + ((int)ecart.Y + (int)formul.Y));
}
else
{
return(ERROR_CELL);
}
}
else if (type.Equals(Type.ROWREF))
{
Point ecart = new Point(col - colRef, 0);
if ((ecart.X + formul.X) > 0 && (ecart.Y + formul.Y) > 0)
{
return(SIGN_EQUAL + DataFormater.getColumnName((int)ecart.X + (int)formul.X) + CELL_REF_SEPARATOR + ((int)ecart.Y + (int)formul.Y));
}
else
{
return(ERROR_CELL);
}
}
return(null);
}
/**
*
* @param formula
* @return
*/
public static Point getCoordonne(String formula)
{
Point point = new Point();
Type type = gettType(formula);
if (type.Equals(Type.CELLREF) || type.Equals(Type.ROWREF))
{
point.X = DataFormater.getColumnIndex(DataFormater.getColumn(formula));
point.Y = DataFormater.getRow(formula);
}
else if (type.Equals(Type.COLREF) || type.Equals(Type.CELL))
{
if (formula.StartsWith(CELL_REF_SEPARATOR))
{
formula = formula.Substring(1);
}
String row = "";
String col = "";
for (int i = 0; i < formula.Length; i++)
{
String car = formula.Substring(i, 1);
if ((car.ToUpper().CompareTo(MIN_CHAR) >= 0 && car.ToUpper().CompareTo(MAX_CHAR) <= 0))
{
col += car;
}
else
{
row += car;
}
}
point.X = DataFormater.getColumnIndex(col.ToUpper());
try
{
point.Y = int.Parse(row);
}
catch (Exception)
{
return(new Point(-1, -1));
}
}
return(point);
}