Northwind question

Weuz · December 17, 2004, 3:20am

private Document CreateInvoiceFromDataSet()

{

DataSet dataSet = new DataSet();

//Populate tables and add to the dataset.

dataSet.Tables.Add(GetTestOrder());

//The document should have mail merge regions designated with

//MERGEFIELD TableStart:MyTableName and TableEnd:MyTableName.

Document doc = new Document(Server.MapPath("") + "\\Documents\\Invoice.doc");

doc.MailMerge.ExecuteWithRegions(dataSet);

return doc;

}

Northwind Traders

INVOICE

One Portals Way, Twin Points WA 98156

Phone: 1-206-555-1417 Fax: 1-206-555-5938

"TableStart:Orders"

Ship To:

"ShipName"

"ShipAddress"

"ShipCity" "ShipPostalCode"

"ShipCountry"

Bill To:

"Customers.CompanyName"

"Address"

"City" "PostalCode"

"Country"

Order ID:	Customer ID:	Sales Person:	Order Date:	Required Date:	Shipped Date:	Shipped By:
"OrderID"	"CustomerID"	"SalesPerson"	"OrderDate"	"RequiredDate"	"ShippedDate"	"Shippers.CompanyName"

"TableEnd:Orders"

Hi I've downloaded your code and I'm testing it.
At the begining, it turns very well.
now I've modified just CreateInvoiceFromDataSet() and respectively the precreated document invoice.doc.

At the execution, the document is showing without filling the merge region (the pre-created document and the result document are identic )
Why ?

My another question is :
In GetTestOrders function you write :

"SELECT * FROM AsposeWordOrders WHERE OrderId = {0}", TestOrderId));

where comes TestOrderId, I've seen it nowhere else in the code.
Why the condition is OrderId = {0} ?

romank · December 17, 2004, 1:00pm

If you want the merge region to be filled, you need to make sure the name of the table specified in TableStart and TableEnd fields should be the same as the name of the DataTable.

Also, to modify the name of the mergefield in MS Word, you need to right click on the field and select Edit Field and modify the name of the field there. Modifying field name right in text in MS Word does not work as you modified only field result, not the field code. You can see that if you select all text and press F9 to update the fields.

{0} gets filled with TestOrderId when the string and the test order id a passed to the Format function. See string.Format and format specifiers in MSDN.

Weuz · December 20, 2004, 1:18am

Thank you,
I said that If I delete some merged fields in the document and I execute the document is not filled in. Why.
Note that I’ve not modifying nothing.
For Example in CustomerLabels.doc If I erase all others merged fields except :

“CompanyName”

“Address”

“City”“PostalCode”

"Country"

Which are then present only once in the doc.

Then I’ve not modiying neither code nor database.

romank · December 20, 2004, 1:34am

You need to pay attention to merge regions. Do you want it to do with merge regions or not? To start simple, you probably want to do a simple mail merge without regions.

In this case, you should also delete TableStart:Orders and TableEnd:Orders fields that designate the start and end of the merge regions. You also need to change the code to call MailMerge.Execute, not MailMerge.ExecuteWithRegions.

On the other hand, if you want to use mail merge regions, you should not delete TableStart: and TableEnd: fields and keep calling MailMerge.ExecuteWithRegions.

Weuz · December 20, 2004, 2:14am

The result is identic by using mail merge without regions or mail merge with regions.
If I delete one field in the document, this does not work.

I’m going to create my own document to see what’s not going on.
regards

Weuz · December 21, 2004, 7:01am

Thank I’m testing this code in tbale Products:

private Document CreateTest()

{

//Open the template document

/

Document doc = OpenDoc(“CustomerLabels.doc”);

/

Document doc = new Document(Server.MapPath("") + “\Documents\TestDoc.doc”);

//I use “try” instead of “using” because my converter to VB code does not support “using”.

OleDbConnection conn = null;

OleDbDataReader dataReader = null;

try

{

//Open database connection.

conn = new OleDbConnection(GetConnString());

conn.Open();

OleDbCommand cmd = new OleDbCommand(

“SELECT * FROM Products”, conn);

//Open the data reader.

dataReader = cmd.ExecuteReader();

//Perform the mail merge

doc.MailMerge.Execute(dataReader);

}

finally

{

if (dataReader != null)

dataReader.Close();

if (conn != null)

conn.Close();

}

//This is useful if you want to delete any MERGEFIELD records that might

//be left at the end of the table.

doc.MailMerge.DeleteFields();

return doc;

}

In My Document TestDoc.doc I’ve defined the merge field like this:

{CHAMPFUSION TableStart: Products *FUSIONFORMAT}
{CHAMPFUSION ProductID * FUSIONFORMAT }

{CHAMPFUSION ProductName * FUSIONFORMAT }
{CHAMPFUSION TableEnd:Products * FUSIONFORMAT }

I’ve showed you the code fields.

At execution, my document don’t show nothing.

