Formating number and date


#1

Hi

I an using aspose.Word version 1.5

This Is My word template

TEST
?TableStart:Participant?
?StartDate?
?TableEnd:Participant?

And the code

{MERGEFIELD TableStart:Participant * MERGEFORMAT}
{ MERGEFIELD StartDate @ “d MMMM yyyy” *
MERGEFORMAT }
{MERGEFIELD TableEnd:Participant * MERGEFORMAT}

My output is

Evaluation Only. Created with Aspose.Word. Copyright 2003-2004 Aspose Pty Ltd.
TEST

05-10-2003 00:00:00

08-02-2004 00:00:00

05-10-2003 00:00:00

08-02-2004 00:00:00

I don’t want the time only the date.

Please Help my. Du you not support formating ???
Thomas


#2

Hi Thomas,

This is a good point. However in Word XP I do not see @ as an available option to specify format for the MERGEFIELD field, it seems to be only available for the DATE field. That’s why we have not implemented support for it. What MS Word version do you use and how did you specify the date format for the field, by manually entering @ …?

There are several possible solutions without using @ format:

1. Use MergeField event to provide a handler and format the date inside the event handler.

2. Add one extra calculated column to your table and format the date into a string in the way you want it before mail merge.

3. Add extra column to your query that produces the data table and format date in the way you need in the query.


#3

Hi

I don′t understand.
1 Use MergeField event to provide a handler and format the date inside the event handler.

Where can I find some documentation or a guide.

Please look here

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnovba00/html/MailMergePartI.asp

Here Microsoft write that I can format Strings

{ MERGEFIELD SaleDate}
3/1/1999 0:00:00
{ MERGEFIELD SaleDate @ “dd-MMM-yyyy” }
03-Jan-1999

{ MERGEFIELD SalesAmt}
1245.9
{ MERGEFIELD SalesAmt # “#,##0.00” }
1,245.90

I have installed SoftArtisans WordWriter
In the documentation they have a great guide to how to format date and strings.

http://support.softartisans.com/WordWriterV1/doc/features/format.asp

And it works.

I will like to use your product instead of SoftArtisans, but if I can’t format my date and strings I can′t use your product.

I use Microsoft Office 2003 Pro

Best Regards
Thomas


#4

Hi,

This is an example how to format field values in the MergeField event handler. It is done both for a currency and datetime fields.

///


/// A customer complained that cannot format double field into currency.
///

[Test]
public void TestFormatFieldsFromObjectArray()
{
TestUtil.SetUnlimitedLicense();

Document doc = TestUtil.Open(@“TestFormatFields.doc”);
doc.MailMerge.MergeField += new MergeFieldEventHandler(HandleFormatPrice);
doc.MailMerge.Execute(
new string[] {“Price”, “Date”},
new object[] {(double)123.45, new DateTime(2004, 6, 10, 9, 30, 0)});

doc = TestUtil.SaveOpen(doc, “TestFormatFields Out.doc”);

Assertion.AssertEquals(
“$123.45\r10 June 2004\x000c”,
doc.Model.GetText());
}

private void HandleFormatPrice(object sender, MergeFieldEventArgs e)
{
Assertion.AssertEquals("", e.TableName);
if (e.FieldName == “Price”)
{
e.Text = string.Format("{0:C}", e.FieldValue);
}
else if (e.FieldName == “Date”)
{
DateTime date = (DateTime)e.FieldValue;
e.Text = date.ToString(“d MMMM yyyy”);
}
}

Also, thanks for the idea about @ and # options. We will add support for them in the next versions.


#5

Hi

Now it works

To everybody who has the same problem. I post my example and it can compile.
Just remember to change the Connectionstring to the Database and change the SQL call and path.

When du you expect to release a new version whith @ and # options ???

using System;
using System.IO;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OleDb;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using Aspose.Word;

namespace Team
{
public class Example : System.Web.UI.Page
{
private string curPath;

private void Page_Load(object sender, System.EventArgs e)
{
this.curPath = MapPath(".");

//Create a document requested by the user. The action is specified in the request string.
Document doc;
string param = this.Request.Params[0];
switch(param)
{
// Write this in your browser (Remember to change the path) participant
case “participant”:
doc = CreateParticipant();
break;
// Write this in your browser (Remember to change the path) test
case “test”:
doc = Test();
break;
default:
return;
}

//Stream the document to the client browser.
doc.Save(
string.Format("{0}.doc", param),
SaveFormat.FormatDocument,
SaveType.OpenInBrowser, Response);
Response.End();
}

private Document CreateParticipant()
{
DataSet dataSet = new DataSet();
dataSet.Tables.Add(GetParticipant_all());
// Remember to make the document first
Document doc = OpenDoc(@“Afkrydsningsliste3.doc”);
doc.MailMerge.ExecuteWithRegions(dataSet);

return doc;
}

/**
* Please help me, The code can not compile.
*/
public Document Test()
{
DataSet dataSet = new DataSet();
dataSet.Tables.Add(GetParticipant_all());
// Remember to make the document first
Document doc = OpenDoc(@“Afkrydsningsliste3.doc”);
doc.MailMerge.MergeField += new MergeFieldEventHandler(HandleFormat);
doc.MailMerge.ExecuteWithRegions(dataSet);
return doc;
}

private void HandleFormat(object sender, MergeFieldEventArgs e)
{
if (e.FieldName == “StartDate”)
{
DateTime date = (DateTime)e.FieldValue;
e.Text = date.ToString(“d MMMM yyyy”);
}
}

///


/// MSSQL SERVER Connection
///

private DataTable ExecuteDataTable(string tableName, string commandText)
{
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings[“Connectionstring”]);
SqlCommand myCommand = new SqlCommand(commandText, myConnection);

SqlDataAdapter sd = new SqlDataAdapter(myCommand);
DataTable table = new DataTable(tableName);
sd.Fill(table);
myConnection.Close();

return table;
}

///
/// Just a convenience function to open a document in the predetermined folder.
///

private Document OpenDoc(string fileName)
{
Word app = new Word();
return app.Open(string.Format(@"{0}/…/…/data/report/team/{1}", curPath, fileName));
}

///
/// Returns all participant in my database.
///

///
private DataTable GetParticipant_all()
{
return ExecuteDataTable(“Participant”, “SELECT *, C.Address AS CourseAddress FROM tStmParticipant AS P, tCourse AS C, rCourseParticipant AS CP WHERE CP.ParticipantID = p.ID AND C.ID = CP.CourseID AND P.ActiveStatus = 1”);
}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

///
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///

private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
}
}


