How to Build a Table from a DataTable

Skip to end of metadata
Go to start of metadata
You can download the complete source code of the ImportTableFromDataTable sample here.

Often your application will pull data from a database and store it in the form of a DataTable. You may wish to easily insert this data into your document as a new table and quickly apply formatting to the whole table.

Using Aspose.Words this task is very simple to achieve. The code presented within this article demonstrates how to do this.

Note that the preferred way of inserting data from a DataTable into a document table is by using Mail Merge with Regions . The technique presented in this article is only suggested if you are unable to create a suitable template before hand to merge data with, in other words if you require everything to happen programmatically.

The Solution

  1. Create a new DocumentBuilder object on your Document.
  2. Start a new table using DocumentBuilder.
  3. If we want to insert the names of each of the columns from our DataTable as a header row then iterate through each data column and write the column names into a row in the table.
  4. Iterate through each DataRow in the DataTable.
    1. Iterate through each object in the DataRow.
    2. Insert the object into the document using DocumentBuilder. The method used depends on the type of the object being inserted e.g DocumentBuilder.Writeln for text and DocumentBuilder.InsertImage for a byte array which represents an image.
    3. At the end of processing of the DataRow also end the row being created by the DocumentBuilder by using DocumentBuilder.EndRow.
  5. Once all rows from the DataTable have been processed finish the table by calling DocumentBuilder.EndTable.
  6. Finally we can set the desired table style using one of the appropriate table properties such as Table.StyleIdentifier to automatically apply formatting to the entire table.
    The following data in our DataTable is used in this example:

The Code

The following code demonstrates how to achieve this in Aspose.Words. The ImportTableFromDataTable method accepts a DocumentBuilder object, the DataTable containing the data and a flag which specifies if the column heading from the DataTable are included at the top of the table. This method builds a table from these parameters using the builder’s current position and formatting.

Example

Provides a method to import data from the DataTable and insert it into a new table using the DocumentBuilder.

C#
/// <summary>
/// Imports the content from the specified DataTable into a new Aspose.Words Table object. 
/// The table is inserted at the current position of the document builder and using the current builder's formatting if any is defined.
/// </summary>
public static Table ImportTableFromDataTable(DocumentBuilder builder, DataTable dataTable, bool importColumnHeadings)
{
    Table table = builder.StartTable();

    // Check if the names of the columns from the data source are to be included in a header row.
    if (importColumnHeadings)
    {
        // Store the original values of these properties before changing them.
        bool boldValue = builder.Font.Bold;
        ParagraphAlignment paragraphAlignmentValue = builder.ParagraphFormat.Alignment;

        // Format the heading row with the appropriate properties.
        builder.Font.Bold = true;
        builder.ParagraphFormat.Alignment = ParagraphAlignment.Center;

        // Create a new row and insert the name of each column into the first row of the table.
        foreach (DataColumn column in dataTable.Columns)
        {
            builder.InsertCell();
            builder.Writeln(column.ColumnName);
        }

        builder.EndRow();

        // Restore the original formatting.
        builder.Font.Bold = boldValue;
        builder.ParagraphFormat.Alignment = paragraphAlignmentValue;
    }

    foreach (DataRow dataRow in dataTable.Rows)
    {
        foreach (object item in dataRow.ItemArray)
        {
            // Insert a new cell for each object.
            builder.InsertCell();

            switch (item.GetType().Name)
            {
                case "Byte[]":
                    // Assume a byte array is an image. Other data types can be added here.
                    builder.InsertImage(GetImageFromByteArray((byte[])item), 50, 50);
                    break;
                case "DateTime":
                    // Define a custom format for dates and times.
                    DateTime dateTime = (DateTime)item;
                    builder.Write(dateTime.ToString("MMMM d, yyyy"));
                    break;
                default:
                    // By default any other item will be inserted as text.
                    builder.Write(item.ToString());
                    break;
            }

        }

        // After we insert all the data from the current record we can end the table row.
        builder.EndRow();
    }

    // We have finished inserting all the data from the DataTable, we can end the table.
    builder.EndTable();

    return table;
}
Visual Basic
''' <summary>
''' Imports the content from the specified DataTable into a new Aspose.Words Table object. 
''' The table is inserted at the current position of the document builder and using the current builder's formatting if any is defined.
''' </summary>
Public Shared Function ImportTableFromDataTable(ByVal builder As DocumentBuilder, ByVal dataTable As DataTable, ByVal importColumnHeadings As Boolean) As Table
    Dim table As Table = builder.StartTable()

    ' Check if the names of the columns from the data source are to be included in a header row.
    If importColumnHeadings Then
        ' Store the original values of these properties before changing them.
        Dim boldValue As Boolean = builder.Font.Bold
        Dim paragraphAlignmentValue As ParagraphAlignment = builder.ParagraphFormat.Alignment

        ' Format the heading row with the appropriate properties.
        builder.Font.Bold = True
        builder.ParagraphFormat.Alignment = ParagraphAlignment.Center

        ' Create a new row and insert the name of each column into the first row of the table.
        For Each column As DataColumn In dataTable.Columns
            builder.InsertCell()
            builder.Writeln(column.ColumnName)
        Next column

        builder.EndRow()

        ' Restore the original formatting.
        builder.Font.Bold = boldValue
        builder.ParagraphFormat.Alignment = paragraphAlignmentValue
    End If

    For Each dataRow As DataRow In dataTable.Rows
        For Each item As Object In dataRow.ItemArray
            ' Insert a new cell for each object.
            builder.InsertCell()

            Select Case item.GetType().Name
                Case "Byte[]"
                    ' Assume a byte array is an image. Other data types can be added here.
                    builder.InsertImage(GetImageFromByteArray(CType(item, Byte())), 50, 50)
                Case "DateTime"
                    ' Define a custom format for dates and times.
                    Dim dateTime As DateTime = CDate(item)
                    builder.Write(dateTime.ToString("MMMM d, yyyy"))
                Case Else
                    ' By default any other item will be inserted as text.
                    builder.Write(item.ToString())
            End Select

        Next item

        ' After we insert all the data from the current record we can end the table row.
        builder.EndRow()
    Next dataRow

    ' We have finished inserting all the data from the DataTable, we can end the table.
    builder.EndTable()

    Return table
