public void TestBug55843b()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet("test") as XSSFSheet;
XSSFRow row = sheet.CreateRow(0) as XSSFRow;
XSSFRow row2 = sheet.CreateRow(1) as XSSFRow;
XSSFCell cellA2 = row2.CreateCell(0, CellType.Formula) as XSSFCell;
XSSFCell cellB1 = row.CreateCell(1, CellType.Numeric) as XSSFCell;
cellB1.SetCellValue(10);
XSSFFormulaEvaluator formulaEvaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator;
cellA2.SetCellFormula("IF(B1=0,\"\",((ROW())))");
CellValue Evaluate = formulaEvaluator.Evaluate(cellA2);
System.Console.WriteLine(Evaluate);
Assert.AreEqual("2", Evaluate.FormatAsString());
cellA2.CellFormula = (/*setter*/ "IF(NOT(B1=0),((ROW())),\"\")");
CellValue EvaluateN = formulaEvaluator.Evaluate(cellA2);
System.Console.WriteLine(EvaluateN);
Assert.AreEqual(Evaluate.ToString(), EvaluateN.ToString());
Assert.AreEqual("2", EvaluateN.FormatAsString());
}
finally
{
wb.Close();
}
}
public string ToString(string format)
{
switch (format)
{
case "Full": return($"i:{mI} j:{mJ} value: {mCellValue.ToString()}");
default: return(ToString());
}
}
internal void ResetScore()
{
StartCoroutine(ColourFlasher(GetComponent <Image>(), Color.yellow));
CellValue = 0;
//scoreMesh.enabled = false;
scoreMesh.color = Color.clear;
scoreMesh.text = CellValue.ToString();
}
public void IncrementScore()
{
this.CellValue += 1;
StartCoroutine(ColourFlasher(GetComponent <Image>(), Color.green));
// display updated score
//scoreMesh.enabled = true;
scoreMesh.color = scoreMeshDefaultColour;
scoreMesh.text = CellValue.ToString();
}
//public GameController gameController;
// Use this for initialization
void Start()
{
Text[] textMeshes = GetComponentsInChildren <Text>();
scoreMesh = textMeshes[0];
scoreMesh.text = CellValue.ToString();
defaultColour = GetComponent <Image>().color;
scoreMeshDefaultColour = scoreMesh.color;
//scoreMesh.enabled = false;
scoreMesh.color = Color.clear;
}
public string GetCellText()
{
if (!IsRevealed)
{
return(IsFlagged ? "!" : "");
}
return(CellValue switch
{
CellValue.Zero => "",
CellValue.Mine => "*",
_ => CellValue.ToString("D"),
});
public object Convert(object value, Type targetType, object parameter, CultureInfo culture)
{
CellValue cell = (CellValue)value;
if (cell == CellValue.Empty)
{
return(string.Empty);
}
else
{
return(cell.ToString());
}
}
static object GetCodeValue(ISheet sheet, CustomWorkbook book, ICell cell, string type, out string error)
{
error = string.Empty;
try
{
object codevalue = null;
if (CustomWorkbook.evaluateSheets.Contains(sheet.SheetName) && cell != null && cell.CellType == CellType.Formula)
{
book.evaluator.DebugEvaluationOutputForNextEval = true;
CellValue cellValue = book.evaluator.Evaluate(cell);
switch (type)
{
case "int":
codevalue = cellValue.CellType == CellType.Numeric ? Convert.ToInt32(cellValue.NumberValue) :
cellValue.CellType != CellType.String || string.IsNullOrEmpty(cellValue.StringValue) ? 0 : int.Parse(cellValue.StringValue); break;
case "long":
codevalue = cellValue.CellType == CellType.Numeric ? Convert.ToInt64(cellValue.NumberValue) :
cellValue.CellType != CellType.String || string.IsNullOrEmpty(cellValue.StringValue) ? 0 : long.Parse(cellValue.StringValue); break;
case "string":
codevalue = cellValue.CellType == CellType.String ? cellValue.StringValue : cellValue.ToString(); break;
case "double":
case "float":
case "float64":
codevalue = cellValue.CellType == CellType.Numeric ? cellValue.NumberValue :
cellValue.CellType != CellType.String || string.IsNullOrEmpty(cellValue.StringValue) ? 0 : double.Parse(cellValue.StringValue); break;
default:
if (type.StartsWith("[]"))
{
string[] arr = (cellValue.CellType == CellType.Numeric ? cellValue.NumberValue.ToString() : (cellValue.CellType == CellType.String ? cellValue.StringValue : "")).Split('|');
if (arr.Length == 1 && string.IsNullOrEmpty(arr[0]))
{
arr = new string[] { }
}
;
switch (type.Substring(2))
{
case "int":
int[] v = new int[arr.Length];
for (int ii = 0; ii < arr.Length; ii++)
{
v[ii] = string.IsNullOrEmpty(arr[ii]) ? 0 : int.Parse(arr[ii]);
}
codevalue = v;
break;
case "long":
long[] v64 = new long[arr.Length];
for (int ii = 0; ii < arr.Length; ii++)
{
v64[ii] = string.IsNullOrEmpty(arr[ii]) ? 0 : long.Parse(arr[ii]);
}
codevalue = v64;
break;
case "string":
codevalue = arr;
break;
case "double":
case "float":
double[] vv = new double[arr.Length];
for (int ii = 0; ii < arr.Length; ii++)
{
vv[ii] = string.IsNullOrEmpty(arr[ii]) ? 0 : double.Parse(arr[ii]);
}
codevalue = vv;
break;
}
}
break;
}
}
else
{
CellType ct = CellType.Blank;
if (cell != null)
{
if (cell.CellType == CellType.Formula)
{
ct = cell.CachedFormulaResultType;
}
else
{
ct = cell.CellType;
}
}
switch (type)
{
case "int":
codevalue = ct == CellType.Numeric ? Convert.ToInt32(cell.NumericCellValue) :
(ct == CellType.String && !string.IsNullOrEmpty(cell.StringCellValue) ? int.Parse(cell.StringCellValue) : 0);
break;
case "long":
codevalue = ct == CellType.Numeric ? Convert.ToInt64(cell.NumericCellValue) :
(ct == CellType.String && !string.IsNullOrEmpty(cell.StringCellValue)) ? long.Parse(cell.StringCellValue) : 0; break;
case "string":
codevalue = ct == CellType.Numeric ? cell.NumericCellValue.ToString() :
(ct == CellType.String ? cell.StringCellValue : "");
break;
case "double":
case "float":
case "float64":
codevalue = ct == CellType.Numeric ? cell.NumericCellValue :
(ct == CellType.String && !string.IsNullOrEmpty(cell.StringCellValue) ? double.Parse(cell.StringCellValue) : 0);
break;
default:
if (type.StartsWith("[]"))
{
string[] arr = (ct == CellType.Numeric ? cell.NumericCellValue.ToString() : (ct == CellType.String ? cell.StringCellValue : "")).Split('|');
if (arr.Length == 1 && string.IsNullOrEmpty(arr[0]))
{
arr = new string[] { }
}
;
switch (type.Substring(2))
{
case "int":
int[] v = new int[arr.Length];
for (int ii = 0; ii < arr.Length; ii++)
{
v[ii] = string.IsNullOrEmpty(arr[ii]) ? 0 : int.Parse(arr[ii]);
}
codevalue = v;
break;
case "long":
long[] v64 = new long[arr.Length];
for (int ii = 0; ii < arr.Length; ii++)
{
v64[ii] = string.IsNullOrEmpty(arr[ii]) ? 0 : long.Parse(arr[ii]);
}
codevalue = v64;
break;
case "string":
codevalue = arr;
break;
case "double":
case "float":
case "float64":
double[] vv = new double[arr.Length];
for (int ii = 0; ii < arr.Length; ii++)
{
vv[ii] = string.IsNullOrEmpty(arr[ii]) ? 0 : double.Parse(arr[ii]);
}
codevalue = vv;
break;
}
}
break;
}
}
return(codevalue);
}
catch (Exception ex)
{
Console.Write(ex);
error = "数据格式有误， 第" + (cell.RowIndex + 1) + "行第" + (cell.ColumnIndex + 1) + "列， SheetName = " + sheet.SheetName + "，FileNames = " + book.fileName;
return(null);
}
}
public override void Render(float timePassed)
{
base.Render(timePassed);
// Do not render if not visible.
if (GUIGraphicsContext.EditMode == false)
{
if (!IsVisible)
{
return;
}
}
// The GUIButtonControl has the focus
//if (value < 0)
//{
if (Focus)
{
//render the focused image
m_imgFocus.Render(timePassed);
}
else
{
//render the non-focused image
m_imgNoFocus.Render(timePassed);
}
//}
m_label.TextAlignment = Alignment.ALIGN_CENTER;
m_label.Label = (CellValue > 0) ? CellValue.ToString() : "";
if (CellValue > 0)
{
if (editable)
{
if (CellValue == SolutionValue)
{
m_label.TextColor = m_dwTextColor;
}
else
{
m_label.TextColor = m_dwCellIncorrectTextColor;
}
}
else
{
m_label.TextColor = m_dwDisabledColor;
}
}
m_label.SetPosition(XPosition, YPosition + m_iTextOffsetY);
m_label.Render(timePassed);
if (_showCandidates)
{
if (_displayColourOverlay && editable && (m_label.Label == string.Empty))
{
_colorOverlay.Render(timePassed);
}
if (m_label.Label == string.Empty)
{
for (int i = 0; i < 9; i++)
{
if (_isCandidate[i])
{
m_imgOverlay[i].Render(timePassed);
}
}
}
}
}
private static void assertFormulaResult(CellValue cv, ICell cell)
{
double actualValue = cv.NumberValue;
double expectedValue = cell.NumericCellValue; // cached formula result calculated by Excel
Assert.AreEqual(CellType.Numeric, cv.CellType, "Invalid formula result: " + cv.ToString());
Assert.AreEqual(expectedValue, actualValue, 1E-8);
}
private static string GetStringValueFromCell(CellValue value)
{
return value != null ? value.ToString() : "NAN";
}
/// <summary>
/// 获取单元格数据
/// </summary>
/// <returns></returns>
protected dynamic GetValue(SSCell cell)
{
dynamic value = String.Empty;
if (cell != null)
{
try
{
if (cell.CellType == CellType.Formula || cell.CellType == CellType.Numeric) //判断是否是日期格式
{
bool isDate = false;
try { isDate = DateUtil.IsCellDateFormatted(cell); }
catch { }
if (isDate)
{
value = cell.DateCellValue;
}
else if (cell.CellType == CellType.Formula) //判断是否是公式和数字
{
IFormulaEvaluator fe = null;
if (work is XSSFWorkbook)
{
fe = new XSSFFormulaEvaluator(work);
}
else if (work is HSSFWorkbook)
{
fe = new HSSFFormulaEvaluator(work);
}
if (fe != null)
{
CellValue cv = fe.Evaluate(cell);
if (cv.CellType == CellType.Numeric)
{
value = cv.NumberValue;
}
else if (cv.CellType == CellType.Boolean)
{
value = cv.BooleanValue;
}
else if (cv.CellType == CellType.Boolean)
{
value = cv.BooleanValue;
}
else
{
value = cv.ToString();
}
}
}
else if (cell.CellType == CellType.Numeric)
{
value = cell.NumericCellValue;
}
}
else if (cell.CellType == CellType.Boolean)
{
value = cell.BooleanCellValue;
}
else if (cell.CellType == CellType.Error)
{
value = cell.ErrorCellValue;
}
else
{
value = cell.ToString();
}
}
catch { value = "erorr"; }
}
return(value);
}
