Generating a template document from user data. Programmatic generation of form documents in Word

In the previous articles in the "Automating Document Filling" series, I talked about how to create an application's user interface, organize input validation, and get a number in words without using VBA code. In this final article, we will talk about magic - transferring all the necessary values ​​​​from an Excel workbook to a Word document. Let me show you the end result:

Description of the mechanism

To begin with, I will describe in general terms exactly how data will be transferred to a Word document. First of all, we need a Word document template containing all the markup, tables, and that part of the text that will remain unchanged. In this template, you need to define the places where the values ​​​​from the Excel workbook will be substituted - the most convenient way to do this is with the help of bookmarks. After that, you need to arrange the Excel data in such a way as to match the Word template, and last but not least, write the transfer procedure itself in VBA.

So, first things first.

Create a Word Document Template

Everything is extremely simple here - we create a regular document, type and format the text, in general, we achieve in order to get the necessary form. In those places where it will be necessary to substitute values ​​from Excel, you need to create bookmarks. This is done in the following way:

Thus, you will need to create all bookmarks, that is, mark all the places where data from Excel will be inserted. The resulting file must be saved as a "MS Word Template" using the "File" -> "Save As..." menu item.

Excel data preparation

For convenience, I decided to put all the data that needs to be transferred to the Word document on a separate worksheet called Bookmarks - bookmarks. This sheet has two columns: the first contains the names of the bookmarks (exactly as they are named in the Word document), and the second contains the corresponding values ​​to be transferred.

Some of these values ​​are obtained directly from the data entry sheet, and some are from auxiliary tables located on the Support sheet. In this article, I will not analyze the formulas that calculate the desired values, if something is not clear, ask questions in the comments.

At this stage, it is important to correctly specify all the names of the bookmarks - the correctness of the data transfer depends on this.

Transfer procedure

But this is the most interesting. There are two options for executing the data migration code:

• The code is executed in an Excel workbook, the data is passed to Word one value at a time and is immediately placed in the document.
• The code is executed in a separate Word document, all data is transferred from Excel in one batch.

From the point of view of execution speed, especially with a large number of bookmarks, the second option looks much more attractive, but requires more complex actions. That's the one I used.

Here is what needs to be done:

• Create a macro-enabled Word document template. This template will contain the executable VBA code.
  
• In the created template, you need to place a program written in VBA. To do this, when editing a template, press the Alt + F11 key combination and enter the program code in the Visual Basic editor window that opens.
• In an Excel workbook, write code that calls the fill procedure from the newly created Word template.

I will not provide the text of the procedure in the article - it can be easily viewed in the FillDocument.dotm file located in the Template folder in the example archive.

How can you use all this to solve your particular problem?

I understand that in words it all looks very simple, but what happens in practice? I suggest you just use the ready-made option. Download the archive with the example, in an Excel workbook, press the key combination Alt + F11 to open the Visual Basic editor and read all my comments on the program. In order to change the program to suit your needs, you only need to change the value of a few constants, they are placed at the very beginning of the program. You can freely copy the entire text of the program into your project.

Archive structure

The archive attached to this article contains several files.

The main file is an Excel workbook called "Generate Confirmations". There are 4 worksheets in this workbook, of which only two are displayed: "Input" - a data entry sheet and "Database" - an archive of all entered documents.

The Templates folder contains Word document templates. One of them is a template containing a bookmark filling program, and the second is a form to fill out. You can use the template with the program without changes, but the form to fill out, of course, will have to be altered in accordance with your needs.

How to rework the example "for yourself"?

1. Prepare a Word document template to be completed. Create all the necessary bookmarks in it and save it as a "MS Word template".
2. Copy the FillDocument.dotm file from the archive attached to this article to the folder with the prepared template. This file is responsible for filling in the template bookmarks, and nothing needs to be changed in it.
3. Prepare an Excel workbook for data entry. It's up to you to decide whether it will have any "advanced" user interface and perform various tricky calculations. The main thing is that it contains a worksheet with a table of correspondence between the name of the bookmark in the Word template and the value to be substituted.
4. Insert the VBA program code from the sample file into the prepared workbook. Replace all constants according to your project.
5. Test for correct operation.
6. Actively enjoy!

We continue the theme of working with forms in Word that we started earlier. In previous articles, we only looked at forms from a “power user” perspective, i.e. we created documents that are convenient for manual filling. Today, I want to propose expanding this task and trying to use the Content controls mechanism to generate documents.

Before we get down to our immediate task, I want to say a few words about how data for content controls is stored in Word documents (I will deliberately omit how they are tied to the content of the document for now, but I hope to return to this sometime in the next articles).

The legitimate question is what is itemProps1.xml and similar components? These components store descriptions of data sources. Most likely, as conceived by the developers, in addition to the xml files embedded in the document, it was supposed to use others, but so far only this method has been implemented.

What is useful to us itemPropsX.xml? The fact that they list xml schemas (their targetNamespace) that are used in the parent itemX.xml. This means that if we have included more than one custom xml in the document, then in order to find the right one, we need to run through itemPropsX.xml components and find the desired circuit, and therefore the desired itemX.xml.

Now one more moment. We will not manually analyze the relationships between components and look for the right ones using only the basic Packaging API! Instead, we will use the Open XML SDK (its builds are available via NuGet). Of course, we didn’t say a word about this API earlier, but for our task it requires a minimum and all the code will be quite transparent.

Well, the main introduction is done, you can proceed to the example.

According to the established tradition, we will take the same “Meeting Report” that we drew in the article. Let me remind you that this is how the document template looked like:

And so, the XML to which the document fields were bound

