We continue the topic of working with forms in Word that we started earlier. In previous articles, we looked at forms only from the point of view of an “advanced user”, i.e. We created documents that were easy to fill out manually. 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 contents of the document for now, but I hope to return to this sometime in the next articles).
A logical question - what is it? itemProps1.xml and similar components? These components store descriptions of data sources. Most likely, as planned by the developers, in addition to the xml files built into the document, others were supposed to be used, but so far only this method has been implemented.
Why are they useful to us? itemPropsX.xml? The fact that they list xml schemas (their targetNamespace), which are used in the parent itemX.xml. This means that if we have included more than one custom xml into the document, then to find the one we need, we need to go through itemPropsX.xml components and find the required diagram, and therefore necessary itemX.xml.
Now one more thing. We will not manually analyze the connections between components and search for the ones we need, using only the basic Packaging API! Instead, we'll use the Open XML SDK (its builds are available via NuGet). Of course, we haven’t said a word about this API before, but for our task the minimum is required from it and all the code will be quite transparent.
Well, the basic introduction is done, we can start with the example.
According to established tradition, we will take the same “Meeting Report” that we drew in the article. Let me remind you that this is what the document template looked like:
And this is 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 in the form of a C# class structure:
Public class MeetingNotes ( public MeetingNotes() ( Participants = new List
By and large, nothing special, except that attributes have been added to control XML serialization (since 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 thing But…
Unfortunately, in current version Office, apparently, has a bug, which is as follows: if in custom xml before by declaring the main namespace (the one from which Word should take elements to display), declare some other one, then repeating Content controls begin to be displayed incorrectly (only as many elements are shown as were in the template itself - i.e. the repeating section does not work ).
Those. This 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" />
but this one is no longer there:
< 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 have no access to submit Office bugs. And the discussion on the MSDN forum didn't help either.
In general, a necessary workaround. If we had generated the XML by hand, there would have been no problems - we would have done 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 completely suppress the output of our own namespaces in the XmlSerializer. True, this approach will only work if it really doesn’t need them (otherwise they will still be added and just BEFORE ours).
Actually, the entire 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. Enter the resulting XML into a Word document.
Here we proceed as follows:
- copy the template and open the copy
- find the required custom xml in it (search by namespace “urn:MeetingNotes”)
- 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
We all deal with texts in 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 with formatting, but there is no text at hand, you are too lazy to write it yourself. What to do? The answer is simple: use the random text generator built into Word!
In the editor Microsoft Word you can generate text quickly and easily using special teams. 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 of text generation.
Method 1: Using rand()
The rand() function inserts a localized sample text, 3 paragraphs by 3 sentences. Open your Word, place your cursor where a bunch of text is about to appear and enter the command:
and press Enter. The rand function itself will disappear and 3 paragraphs of text will appear in its place:
But that's not all. If you need a lot of text, you can use the rand function with additional arguments, like this:
=rand(x,y)
Where " x" means the number of paragraphs, and " y"—the number of sentences in each paragraph. For example, =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 good old Lorem Ipsum as a sample, use the lorem() function. Type the following command and press Enter:
And we get this pan-Latin text
The lorem() function also readily accepts additional arguments, like rand(), in the form of the number of paragraphs and sentences. Without arguments, the function inserts by default 3 paragraphs with three sentences each.
Method 3. rand.old() function
Usage is similar to the previous commands:
=rand.old()
and press Enter.
The rand.old() function is left for compatibility with old office, up to 2003 inclusive. The method of application is the same as the previous two, only the text will consist of the same phrases “Eat some more of these soft French rolls and drink some tea.” Anyone who has occasionally worked with fonts knows this phrase.
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 paste text using the above functions?
A, last names in column B and professions in the column C.
2. Create a word document (.doc or .docx)
(A), (B) And (C).
(A), (B) And (C) (A)- name, (B)- last name, (C)- profession.
Settings programs.
3. Select paths for files and folders
Select
4. Specify the sheets and rows of the required data
Excel file data sheets
Excel file data rows Excel file data sheets
1 .
If you want all sheets and/or rows of your excel file with data to participate in the formation of the document, click on the corresponding button with the inscription on the right Numbers(its inscription will change to All).
5. Set a template for naming new word files
Set the naming pattern for new word files:
New word files names template- this is a template for the names of new documents (word files) generated by the program. Here the name pattern contains the column names of the excel file, surrounded by curly braces: (A) And (B). When creating a new document, the program will replace everything (A) And (B) the corresponding cell values from the excel file - this will be the name of the new document (word file).
You can set your framing characters on the tab Settings programs.
6. Click "Generate"
Click the button Generate and progress will appear on the screen. Exactly as many documents (word files) will be created as the number of lines in the excel file involved in the formation.
7. Everything
All documents (word files) have been created and are located in the folder specified in Folder to save the new word files. All:)
Exwog- report generator from Excel to Word using a template
Free Word file generator using a template (Word file) based on Excel file data
Works on Mac OS, Windows and Linux
Allows you to set the names of new generated word files
Allows you to specify sheets and rows of the required data
Allows you to specify surrounding characters for Excel column names
Easy to use
Store your data in Excel format (.xls and .xlsx) and generate Word files (.doc and .docx) in a few clicks :)
How does this work?
Take a look at your excel file
In this example, the excel file contains information about clients. Each line corresponds to a specific client. Names are arranged in a column A, last names in column B and professions in the column C.
Click to view
Create a word document (.doc or .docx)
Click to view
Create a “template” (word file) for generating new documents (word files). Here the "template" text contains the names of the excel file columns, surrounded by curly braces: (A), (B) And (C).
The program will generate new documents according to the "template" replacing all (A), (B) And (C) corresponding cell values from the excel file: (A)- name, (B)- last name, (C)- profession.
You can also set your framing characters on the tab Settings programs.
Select paths for files and folders
Select paths for files and folders (buttons labeled Select). In the program you specify the following paths:
Excel file with data (*.xls, *.xlsx)- this is the path to your Excel file with data (customer information);
Word template file (*.doc, *.docx)- this is the path to your “template” (the word file created in the previous step);
Folder to save the new word files- this is the path to the folder in which the program will save new generated documents.
Click to view
Specify the sheets and rows of the required data
Click to view
Specify the numbers of sheets and rows of your excel file with data (customer information) for which you want to generate documents:
Excel file data sheets- numbers of sheets of your excel file that will participate in the formation of new documents;
Excel file data rows- line numbers of sheets (sheets specified in Excel file data sheets) of your excel file which will participate in the generation of new documents. Based on the data of each specified line, a separate document (word file) will be created.
The numbering of sheets and lines in the program begins with 1 .
Once, after summing up the results of the Olympiad, when the participants were required to send letters with the results of the passed (or failed) subjects, I noticed that a girl was sitting and using her hands to enter the exam results, the student’s name and other information into the letter template. In front of her lay several printed Excel sheets with names and ratings. Copy-Paste, Ctrl+C - Ctrl+V, translation of the full name from the nominative to the genitive case, and so the work has already been done with half of the first of three sheets of data. How long do you think she could have sat there copying data and then checking the result? I think it would take quite a long time, and there would be quite a few mistakes. But then she still had to sign postal envelopes... I felt sorry for her time and in a few minutes I showed how this process could be automated using simple means. After a little excursion, her work was completed in 20 minutes.
In this lesson I will show you how you can quickly create documents (letters, invitations) spending a minimum of time on it. IN different versions This described process is called differently in different languages. So in Russian it is “Merge”, and in English it is “Merge”.
I hope that the chain letters are from pension fund, tax notices are created in the same way :)
Tools
To create documents we need OpenOffice Writer. In it we will create a letter template. You will also need OpenOffice Calc. In it we will create a database with the names and addresses of those people whom we, for example, want to invite to a party. MS Word and Excel can easily be used instead of programs from the OpenOffice package. The database can easily be stored in MS Access. And so, let's get started.
Creating a Database
A database is nothing more than a table in MS Excel or OpenOffice Calc, where the data is arranged in columns and the first line is used to name the columns. Let’s create, for example, columns “full name”, “city”, “index”, “address”. Enter the data and save the file to disk.
Other sources can also be used as databases, for example address book Outlook, or MS Access database.
Creating a letter template
Creating a letter template is a little more complicated. Since data from the table (our database) will be inserted into the template as it is, the letter must be written accordingly. If your full name is in the nominative case in your database, then you are unlikely to be able to use this field in the address “Dear,<ФИО>! and for the envelope in the line “To:<ФИО>" In the latter case, the full name will look somewhat clumsy.
When composing a letter template, I recommend starting by composing a letter to someone to a real person. You can do it yourself. Then highlight in it the data that you will take from the database and replace it with the appropriate fields. Before replacing selected text in a field, I recommend paying attention to what letter the field in the database should begin with (lowercase or uppercase). Of course, you can create a template right away with the fields, but then you may not notice some mistakes, such as inconsistencies in cases.
Already in the given template it is clear that we will need a full name in the genitive case and a field containing only the first and middle names. With the first field, we can replace “To” with “Recipient” and then the full name in the nominative case will suit us perfectly. With the second field, everything is somewhat more complicated and we will have to create another column in the database and fill it with the corresponding data. In one of the next lessons I will tell you how to do this automatically, but for now we will assume that we already have such a field.
To insert a field and bind the template to the database, you must perform the following sequence of actions. In OpenOffice Writer, select a database
and click “Define”.
Then insert a field from the connected database into the desired location. To do this, press Ctrl+F2, or in the “Insert” menu select “Fields” and “Other”. In the window that opens, select the “Databases” tab, select “Mail Merge fields” in the field type, select the appropriate field from the database and click “Insert”. The actual database can also be selected in this window.
After one field is inserted, you can, without closing the current window, select the text to replace it with the next field, select the field and click “Insert” again. And so on for each of the fields.
For MS Word the situation is somewhat similar and, perhaps, more convenient. After creating the table in MS Excel, launch MS Word and go to the “Distributions” tab. On this tab you can see three necessary steps: “Start a merge”, “Draft a document and insert fields” and “Complete”. There is also an intermediate step “View result”, but it is optional.
And so the work begins with selecting a document. This could be letters, stickers, or a regular Word document. You can immediately launch the Merge Wizard, which will guide you through all stages of the process. The next step is to select the recipient, that is, the database. Here you can select a ready-made database (for example, an MS Excel table created at the previous stage), or create new list. After the list is selected, the buttons “Insert merge field”, “Change list of recipients”, etc. become active. Through the “Change list of recipients” dialog, which opens with the corresponding button, you can select from the entire list only the records necessary for merging.
 |
 |