#6

Hi Roman,

Just checking to see whether the /@ and /# field formatting commands can be used within a mergefield yet.

Thanks,
Rich.


#7

Hi Rich,

We’ve discovered that Word XP will remove all formatting flags it does not understand if you Edit Field and click OK. This behaviour is probably same for all Word versions to strip unknown field flags.

I still plan to support @ and #, but remember that you might lose them if you attempt to edit the document in a previous Word version.

The flags should be ready by the end of July.


#8

Hi Roman,

Thanks for your feedback, we would like to have @ and # implemented as this will give more functionality to end user.
However we will keep the issues in mind you have highlighted for us.

Will Aspose.Word deal with formatting commands on text such as bold, italic, and underline.

For instance, if I pass through a sentence in a node in the XML data, and I wanted one of the words in the sentence to be bold, can I enter format commands such as HTML “B” “/B” and will Aspose interpret these correctly in the output document.

Thanks,
Rich.


#9

Hi Rich,

We plan to support RTF and HTML documents, but it also seems we will need to support HTML fragments exactly as you describe. Estimated delivery August-September.

In the meantime, if you want to insert formatted text then just use DocumentBuilder.MoveToMergeField to navigate to the field and use Font and ParagraphFormat and DocumentBuilder methods to insert formatted content.


#10

@ and # flags are now supported in Aspose.Word 1.6.5


#11

Hi Roman,

I have been trying to test the formatting commands /@ and /# inside the Mergefields within the template document. I can’t get either currency or date fields formatting.

How did you pass the data from C# through Aspose to the Template document?

I created a XSD for my XML document, defining fieldnames and datatypes. I then read the XSD into my dataset in C# prior to reading the XML document into my dataset.

Once the XSD and the XML doc were both read into the dataset I checked the datatype of each field using ‘GetType’, and all fields had been assigned correctly.

I then attempted to merge the fields to the template using Aspose’s DocumentBuilder, but the write command turned all values to strings on output.

builder.Write(row[fieldName].ToString());

I then attempted to merge the fields to the template using Aspose’s ExecuteWithRegions command but this threw an error stating "Specified cast is not valid"

docOutput.MailMerge.ExecuteWithRegions(dset);

Is it possible for me to get hold of the sample code you used to test this.

Thanks alot,
Rich.


#12

Hi Rich,

Here is my test code:

