public void TestSetProperties() { String inputPath = OpenXml4NetTestDataSamples.GetSampleFileName("TestPackageCoreProperiesSetters.docx"); FileInfo outputFile = OpenXml4NetTestDataSamples.GetOutputFile("TestPackageCoreProperiesSettersOUTPUT.docx"); // Open namespace OPCPackage p = OPCPackage.Open(inputPath, PackageAccess.READ_WRITE); try { SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'"); DateTime dateToInsert = DateTime.Parse("2007-05-12T08:00:00Z").ToUniversalTime(); PackageProperties props = p.GetPackageProperties(); props.SetCategoryProperty("MyCategory"); props.SetContentStatusProperty("MyContentStatus"); props.SetContentTypeProperty("MyContentType"); props.SetCreatedProperty(new DateTime?(dateToInsert)); props.SetCreatorProperty("MyCreator"); props.SetDescriptionProperty("MyDescription"); props.SetIdentifierProperty("MyIdentifier"); props.SetKeywordsProperty("MyKeywords"); props.SetLanguageProperty("MyLanguage"); props.SetLastModifiedByProperty("Julien Chable"); props.SetLastPrintedProperty(new Nullable<DateTime>(dateToInsert)); props.SetModifiedProperty(new Nullable<DateTime>(dateToInsert)); props.SetRevisionProperty("2"); props.SetTitleProperty("MyTitle"); props.SetSubjectProperty("MySubject"); props.SetVersionProperty("2"); using (FileStream fs = outputFile.OpenWrite()) { // Save the namespace in the output directory p.Save(fs); } // Open the newly Created file to check core properties saved values. OPCPackage p2 = OPCPackage.Open(outputFile.Name, PackageAccess.READ); try { CompareProperties(p2); p2.Revert(); } finally { p2.Close(); } outputFile.Delete(); } finally { // use revert to not re-write the input file p.Revert(); } }
public void TestCoreProperties_bug51374() { SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'"); String strDate = "2007-05-12T08:00:00Z"; DateTime date = DateTime.Parse(strDate).ToUniversalTime(); OPCPackage pkg = new ZipPackage(); PackagePropertiesPart props = (PackagePropertiesPart)pkg.GetPackageProperties(); // Created Assert.AreEqual("", props.GetCreatedPropertyString()); Assert.IsNull(props.GetCreatedProperty()); props.SetCreatedProperty((String)null); Assert.AreEqual("", props.GetCreatedPropertyString()); Assert.IsNull(props.GetCreatedProperty()); props.SetCreatedProperty(new Nullable<DateTime>()); Assert.AreEqual("", props.GetCreatedPropertyString()); Assert.IsNull(props.GetCreatedProperty()); props.SetCreatedProperty(new Nullable<DateTime>(date)); Assert.AreEqual(strDate, props.GetCreatedPropertyString()); Assert.AreEqual(date, props.GetCreatedProperty()); props.SetCreatedProperty(strDate); Assert.AreEqual(strDate, props.GetCreatedPropertyString()); Assert.AreEqual(date, props.GetCreatedProperty()); // lastPrinted Assert.AreEqual("", props.GetLastPrintedPropertyString()); Assert.IsNull(props.GetLastPrintedProperty()); props.SetLastPrintedProperty((String)null); Assert.AreEqual("", props.GetLastPrintedPropertyString()); Assert.IsNull(props.GetLastPrintedProperty()); props.SetLastPrintedProperty(new Nullable<DateTime>()); Assert.AreEqual("", props.GetLastPrintedPropertyString()); Assert.IsNull(props.GetLastPrintedProperty()); props.SetLastPrintedProperty(new Nullable<DateTime>(date)); Assert.AreEqual(strDate, props.GetLastPrintedPropertyString()); Assert.AreEqual(date, props.GetLastPrintedProperty()); props.SetLastPrintedProperty(strDate); Assert.AreEqual(strDate, props.GetLastPrintedPropertyString()); Assert.AreEqual(date, props.GetLastPrintedProperty()); // modified Assert.IsNull(props.GetModifiedProperty()); props.SetModifiedProperty((String)null); Assert.IsNull(props.GetModifiedProperty()); props.SetModifiedProperty(new Nullable<DateTime>()); Assert.IsNull(props.GetModifiedProperty()); props.SetModifiedProperty(new Nullable<DateTime>(date)); Assert.AreEqual(strDate, props.GetModifiedPropertyString()); Assert.AreEqual(date, props.GetModifiedProperty()); props.SetModifiedProperty(strDate); Assert.AreEqual(strDate, props.GetModifiedPropertyString()); Assert.AreEqual(date, props.GetModifiedProperty()); }
public void TestTextWithDateFormatSecondArg() { // Test with Java style M=Month System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.GetCultureInfo("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.SS.Formula.Functions.Text.Formatter = new NPOI.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()); }
public void TestGetFormattedCellValueHSSFCell() { // Valid date formats -- cell values should be date formatted & not "555.555" IRow row = wb.GetSheetAt(0).GetRow(0); IEnumerator it = row.GetEnumerator(); log("==== VALID DATE FORMATS ===="); while (it.MoveNext()) { ICell cell = (ICell)it.Current; String fmtval = formatter.FormatCellValue(cell); log(fmtval); // should not be equal to "555.555" Assert.IsTrue(DateUtil.IsCellDateFormatted(cell)); Assert.IsTrue(!"555.555".Equals(fmtval)); String fmt = cell.CellStyle.GetDataFormatString(); //assert the correct month form, as in the original Excel format String monthPtrn = fmt.IndexOf("mmmm") != -1 ? "MMMM" : "MMM"; // this line is intended to compute how "July" would look like in the current locale String jul = new SimpleDateFormat(monthPtrn).Format(new DateTime(2010, 7, 15), CultureInfo.CurrentCulture); // special case for MMMMM = 1st letter of month name if (fmt.IndexOf("mmmmm") > -1) { jul = jul.Substring(0, 1); } // check we found july properly Assert.IsTrue(fmtval.IndexOf(jul) > -1, "Format came out incorrect - " + fmt); } row = wb.GetSheetAt(0).GetRow(1); it = row.GetEnumerator(); log("==== VALID TIME FORMATS ===="); while (it.MoveNext()) { ICell cell = (ICell)it.Current; String fmt = cell.CellStyle.GetDataFormatString(); String fmtval = formatter.FormatCellValue(cell); log(fmtval); // should not be equal to "555.47431" Assert.IsTrue(DateUtil.IsCellDateFormatted(cell)); Assert.IsTrue(!"555.47431".Equals(fmtval)); // check we found the time properly Assert.IsTrue(fmtval.IndexOf("11:23") > -1, "Format came out incorrect - " + fmt); } // Test number formats row = wb.GetSheetAt(0).GetRow(1); it = row.GetEnumerator(); log("\n==== VALID NUMBER FORMATS ===="); while (it.MoveNext()) { ICell cell = (ICell)it.Current; log(formatter.FormatCellValue(cell)); // should not be equal to "1234567890.12345" Assert.IsTrue(!"1234567890.12345".Equals(formatter.FormatCellValue(cell))); } // Test bad number formats row = wb.GetSheetAt(0).GetRow(3); it = row.GetEnumerator(); log("\n==== INVALID NUMBER FORMATS ===="); while (it.MoveNext()) { ICell cell = (ICell)it.Current; log(formatter.FormatCellValue(cell)); // should be equal to "1234567890.12345" // in some locales the the decimal delimiter is a comma, not a dot string decimalSeparator = System.Globalization.CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator; Assert.AreEqual("1234567890" + decimalSeparator + "12345", formatter.FormatCellValue(cell)); } // Test Zip+4 format row = wb.GetSheetAt(0).GetRow(4); ICell cell1 = row.GetCell(0); log("\n==== ZIP FORMAT ===="); log(formatter.FormatCellValue(cell1)); Assert.AreEqual("12345-6789", formatter.FormatCellValue(cell1)); // Test phone number format row = wb.GetSheetAt(0).GetRow(5); cell1 = row.GetCell(0); log("\n==== PHONE FORMAT ===="); log(formatter.FormatCellValue(cell1)); Assert.AreEqual("(555) 123-4567", formatter.FormatCellValue(cell1)); // Test SSN format row = wb.GetSheetAt(0).GetRow(6); cell1 = row.GetCell(0); log("\n==== SSN FORMAT ===="); log(formatter.FormatCellValue(cell1)); Assert.AreEqual("444-55-1234", formatter.FormatCellValue(cell1)); // null Test-- null cell should result in empty String Assert.AreEqual(formatter.FormatCellValue(null), ""); // null Test-- null cell should result in empty String Assert.AreEqual(formatter.FormatCellValue(null), ""); }
private void CompareProperties(OPCPackage p) { SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'"); DateTime expectedDate = DateTime.Parse("2007/05/12T08:00:00Z").ToUniversalTime(); // Gets the core properties PackageProperties props = p.GetPackageProperties(); Assert.AreEqual("MyCategory", props.GetCategoryProperty()); Assert.AreEqual("MyContentStatus", props.GetContentStatusProperty() ); Assert.AreEqual("MyContentType", props.GetContentTypeProperty()); Assert.AreEqual(expectedDate, props.GetCreatedProperty()); Assert.AreEqual("MyCreator", props.GetCreatorProperty()); Assert.AreEqual("MyDescription", props.GetDescriptionProperty()); Assert.AreEqual("MyIdentifier", props.GetIdentifierProperty()); Assert.AreEqual("MyKeywords", props.GetKeywordsProperty()); Assert.AreEqual("MyLanguage", props.GetLanguageProperty()); Assert.AreEqual("Julien Chable", props.GetLastModifiedByProperty() ); Assert.AreEqual(expectedDate, props.GetLastPrintedProperty()); Assert.AreEqual(expectedDate, props.GetModifiedProperty()); Assert.AreEqual("2", props.GetRevisionProperty()); Assert.AreEqual("MySubject", props.GetSubjectProperty()); Assert.AreEqual("MyTitle", props.GetTitleProperty()); Assert.AreEqual("2", props.GetVersionProperty()); }
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); }
/// <summary> /// Formats a number or date cell, be that a real number, or the /// answer to a formula /// </summary> /// <param name="cell">The cell.</param> /// <param name="value">The value.</param> /// <returns></returns> private String FormatNumberDateCell(CellValueRecordInterface cell, double value) { // Get the built in format, if there is one int formatIndex = ft.GetFormatIndex(cell); String formatString = ft.GetFormatString(cell); if (formatString == null) { return value.ToString(CultureInfo.InvariantCulture); } else { // Is it a date? if (NPOI.SS.UserModel.DateUtil.IsADateFormat(formatIndex, formatString) && NPOI.SS.UserModel.DateUtil.IsValidExcelDate(value)) { // Java wants M not m for month formatString = formatString.Replace('m', 'M'); // Change \- into -, if it's there formatString = formatString.Replace("\\\\-", "-"); // Format as a date DateTime d = NPOI.SS.UserModel.DateUtil.GetJavaDate(value, false); SimpleDateFormat df = new SimpleDateFormat(formatString); return df.Format(d); } else { if (formatString == "General") { // Some sort of wierd default return value.ToString(CultureInfo.InvariantCulture); } // Format as a number DecimalFormat df = new DecimalFormat(formatString); return df.Format(value); } } }
/** * Convert a Nullable<DateTime> into a String. * * @param d * The Date to convert. * @return The formated date or null. * @see java.util.SimpleDateFormat */ private String GetDateValue(Nullable<DateTime> d) { if (d == null || d.Equals("")) return ""; else { SimpleDateFormat df = new SimpleDateFormat( "yyyy-MM-dd'T'HH:mm:ss'Z'"); return df.Format(d.Value, CultureInfo.CurrentCulture); } }
/** * Convert a string value represented a date into a Nullable<DateTime>. * * @throws InvalidFormatException * Throws if the date format isnot valid. */ private Nullable<DateTime> SetDateValue(String s){ if (s == null || s.Equals("")) return new Nullable<DateTime>(); else { SimpleDateFormat df = new SimpleDateFormat( "yyyy-MM-dd'T'HH:mm:ss'Z'"); DateTime d = (DateTime)df.ParseObject(s, 0); if (d == null) throw new InvalidFormatException("Date not well formated"); return new Nullable<DateTime>(d); } }
/// <summary> /// Returns a string representation of the cell /// </summary> /// <returns>Formula cells return the formula string, rather than the formula result. /// Dates are displayed in dd-MMM-yyyy format /// Errors are displayed as #ERR<errIdx> /// </returns> public override String ToString() { switch (CellType) { case CellType.Blank: return ""; case CellType.Boolean: return BooleanCellValue ? "TRUE" : "FALSE"; case CellType.Error: return ErrorEval.GetText(ErrorCellValue); case CellType.Formula: return CellFormula; case CellType.Numeric: if (DateUtil.IsCellDateFormatted(this)) { FormatBase sdf = new SimpleDateFormat("dd-MMM-yyyy"); return sdf.Format(DateCellValue, CultureInfo.CurrentCulture); } return NumericCellValue + ""; case CellType.String: return RichStringCellValue.ToString(); default: return "Unknown Cell Type: " + CellType; } }
/** * @param dateFormat pass <code>null</code> for default YYYYMMDD * @return <code>null</code> if timeStr is <code>null</code> */ private static Double ConvertDate(String dateStr, SimpleDateFormat dateFormat) { if (dateStr == null) { return Double.NaN; } DateTime dateVal; if (dateFormat == null) { dateVal = HSSFDateUtil.ParseYYYYMMDDDate(dateStr); } else { try { dateVal = DateTime.Parse(dateStr, CultureInfo.CurrentCulture); } catch (FormatException e) { throw new InvalidOperationException("Failed to parse date '" + dateStr + "' using specified format '" + dateFormat + "'", e); } } return HSSFDateUtil.GetExcelDate(dateVal); }
public void ExcelDateBorderCases() { SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd"); Assert.AreEqual(1.0, DateUtil.GetExcelDate(df.Parse("1900-01-01")), 0.00001); Assert.AreEqual(31.0, DateUtil.GetExcelDate(df.Parse("1900-01-31")), 0.00001); Assert.AreEqual(32.0, DateUtil.GetExcelDate(df.Parse("1900-02-01")), 0.00001); Assert.AreEqual(/* BAD_DATE! */ -1.0, DateUtil.GetExcelDate(df.Parse("1899-12-31")), 0.00001); }
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { double s0; String s1; try { s0 = TextFunction.EvaluateDoubleArg(arg0, srcRowIndex, srcColumnIndex); s1 = TextFunction.EvaluateStringArg(arg1, srcRowIndex, srcColumnIndex); } catch (EvaluationException e) { return e.GetErrorEval(); } if (Regex.Match(s1, @"[\d,\#,\.,\$,\,]+").Success) { FormatBase formatter = new DecimalFormat(s1); return new StringEval(formatter.Format(s0)); } else if (s1.IndexOf("/") == s1.LastIndexOf("/") && s1.IndexOf("/") >= 0 && !s1.Contains("-")) { double wholePart = Math.Floor(s0); double decPart = s0 - wholePart; if (wholePart * decPart == 0) { return new StringEval("0"); } String[] parts = s1.Split(' '); String[] fractParts; if (parts.Length == 2) { fractParts = parts[1].Split('/'); } else { fractParts = s1.Split('/'); } if (fractParts.Length == 2) { double minVal = 1.0; double currDenom = Math.Pow(10, fractParts[1].Length) - 1d; double currNeum = 0; for (int i = (int)(Math.Pow(10, fractParts[1].Length) - 1d); i > 0; i--) { for (int i2 = (int)(Math.Pow(10, fractParts[1].Length) - 1d); i2 > 0; i2--) { if (minVal >= Math.Abs((double)i2 / (double)i - decPart)) { currDenom = i; currNeum = i2; minVal = Math.Abs((double)i2 / (double)i - decPart); } } } FormatBase neumFormatter = new DecimalFormat(fractParts[0]); FormatBase denomFormatter = new DecimalFormat(fractParts[1]); if (parts.Length == 2) { FormatBase wholeFormatter = new DecimalFormat(parts[0]); String result = wholeFormatter.Format(wholePart) + " " + neumFormatter.Format(currNeum) + "/" + denomFormatter.Format(currDenom); return new StringEval(result); } else { String result = neumFormatter.Format(currNeum + (currDenom * wholePart)) + "/" + denomFormatter.Format(currDenom); return new StringEval(result); } } else { return ErrorEval.VALUE_INVALID; } } else { try { FormatBase dateFormatter = new SimpleDateFormat(s1); DateTime dt = new DateTime(1899, 11, 30, 0, 0, 0); dt.AddDays((int)Math.Floor(s0)); double dayFraction = s0 - Math.Floor(s0); dt.AddMilliseconds((int)Math.Round(dayFraction * 24 * 60 * 60 * 1000)); return new StringEval(dateFormatter.Format(dt)); } catch (Exception) { return ErrorEval.VALUE_INVALID; } } }
public void TestTextWithDateFormatSecondArg() { System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.GetCultureInfo("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()); // 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)); 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()); }
private ValueEval TryParseDateTime(double s0, string s1) { try { FormatBase dateFormatter = new SimpleDateFormat(s1); //first month of java Gregorian Calendar month field is 0 DateTime dt = new DateTime(1899, 12, 30, 0, 0, 0); dt = dt.AddDays((int)Math.Floor(s0)); double dayFraction = s0 - Math.Floor(s0); dt = dt.AddMilliseconds((int)Math.Round(dayFraction * 24 * 60 * 60 * 1000)); return new StringEval(dateFormatter.Format(dt)); } catch (Exception) { return ErrorEval.VALUE_INVALID; } }
/** * Returns a string representation of the cell * <p> * Formula cells return the formula string, rather than the formula result. * Dates are displayed in dd-MMM-yyyy format * Errors are displayed as #ERR<errIdx> * </p> */ public override String ToString() { switch (CellType) { case CellType.BLANK: return ""; case CellType.BOOLEAN: return BooleanCellValue ? "TRUE" : "FALSE"; case CellType.ERROR: return ErrorEval.GetText(ErrorCellValue); case CellType.FORMULA: return CellFormula; case CellType.NUMERIC: if (DateUtil.IsCellDateFormatted(this)) { FormatBase sdf = new SimpleDateFormat("dd-MMM-yyyy"); return sdf.Format(DateCellValue); } return NumericCellValue + ""; case CellType.STRING: return RichStringCellValue.ToString(); default: return "Unknown Cell Type: " + CellType; } }