In a previous post, I showed you how to import charts from spreadsheets to a Wordprocessing document. Today, as promised in my last post, I am going to show you how to import a table from a Wordprocessing document into a spreadsheet.
To import a table from a Wordprocessing document to a spreadsheet we need to take the following actions:
My post will talk about using version 2 of the SDK.
For the sake of this post, let's say I am starting with the following Word document and table (notice that the table is contained within a content control):
Also, let's say I am starting with a completely empty Excel workbook, which only contains a style definition for tables:
If you just want to jump straight into the code, feel free to download this solution here.
As described in the solution section above, steps two and three require us to open the Word and Excel files. Below is the code snippet necessary to accomplish these tasks:
I created a generic method called ImportWordTable, which will accomplish the rest of the steps in the solution section above. The next step is to get access to the Word table object found within the content control, which can be accomplished with the following code:
Now that we have access to the Word table our next task is to create an Excel table based on the data contained within the Word table. This task can be accomplished with the following code snippet:
The code snippet above simply creates an Excel spreadsheet row and cell for every Word table row and cell. A spreadsheet cell is a bit special as compared to a Word cell because a spreadsheet cell defines the datatype of the cell content. In the example above, we have two types of spreadsheet cells:
Since this solution is about creating an Excel table, we need to take care of a couple of extra things. For one, we need to make sure that the first row of the table has only inline string cells, since we will be identifying this row as our table header. Secondly, we need to keep track of the header row strings because we need to reuse these strings within the table definitions part.
At the end of this step we have generated a valid Excel spreadsheet that contains all of the data from the Word table:
We could have called this job done at this point, but we can further improve this output by formatting the data as a table.
Looking at step #6 in the solution section above, we need to add a reference to a table definitions part within the worksheet where the data exists. Here is the code snippet that creates a new table definition part and adds a reference to that part from the worksheet that contains the table data:
The last major set of steps is to create the appropriate xml within our new table definitions part. Every table definition needs a unique name, unique id, display name, and appropriate data range reference in order to get everything to work properly. The following code snippet accomplishes these tasks:
One advantage of an Excel table vs. a Word table is an Excel table allows you to filter values within a column. It's pretty easy to add this functionality to our table with the following code:
Now let's add the table header information:
Almost done! Let's make this table look good by applying a table style. Once we apply the style we can append all the appropriate elements together and save our changes. These tasks can be accomplished with the following code:
Putting everything together and running this code, we end up with an Excel workbook, which contains a table imported from my Word document.
Here is a screenshot of the final workbook (notice the filtering capability):