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());
        }
Beispiel #3
0
        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);
		}
	}
Beispiel #10
0
 /// <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&lt;errIdx&gt;
 /// </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;
     }
 }
Beispiel #11
0
 /**
  * @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);
 }
Beispiel #12
0
        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);
        }
Beispiel #13
0
        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;
                }
            }
        }
Beispiel #14
0
        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());
        }
Beispiel #15
0
 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;
     }
 }
Beispiel #16
0
 /**
  * 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&lt;errIdx&gt;
  * </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;
     }
 }