< meetingNotes xmlns ="urn:MeetingNotes" subject ="" date ="" secretary ="" > < participants > < participant name ="" /> < decisions > < decision problem ="" solution ="" responsible ="" controlDate ="" />

Step 1. Create a data model

Actually, our task is not just to generate a document, but to create (at least in a draft version) a convenient tool for use by both the developer and the user.

Therefore, we will declare the model as a structure of C# classes:

Public class MeetingNotes ( public MeetingNotes() ( Participants = new List (); Decisions = new List (); ) public string Subject ( get; set; ) public DateTime Date ( get; set; ) public string Secretary ( get; set; ) public List Participants ( get; set; ) public List Decisions ( get; set; ) ) public class Decision ( public string Problem ( get; set; ) public string Solution ( get; set; ) public string Responsible ( get; set; ) public DateTime ControlDate ( get; set; ) ) public class Participant ( public string Name ( get; set; ) )

By and large, nothing special, except that attributes have been added to control XML serialization (because the names in the model and the required XML are slightly different).

Step 2: Serialize the above model to XML

The task is, in principle, trivial. What is called “take our favorite XmlSerializer and go”, if not for one but…

Unfortunately, in the current version of Office, apparently, there is a bug, which is as follows: if in custom xml front declaring the main namespace (the one from which Word should take elements for display), declare some more, then the repeated Content controls start to display incorrectly (only as many elements are shown as there were in the template itself - i.e. repeating section does not work ).

Those. Here is the xml that works:

< test xmlns ="urn:Test" attr1 ="1" attr2 ="2" > < repeatedTag attr ="1" /> < repeatedTag attr ="2" /> < repeatedTag attr ="3" />

and this one too:

< test xmlns ="urn:Test" attr1 ="1" attr2 ="2" xmlns:t ="urn:TTT" > < repeatedTag attr ="1" /> < repeatedTag attr ="2" /> < repeatedTag attr ="3" />

and this one is gone:

< test xmlns:t ="urn:TTT" xmlns ="urn:Test" attr1 ="1" attr2 ="2" > < repeatedTag attr ="1" /> < repeatedTag attr ="2" /> < repeatedTag attr ="3" />

I tried to submit a bug to Microsoft support on Connect , but for some reason I was denied access to submit bugs via Office. A discussion on the MSDN forum did not help either.

In general, the necessary detour maneuver. If we formed XML by hand, there would be no problems - we would do everything ourselves. However, in this case, I really want to use the standard XmlSerializer, which by default adds several of its namespaces to the output XML, even if these namespaces are not used.

We will do a complete suppression of the output of our own namespaces in the XmlSerializer. True, this approach will work only if he really doesn’t need them (otherwise they will still be added and just BEFORE ours).

Actually, the whole code (provided that the variable meetingNotes contains a previously populated object of type MeetingNotes):

var serializer = new XmlSerializer(typeof (MeetingNotes));
var serializedDataStream = new MemoryStream();

var namespaces = new XmlSerializerNamespaces();
namespaces.Add(“” , “” );

serializer.Serialize(serializedDataStream, meetingNotes, namespaces);
serializedDataStream.Seek(0, SeekOrigin.Begin);

Step 3. We enter the resulting XML into a Word document.

Here we do the following:

• copy the template and open the copy
• find the desired custom xml in it (search by namespace “urn:MeetingNotes”)
• we replace the contents of the component with our XML

File.Copy(templateName, resultDocumentName, true ); using (var document = WordprocessingDocument.Open(resultDocumentName, true )) ( var xmlpart = document.MainDocumentPart.CustomXmlParts .Single(xmlPart => xmlPart.CustomXmlPropertiesPart.DataStoreItem.SchemaReferences.OfType () .Any(sr => sr.Uri.Value == "(!LANG:urn:MeetingNotes"!}

We all deal with texts, one way or another. Sometimes there is a need to generate a large or not very large amount of text for some task, for example, to play around with formatting, but there is no text at hand, it’s too lazy to write it yourself. What to do? The answer is simple: use the built-in random text generator in Word!

In the Microsoft Word editor, you can generate text quickly and easily using special commands. I will use Word 2007 as a guinea pig. These commands should work in all versions of Word. I'll tell you about three methods for generating text.

Method 1: Using rand()

The rand() function inserts a localized text sample, 3 paragraphs of 3 sentences. Open your Word, put your cursor where the text will appear soon and enter the command:

and press Enter. The rand function itself will disappear and 3 paragraphs of text will appear instead:

But that's not all. If you need a lot of text, then you can use the rand function with additional arguments, like this:

=rand(x,y)

where " x' means the number of paragraphs, and ' y» is the number of sentences in each paragraph. For instance, =rand(20.5) will insert 20 paragraphs with five phrases each. A =rand(7) will insert 7 paragraphs of 3 sentences each.

Method 2: Using lorem()

To insert the good old Lorem Ipsum as a sample - use the lorem () function. Type the following command and press Enter:

And we get this all-Latin text

The lorem() function also readily accepts additional arguments, like rand(), in the form of a number of paragraphs and sentences. With no arguments, the function inserts by default 3 paragraphs with 3 sentences each.

Method 3. The rand.old() function

The usage is similar to the previous commands:

=rand.old()

and press Enter.

The rand.old() function is left for compatibility with the old office, up to and including 2003. The method of application is the same as the previous two, only the text will consist of the same phrases "Eat more of these soft French rolls and drink tea." This phrase is known to everyone who sometimes had to work with fonts.

You can pass arguments as in the first two methods.

That's all, the texts have been generated, now you can eat more of these soft French rolls and drink tea :)

Were you able to insert text using the above functions?