[Test]
public void TestFormatFieldsWithSwitches()
{
TestUtil.SetUnlimitedLicense();
Document doc = TestUtil.Open(@“TestFormatFieldsWithSwitches.doc”);
doc.MailMerge.Execute(
new string[] {“Price”, “Date”},
new object[] {(double)-1234.56, new DateTime(2004, 6, 10, 9, 30, 0)});

doc = TestUtil.SaveOpen(doc, “TestFormatFieldsWithSwitches Out.doc”);
Assertion.AssertEquals(
“($1,234.56)\r2004, 10 June\x000c”,
doc.Model.GetText());
}


Not sure why you refer to the flags as /@ and /# because they are @ and #.

These are the fields in the test document:

{ MERGEFIELD Price # “$#,##0.00;($#,##0.00);Zero” * MERGEFORMAT }
{ MERGEFIELD Date @ “yyyy, dd MMMM” * MERGEFORMAT }

The formatting only works when you execute mail merge. DocumentBuilder has nothing to do with this and outputs only strings.

You probably need to double check that the values in your data set are indeed converted into appropriate types.

If you use # then the field value must be convertable to double. If you use @, then the field value must be DateTime.

This is actual code from the component that has to deal with formatting:

//Apply any formatting specified in the document.
if (field.NumberFormat != null)
fieldValue = ((double)newFieldValue).ToString(field.NumberFormat);
else if (field.DateFormat != null)
fieldValue = ((DateTime)newFieldValue).ToString(field.DateFormat);
else
fieldValue = newFieldValue.ToString();

If nothing helps send me your XSD and XML.


#13

Hi Roman,

Thanks for the example code, I was able to get the formatting working for both dates and currencies.

Are there any plans to allow DocumentBuilder to pass through values such as dates and currencies to the template without converting them to strings.

It would be a benefit to us where end user managability of the template is concerned.

Thanks,
Rich.


#14

Are you talking about filling a single merge field with a value using DocumentBuilder like:
DocumentBuilder.FillMergeField(string name, object value)?


#15

Roman,

I see that you say the # and @ formatting tags are supported by Aspose.Word and have been since version 1.5 or so… what about the * formatting tag? In Word you supposedly can use the * Upper setting on a mergefield to make it all uppercase. I have a pretty significant need for this flexibilty for a client I am working on and was wondering if it is supported because I cannot get it to work. After the merge, all fields seem to ignore anything with * formatting options set.

Since the other two formatting options are supported, maybe this one would be easy to add if it is not already in there? Any comments, suggestions you have would be appreciated.


#16

Sure, just added this one, will be released in Aspose.Word 1.8.3 later today or tomorrow.


#17

Awesome thanks for the info. I am having problems getting the other formatting items to work by the way. I am trying to format a decimal field using the # switch and some parameters, but I keep getting an exception “Specified Cast is Invalid” while attempting to do the mailmerge. I know for a fact that the value I am trying to format is a decimal (the XML schema has it as xsBig Smileecimal), so I am not sure what I am doing wrong.

Could you possibly send a word document with the proper field codes in it for formatting a decimal as a currency string? I can send you my Word template and XML if you like as well… my email is <a target="_blank" title=“mailtoTongue Tiedpotvader@yahoo.com” href=“mailtoTongue Tiedpotvader@yahoo.com”>spotvader@yahoo.com

Thanks again…

email address should read spotvader@yahoo.com


#18

Here are examples of merge fields with formatting switches:
{ MERGEFIELD Price \# "$#,##0.00;($#,##0.00);Zero" \* MERGEFORMAT }
{ MERGEFIELD Date \@ "yyyy, dd MMMM" \* MERGEFORMAT }

Here is the code that tests these:

[Test]

public void TestFormatFieldsWithSwitches()

{

TestUtil.SetUnlimitedLicense();

Document doc = TestUtil.Open(@"MailMerge\TestFormatFieldsWithSwitches.doc");

doc.MailMerge.Execute(

new string[] {"Price", "Date"},

new object[] {(double)-1234.56, new DateTime(2004, 6, 10, 9, 30, 0)});

doc = TestUtil.SaveOpen(doc, @"MailMerge\TestFormatFieldsWithSwitches Out.doc");

Assert.AreEqual(

"($1,234.56)\r2004, 10 June\x000c",

doc.Model.GetText());

}





#19

But try latest Aspose.Word 1.8.3, I might have it fixed there. There was a cast from object to double, which I replaced with Convert.ToDouble, I think it should help in your case.


#20

Roman,

Thanks for your help. I upgraded to version 1.8.3 this morning and used your sample mergefield codes and everything is working correctly. I did notice one bit of odd behavior on the part of Word though… it appears to strip out all of your mergefield codes the next time you open the template file in Word. Is this a known bug? I’m using version 2003 SP1.

Thanks again,

spotvader