public void TestFunctionsFromTestSpreadsheet()
{
NumberToTextConverter.RawDoubleBitsToText(BitConverter.DoubleToInt64Bits(1.0));
ProcessFunctionGroup(SS.START_OPERATORS_ROW_INDEX, null);
ProcessFunctionGroup(SS.START_FUNCTIONS_ROW_INDEX, null);
// example for debugging individual functions/operators:
// ProcessFunctionGroup(SS.START_OPERATORS_ROW_INDEX, "ConcatEval");
// ProcessFunctionGroup(SS.START_FUNCTIONS_ROW_INDEX, "AVERAGE");
// confirm results
String successMsg = "There were "
+ _EvaluationSuccessCount + " successful Evaluation(s) and "
+ _functionSuccessCount + " function(s) without error";
if (_functionFailureCount > 0)
{
String msg = _functionFailureCount + " function(s) failed in "
+ _EvaluationFailureCount + " Evaluation(s). " + successMsg;
throw new AssertionException(msg);
}
//if (false)
//{ // normally no output for successful Tests
// Console.WriteLine(this.GetType().Name + ": " + successMsg);
//}
}
/**
* Formats the given raw cell value, based on the supplied
* format index and string, according to excel style rules.
* @see #formatCellValue(Cell)
*/
public String FormatRawCellContents(double value, int formatIndex, String formatString, bool use1904Windowing)
{
// Is it a date?
if (DateUtil.IsADateFormat(formatIndex, formatString))
{
if (DateUtil.IsValidExcelDate(value))
{
FormatBase dateFormat = GetFormat(value, formatIndex, formatString);
if (dateFormat is ExcelStyleDateFormatter)
{
// Hint about the raw excel value
((ExcelStyleDateFormatter)dateFormat).SetDateToBeFormatted(value);
}
DateTime d = DateUtil.GetJavaDate(value, use1904Windowing);
return(PerformDateFormatting(d, dateFormat));
}
// RK: Invalid dates are 255 #s.
if (emulateCSV)
{
return(invalidDateTimeString);
}
}
// else Number
FormatBase numberFormat = GetFormat(value, formatIndex, formatString);
if (numberFormat == null)
{
return(value.ToString(currentCulture));
}
// When formatting 'value', double to text to BigDecimal produces more
// accurate results than double to Double in JDK8 (as compared to
// previous versions). However, if the value contains E notation, this
// would expand the values, which we do not want, so revert to
// original method.
String result;
String textValue = NumberToTextConverter.ToText(value);
if (textValue.IndexOf('E') > -1)
{
result = numberFormat.Format(value);
}
else
{
result = numberFormat.Format(textValue);
}
// Complete scientific notation by adding the missing +.
if (result.Contains("E") && !result.Contains("E-"))
{
result = result.Replace("E", "E+");
}
return(result);
}
/**
* Excel's abnormal rendering of NaNs is both difficult to test and even reproduce in java. In
* general, Excel does not attempt to use raw NaN in the IEEE sense. In {@link FormulaRecord}s,
* Excel uses the NaN bit pattern to flag non-numeric (text, boolean, error) cached results.
* If the formula result actually evaluates to raw NaN, Excel transforms it to <i>#NUM!</i>.
* In other places (e.g. {@link NumberRecord}, {@link NumberPtg}, array items (via {@link
* ConstantValueParser}), there seems to be no special NaN translation scheme. If a NaN bit
* pattern is somehow encoded into any of these places Excel actually attempts to render the
* values as a plain number. That is the unusual functionality that this method is testing.<p/>
*
* There are multiple encodings (bit patterns) for NaN, and CPUs and applications can convert
* to a preferred NaN encoding (Java prefers <c>0x7FF8000000000000L</c>). Besides the
* special encoding in {@link FormulaRecord.SpecialCachedValue}, it is not known how/whether
* Excel attempts to encode NaN values.
*
* Observed NaN behaviour on HotSpot/Windows:
* <c>Double.longBitsToDouble()</c> will set one bit 51 (the NaN signaling flag) if it isn't
* already. <c>Double.doubleToLongBits()</c> will return a double with bit pattern
* <c>0x7FF8000000000000L</c> for any NaN bit pattern supplied.<br/>
* Differences are likely to be observed with other architectures.<p/>
*
* <p/>
* The few test case examples calling this method represent functionality which may not be
* important for POI to support.
*/
private void ConfirmNaN(long l, String excelRep)
{
double d = BitConverter.Int64BitsToDouble(l);
Assert.AreEqual("NaN", d.ToString());
//to make this assert work, please set the CurrentCulture above, too. Assert.AreEqual("非数字", d.ToString());
String strExcel = NumberToTextConverter.RawDoubleBitsToText(l);
Assert.AreEqual(excelRep, strExcel);
}
public void TestSimpleRendering_bug56156()
{
double dResult = 0.05 + 0.01; // values chosen to produce rounding anomaly
String actualText = NumberToTextConverter.ToText(dResult);
String jdkText = dResult.ToString("R");
//in c#, the value of dResult.ToString() is 0.06 and equals to actualText;
//so we use dResult.ToString("R"), thus the test passes.
if (jdkText.Equals(actualText))
{
// "0.060000000000000005"
throw new Exception("Should not use default JDK IEEE double rendering");
}
Assert.AreEqual("0.06", actualText);
}
public void OnCell(ICell cell, ICellWalkContext ctx)
{
double pointValue = GetOrEvalCellValue(cell);
/* Silently ignore non-numeric values.
* This is Office default behaviour. */
if (Double.IsNaN(pointValue))
{
return;
}
CT_NumVal point = this.ctNumData.AddNewPt();
point.idx = (uint)ctx.OrdinalNumber;
point.v = (NumberToTextConverter.ToText(pointValue));
}
private String ConvertCellValueToString()
{
switch (cellType)
{
case CellType.Blank:
return("");
case CellType.Boolean:
return(((BoolErrRecord)_record).BooleanValue ? "TRUE" : "FALSE");
case CellType.String:
int sstIndex = ((LabelSSTRecord)_record).SSTIndex;
return(book.Workbook.GetSSTString(sstIndex).String);
case CellType.Numeric:
return(NumberToTextConverter.ToText(((NumberRecord)_record).Value));
case CellType.Error:
return(HSSFErrorConstants.GetText(((BoolErrRecord)_record).ErrorValue));
case CellType.Formula:
// should really evaluate, but Cell can't call HSSFFormulaEvaluator
// just use cached formula result instead
break;
default:
throw new InvalidDataException("Unexpected cell type (" + cellType + ")");
}
FormulaRecordAggregate fra = ((FormulaRecordAggregate)_record);
FormulaRecord fr = fra.FormulaRecord;
switch (fr.CachedResultType)
{
case CellType.Boolean:
return(fr.CachedBooleanValue ? "TRUE" : "FALSE");
case CellType.String:
return(fra.StringValue);
case CellType.Numeric:
return(NumberToTextConverter.ToText(fr.Value));
case CellType.Error:
return(HSSFErrorConstants.GetText(fr.CachedErrorValue));
}
throw new InvalidDataException("Unexpected formula result type (" + cellType + ")");
}
public void TestAllNumberToText()
{
int failureCount = 0;
NumberToTextConversionExamples.ExampleConversion[] examples = NumberToTextConversionExamples.GetExampleConversions();
for (int i = 0; i < examples.Length; i++)
{
NumberToTextConversionExamples.ExampleConversion example = examples[i];
try
{
if (example.IsNaN)
{
ConfirmNaN(example.RawDoubleBits, example.ExcelRendering);
continue;
}
String actual = NumberToTextConverter.ToText(example.DoubleValue);
if (!example.ExcelRendering.Equals(actual))
{
failureCount++;
String msg = "Error rendering for examples[" + i + "] "
+ FormatExample(example) + " "
+ " bad-result='" + actual + "' "
+ "Excel String=" + example.ExcelRendering;
Console.WriteLine(msg);
}
}
catch (Exception e)
{
failureCount++;
Console.WriteLine("Error in excel rendering for examples[" + i + "] "
+ FormatExample(example) + "':" + e.Message);
Console.Write(e.StackTrace);
}
}
if (failureCount > 0)
{
throw new Exception(failureCount
+ " error(s) in excel number to text conversion (see std-err)");
}
}
public static string cellToString(ICell cell)
{
if (cell == null)
{
return("");
}
switch (cell.CellType)
{
case CellType.Numeric: //Cell has Numeric content.
if (DateUtil.IsCellDateFormatted(cell))
{
return(cell.DateCellValue.ToLongDateString());
}
else
{
return(NumberToTextConverter.ToText(cell.NumericCellValue));
}
case CellType.Formula: //Cell contains a formula.
if (cell.CachedFormulaResultType == CellType.String)
{
return(cell.StringCellValue);
}
else
{
if (DateUtil.IsCellDateFormatted(cell))
{
return(cell.DateCellValue.ToLongDateString());
}
else
{
return(NumberToTextConverter.ToText(cell.NumericCellValue));
}
}
case CellType.String: //Cell has String content.
case CellType.Error: //Cell contains an error.
case CellType.Boolean: //Cell contains a boolean.
return(cell.StringCellValue);
default: //Cell is blank.
return(cell.StringCellValue);
}
}
private void TextBox_TextChanged(object sender, TextChangedEventArgs e)
{
try
{
if (!string.IsNullOrWhiteSpace(InputNumber.Text))
{
var number = double.Parse(InputNumber.Text);
var(wholePart, decimalPart) = NumberToTextConverter.Convert(number);
OutputNumber.Text = $"{wholePart} ლარი და {decimalPart} თეთრი";
}
}
catch (FormatException)
{
OutputNumber.Text = InvalidFromatMessage;
}
catch (ArgumentOutOfRangeException)
{
OutputNumber.Text = NumberOutOfRangeMessage;
}
}
public void SetUpClass()
{
System.Threading.Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US");
// some pre-checks to hunt for a problem in the Maven build
// these checks ensure that the correct locale is set, so a Assert.Failure here
// usually indicates an invalid locale during test-execution
Assert.IsFalse(DateUtil.IsADateFormat(-1, "_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"??_-;_-@_-"));
//Locale ul = LocaleUtil.getUserLocale();
//assertTrue(Locale.ROOT.equals(ul) || Locale.getDefault().equals(ul));
String textValue = NumberToTextConverter.ToText(1234.56);
Assert.AreEqual(-1, textValue.IndexOf('E'));
Object cellValueO = 1234.56d;
/*CellFormat cellFormat = new CellFormat("_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"??_-;_-@_-");
* CellFormatResult result = cellFormat.apply(cellValueO);
* Assert.AreEqual(" 1,234.56 ", result.text);*/
CellFormat cfmt = CellFormat.GetInstance("_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"??_-;_-@_-");
CellFormatResult result = cfmt.Apply(cellValueO);
Assert.AreEqual(" 1,234.56 ", result.Text,
"This Assert.Failure can indicate that the wrong locale is used during test-execution, ensure you run with english/US via -Duser.language=en -Duser.country=US");
}
private static String GetConstantText(Object o)
{
if (o == null)
{
return(""); // TODO - how is 'empty value' represented in formulas?
}
if (o is String)
{
return("\"" + (String)o + "\"");
}
if (o is Double || o is double)
{
return(NumberToTextConverter.ToText((Double)o));
}
if (o is bool || o is Boolean)
{
return(((bool)o).ToString().ToUpper());
}
if (o is ErrorConstant)
{
return(((ErrorConstant)o).Text);
}
throw new ArgumentException("Unexpected constant class (" + o.GetType().Name + ")");
}
public override String ToFormulaString()
{
return(NumberToTextConverter.ToText(Value));
}
protected override void AppendValueText(StringBuilder sb)
{
sb.Append(" .value= ").Append(NumberToTextConverter.ToText(field_4_value));
}
public NumberTranslator(NumberToTextConverter converter)
{
Converter = converter;
}
public void CopyCellValue(ICell sourceCell, ICell targetCell)
{
targetCell.CellStyle = sourceCell.CellStyle;
if (sourceCell.CellComment != null)
{
targetCell.CellComment = sourceCell.CellComment;
}
CellType srcCellType = sourceCell.CellType;
targetCell.SetCellType(srcCellType);
switch (srcCellType)
{
case CellType.Numeric:
string value;
double dateValue = sourceCell.NumericCellValue;
ICellStyle cellStyle = sourceCell.CellStyle;
short dataFormat = cellStyle.DataFormat;
if (DateUtil.IsCellDateFormatted(sourceCell))
{
value = getDateValue(dataFormat, cellStyle.GetDataFormatString(), dateValue);
}
else
{
if (String.Equals("General", cellStyle.GetDataFormatString(), StringComparison.CurrentCultureIgnoreCase))
{
DecimalFormat format = new DecimalFormat("#");
value = format.Format(dateValue);
}
else
{
value = NumberToTextConverter.ToText(dateValue);
}
}
targetCell.SetCellValue(value);
break;
//short format = sourceCell.CellStyle.DataFormat;
////对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理***14/31/57/58日期20/32时间
//if (format == 14 || format == 31 || format == 57 || format == 58 || format == 20 || format == 32)
// targetCell.SetCellValue(sourceCell.DateCellValue);
//else
// targetCell.SetCellValue(sourceCell.NumericCellValue);
//break;
case CellType.String:
targetCell.SetCellValue(sourceCell.RichStringCellValue);
break;
case CellType.Formula:
targetCell.SetCellFormula(sourceCell.CellFormula);
break;
case CellType.Blank:
targetCell.SetCellValue("");
break;
case CellType.Boolean:
targetCell.SetCellValue(sourceCell.BooleanCellValue);
break;
case CellType.Error:
targetCell.SetCellErrorValue(sourceCell.ErrorCellValue);
break;
default:
break;
}
}
public Number(long value, NumberToTextConverter converter)
{
Value = value;
Converter = converter;
}