public void TestUnusualArgs()
{
CultureShim.SetCurrentCulture("en-US");
// startPos with fractional digits
ConfirmMid(new StringEval("galactic"), new NumberEval(3.1), new NumberEval(4), "lact");
// string startPos
ConfirmMid(new StringEval("galactic"), new StringEval("3"), new NumberEval(4), "lact");
// text (first) arg type is number, other args are strings with fractional digits
ConfirmMid(new NumberEval(123456), new StringEval("3.1"), new StringEval("2.9"), "34");
// startPos is 1x1 area ref, numChars is cell ref
AreaEval aeStart = EvalFactory.CreateAreaEval("A1:A1", new ValueEval[] { new NumberEval(2), });
RefEval reNumChars = EvalFactory.CreateRefEval("B1", new NumberEval(3));
ConfirmMid(new StringEval("galactic"), aeStart, reNumChars, "ala");
ConfirmMid(new StringEval("galactic"), new NumberEval(3.1), BlankEval.instance, "");
ConfirmMid(new StringEval("galactic"), new NumberEval(3), BoolEval.FALSE, "");
ConfirmMid(new StringEval("galactic"), new NumberEval(3), BoolEval.TRUE, "l");
ConfirmMid(BlankEval.instance, new NumberEval(3), BoolEval.TRUE, "");
}
public void TestBasic()
{
CultureShim.SetCurrentCulture("en-US");
ConfirmValue("100", 100);
ConfirmValue("-2.3", -2.3);
ConfirmValue(".5", 0.5);
ConfirmValue(".5e2", 50);
ConfirmValue(".5e-2", 0.005);
ConfirmValue(".5e+2", 50);
ConfirmValue("+5", 5);
ConfirmValue("$1,000", 1000);
ConfirmValue("100.5e1", 1005);
ConfirmValue("1,0000", 10000);
ConfirmValue("1,000,0000", 10000000);
ConfirmValue("1,000,0000,00000", 1000000000000.0);
ConfirmValue(" 100 ", 100);
ConfirmValue(" + 100", 100);
ConfirmValue("10000", 10000);
ConfirmValue("$-5", -5);
ConfirmValue("$.5", 0.5);
ConfirmValue("123e+5", 12300000);
ConfirmValue("1,000e2", 100000);
ConfirmValue("$10e2", 1000);
ConfirmValue("$1,000e2", 100000);
ConfirmValue("30%", 0.3);
ConfirmValue("30 %", 0.3);
}
public void TestLocale()
{
DataFormatter dfUS = new DataFormatter(CultureShim.GetCultureInfo("en-US"));
DataFormatter dfFR = new DataFormatter(CultureShim.GetCultureInfo("fr-FR"));
Assert.AreEqual("1234", dfUS.FormatRawCellContents(1234, -1, "@"));
Assert.AreEqual("1234", dfFR.FormatRawCellContents(1234, -1, "@"));
Assert.AreEqual("12.34", dfUS.FormatRawCellContents(12.34, -1, "@"));
CultureShim.SetCurrentCulture("fr-FR");
Assert.AreEqual("12,34", dfFR.FormatRawCellContents(12.34, -1, "@"));
}
public void TestMMMMM()
{
//DataFormatter dfUS = new DataFormatter(Locale.US);
DataFormatter dfUS = new DataFormatter(CultureShim.GetCultureInfo("en-US"));
DateTime c = new DateTime(2010, 6, 1, 2, 0, 0, 0);
CultureShim.SetCurrentCulture("en-US");
Assert.AreEqual("2010-J-1 2:00:00", dfUS.FormatRawCellContents(
DateUtil.GetExcelDate(c, false), -1, "YYYY-MMMMM-D h:mm:ss"
));
}
public void NumericCells()
{
CultureShim.SetCurrentCulture("en-US");
IWorkbook workbook = _testDataProvider.CreateWorkbook();
FixFonts(workbook);
IDataFormat df = workbook.GetCreationHelper().CreateDataFormat();
ISheet sheet = workbook.CreateSheet();
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue(0); // GetCachedFormulaResult() returns 0 for not Evaluated formula cells
row.CreateCell(1).SetCellValue(10);
row.CreateCell(2).SetCellValue("10");
row.CreateCell(3).CellFormula = (/*setter*/ "(A1+B1)*1.0"); // a formula that returns '10'
ICell cell4 = row.CreateCell(4); // numeric cell with a custom style
ICellStyle style4 = workbook.CreateCellStyle();
style4.DataFormat = (/*setter*/ df.GetFormat("0.0000"));
cell4.CellStyle = (/*setter*/ style4);
cell4.SetCellValue(10); // formatted as '10.0000'
row.CreateCell(5).SetCellValue("10.0000");
// autosize not-Evaluated cells, formula cells are sized as if the result is 0
for (int i = 0; i < 6; i++)
{
sheet.AutoSizeColumn(i);
}
Assert.IsTrue(sheet.GetColumnWidth(0) < sheet.GetColumnWidth(1)); // width of '0' is less then width of '10'
Assert.AreEqual(sheet.GetColumnWidth(1), sheet.GetColumnWidth(2)); // 10 and '10' should be sized Equally
Assert.AreEqual(sheet.GetColumnWidth(3), sheet.GetColumnWidth(0)); // formula result is unknown, the width is calculated for '0'
Assert.AreEqual(sheet.GetColumnWidth(4), sheet.GetColumnWidth(5)); // 10.0000 and '10.0000'
// Evaluate formulas and re-autosize
EvaluateWorkbook(workbook);
for (int i = 0; i < 6; i++)
{
sheet.AutoSizeColumn(i);
}
Assert.IsTrue(sheet.GetColumnWidth(0) < sheet.GetColumnWidth(1)); // width of '0' is less then width of '10'
Assert.AreEqual(sheet.GetColumnWidth(1), sheet.GetColumnWidth(2)); // columns 1, 2 and 3 should have the same width
Assert.AreEqual(sheet.GetColumnWidth(2), sheet.GetColumnWidth(3)); // columns 1, 2 and 3 should have the same width
Assert.AreEqual(sheet.GetColumnWidth(4), sheet.GetColumnWidth(5)); // 10.0000 and '10.0000'
}
public void TestTextWithDateFormatSecondArg()
{
// Test with Java style M=Month
CultureShim.SetCurrentCulture("en-US");
ValueEval numArg = new NumberEval(321.321);
ValueEval formatArg = new StringEval("dd:MM:yyyy hh:mm:ss");
ValueEval[] args = { numArg, formatArg };
ValueEval result = TextFunction.TEXT.Evaluate(args, -1, (short)-1);
ValueEval testResult = new StringEval("16:11:1900 07:42:14");
Assert.AreEqual(testResult.ToString(), result.ToString());
// Excel also supports "m before h is month"
formatArg = new StringEval("dd:mm:yyyy hh:mm:ss");
args[1] = formatArg;
result = TextFunction.TEXT.Evaluate(args, -1, (short)-1);
testResult = new StringEval("16:11:1900 07:42:14");
//Assert.AreEqual(testResult.ToString(), result.ToString());
// this line is intended to compute how "November" would look like in the current locale
string november = new SimpleDateFormat("MMMM").Format(new DateTime(2010, 11, 15), CultureInfo.CurrentCulture);
// Again with Java style
formatArg = new StringEval("MMMM dd, yyyy");
args[1] = formatArg;
//fix error in non-en Culture
Npoi.Core.SS.Formula.Functions.Text.Formatter = new Npoi.Core.SS.UserModel.DataFormatter(CultureInfo.CurrentCulture);
result = TextFunction.TEXT.Evaluate(args, -1, (short)-1);
testResult = new StringEval(november + " 16, 1900");
Assert.AreEqual(testResult.ToString(), result.ToString());
// And Excel style
formatArg = new StringEval("mmmm dd, yyyy");
args[1] = formatArg;
result = TextFunction.TEXT.Evaluate(args, -1, (short)-1);
testResult = new StringEval(november + " 16, 1900");
Assert.AreEqual(testResult.ToString(), result.ToString());
}