Why !!!

My merge fields aren’t good or not?

I’m using a french Version of MsWord.

romank · December 21, 2004, 4:39pm

Hi,

I can now see what the problem is. The problem is really related to French version of MS Word. You field names are CHAMPFUSION, but Aspose.Word expect MERGEFIELD.

Please email me your document to word@aspose.com, we will have this fixed asap. Sorry for the troubles and thank you for your patience.

I will ask our sales team to provide you a good discount if you decide to go with the purchase later.

Weuz · December 22, 2004, 1:08am

Ok, I have sent you my document by Gora.Leye@sigmakalon.com.
We’ll by this product if it can do reporting from database (Related to your demos I think this is possible ).

Weuz · December 22, 2004, 1:16am

By Waiting for you ,
Can I test it if I download and install english version word of Word Viewer.

romank · December 22, 2004, 1:29am

If you have english version of MS Word, it will work. Word Viewer will not help in your case. Don't worry, I should have a fix for you tomorrow.

Weuz · December 28, 2004, 1:58am

Hi, I’m regarding your employees report but I cannot insert a new employer with his photo.
If I copy and past a photo or insert a file image at form employees. At execution I 've error :

Specified cast is not valid.

In your table structure what is the diference between fields PhotoBLOB and Photo. ? I think you are using PhotoBLOB to mail merge .But if I insert a image it is inserted in the field photo and nothing in PhotoBLOB.
This interest me because I would like to store images in my database table and build a word document with photos at each line. Like you’ve done in your employees report.
Regards,

romank · December 28, 2004, 1:32pm

Originally, there was just the Employees.Photo field in Northwind.mdb. This field contains a picture as an OLE object.

Aspose.Word cannot load an OLE object as such. Aspose.Word can only load an image (as if it was an image file) in any of the image formats supported by .NET such as JPG, GIF, PNG etc.

So to get the demo working I added Employees.PhotoBLOB field and loaded the photos into this field in their normal image (not OLE object) format. I don’t exactly remember how I converted OLE objects into image files, probably I copied the pictures as files and then used .NET Image class to open the file and save into the blob fields.

So, if you insert another record into Employees and insert a photo, it will only be inserted into the original Employees.Photo field in OLE object format and the new Employees.PhotoBLOB will be empty. Aspose.Word tries to load the image from the empty field and that causes the exception you see.

As you can see, Aspose.Word can load images from a database, the problem is only that the images must be stored in the database as images, not as OLE objects.

Right now I’m not sure what is the best way to convert an OLE object stored in MS Access into an image file. Let me know if you really need to do this, I might come up with some idea.

Weuz · December 29, 2004, 1:11am

Thank you,
What I want is to insert records with image (photo) field in my table and then be able to build a document (with aspose ) showing this records.
Any help will be appreciated.

Weuz · December 31, 2004, 6:24am

Hi,
Now I’m doing like this :
Considering this table structure RMO(NumberId:string,Name:string,date Big Smile ate/Time)
In a Gif repert I stock photos saved as NumberId.
For example:
in my table database I’ve

NumberId	Name	Date	adresse
12A28	SKINFLEX 42-1 dilué à 15% d’eau 250 g/m2	10/08/2004	10 rue Henri sainte claire de ville 92656 Paris

And in my Gif repert I stock images as 12A28.gif …

Is it possible for aspose.word to report tables in word document by replacing text (example 12A28 ) by his corresponding image ?

this is possible in C#,ASP.net.

If yes how can we do this ?

romank · December 31, 2004, 8:09pm

It is not directly possible to find and replace a piece of text in the document with an image, but I’ll try to explain what’s possible, maybe we can find a solution that will work for you.

There are several ways to insert an image into the document:

1. Use mail merge and Image:FieldName merge field. In this case, the field in the data source must contain the image bytes or image file name. Aspose.Word will load the image automatically and insert into the document during mail merge.

2. Use DocumentBuilder.MoveToBookmark, MoveToMergeField, MoveToCell or other MoveToXXX methods to move the cursor to a specific position in the document and then use DocumentBuilder.InsertImage to insert an image there.

So technically speaking, in your case it is possible to replace text 12A28 with empty string “” using Range.Replace and then use DocumentBuilder.MoveToCell and InsertImage to insert the image there, but I don’t think this is what you need.

I think the best for your is to make sure the images are stored in proper format (not as OLE objects) in the database field and use mail merge to insert the images.
By the way, I’m still looking for a solution to convert from OLE objects into images for you.

Weuz · January 3, 2005, 12:45am

Thank you,
I think that your first solution will resolve my problem by replacing 12A281 by 12A28.gif. Then the field in the data source will contain the file name.
But how can we use mail merge and Image:FieldName merge field.

Weuz · January 3, 2005, 5:44am

Thank you, I think the first suggest will resolve my problem by using mail merge and Image:FieldName merge field.
If the field in the data source contain the image file name, how can we use mail merge and image ?