public CellFormatResult Apply(ICell cell) { try { return(cellformat.Apply(cell)); } catch (Exception) { var formatStr = cell.CellStyle.GetDataFormatString(); var mc = new Regex(@"(yy|M|d|H|s|ms)").Match(formatStr); /* * 目前全部不能正常转换的日期格式都转换成 yyyy - MM - dd 的形式 * 比如说:【[$-F800]dddd\,\ mmmm\ dd\,\ yyyy】这个格式 * 稍微 google 了下( https://msdn.microsoft.com/en-us/library/dd318693(VS.85).aspx) * 这个字符串 0x0800 表示 [System default locale language] * 因时间关系,只能干完手头的活之后再慢慢研究了。 */ if (mc.Success) { return(CellFormat.GetInstance("yyyy-MM-dd").Apply(cell)); } else { return(cellformat.Apply(cell.ToString() + "<!-- This is the bug of NPOI, Maybe you should modify the file which name is \"MyCellFormat.cs\" -->")); } } }
public void TestApplyCellForDateFormat() { Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US"); // Create a workbook, IRow and ICell to test with IWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet(); IRow row = sheet.CreateRow(0); ICell cell0 = row.CreateCell(0); ICell cell1 = row.CreateCell(1); CellFormat cf = CellFormat.GetInstance("dd/mm/yyyy"); cell0.SetCellValue(10); CellFormatResult result0 = cf.Apply(cell0); Assert.AreEqual("10/01/1900", result0.Text); cell1.SetCellValue(-1); CellFormatResult result1 = cf.Apply(cell1); Assert.AreEqual(_255_POUND_SIGNS, result1.Text); wb.Close(); }
public void TestSome() { CellFormat fmt = CellFormat.GetInstance( "\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)"); fmt.Apply(1.1); }
public void TestThreePartComplexFormat1() { Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US"); // verify a rather complex format found e.g. in http://wahl.land-oberoesterreich.gv.at/Downloads/bp10.xls CellFormatPart posPart = new CellFormatPart("[$-F400]h:mm:ss\\ AM/PM"); Assert.IsNotNull(posPart); DateTime baseTime = new DateTime(1970, 1, 1, 0, 0, 0); double exceldata = DateUtil.GetExcelDate(baseTime.AddMilliseconds(12345)); //format part 'h', means hour, using a 12-hour clock from 1 to 12.(in excel and .net framework) //so the excepted value should be 12:00:12 AM Assert.AreEqual("12:00:12 AM", posPart.Apply(baseTime.AddMilliseconds(12345)).Text); CellFormatPart negPart = new CellFormatPart("[$-F40]h:mm:ss\\ AM/PM"); Assert.IsNotNull(negPart); Assert.AreEqual("12:00:12 AM", posPart.Apply(baseTime.AddMilliseconds(12345)).Text); //Assert.IsNotNull(new CellFormatPart("_-* \"\"??_-;_-@_-")); CellFormat instance = CellFormat.GetInstance("[$-F400]h:mm:ss\\ AM/PM;[$-F40]h:mm:ss\\ AM/PM;_-* \"\"??_-;_-@_-"); Assert.IsNotNull(instance); Assert.AreEqual("12:00:12 AM", instance.Apply(baseTime.AddMilliseconds(12345)).Text); }
public void TestApplyFormatHasThreePartsFirstIsDateFirstTwoHaveCondition() { TestCases.CultureShim.SetCurrentCulture("en-US"); // Create a workbook, IRow and ICell to test with IWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet(); IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(0); CellFormat cf = CellFormat.GetInstance("[>=100]0.00;[>=10]dd/mm/yyyy;0.0"); cell.SetCellValue(100); Assert.AreEqual("100.00", cf.Apply(cell).Text); cell.SetCellValue(10); Assert.AreEqual("10/01/1900", cf.Apply(cell).Text); cell.SetCellValue(0); Assert.AreEqual("0.0", cf.Apply(cell).Text); cell.SetCellValue(-10); Assert.AreEqual("-10.0", cf.Apply(cell).Text); cell.SetCellValue("abc"); Assert.AreEqual("abc", cf.Apply(cell).Text); }
public void TestApplyFormatHasTwoPartsFirstHasConditionSecondIsGeneral() { Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US"); // Create a workbook, IRow and ICell to test with IWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet(); IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(0); CellFormat cf = CellFormat.GetInstance("[>=100]0.00;General"); cell.SetCellValue(100); Assert.AreEqual("100.00", cf.Apply(cell).Text); cell.SetCellValue(10); Assert.AreEqual("10", cf.Apply(cell).Text); cell.SetCellValue(0); Assert.AreEqual("0", cf.Apply(cell).Text); cell.SetCellValue(-10); Assert.AreEqual("-10", cf.Apply(cell).Text); cell.SetCellValue("abc"); Assert.AreEqual("abc", cf.Apply(cell).Text); }
public void TestApplyFormatHasTwoPartsBothHaveCondition() { Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US"); // Create a workbook, IRow and ICell to test with IWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet(); IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(0); CellFormat cf = CellFormat.GetInstance("[>=100]0.00;[>=10]0.000"); cell.SetCellValue(100); Assert.AreEqual("100.00", cf.Apply(cell).Text); cell.SetCellValue(10); Assert.AreEqual("10.000", cf.Apply(cell).Text); cell.SetCellValue(0); Assert.AreEqual(_255_POUND_SIGNS, cf.Apply(cell).Text); cell.SetCellValue(-0.123456789012345); Assert.AreEqual(_255_POUND_SIGNS, cf.Apply(cell).Text); cell.SetCellValue(-10); Assert.AreEqual(_255_POUND_SIGNS, cf.Apply(cell).Text); cell.SetCellValue("abc"); Assert.AreEqual("abc", cf.Apply(cell).Text); }
public void TestApplyFormatHasTwoPartsFirstHasCondition() { TestCases.CultureShim.SetCurrentCulture("en-US"); // Create a workbook, IRow and ICell to test with IWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet(); IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(0); CellFormat cf = CellFormat.GetInstance("[>=100]0.00;0.000"); cell.SetCellValue(100); Assert.AreEqual("100.00", cf.Apply(cell).Text); cell.SetCellValue(10); Assert.AreEqual("10.000", cf.Apply(cell).Text); cell.SetCellValue(0.123456789012345); Assert.AreEqual("0.123", cf.Apply(cell).Text); cell.SetCellValue(0); Assert.AreEqual("0.000", cf.Apply(cell).Text); cell.SetCellValue(-10); Assert.AreEqual("-10.000", cf.Apply(cell).Text); cell.SetCellValue("abc"); Assert.AreEqual("abc", cf.Apply(cell).Text); cell.SetCellValue("TRUE"); Assert.AreEqual("TRUE", cf.Apply(cell).Text); }
public void TestZeroFormatHasOnePart() { CellFormat fmt = CellFormat.GetInstance("0.00"); CellFormatResult result = fmt.Apply(0.0); Assert.AreEqual("0.00", result.Text); }
public void TestNegativeFormatHasPosAndNegParts() { CellFormat fmt = CellFormat.GetInstance("0.00;-0.00"); CellFormatResult result = fmt.Apply(-12.345); Assert.AreEqual("-12.35", result.Text); }
public void TestApplyFormatHasThreePartsFirstTwoHaveConditionThirdIsGeneral() { // Create a workbook, IRow and ICell to test with IWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet(); IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(0); CellFormat cf = CellFormat.GetInstance("[>=100]0.00;[>=10]0.000;General"); cell.SetCellValue(100); Assert.AreEqual("100.00", cf.Apply(cell).Text); cell.SetCellValue(10); Assert.AreEqual("10.000", cf.Apply(cell).Text); cell.SetCellValue(0); Assert.AreEqual("0", cf.Apply(cell).Text); cell.SetCellValue(-10); Assert.AreEqual("-10", cf.Apply(cell).Text); cell.SetCellValue("abc"); Assert.AreEqual("abc", cf.Apply(cell).Text); }
public void TestApplyFormatHasFourPartsSecondHasCondition() { // Create a workbook, IRow and ICell to test with IWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet(); IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(0); CellFormat cf = CellFormat.GetInstance("0.00;[>=100]0.000;0.0000;~~@~~"); cell.SetCellValue(100); Assert.AreEqual("100.00", cf.Apply(cell).Text); cell.SetCellValue(10); Assert.AreEqual("10.00", cf.Apply(cell).Text); cell.SetCellValue(0.123456789012345); Assert.AreEqual("0.12", cf.Apply(cell).Text); cell.SetCellValue(0); Assert.AreEqual("0.0000", cf.Apply(cell).Text); cell.SetCellValue(-10); Assert.AreEqual("-10.0000", cf.Apply(cell).Text); cell.SetCellValue("abc"); Assert.AreEqual("~~abc~~", cf.Apply(cell).Text); cell.SetCellValue(true); Assert.AreEqual("~~TRUE~~", cf.Apply(cell).Text); }
public void TestPositiveFormatHasOnePart() { CellFormat fmt = CellFormat.GetInstance("0.00"); CellFormatResult result = fmt.Apply(12.345); Assert.AreEqual("12.35", result.Text); }
public void TestApplyFormatHasOnePartAndPartHasCondition() { // Create a workbook, IRow and ICell to test with IWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet(); IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(0); CellFormat cf = CellFormat.GetInstance("[>=100]0.00"); cell.SetCellValue(100); Assert.AreEqual("100.00", cf.Apply(cell).Text); cell.SetCellValue(10); Assert.AreEqual("10", cf.Apply(cell).Text); cell.SetCellValue(0.123456789012345); Assert.AreEqual("0.123456789", cf.Apply(cell).Text); cell.SetCellValue(0); Assert.AreEqual("0", cf.Apply(cell).Text); cell.SetCellValue("abc"); Assert.AreEqual("abc", cf.Apply(cell).Text); }
public void TestApplyFormatHasFourPartsFirstTwoHaveCondition() { Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US"); // Create a workbook, IRow and ICell to test with IWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet(); IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(0); CellFormat cf = CellFormat.GetInstance("[>=100]0.00;[>=10]0.000;0.0000;~~@~~"); cell.SetCellValue(100); Assert.AreEqual("100.00", cf.Apply(cell).Text); cell.SetCellValue(10); Assert.AreEqual("10.000", cf.Apply(cell).Text); cell.SetCellValue(0); Assert.AreEqual("0.0000", cf.Apply(cell).Text); cell.SetCellValue(-10); Assert.AreEqual("-10.0000", cf.Apply(cell).Text); cell.SetCellValue("abc"); Assert.AreEqual("~~abc~~", cf.Apply(cell).Text); cell.SetCellValue(true); Assert.AreEqual("~~TRUE~~", cf.Apply(cell).Text); }
public void TestApplyLabelCellForDateFormatAndNegativeFormat() { Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US"); // Create a workbook, IRow and ICell to test with IWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet(); IRow row = sheet.CreateRow(0); ICell cell0 = row.CreateCell(0); ICell cell1 = row.CreateCell(1); CellFormat cf = CellFormat.GetInstance("dd/mm/yyyy;(0)"); Label label0 = new Label(); Label label1 = new Label(); cell0.SetCellValue(10); CellFormatResult result0 = cf.Apply(label0, cell0); Assert.AreEqual("10/01/1900", result0.Text); Assert.AreEqual("10/01/1900", label0.Text); cell1.SetCellValue(-1); CellFormatResult result1 = cf.Apply(label1, cell1); Assert.AreEqual("(1)", result1.Text); Assert.AreEqual("(1)", label1.Text); }
public void TestApplyFormatHasFourPartsFirstHasCondition() { Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US"); // Create a workbook, IRow and ICell to test with IWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet(); IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(0); CellFormat cf = CellFormat.GetInstance("[>=100]0.00;0.000;0.0000;~~@~~"); cell.SetCellValue(100); Assert.AreEqual("100.00", cf.Apply(cell).Text); cell.SetCellValue(10); Assert.AreEqual("10.0000", cf.Apply(cell).Text); cell.SetCellValue(0.123456789012345); Assert.AreEqual("0.1235", cf.Apply(cell).Text); cell.SetCellValue(0); Assert.AreEqual("0.0000", cf.Apply(cell).Text); // Second format part ('0.000') is used for negative numbers // so result does not have a minus sign cell.SetCellValue(-10); Assert.AreEqual("10.000", cf.Apply(cell).Text); cell.SetCellValue("abc"); Assert.AreEqual("~~abc~~", cf.Apply(cell).Text); }
public void TestNegativeFormatHasPosAndNegParts() { Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US"); CellFormat fmt = CellFormat.GetInstance("0.00;-0.00"); CellFormatResult result = fmt.Apply(-12.345); Assert.AreEqual("-12.35", result.Text); }
public void TestSome() { Label l = new Label(); CellFormat fmt = CellFormat.GetInstance( "\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)"); fmt.Apply(l, 1.1); }
public void TestZeroFormatHasOnePart() { TestCases.CultureShim.SetCurrentCulture("en-US"); CellFormat fmt = CellFormat.GetInstance("0.00"); CellFormatResult result = fmt.Apply(0.0); Assert.AreEqual("0.00", result.Text); }
public void TestApplyObjectDate() { Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US"); CellFormat cf1 = CellFormat.GetInstance("m/d/yyyy"); DateTime date1 = new SimpleDateFormat("M/d/y").Parse("01/11/2012"); Assert.AreEqual("1/11/2012", cf1.Apply(date1).Text); }
public void TestPositiveFormatHasPosAndNegParts() { TestCases.CultureShim.SetCurrentCulture("en-US"); CellFormat fmt = CellFormat.GetInstance("0.00;-0.00"); CellFormatResult result = fmt.Apply(12.345); Assert.AreEqual("12.35", result.Text); }
public void TestZeroFormatHasOnePart() { Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US"); CellFormat fmt = CellFormat.GetInstance("0.00"); CellFormatResult result = fmt.Apply(0.0); Assert.AreEqual("0.00", result.Text); }
public void TestFormatWithFourSections() { CellFormat fmt = CellFormat.GetInstance("0.00;-0.00;-; @ "); Assert.AreEqual("12.35", fmt.Apply(12.345).Text); Assert.AreEqual("-12.35", fmt.Apply(-12.345).Text); Assert.AreEqual("-", fmt.Apply(0.0).Text); Assert.AreEqual(" abc ", fmt.Apply("abc").Text); }
public void TestFormatWithThreeSections() { TestCases.CultureShim.SetCurrentCulture("en-US"); CellFormat fmt = CellFormat.GetInstance("0.00;-0.00;-"); Assert.AreEqual("12.35", fmt.Apply(12.345).Text); Assert.AreEqual("-12.35", fmt.Apply(-12.345).Text); Assert.AreEqual("-", fmt.Apply(0.0).Text); Assert.AreEqual("abc", fmt.Apply("abc").Text); }
public void TestApplyObjectBoolean() { CellFormat cf1 = CellFormat.GetInstance("0"); CellFormat cf2 = CellFormat.GetInstance("General"); CellFormat cf3 = CellFormat.GetInstance("@"); Assert.AreEqual("TRUE", cf1.Apply(true).Text); Assert.AreEqual("FALSE", cf2.Apply(false).Text); Assert.AreEqual("TRUE", cf3.Apply(true).Text); }
public void TestFormatWithFourSections() { Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US"); CellFormat fmt = CellFormat.GetInstance("0.00;-0.00;-; @ "); Assert.AreEqual("12.35", fmt.Apply(12.345).Text); Assert.AreEqual("-12.35", fmt.Apply(-12.345).Text); Assert.AreEqual("-", fmt.Apply(0.0).Text); Assert.AreEqual(" abc ", fmt.Apply("abc").Text); }
public void TestApplyLabelCellForAtFormat() { Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US"); // Create a workbook, IRow and ICell to test with IWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet(); IRow row = sheet.CreateRow(0); ICell cell0 = row.CreateCell(0); ICell cell1 = row.CreateCell(1); ICell cell2 = row.CreateCell(2); ICell cell3 = row.CreateCell(3); ICell cell4 = row.CreateCell(4); CellFormat cf = CellFormat.GetInstance("@"); Label label0 = new Label(); Label label1 = new Label(); Label label2 = new Label(); Label label3 = new Label(); Label label4 = new Label(); // case Cell.CELL_TYPE_BLANK CellFormatResult result0 = cf.Apply(label0, cell0); Assert.AreEqual(string.Empty, result0.Text); Assert.AreEqual(string.Empty, label0.Text); // case Cell.CELL_TYPE_BOOLEAN cell1.SetCellValue(true); CellFormatResult result1 = cf.Apply(label1, cell1); Assert.AreEqual("TRUE", result1.Text); Assert.AreEqual("TRUE", label1.Text); // case Cell.CELL_TYPE_NUMERIC cell2.SetCellValue(1.23); CellFormatResult result2 = cf.Apply(label2, cell2); Assert.AreEqual("1.23", result2.Text); Assert.AreEqual("1.23", label2.Text); cell3.SetCellValue(123.0); CellFormatResult result3 = cf.Apply(label3, cell3); Assert.AreEqual("123", result3.Text); Assert.AreEqual("123", label3.Text); // case Cell.CELL_TYPE_STRING cell4.SetCellValue("abc"); CellFormatResult result4 = cf.Apply(label4, cell4); Assert.AreEqual("abc", result4.Text); Assert.AreEqual("abc", label4.Text); }
public void TestAccountingFormats() { char pound = '\u00A3'; char euro = '\u20AC'; // Accounting -> 0 decimal places, default currency symbol String formatDft = "_-\"$\"* #,##0_-;\\-\"$\"* #,##0_-;_-\"$\"* \"-\"_-;_-@_-"; // Accounting -> 0 decimal places, US currency symbol String formatUS = "_-[$$-409]* #,##0_ ;_-[$$-409]* -#,##0 ;_-[$$-409]* \"-\"_-;_-@_-"; // Accounting -> 0 decimal places, UK currency symbol String formatUK = "_-[$" + pound + "-809]* #,##0_-;\\-[$" + pound + "-809]* #,##0_-;_-[$" + pound + "-809]* \"-\"??_-;_-@_-"; // French style accounting, euro sign comes after not before String formatFR = "_-#,##0* [$" + euro + "-40C]_-;\\-#,##0* [$" + euro + "-40C]_-;_-\"-\"??* [$" + euro + "-40C] _-;_-@_-"; // Has +ve, -ve and zero rules CellFormat cfDft = CellFormat.GetInstance(formatDft); CellFormat cfUS = CellFormat.GetInstance(formatUS); CellFormat cfUK = CellFormat.GetInstance(formatUK); CellFormat cfFR = CellFormat.GetInstance(formatFR); // For +ve numbers, should be Space + currency symbol + spaces + whole number with commas + space // (Except French, which is mostly reversed...) Assert.AreEqual(" $ 12 ", cfDft.Apply((12.33)).Text); Assert.AreEqual(" $ 12 ", cfDft.Apply((12.33)).Text); Assert.AreEqual(" $ 12 ", cfUS.Apply((12.33)).Text); Assert.AreEqual(" " + pound + " 12 ", cfUK.Apply((12.33)).Text); Assert.AreEqual(" 12 " + euro + " ", cfFR.Apply((12.33)).Text); Assert.AreEqual(" $ 16,789 ", cfDft.Apply((16789.2)).Text); Assert.AreEqual(" $ 16,789 ", cfUS.Apply((16789.2)).Text); Assert.AreEqual(" " + pound + " 16,789 ", cfUK.Apply((16789.2)).Text); Assert.AreEqual(" 16,789 " + euro + " ", cfFR.Apply((16789.2)).Text); // For -ve numbers, gets a bit more complicated... Assert.AreEqual("-$ 12 ", cfDft.Apply((-12.33)).Text); Assert.AreEqual(" $ -12 ", cfUS.Apply((-12.33)).Text); Assert.AreEqual("-" + pound + " 12 ", cfUK.Apply((-12.33)).Text); Assert.AreEqual("-12 " + euro + " ", cfFR.Apply((-12.33)).Text); Assert.AreEqual("-$ 16,789 ", cfDft.Apply((-16789.2)).Text); Assert.AreEqual(" $ -16,789 ", cfUS.Apply((-16789.2)).Text); Assert.AreEqual("-" + pound + " 16,789 ", cfUK.Apply((-16789.2)).Text); Assert.AreEqual("-16,789 " + euro + " ", cfFR.Apply((-16789.2)).Text); // For zero, should be Space + currency symbol + spaces + Minus + spaces Assert.AreEqual(" $ - ", cfDft.Apply((0)).Text); // TODO Fix the exception this incorrectly triggers //Assert.AreEqual(" $ - ", cfUS.Apply((0)).Text); // TODO Fix these to not have an incorrect bonus 0 on the end //Assert.AreEqual(" "+pound+" - ", cfUK.Apply((0)).Text); //Assert.AreEqual(" - "+euro+" ", cfFR.Apply((0)).Text); }
public void TestSimpleFractionFormat() { CellFormat cf1 = CellFormat.GetInstance("# ?/?"); // Create a workbook, row and cell to test with IWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet(); IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(0); cell.SetCellValue(123456.6); //System.out.println(cf1.apply(cell).text); Assert.AreEqual("123456 3/5", cf1.Apply(cell).Text); }