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;
}
}
