wordpress hit counter
Generating a WordprocessingML Table from a Data Source - OpenXML Developer - Blog - OpenXML Developer

Generating a WordprocessingML Table from a Data Source

Blog

Samples, Demos, and Reference Articles

Generating a WordprocessingML Table from a Data Source

  • Comments 3

Author: Sanjay Kumar Madhva of Sonata Software Limited

Introduction

In the previous article “Creating an Open XML Document in .NET” we demonstrated how to create a word document using .NET, where we created the document.xml which looked as shown below.

<?xml version="1.0" encoding="utf-8" ?>
<w:wordDocument xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/3/main">
  <w:body>
    <w:p>
      <w:r>
        <w:t>Text as paragraph 1 </w:t>
      </w:r>
    </w:p>
    <w:p>
      <w:r>
        <w:t>Text as paragraph 2 </w:t>
      </w:r>
    </w:p>
    .
    .
    .
    .
    .
    .
    <w:p>
      <w:r>
        <w:t>Text as paragraph n </w:t>
      </w:r>
    </w:p>
  </w:body>
</w:wordDocument>

When the above XML was packaged into a word document, the document contained n paragraph of text.

To take “Creating an Open XML Document in .NET” to the next step we will try to display text in tabular format. This can be achieved by creating a document.xml as shown below.

<?xml version="1.0" encoding="utf-8" standalone="yes" ?> 
<w:wordDocument xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/3/main">
  <w:body>
    <w:tbl>
      <w:tr>
        <w:tc>
          <w:p>
            <w:r>
              <w:t>Text for Row 1 Col 1 </w:t> 
            </w:r>
          </w:p>
        </w:tc>
        <w:tc>
          <w:p>
            <w:r>
              <w:t>Text for Row 1 Col 2 </w:t> 
            </w:r>
          </w:p>
        </w:tc>
        .
        .
        .
        <w:tc>
          <w:p>
            <w:r>
              <w:t>Text for Row 1 Col n </w:t> 
            </w:r>
          </w:p>
        </w:tc>
      </w:tr>
      <w:tr>
        <w:tc>
          <w:p>
            <w:r>
              <w:t>Text for Row 2 Col 1 </w:t> 
            </w:r>
          </w:p>
        </w:tc>
        .
        .
        .
        <w:tc>
          <w:p>
            <w:r>
              <w:t>Text for Row 2 Col n </w:t> 
            </w:r>
          </w:p>
        </w:tc>
      </w:tr>
      .
      .
      .
      <w:tr>
        <w:tc>
          <w:p>
            <w:r>
              <w:t>Text for Row N Col 1 </w:t> 
            </w:r>
          </w:p>
        </w:tc>
        .
        .
        .
        <w:tc>
          <w:p>
            <w:r>
              <w:t>Text for Row N Col n </w:t> 
            </w:r>
          </w:p>
        </w:tc>
      </w:tr>
    </w:tbl>
  </w:body>
</w:wordDocument>

NOTE: there are different namespace references for Office 2007 Beta 1 and Beta 2 as follows ...

Beta 2: http://schemas.openxmlformats.org/wordprocessingml/2006/3/main
Beta 1: http://schemas.microsoft.com/office/word/2005/10/wordml

There could not be a better example for table on a word document than taking data from any table of any database and saving it as word document. In this article we will try to achieve the same. To do this we need to connect to a database. Load the data into a dataset. From the table for each row get every column and create an XML as shown above.

Step 1. Create a new C# project.

Create a new project “WordDocCreator” in a new solution.

Step 2. Add reference to WindowsBase provided WinFx.

Right click on the reference and form the pop up menu select “Add Reference”

Add reference windows pops up. Select “Windows Base” from the .NET tab as shown in the fig below.

Step 3. Create a windows form.

Create a form as shown above which contains the following controls and also add the static text as shown in the figure above.

1. Text Box “ConnectionString“
(This will contain the connection string to connect to the database)

2. Combo Box “TableList“
(This will contain the entire table available in the database)

3. List Box “ColumnsList"
(This will contain all the column of the selected table used to generate the Select statement)

4. Button “LoadData“
(When clicked will take the generated select statement an load it DataGridView)

5. Text Box “SqlStatment“
(This will contain the generated select statement)

6. DataGridView “myDataGridView“
(Which will display retrieve the data of the select statement in a Grid format)

7. Button “GenerateDocument“
(On click, will take the content displayed in the DataGridView and create a OpenXML word document form it)

8. Button “Exit“
(Exit button will close the window and exit out)

Step 4. Add using directive

Go the code behind and add the using directive as shown below.

using System.Data.SqlClient;
using System.Xml;
using System.IO;
using System.IO.Packaging;

Step 5. Define private variable.

Declare some private variables that will be used by the application.

private string _connectionString;  // Contains the connection string to the database.

private SqlConnection conn = null; // Connection object.

DataTable table = new DataTable (); // Data from the database to be loaded into.

private string _DocumentFileName = @"Document.xml"; // Document XML file name

private string docFileName = "";  // Word document file name

private Package package = null;

Step 6. Create Methods

Load Table Method

This method will get the list of columns from the Sys.Column table for the selected table.

