public void TestColours()
{
//DataFormatter dfUS = new DataFormatter(Locale.US);
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
String[] formats = new String[] {
"##.##",
"[WHITE]##.##",
"[BLACK]##.##;[RED]-##.##",
"[COLOR11]##.##;[COLOR 43]-##.00",
};
foreach (String format in formats)
{
Assert.AreEqual(
"12.34",
dfUS.FormatRawCellContents(12.343, -1, format),
"Wrong format for: " + format
);
Assert.AreEqual(
"-12.34",
dfUS.FormatRawCellContents(-12.343, -1, format),
"Wrong format for: " + format
);
}
// Ensure that random square brackets remain
Assert.AreEqual("12.34[a]", dfUS.FormatRawCellContents(12.343, -1, "##.##[a]"));
Assert.AreEqual("[ab]12.34[x]", dfUS.FormatRawCellContents(12.343, -1, "[ab]##.##[x]"));
}
public void TestLocale()
{
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
DataFormatter dfFR = new DataFormatter(System.Globalization.CultureInfo.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, "@"));
Assert.AreEqual("12,34", dfFR.FormatRawCellContents(12.34, -1, "@"));
}
public void TestLocaleBasedFormats()
{
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
// Standard formats
Assert.AreEqual("63", dfUS.FormatRawCellContents(63.0, -1, "[$-1010409]General"));
Assert.AreEqual("63", dfUS.FormatRawCellContents(63.0, -1, "[$-1010409]@"));
// Regular numeric style formats
Assert.AreEqual("63", dfUS.FormatRawCellContents(63.0, -1, "[$-1010409]##"));
Assert.AreEqual("63", dfUS.FormatRawCellContents(63.0, -1, "[$-1010409]00"));
}
/**
* TODO Fix these so that they work
*/
public void DISABLEDtestCustomFormats()
{
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"), true);
String fmt;
fmt = "\"At\" H:MM AM/PM \"on\" DDDD MMMM D\",\" YYYY";
Assert.AreEqual(
"At 4:20 AM on Thursday May 17, 2007",
dfUS.FormatRawCellContents(39219.1805636921, -1, fmt)
);
fmt = "0 \"dollars and\" .00 \"cents\"";
Assert.AreEqual("19 dollars and .99 cents", dfUS.FormatRawCellContents(19.99, -1, fmt));
}
public void TestAMPM()
{
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
Assert.AreEqual("06:00", dfUS.FormatRawCellContents(0.25, -1, "hh:mm"));
Assert.AreEqual("18:00", dfUS.FormatRawCellContents(0.75, -1, "hh:mm"));
Assert.AreEqual("06:00 AM", dfUS.FormatRawCellContents(0.25, -1, "hh:mm AM/PM"));
Assert.AreEqual("06:00 PM", dfUS.FormatRawCellContents(0.75, -1, "hh:mm AM/PM"));
Assert.AreEqual("1904-01-01 06:00:00 AM", dfUS.FormatRawCellContents(0.25, -1, "yyyy-mm-dd hh:mm:ss AM/PM", true));
Assert.AreEqual("1904-01-01 06:00:00 PM", dfUS.FormatRawCellContents(0.75, -1, "yyyy-mm-dd hh:mm:ss AM/PM", true));
}
public void TestEscapes()
{
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
Assert.AreEqual("1901-01-01", dfUS.FormatRawCellContents(367.0, -1, "yyyy-mm-dd"));
Assert.AreEqual("1901-01-01", dfUS.FormatRawCellContents(367.0, -1, "yyyy\\-mm\\-dd"));
Assert.AreEqual("1901.01.01", dfUS.FormatRawCellContents(367.0, -1, "yyyy.mm.dd"));
Assert.AreEqual("1901.01.01", dfUS.FormatRawCellContents(367.0, -1, "yyyy\\.mm\\.dd"));
Assert.AreEqual("1901/01/01", dfUS.FormatRawCellContents(367.0, -1, "yyyy/mm/dd"));
Assert.AreEqual("1901/01/01", dfUS.FormatRawCellContents(367.0, -1, "yyyy\\/mm\\/dd"));
}
public void TestInvalidDate()
{
//DataFormatter df1 = new DataFormatter(Locale.US);
DataFormatter df1 = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
//Assert.AreEqual("-1.0", df1.FormatRawCellContents(-1, -1, "mm/dd/yyyy"));
//in java -1.toString() is "-1.0", but in C# -1.ToString() is "-1".
Assert.AreEqual("-1", df1.FormatRawCellContents(-1, -1, "mm/dd/yyyy"));
//DataFormatter df2 = new DataFormatter(Locale.US);
DataFormatter df2 = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"), true);
Assert.AreEqual("###############################################################################################################################################################################################################################################################",
df2.FormatRawCellContents(-1, -1, "mm/dd/yyyy"));
}
public void TestScientificNotation()
{
//DataFormatter dfUS = new DataFormatter(Locale.US);
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
Assert.AreEqual("1.23E+01", dfUS.FormatRawCellContents(12.343, -1, "0.00E+00"));
Assert.AreEqual("-1.23E+01", dfUS.FormatRawCellContents(-12.343, -1, "0.00E+00"));
Assert.AreEqual("0.00E+00", dfUS.FormatRawCellContents(0.0, -1, "0.00E+00"));
}
public void TestDateWindowing()
{
//DataFormatter dfUS = new DataFormatter(Locale.US);
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
Assert.AreEqual("1899-12-31 00:00:00", dfUS.FormatRawCellContents(0.0, -1, "yyyy-mm-dd hh:mm:ss"));
Assert.AreEqual("1899-12-31 00:00:00", dfUS.FormatRawCellContents(0.0, -1, "yyyy-mm-dd hh:mm:ss", false));
Assert.AreEqual("1904-01-01 00:00:00", dfUS.FormatRawCellContents(0.0, -1, "yyyy-mm-dd hh:mm:ss", true));
}
public void TestElapsedTime()
{
//DataFormatter dfUS = new DataFormatter(Locale.US);
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
double hour = 1.0 / 24.0;
Assert.AreEqual("01:00", dfUS.FormatRawCellContents(1 * hour, -1, "hh:mm"));
Assert.AreEqual("05:00", dfUS.FormatRawCellContents(5 * hour, -1, "hh:mm"));
Assert.AreEqual("20:00", dfUS.FormatRawCellContents(20 * hour, -1, "hh:mm"));
Assert.AreEqual("23:00", dfUS.FormatRawCellContents(23 * hour, -1, "hh:mm"));
Assert.AreEqual("00:00", dfUS.FormatRawCellContents(24 * hour, -1, "hh:mm"));
Assert.AreEqual("02:00", dfUS.FormatRawCellContents(26 * hour, -1, "hh:mm"));
Assert.AreEqual("20:00", dfUS.FormatRawCellContents(44 * hour, -1, "hh:mm"));
Assert.AreEqual("02:00", dfUS.FormatRawCellContents(50 * hour, -1, "hh:mm"));
Assert.AreEqual("01:00", dfUS.FormatRawCellContents(1 * hour, -1, "[hh]:mm"));
Assert.AreEqual("05:00", dfUS.FormatRawCellContents(5 * hour, -1, "[hh]:mm"));
Assert.AreEqual("20:00", dfUS.FormatRawCellContents(20 * hour, -1, "[hh]:mm"));
Assert.AreEqual("23:00", dfUS.FormatRawCellContents(23 * hour, -1, "[hh]:mm"));
Assert.AreEqual("24:00", dfUS.FormatRawCellContents(24 * hour, -1, "[hh]:mm"));
Assert.AreEqual("26:00", dfUS.FormatRawCellContents(26 * hour, -1, "[hh]:mm"));
Assert.AreEqual("44:00", dfUS.FormatRawCellContents(44 * hour, -1, "[hh]:mm"));
Assert.AreEqual("50:00", dfUS.FormatRawCellContents(50 * hour, -1, "[hh]:mm"));
Assert.AreEqual("01", dfUS.FormatRawCellContents(1 * hour, -1, "[hh]"));
Assert.AreEqual("05", dfUS.FormatRawCellContents(5 * hour, -1, "[hh]"));
Assert.AreEqual("20", dfUS.FormatRawCellContents(20 * hour, -1, "[hh]"));
Assert.AreEqual("23", dfUS.FormatRawCellContents(23 * hour, -1, "[hh]"));
Assert.AreEqual("24", dfUS.FormatRawCellContents(24 * hour, -1, "[hh]"));
Assert.AreEqual("26", dfUS.FormatRawCellContents(26 * hour, -1, "[hh]"));
Assert.AreEqual("44", dfUS.FormatRawCellContents(44 * hour, -1, "[hh]"));
Assert.AreEqual("50", dfUS.FormatRawCellContents(50 * hour, -1, "[hh]"));
double minute = 1.0 / 24.0 / 60.0;
Assert.AreEqual("01:00", dfUS.FormatRawCellContents(1 * minute, -1, "[mm]:ss"));
Assert.AreEqual("05:00", dfUS.FormatRawCellContents(5 * minute, -1, "[mm]:ss"));
Assert.AreEqual("20:00", dfUS.FormatRawCellContents(20 * minute, -1, "[mm]:ss"));
Assert.AreEqual("23:00", dfUS.FormatRawCellContents(23 * minute, -1, "[mm]:ss"));
Assert.AreEqual("24:00", dfUS.FormatRawCellContents(24 * minute, -1, "[mm]:ss"));
Assert.AreEqual("26:00", dfUS.FormatRawCellContents(26 * minute, -1, "[mm]:ss"));
Assert.AreEqual("44:00", dfUS.FormatRawCellContents(44 * minute, -1, "[mm]:ss"));
Assert.AreEqual("50:00", dfUS.FormatRawCellContents(50 * minute, -1, "[mm]:ss"));
Assert.AreEqual("59:00", dfUS.FormatRawCellContents(59 * minute, -1, "[mm]:ss"));
Assert.AreEqual("60:00", dfUS.FormatRawCellContents(60 * minute, -1, "[mm]:ss"));
Assert.AreEqual("61:00", dfUS.FormatRawCellContents(61 * minute, -1, "[mm]:ss"));
Assert.AreEqual("119:00", dfUS.FormatRawCellContents(119 * minute, -1, "[mm]:ss"));
Assert.AreEqual("120:00", dfUS.FormatRawCellContents(120 * minute, -1, "[mm]:ss"));
Assert.AreEqual("121:00", dfUS.FormatRawCellContents(121 * minute, -1, "[mm]:ss"));
Assert.AreEqual("01", dfUS.FormatRawCellContents(1 * minute, -1, "[mm]"));
Assert.AreEqual("05", dfUS.FormatRawCellContents(5 * minute, -1, "[mm]"));
Assert.AreEqual("20", dfUS.FormatRawCellContents(20 * minute, -1, "[mm]"));
Assert.AreEqual("23", dfUS.FormatRawCellContents(23 * minute, -1, "[mm]"));
Assert.AreEqual("24", dfUS.FormatRawCellContents(24 * minute, -1, "[mm]"));
Assert.AreEqual("26", dfUS.FormatRawCellContents(26 * minute, -1, "[mm]"));
Assert.AreEqual("44", dfUS.FormatRawCellContents(44 * minute, -1, "[mm]"));
Assert.AreEqual("50", dfUS.FormatRawCellContents(50 * minute, -1, "[mm]"));
Assert.AreEqual("59", dfUS.FormatRawCellContents(59 * minute, -1, "[mm]"));
Assert.AreEqual("60", dfUS.FormatRawCellContents(60 * minute, -1, "[mm]"));
Assert.AreEqual("61", dfUS.FormatRawCellContents(61 * minute, -1, "[mm]"));
Assert.AreEqual("119", dfUS.FormatRawCellContents(119 * minute, -1, "[mm]"));
Assert.AreEqual("120", dfUS.FormatRawCellContents(120 * minute, -1, "[mm]"));
Assert.AreEqual("121", dfUS.FormatRawCellContents(121 * minute, -1, "[mm]"));
double second = 1.0 / 24.0 / 60.0 / 60.0;
Assert.AreEqual("86400", dfUS.FormatRawCellContents(86400 * second, -1, "[ss]"));
Assert.AreEqual("01", dfUS.FormatRawCellContents(1 * second, -1, "[ss]"));
Assert.AreEqual("05", dfUS.FormatRawCellContents(5 * second, -1, "[ss]"));
Assert.AreEqual("20", dfUS.FormatRawCellContents(20 * second, -1, "[ss]"));
Assert.AreEqual("23", dfUS.FormatRawCellContents(23 * second, -1, "[ss]"));
Assert.AreEqual("24", dfUS.FormatRawCellContents(24 * second, -1, "[ss]"));
Assert.AreEqual("26", dfUS.FormatRawCellContents(26 * second, -1, "[ss]"));
Assert.AreEqual("44", dfUS.FormatRawCellContents(44 * second, -1, "[ss]"));
Assert.AreEqual("50", dfUS.FormatRawCellContents(50 * second, -1, "[ss]"));
Assert.AreEqual("59", dfUS.FormatRawCellContents(59 * second, -1, "[ss]"));
Assert.AreEqual("60", dfUS.FormatRawCellContents(60 * second, -1, "[ss]"));
Assert.AreEqual("61", dfUS.FormatRawCellContents(61 * second, -1, "[ss]"));
Assert.AreEqual("119", dfUS.FormatRawCellContents(119 * second, -1, "[ss]"));
Assert.AreEqual("120", dfUS.FormatRawCellContents(120 * second, -1, "[ss]"));
Assert.AreEqual("121", dfUS.FormatRawCellContents(121 * second, -1, "[ss]"));
//boolean jdk_1_5 = System.getProperty("java.vm.version").startsWith("1.5");
//if(!jdk_1_5) {
// YK: the tests below were written under JDK 1.6 and assume that
// the rounding mode in the underlying decimal formatters is HALF_UP
// It is not so JDK 1.5 where the default rounding mode is HALV_EVEN and cannot be changed.
Assert.AreEqual("27:18:08", dfUS.FormatRawCellContents(1.1376, -1, "[h]:mm:ss"));
Assert.AreEqual("28:48:00", dfUS.FormatRawCellContents(1.2, -1, "[h]:mm:ss"));
Assert.AreEqual("29:31:12", dfUS.FormatRawCellContents(1.23, -1, "[h]:mm:ss"));
Assert.AreEqual("31:26:24", dfUS.FormatRawCellContents(1.31, -1, "[h]:mm:ss"));
Assert.AreEqual("27:18:08", dfUS.FormatRawCellContents(1.1376, -1, "[hh]:mm:ss"));
Assert.AreEqual("28:48:00", dfUS.FormatRawCellContents(1.2, -1, "[hh]:mm:ss"));
Assert.AreEqual("29:31:12", dfUS.FormatRawCellContents(1.23, -1, "[hh]:mm:ss"));
Assert.AreEqual("31:26:24", dfUS.FormatRawCellContents(1.31, -1, "[hh]:mm:ss"));
Assert.AreEqual("57:07.2", dfUS.FormatRawCellContents(.123, -1, "mm:ss.0;@"));
Assert.AreEqual("57:41.8", dfUS.FormatRawCellContents(.1234, -1, "mm:ss.0;@"));
Assert.AreEqual("57:41.76", dfUS.FormatRawCellContents(.1234, -1, "mm:ss.00;@"));
Assert.AreEqual("57:41.760", dfUS.FormatRawCellContents(.1234, -1, "mm:ss.000;@"));
Assert.AreEqual("24:00.0", dfUS.FormatRawCellContents(123456.6, -1, "mm:ss.0"));
//}
}
public void TestDatesWithLocales()
{
DataFormatter dfUS = new DataFormatter(CultureInfo.GetCultureInfo("en-US"), true);
String dateFormatEnglish = "[$-409]mmmm dd yyyy h:mm AM/PM";
String dateFormatChinese = "[$-804]mmmm dd yyyy h:mm AM/PM";
// Check we format the English one correctly
double date = 26995.477777777778;
Assert.AreEqual(
"November 27 1973 11:28 AM",
dfUS.FormatRawCellContents(date, -1, dateFormatEnglish)
);
// Check that, in the absence of locale support, we handle
// the Chinese one the same as the English one
Assert.AreEqual(
"November 27 1973 11:28 AM",
dfUS.FormatRawCellContents(date, -1, dateFormatChinese)
);
}
public void TestBug54786()
{
DataFormatter formatter = new DataFormatter();
String format = "[h]\"\"h\"\" m\"\"m\"\"";
Assert.IsTrue(DateUtil.IsADateFormat(-1, format));
Assert.IsTrue(DateUtil.IsValidExcelDate(_15_MINUTES));
Assert.AreEqual("1h 0m", formatter.FormatRawCellContents(_15_MINUTES, -1, format, false));
Assert.AreEqual("0.041666667", formatter.FormatRawCellContents(_15_MINUTES, -1, "[h]'h'", false));
Assert.AreEqual("1h 0m\"", formatter.FormatRawCellContents(_15_MINUTES, -1, "[h]\"\"h\"\" m\"\"m\"\"\"", false));
Assert.AreEqual("1h", formatter.FormatRawCellContents(_15_MINUTES, -1, "[h]\"\"h\"\"", false));
Assert.AreEqual("h1", formatter.FormatRawCellContents(_15_MINUTES, -1, "\"\"h\"\"[h]", false));
Assert.AreEqual("h1", formatter.FormatRawCellContents(_15_MINUTES, -1, "\"\"h\"\"h", false));
Assert.AreEqual(" 60", formatter.FormatRawCellContents(_15_MINUTES, -1, " [m]", false));
Assert.AreEqual("h60", formatter.FormatRawCellContents(_15_MINUTES, -1, "\"\"h\"\"[m]", false));
Assert.AreEqual("m1", formatter.FormatRawCellContents(_15_MINUTES, -1, "\"\"m\"\"h", false));
try
{
Assert.AreEqual("1h 0m\"", formatter.FormatRawCellContents(_15_MINUTES, -1, "[h]\"\"h\"\" m\"\"m\"\"\"\"", false));
Assert.Fail("Catches exception because of invalid format, i.e. trailing quoting");
}
catch (Exception e)
{
//Assert.IsTrue(e.Message.Contains("Cannot format given Object as a Number"));
}
}
public void TestFractions()
{
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
// Excel often prefers "# #/#"
Assert.AreEqual("321 1/3", dfUS.FormatRawCellContents(321.321, -1, "# #/#"));
Assert.AreEqual("321 26/81", dfUS.FormatRawCellContents(321.321, -1, "# #/##"));
Assert.AreEqual("26027/81", dfUS.FormatRawCellContents(321.321, -1, "#/##"));
// OOo seems to like the "# ?/?" form
Assert.AreEqual("321 1/3", dfUS.FormatRawCellContents(321.321, -1, "# ?/?"));
Assert.AreEqual("321 26/81", dfUS.FormatRawCellContents(321.321, -1, "# ?/??"));
Assert.AreEqual("26027/81", dfUS.FormatRawCellContents(321.321, -1, "?/??"));
// p;n;z;s parts
Assert.AreEqual("321 1/3", dfUS.FormatRawCellContents(321.321, -1, "# #/#;# ##/#;0;xxx"));
Assert.AreEqual("-321 1/3", dfUS.FormatRawCellContents(-321.321, -1, "# #/#;# ##/#;0;xxx"));
Assert.AreEqual("0", dfUS.FormatRawCellContents(0, -1, "# #/#;# ##/#;0;xxx"));
// Assert.AreEqual("0.0", dfUS.FormatRawCellContents(0, -1, "# #/#;# ##/#;#.#;xxx")); // currently hard coded to 0
// Custom formats with text are not currently supported
// Assert.AreEqual("+ve", dfUS.FormatRawCellContents(0, -1, "+ve;-ve;zero;xxx"));
// Assert.AreEqual("-ve", dfUS.FormatRawCellContents(0, -1, "-ve;-ve;zero;xxx"));
// Assert.AreEqual("zero", dfUS.FormatRawCellContents(0, -1, "zero;-ve;zero;xxx"));
// Custom formats - check text is stripped, including multiple spaces
Assert.AreEqual("321 1/3", dfUS.FormatRawCellContents(321.321, -1, "# #/#"));
Assert.AreEqual("321 1/3", dfUS.FormatRawCellContents(321.321, -1, "#\" \" #/#"));
Assert.AreEqual("321 1/3", dfUS.FormatRawCellContents(321.321, -1, "#\"FRED\" #/#"));
Assert.AreEqual("321 1/3", dfUS.FormatRawCellContents(321.321, -1, "#\\ #/#"));
Assert.AreEqual("321 1/3", dfUS.FormatRawCellContents(321.321, -1, "# \\q#/#"));
// Cases that were very slow
Assert.AreEqual("321 1/3", dfUS.FormatRawCellContents(321.321, -1, "0\" \"?/?;?/?")); // 0" "?/?;?/? - length of -ve part was used
Assert.AreEqual("321 1/3", dfUS.FormatRawCellContents(321.321, -1, "0 \"#\"\\#\\#?/?")); // 0 "#"\#\#?/? - length of text was used
Assert.AreEqual("321 295/919", dfUS.FormatRawCellContents(321.321, -1, "# #/###"));
Assert.AreEqual("321 321/1000", dfUS.FormatRawCellContents(321.321, -1, "# #/####")); // Code limits to #### as that is as slow as we want to get
Assert.AreEqual("321 321/1000", dfUS.FormatRawCellContents(321.321, -1, "# #/##########"));
// Not a valid fraction formats (too many #/# or ?/?) - hence the strange expected results
/*Assert.AreEqual("321 / ?/?", dfUS.FormatRawCellContents(321.321, -1, "# #/# ?/?"));
Assert.AreEqual("321 / /", dfUS.FormatRawCellContents(321.321, -1, "# #/# #/#"));
Assert.AreEqual("321 ?/? ?/?", dfUS.FormatRawCellContents(321.321, -1, "# ?/? ?/?"));
Assert.AreEqual("321 ?/? / /", dfUS.FormatRawCellContents(321.321, -1, "# ?/? #/# #/#"));
*/
//Bug54686 patch sets default behavior of # #/## if there is a failure to parse
Assert.AreEqual("321 1/3", dfUS.FormatRawCellContents(321.321, -1, "# #/# ?/?"));
Assert.AreEqual("321 1/3", dfUS.FormatRawCellContents(321.321, -1, "# #/# #/#"));
Assert.AreEqual("321 1/3", dfUS.FormatRawCellContents(321.321, -1, "# ?/? ?/?"));
Assert.AreEqual("321 1/3", dfUS.FormatRawCellContents(321.321, -1, "# ?/? #/# #/#"));
// Where both p and n don't include a fraction, so cannot always be formatted
//Assert.AreEqual("123", dfUS.FormatRawCellContents(-123.321, -1, "0 ?/?;0"));
//Bug54868 patch has a hit on the first string before the ";"
Assert.AreEqual("-123 1/3", dfUS.FormatRawCellContents(-123.321, -1, "0 ?/?;0"));
//Bug53150 formatting a whole number with fractions should just give the number
Assert.AreEqual("1", dfUS.FormatRawCellContents(1.0, -1, "# #/#"));
Assert.AreEqual("11", dfUS.FormatRawCellContents(11.0, -1, "# #/#"));
}
public void TestNegativeZero()
{
//DataFormatter dfUS = new DataFormatter(Locale.US);
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
String all2dp = "00.00";
String alln1dp = "(00.0)";
String p1dp_n1dp = "00.0;(00.0)";
String p2dp_n1dp = "00.00;(00.0)";
String p2dp_n1dp_z0 = "00.00;(00.0);0";
String all2dpTSP = "00.00_x";
String p2dp_n2dpTSP = "00.00_x;(00.00)_x";
Assert.AreEqual("12.34", dfUS.FormatRawCellContents(12.343, -1, all2dp));
Assert.AreEqual("12.34", dfUS.FormatRawCellContents(12.343, -1, p2dp_n1dp));
Assert.AreEqual("12.34", dfUS.FormatRawCellContents(12.343, -1, p2dp_n1dp_z0));
Assert.AreEqual("(12.3)", dfUS.FormatRawCellContents(12.343, -1, alln1dp));
Assert.AreEqual("-(12.3)", dfUS.FormatRawCellContents(-12.343, -1, alln1dp));
Assert.AreEqual("12.3", dfUS.FormatRawCellContents(12.343, -1, p1dp_n1dp));
Assert.AreEqual("(12.3)", dfUS.FormatRawCellContents(-12.343, -1, p1dp_n1dp));
Assert.AreEqual("-12.34", dfUS.FormatRawCellContents(-12.343, -1, all2dp));
// TODO - fix case of negative subpattern differing from the
// positive one by more than just the prefix+suffix, which
// is all DecimalFormat supports...
// Assert.AreEqual("(12.3)", dfUS.FormatRawCellContents(-12.343, -1, p2dp_n1dp));
// Assert.AreEqual("(12.3)", dfUS.FormatRawCellContents(-12.343, -1, p2dp_n1dp_z0));
Assert.AreEqual("00.00", dfUS.FormatRawCellContents(0, -1, all2dp));
Assert.AreEqual("00.00", dfUS.FormatRawCellContents(0, -1, p2dp_n1dp));
Assert.AreEqual("0", dfUS.FormatRawCellContents(0, -1, p2dp_n1dp_z0));
// Spaces are skipped
Assert.AreEqual("12.34", dfUS.FormatRawCellContents(12.343, -1, all2dpTSP));
Assert.AreEqual("12.34", dfUS.FormatRawCellContents(12.343, -1, p2dp_n2dpTSP));
Assert.AreEqual("(12.34)", dfUS.FormatRawCellContents(-12.343, -1, p2dp_n2dpTSP));
//String p2dp_n1dpTSP = "00.00_x;(00.0)_x";
// Assert.AreEqual("12.34", dfUS.FormatRawCellContents(12.343, -1, p2dp_n1dpTSP));
// Assert.AreEqual("(12.3)", dfUS.FormatRawCellContents(-12.343, -1, p2dp_n1dpTSP));
}
public void TestColoursAndBrackets()
{
//DataFormatter dfUS = new DataFormatter(Locale.US);
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
// Without currency symbols
String[] formats = new String[] {
"#,##0.00;[Blue](#,##0.00)",
};
foreach (String format in formats)
{
Assert.AreEqual(
"12.34",
dfUS.FormatRawCellContents(12.343, -1, format),
"Wrong format for: " + format
);
Assert.AreEqual(
"(12.34)",
dfUS.FormatRawCellContents(-12.343, -1, format),
"Wrong format for: " + format
);
}
// With
formats = new String[] {
"$#,##0.00;[Red]($#,##0.00)"
};
foreach (String format in formats)
{
Assert.AreEqual(
"$12.34",
dfUS.FormatRawCellContents(12.343, -1, format),
"Wrong format for: " + format
);
Assert.AreEqual(
"($12.34)",
dfUS.FormatRawCellContents(-12.343, -1, format),
"Wrong format for: " + format
);
}
}
private void HandleNonStringCell(StringBuilder text, ICell cell, DataFormatter formatter)
{
CellType type = cell.CellType;
if (type == CellType.Formula)
{
type = cell.CachedFormulaResultType;
}
if (type == CellType.Numeric)
{
ICellStyle cs = cell.CellStyle;
if (cs.GetDataFormatString()!= null)
{
text.Append(formatter.FormatRawCellContents(
cell.NumericCellValue, cs.DataFormat, cs.GetDataFormatString()
));
return;
}
}
// No supported styling applies to this cell
XSSFCell xcell = (XSSFCell)cell;
text.Append(xcell.GetRawValue());
}
public void TestElapsedTime()
{
//DataFormatter dfUS = new DataFormatter(Locale.US);
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
double hour = 1.0 / 24.0;
Assert.AreEqual("01:00", dfUS.FormatRawCellContents(1 * hour, -1, "hh:mm"));
Assert.AreEqual("05:00", dfUS.FormatRawCellContents(5 * hour, -1, "hh:mm"));
Assert.AreEqual("20:00", dfUS.FormatRawCellContents(20 * hour, -1, "hh:mm"));
Assert.AreEqual("23:00", dfUS.FormatRawCellContents(23 * hour, -1, "hh:mm"));
Assert.AreEqual("00:00", dfUS.FormatRawCellContents(24 * hour, -1, "hh:mm"));
Assert.AreEqual("02:00", dfUS.FormatRawCellContents(26 * hour, -1, "hh:mm"));
Assert.AreEqual("20:00", dfUS.FormatRawCellContents(44 * hour, -1, "hh:mm"));
Assert.AreEqual("02:00", dfUS.FormatRawCellContents(50 * hour, -1, "hh:mm"));
Assert.AreEqual("01:00", dfUS.FormatRawCellContents(1 * hour, -1, "[hh]:mm"));
Assert.AreEqual("05:00", dfUS.FormatRawCellContents(5 * hour, -1, "[hh]:mm"));
Assert.AreEqual("20:00", dfUS.FormatRawCellContents(20 * hour, -1, "[hh]:mm"));
Assert.AreEqual("23:00", dfUS.FormatRawCellContents(23 * hour, -1, "[hh]:mm"));
Assert.AreEqual("24:00", dfUS.FormatRawCellContents(24 * hour, -1, "[hh]:mm"));
Assert.AreEqual("26:00", dfUS.FormatRawCellContents(26 * hour, -1, "[hh]:mm"));
Assert.AreEqual("44:00", dfUS.FormatRawCellContents(44 * hour, -1, "[hh]:mm"));
Assert.AreEqual("50:00", dfUS.FormatRawCellContents(50 * hour, -1, "[hh]:mm"));
Assert.AreEqual("01", dfUS.FormatRawCellContents(1 * hour, -1, "[hh]"));
Assert.AreEqual("05", dfUS.FormatRawCellContents(5 * hour, -1, "[hh]"));
Assert.AreEqual("20", dfUS.FormatRawCellContents(20 * hour, -1, "[hh]"));
Assert.AreEqual("23", dfUS.FormatRawCellContents(23 * hour, -1, "[hh]"));
Assert.AreEqual("24", dfUS.FormatRawCellContents(24 * hour, -1, "[hh]"));
Assert.AreEqual("26", dfUS.FormatRawCellContents(26 * hour, -1, "[hh]"));
Assert.AreEqual("44", dfUS.FormatRawCellContents(44 * hour, -1, "[hh]"));
Assert.AreEqual("50", dfUS.FormatRawCellContents(50 * hour, -1, "[hh]"));
double minute = 1.0 / 24.0 / 60.0;
Assert.AreEqual("01:00", dfUS.FormatRawCellContents(1 * minute, -1, "[mm]:ss"));
Assert.AreEqual("05:00", dfUS.FormatRawCellContents(5 * minute, -1, "[mm]:ss"));
Assert.AreEqual("20:00", dfUS.FormatRawCellContents(20 * minute, -1, "[mm]:ss"));
Assert.AreEqual("23:00", dfUS.FormatRawCellContents(23 * minute, -1, "[mm]:ss"));
Assert.AreEqual("24:00", dfUS.FormatRawCellContents(24 * minute, -1, "[mm]:ss"));
Assert.AreEqual("26:00", dfUS.FormatRawCellContents(26 * minute, -1, "[mm]:ss"));
Assert.AreEqual("44:00", dfUS.FormatRawCellContents(44 * minute, -1, "[mm]:ss"));
Assert.AreEqual("50:00", dfUS.FormatRawCellContents(50 * minute, -1, "[mm]:ss"));
Assert.AreEqual("59:00", dfUS.FormatRawCellContents(59 * minute, -1, "[mm]:ss"));
Assert.AreEqual("60:00", dfUS.FormatRawCellContents(60 * minute, -1, "[mm]:ss"));
Assert.AreEqual("61:00", dfUS.FormatRawCellContents(61 * minute, -1, "[mm]:ss"));
Assert.AreEqual("119:00", dfUS.FormatRawCellContents(119 * minute, -1, "[mm]:ss"));
Assert.AreEqual("120:00", dfUS.FormatRawCellContents(120 * minute, -1, "[mm]:ss"));
Assert.AreEqual("121:00", dfUS.FormatRawCellContents(121 * minute, -1, "[mm]:ss"));
Assert.AreEqual("01", dfUS.FormatRawCellContents(1 * minute, -1, "[mm]"));
Assert.AreEqual("05", dfUS.FormatRawCellContents(5 * minute, -1, "[mm]"));
Assert.AreEqual("20", dfUS.FormatRawCellContents(20 * minute, -1, "[mm]"));
Assert.AreEqual("23", dfUS.FormatRawCellContents(23 * minute, -1, "[mm]"));
Assert.AreEqual("24", dfUS.FormatRawCellContents(24 * minute, -1, "[mm]"));
Assert.AreEqual("26", dfUS.FormatRawCellContents(26 * minute, -1, "[mm]"));
Assert.AreEqual("44", dfUS.FormatRawCellContents(44 * minute, -1, "[mm]"));
Assert.AreEqual("50", dfUS.FormatRawCellContents(50 * minute, -1, "[mm]"));
Assert.AreEqual("59", dfUS.FormatRawCellContents(59 * minute, -1, "[mm]"));
Assert.AreEqual("60", dfUS.FormatRawCellContents(60 * minute, -1, "[mm]"));
Assert.AreEqual("61", dfUS.FormatRawCellContents(61 * minute, -1, "[mm]"));
Assert.AreEqual("119", dfUS.FormatRawCellContents(119 * minute, -1, "[mm]"));
Assert.AreEqual("120", dfUS.FormatRawCellContents(120 * minute, -1, "[mm]"));
Assert.AreEqual("121", dfUS.FormatRawCellContents(121 * minute, -1, "[mm]"));
double second = 1.0 / 24.0 / 60.0 / 60.0;
Assert.AreEqual("86400", dfUS.FormatRawCellContents(86400 * second, -1, "[ss]"));
Assert.AreEqual("01", dfUS.FormatRawCellContents(1 * second, -1, "[ss]"));
Assert.AreEqual("05", dfUS.FormatRawCellContents(5 * second, -1, "[ss]"));
Assert.AreEqual("20", dfUS.FormatRawCellContents(20 * second, -1, "[ss]"));
Assert.AreEqual("23", dfUS.FormatRawCellContents(23 * second, -1, "[ss]"));
Assert.AreEqual("24", dfUS.FormatRawCellContents(24 * second, -1, "[ss]"));
Assert.AreEqual("26", dfUS.FormatRawCellContents(26 * second, -1, "[ss]"));
Assert.AreEqual("44", dfUS.FormatRawCellContents(44 * second, -1, "[ss]"));
Assert.AreEqual("50", dfUS.FormatRawCellContents(50 * second, -1, "[ss]"));
Assert.AreEqual("59", dfUS.FormatRawCellContents(59 * second, -1, "[ss]"));
Assert.AreEqual("60", dfUS.FormatRawCellContents(60 * second, -1, "[ss]"));
Assert.AreEqual("61", dfUS.FormatRawCellContents(61 * second, -1, "[ss]"));
Assert.AreEqual("119", dfUS.FormatRawCellContents(119 * second, -1, "[ss]"));
Assert.AreEqual("120", dfUS.FormatRawCellContents(120 * second, -1, "[ss]"));
Assert.AreEqual("121", dfUS.FormatRawCellContents(121 * second, -1, "[ss]"));
Assert.AreEqual("27:18:08", dfUS.FormatRawCellContents(1.1376, -1, "[h]:mm:ss"));
Assert.AreEqual("28:48:00", dfUS.FormatRawCellContents(1.2, -1, "[h]:mm:ss"));
Assert.AreEqual("29:31:12", dfUS.FormatRawCellContents(1.23, -1, "[h]:mm:ss"));
Assert.AreEqual("31:26:24", dfUS.FormatRawCellContents(1.31, -1, "[h]:mm:ss"));
Assert.AreEqual("27:18:08", dfUS.FormatRawCellContents(1.1376, -1, "[hh]:mm:ss"));
Assert.AreEqual("28:48:00", dfUS.FormatRawCellContents(1.2, -1, "[hh]:mm:ss"));
Assert.AreEqual("29:31:12", dfUS.FormatRawCellContents(1.23, -1, "[hh]:mm:ss"));
Assert.AreEqual("31:26:24", dfUS.FormatRawCellContents(1.31, -1, "[hh]:mm:ss"));
Assert.AreEqual("57:07.2", dfUS.FormatRawCellContents(.123, -1, "mm:ss.0;@"));
Assert.AreEqual("57:41.8", dfUS.FormatRawCellContents(.1234, -1, "mm:ss.0;@"));
Assert.AreEqual("57:41.76", dfUS.FormatRawCellContents(.1234, -1, "mm:ss.00;@"));
Assert.AreEqual("57:41.760", dfUS.FormatRawCellContents(.1234, -1, "mm:ss.000;@"));
Assert.AreEqual("24:00.0", dfUS.FormatRawCellContents(123456.6, -1, "mm:ss.0"));
}
public void TestOther()
{
//DataFormatter dfUS = new DataFormatter(Locale.US, true);
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"), true);
Assert.AreEqual(" 12.34 ", dfUS.FormatRawCellContents(12.34, -1, "_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"??_-;_-@_-"));
Assert.AreEqual("-12.34 ", dfUS.FormatRawCellContents(-12.34, -1, "_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"??_-;_-@_-"));
Assert.AreEqual(" - ", dfUS.FormatRawCellContents(0.0, -1, "_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"??_-;_-@_-"));
Assert.AreEqual(" $- ", dfUS.FormatRawCellContents(0.0, -1, "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* \"-\"??_-;_-@_-"));
}
public void TestExcelStyleDateFormatterStringOnMillis()
{
// Test directly with the .000 style
SimpleDateFormat formatter1 = new ExcelStyleDateFormatter("ss.000");
CultureInfo culture = System.Globalization.CultureInfo.GetCultureInfo("en-US");
DateTime dt = DateTime.Now.Date;
Assert.AreEqual("00.001", formatter1.Format(dt.AddMilliseconds(1L), culture));
Assert.AreEqual("00.010", formatter1.Format(dt.AddMilliseconds(10L), culture));
Assert.AreEqual("00.100", formatter1.Format(dt.AddMilliseconds(100L), culture));
Assert.AreEqual("01.000", formatter1.Format(dt.AddMilliseconds(1000L), culture));
Assert.AreEqual("01.001", formatter1.Format(dt.AddMilliseconds(1001L), culture));
Assert.AreEqual("10.000", formatter1.Format(dt.AddMilliseconds(10000L), culture));
Assert.AreEqual("10.001", formatter1.Format(dt.AddMilliseconds(10001L), culture));
// Test directly with the .SSS style
SimpleDateFormat formatter2 = new ExcelStyleDateFormatter("ss.fff");
Assert.AreEqual("00.001", formatter2.Format(dt.AddMilliseconds(1L), culture));
Assert.AreEqual("00.010", formatter2.Format(dt.AddMilliseconds(10L), culture));
Assert.AreEqual("00.100", formatter2.Format(dt.AddMilliseconds(100L), culture));
Assert.AreEqual("01.000", formatter2.Format(dt.AddMilliseconds(1000L), culture));
Assert.AreEqual("01.001", formatter2.Format(dt.AddMilliseconds(1001L), culture));
Assert.AreEqual("10.000", formatter2.Format(dt.AddMilliseconds(10000L), culture));
Assert.AreEqual("10.001", formatter2.Format(dt.AddMilliseconds(10001L), culture));
// Test via DataFormatter
DataFormatter dfUS = new DataFormatter(culture, true);
Assert.AreEqual("01.010", dfUS.FormatRawCellContents(0.0000116898, -1, "ss.000"));
}
public void TestMMMMM()
{
//DataFormatter dfUS = new DataFormatter(Locale.US);
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
DateTime c = new DateTime(2010, 6, 1, 2, 0, 0, 0);
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.GetCultureInfo("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 TestPaddingSpacesCSV()
{
//DataFormatter dfUS = new DataFormatter(Locale.US);
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"), true);
Assert.AreEqual("12.34 ", dfUS.FormatRawCellContents(12.343, -1, "##.##_ "));
Assert.AreEqual("-12.34 ", dfUS.FormatRawCellContents(-12.343, -1, "##.##_ "));
Assert.AreEqual(". ", dfUS.FormatRawCellContents(0.0, -1, "##.##_ "));
Assert.AreEqual("12.34 ", dfUS.FormatRawCellContents(12.343, -1, "##.##_1"));
Assert.AreEqual("-12.34 ", dfUS.FormatRawCellContents(-12.343, -1, "##.##_1"));
Assert.AreEqual(". ", dfUS.FormatRawCellContents(0.0, -1, "##.##_1"));
Assert.AreEqual("12.34 ", dfUS.FormatRawCellContents(12.343, -1, "##.##_)"));
Assert.AreEqual("-12.34 ", dfUS.FormatRawCellContents(-12.343, -1, "##.##_)"));
Assert.AreEqual(". ", dfUS.FormatRawCellContents(0.0, -1, "##.##_)"));
Assert.AreEqual(" 12.34", dfUS.FormatRawCellContents(12.343, -1, "_-##.##"));
Assert.AreEqual("- 12.34", dfUS.FormatRawCellContents(-12.343, -1, "_-##.##"));
Assert.AreEqual(" .", dfUS.FormatRawCellContents(0.0, -1, "_-##.##"));
Assert.AreEqual("12.34", dfUS.FormatRawCellContents(12.343, -1, "##.##* "));
Assert.AreEqual("-12.34", dfUS.FormatRawCellContents(-12.343, -1, "##.##* "));
Assert.AreEqual(".", dfUS.FormatRawCellContents(0.0, -1, "##.##* "));
Assert.AreEqual("12.34", dfUS.FormatRawCellContents(12.343, -1, "##.##*1"));
Assert.AreEqual("-12.34", dfUS.FormatRawCellContents(-12.343, -1, "##.##*1"));
Assert.AreEqual(".", dfUS.FormatRawCellContents(0.0, -1, "##.##*1"));
Assert.AreEqual("12.34", dfUS.FormatRawCellContents(12.343, -1, "##.##*)"));
Assert.AreEqual("-12.34", dfUS.FormatRawCellContents(-12.343, -1, "##.##*)"));
Assert.AreEqual(".", dfUS.FormatRawCellContents(0.0, -1, "##.##*)"));
Assert.AreEqual("12.34", dfUS.FormatRawCellContents(12.343, -1, "*-##.##"));
Assert.AreEqual("-12.34", dfUS.FormatRawCellContents(-12.343, -1, "*-##.##"));
Assert.AreEqual(".", dfUS.FormatRawCellContents(0.0, -1, "*-##.##"));
}
public void Test47490()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("GeneralFormatTests.xlsx");
ISheet s = wb.GetSheetAt(1);
IRow r;
DataFormatter df = new DataFormatter();
r = s.GetRow(1);
Assert.AreEqual(1.0, r.GetCell(2).NumericCellValue);
Assert.AreEqual("General", r.GetCell(2).CellStyle.GetDataFormatString());
Assert.AreEqual("1", df.FormatCellValue(r.GetCell(2)));
Assert.AreEqual("1", df.FormatRawCellContents(1.0, -1, "@"));
Assert.AreEqual("1", df.FormatRawCellContents(1.0, -1, "General"));
r = s.GetRow(2);
Assert.AreEqual(12.0, r.GetCell(2).NumericCellValue);
Assert.AreEqual("General", r.GetCell(2).CellStyle.GetDataFormatString());
Assert.AreEqual("12", df.FormatCellValue(r.GetCell(2)));
Assert.AreEqual("12", df.FormatRawCellContents(12.0, -1, "@"));
Assert.AreEqual("12", df.FormatRawCellContents(12.0, -1, "General"));
r = s.GetRow(3);
Assert.AreEqual(123.0, r.GetCell(2).NumericCellValue);
Assert.AreEqual("General", r.GetCell(2).CellStyle.GetDataFormatString());
Assert.AreEqual("123", df.FormatCellValue(r.GetCell(2)));
Assert.AreEqual("123", df.FormatRawCellContents(123.0, -1, "@"));
Assert.AreEqual("123", df.FormatRawCellContents(123.0, -1, "General"));
}