When inserting fields, I find it convenient to highlight all fields in gray by clicking on the “Highlight Merge Fields” button. Otherwise, if there are many such fields, they can be lost in the text.
And so, the template is ready.
Generating letters
The last stage is generating letters. In OpenOffice, to do this, in the “Tools” menu, select “Mail Merge Wizard...” and go through all the proposed steps for merging our template with the database.
 |
 |
In the dialog box we see that out of the 8 points proposed, we do not have to carry out some points. So we have already created the document and we do not need to edit it, we have also already inserted the address block. But let's take things in order.
First, select that we are creating letters based on the current document and click “Next” at the bottom of the window. The second point is to choose whether we will have this paper letter or e-mail. In order for the letter to be saved to a file, you should select the first one. Click “Next”. The third point asks us to create an address block, but since we created it manually, we uncheck all the boxes and move on to the next step. Here we are asked to insert a request, but we already have one, so we click “Next”. The sixth point is to edit the document, look at the document with fields inserted from the database and, if necessary, exclude some fields.
Step 7 asks you to edit each of the files created as a result of connecting the template to the database. And finally, in the last, 8th step, you can choose what to do with the received documents. You can save, print or send by e-mail. In this case, you can save all documents in one file, or each document separately.
In MS Word, the merge process ends by clicking the Find and Merge button.
From the drop-down menu it is obvious that all letters can be printed and sent via e-mail, but it is not obvious that all of them can be saved to a file. To save, you need to select the first item - “Change individual documents”. This will open new document, in which each new letter will be located with new page, and here you can save this file.
Conclusion
And so, in this lesson you learned how to use the merge tool to create documents based on a template. Let's briefly write down the sequence of actions:
- It is necessary to create a database in the table processor from which data will be taken to fill in the fields.
- You need to create a letter template.
- Perform the merge and save the result.
In previous articles in the “Automating Document Filling” series, I talked about how to create the application’s user interface, organize input data validation, and get numbers in words without using VBA code. In this final article we will talk about the magic of transferring all the necessary values from an Excel workbook to a Word document. Let me show you what should happen in the end:
Description of the mechanism
To start with general outline I will describe exactly how the data will be transferred to a Word document. First of all, we need a template Word document, 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; this is most conveniently done using bookmarks. After this, you need to organize the Excel data in such a way as to ensure compliance with 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 here is extremely simple - we create a regular document, type and format the text, in general, we achieve the required form. In those places where you will need to substitute values from Excel, you need to create bookmarks. This is done as follows:
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 menu item “File” -> “Save as...”.
Preparing Excel Data
For convenience, I decided to place all the data that needs to be transferred to a 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 obtained from auxiliary tables located on the Support sheet. In this article I will not analyze the formulas that calculate the required values; if something is unclear, ask questions in the comments.
At this stage, it is important to correctly indicate all bookmark names - the correctness of data transfer depends on this.
Transfer procedure
But this is the most interesting thing. There are two options for executing the data migration code:
- The code runs in an Excel workbook, the data is transferred to Word one value at a time and 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 exactly what I used.
Here's what you need to do:
- Create a Word document template with macro support. This template will contain executable VBA code.
Why do you need to create a template? This way you can protect program code from unintentional change. The fact is that every time you open a template, it creates new file Word, which simply cannot be saved over an existing template. The template itself remains unchanged.
- In the created template you need to place a program written in VBA. To do this, when editing the template, press the key combination Alt+F11 and enter in the editor window that opens Visual Basic program code.
- In an Excel workbook, write code that calls the filling 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 file FillDocument.dotm, located in the Template folder in the archive with the example.
How can you use all this to solve your particular problem?
I understand that in words this all looks very simple, but what happens in practice? I suggest you simply use a ready-made option. Download the archive with the example, in the 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 several constants; they are located 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 "Creating Confirmations". This workbook has 4 worksheets, 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 program for filling out bookmarks, and the second is a form to fill out. You can use the template with the program without modifications, but the form to fill out, naturally, will have to be redone in accordance with your needs.
How to rework the example “for yourself”?
- Prepare a Word document template to be filled out. Create all the necessary bookmarks in it and save it as a “MS Word template”.
- 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 out the template bookmarks, and nothing needs to be changed in it.
- Prepare a working Excel workbook for data entry. It's up to you to decide whether she will have any "advanced" user interface and carry out 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 that needs to be substituted.
- Insert the VBA program code from the example file into the prepared workbook. Replace all constants according to your project.
- Test for correct operation.
- Actively use it!