private void loadTable ()
{
  try
  {
    // connectionString = @"Data Source=SON1289\sqlexpress;Initial
    //                                  Catalog=AdventureWorks;Integrated Security=True"
    if (conn == null)
    {
      conn = new SqlConnection (_connectionString);
    }
    else
    {
      conn.ConnectionString = _connectionString;
    }
    SqlCommand comm = new SqlCommand ("SELECT ss.name + '.' + st.name " +
        "  FROM sys.tables st, sys.schemas ss " + 
        " WHERE ss.schema_id = st.schema_id;", conn);
    conn.Open();

    TableList.Items.Clear ();
    ColumnsList.Items.Clear ();
    SqlStatment.Text = "";

    SqlDataReader reader = comm.ExecuteReader ();

    // Call Read before accessing data.
    while (reader.Read())
    {
      TableList.Items.Add(reader[0]);
    }

    // Call Close when done reading.
    reader.Close();
    conn.Close();
  }
  catch (Exception ex)
  {
    MessageBox.Show (ex.Message);
  }
}

LoadColumn Method.

This method will get the list of columns from the Sys.Column table for the user-selected table.

private void loadColumns (string tableName)
{
  string selectstatement = "SELECT sc.name " + 
      "  FROM sys.tables st, sys.columns sc, sys.schemas  ss " +  
      " WHERE st.object_id = sc.object_id " + 
      "   AND ss.schema_id = st.schema_id " + 
      "   AND ss.name + '.' + st.name = '" + tableName + "'";
  SqlCommand comm = new SqlCommand (selectstatement, conn);
  conn.Open ();

  SqlDataReader reader = comm.ExecuteReader();
  ColumnsList.Items.Clear();
  SqlStatment.Text = "";

  // Call Read before accessing data.
  while (reader.Read())
  {
    ColumnsList.Items.Add(reader[0]);
  }

  // Call Close when done reading.
  reader.Close();
  conn.Close();
}

GenetateSelect method

This method will create a select statement out of the user specified table and the column.

private string GenetateSelect ()
{
string selectstatement ;
string select = "SELECT ";
string selectcolumns = "";
string SelectFrom = " FROM ";
string selectTable = TableList.Text;

// if any column is selected in the column list box
if (ColumnsList.SelectedItems.Count > 0)    {
//Generate column list for select statement
for (int COUNT = 0; COUNT < ColumnsList.SelectedItems.Count ; COUNT++)
{
if (selectcolumns == "")
{
selectcolumns = ColumnsList.SelectedItems[COUNT].ToString();
}
else
{
selectcolumns += ", " + ColumnsList.SelectedItems[COUNT].ToString();
}
}
selectstatement = select + selectcolumns + SelectFrom + selectTable;
} 
else
{
selectstatement = "select * From " + TableList.Text;
}

return selectstatement;
}

GenerateDocumentXML Method.

This method will take data from the table and create a XML form of the document that becomes the main part Word document. (In the previous article we used DOM to build the XML in this article we are using string builder)

Private void GenerateDocumentXML ()
{
  if (File.Exists (_DocumentFileName))
  {
    File.Delete (_DocumentFileName);
  }

  StringBuilder sb = new StringBuilder();
  if (table.Rows.Count > 0)
  {
    sb.Append(@" ");

    sb.Append(@""
      );

    sb.Append(@"");

    sb.Append(@"");

    foreach (DataRow row in table.Rows)
    {
      sb.Append(@"");
      foreach (DataColumn column in table.Columns)
      {
        sb.Append(@"");
        sb.Append(row[column].ToString() );
        sb.Append("");
      }
      sb.Append(@"");
    }
    sb.Append(@"");

    sb.Append(@"");

    sb.Append(@"");

    using (StreamWriter sw = new StreamWriter(_DocumentFileName))
    {
      // Add some text to the file.
      sw.Write(sb.ToString());
      sw.Close();
    }
  }
}

PackDocument Method

This method will take the “document.XML” created and pack it into a WordprocessingML.

private void packDocument()
{
  //-- Step 1 - Creating the Package
  this.package = Package.Open(docFileName, FileMode.Create, FileAccess.ReadWrite);

  //-- Step 2 - Create the main document part (document.xml)
  XmlDocument doc = new XmlDocument();
  doc.Load(@"document.xml");

  Uri uri = new Uri("/word/document.xml", UriKind.Relative);
  // Beta2 : 
  //application/vnd.openxmlformats-officedocument.wordprocessingml.document.main+xml
  // Beta1 : application/vnd.ms-word.main+xml
  PackagePart part = this.package.CreatePart(uri,
    "application/vnd.openxmlformats-officedocument.wordprocessingml.document.main+xml"
    );

  StreamWriter partWrt = new StreamWriter(part.GetStream(FileMode.Create,
    FileAccess.Write));

  doc.Save(partWrt);
  partWrt.Close();
  this.package.Flush();

  //-- Step 3 - Create the relationship file
  uri = new Uri("/word/document.xml", UriKind.Relative);
  string relShipType;
  //Beta2: ttp://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument 
  //Beta1 "http://schemas.microsoft.com/office/2006/relationships/officeDocument" 
  relShipType = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument";

  PackageRelationship rel = this.package.CreateRelationship(uri, TargetMode.Internal,
    relShipType,  "rId1");

  this.package.Flush();

  //-- Step 4- Close the document.
  this.package.Close();
}