End Function
The method can then be easily called using your DocumentBuilder and data.
Example

Shows how to import the data from a DataTable and insert it into a new table in the document.

C#
// Create a new document.
Document doc = new Document();

// We can position where we want the table to be inserted and also specify any extra formatting to be
// applied onto the table as well.
DocumentBuilder builder = new DocumentBuilder(doc);

// We want to rotate the page landscape as we expect a wide table.
doc.FirstSection.PageSetup.Orientation = Orientation.Landscape;

// Retrieve the data from our data source which is stored as a DataTable.
DataTable dataTable = GetEmployees(databaseDir);

// Build a table in the document from the data contained in the DataTable.
Table table = ImportTableFromDataTable(builder, dataTable, true);

// We can apply a table style as a very quick way to apply formatting to the entire table.
table.StyleIdentifier = StyleIdentifier.MediumList2Accent1;
table.StyleOptions = TableStyleOptions.FirstRow | TableStyleOptions.RowBands | TableStyleOptions.LastColumn;

// For our table we want to remove the heading for the image column.
table.FirstRow.LastCell.RemoveAllChildren();

doc.Save(dataDir + "Table.FromDataTable Out.docx");
Visual Basic
' Create a new document.
Dim doc As New Document()

' We can position where we want the table to be inserted and also specify any extra formatting to be
' applied onto the table as well.
Dim builder As New DocumentBuilder(doc)

' We want to rotate the page landscape as we expect a wide table.
doc.FirstSection.PageSetup.Orientation = Orientation.Landscape

' Retrieve the data from our data source which is stored as a DataTable.
Dim dataTable As DataTable = GetEmployees(databaseDir)

' Build a table in the document from the data contained in the DataTable.
Dim table As Table = ImportTableFromDataTable(builder, dataTable, True)

' We can apply a table style as a very quick way to apply formatting to the entire table.
table.StyleIdentifier = StyleIdentifier.MediumList2Accent1
table.StyleOptions = TableStyleOptions.FirstRow Or TableStyleOptions.RowBands Or TableStyleOptions.LastColumn

' For our table we want to remove the heading for the image column.
table.FirstRow.LastCell.RemoveAllChildren()

doc.Save(dataDir & "Table.FromDataTable Out.docx")

The Result

The following table is produced by running the code above:

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.