GetSavePath Method

We may have to create a file dialog that will accept a filename from the user. We can call the SaveFileDialog provided by the .net framework to get the directory and the name of the file in which the user wants to save the document content.

The code is as follows.

//Get the file path where the user wants to save the new generated document.
private string GetSavePath()
  {
  SaveFileDialog sfd = new SaveFileDialog();
  sfd.AddExtension = true;

  //Get only Docx file
  sfd.Filter = "docx|";
  sfd.CheckPathExists = true;

  sfd.DefaultExt = ".docx";

  sfd.ShowDialog();

  return sfd.FileName;
  // return the filename and the path in which the user wants to create the file
}

CleanUp Method.

This method will delete the temp document.xml created.

The code is as follows.

private void cleanUp ()
{
  File.Delete("document.xml");
}

Step 6. Define Events

Form Load Event

When the form gets loaded the connection string is set to what ever is the default text present in the ConnectionString TextBox. And then the tables are loaded in the TableList combo Box by calling the LoadTable method.

Private void Form2_Load (object sender, EventArgs e)
{
  // @"Data Source=SON1289\sqlexpress;Initial Catalog=AdventureWorks;Integrated 
  // Security=True";
  _connectionString = ConnectionString.Text;
  loadTable();
}

ConnectionString TextChanged Event

This event is fired whenever user changes the connection string in the ConnectionString textbox. This event it will reload the tables from the newly provided connection string.

private void ConnectionString_TextChanged(object sender, EventArgs e)
{
  conn.ConnectionString = ConnectionString.Text;
  loadTable();
}

TableList Selection Index Changed Event

This event is fired whenever user selects a new table from the table list provided. This event it will load the columns for the newly selected table.

Private void TableList_SelectedIndexChanged(object sender, EventArgs e)
{
  string tableName = TableList.Text;
  loadColumns(tableName);
  SqlStatment.Text = GenetateSelect();
}

ColumnList Click Event

This event is fired whenever user selects or de-selects a column from the column list provided. This event it will generate a new Select statement for the selected column.

private void ColumnsList_Click(object sender, EventArgs e)
{
  SqlStatment.Text = GenetateSelect();
}

LoadData Click Event

This event is fired whenever user clicks on the Load Data button. This event it will load the grid with data which is returned by executing the select statement in the.

private void LoadData_Click(object sender, EventArgs e)
{
  try
  {
    table = new DataTable();
    SqlDataAdapter myAdpt = new SqlDataAdapter(SqlStatment.Text, conn);
    myAdpt.Fill(table);

    myDataGridView.DataSource = table;

    Statistics.Text  = "Statistics : Total number of rows = " + table.Rows.Count;
  }
  catch (Exception ex)
  {
    MessageBox.Show(ex.Message);
  }
}

GenerateDocument Click Event.

When the user clicks on generate button this will create a WordprocessingML.

private void GenerateDcoument_Click(object sender, EventArgs e)
{
  if (table.Rows.Count > 0)
  {
    GenerateDcoument.Enabled = false;
    docFileName = GetSavePath();
    if (docFileName.Length > 0)
    {
      GenerateDocumentXML();
      if (File.Exists(_DocumentFileName))
      {
        packDocument ();
        cleanUp(); 
      }
    }
    GenerateDcoument.Enabled = true;
  }
  else
  {
    MessageBox.Show ("No rows in the Grid View");
  }
}

Exit Click Event

Double click on the exit button to generate the click event for the button add close() in the button clicked event as shown below.

private void Exit_Click(object sender, EventArgs e)
{
  Close();
}

Step 7. Build and Run.

From the Build menu select “Build WordDocument”

To run with out debug, select “Start Without Debugging” under “Debug” menu or press Ctrl+F5.

Application window appears.

Select the table from the Combo Box

Select the column from the list box. When ever you select the column the Select statement is generated in the “Edit Select Statement” text box. You can change or key in the where criteria.

Click on “Load Table “ Button to see the select statement being executed and the data loaded in the grid.

Click on “Generate Document” button, a file dialog appears. Provide the file name and click save.

Note: remember the save in directory.

Open file explorer and go the above-specified directory. Double click on the document you just created.

See the content displayed in the grid as a word document table.

See source code for this project in ZIP file attached to this article.

Attachment: TableToDocument.zip
  • Extremely cool! Very nice of example of loading dynamic content from a db.

    I am looking for Power Point specific examples. Mainly working with the chart object and populating it from a database.
  • Our next article is on creation of Power point PPT from an UI which can be made to be populated from DB.
  • Dear Sanjay, your code is working properly but I have a problem common to other code snippets on this site. The Table and cell borders are not visible. I'm using Office 2007 (no beta versions) under Vista on 3 different pcs.
    Thanks for your kind attention!

    Salvatore Sorrentino
    salvatoresorrentino801@msn.com
Page 1 of 1 (3 items)